/
搜索到
23
篇与
的结果
-
金蝶云星空 K3cloud 7.3 破解版 联系QQ: 2219377135 获取金蝶云星空 K3cloud 7.3 破解版简介金蝶云星空是数字经济时代的新型ERP，是基于WEB2.0与云计算、大数据、物联网、人工智能技术的新时代企业管理服务平台。整个产品采用SOA架构，完全基于BOS平台组建而成，业务架构上贯穿流程驱动与角色驱动思想，结合中国管理模式与中国管理实践积累，精细化支持企业数字化管理重构，涵盖企业财务管理、供应链管理、生产管理、s-HR管理等核心云服务。技术架构上该产品采用平台化构建，支持跨数据应用，支持公有云及私有云部署方式，同时还在公有云上开放中国第一款基于ERP的协同开发云平台。任何一家使用金蝶云星空产品的企业，其拥有的是包含金蝶在内的众多基于同一个平台提供服务的IT服务伙伴。 金蝶云星空以其独特的“开放、标准、社交”三大特性为企业提供开放的ERP云平台，支撑企业数字化转型升级的全生命周期管理需求，是中国企业管理云服务第一品牌。 -
-
金蝶云星空 K3cloud 7.2安装程序 金蝶云星空 K3cloud 7.2安装程序简介金蝶云星空V7.2在金蝶集团“新模式、新金蝶”的战略指导下，秉承“帮助客户成功”的宗旨，基于对上述变化的深刻洞察，利用云计算、大数据、移动互联网、人工智能等技术，打造了面向数字经济时代的企业管理服务开放云平台，为企业提供财务云服务、供应链云服务、全渠道营销云服务，以及智能制造云服务，并联手生态伙伴资源，帮助企业从新模式、新业态、新生态进行全面转型与升级，帮助企业落地实现数字化营销新生态的重构、面向价值网络的供应协同、以及管理的重构，确保企业数字化能力的全面提升。金蝶云星空V7.2在以往版本的基础上，逐步完善功能模块。例如：财务云六大模块，本次版本中共新增或完善105项功能点；供应链云新增或完善18个功能点；全渠道营销云的全渠道BBC与全渠道云零售也有重大突破；制造云六大模块共完善31个功能点；除此之外，智慧工厂云、PLM云、移动应用三大领域在本版中也有不少的突破点与新增特性。技术定位金蝶云星空ERP面向中小型企业，构建BOS平台之上，帮助企业全面整合内外资源，快速实现个性化需求。金蝶云星空在企业价值创造的各环节，包括采购管理、销售管理、库存管理、生产管理、看板管理等基础业务管理，计划管理、财务管理、人力资源管理、协同办公等企业辅助管理方面，更加注重深入应用，使企业在创造价值过程中的每个环节都得以完美衔接。应用金蝶云星空 ERP，可以帮助企业打造最佳管理模式，使企业资源配置最优化，提高企业核心竞争力。技术架构金蝶云星空构建于金蝶自主研发的业务操作系统——金蝶BOS（Business Operating System）之上，采用.NET Framework软件开发平台和SOA服务架构，支持32/64位的Windows操作系统、SQL Server和Oracle数据库引擎。金蝶BOS（Business Operation System）——金蝶业务操作系统，它使用先进、开放、可伸缩的技术体系，面向企业快速成长的业务需求，协同合作伙伴拓展管理业务，通过科学的工具和流程对企业建模进行全程监控。金蝶云星空 BOS是金蝶BOS产品家族中的一员，是专门针对金蝶云星空系列产品，自主研发的新一代技术平台；用以解决金蝶云星空系统日益增加的应用复杂度和快速开发与实施之间的矛盾；集成了金蝶云星空产品底层的相关服务，如消息中心、数据传输、权限模型、网络控制等，同时提供一系列的客户化开发工具，让客户在不需要任何编程知识的前提下，快速的定制新的业务单据、业务流程和报表，以适应客户业务环境不断变化的需要。金蝶云星空 BOS是与金蝶云星空紧密地集成在一起，为金蝶云星空的运行提供平台，依托与支撑金蝶云星空的发展。金蝶云星空是金蝶采用最新的云计算技术开发的适应在互联网商业环境和“云+端”模式下运行的新一代企业管理软件，提供公共云服务和企业私有云应用，公共云服务包括金蝶云星空开发云服务和应用云服务，同时提供给金蝶合作伙伴基于标准金蝶云星空产品的扩展开发包及对应的技术支持，还可以对金蝶云星空公共云服务进行全面监控，致力于打造“开放”、“标准”、“社交”的企业管理应用架构。金蝶云星空计算技术提供开发平台和企业应用服务，开发伙伴可以基于开发平台方便、快速完成开发，基于企业应用服务实现与企业客户咨询、体验与交易等的应用完整生命周期管理。通过BOS云平台，所有金蝶云星空的标准应用、行业和客户化应用都采用相同的开发规范和标准，从而使得标准应用和客户化应用组件能够无缝集成、协同工作、平滑升级。金蝶云星空的“云+端”技术，实现企业人员随时、随地协同工作。金蝶云星空采用的云计算、多服务集群等技术，实现系统稳定可控。金蝶云星空采用B/S架构为基础。B/S架构是一种典型的三层结构。以浏览器为支撑的客户端负责与用户交互。业务服务器层进行业务逻辑处理。数据服务器层采用关系数据库进行业务数据的持久化存储。数据库——安装数据库产品和金蝶云星空数据库服务部件，目前金蝶云星空系统同时支持数据库产品Microsoft SQL Server和Oracle，所有的业务数据都存储在这里；WEB服务层——包括所有业务系统的业务逻辑组件，这些组件会被客户端所调用，是金蝶云星空系统的核心部分；客户端——金蝶云星空客户端桌面应用程序，基于Windows GUI，安装在业务系统操作人员的机器上；金蝶云星空 K3cloud 7.2安装程序下载
-
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'
