/
搜索到
50
篇与
的结果
-
Kingdee-常用数据表 一、金蝶云星空常用数据表数据表命名基本规则|表名前缀: t_ 或者 T_视图前缀: v_ 或者 V_多语言表后缀: _L关联关系表后缀: _LK1.1 元数据描述表名元数据t_meta_objectType元数据扩展信息表t_meta_objectType_E元数据缓存表T_META_OBJECTTYPECACHE元数据视图表T_META_OBJECTTYPEVIEW元数据视图V_META_OBJECTTYPE_L元数据扩展多语言名称T_META_OBJECTTYPENAMEEX_L元数据序号T_META_OBJECTTYPE_E基础资料元数据信息表T_META_LOOKUPCLASS基础资料元数据类型表T_META_BASEDATATYPE单据类型表T_BAS_BILLTYPE，T_BAS_BILLTYPEFLDCTL枚举元数据表T_META_FORMENUM枚举项元数据表T_META_FORMENUMITEM转换规则元数据表T_META_CONVERTRULE反写规则元数据表t_bf_writebackrule反写规则扩展表t_BF_WriteBackRuleCust表单和表关系表T_META_TRACKERBILLTABLE领域模型T_MDL_DOMAINMODETYPE领域子模型T_MDL_DOMAINMODELSUB_L元素T_MDL_ELEMENTTYPE元素分组T_MDL_ELEMENTGROUP_L属性T_MDL_ELEMENTPROPERTY领域元素T_MDL_DOMAINMODELELEMENTMAP领域元素属性T_MDL_DOMAINMODELELPROPMAP表单服务T_MDL_FORMBUSINESS操作函数T_MDL_FORMFUNCTION操作T_MDL_FORMOPERATIONTYPE校验T_MDL_FORMVALIDATIONTYPE1.2 弹性域相关表描述表名辅助属性维度定义T_BD_FLEXAUXPROPERTY辅助属性数据表T_BD_FLEXSITEMDETAILV仓位值集表维度定义t_bas_flexValues仓位值集表维度仓位定义T_BAS_FLEXVALUESENTRY仓位值集数据表t_bas_flexValuesDetail核算维度维度定义T_BD_FLEXITEMPROPERTY核算维度数据表T_BD_FLEXITEMDETAILV1.3 菜单描述表名子系统分组T_META_TOPCLASS子系统T_META_SUBSYSTEM_L模块T_META_CONSOLESUBFUNC_L菜单T_META_CONSOLEDETAIL1.4 权限描述表名对象权限T_SEC_OBJECTTYPEPERMISSION权限表T_SEC_FIELDPERMISSION权限项表t_sec_permissionItem功能权限表T_SEC_FUNCPERMISSION功能权限项表T_SEC_FUNCPERMISSIONENTRY字段权限表T_SEC_FIELDPERMISSION字段权限项表T_SEC_FIELDPERMISSIONENTRY用户组织表T_SEC_USERORG角色表T_SEC_ROLE1.5 基础资料描述表名基础资料和表T_META_LOOKUPCLASS组织表T_ORG_ORGANIZATIONS物料T_BD_MATERIAL采购价目表t_PUR_PriceList采购价目分发表t_PUR_PriceList_Issue用户表T_SEC_USER部门表T_BD_DEPARTMENT员工信息表T_BD_STAFF员工任职信息表T_BD_STAFFPOSTINFO业务员表T_BD_OPERATOR业务员分录表T_BD_OPERATORENTRY销售员表V_BD_SALESMAN销售员单据体表V_BD_SALESMANENTRY岗位信息表T_ORG_POST\HR_ORG_HRPOST岗位对应角色表T_ORG_POSTROLE辅助资料表T_BAS_ASSISTANTDATAENTRY辅助资料类别表T_BAS_ASSISTANTDATA基础资料分配表T_ORG_BASEDATAALLOCATEENTRY仓库T_BD_Stock供应商t_BD_Supplier1.6 网控描述表名网控对象表T_BAS_NETWORKCTRLOBJECT互斥对象表T_BAS_NETWORKCTRLMUTEX网络控制表T_BAS_MUTEX网控记录表T_BAS_NETWORKCTRLRECORDS1.7 参数描述表名用户参数表T_BAS_UserParameter系统参数表T_BAS_SysParameter发布参数表T_BAS_ReleaseParameter单据参数表t_BAS_BillGlobalParameter用户界面参数T_BAS_FormParameter1.8 服务描述表名表单服务表T_MDL_FORMBUSINESS操作服务表T_MDL_FORMOPERATIONTYPE校验器表T_MDL_FORMVALIDATIONTYPE1.9 日志描述表名记录引出记录表T_BAS_EXPORTRECORD上机操作日志表t_bas_operateLog上机操作日志备份表t_bas_operateLogBK系统日志表T_BAS_LOG执行计划日志表T_BAS_SCHEDULELOGWebApi日志T_BAS_WEBAPILOG1.10 管理中心描述表名数据中心表T_BAS_DATACENTER补丁日志表表t_bos_installedpackagehis1.11 业务流程图描述表名业务流程版本表T_BF_DEFVERSION业务流程发布表t_BF_Publish业务流程发布分录表t_BF_PublishEntry（流程跟单据类型的关系）业务流程定义单据检索表T_BF_DEFVERSIONLOOKUP（流程跟表的关系）业务流程定义表T_BF_PROCDEF数据中心表T_BAS_DATACENTER1.12 反写描述表名反写规则元数据表t_bf_writebackrule反写规则元数据扩展表t_BF_WriteBackRuleCust反写记录表t_bf_instanceSnap反写记录历史表t_bf_instanceSnapHis反写记录归档表t_bf_SnapBackUp反写日志表T_BF_WBLOG1.13 业务流程实例描述表名业务流程实例表t_BF_Instance业务流程节点表t_BF_InstanceEntry业务流程控制字段反写记录表t_bf_instanceAmount业务流程实例历史表t_bf_instanceHis业务流程节点历史表t_bf_instanceEntryHis业务流程控制字段反写记录历史表t_bf_instanceAmountHis业务流程实例归档表t_bf_instBackUp业务流程节点归档表t_bf_instEntryBackUp业务流程控制字段反写记录归档表t_bf_instAmountBackUp业务流程压缩文件日志表t_bf_InstArchiveLog业务流程压缩文件表t_BF_ArchiveFiles1.14 过滤方案描述表名过滤方案表T_BAS_FILTERSCHEME万能报表默认过滤T_BOS_DEVREPORTDATASOURCE1.15 单据销售描述表名订单表T_SAL_ORDER销售订单明细表T_SAL_ORDERENTRY销售出库单表T_SAL_OUTSTOCK销售出库单明细表T_SAL_OUTSTOCKENTRY应收单表t_AR_receivable应收单明细表t_AR_receivableEntry应付单表T_AP_PAYABLE应付单明细表T_AP_PAYABLEENTRY采购订单表t_PUR_POOrder采购订单明细表t_PUR_POOrderEntry生产订单表PRD_MO、T_PRD_MO工序计划表T_SFC_OPERPLANNING工序计划系列表T_SFC_OPERPLANNINGSEQ工序汇报表T_SFC_OPTRPTENTRY工序转移表T_SFC_OPERATIONTRANSFER生成入库单PRD_INSTOCK 、T_PRD_INSTOCK付款申请单T_CN_PAYAPPLY1.16 附件和附件信息描述表名附件信息T_BAS_FileServerFileInfo附件跟单据关系表T_BAS_ATTACHMENT1.17 报表描述表名关键字T_MDL_RPTKEYWORDS1.18 工作流和审批流描述表名流程定义t_WF_ProcDef版本t_wf_DefVersion流程类型t_WF_ProcessType流程模板t_WF_Template流程发起配置t_wf_prclaunchconfig、T_WF_PRCLAUNCHCONFIG_L流程实例和单据关系t_WF_PiBiMap流程实例T_WF_PROCINST流程节点实例T_WF_ACTINST流程任务T_WF_ASSIGN流程任务接收人T_WF_RECEIVER流程任务详情T_WF_APPROVALASSIGN流程任务处理记录T_WF_APPROVALITEM流程反馈信息表T_WF_DISCUSS1.19 其他描述表名临时表T_BAS_TEMPORARYTABLENAME单据类型T_BAS_BILLTYPE安装包t_bos_installedpackage -
Kingdee-SQL语句汇总 Matsuoka SQL一、文档：管理中心：http://localhost:8000/cmc.aspx默认仅允许在管理中心服务器本机进行访问使用管理中心安全加固：https://vip.kingdee.com/article/248777993676668672?productLineId=1&isKnowledge=2金蝶星空数据库运维：https://vip.kingdee.com/article/313695351519426560?productLineId=1金蝶云星空数据库大表清理:https://vip.kingdee.com/article/162587?productLineId=1数据库优化：https://vip.kingdee.com/article/287011190763242496?productLineId=1https://vip.kingdee.com/article/295124010390797312?productLineId=1&share_fromuid=78815仓位相关说明：https://vip.kingdee.com/article/24803?productLineId=1根据仓位ID查询仓位信息:https://vip.kingdee.com/article/290430888922655744?productLineId=1SQL Server数据库开启自动收缩引发的严重系统阻塞案例:https://vip.kingdee.com/article/125287533700639744?productLineId=1&isKnowledge=2https://open.kingdee.com/K3Cloud/WenKu/DocumentView.aspx?docId=101963https://open.kingdee.com/K3Cloud/CDPPortal/DataModel.aspx教程：https://vip.kingdee.com/school/topic?productLineId=1&opc=1&so=desc&pro=1&searchText=金蝶BOS二次开发案例演示https://vip.kingdee.com/article/94751030918525696?productLineId=1&isKnowledge=2https://vip.kingdee.com/article/330000931540780032?productLineId=1套打实体动态字段应用汇总：https://vip.kingdee.com/article/314428448414320640?productLineId=1&isKnowledge=2https://vip.kingdee.com/people/feikang-2147403458?productLineId=1https://vip.kingdee.com/article/254305127295375360?productLineId=1 仓位账表：https://vip.kingdee.com/article/238593661947423488?productLineId=1金蝶云平台:https://cloud.kingdee.com/qy/###二、SQL语句-- 查询业务关联数据表SELECT a.FID,b.FNAME,a.Item FROM (SELECT FKERNELXML.query('//TableName')'Item', * FROM T_META_OBJECTTYPE) a JOIN T_META_OBJECTTYPE_L b ON a.FID = b.FID AND b.FLOCALEID = 2052 WHERE b.FNAME LIKE '%销售订单%' SELECT FID, (SELECT FNAME FROM T_META_OBJECTTYPE_L WHERE FID = a.FID AND FLOCALEID = 2052) FNAME, Item FROM (SELECT FKERNELXML.query('//TableName')'Item', * FROM T_META_OBJECTTYPE) a WHERE FID = 'SAL_SaleOrder'-- 表单查询SELECT b.FNAME,a.* FROM T_META_OBJECTTYPE a LEFT JOIN T_META_OBJECTTYPE_L b ON a.FID=b.FID AND b.FLOCALEID=2052 WHERE a.FMODELTYPEID=100 AND a.FDEVTYPE IN (0,1) AND b.FNAME ='采购订单'-- 获取销售出库单的单据视图内码SELECT c.FNAME AS 业务对象名称,a.FDEPENDENCYOBJECTID AS [业务对象内码(FormId)], b.FNAME AS 视图名称,a.FID AS [视图内码(layoutid)],* FROM T_META_OBJECTTYPEVIEW a LEFT JOIN T_META_OBJECTTYPEVIEW_L b ON a.FID=b.FID AND b.FLOCALEID=2052 LEFT JOIN T_META_OBJECTTYPE_L c ON a.FDEPENDENCYOBJECTID=c.FID AND c.FLOCALEID=2052 WHERE a.FDEPENDENCYOBJECTID IN ('SAL_OUTSTOCK','6bd6de25-2d2f-4c9e-aa95-166db3e9f198')-- 功能查询SELECT L.FNAME,H.FNAME,B.FNAME,E.FNAME FROM T_META_CONSOLESUBFUNC A JOIN T_META_CONSOLESUBFUNC_l B ON A.FSUBFUNCID = B.FSUBFUNCID AND B.FLOCALEID = 2052 JOIN(SELECT C.FSUBFUNCID, C.FOBJECTID, D.FNAME FROM T_META_CONSOLEDETAIL C JOIN T_META_CONSOLEDETAIL_l D ON C.FDETAILFUNCID = D.FDETAILFUNCID AND D.FLOCALEID = 2052)E ON A.FSUBFUNCID = E.FSUBFUNCID JOIN(SELECT F.FID, G.FNAME, F.FTOPCLASSID FROM T_META_SUBSYSTEM F JOIN T_META_SUBSYSTEM_l G ON F.FID = G.FID AND G.FLOCALEID = 2052) H ON A.FSUBSYSTEMID = H.FID JOIN(SELECT J.FTOPCLASSID, K.FNAME FROM T_META_TOPCLASS J JOIN T_META_TOPCLASS_L K ON J.FTOPCLASSID = K.FTOPCLASSID AND K.FLOCALEID = 2052) L ON H.FTOPCLASSID = L.FTOPCLASSID ORDER BY L.FNAME,H.FNAME,B.FNAME,E.FNAME-- 查询部门信息T_BD_DEPARTMENT 部门表 T_BD_DEPARTMENT_L 部门多语言表 SELECT a.FDEPTID,b.FNAME,b.FFULLNAME FROM T_BD_DEPARTMENT a JOIN T_BD_DEPARTMENT_L b ON a.FDEPTID = b.FDEPTID WHERE b.FNAME = '信息科'-- 按部门查询人员信息SQLSELECT a.FSTAFFID,a.FMASTERID,a.FNUMBER,a.FPERSONID,a.FPOSTID,a.FDEPTID, b.FNAME,c.FNAME FROM T_BD_STAFF a JOIN T_BD_STAFF_L b ON a.FSTAFFID = b.FSTAFFID JOIN T_BD_DEPARTMENT_L c ON a.FDEPTID = c.FDEPTID WHERE a.FDEPTID = '180641'-- 销售订单SELECT a.FID, b.FCREATEDATE 日期,b.FBILLNO 单据编号,c.FNAME 物料名称,c.FSPECIFICATION 规格型号,d.FNAME 单位, a.FQTY 数量,e.FNAME 销售部门,g.FNAME 销售员,h.FNAME 客户,i.FNAME 单据类型,i.FLOCALEID FROM T_SAL_ORDERENTRY a LEFT JOIN T_SAL_ORDER b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL_L c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_UNIT_L d ON a.FUNITID = d.FUNITID LEFT JOIN T_BD_DEPARTMENT_L e ON b.FSALEDEPTID = e.FDEPTID LEFT JOIN T_BD_OPERATORENTRY f ON b.FSALERID = f.FENTRYID LEFT JOIN T_BD_STAFF_L g ON f.FSTAFFID = g.FSTAFFID LEFT JOIN T_BD_CUSTOMER_L h ON b.FCUSTID = h.FCUSTID LEFT JOIN T_BAS_BILLTYPE_L i ON b.FBILLTYPEID = i.FBILLTYPEID WHERE b.FBILLNO = 'XSDD000152' AND i.FLOCALEID = '2052'-- 销售报价单SELECT a.FID, b.FBILLNO 单据编号,c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号, CONVERT(DECIMAL(18,0),a.FQTY) 数量,e.FNAME 单位,CONVERT(DECIMAL(18,2),f.FPRICE) 单价, CONVERT(DECIMAL(18,2),f.FTAXNETPRICE) 含税单价, CONVERT(DECIMAL(18,0),f.FTAXRATE) "税率%", CONVERT(DECIMAL(18,2),f.FAMOUNT) 金额,CONVERT(DECIMAL(18,2),f.FALLAMOUNT) 含税金额, h.FNAME 客户,g.FNAME 单据类型 FROM T_SAL_QUOTATIONENTRY a LEFT JOIN T_SAL_QUOTATION b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID LEFT JOIN T_SAL_QUOTATIONENTRY_F f ON a.FID = f.FID LEFT JOIN T_BAS_BILLTYPE_L g ON b.FBILLTYPEID = g.FBILLTYPEID LEFT JOIN T_BD_CUSTOMER_L h ON b.FCUSTID = h.FCUSTID WHERE b.FBILLNO = 'XSBJD0003'-- 销售价目表SELECT a.FID ID, b.FNUMBER 编号,c.FNAME 名称,d.FNUMBER 物料编号,e.FNAME 物料名称,e.FSPECIFICATION 规格,f.FNAME 单位, a.FPRICE 价格,a.FDOWNPRICE 最低限价, CONVERT(varchar(10),a.FEFFECTIVEDATE,126) 生效日期,CONVERT(varchar(10),a.FEXPRIYDATE,126) 失效日期 FROM T_SAL_PRICELISTENTRY a LEFT JOIN T_SAL_PRICELIST b ON a.FID = b.FID LEFT JOIN T_SAL_PRICELIST_L c ON b.FID = c.FID LEFT JOIN T_BD_MATERIAL d ON a.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_MATERIAL_L e ON d.FMATERIALID = e.FMATERIALID LEFT JOIN T_BD_UNIT_L f ON a.FUNITID = f.FUNITID WHERE b.FNUMBER = 'XSJMB0005'-- 即时库存查询SELECT a.FMATERIALID 物料内码, b.FNUMBER 物料编码, c.FNAME 物料名称, c.FSPECIFICATION 规格型号, d.FNUMBER 仓库代码,e.FNAME 仓库名称,d.FADDRESS 仓库地址,CONVERT(DECIMAL(18,0),a.FBASEQTY) 数量, h.FNUMBER 批号 ,g.FNAME 库存主单位, i.FNAME 库存状态,j.FNAME 库存组织 FROM T_STK_INVENTORY a LEFT JOIN T_BD_MATERIAL b ON a.FMATERIALID = b.FMATERIALID LEFT JOIN T_BD_MATERIAL_L c ON b.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_STOCK d ON a.FSTOCKID = d.FSTOCKID LEFT JOIN T_BD_STOCK_L e ON d.FSTOCKID = e.FSTOCKID LEFT JOIN T_BD_UNIT f ON a.FSTOCKUNITID = f.FUNITID LEFT JOIN T_BD_UNIT_L g ON f.FUNITID = g.FUNITID LEFT JOIN T_BD_LOTMASTER h ON b.FMASTERID = h.FMATERIALID LEFT JOIN T_BD_STOCKSTATUS_L i ON a.FSTOCKSTATUSID = i.FSTOCKSTATUSID LEFT JOIN T_ORG_ORGANIZATIONS_L j ON a.FSTOCKORGID = j.FORGID WHERE b.FNUMBER IN ('2301004700','2518072700','2518066500','2518066300') AND j.FLOCALEID = 2052-- 采购申请单SELECT a.FID,f.FNAME 申请人,d.FNAME 单据类型,b.FBILLNO 单据编号,b.FAPPLICATIONDATE 申请日期,e.FNUMBER 物料编码, c.FNAME 物料名称, c.FSPECIFICATION 规格型号,a.FREQQTY 申请数量,a.FAPPROVEQTY 批准数量,a.FEVALUATEPRICE 单价,a.FTAXPRICE 含税单价,h.FNAME 单位, a.FAMOUNT 金额,(a.FREQQTY*a.FTAXPRICE) as 含税金额 ,a.FARRIVALDATE 到货日期,g.FNAME 供应商,i.FSRCBILLNO 来源单号,i.FDEMANDBILLNO 需求单号 FROM T_PUR_REQENTRY a LEFT JOIN T_PUR_REQUISITION b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL_L c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BAS_BILLTYPE_L d ON b.FBILLTYPEID = d.FBILLTYPEID LEFT JOIN T_BD_MATERIAL e ON a.FMATERIALID = e.FMATERIALID LEFT JOIN T_BD_STAFF_L f ON b.FAPPLICANTID = f.FSTAFFID LEFT JOIN T_BD_Supplier_L g ON a.FSUGGESTSUPPLIERID = g.FSUPPLIERID LEFT JOIN T_BD_UNIT_L h ON a.FUNITID = h.FUNITID LEFT JOIN T_PUR_REQENTRY_R i ON a.FENTRYID = i.FENTRYID LEFT JOIN T_BAS_BILLTYPE_L j ON d.FBILLTYPEID = i.FSRCBILLTYPEID WHERE b.FBILLNO = 'CGSQ000062'-- 采购订单SELECT a.FID,c.FBILLNO 单据编号,d.FNAME 单据类型,c.FDATE 采购日期,e.FNAME 供应商,F.FNUMBER 物料编码, g.FNAME 物料名称,g.FSPECIFICATION 规格型号,h.FNAME 单位,a.FQTY 采购数量,i.FSRCBILLNO 来源单号 FROM T_PUR_POORDERENTRY a LEFT JOIN T_PUR_POORDERENTRY_L b ON a.FID = b.FPKID LEFT JOIN T_PUR_POORDER c ON a.FID = c.FID LEFT JOIN T_BAS_BILLTYPE_L d ON c.FBILLTYPEID = d.FBILLTYPEID LEFT JOIN T_BD_Supplier_L e ON c.FSUPPLIERID = e.FSUPPLIERID LEFT JOIN T_BD_MATERIAL f ON a.FMATERIALID = f.FMATERIALID LEFT JOIN T_BD_MATERIAL_L g ON a.FMATERIALID = g.FMATERIALID LEFT JOIN T_BD_UNIT_L h ON a.FUNITID = h.FUNITID LEFT JOIN T_PUR_POORDERENTRY_R i ON a.FENTRYID = i.FENTRYID WHERE c.FBILLNO = 'CGDD000177'-- 直接调拨单SELECT g.FSTOCKID,a.FID, b.FBILLNO 单据编号,b.FCREATEDATE 日期,b.FTRANSFERDIRECT 调拨方向, c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号,e.FNAME 单位, a.FQTY 调拨数量, a.FSRCSTOCKID 仓库ID,f.FNUMBER 仓库编码,g.FNAME 调出仓库,i.FNUMBER 调出仓位,h.FNAME 调入仓库,j.FNAME 单据类型,k.FSRCBILLNO 来源单号,k.FORDERNO 订单编号 FROM T_STK_STKTRANSFERINENTRY a LEFT JOIN T_STK_STKTRANSFERIN b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID LEFT JOIN T_BD_STOCK f ON a.FSRCSTOCKID = f.FSTOCKID LEFT JOIN T_BD_STOCK_L g ON f.FSTOCKID = g.FSTOCKID LEFT JOIN T_BD_STOCK_L h ON a.FDESTSTOCKID = h.FSTOCKID LEFT JOIN T_BAS_FLEXVALUESENTRY i ON a.FID = i.FID LEFT JOIN T_BAS_BILLTYPE_L j ON b.FBILLTYPEID = j.FBILLTYPEID LEFT JOIN T_STK_STKTRANSFERINENTRY_R k ON a.FENTRYID = k.FENTRYID WHERE b.FBILLNO = 'ZJDB000057'-- 调拨申请单SELECT a.FID, b.FBILLNO 单据编号,c.FNUMBER 物料编码, d.FNAME 物料名称,d.FSPECIFICATION 规格型号, e.FNAME 单位,CONVERT(DECIMAL(18,0),a.FQTY) 数量, f.FNAME 调出仓库,l.FNAME 调出仓位1,m.FNAME 调出仓位2, g.FNAME 调入仓库,j.FNAME 调入仓位,h.FNAME 单据类型 FROM T_STK_STKTRANSFERAPPENTRY a LEFT JOIN T_STK_STKTRANSFERAPP b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID LEFT JOIN T_BD_STOCK_L f ON a.FSTOCKID = f.FSTOCKID LEFT JOIN T_BD_STOCK_L g ON a.FSTOCKINID = g.FSTOCKID LEFT JOIN T_BAS_BILLTYPE_L h ON b.FBILLTYPEID = h.FBILLTYPEID LEFT JOIN T_BAS_FLEXVALUESDETAIL i ON a.FSTOCKLOCINID = i.FID LEFT JOIN T_BAS_FLEXVALUESENTRY_L j ON i.FF100001 = j.FPKID LEFT JOIN T_BAS_FLEXVALUESDETAIL k ON a.FSTOCKLOCID = k.FID LEFT JOIN T_BAS_FLEXVALUESENTRY_L l ON k.FF100046 = l.FPKID LEFT JOIN T_BAS_FLEXVALUESENTRY_L m ON k.FF100047 = m.FPKID WHERE b.FBILLNO = 'DBSQ000018'-- 查询角色SELECT a.FROLEID ID, a.FNUMBER 编码,b.FNAME 名称 FROM T_SEC_ROLE a LEFT JOIN T_SEC_ROLE_L b ON a.FROLEID = b.FROLEID-- 查询用户&用户组SELECT a.FPRIMARYGROUP ID,a.FNAME 用户名,b.FNAME 用户组,a.FPHONE 手机号, a.FAPPGROUP 许可分组,c.FDESCRIPTION 描述 FROM T_SEC_USER a JOIN T_SEC_USERGROUP_L b ON a.FPRIMARYGROUP = b.FID JOIN T_SEC_USER_L c ON a.FUSERID = c.FUSERID where b.FNAME LIKE '%研发%'-- 用户同步日志SELECT * FROM T_SEC_REGUSERSYNCLOG ORDER BY FDATETIME DESC-- 收料通知单 采购收料SELECT b.FID,b.FBILLNO 单据编号,CONVERT(varchar(10),FDATE,126)收料日期,c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号, e.FNAME 单位,CONVERT(DECIMAL(18,0),a.FACTLANDQTY) 实到数量,CONVERT(DECIMAL(18,0),a.FACTRECEIVEQTY) 实收数量,a.FSRCBILLNO 来源单号, F.FNAME 仓库,g.FNAME 供应商 FROM T_PUR_RECEIVEENTRY a LEFT JOIN T_PUR_RECEIVE b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID LEFT JOIN T_BD_STOCK_L f ON a.FSTOCKID = f.FSTOCKID LEFT JOIN T_BD_SUPPLIER_L g ON b.FSUPPLIERID = g.FSUPPLIERID WHERE b.FBILLNO = '1201-2206290006'-- 供应商查询SELECT a.FSUPPLIERID ID,b.FNAME 供应商名称,b.FSHORTNAME 简称,c.FADDRESS 地址,d.FTaxRegisterCode 税务登记号,e.FContact 联系人,e.FTEL 电话,e.FMOBILE 手机 FROM T_BD_SUPPLIER a LEFT JOIN T_BD_SUPPLIER_L b ON a.FSUPPLIERID = b.FSUPPLIERID LEFT JOIN T_BD_SUPPLIERBASE c ON a.FSUPPLIERID = c.FSUPPLIERID LEFT JOIN t_BD_SupplierFinance d ON a.FSUPPLIERID = d.FSUPPLIERID LEFT JOIN t_BD_SupplierContact e ON a.FSUPPLIERID = e.FSUPPLIERID-- 采购价目表SELECT a.FID ID, b.FNUMBER 编码,c.FNAME 名称,g.FNAME 供应商,d.FNUMBER 物料编码,e.FNAME 物料名称,e.FSPECIFICATION 规格,f.FNAME 单位, a.FPRICE 单价,a.FTAXPRICE 含税单价,CONVERT(DECIMAL(18,0),a.FTAXRATE) "税率%", CONVERT(varchar(10),a.FEFFECTIVEDATE,126) 生效日期,CONVERT(varchar(10),a.FEXPIRYDATE,126) 失效日期 FROM T_PUR_PRICELISTENTRY a LEFT JOIN T_PUR_PRICELIST b ON a.FID = b.FID LEFT JOIN T_PUR_PRICELIST_L c ON b.FID = c.FID LEFT JOIN T_BD_MATERIAL d ON a.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_MATERIAL_L e ON a.FMATERIALID = e.FMATERIALID LEFT JOIN T_BD_UNIT_L f ON a.FUNITID = f.FUNITID LEFT JOIN T_BD_SUPPLIER_L g ON b.FSUPPLIERID = g.FSUPPLIERID WHERE b.FNUMBER = 'CGJM000166'-- 生产订单SELECT a.FID,b.FBILLNO 单据编号,CONVERT(varchar(10),b.FDATE,126) 单据日期,f.FNAME 单据类型, c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号,e.FNAME 单位,FQTY 数量, g.FNAME 生产车间,h.FSERIALNO 序列号,a.FPLANSTARTDATE 计划开工时间,a.FPLANFINISHDATE 计划完工时间, a.FMTONO 计划跟踪号 FROM T_PRD_MOENTRY a LEFT JOIN T_PRD_MO b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID LEFT JOIN T_BAS_BILLTYPE_L f ON b.FBILLTYPE = f.FBILLTYPEID LEFT JOIN T_BD_DEPARTMENT_L g ON a.FWORKSHOPID = g.FDEPTID LEFT JOIN T_PRD_MOSNDETAIL h ON a.FENTRYID = h.FENTRYID --序列号 WHERE b.FBILLNO = '3101-2206300010' AND f.FLOCALEID = '2052'-- 单据类型查询SELECT a.FBILLTYPEID,a.FNUMBER 单据编码, b.FNAME 单据名称 FROM T_BAS_BILLTYPE a LEFT JOIN T_BAS_BILLTYPE_L b ON a.FBILLTYPEID = b.FBILLTYPEID WHERE b.FLOCALEID = '2052' AND b.FNAME LIKE '%配件%'-- 用户组织角色查询SELECT distinct a.FUSERID 用户id,a.FNAME 用户名称,d.FNUMBER 组织编码, e.FNAME 组织名称,f.FNUMBER 角色编码,g.FNAME 角色名称 FROM T_SEC_USER a INNER JOIN T_SEC_USERORG b ON b.FUSERID= a.FUSERID LEFT JOIN T_SEC_USERROLEMAP c ON c.FENTITYID = b.FENTITYID LEFT JOIN T_ORG_ORGANIZATIONS d ON d.FORGID=b.FORGID LEFT JOIN T_ORG_ORGANIZATIONS_L e ON d.FORGID=e.FORGID AND e.FLOCALEID=2052 LEFT JOIN T_SEC_ROLE f ON f.FROLEID=c.FROLEID LEFT JOIN T_SEC_ROLE_L g ON f.FROLEID=g.FROLEID WHERE a.FNAME='用户名'-- 员工角色、岗位查询SELECT k.fname 组织名称,a.FUSERID AS 用户内码, a.FNAME 用户名,b.FPERSONID AS 人员内码, c.FNAME AS 人员姓名,i.FID 员工内码,j.FNUMBER 员工编码,i.FNAME 员工名称, d.FSTAFFNUMBER 员工任岗编码,e.FNUMBER 任岗部门编码,f.FNAME AS 任岗部门名称,g.FNUMBER 任岗岗位编码,h.FNAME AS 任岗岗位名称 FROM T_SEC_USER a INNER JOIN T_BD_PERSON b ON a.FLINKOBJECT = b.FPERSONID LEFT JOIN T_BD_PERSON_L c ON b.FPERSONID=c.FPERSONID AND c.FLOCALEID=2052 INNER JOIN T_BD_STAFF d ON b.FPERSONID=d.FPERSONID LEFT JOIN T_BD_DEPARTMENT e ON d.FDEPTID=e.FDEPTID LEFT JOIN T_BD_DEPARTMENT_L f ON d.FDEPTID=f.FDEPTID AND f.FLOCALEID=2052 LEFT JOIN T_ORG_POST g ON d.FPOSTID=g.FPOSTID LEFT JOIN T_ORG_POST_L h ON g.FPOSTID=h.FPOSTID AND h.FLOCALEID=2052 LEFT JOIN T_HR_EMPINFO_L i ON i.FID=d.FEMPINFOID AND f.FLOCALEID=2052 LEFT JOIN T_HR_EMPINFO j ON j.FID=i.FID LEFT JOIN T_ORG_ORGANIZATIONS_L k ON k.forgid=j.FUSEORGID AND k.FLOCALEID=2052 WHERE a.FNAME='用户名'-- 计划订单 主产品SELECT a.FID, a.FBILLNO 单据号,d.FNAME 单据类型,a.FMATERIALID 物料代码,b.FNUMBER 物料编码,c.FNAME 物料名称,c.FSPECIFICATION 规格型号, e.FNAME 单位,g.FNAME 投放单据类型, FFIRMQTY 确认订单量,a.FCOMPUTERNO 运算编号,h.FSALEORDERNO 来源单号,h.FSALEORDERENTRYSEQ 来源单据行号 FROM T_PLN_PLANORDER a JOIN T_BD_MATERIAL b ON a.FMATERIALID = b.FMATERIALID JOIN T_BD_MATERIAL_L c ON b.FMATERIALID = c.FMATERIALID JOIN T_BAS_BILLTYPE_L d ON a.FBILLTYPEID = d.FBILLTYPEID JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID JOIN T_PLN_PLANORDER_A f ON a.FID = f.FID JOIN T_BAS_BILLTYPE_L g ON f.FRELEASEBILLTYPE = g.FBILLTYPEID JOIN T_PLN_PLANORDER_B h ON a.FID = h.FID WHERE a.FBILLNO = 'MRP00066201-2'-- 计划订单 计划BOMSELECT a.FID, a.FBILLNO 单据号,b.FMATERIALID 子项物料代码,c.FNUMBER 子项物料编码, d.FNAME 物料名称,d.FSPECIFICATION 规格型号,e.FNAME 单位, CONVERT(DECIMAL(18,0),b.FNUMERATOR) 分子,CONVERT(DECIMAL(18,0),b.FDENOMINATOR) 分母, b.FSTDQTY 标准用量,b.FNEEDQTY 计划投料数量,b.FMUSTQTY 应发数量 FROM T_PLN_PLANORDER a JOIN T_PLN_PLBOMENTRY b ON a.FID = b.FID JOIN T_BD_MATERIAL c ON b.FMATERIALID = c.FMATERIALID JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID JOIN T_BD_UNIT_L e ON b.FUNITID = e.FUNITID WHERE a.FBILLNO = 'MRP00066201-2'-- 预测单SELECT a.FID,b.FBILLNO 单据编号,c.FNAME 单据类型,b.FDATE 日期,d.FNUMBER 物料编码,e.FNAME 物料名称, e.FSPECIFICATION 规格型号,f.FNAME 单位,a.FBASEQTY 数量,g.FNUMBER BOM版本, CONVERT(VARCHAR(10),a.FSTARTDATE,126) 预测开始日期,CONVERT(VARCHAR(10),a.FENDDATE,126) 预测结束日期 FROM T_PLN_FORECASTENTRY a JOIN T_PLN_FORECAST b ON a.FID = b.FID JOIN T_BAS_BILLTYPE_L c ON b.FBILLTYPEID = c.FBILLTYPEID JOIN T_BD_MATERIAL d ON a.FMATERIALID = d.FMATERIALID JOIN T_BD_MATERIAL_L e ON d.FMATERIALID = e.FMATERIALID JOIN T_BD_UNIT_L f ON a.FUNITID = f.FUNITID LEFT JOIN T_ENG_BOM g ON a.FBOMID = g.FID WHERE b.FBILLNO = 'FO00000088 '-- 生产制造 物料清单SELECT a.FID,a.FNUMBER BOM版本,a.FMATERIALID 物料内码,b.FNUMBER 父项物料编码,c.FNAME 物料名称, c.FSPECIFICATION 规格型号,d.FNAME 单位,e.FMATERIALID 子项物料内码,f.FNUMBER 子项物料编码, g.FNAME 子项物料名称,g.FSPECIFICATION 子项规格型号,h.FNAME 单位,e.FBASENUMERATOR 用量分子,e.FBASEDENOMINATOR 用量分母 FROM T_ENG_BOM a JOIN T_BD_MATERIAL b ON a.FMATERIALID = b.FMATERIALID JOIN T_BD_MATERIAL_L c ON b.FMASTERID = c.FMATERIALID JOIN T_BD_UNIT_L d ON a.FUNITID = d.FUNITID JOIN T_ENG_BOMCHILD e ON a.FID = e.FID JOIN T_BD_MATERIAL f ON e.FMATERIALID = f.FMATERIALID JOIN T_BD_MATERIAL_L g ON f.FMATERIALID = g.FMATERIALID JOIN T_BD_UNIT_L h ON e.FUNITID = h.FUNITID WHERE a.FNUMBER = '2104171100_V1.0'-- 生产用料清单SELECT a.FID,b.FBILLNO 单据编号,c.FNUMBER 子项物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号, e.FNAME 单位,a.FMOBILLNO 生产订单编号,a.FBASENUMERATOR 分子,a.FBASEDENOMINATOR 分母, a.FMUSTQTY 应发数量,f.FPICKEDQTY 已领数量 FROM T_PRD_PPBOMENTRY a JOIN T_PRD_PPBOM b ON a.FID = b.FID JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID JOIN T_PRD_PPBOMENTRY_Q f ON a.FENTRYID = f.FENTRYID --JOIN T_PRD_MOENTRY e ON a.FMOID = e.FID --JOIN T_BD_MATERIAL f ON e.FMATERIALID = f.FMATERIALID WHERE b.FBILLNO = 'PPBOM00001350'-- 上机操作日志SELECT a.FUSERID,b.FNAME 用户名,a.FDATETIME 操作时间,c.FNAME 当前组织, d.FNAME 子系统,e.FNAME 业务对象,a.FOPERATENAME 操作名称,a.FDESCRIPTION 描述, a.FCOMPUTERNAME 客户端,a.FCLIENTIP 客户端IP,a.FGATEWAYIP 网关 FROM T_BAS_OPERATELOG a LEFT JOIN T_SEC_USER b ON a.FUSERID = b.FUSERID LEFT JOIN T_ORG_ORGANIZATIONS_L c ON a.FLOGONORGID = c.FORGID LEFT JOIN T_META_SUBSYSTEM_L d ON a.FSUBSYSTEMID = d.FID LEFT JOIN T_META_OBJECTTYPE_L e ON a.FOBJECTTYPEID = e.FID LEFT JOIN T_MDL_FORMOPERATIONTYPE_L f ON a.FENVIRONMENT = f.FID WHERE c.FLOCALEID = 2052 AND b.FNAME = '用户名' AND a.FDATETIME BETWEEN '2022-07-01' AND '2022-07-06' ORDER BY a.FDATETIME DESC-- 上机操作日志 备份表SELECT a.FUSERID,b.FNAME 用户名,a.FDATETIME 操作时间,c.FNAME 当前组织, d.FNAME 子系统,e.FNAME 业务对象,a.FOPERATENAME 操作名称,a.FDESCRIPTION 描述, a.FCOMPUTERNAME 客户端,a.FCLIENTIP 客户端IP,a.FGATEWAYIP 网关 FROM T_BAS_OPERATELOGBK a LEFT JOIN T_SEC_USER b ON a.FUSERID = b.FUSERID LEFT JOIN T_ORG_ORGANIZATIONS_L c ON a.FLOGONORGID = c.FORGID LEFT JOIN T_META_SUBSYSTEM_L d ON a.FSUBSYSTEMID = d.FID LEFT JOIN T_META_OBJECTTYPE_L e ON a.FOBJECTTYPEID = e.FID LEFT JOIN T_MDL_FORMOPERATIONTYPE_L f ON a.FENVIRONMENT = f.FID WHERE c.FLOCALEID = 2052 --AND b.FNAME = '用户名' AND a.FDATETIME BETWEEN '2022-06-20' AND '2022-07-01' ORDER BY a.FDATETIME DESC-- 仓库信息查询SELECT a.FSTOCKID,a.FNUMBER 仓库代码,b.FNAME 仓库名称,a.FADDRESS 地址 FROM T_BD_STOCK a JOIN T_BD_STOCK_L b ON a.FSTOCKID = b.FSTOCKID WHERE b.FNAME LIKE '%成品%'-- 供应商对账单SELECT b.FID,b.FBILLNO 单据号,CONVERT(VARCHAR(10),b.FDATE,126) 单据日期,c.FNAME 供应商,d.FNUMBER 物料编码, e.FNAME 名称,e.FSPECIFICATION 规格,f.FNAME 采购单位,CONVERT(DECIMAL(18,2),a.FQTY) 合同数量, CONVERT(VARCHAR(10),a.FDELIVERYDATE,126) 交货日期,a.FGIVEAWAY "是否赠品:0否 1是",g.FPRICE 单价, g.FAMOUNT 金额,CONVERT(DECIMAL(18,0),g.FTAXRATE) "税率%",g.FTAXAMOUNT 税额,g.FTAXPRICE 含税单价, g.FALLAMOUNT 含税金额,h.FSRCBILLNO 来源单号,h.FSRCBILLTYPEID 来源单据类型 FROM T_PUR_CONTRACTENTRY a -- 采购合同明细表 JOIN T_PUR_CONTRACT b ON a.FID = b.FID --采购合同表 JOIN T_BD_SUPPLIER_L c ON b.FSUPPLIERID = c.FSUPPLIERID --供应商 JOIN T_BD_MATERIAL d ON a.FMATERIALID = d.FMATERIALID --物料 JOIN T_BD_MATERIAL_L e ON d.FMATERIALID = e.FMATERIALID JOIN T_BD_UNIT_L f ON a.FUNITID = f.FUNITID --单位 JOIN T_PUR_CONTRACTENTRY_F g ON a.FENTRYID = g.FENTRYID JOIN T_PUR_CONTRACTENTRY_R h ON a.FENTRYID = h.FENTRYID WHERE b.FBILLNO = 'CGHT000009'-- 获取当前数据库最大的十张表SELECT TOP 10 NAME 表名,sum(RowCnt) 表行数,sum(ReservedKb) "表大小(Kb)", sum(TableUsedKb) "数据大小(Kb)",sum(UsedKb-TableUsedKb) "索引大小(Kb)",sum(TMPTABLESNum) 临时表NUM FROM(SELECT OBJECT_ID,SCHEMA_ID, CASE WHEN NAME LIKE 'TMP%' THEN 'TMPTABLES' ELSE NAME END NAME, CASE WHEN NAME LIKE 'TMP%' THEN 1 ELSE 0 END TMPTABLESNum, (SELECT MAX(row_count) FROM sys.dm_db_partition_stats p WITH(nolock) WHERE p.object_id=t.object_id and p.index_id < 2) AS RowCnt, (SELECT COUNT(1) FROM dbo.syscolumns WITH(nolock) WHERE id = t.object_id) Columns, (SELECT COUNT(DISTINCT index_id) FROM sys.dm_db_partition_stats p WITH(nolock) WHERE p.object_id=t.object_id) AS Indexes, (SELECT SUM(length) FROM dbo.syscolumns WITH(nolock) WHERE id = t.object_id) AS RowLength, ISNULL((SELECT SUM(reserved_page_count) FROM sys.dm_db_partition_stats p WITH(nolock) WHERE p.object_id=t.object_id),0)*8 + ISNULL((SELECT SUM(reserved_page_count) FROM sys.dm_db_partition_stats p2 WITH(nolock) INNER JOIN sys.internal_tables i WITH(nolock) ON p2.object_id = i.object_id WHERE i.parent_id = t.object_id AND i.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)),0)* 8 AS ReservedKb, ISNULL((SELECT SUM(in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) from sys.dm_db_partition_stats p with(nolock) where p.object_id=t.object_id and p.index_id < 2),0)* 8 AS TableUsedKb, ISNULL((SELECT SUM(used_page_count) FROM sys.dm_db_partition_stats p WITH(nolock) WHERE p.object_id=t.object_id),0)*8 + ISNULL((SELECT SUM(used_page_count) FROM sys.dm_db_partition_stats p2 WITH(nolock) INNER JOIN sys.internal_tables i WITH(nolock) on p2.object_id = i.object_id WHERE i.parent_id = t.object_id AND i.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)),0)* 8 AS UsedKb,create_date,modify_date FROM sys.tables t WITH(nolock) WHERE TYPE='U') A GROUP BY name ORDER BY 3 DESC-- Z表创建【Z表简介】金蝶云星空中业务对象内码获取的方式有如下两种：1、整数型的内码，绝大部分是通过一个包含自增列（列名统一为id）的z_开头的表来实现的，如科目多语言表t_bd_account_l对应的z表为z_bd_account_l;当然也有例外如T_BD_MATERIALGROUPLEVEL及其他分组级次表的主键是直接利用自增列来生成内码的。2、字符型的内码，通过newid()生成唯一的guid作为内码，比如报表T_KDS_RPT或者辅助资料类别T_BAS_ASSISTANTDATA。【Z表生成时机】部分表创建数据中心的时候已经生成，但是，也有一部分的表要等到第一次使用的时候系统才生成，如初始核算数据明细表Z_HS_INITCALCULATEENTRY空白账套是没有这个表的【如何生成Z表】如果不是通过数据库直接操作，是不需要担心Z表不存在的，因为不存在时系统会自动生成。当系统中不存在Z表又需要通过数据库直接获取Z表的内码的时候，才需要自己创建。比如我们的【金蝶云星空专项服务工具】的数据结转（https://kdcsrv.ik3cloud.com/）工具，自动生成初始核算数据，如果操作的数据中心没有做过核算数据录入，那么我们就需要自己创建Z_HS_INITCALCULATEENTRY了。以下是创建Z表的脚本，只需要替换@TableName参数即可执行，并且只有在Z表不存在时创建。DECLARE @sql nvarchar(max) DECLARE @TableName nvarchar(100) SET @TableName='z_bd_account_L' SET @sql='IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name='''+@TableName+''') CREATE TABLE [dbo].['+@TableName+'] ([Id] [bigint] IDENTITY(100001,1) NOT NULL, [Column1] [int] NOT NULL, CONSTRAINT [P'+@TableName+'] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]' EXEC (@sql)-- 查看数据库服务器硬盘空间WITH T1 AS( SELECT DISTINCT REPLACE(vs.volume_mount_point,':\','') AS 盘符 , CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS 总空间 , CAST(vs.available_bytes/ 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS 剩余空间 FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs ) SELECT 盘符, 总空间, 总空间-剩余空间 AS 已使用空间, 剩余空间,CAST(剩余空间*100/总空间 AS NUMERIC(18,2)) AS 空闲率 FROM T1-- 数据库优化 1--第一步重建索引DECLARE @sName VARCHAR( 200) DECLARE @SqlStr VARCHAR(2000) DECLARE Opo_Cursor CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'U' AND ( name NOT LIKE 'TMP%' ) ; OPEN Opo_Cursor FETCH NEXT FROM Opo_Cursor INTO @sName WHILE @@FETCH_STATUS = 0 BEGIN TRY SET @SqlStr = ''; SELECT @SqlStr =@SqlStr + 'alter index all on [' + @sName + '] rebuild;' EXEC ( @SqlStr ); FETCH NEXT FROM Opo_Cursor INTO @sName END TRY BEGIN CATCH FETCH NEXT FROM Opo_Cursor INTO @sName END CATCH CLOSE Opo_Cursor DEALLOCATE Opo_Cursor--第二步更新统计信息DECLARE @sName1 VARCHAR( 200) DECLARE @SqlStr1 VARCHAR(2000) DECLARE Opo_Cursor1 CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'U' AND ( name NOT LIKE 'TMP%' ) ; OPEN Opo_Cursor1 FETCH NEXT FROM Opo_Cursor1 INTO @sName1 WHILE @@FETCH_STATUS = 0 BEGIN TRY SET @SqlStr1 = ''; SELECT @SqlStr1 =@SqlStr1 + 'UPDATE STATISTICS ['+ @sName1+'];' EXEC ( @SqlStr1 ); FETCH NEXT FROM Opo_Cursor1 INTO @sName1 END TRY BEGIN CATCH FETCH NEXT FROM Opo_Cursor1 INTO @sName1 END CATCH CLOSE Opo_Cursor1 DEALLOCATE Opo_Cursor1--第三步压缩索引DECLARE @sName2 VARCHAR( 200) DECLARE @SqlStr2 VARCHAR(2000) DECLARE Opo_Cursor2 CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'U' AND ( name NOT LIKE 'TMP%' ) ; OPEN Opo_Cursor2 FETCH NEXT FROM Opo_Cursor2 INTO @sName2 WHILE @@FETCH_STATUS = 0 BEGIN TRY SET @SqlStr2 = ''; SELECT @SqlStr2 =@SqlStr1 + 'alter index all on ['+@sName2+ '] rebuild WITH (DATA_COMPRESSION=ROW);' EXEC ( @SqlStr2 ); FETCH NEXT FROM Opo_Cursor2 INTO @sName2 END TRY BEGIN CATCH FETCH NEXT FROM Opo_Cursor2 INTO @sName2 END CATCH CLOSE Opo_Cursor2 DEALLOCATE Opo_Cursor2--第四步压缩数据表DECLARE @sName3 VARCHAR( 200) DECLARE @SqlStr3 VARCHAR(2000) DECLARE Opo_Cursor3 CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'U' AND ( name NOT LIKE 'TMP%' ) ; OPEN Opo_Cursor3 FETCH NEXT FROM Opo_Cursor3 INTO @sName3 WHILE @@FETCH_STATUS = 0 BEGIN TRY SET @SqlStr3 = ''; SELECT @SqlStr3 =@SqlStr1 + 'alter index all on ['+@sName3+ '] rebuild WITH (DATA_COMPRESSION=ROW);' EXEC ( @SqlStr3 ); FETCH NEXT FROM Opo_Cursor3 INTO @sName3 END TRY BEGIN CATCH FETCH NEXT FROM Opo_Cursor3 INTO @sName3 END CATCH CLOSE Opo_Cursor3 DEALLOCATE Opo_Cursor3-- 数据库优化 2/*可一次全部执行，不必分4次。4段语句均为执行全库扫描与优化，故耗时会很久。执行期间将会拖慢金蝶系统性能，强烈要求必须放在系统空闲时执行。作者：深圳周少斌*/SET NOCOUNT ON --关闭统计以提升整体执行效率 declare @StepStartTime datetime,@GlobalStartTime datetime --时长统计公共变量：步进开始时间，全局开始时间。 select @StepStartTime=getdate(),@GlobalStartTime=getdate() declare @TableName TABLE(name nvarchar(100) NOT NULL,UNIQUE CLUSTERED(name)) --声明表变量：待优化的数据库物理表名清单。 declare @TableNameForLoop TABLE(name nvarchar(100) NOT NULL,UNIQUE CLUSTERED(name)) --声明表变量：待优化的数据库物理表名清单_用于循环。 declare @name nvarchar(100),@SqlStr nvarchar(300) --声明变量：待优化物理表名，优化语句。 Insert into @TableName SELECT name from sysobjects where xtype='U' and (name not like'TMP%') --初始化表变量：待优化的数据库物理表名清单--第1步.重建索引，整理碎片Insert into @TableNameForLoop SELECT * from @TableName --初始化表变量：用于循环待优化的数据库物理表名清单_用于循环 WHILE EXISTS(SELECT top 100 percent name FROM @TableNameForLoop order by Name) begin select top 1 @name=name,@SqlStr='' FROM @TableNameForLoop order by Name --取值@TableNameForLoop的第一行 delete from @TableNameForLoop WHERE name=@name;--删除上一行语句已取值的行。 set @SqlStr='alter index all on ['+@name+'] rebuild;' --拼接优化语句与待优化物理表名 BEGIN TRY exec (@SqlStr) END TRY BEGIN CATCH END CATCH --执行优化语句，如果有异常就直接吞掉，继续循环。 end print '第1步执行时长:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@StepStartTime,getdate())))/60000,2)))+'分钟'; --统计单步执行时长 set @StepStartTime=getdate();--第2步.更新统计信息，以利于SQL Server后台优化执行计划Insert into @TableNameForLoop SELECT * from @TableName --重新初始化表变量：用于循环待优化的数据库物理表名清单_用于循环 WHILE EXISTS(SELECT top 100 percent name FROM @TableNameForLoop order by Name) begin select top 1 @name=name,@SqlStr='' FROM @TableNameForLoop order by Name --取值@TableNameForLoop的第一行 delete from @TableNameForLoop WHERE name=@name;--删除上一行语句已取值的行。 set @SqlStr='UPDATE STATISTICS ['+@name+'];' --拼接优化语句与待优化物理表名 BEGIN TRY exec (@SqlStr) END TRY BEGIN CATCH END CATCH --执行优化语句，如果有异常就直接吞掉，继续循环。 end print '第2步执行时长:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@StepStartTime,getdate())))/60000,2)))+'分钟'; --统计单步执行时长 set @StepStartTime=getdate();--第3步.压缩索引以提升I/O性能（注意，这里并不是收缩数据库，而是压缩索引）Insert into @TableNameForLoop SELECT * from @TableName --重新初始化表变量：用于循环待优化的数据库物理表名清单_用于循环 WHILE EXISTS(SELECT top 100 percent name FROM @TableNameForLoop order by Name) begin select top 1 @name=name,@SqlStr='' FROM @TableNameForLoop order by Name --取值@TableNameForLoop的第一行 delete from @TableNameForLoop WHERE Name=@name;--删除上一行语句已取值的行。 set @SqlStr='alter index all on ['+@name+ '] rebuild WITH (DATA_COMPRESSION=ROW);' --拼接优化语句与待优化物理表名 BEGIN TRY exec (@SqlStr) END TRY BEGIN CATCH END CATCH --执行优化语句，如果有异常就直接吞掉，继续循环。 end print '第3步执行时长:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@StepStartTime,getdate())))/60000,2)))+'分钟'; --统计单步执行时长 set @StepStartTime=getdate();--第4步.压缩数据表以提升I/O性能（注意，这里并不是收缩数据库，而是压缩物理表)Insert into @TableNameForLoop SELECT * from @TableName --重新初始化表变量：用于循环待优化的数据库物理表名清单_用于循环 WHILE EXISTS(SELECT top 100 percent name FROM @TableNameForLoop order by Name) begin select top 1 @name=name,@SqlStr='' FROM @TableNameForLoop order by Name --取值@TableNameForLoop的第一行 delete from @TableNameForLoop WHERE Name=@name;--删除上一行语句已取值的行。 set @SqlStr='alter table ['+@name+'] rebuild WITH (DATA_COMPRESSION=ROW);' --拼接优化语句与待优化物理表名 BEGIN TRY exec (@SqlStr) END TRY BEGIN CATCH END CATCH --执行优化语句，如果有异常就直接吞掉，继续循环。 end print '第4步执行时长:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@StepStartTime,getdate())))/60000,2)))+'分钟'; --统计单步执行时长--*最后，一定记录本次执行以上四步优化的全程总耗时，做为后续设置SQL Server定时作业的重要参考。print '全程总耗时:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@GlobalStartTime,getdate())))/60000,2)))+'分钟。请记录此总耗时，做为设置定时作业的依据';SET NOCOUNT OFF --恢复统计/*特别说明：第1步、第2步，推荐每天、每周执行；第3步、第4步，推荐每月、每季执行，不推荐高频执行*/-- 查询数据库版本SELECT @@VERSION;-- 查询BOS版本 （基于管理中心库 K3DBConfiger）SELECT a.FDATACENTERID AS 数据中心内码,b.FNAME AS 数据中心名称,a.* FROM T_BAS_DATACENTER a LEFT JOIN T_BAS_DATACENTER_L b ON a.FDATACENTERID=b.FDATACENTERID and b.FLOCALEID=2052 SELECT b.FNAME AS [数据中心(账套名称)],a.FDATACENTERID AS [数据中心内码(DBID)],a.FVISION AS 产品版本号,a.* FROM T_BAS_DATACENTER a LEFT JOIN T_BAS_DATACENTER_L b ON a.FDATACENTERID=b.FDATACENTERID AND b.FLOCALEID=2052-- 查询BOS版本（基于业务数据库）SELECT * FROM T_BOS_INSTALLEDPACKAGE WHERE FPKGTYPE='DataModel' AND FPKGID='K3Cloud_DC_VERSION'此处不加PKKGID可查询其他二开补丁包的打包版本记录，此处记录的是版本号非产品版本而是构件号，需要自行匹配对应的产品版本号： eg :平台最新的2022年6月补丁 PT-146915 [8.0.0.202206] 对应构建号：8.0.277.10-- 查询视图依赖关系：EXEC SP_DEPENDS 视图名称-- 采购入库单SELECT a.FID,a.FENTRYID,b.FBILLNO 单据号,CONVERT(VARCHAR(10),b.FDATE,126) 入库日期,f.FNAME 供应商, c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格,e.FNAME 单位,a.FREALQTY 实收数量, g.FNAME 仓库,i.FNAME 仓位,j.FPRICE 单价,CONVERT(DECIMAL(18,2),j.FAMOUNT) 金额, CONVERT(DECIMAL(18,0),j.FTAXRATE) "税率%",CONVERT(DECIMAL(18,2),j.FTAXAMOUNT) 税额, CONVERT(DECIMAL(18,2),J.FTAXPRICE) 含税单价,CONVERT(DECIMAL(18,2),j.FALLAMOUNT) 含税金额, a.FSRCBILLNO 来源单号,a.FSRCBILLTYPEID 来源单据 FROM T_STK_INSTOCKENTRY a JOIN T_STK_INSTOCK b ON a.FID = b.FID JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID JOIN T_BD_SUPPLIER_L f ON b.FSUPPLIERID = f.FSUPPLIERID JOIN T_BD_STOCK_L g ON a.FSTOCKID = g.FSTOCKID JOIN T_BAS_FLEXVALUESDETAIL h ON a.FSTOCKLOCID = h.FID JOIN T_BAS_FLEXVALUESENTRY_L i ON h.FF100004 = i.FPKID JOIN T_STK_INSTOCKENTRY_F j ON a.FENTRYID = j.FENTRYID -- JOIN T_STK_INSTOCKSERIAL k ON a.FENTRYID = k.FENTRYID --序列号信息 WHERE b.FBILLNO = 'CGRK00306'
-
Kingdee-常用SQL查询2 -- 用户组织角色查询SELECT distinct a.FUSERID 用户id,a.FNAME 用户名称,d.FNUMBER 组织编码, e.FNAME 组织名称,f.FNUMBER 角色编码,g.FNAME 角色名称 FROM T_SEC_USER a INNER JOIN T_SEC_USERORG b ON b.FUSERID= a.FUSERID LEFT JOIN T_SEC_USERROLEMAP c ON c.FENTITYID = b.FENTITYID LEFT JOIN T_ORG_ORGANIZATIONS d ON d.FORGID=b.FORGID LEFT JOIN T_ORG_ORGANIZATIONS_L e ON d.FORGID=e.FORGID AND e.FLOCALEID=2052 LEFT JOIN T_SEC_ROLE f ON f.FROLEID=c.FROLEID LEFT JOIN T_SEC_ROLE_L g ON f.FROLEID=g.FROLEID WHERE a.FNAME='用户名'-- 员工角色、岗位查询SELECT k.fname 组织名称,a.FUSERID AS 用户内码, a.FNAME 用户名,b.FPERSONID AS 人员内码, c.FNAME AS 人员姓名,i.FID 员工内码,j.FNUMBER 员工编码,i.FNAME 员工名称, d.FSTAFFNUMBER 员工任岗编码,e.FNUMBER 任岗部门编码,f.FNAME AS 任岗部门名称,g.FNUMBER 任岗岗位编码,h.FNAME AS 任岗岗位名称 FROM T_SEC_USER a INNER JOIN T_BD_PERSON b ON a.FLINKOBJECT = b.FPERSONID LEFT JOIN T_BD_PERSON_L c ON b.FPERSONID=c.FPERSONID AND c.FLOCALEID=2052 INNER JOIN T_BD_STAFF d ON b.FPERSONID=d.FPERSONID LEFT JOIN T_BD_DEPARTMENT e ON d.FDEPTID=e.FDEPTID LEFT JOIN T_BD_DEPARTMENT_L f ON d.FDEPTID=f.FDEPTID AND f.FLOCALEID=2052 LEFT JOIN T_ORG_POST g ON d.FPOSTID=g.FPOSTID LEFT JOIN T_ORG_POST_L h ON g.FPOSTID=h.FPOSTID AND h.FLOCALEID=2052 LEFT JOIN T_HR_EMPINFO_L i ON i.FID=d.FEMPINFOID AND f.FLOCALEID=2052 LEFT JOIN T_HR_EMPINFO j ON j.FID=i.FID LEFT JOIN T_ORG_ORGANIZATIONS_L k ON k.forgid=j.FUSEORGID AND k.FLOCALEID=2052 WHERE a.FNAME='用户名'--计划订单 主产品SELECT a.FID, a.FBILLNO 单据号,d.FNAME 单据类型,a.FMATERIALID 物料代码,b.FNUMBER 物料编码,c.FNAME 物料名称,c.FSPECIFICATION 规格型号, e.FNAME 单位,g.FNAME 投放单据类型, FFIRMQTY 确认订单量,a.FCOMPUTERNO 运算编号,h.FSALEORDERNO 来源单号,h.FSALEORDERENTRYSEQ 来源单据行号 FROM T_PLN_PLANORDER a JOIN T_BD_MATERIAL b ON a.FMATERIALID = b.FMATERIALID JOIN T_BD_MATERIAL_L c ON b.FMATERIALID = c.FMATERIALID JOIN T_BAS_BILLTYPE_L d ON a.FBILLTYPEID = d.FBILLTYPEID JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID JOIN T_PLN_PLANORDER_A f ON a.FID = f.FID JOIN T_BAS_BILLTYPE_L g ON f.FRELEASEBILLTYPE = g.FBILLTYPEID JOIN T_PLN_PLANORDER_B h ON a.FID = h.FID WHERE a.FBILLNO = 'MRP00066201-2'--计划订单 计划BOMSELECT a.FID, a.FBILLNO 单据号,b.FMATERIALID 子项物料代码,c.FNUMBER 子项物料编码, d.FNAME 物料名称,d.FSPECIFICATION 规格型号,e.FNAME 单位, CONVERT(DECIMAL(18,0),b.FNUMERATOR) 分子,CONVERT(DECIMAL(18,0),b.FDENOMINATOR) 分母, b.FSTDQTY 标准用量,b.FNEEDQTY 计划投料数量,b.FMUSTQTY 应发数量 FROM T_PLN_PLANORDER a JOIN T_PLN_PLBOMENTRY b ON a.FID = b.FID JOIN T_BD_MATERIAL c ON b.FMATERIALID = c.FMATERIALID JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID JOIN T_BD_UNIT_L e ON b.FUNITID = e.FUNITID WHERE a.FBILLNO = 'MRP00066201-2'--预测单SELECT a.FID,b.FBILLNO 单据编号,c.FNAME 单据类型,b.FDATE 日期,d.FNUMBER 物料编码,e.FNAME 物料名称, e.FSPECIFICATION 规格型号,f.FNAME 单位,a.FBASEQTY 数量,g.FNUMBER BOM版本, CONVERT(VARCHAR(10),a.FSTARTDATE,126) 预测开始日期,CONVERT(VARCHAR(10),a.FENDDATE,126) 预测结束日期 FROM T_PLN_FORECASTENTRY a JOIN T_PLN_FORECAST b ON a.FID = b.FID JOIN T_BAS_BILLTYPE_L c ON b.FBILLTYPEID = c.FBILLTYPEID JOIN T_BD_MATERIAL d ON a.FMATERIALID = d.FMATERIALID JOIN T_BD_MATERIAL_L e ON d.FMATERIALID = e.FMATERIALID JOIN T_BD_UNIT_L f ON a.FUNITID = f.FUNITID LEFT JOIN T_ENG_BOM g ON a.FBOMID = g.FID WHERE b.FBILLNO = 'FO00000088 '--生产制造 物料清单SELECT a.FID,a.FNUMBER BOM版本,a.FMATERIALID 物料内码,b.FNUMBER 父项物料编码,c.FNAME 物料名称, c.FSPECIFICATION 规格型号,d.FNAME 单位,e.FMATERIALID 子项物料内码,f.FNUMBER 子项物料编码, g.FNAME 子项物料名称,g.FSPECIFICATION 子项规格型号,h.FNAME 单位,e.FBASENUMERATOR 用量分子,e.FBASEDENOMINATOR 用量分母 FROM T_ENG_BOM a JOIN T_BD_MATERIAL b ON a.FMATERIALID = b.FMATERIALID JOIN T_BD_MATERIAL_L c ON b.FMASTERID = c.FMATERIALID JOIN T_BD_UNIT_L d ON a.FUNITID = d.FUNITID JOIN T_ENG_BOMCHILD e ON a.FID = e.FID JOIN T_BD_MATERIAL f ON e.FMATERIALID = f.FMATERIALID JOIN T_BD_MATERIAL_L g ON f.FMATERIALID = g.FMATERIALID JOIN T_BD_UNIT_L h ON e.FUNITID = h.FUNITID WHERE a.FNUMBER = '2104171100_V1.0'--生产用料清单SELECT a.FID,b.FBILLNO 单据编号,c.FNUMBER 子项物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号, e.FNAME 单位,a.FMOBILLNO 生产订单编号,a.FBASENUMERATOR 分子,a.FBASEDENOMINATOR 分母, a.FMUSTQTY 应发数量,f.FPICKEDQTY 已领数量 FROM T_PRD_PPBOMENTRY a JOIN T_PRD_PPBOM b ON a.FID = b.FID JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID JOIN T_PRD_PPBOMENTRY_Q f ON a.FENTRYID = f.FENTRYID --JOIN T_PRD_MOENTRY e ON a.FMOID = e.FID --JOIN T_BD_MATERIAL f ON e.FMATERIALID = f.FMATERIALID WHERE b.FBILLNO = 'PPBOM00001350'-- 上机操作日志SELECT a.FUSERID,b.FNAME 用户名,a.FDATETIME 操作时间,c.FNAME 当前组织, d.FNAME 子系统,e.FNAME 业务对象,a.FOPERATENAME 操作名称,a.FDESCRIPTION 描述, a.FCOMPUTERNAME 客户端,a.FCLIENTIP 客户端IP,a.FGATEWAYIP 网关 FROM T_BAS_OPERATELOG a LEFT JOIN T_SEC_USER b ON a.FUSERID = b.FUSERID LEFT JOIN T_ORG_ORGANIZATIONS_L c ON a.FLOGONORGID = c.FORGID LEFT JOIN T_META_SUBSYSTEM_L d ON a.FSUBSYSTEMID = d.FID LEFT JOIN T_META_OBJECTTYPE_L e ON a.FOBJECTTYPEID = e.FID LEFT JOIN T_MDL_FORMOPERATIONTYPE_L f ON a.FENVIRONMENT = f.FID WHERE c.FLOCALEID = 2052 AND b.FNAME = '用户名' AND a.FDATETIME BETWEEN '2022-07-01' AND '2022-07-06' ORDER BY a.FDATETIME DESC
-
Kingdee-常用SQL查询1 ---- 表单查询SELECT b.FNAME,a.* FROM T_META_OBJECTTYPE a LEFT JOIN T_META_OBJECTTYPE_L b ON a.FID=b.FID AND b.FLOCALEID=2052 WHERE a.FMODELTYPEID=100 AND a.FDEVTYPE IN (0,1) AND b.FNAME ='采购订单'-- 获取销售出库单的单据视图内码SELECT c.FNAME AS 业务对象名称,a.FDEPENDENCYOBJECTID AS [业务对象内码(FormId)], b.FNAME AS 视图名称,a.FID AS [视图内码(layoutid)],* FROM T_META_OBJECTTYPEVIEW a LEFT JOIN T_META_OBJECTTYPEVIEW_L b ON a.FID=b.FID AND b.FLOCALEID=2052 LEFT JOIN T_META_OBJECTTYPE_L c ON a.FDEPENDENCYOBJECTID=c.FID AND c.FLOCALEID=2052 WHERE a.FDEPENDENCYOBJECTID IN ('SAL_OUTSTOCK','6bd6de25-2d2f-4c9e-aa95-166db3e9f198')-- 00 功能查询SELECT L.FNAME,H.FNAME,B.FNAME,E.FNAME FROM T_META_CONSOLESUBFUNC A JOIN T_META_CONSOLESUBFUNC_l B ON A.FSUBFUNCID = B.FSUBFUNCID AND B.FLOCALEID = 2052 JOIN(SELECT C.FSUBFUNCID, C.FOBJECTID, D.FNAME FROM T_META_CONSOLEDETAIL C JOIN T_META_CONSOLEDETAIL_l D ON C.FDETAILFUNCID = D.FDETAILFUNCID AND D.FLOCALEID = 2052)E ON A.FSUBFUNCID = E.FSUBFUNCID JOIN(SELECT F.FID, G.FNAME, F.FTOPCLASSID FROM T_META_SUBSYSTEM F JOIN T_META_SUBSYSTEM_l G ON F.FID = G.FID AND G.FLOCALEID = 2052) H ON A.FSUBSYSTEMID = H.FID JOIN(SELECT J.FTOPCLASSID, K.FNAME FROM T_META_TOPCLASS J JOIN T_META_TOPCLASS_L K ON J.FTOPCLASSID = K.FTOPCLASSID AND K.FLOCALEID = 2052) L ON H.FTOPCLASSID = L.FTOPCLASSID ORDER BY L.FNAME,H.FNAME,B.FNAME,E.FNAME-- 01 查询部门信息T_BD_DEPARTMENT 部门表 T_BD_DEPARTMENT_L 部门多语言表 SELECT a.FDEPTID,b.FNAME,b.FFULLNAME FROM T_BD_DEPARTMENT a JOIN T_BD_DEPARTMENT_L b ON a.FDEPTID = b.FDEPTID WHERE b.FNAME = '信息科'-- 02 按部门查询人员信息SQLSELECT a.FSTAFFID,a.FMASTERID,a.FNUMBER,a.FPERSONID,a.FPOSTID,a.FDEPTID, b.FNAME,c.FNAME FROM T_BD_STAFF a JOIN T_BD_STAFF_L b ON a.FSTAFFID = b.FSTAFFID JOIN T_BD_DEPARTMENT_L c ON a.FDEPTID = c.FDEPTID WHERE a.FDEPTID = '180641'-- 03 即时库存查询SELECT a.FMATERIALID 物料内码, b.FNUMBER 物料编码, c.FNAME 物料名称, c.FSPECIFICATION 规格型号,d.FNUMBER 仓库代码,e.FNAME 仓库名称,d.FADDRESS 仓库地址,CONVERT(DECIMAL(18,0),a.FBASEQTY) 数量,h.FNUMBER 批号 ,g.FNAME 库存主单位, i.FNAME 库存状态 FROM T_STK_INVENTORY a LEFT JOIN T_BD_MATERIAL b ON a.FMATERIALID = b.FMATERIALID LEFT JOIN T_BD_MATERIAL_L c ON b.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_STOCK d ON a.FSTOCKID = d.FSTOCKID LEFT JOIN T_BD_STOCK_L e ON d.FSTOCKID = e.FSTOCKID LEFT JOIN T_BD_UNIT f ON a.FSTOCKUNITID = f.FUNITID LEFT JOIN T_BD_UNIT_L g ON f.FUNITID = g.FUNITID LEFT JOIN T_BD_LOTMASTER h ON b.FMASTERID = h.FMATERIALID LEFT JOIN T_BD_STOCKSTATUS_L i ON a.FSTOCKSTATUSID = i.FSTOCKSTATUSID WHERE b.FNUMBER IN ('2301004700','2518072700','2518066500','2518066300')-- 04 采购申请单SELECT a.FID,f.FNAME 申请人,d.FNAME 单据类型,b.FBILLNO 单据编号,b.FAPPLICATIONDATE 申请日期,e.FNUMBER 物料编码, c.FNAME 物料名称, c.FSPECIFICATION 规格型号,a.FREQQTY 申请数量,a.FAPPROVEQTY 批准数量,a.FEVALUATEPRICE 单价,a.FTAXPRICE 含税单价,h.FNAME 单位, a.FAMOUNT 金额,(a.FREQQTY*a.FTAXPRICE) as 含税金额 ,a.FARRIVALDATE 到货日期,g.FNAME 供应商 FROM T_PUR_REQENTRY a LEFT JOIN T_PUR_REQUISITION b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL_L c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BAS_BILLTYPE_L d ON b.FBILLTYPEID = d.FBILLTYPEID LEFT JOIN T_BD_MATERIAL e ON a.FMATERIALID = e.FMATERIALID LEFT JOIN T_BD_STAFF_L f ON b.FAPPLICANTID = f.FSTAFFID LEFT JOIN T_BD_Supplier_L g ON a.FSUGGESTSUPPLIERID = g.FSUPPLIERID LEFT JOIN T_BD_UNIT_L h ON a.FUNITID = h.FUNITID WHERE b.FBILLNO = 'CGSQ02362'-- 05 采购订单SELECT a.FID,c.FBILLNO 单据编号,d.FNAME 单据类型,c.FDATE 采购日期,e.FNAME 供应商,F.FNUMBER 物料编码, g.FNAME 物料名称,g.FSPECIFICATION 规格型号,h.FNAME 单位,a.FQTY 采购数量 FROM T_PUR_POORDERENTRY a LEFT JOIN T_PUR_POORDERENTRY_L b ON a.FID = b.FPKID LEFT JOIN T_PUR_POORDER c ON a.FID = c.FID LEFT JOIN T_BAS_BILLTYPE_L d ON c.FBILLTYPEID = d.FBILLTYPEID LEFT JOIN T_BD_Supplier_L e ON c.FSUPPLIERID = e.FSUPPLIERID LEFT JOIN T_BD_MATERIAL f ON a.FMATERIALID = f.FMATERIALID LEFT JOIN T_BD_MATERIAL_L g ON a.FMATERIALID = g.FMATERIALID LEFT JOIN T_BD_UNIT_L h ON a.FUNITID = h.FUNITID WHERE c.FBILLNO = 'CGDD0342177'--直接调拨单SELECT g.FSTOCKID,a.FID, b.FBILLNO 单据编号,b.FCREATEDATE 日期,b.FTRANSFERDIRECT 调拨方向, c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号,e.FNAME 单位, a.FQTY 调拨数量, a.FSRCSTOCKID 仓库ID,f.FNUMBER 仓库编码,g.FNAME 调出仓库,i.FNUMBER 调出仓位,h.FNAME 调入仓库,j.FNAME 单据类型 FROM T_STK_STKTRANSFERINENTRY a LEFT JOIN T_STK_STKTRANSFERIN b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID LEFT JOIN T_BD_STOCK f ON a.FSRCSTOCKID = f.FSTOCKID LEFT JOIN T_BD_STOCK_L g ON f.FSTOCKID = g.FSTOCKID LEFT JOIN T_BD_STOCK_L h ON a.FDESTSTOCKID = h.FSTOCKID LEFT JOIN T_BAS_FLEXVALUESENTRY i ON a.FID = i.FID LEFT JOIN T_BAS_BILLTYPE_L j ON b.FBILLTYPEID = j.FBILLTYPEID WHERE b.FBILLNO = 'ZJDB000057'--销售订单SELECT a.FID, b.FCREATEDATE 日期,b.FBILLNO 单据编号,c.FNAME 物料名称,c.FSPECIFICATION 规格型号,d.FNAME 单位, a.FQTY 数量,e.FNAME 销售部门,g.FNAME 销售员,h.FNAME 客户,i.FNAME 单据类型,i.FLOCALEID FROM T_SAL_ORDERENTRY a LEFT JOIN T_SAL_ORDER b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL_L c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_UNIT_L d ON a.FUNITID = d.FUNITID LEFT JOIN T_BD_DEPARTMENT_L e ON b.FSALEDEPTID = e.FDEPTID LEFT JOIN T_BD_OPERATORENTRY f ON b.FSALERID = f.FENTRYID LEFT JOIN T_BD_STAFF_L g ON f.FSTAFFID = g.FSTAFFID LEFT JOIN T_BD_CUSTOMER_L h ON b.FCUSTID = h.FCUSTID LEFT JOIN T_BAS_BILLTYPE_L i ON b.FBILLTYPEID = i.FBILLTYPEID WHERE b.FBILLNO = 'XsssD000152' AND i.FLOCALEID = '2052'-- 调拨申请单SELECT a.FID, b.FBILLNO 单据编号,c.FNUMBER 物料编码, d.FNAME 物料名称,d.FSPECIFICATION 规格型号, e.FNAME 单位,CONVERT(DECIMAL(18,0),a.FQTY) 数量, f.FNAME 调出仓库, g.FNAME 调入仓库,h.FNAME 单据类型 FROM T_STK_STKTRANSFERAPPENTRY a LEFT JOIN T_STK_STKTRANSFERAPP b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID LEFT JOIN T_BD_STOCK_L f ON a.FSTOCKID = f.FSTOCKID LEFT JOIN T_BD_STOCK_L g ON a.FSTOCKINID = g.FSTOCKID LEFT JOIN T_BAS_BILLTYPE_L h ON b.FBILLTYPEID = h.FBILLTYPEID WHERE b.FBILLNO = 'DBSQ000012'-- 查询角色SELECT a.FROLEID ID, a.FNUMBER 编码,b.FNAME 名称 FROM T_SEC_ROLE a LEFT JOIN T_SEC_ROLE_L b ON a.FROLEID = b.FROLEID-- 查询用户&用户组SELECT a.FPRIMARYGROUP ID,a.FNAME 用户名,b.FNAME 用户组,a.FPHONE 手机号, a.FAPPGROUP 许可分组,a.* FROM T_SEC_USER a LEFT JOIN T_SEC_USERGROUP_L b ON a.FPRIMARYGROUP = b.FID-- 销售报价单SELECT a.FID, b.FBILLNO 单据编号,c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号, CONVERT(DECIMAL(18,0),a.FQTY) 数量,e.FNAME 单位,CONVERT(DECIMAL(18,2),f.FPRICE) 单价, CONVERT(DECIMAL(18,2),f.FTAXNETPRICE) 含税单价, CONVERT(DECIMAL(18,0),f.FTAXRATE) "税率%", CONVERT(DECIMAL(18,2),f.FAMOUNT) 金额,CONVERT(DECIMAL(18,2),f.FALLAMOUNT) 含税金额, h.FNAME 客户,g.FNAME 单据类型 FROM T_SAL_QUOTATIONENTRY a LEFT JOIN T_SAL_QUOTATION b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID LEFT JOIN T_SAL_QUOTATIONENTRY_F f ON a.FID = f.FID LEFT JOIN T_BAS_BILLTYPE_L g ON b.FBILLTYPEID = g.FBILLTYPEID LEFT JOIN T_BD_CUSTOMER_L h ON b.FCUSTID = h.FCUSTID WHERE b.FBILLNO = 'XSBJD0363'-- 收料通知单 采购收料SELECT b.FID,b.FBILLNO 单据编号,CONVERT(varchar(10),FDATE,126)收料日期,c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号, e.FNAME 单位,CONVERT(DECIMAL(18,0),a.FACTLANDQTY) 实到数量,CONVERT(DECIMAL(18,0),a.FACTRECEIVEQTY) 实收数量, F.FNAME 仓库,g.FNAME 供应商 FROM T_PUR_RECEIVEENTRY a LEFT JOIN T_PUR_RECEIVE b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID LEFT JOIN T_BD_STOCK_L f ON a.FSTOCKID = f.FSTOCKID LEFT JOIN T_BD_SUPPLIER_L g ON b.FSUPPLIERID = g.FSUPPLIERID WHERE b.FBILLNO = '22062636503'-- 供应商查询SELECT a.FSUPPLIERID ID,b.FNAME 供应商名称,b.FSHORTNAME 简称,c.FADDRESS 地址,d.FTaxRegisterCode 税务登记号,e.FContact 联系人,e.FTEL 电话,e.FMOBILE 手机 FROM T_BD_SUPPLIER a LEFT JOIN T_BD_SUPPLIER_L b ON a.FSUPPLIERID = b.FSUPPLIERID LEFT JOIN T_BD_SUPPLIERBASE c ON a.FSUPPLIERID = c.FSUPPLIERID LEFT JOIN t_BD_SupplierFinance d ON a.FSUPPLIERID = d.FSUPPLIERID LEFT JOIN t_BD_SupplierContact e ON a.FSUPPLIERID = e.FSUPPLIERID-- 采购价目表SELECT a.FID ID, b.FNUMBER 编码,c.FNAME 名称,g.FNAME 供应商,d.FNUMBER 物料编码,e.FNAME 物料名称,e.FSPECIFICATION 规格,f.FNAME 单位, a.FPRICE 单价,a.FTAXPRICE 含税单价,CONVERT(DECIMAL(18,0),a.FTAXRATE) "税率%", CONVERT(varchar(10),a.FEFFECTIVEDATE,126) 生效日期,CONVERT(varchar(10),a.FEXPIRYDATE,126) 失效日期 FROM T_PUR_PRICELISTENTRY a LEFT JOIN T_PUR_PRICELIST b ON a.FID = b.FID LEFT JOIN T_PUR_PRICELIST_L c ON b.FID = c.FID LEFT JOIN T_BD_MATERIAL d ON a.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_MATERIAL_L e ON a.FMATERIALID = e.FMATERIALID LEFT JOIN T_BD_UNIT_L f ON a.FUNITID = f.FUNITID LEFT JOIN T_BD_SUPPLIER_L g ON b.FSUPPLIERID = g.FSUPPLIERID WHERE b.FNUMBER = 'CGJMsfd523666'-- 销售价目表SELECT a.FID ID, b.FNUMBER 编号,c.FNAME 名称,d.FNUMBER 物料编号,e.FNAME 物料名称,e.FSPECIFICATION 规格,f.FNAME 单位, a.FPRICE 价格,a.FDOWNPRICE 最低限价, CONVERT(varchar(10),a.FEFFECTIVEDATE,126) 生效日期,CONVERT(varchar(10),a.FEXPRIYDATE,126) 失效日期 FROM T_SAL_PRICELISTENTRY a LEFT JOIN T_SAL_PRICELIST b ON a.FID = b.FID LEFT JOIN T_SAL_PRICELIST_L c ON b.FID = c.FID LEFT JOIN T_BD_MATERIAL d ON a.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_MATERIAL_L e ON d.FMATERIALID = e.FMATERIALID LEFT JOIN T_BD_UNIT_L f ON a.FUNITID = f.FUNITID WHERE b.FNUMBER = 'XSJMB36535'--生产订单SELECT a.FID,b.FBILLNO 单据编号,CONVERT(varchar(10),b.FDATE,126) 单据日期,f.FNAME 单据类型, c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号,e.FNAME 单位,FQTY 数量 FROM T_PRD_MOENTRY a LEFT JOIN T_PRD_MO b ON a.FID = b.FID LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID LEFT JOIN T_BAS_BILLTYPE_L f ON b.FBILLTYPE = f.FBILLTYPEID WHERE b.FBILLNO = '223633610811' AND f.FLOCALEID = '2052'-- 单据类型查询SELECT a.FBILLTYPEID,a.FNUMBER 单据编码, b.FNAME 单据名称 FROM T_BAS_BILLTYPE a LEFT JOIN T_BAS_BILLTYPE_L b ON a.FBILLTYPEID = b.FBILLTYPEID WHERE b.FLOCALEID = '2052' AND b.FNAME LIKE '%单据%'
-
Kingdee-采购管理-T_PUR_MRAPP-退料申请表 T_PUR_MRAPP表描述：退料申请表备注：退料申请信息主键字段名字段描述数据类型长度可空备注✓FID单据内码int 默认值:0null FBILLNO单据编号nvarchar30 默认值:''null FDATE日期datetime 默认值:getdate()null FBILLTYPEID单据类型varchar36 默认值:''null FSUPPLIERID供应商内码int 默认值:0null FRMTYPE退货类型varchar20✓默认值:''null FPURCHASEORGID采购组织int 默认值:0null FAPPORGID申请组织int 默认值:0null FAPPDEPTID申请部门int 默认值:0null FAPPLICANTID申请人int 默认值:0null FRMMODE退货方式varchar20 默认值:''A:补货退货 B:退货并退款 C:扣款不退货 FREPLENISHMODE补货方式varchar20 默认值:''A:按源单补货 B:创建补货订单 FRMREASON退货原因varchar36 默认值:''null FRMLOC退货地点nvarchar120 默认值:''null FDOCUMENTSTATUS单据状态char1 默认值:''A:新建 Z:暂存 B: 审核中 C: 已审核 D:重新审核 FDESCRIPTION描述nvarchar255 默认值:''null FCREATORID制单人int 默认值:0null FCREATEDATE制单日期datetime 默认值:getdate()null FMODIFIERID最近修改人内码int 默认值:0null FMODIFYDATE最新修改日期datetime ✓ FAPPROVERID审核人内码int 默认值:0null FAPPROVEDATE审核日期datetime ✓ FCANCELSTATUS作废状态char1 默认值:''A:未作废 B:已作废 FCANCELLERID作废人内码int 默认值:0null FCANCELDATE作废日期datetime ✓ FIsConvert是否是单据转换char1 默认值:'0'隐藏字段, 判断单据是否是下推或选单生成. FBUSINESSTYPE业务类型nvarchar20 默认值:''枚举:普通:CG委外:WW FOBJECTTYPEID业务对象类型varchar36 默认值:''null FREQUIREORGID需求组织携带int 默认值:0null FCorrespondOrgId对应组织int ✓默认值:0null FCONFIRMSTATUS确认状态char1 默认值:''null FCONFIRMERID确认人int 默认值:0null FCONFIRMDATE确认日期datetime ✓ FBILLTYPEIDVM协同虚拟单据类型varchar36 默认值:''null FCLOSERID关闭人int 默认值:0null FCLOSEDATE关闭日期datetime ✓ FCLOSESTATUS关闭状态char1 默认值:''null--建表脚本--create table T_PUR_MRAPP( FID int default 0 comment '单据内码' ,FBILLNO nvarchar(30) default '' comment '单据编号' ,FDATE datetime default getdate() comment '日期' ,FBILLTYPEID varchar(36) default '''' comment '单据类型' ,FSUPPLIERID int default 0 comment '供应商内码' ,FRMTYPE varchar(20) not null comment '退货类型' ,FPURCHASEORGID int default 0 comment '采购组织' ,FAPPORGID int default 0 comment '申请组织' ,FAPPDEPTID int default 0 comment '申请部门' ,FAPPLICANTID int default 0 comment '申请人' ,FRMMODE varchar(20) default '''' comment '退货方式' ,FREPLENISHMODE varchar(20) default '''' comment '补货方式' ,FRMREASON varchar(36) default '''' comment '退货原因' ,FRMLOC nvarchar(120) default '' comment '退货地点' ,FDOCUMENTSTATUS char(1) default '''' comment '单据状态' ,FDESCRIPTION nvarchar(255) default '' comment '描述' ,FCREATORID int default 0 comment '制单人' ,FCREATEDATE datetime default getdate() comment '制单日期' ,FMODIFIERID int default 0 comment '最近修改人内码' ,FMODIFYDATE datetime not null comment '最新修改日期' ,FAPPROVERID int default 0 comment '审核人内码' ,FAPPROVEDATE datetime not null comment '审核日期' ,FCANCELSTATUS char(1) default '''' comment '作废状态' ,FCANCELLERID int default 0 comment '作废人内码' ,FCANCELDATE datetime not null comment '作废日期' ,FIsConvert char(1) default ''0'' comment '是否是单据转换' ,FBUSINESSTYPE nvarchar(20) default '' comment '业务类型' ,FOBJECTTYPEID varchar(36) default '''' comment '业务对象类型' ,FREQUIREORGID int default 0 comment '需求组织携带' ,FCorrespondOrgId int not null comment '对应组织' ,FCONFIRMSTATUS char(1) default '''' comment '确认状态' ,FCONFIRMERID int default 0 comment '确认人' ,FCONFIRMDATE datetime not null comment '确认日期' ,FBILLTYPEIDVM varchar(36) default '''' comment '协同虚拟单据类型' ,FCLOSERID int default 0 comment '关闭人' ,FCLOSEDATE datetime not null comment '关闭日期' ,FCLOSESTATUS char(1) default '''' comment '关闭状态' ,primary key (FID) ) comment = '退料申请'--查询--select FID as "fid",FBILLNO as "fbillno",FDATE as "fdate",FBILLTYPEID as "fbilltypeid",FSUPPLIERID as "fsupplierid",FRMTYPE as "frmtype",FPURCHASEORGID as "fpurchaseorgid",FAPPORGID as "fapporgid",FAPPDEPTID as "fappdeptid",FAPPLICANTID as "fapplicantid",FRMMODE as "frmmode",FREPLENISHMODE as "freplenishmode",FRMREASON as "frmreason",FRMLOC as "frmloc",FDOCUMENTSTATUS as "fdocumentstatus",FDESCRIPTION as "fdescription",FCREATORID as "fcreatorid",FCREATEDATE as "fcreatedate",FMODIFIERID as "fmodifierid",FMODIFYDATE as "fmodifydate",FAPPROVERID as "fapproverid",FAPPROVEDATE as "fapprovedate",FCANCELSTATUS as "fcancelstatus",FCANCELLERID as "fcancellerid",FCANCELDATE as "fcanceldate",FIsConvert as "fisconvert",FBUSINESSTYPE as "fbusinesstype",FOBJECTTYPEID as "fobjecttypeid",FREQUIREORGID as "frequireorgid",FCorrespondOrgId as "fcorrespondorgid",FCONFIRMSTATUS as "fconfirmstatus",FCONFIRMERID as "fconfirmerid",FCONFIRMDATE as "fconfirmdate",FBILLTYPEIDVM as "fbilltypeidvm",FCLOSERID as "fcloserid",FCLOSEDATE as "fclosedate",FCLOSESTATUS as "fclosestatus" from T_PUR_MRAPP--查询(中文字段)--select FID as "单据内码",FBILLNO as "单据编号",FDATE as "日期",FBILLTYPEID as "单据类型",FSUPPLIERID as "供应商内码",FRMTYPE as "退货类型",FPURCHASEORGID as "采购组织",FAPPORGID as "申请组织",FAPPDEPTID as "申请部门",FAPPLICANTID as "申请人",FRMMODE as "退货方式",FREPLENISHMODE as "补货方式",FRMREASON as "退货原因",FRMLOC as "退货地点",FDOCUMENTSTATUS as "单据状态",FDESCRIPTION as "描述",FCREATORID as "制单人",FCREATEDATE as "制单日期",FMODIFIERID as "最近修改人内码",FMODIFYDATE as "最新修改日期",FAPPROVERID as "审核人内码",FAPPROVEDATE as "审核日期",FCANCELSTATUS as "作废状态",FCANCELLERID as "作废人内码",FCANCELDATE as "作废日期",FIsConvert as "是否是单据转换",FBUSINESSTYPE as "业务类型",FOBJECTTYPEID as "业务对象类型",FREQUIREORGID as "需求组织携带",FCorrespondOrgId as "对应组织",FCONFIRMSTATUS as "确认状态",FCONFIRMERID as "确认人",FCONFIRMDATE as "确认日期",FBILLTYPEIDVM as "协同虚拟单据类型",FCLOSERID as "关闭人",FCLOSEDATE as "关闭日期",FCLOSESTATUS as "关闭状态" from T_PUR_MRAPP--INSERT脚本--insert into T_PUR_MRAPP(FID,FBILLNO,FDATE,FBILLTYPEID,FSUPPLIERID,FRMTYPE,FPURCHASEORGID,FAPPORGID,FAPPDEPTID,FAPPLICANTID,FRMMODE,FREPLENISHMODE,FRMREASON,FRMLOC,FDOCUMENTSTATUS,FDESCRIPTION,FCREATORID,FCREATEDATE,FMODIFIERID,FMODIFYDATE,FAPPROVERID,FAPPROVEDATE,FCANCELSTATUS,FCANCELLERID,FCANCELDATE,FIsConvert,FBUSINESSTYPE,FOBJECTTYPEID,FREQUIREORGID,FCorrespondOrgId,FCONFIRMSTATUS,FCONFIRMERID,FCONFIRMDATE,FBILLTYPEIDVM,FCLOSERID,FCLOSEDATE,FCLOSESTATUS) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)--UPDATE脚本--update T_PUR_MRAPP set FID=?,FBILLNO=?,FDATE=?,FBILLTYPEID=?,FSUPPLIERID=?,FRMTYPE=?,FPURCHASEORGID=?,FAPPORGID=?,FAPPDEPTID=?,FAPPLICANTID=?,FRMMODE=?,FREPLENISHMODE=?,FRMREASON=?,FRMLOC=?,FDOCUMENTSTATUS=?,FDESCRIPTION=?,FCREATORID=?,FCREATEDATE=?,FMODIFIERID=?,FMODIFYDATE=?,FAPPROVERID=?,FAPPROVEDATE=?,FCANCELSTATUS=?,FCANCELLERID=?,FCANCELDATE=?,FIsConvert=?,FBUSINESSTYPE=?,FOBJECTTYPEID=?,FREQUIREORGID=?,FCorrespondOrgId=?,FCONFIRMSTATUS=?,FCONFIRMERID=?,FCONFIRMDATE=?,FBILLTYPEIDVM=?,FCLOSERID=?,FCLOSEDATE=?,FCLOSESTATUS=? where FID=?--delete脚本--delete from T_PUR_MRAPP where FID=?--给字段加备注--alter table T_PUR_MRAPP modify column FID int default 0 comment '单据内码'; alter table T_PUR_MRAPP modify column FBILLNO nvarchar(30) default '' comment '单据编号'; alter table T_PUR_MRAPP modify column FDATE datetime default getdate() comment '日期'; alter table T_PUR_MRAPP modify column FBILLTYPEID varchar(36) default '''' comment '单据类型'; alter table T_PUR_MRAPP modify column FSUPPLIERID int default 0 comment '供应商内码'; alter table T_PUR_MRAPP modify column FRMTYPE varchar(20) not null comment '退货类型'; alter table T_PUR_MRAPP modify column FPURCHASEORGID int default 0 comment '采购组织'; alter table T_PUR_MRAPP modify column FAPPORGID int default 0 comment '申请组织'; alter table T_PUR_MRAPP modify column FAPPDEPTID int default 0 comment '申请部门'; alter table T_PUR_MRAPP modify column FAPPLICANTID int default 0 comment '申请人'; alter table T_PUR_MRAPP modify column FRMMODE varchar(20) default '''' comment '退货方式'; alter table T_PUR_MRAPP modify column FREPLENISHMODE varchar(20) default '''' comment '补货方式'; alter table T_PUR_MRAPP modify column FRMREASON varchar(36) default '''' comment '退货原因'; alter table T_PUR_MRAPP modify column FRMLOC nvarchar(120) default '' comment '退货地点'; alter table T_PUR_MRAPP modify column FDOCUMENTSTATUS char(1) default '''' comment '单据状态'; alter table T_PUR_MRAPP modify column FDESCRIPTION nvarchar(255) default '' comment '描述'; alter table T_PUR_MRAPP modify column FCREATORID int default 0 comment '制单人'; alter table T_PUR_MRAPP modify column FCREATEDATE datetime default getdate() comment '制单日期'; alter table T_PUR_MRAPP modify column FMODIFIERID int default 0 comment '最近修改人内码'; alter table T_PUR_MRAPP modify column FMODIFYDATE datetime not null comment '最新修改日期'; alter table T_PUR_MRAPP modify column FAPPROVERID int default 0 comment '审核人内码'; alter table T_PUR_MRAPP modify column FAPPROVEDATE datetime not null comment '审核日期'; alter table T_PUR_MRAPP modify column FCANCELSTATUS char(1) default '''' comment '作废状态'; alter table T_PUR_MRAPP modify column FCANCELLERID int default 0 comment '作废人内码'; alter table T_PUR_MRAPP modify column FCANCELDATE datetime not null comment '作废日期'; alter table T_PUR_MRAPP modify column FIsConvert char(1) default ''0'' comment '是否是单据转换'; alter table T_PUR_MRAPP modify column FBUSINESSTYPE nvarchar(20) default '' comment '业务类型'; alter table T_PUR_MRAPP modify column FOBJECTTYPEID varchar(36) default '''' comment '业务对象类型'; alter table T_PUR_MRAPP modify column FREQUIREORGID int default 0 comment '需求组织携带'; alter table T_PUR_MRAPP modify column FCorrespondOrgId int not null comment '对应组织'; alter table T_PUR_MRAPP modify column FCONFIRMSTATUS char(1) default '''' comment '确认状态'; alter table T_PUR_MRAPP modify column FCONFIRMERID int default 0 comment '确认人'; alter table T_PUR_MRAPP modify column FCONFIRMDATE datetime not null comment '确认日期'; alter table T_PUR_MRAPP modify column FBILLTYPEIDVM varchar(36) default '''' comment '协同虚拟单据类型'; alter table T_PUR_MRAPP modify column FCLOSERID int default 0 comment '关闭人'; alter table T_PUR_MRAPP modify column FCLOSEDATE datetime not null comment '关闭日期'; alter table T_PUR_MRAPP modify column FCLOSESTATUS char(1) default '''' comment '关闭状态';
