操作员-员工-角色-任职-工作流角色组
select {0} from
T_SEC_USER a FULL JOIN T_BD_PERSON b ON a.FLINKOBJECT = b.FPERSONID -- 人员表
LEFT JOIN T_BD_PERSON_L bl ON b.FPERSONID=bl.FPERSONID AND bl.FLOCALEID=2052
LEFT JOIN T_BD_STAFF c ON b.FPERSONID=c.FPERSONID -- 员工任岗表
LEFT JOIN T_BD_DEPARTMENT d ON c.FDEPTID=d.FDEPTID -- 部门表
LEFT JOIN T_BD_DEPARTMENT_L dl ON d.FDEPTID=dl.FDEPTID AND dl.FLOCALEID=2052
LEFT JOIN T_ORG_POST e ON c.FPOSTID=e.FPOSTID -- 岗位表
LEFT JOIN T_ORG_POST_L el ON e.FPOSTID=el.FPOSTID AND el.FLOCALEID=2052
left join T_HR_EMPINFO_L fl on fl.fid=c.FEMPINFOID and fl.FLOCALEID=2052
left join T_HR_EMPINFO f on fl.fid=f.fid
left join T_ORG_ORGANIZATIONS_L g on g.forgid=f.FUSEORGID and g.FLOCALEID=2052
left join T_BD_STAFFTEMP h on h.FSTAFFID=c.FSTAFFID and h.FPOSTID=e.FPOSTID
left join T_WF_RoleEmp j on j.F_EMP_EMP=f.FID
left join T_WF_ROLE k on k.FID=j.FID
left join T_WF_ROLE_L kl on kl.FID=k.FID
where {1}
用户表a，人员表b,人员表(多语言)bl,员工任岗表c，部门表d，部门表(多语言)dl,岗位表e,岗位表(多语言)el,员工表f,员工表fl,组织表g
2.存货收发汇总-核算维度（无仓库，可在物料中将仓库作为维度）
SELECT C.FFINORGID FORGID,M.FMATERIALID,M.FNUMBER,O.FYEAR,O.FPERIOD,ST.FSTOCKID,FLOTNUMBER,
SUM(CASE WHEN B.FENDINITKEY=0 THEN B.FQTY ELSE 0 END) AS 期初数,
SUM(CASE WHEN B.FENDINITKEY=0 THEN B.FAMOUNT ELSE 0 END) AS 期初金额,
SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FCURRENTINQTY ELSE 0 END) AS 入库数,
SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FCURRENTINAMOUNT ELSE 0 END) AS 入库金额,
SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FOUTSTOCKQTY ELSE 0 END) AS 出库数,
SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FCURRENTOUTAMOUNT ELSE 0 END) AS 出库金额,
SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FQTY ELSE 0 END) AS 期末数,
SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FAMOUNT ELSE 0 END) AS 期末金额
FROM T_HS_BALANCE_H B --历史核算信息
LEFT JOIN T_HS_STOCKDIMENSION S ON B.FDIMEENTRYID=S.FENTRYID --物料
LEFT JOIN T_HS_CALDIMENSIONS C ON B.FDIMENSIONID=C.FDIMENSIONID --核算组织
LEFT JOIN T_HS_OUTACCTG O ON B.FID=O.FID --年月
LEFT JOIN T_BD_MATERIAL M ON S.FMASTERID=M.FMASTERID AND C.FFINORGID=M.FUSEORGID
LEFT JOIN T_ORG_ORGANIZATIONS_L L ON L.FORGID=C.FFINORGID AND L.FLOCALEID=2052
LEFT JOIN T_BD_STOCK AS ST ON ST.FSTOCKID =S.FSTOCKID
GROUP BY C.FFINORGID,M.FMATERIALID,O.FYEAR,O.FPERIOD,M.FNUMBER ,ST.FSTOCKID ,s.FLOTNUMBER
3.存货收发汇总-库存维度
SELECT C.FFINORGID FORGID,M.FMATERIALID,M.FNUMBER,O.FYEAR,O.FPERIOD,ST.FSTOCKID,s.FLOT,
SUM(CASE WHEN B.FENDINITKEY=0 THEN B.FQTY ELSE 0 END) AS 期初数,
SUM(CASE WHEN B.FENDINITKEY=0 THEN B.FAMOUNT ELSE 0 END) AS 期初金额,
SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FCURRENTINQTY ELSE 0 END) AS 入库数,
SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FCURRENTINAMOUNT ELSE 0 END) AS 入库金额,
SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FOUTSTOCKQTY ELSE 0 END) AS 出库数,
SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FCURRENTOUTAMOUNT ELSE 0 END) AS 出库金额,
SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FQTY ELSE 0 END) AS 期末数,
SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FAMOUNT ELSE 0 END) AS 期末金额
FROM t_Hs_Inivbalance_h B
LEFT JOIN T_HS_InivStockDimension S ON B.FDIMEENTRYID=S.FENTRYID
inner JOIN T_HS_CALDIMENSIONS C ON B.FDIMENSIONID=C.FDIMENSIONID
inner JOIN T_HS_OUTACCTG O ON B.FID=O.FID
LEFT JOIN T_BD_MATERIAL M ON S.FMATERIALID=M.FMATERIALID AND C.FFINORGID=M.FUSEORGID
LEFT JOIN T_ORG_ORGANIZATIONS_L L ON L.FORGID=C.FFINORGID AND L.FLOCALEID=2052
LEFT JOIN T_BD_STOCK ST ON ST.FSTOCKID =S.FSTOCKID
GROUP BY C.FFINORGID,M.FMATERIALID,O.FYEAR,O.FPERIOD,M.FNUMBER,s.FLOT,ST.FSTOCKID
4.简单库存查询
select TS.FMATERIALID,FstockQty=Isnull(sum(FBASEQTY),0),FSTOREUNITID from T_STK_INVENTORY TS INNER JOIN T_BD_MATERIALSTOCK MS ON MS.FMATERIALID = TS.FMATERIALID
group by TS.FMATERIALID,FSTOREUNITID
5.查询最近临时表（用于提取账表数据）
select * from T_BAS_TEMPORARYTABLENAME order by FCREATEDATE desc
6.种子表
使用
declare @id int
insert into z_bas_item (column1) values (1)
select @id = id from z_bas_item
delete from z_bas_item
select @id
修复
DBCC CHECKIDENT('z_bas_voucher', NORESEED)
/*第二步，查看报错表的主键最大值，其中t_bas_voucher为业务表，获取主键当前最大值应该查业务表而非种子表*/
DECLARE @FID bigint
SELECT @FID=ISNULL(MAX(FVOUCHERID),100001)+1 FROM t_bas_voucher
SELECT @FID
/*第三步，更新种子值（更新前对比第一步和第二步获取的值，如果第一步获取的种子值更大，则无需更新）*/
DBCC CHECKIDENT('z_bas_voucher', RESEED, @FID)
7.根据关键字搜索单据标识
select * from T_META_OBJECTTYPE where
FKERNELXML.value('(/FormMetadata)[1]', 'varchar(max)') like '%验收方式%'
