薪酬核算向导,薪酬报表,都是要去查询员工基本信息,员工职业信息,员工任职,组织,岗位的历史表中的信息,所有在这些实体上自定义了字段,并且在薪酬中引用:比如配到信息集中进行查询,数据权限条件设置,列表显示等等,那么要在对应的历史表中也进行配置,否则在薪酬这边会发现查询不到,或者报错的情况,查看apusic.0.log中可能是这种错误:
下面这个例子就是在员工职业信息中自定义增加了职级"CFZJ",但是历史表(T_HR_PERSONPOSITIONHIS)中么有设置
Caused by:
2015-12-18 09:27:32 错误 [con.err] java.sql.SQLException: ORA-00904: "PP"."CFZJID": 标识符无效
execute sql error: ORA-00904: "PP"."CFZJID": invalid identifier
sql: SELECT * FROM(SELECT tableA.*, ROWNUM RN FROM ( SELECT * FROM (SELECT DISTINCT "POSITION".FNumber "POSITION.NUMBER", "CMPSALARYSYSTEM".FName_l2 "CMPSALARYSYSTEM.NAME", "ADMINORGUNIT".FSortCode "ADMINORGUNIT.SORTCODE", "PERSON".FNumber "PERSON.NUMBER", "PERSON".FIndex "PERSON.INDEXOF", "EMPTYPE".FName_l2 "EMPTYPE.NAME", "PERSON".FName_l2 "PERSON.NAME", "POSITION".FName_l2 "POSITION.NAME", "ADMINORGUNIT".FName_l2 "ADMINORGUNIT.NAME", "POSITION".FIndex "POSITION.INDEX", "PERSON".FID "PERSON.ID", CASE WHEN ("PERSONCMP".FIsSendSalary IS NULL) THEN 1 ELSE "PERSONCMP".FIsSendSalary END "PERSONCMP.ISSENDSALARY" FROM T_BD_PERSON "PERSON" LEFT OUTER JOIN T_HR_EMPORGRELATION "EMPORGRELATION" ON (("EMPORGRELATION".FPersonID = "PERSON".FID AND "EMPORGRELATION".FIsInner = 1) AND (("EMPORGRELATION".FAssignType = 1 AND ("EMPORGRELATION".FEFFDT <= TO_DATE('2015-12-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))) AND ("EMPORGRELATION".FLEFFDT >= TO_DATE('2015-12-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS')))) LEFT OUTER JOIN T_ORG_POSITION "POSITION" ON "POSITION".FID = "EMPORGRELATION".FPOSITIONID LEFT OUTER JOIN T_HR_SPERSONCMP "PERSONCMP" ON "PERSONCMP".FPersonId = "PERSON".FID LEFT OUTER JOIN T_HR_SCMPSALARYSYSTEM "CMPSALARYSYSTEM" ON "CMPSALARYSYSTEM".FID = "PERSONCMP".FSALARYSYSTEMID LEFT OUTER JOIN T_ORG_ADMIN "ADMINORGUNIT" ON "ADMINORGUNIT".FID = "EMPORGRELATION".FADMINORGID LEFT OUTER JOIN T_HR_EMPLABORRELATION "LABORRELA" ON "LABORRELA".FPersonID = "EMPORGRELATION".FPERSONID LEFT OUTER JOIN T_HR_BDEMPLOYEETYPE "EMPTYPE" ON "EMPTYPE".FID = "LABORRELA".FLABORRELATIONSTATEID LEFT OUTER JOIN T_HR_SCMPCALORG "CMPCALORG" ON "CMPCALORG".FPersonID = "PERSON".FID LEFT OUTER JOIN T_ORG_ADMIN "CMPCALORGUNIT" ON ("CMPCALORGUNIT".FID = "CMPCALORG".FCLUORGID AND (("CMPCALORGUNIT".FEFFDT <= TO_DATE('2015-12-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) AND ("CMPCALORGUNIT".FLEFFDT >= TO_DATE('2015-12-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS')))) LEFT OUTER JOIN T_HR_PERSONPOSITION "PP" ON "PP".FPersonID = "PERSON".FID WHERE ((("PERSON".FID IN (SELECT DISTINCT "PERSON".FhistoryRelateID "PERSON.ID" FROM T_BD_PERSONHIS "PERSON" LEFT OUTER JOIN T_HR_PERSONPOSITIONHIS "PP" ON "PP".FPersonID = "PERSON".FhistoryRelateID LEFT OUTER JOIN CT_MP_ZJ "ZJ" ON "ZJ".FID = "PP".CFZJID WHERE ("ZJ".FName_l2 LIKE '%主管级%')) AND ("ADMINORGUNIT".FID IN (SELECT FOrgID FROM T_PM_OrgRange WHERE (FUserID = 'pnfvDYNpSIWwXzixTNnTMRO33n8=' AND FType = 20)) OR "CMPCALORGUNIT".FID IN (SELECT FOrgID FROM T_PM_OrgRange WHERE (FUserID = 'pnfvDYNpSIWwXzixTNnTMRO33n8=' AND FType = 20)))) AND ("ADMINORGUNIT".FLongNumber = '10' OR ("ADMINORGUNIT".FLongNumber LIKE '10!%') OR "CMPCALORGUNIT".FLongNumber = '10' OR ("CMPCALORGUNIT".FLongNumber LIKE '10!%'))) AND (((((("PERSON".FEFFDT <= TO_DATE('2015-12-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) AND ("PERSON".FLEFFDT >= TO_DATE('2015-12-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))) AND ("PP".FEFFDT <= TO_DATE('2015-12-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))) AND ("PP".FLEFFDT >= TO_DATE('2015-12-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))) AND ("EMPORGRELATION".FEFFDT <= TO_DATE('2015-12-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))) AND ("EMPORGRELATION".FLEFFDT >= TO_DATE('2015-12-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS')))) ORDER BY "ADMINORGUNIT.SORTCODE" ASC, "POSITION.INDEX" ASC, "POSITION.NUMBER" ASC, "PERSON.INDEXOF" ASC, "PERSON.ID" ASC) WHERE ROWNUM <= 20 )tableA )WHERE RN >0 AND RN <= 20
推荐阅读