001 /*
002 * Copyright 2011 The Kuali Foundation.
003 *
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.opensource.org/licenses/ecl2.php
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016 package org.kuali.kfs.module.bc.document.dataaccess.impl;
017
018 import org.kuali.kfs.module.bc.document.dataaccess.OrganizationSalarySettingSearchDao;
019 import org.kuali.rice.kns.util.Guid;
020
021 /**
022 * This class...
023 *
024 */
025 public class OrganizationSalarySettingSearchDaoJdbc extends BudgetConstructionDaoJdbcBase implements OrganizationSalarySettingSearchDao {
026
027 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(OrganizationSalarySettingSearchDaoJdbc.class);
028
029 private static final int MAXLEVEL = 50;
030 private static String[] buildIntendedIncumbentSelectTemplates = new String[1];
031 private static String[] initSelectedPositionOrgsTemplates = new String[1];
032 private static String[] populateSelectedPositionOrgsSubTreeTemplates = new String[1];
033 private static String[] populatePositionSelectForSubTreeTemplates = new String[7];
034
035 public OrganizationSalarySettingSearchDaoJdbc() {
036
037 StringBuilder sqlText = new StringBuilder(500);
038
039 // This uses the GROUP BY clause to force a distinct set so as to not trip over the unique constraint on the target table.
040 // For some reason the constraint is violated without the use of GROUP BY in Oracle
041 sqlText.append("INSERT INTO LD_BCN_INCUMBENT_SEL_T \n");
042 sqlText.append(" (PERSON_UNVL_ID, EMPLID, FIN_OBJECT_CD, PERSON_NM) \n");
043 sqlText.append("SELECT DISTINCT pull.person_unvl_id, bcaf.emplid, bcaf.fin_object_cd, iinc.person_nm \n");
044 sqlText.append("FROM LD_BCN_PULLUP_T pull, LD_BCN_ACCT_ORG_HIER_T hier, LD_PNDBC_APPTFND_T bcaf, LD_BCN_INTINCBNT_T iinc \n");
045 sqlText.append("WHERE pull.pull_flag > 0 \n");
046 sqlText.append(" AND pull.person_unvl_id = ? \n");
047 sqlText.append(" AND hier.univ_fiscal_yr = ? \n");
048 sqlText.append(" AND hier.org_fin_coa_cd = pull.fin_coa_cd \n");
049 sqlText.append(" AND hier.org_cd = pull.org_cd \n");
050 sqlText.append(" AND bcaf.univ_fiscal_yr = hier.univ_fiscal_yr \n");
051 sqlText.append(" AND bcaf.fin_coa_cd = hier.fin_coa_cd \n");
052 sqlText.append(" AND bcaf.account_nbr = hier.account_nbr \n");
053 sqlText.append(" AND bcaf.emplid NOT IN ('VACANT') \n");
054 sqlText.append(" AND iinc.emplid = bcaf.emplid \n");
055 sqlText.append("GROUP BY pull.person_unvl_id, bcaf.emplid, bcaf.fin_object_cd, iinc.person_nm");
056 buildIntendedIncumbentSelectTemplates[0] = sqlText.toString();
057 sqlText.delete(0, sqlText.length());
058
059 sqlText.append("INSERT INTO LD_BCN_BUILD_POS_SEL01_MT \n");
060 sqlText.append(" (SESID, FIN_COA_CD, ORG_CD, ORG_LEVEL_CD) \n");
061 sqlText.append("SELECT ?, p.fin_coa_cd, p.org_cd, ? \n");
062 sqlText.append("FROM LD_BCN_PULLUP_T p \n");
063 sqlText.append("WHERE p.pull_flag > 0 \n");
064 sqlText.append(" AND p.person_unvl_id = ?");
065 initSelectedPositionOrgsTemplates[0] = sqlText.toString();
066 sqlText.delete(0, sqlText.length());
067
068 sqlText.append("INSERT INTO LD_BCN_BUILD_POS_SEL01_MT \n");
069 sqlText.append(" (SESID, FIN_COA_CD, ORG_CD, ORG_LEVEL_CD) \n");
070 sqlText.append("SELECT ?, r.fin_coa_cd, r.org_cd, ? \n");
071 sqlText.append("FROM LD_BCN_ORG_RPTS_T r, LD_BCN_BUILD_POS_SEL01_MT a \n");
072 sqlText.append("WHERE a.sesid = ? \n");
073 sqlText.append(" AND a.org_level_cd = ? \n");
074 sqlText.append(" AND a.fin_coa_cd = r.rpts_to_fin_coa_cd \n");
075 sqlText.append(" AND a.org_cd = r.rpts_to_org_cd \n");
076 sqlText.append(" AND not (r.fin_coa_cd = r.rpts_to_fin_coa_cd and r.org_cd = r.rpts_to_org_cd)");
077 populateSelectedPositionOrgsSubTreeTemplates[0] = sqlText.toString();
078 sqlText.delete(0, sqlText.length());
079
080 // insert actives that are funded with person or vacant
081 sqlText.append("INSERT INTO LD_BCN_POS_SEL_T \n");
082 sqlText.append(" (PERSON_UNVL_ID, POSITION_NBR, UNIV_FISCAL_YR, EMPLID, \n");
083 sqlText.append(" IU_POSITION_TYPE, POS_DEPTID, SETID_SALARY , SAL_ADMIN_PLAN, GRADE, POS_DESCR, PERSON_NM) \n");
084 sqlText.append("SELECT DISTINCT ?, p.position_nbr,p.univ_fiscal_yr, af.emplid, p.iu_position_type, \n");
085 sqlText.append(" p.pos_deptid, p.setid_salary, p.pos_sal_plan_dflt, p.pos_grade_dflt, p.pos_descr, i.person_nm \n");
086 sqlText.append("FROM LD_BCN_BUILD_POS_SEL01_MT o, LD_BCN_POS_T p, \n");
087 sqlText.append(" LD_PNDBC_APPTFND_T af LEFT OUTER JOIN LD_BCN_INTINCBNT_T i ON (af.emplid=i.emplid) \n");
088 sqlText.append("WHERE o.sesid = ? \n");
089 sqlText.append(" AND p.pos_deptid = CONCAT(o.fin_coa_cd, CONCAT('-', o.org_cd)) \n");
090 sqlText.append(" AND p.univ_fiscal_yr = ? \n");
091 sqlText.append(" AND p.pos_eff_status <> 'I' \n");
092 sqlText.append(" AND p.univ_fiscal_yr = af.univ_fiscal_yr \n");
093 sqlText.append(" AND p.position_nbr = af.position_nbr \n");
094 sqlText.append("GROUP BY p.position_nbr, p.univ_fiscal_yr, af.emplid,p.iu_position_type, p.pos_deptid, \n");
095 sqlText.append(" p.setid_salary, p.pos_sal_plan_dflt, p.pos_grade_dflt, p.pos_descr, i.person_nm");
096 populatePositionSelectForSubTreeTemplates[0] = sqlText.toString();
097 sqlText.delete(0, sqlText.length());
098
099 // add actives that are unfunded
100 sqlText.append("INSERT INTO LD_BCN_POS_SEL_T \n");
101 sqlText.append(" (PERSON_UNVL_ID, POSITION_NBR, UNIV_FISCAL_YR, EMPLID, \n");
102 sqlText.append(" IU_POSITION_TYPE, POS_DEPTID, SETID_SALARY , SAL_ADMIN_PLAN, GRADE, POS_DESCR) \n");
103 sqlText.append("SELECT DISTINCT ?, p.position_nbr, p.univ_fiscal_yr, 'NOTFUNDED', p.iu_position_type, \n");
104 sqlText.append(" p.pos_deptid, p.setid_salary, p.pos_sal_plan_dflt, p.pos_grade_dflt, p.pos_descr \n");
105 sqlText.append("FROM LD_BCN_BUILD_POS_SEL01_MT o, LD_BCN_POS_T p \n");
106 sqlText.append("WHERE o.sesid = ? \n");
107 sqlText.append(" AND p.pos_deptid = CONCAT(o.fin_coa_cd, CONCAT('-', o.org_cd)) \n");
108 sqlText.append(" AND p.univ_fiscal_yr = ? \n");
109 sqlText.append(" AND p.pos_eff_status <> 'I' \n");
110 sqlText.append(" AND NOT EXISTS \n");
111 sqlText.append(" (SELECT * \n");
112 sqlText.append(" FROM LD_BCN_POS_SEL_T ps \n");
113 sqlText.append(" WHERE ps.person_unvl_id = ? \n");
114 sqlText.append(" AND ps.position_nbr = p.position_nbr \n");
115 sqlText.append(" AND ps.univ_fiscal_yr = p.univ_fiscal_yr) \n");
116 sqlText.append("GROUP BY p.position_nbr, p.univ_fiscal_yr, p.iu_position_type, p.pos_deptid, \n");
117 sqlText.append(" p.setid_salary, p.pos_sal_plan_dflt, p.pos_grade_dflt, p.pos_descr");
118 populatePositionSelectForSubTreeTemplates[1] = sqlText.toString();
119 sqlText.delete(0, sqlText.length());
120
121 // insert inactives that are funded due to timing problem
122 sqlText.append("INSERT INTO LD_BCN_POS_SEL_T \n");
123 sqlText.append(" (PERSON_UNVL_ID, POSITION_NBR, UNIV_FISCAL_YR, EMPLID, \n");
124 sqlText.append(" IU_POSITION_TYPE, POS_DEPTID, SETID_SALARY , SAL_ADMIN_PLAN, GRADE, POS_DESCR, PERSON_NM) \n");
125 sqlText.append("SELECT DISTINCT ?, p.position_nbr, p.univ_fiscal_yr, af.emplid, p.iu_position_type, \n");
126 sqlText.append(" p.pos_deptid, p.setid_salary, p.pos_sal_plan_dflt, p.pos_grade_dflt, p.pos_descr, 'INACTIVE POS.' \n");
127 sqlText.append("FROM LD_BCN_BUILD_POS_SEL01_MT o, LD_BCN_POS_T p, \n");
128 sqlText.append(" LD_PNDBC_APPTFND_T af LEFT OUTER JOIN LD_BCN_INTINCBNT_T i ON (af.emplid=i.emplid) \n");
129 sqlText.append("WHERE o.sesid = ? \n");
130 sqlText.append(" AND p.pos_deptid = CONCAT(o.fin_coa_cd, CONCAT('-', o.org_cd)) \n");
131 sqlText.append(" AND p.univ_fiscal_yr = ? \n");
132 sqlText.append(" AND p.pos_eff_status = 'I' \n");
133 sqlText.append(" AND p.univ_fiscal_yr = af.univ_fiscal_yr \n");
134 sqlText.append(" AND p.position_nbr = af.position_nbr \n");
135 sqlText.append("GROUP BY p.position_nbr, p.univ_fiscal_yr, af.emplid,p.iu_position_type, p.pos_deptid, \n");
136 sqlText.append(" p.setid_salary, p.pos_sal_plan_dflt, p.pos_grade_dflt, p.pos_descr");
137 populatePositionSelectForSubTreeTemplates[2] = sqlText.toString();
138 sqlText.delete(0, sqlText.length());
139
140 // insert inactives that are unfunded
141 sqlText.append("INSERT INTO LD_BCN_POS_SEL_T \n");
142 sqlText.append(" (PERSON_UNVL_ID, POSITION_NBR, UNIV_FISCAL_YR, EMPLID, \n");
143 sqlText.append(" IU_POSITION_TYPE, POS_DEPTID, SETID_SALARY , SAL_ADMIN_PLAN, GRADE, POS_DESCR, PERSON_NM) \n");
144 sqlText.append("SELECT DISTINCT ?, p.position_nbr, p.univ_fiscal_yr, 'NOTFUNDED', p.iu_position_type, \n");
145 sqlText.append(" p.pos_deptid, p.setid_salary, p.pos_sal_plan_dflt, p.pos_grade_dflt, p.pos_descr, 'INACTIVE POS.' \n");
146 sqlText.append("FROM LD_BCN_BUILD_POS_SEL01_MT o, LD_BCN_POS_T p \n");
147 sqlText.append("WHERE o.sesid = ? \n");
148 sqlText.append(" AND p.pos_deptid = CONCAT(o.fin_coa_cd, CONCAT('-', o.org_cd)) \n");
149 sqlText.append(" AND p.univ_fiscal_yr = ? \n");
150 sqlText.append(" AND p.pos_eff_status = 'I' \n");
151 sqlText.append(" AND NOT EXISTS \n");
152 sqlText.append(" (SELECT * \n");
153 sqlText.append(" FROM LD_BCN_POS_SEL_T ps \n");
154 sqlText.append(" WHERE ps.person_unvl_id = ? \n");
155 sqlText.append(" AND ps.position_nbr = p.position_nbr \n");
156 sqlText.append(" AND ps.univ_fiscal_yr = p.univ_fiscal_yr) \n");
157 sqlText.append("GROUP BY p.position_nbr, p.univ_fiscal_yr, p.iu_position_type, p.pos_deptid, \n");
158 sqlText.append(" p.setid_salary, p.pos_sal_plan_dflt, p.pos_grade_dflt, p.pos_descr");
159 populatePositionSelectForSubTreeTemplates[3] = sqlText.toString();
160 sqlText.delete(0, sqlText.length());
161
162 // set name field for vacants
163 sqlText.append("UPDATE LD_BCN_POS_SEL_T p \n");
164 sqlText.append("SET person_nm = 'VACANT' \n");
165 sqlText.append("WHERE p.person_unvl_id = ? \n");
166 sqlText.append(" AND p.emplid = 'VACANT' \n");
167 sqlText.append(" AND p.person_nm IS NULL");
168 populatePositionSelectForSubTreeTemplates[4] = sqlText.toString();
169 sqlText.delete(0, sqlText.length());
170
171 // reset name field for positions that only have deleted funding associated
172 // note that this overwrites any actual names except for Inactives
173 sqlText.append("UPDATE LD_BCN_POS_SEL_T p \n");
174 sqlText.append("SET person_nm = 'NOT FUNDED' \n");
175 sqlText.append("WHERE p.person_unvl_id = ? \n");
176 sqlText.append(" AND p.person_nm <> 'INACTIVE POS.' \n");
177 sqlText.append(" AND NOT EXISTS \n");
178 sqlText.append(" (SELECT * \n");
179 sqlText.append(" FROM LD_PNDBC_APPTFND_T af \n");
180 sqlText.append(" WHERE af.univ_fiscal_yr = p.univ_fiscal_yr \n");
181 sqlText.append(" AND af.position_nbr = p.position_nbr \n");
182 sqlText.append(" AND af.appt_fnd_dlt_cd = 'N')");
183 populatePositionSelectForSubTreeTemplates[5] = sqlText.toString();
184 sqlText.delete(0, sqlText.length());
185
186 // anything leftover is not funded
187 sqlText.append("UPDATE LD_BCN_POS_SEL_T p \n");
188 sqlText.append("SET person_nm = 'NOT FUNDED' \n");
189 sqlText.append("WHERE p.person_unvl_id = ? \n");
190 sqlText.append(" AND p.person_nm IS NULL \n");
191 populatePositionSelectForSubTreeTemplates[6] = sqlText.toString();
192
193 }
194
195 /**
196 * @see org.kuali.kfs.module.bc.document.dataaccess.OrganizationSalarySettingSearchDao#buildIntendedIncumbentSelect(java.lang.String,
197 * java.lang.Integer)
198 */
199 public void buildIntendedIncumbentSelect(String principalName, Integer universityFiscalYear) {
200
201 LOG.debug("buildIntendedIncumbentSelect() started");
202
203 getSimpleJdbcTemplate().update(buildIntendedIncumbentSelectTemplates[0], principalName, universityFiscalYear);
204 }
205
206 /**
207 * @see org.kuali.kfs.module.bc.document.dataaccess.OrganizationSalarySettingSearchDao#cleanIntendedIncumbentSelect(java.lang.String)
208 */
209 public void cleanIntendedIncumbentSelect(String principalName) {
210
211 clearTempTableByUnvlId("LD_BCN_INCUMBENT_SEL_T", "PERSON_UNVL_ID", principalName);
212 }
213
214 /**
215 * @see org.kuali.kfs.module.bc.document.dataaccess.OrganizationSalarySettingSearchDao#buildPositionSelect(java.lang.String, java.lang.Integer)
216 */
217 public void buildPositionSelect(String principalName, Integer universityFiscalYear) {
218
219 LOG.debug("buildPositionSelect() started");
220
221 String sessionId = new Guid().toString();
222 initSelectedPositionOrgs(sessionId, principalName);
223
224 populatePositionSelectForSubTree(sessionId, principalName, universityFiscalYear);
225
226 clearTempTableBySesId("LD_BCN_BUILD_POS_SEL01_MT", "SESID", sessionId);
227 }
228
229 protected void initSelectedPositionOrgs(String sessionId, String principalName) {
230
231 int currentLevel = 0;
232
233 int rowsAffected = getSimpleJdbcTemplate().update(initSelectedPositionOrgsTemplates[0], sessionId, currentLevel, principalName);
234
235 if (rowsAffected > 0) {
236 populateSelectedPositionOrgsSubTree(currentLevel, sessionId);
237 }
238 }
239
240 protected void populateSelectedPositionOrgsSubTree(int previousLevel, String sessionId) {
241
242 if (previousLevel <= MAXLEVEL) {
243 int currentLevel = previousLevel + 1;
244
245 int rowsAffected = getSimpleJdbcTemplate().update(populateSelectedPositionOrgsSubTreeTemplates[0], sessionId, currentLevel, sessionId, previousLevel);
246
247 if (rowsAffected > 0) {
248 populateSelectedPositionOrgsSubTree(currentLevel, sessionId);
249 }
250 }
251 else {
252 // overrun problem
253 LOG.warn(String.format("\nWarning: One or more selected organizations have reporting organizations more than maxlevel of %d deep.", MAXLEVEL));
254 }
255 }
256
257 protected void populatePositionSelectForSubTree(String sessionId, String principalName, Integer universityFiscalYear) {
258
259 // insert actives that are funded with person or vacant
260 getSimpleJdbcTemplate().update(populatePositionSelectForSubTreeTemplates[0], principalName, sessionId, universityFiscalYear);
261
262 // add actives that are unfunded
263 getSimpleJdbcTemplate().update(populatePositionSelectForSubTreeTemplates[1], principalName, sessionId, universityFiscalYear, principalName);
264
265 // insert inactives that are funded due to timing problem
266 getSimpleJdbcTemplate().update(populatePositionSelectForSubTreeTemplates[2], principalName, sessionId, universityFiscalYear);
267
268 // insert inactives that are unfunded
269 getSimpleJdbcTemplate().update(populatePositionSelectForSubTreeTemplates[3], principalName, sessionId, universityFiscalYear, principalName);
270
271 // set name field for vacants
272 getSimpleJdbcTemplate().update(populatePositionSelectForSubTreeTemplates[4], principalName);
273
274 // reset name field for positions that only have deleted funding associated
275 // note that this overwrites any actual names except for Inactives
276 getSimpleJdbcTemplate().update(populatePositionSelectForSubTreeTemplates[5], principalName);
277
278 // anything leftover is not funded
279 getSimpleJdbcTemplate().update(populatePositionSelectForSubTreeTemplates[6], principalName);
280
281 }
282
283 /**
284 * @see org.kuali.kfs.module.bc.document.dataaccess.OrganizationSalarySettingSearchDao#cleanPositionSelect(java.lang.String)
285 */
286 public void cleanPositionSelect(String principalName) {
287
288 clearTempTableByUnvlId("LD_BCN_POS_SEL_T", "PERSON_UNVL_ID", principalName);
289 }
290
291 }
292