用户权限查询相关sql

SELECT 
 
DISTINCT "USERORGPERM".FID AS "ID", 
"PERMITEM".FNumber AS "PERMITEM.NUMBER", 
"PERMITEM".FName AS "PERMITEM.NAME", 
"OWNER".FNumber AS "OWNER.NUMBER", 
"OWNER".FName_l2 AS "OWNER.NAME", 
"DEFORGUNIT".FDisplayName_l2 AS "DEFORGUNIT.DISPLAYNAME", 
"ORG".FDisplayName_l2 AS "ORG.DISPLAYNAME", 
"ORG".FNumber AS "ORG.NUMBER", 
"CTRLUNIT".FLongNumber AS "CTRLUNIT.LONGNUMBER", 
"CTRLUNIT".FDisplayName_l2 AS "CTRLUNIT.DISPLAYNAME", 
"CTRLUNIT".FName_l2 AS "CTRLUNIT.NAME", 
"CTRLUNIT".FNumber AS "CTRLUNIT.NUMBER", 
"CTRLUNIT".FID AS "CTRLUNIT.ID", 
"PERMITEM".FAlias_l2 AS "PERMITEM.ALIAS", 
"PERMITEM".FID AS "PERMITEM.ID", 
"OWNER".FID AS "OWNER.ID", 
"ORG".FID AS "ORG.ID", 
"PERMITEM".FLongNumber AS "PERMITEM.LONGNUMBER"
 
FROM T_PM_UserOrgPerm AS "USERORGPERM"
 
INNER JOIN T_PM_PermItem AS "PERMITEM"
ON "USERORGPERM".FPermItemID = "PERMITEM".FID
 
INNER JOIN T_PM_User AS "OWNER"
ON "USERORGPERM".FOwner = "OWNER".FID
 
INNER JOIN T_ORG_BaseUnit AS "ORG"
ON "USERORGPERM".FOrgID = "ORG".FID
 
INNER JOIN T_ORG_CtrlUnit AS "CTRLUNIT"
ON "USERORGPERM".FCtrlUnitID = "CTRLUNIT".FID
 
INNER JOIN T_ORG_BaseUnit AS "DEFORGUNIT"
ON "OWNER".FDefOrgUnitID = "DEFORGUNIT".FID
 
where "PERMITEM".FAlias_l2 like '%内部账户明细%'
 
ORDER BY 
"OWNER.NUMBER" ASC,
"ORG.NUMBER" ASC,
"PERMITEM.LONGNUMBER" ASC

---

--根据人员获取指定单据权限
SELECT ROLEPERM.FID,
       ROLEPERM.FROLEID,
       ROLEPERM.FPERMITEMID,
       ROLEPERM.FPERMTYPE,
       ROLEPERM.FRULESTRUCTURE,
       ROLEPERM.FRULEEXPR,
       PERMITEM.FALIAS_L2,
       PERMITEM.FORGRELATION,
       PERMITEM.FISLEAF,
       PERMITEM.FPARENTID,
       PERMITEM.FOBJECTTYPE,
       PERMITEM.FISAPPLYTOF7
  FROM T_PM_ROLEPERM ROLEPERM
 INNER JOIN T_PM_PERMITEM PERMITEM ON PERMITEM.FID = ROLEPERM.FPERMITEMID
 WHERE ROLEPERM.FROLEID IN ( SELECT DISTINCT ROL.FID
                              FROM T_PM_USERROLEORG USEROLE
                              LEFT JOIN T_PM_ROLE ROL
                                ON USEROLE.FROLEID = ROL.FID
                              LEFT JOIN T_PM_USER US
                                ON USEROLE.FUSERID = US.FID
                             WHERE US.FNUMBER = 'luopei'
                               AND ROL.FID IS NOT NULL  )
   AND PERMITEM.FNUMBER IN ('pigAdjustableOrder_addnew','pigBackfatDetect_addnew','pigAdjustEarno_addnew','pigBirthRec_addnew','pigMatingRecord_addnew','pigWeaningRec_addnew','pigChangingPool_addnew','pigCoreGroup_addnew','pigDeadRecord_addnew','药品领用申请_集合新增','pigEjaculations_addnew','pigHeatRecord_addnew','pigDrugApply_addnew','newItem1','pigWeedApply_addnew')