-- 用户组织角色查询
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'--计划订单 计划BOM
SELECT 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
评论