/
搜索到
23
篇与
的结果
-
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 '关闭状态';
-
Kingdee-采购管理-T_PUR_CATALOG_ISSUE-采购目录下发信息表 T_PUR_CATALOG_ISSUE表描述：采购目录下发信息表备注：存储价目表下发信息主键字段名字段描述数据类型长度可空备注✓FISSUEPKID主键varchar36 默认值:''null FID单据内码int ✓默认值:0null FISSUEORGID下发组织int ✓ FISSUEMAN下发人int ✓ FISSUEDATE下发日期datetime ✓ --建表脚本--create table T_PUR_CATALOG_ISSUE( FISSUEPKID varchar(36) default '''' comment '主键' ,FID int not null comment '单据内码' ,FISSUEORGID int not null comment '下发组织' ,FISSUEMAN int not null comment '下发人' ,FISSUEDATE datetime not null comment '下发日期' ,primary key (FISSUEPKID) ) comment = '采购目录下发信息'--查询--select FISSUEPKID as "fissuepkid",FID as "fid",FISSUEORGID as "fissueorgid",FISSUEMAN as "fissueman",FISSUEDATE as "fissuedate" from T_PUR_CATALOG_ISSUE--查询(中文字段)--select FISSUEPKID as "主键",FID as "单据内码",FISSUEORGID as "下发组织",FISSUEMAN as "下发人",FISSUEDATE as "下发日期" from T_PUR_CATALOG_ISSUE--INSERT脚本--insert into T_PUR_CATALOG_ISSUE(FISSUEPKID,FID,FISSUEORGID,FISSUEMAN,FISSUEDATE) values (?,?,?,?,?)--UPDATE脚本--update T_PUR_CATALOG_ISSUE set FISSUEPKID=?,FID=?,FISSUEORGID=?,FISSUEMAN=?,FISSUEDATE=? where FISSUEPKID=?--delete脚本--delete from T_PUR_CATALOG_ISSUE where FISSUEPKID=?--给字段加备注--alter table T_PUR_CATALOG_ISSUE modify column FISSUEPKID varchar(36) default '''' comment '主键'; alter table T_PUR_CATALOG_ISSUE modify column FID int not null comment '单据内码'; alter table T_PUR_CATALOG_ISSUE modify column FISSUEORGID int not null comment '下发组织'; alter table T_PUR_CATALOG_ISSUE modify column FISSUEMAN int not null comment '下发人'; alter table T_PUR_CATALOG_ISSUE modify column FISSUEDATE datetime not null comment '下发日期';
-
Kingdee-采购管理-T_PUR_CATALOGENTRY_R-采购目录_关联信息表 T_PUR_CATALOGENTRY_R表描述：采购目录_关联信息主键字段名字段描述数据类型长度可空备注✓FENTRYID分录内码int 默认值:0null FID单据内码int 默认值:0null FPAYCONDITION付款条件int 默认值:0null FSETTLETYPEID结算方式int 默认值:0null FMinBasePackagingQty最小装包数量基本单位decimal23,10 默认值:0.0null--建表脚本--create table T_PUR_CATALOGENTRY_R( FENTRYID int default 0 comment '分录内码' ,FID int default 0 comment '单据内码' ,FPAYCONDITION int default 0 comment '付款条件' ,FSETTLETYPEID int default 0 comment '结算方式' ,FMinBasePackagingQty decimal(23,10) default 0.0 comment '最小装包数量基本单位' ,primary key (FENTRYID) ) comment = '采购目录_关联信息'--查询--select FENTRYID as "fentryid",FID as "fid",FPAYCONDITION as "fpaycondition",FSETTLETYPEID as "fsettletypeid",FMinBasePackagingQty as "fminbasepackagingqty" from T_PUR_CATALOGENTRY_R--查询(中文字段)--select FENTRYID as "分录内码",FID as "单据内码",FPAYCONDITION as "付款条件",FSETTLETYPEID as "结算方式",FMinBasePackagingQty as "最小装包数量基本单位" from T_PUR_CATALOGENTRY_R--INSERT脚本--insert into T_PUR_CATALOGENTRY_R(FENTRYID,FID,FPAYCONDITION,FSETTLETYPEID,FMinBasePackagingQty) values (?,?,?,?,?)--UPDATE脚本--update T_PUR_CATALOGENTRY_R set FENTRYID=?,FID=?,FPAYCONDITION=?,FSETTLETYPEID=?,FMinBasePackagingQty=? where FENTRYID=?--delete脚本--delete from T_PUR_CATALOGENTRY_R where FENTRYID=?--给字段加备注--alter table T_PUR_CATALOGENTRY_R modify column FENTRYID int default 0 comment '分录内码'; alter table T_PUR_CATALOGENTRY_R modify column FID int default 0 comment '单据内码'; alter table T_PUR_CATALOGENTRY_R modify column FPAYCONDITION int default 0 comment '付款条件'; alter table T_PUR_CATALOGENTRY_R modify column FSETTLETYPEID int default 0 comment '结算方式'; alter table T_PUR_CATALOGENTRY_R modify column FMinBasePackagingQty decimal(23,10) default 0.0 comment '最小装包数量基本单位';
