NC65 sql server 查询人员薪资发放 数据

1、查询当前登录用户所在的集团和公司分配了哪些薪资项目的权限，根据薪资类别、当前所属期间查询

select wa_item.itemkey,
       wa_item.iitemtype,
       wa_item.defaultflag,
       wa_item.ifldwidth,
       wa_item.category_id,
       wa_classitem.*,
       N'Y'                        editflag,
       N'Y'                     as showflag,
       wa_classitem.idisplayseq as idisplayseq,
       itempower.editflag
from wa_classitem,
     wa_item,
     (SELECT pk_wa_item, MAX(editflag) as editflag
      FROM wa_itempower
      WHERE pk_wa_class = N'1001A11000000010T8Z6'
        AND pk_group = N'0001A11000000000037X'
        AND pk_org = (select pk_org from wa_waclass where pk_wa_class = N'1001A11000000010T8Z6')
        AND (pk_subject IN (SELECT pk_role FROM sm_user_role WHERE cuserid = N'1001A11000000006PCEM') or
             pk_subject = N'1001A11000000006PCEM')
      GROUP BY pk_wa_item) as itempower
where wa_classitem.pk_wa_item = wa_item.pk_wa_item
  and wa_classitem.pk_wa_item = itempower.pk_wa_item
  and wa_classitem.pk_wa_class = '1001A11000000010T8Z6'
  and wa_classitem.cyear = '2022'
  and wa_classitem.cperiod = '04'
order by wa_classitem.idisplayseq;

2、查询相关人员的薪资发放数据

--查询薪资档案（发放）主键
select wa_data.pk_wa_data
from wa_data
         inner join bd_psndoc on wa_data.pk_psndoc = bd_psndoc.pk_psndoc
         inner join hi_psnjob on wa_data.pk_psnjob = hi_psnjob.pk_psnjob
         left outer join org_orgs_v on wa_data.WORKORGVID = org_orgs_v.PK_VID
         LEFT OUTER JOIN org_dept_v ON wa_data.WORKDEPTVID = org_dept_v.PK_VID
         left outer join om_job on hi_psnjob.pk_job = om_job.pk_job
         left outer join om_post on hi_psnjob.pk_post = om_post.pk_post
         left outer join bd_psncl on hi_psnjob.pk_psncl = bd_psncl.pk_psncl
where wa_data.pk_wa_class = N'1001A11000000010T8Z6'
  and wa_data.cyear = N'2022'
  and wa_data.cperiod = N'04'
  and wa_data.stopflag = N'N'
order by org_dept_v.code, hi_psnjob.clerkcode;

明细

select bd_psndoc.name     psnname,
       bd_psndoc.code     psncode,
       hi_psnjob.clerkcode,
       org_dept_v.name    deptname,
       org_orgs_v.name    orgname,
       bd_psncl.name      plsname,
       financeorg.name    financeorg,
       financedept.name   financedept,
       liabilityorg.name  liabilityorg,
       liabilitydept.name liabilitydept,
       taxorg.name        taxorgname,
       om_job.jobname,
       om_post.postname   postname,
       wa_data.*,
       datapower.operateflag
from wa_data
         inner join bd_psndoc on wa_data.pk_psndoc = bd_psndoc.pk_psndoc
         inner join hi_psnjob on wa_data.pk_psnjob = hi_psnjob.pk_psnjob
         left outer join org_orgs_v on org_orgs_v.pk_vid = wa_data.workorgvid
         left outer join org_dept_v on org_dept_v.pk_vid = wa_data.workdeptvid
         left outer join om_job on hi_psnjob.pk_job = om_job.pk_job
         left outer join om_post on hi_psnjob.pk_post = om_post.pk_post
         left outer join bd_psncl on hi_psnjob.pk_psncl = bd_psncl.pk_psncl
         left outer join org_orgs financeorg on wa_data.pk_financeorg = financeorg.pk_org
         left outer join org_dept financedept on wa_data.pk_financedept = financedept.pk_dept
         left outer join org_orgs liabilityorg on wa_data.pk_liabilityorg = liabilityorg.pk_org
         left outer join org_dept liabilitydept on wa_data.pk_liabilitydept = liabilitydept.pk_dept
         left outer join org_orgs taxorg on wa_data.taxorg = taxorg.pk_org
         left outer join (select N'Y' as operateflag, pk_wa_data from wa_data where 1 = 1) datapower
                         on wa_data.pk_wa_data = datapower.pk_wa_data
/*
where wa_data.pk_wa_data in
(N'0001A110000000023XVC', N'0001A110000000023XVD', N'0001A110000000023XVB', N'0001A110000000023XVE',
       N'0001A110000000023XVI', N'0001A110000000023XVH', N'0001A110000000023XVL', N'0001A110000000023XVG',
       N'0001A110000000023XVF', N'0001A110000000023XVJ', N'0001A110000000023XVK', N'0001A110000000023XVO',
       N'0001A110000000023XVN', N'0001A110000000023XVM', N'0001A110000000023XVP', N'1001A11000000005BYCF',
       N'0001A110000000023XVA');
*/
--可以直接把查询薪资主键的语句放在这里，但是sql server 就需要 加 top   
where wa_data.pk_wa_data in
      (select top 100 wa_data.pk_wa_data
       from wa_data
                inner join bd_psndoc on wa_data.pk_psndoc = bd_psndoc.pk_psndoc
                inner join hi_psnjob on wa_data.pk_psnjob = hi_psnjob.pk_psnjob
                left outer join org_orgs_v on wa_data.WORKORGVID = org_orgs_v.PK_VID
                LEFT OUTER JOIN org_dept_v ON wa_data.WORKDEPTVID = org_dept_v.PK_VID
                left outer join om_job on hi_psnjob.pk_job = om_job.pk_job
                left outer join om_post on hi_psnjob.pk_post = om_post.pk_post
                left outer join bd_psncl on hi_psnjob.pk_psncl = bd_psncl.pk_psncl
       where wa_data.pk_wa_class = N'1001A11000000010T8Z6'
         and wa_data.cyear = N'2022'
         and wa_data.cperiod = N'04'
         and wa_data.stopflag = N'N'
       order by org_dept_v.code, hi_psnjob.clerkcode);

3、如果当前人员没有授权薪资方案和薪资项目，则查看不到相关薪资方案和薪资项目

薪资项目授权，勾选相关薪资项目后保存，在薪资发放节点即可看到相关薪资项目项，否则看不到