---- 表单查询
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 按部门查询人员信息SQL
SELECT 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 '%单据%'
评论