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.BCConstants;
019 import org.kuali.kfs.module.bc.document.BudgetConstructionDocument;
020 import org.kuali.kfs.module.bc.document.dataaccess.OrganizationBCDocumentSearchDao;
021 import org.kuali.kfs.sys.KFSConstants;
022 import org.kuali.rice.kns.service.DataDictionaryService;
023
024 /**
025 * This class...
026 */
027 public class OrganizationBCDocumentSearchDaoJdbc extends BudgetConstructionDaoJdbcBase implements OrganizationBCDocumentSearchDao {
028 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(OrganizationBCDocumentSearchDaoJdbc.class);
029
030 private static String[] buildAccountSelectPullListTemplates = new String[1];
031 private static String[] buildBudgetedAccountsAbovePointsOfView = new String[1];
032 private static String[] buildAccountManagerDelegateListTemplates = new String[3];
033
034 private DataDictionaryService dataDictionaryService;
035
036 public OrganizationBCDocumentSearchDaoJdbc() {
037
038 StringBuilder sqlText = new StringBuilder(500);
039
040 sqlText.append("INSERT INTO LD_BCN_ACCTSEL_T \n");
041 sqlText.append(" (PERSON_UNVL_ID,UNIV_FISCAL_YR,FIN_COA_CD,ACCOUNT_NBR,SUB_ACCT_NBR,FDOC_NBR, \n");
042 sqlText.append(" ORG_LEVEL_CD,ORG_FIN_COA_CD,ORG_CD,FDOC_STATUS_CD,FDOC_CREATE_DT) \n");
043 sqlText.append("SELECT pull.person_unvl_id, head.univ_fiscal_yr, head.fin_coa_cd, head.account_nbr, head.sub_acct_nbr,head.fdoc_nbr, \n");
044 sqlText.append(" head.org_level_cd, hier2.org_fin_coa_cd, hier2.org_cd, fshd.fdoc_status_cd, fshd.temp_doc_fnl_dt \n");
045 sqlText.append("FROM LD_BCN_PULLUP_T pull, LD_BCN_ACCT_ORG_HIER_T hier, LD_BCN_ACCT_ORG_HIER_T hier2, \n");
046 sqlText.append(" LD_BCNSTR_HDR_T head, FS_DOC_HEADER_T fshd \n");
047 sqlText.append("WHERE pull.pull_flag > 0 \n");
048 sqlText.append(" AND pull.person_unvl_id = ? \n");
049 sqlText.append(" AND hier.univ_fiscal_yr = ? \n");
050 sqlText.append(" AND hier.org_fin_coa_cd = pull.fin_coa_cd \n");
051 sqlText.append(" AND hier.org_cd = pull.org_cd \n");
052 sqlText.append(" AND hier2.univ_fiscal_yr = hier.univ_fiscal_yr \n");
053 sqlText.append(" AND hier2.fin_coa_cd = hier.fin_coa_cd \n");
054 sqlText.append(" AND hier2.account_nbr = hier.account_nbr \n");
055 sqlText.append(" AND head.univ_fiscal_yr = hier2.univ_fiscal_yr \n");
056 sqlText.append(" AND head.fin_coa_cd = hier2.fin_coa_cd \n");
057 sqlText.append(" AND head.account_nbr = hier2.account_nbr \n");
058 sqlText.append(" AND head.org_level_cd = hier2.org_level_cd \n");
059 sqlText.append(" AND fshd.fdoc_nbr = head.fdoc_nbr \n");
060 sqlText.append("UNION \n");
061 sqlText.append("SELECT pull.person_unvl_id, head.univ_fiscal_yr, head.fin_coa_cd, head.account_nbr, head.sub_acct_nbr, head.fdoc_nbr, \n");
062 sqlText.append(" head.org_level_cd, hier2.org_fin_coa_cd, hier2.org_cd, fshd.fdoc_status_cd, fshd.temp_doc_fnl_dt \n");
063 sqlText.append("FROM LD_BCN_PULLUP_T pull, LD_BCN_ACCT_ORG_HIER_T hier, LD_BCN_ACCT_ORG_HIER_T hier2, \n");
064 sqlText.append(" LD_BCNSTR_HDR_T head, FS_DOC_HEADER_T fshd \n");
065 sqlText.append("WHERE pull.pull_flag > 0 \n");
066 sqlText.append(" AND pull.person_unvl_id = ? \n");
067 sqlText.append(" AND hier.univ_fiscal_yr = ? \n");
068 sqlText.append(" AND hier.org_fin_coa_cd = pull.fin_coa_cd \n");
069 sqlText.append(" AND hier.org_cd = pull.org_cd \n");
070 sqlText.append(" AND hier2.univ_fiscal_yr = hier.univ_fiscal_yr \n");
071 sqlText.append(" AND hier2.fin_coa_cd = hier.fin_coa_cd \n");
072 sqlText.append(" AND hier2.account_nbr = hier.account_nbr \n");
073 sqlText.append(" AND hier2.org_level_cd = 1 \n");
074 sqlText.append(" AND head.univ_fiscal_yr = hier2.univ_fiscal_yr \n");
075 sqlText.append(" AND head.fin_coa_cd = hier2.fin_coa_cd \n");
076 sqlText.append(" AND head.account_nbr = hier2.account_nbr \n");
077 sqlText.append(" AND head.org_level_cd = 0 \n");
078 sqlText.append(" AND fshd.fdoc_nbr = head.fdoc_nbr\n");
079 buildAccountSelectPullListTemplates[0] = sqlText.toString();
080 sqlText.delete(0, sqlText.length());
081
082 sqlText.append("INSERT INTO LD_BCN_ACCTSEL_T \n");
083 sqlText.append(" (PERSON_UNVL_ID, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FDOC_NBR, \n");
084 sqlText.append(" ORG_LEVEL_CD, ORG_FIN_COA_CD, ORG_CD, FDOC_STATUS_CD, FDOC_CREATE_DT) \n");
085 sqlText.append("SELECT ?, \n");
086 sqlText.append(" head.univ_fiscal_yr, \n");
087 sqlText.append(" head.fin_coa_cd, \n");
088 sqlText.append(" head.account_nbr, \n");
089 sqlText.append(" head.sub_acct_nbr, \n");
090 sqlText.append(" head.fdoc_nbr, \n");
091 sqlText.append(" head.org_level_cd, \n");
092 sqlText.append(" ah.org_fin_coa_cd, \n");
093 sqlText.append(" ah.org_cd, \n");
094 sqlText.append(" fshd.fdoc_status_cd, \n");
095 sqlText.append(" fshd.TEMP_DOC_FNL_DT \n");
096 sqlText.append("FROM LD_BCN_PULLUP_T pull, \n");
097 sqlText.append(" LD_BCNSTR_HDR_T head, \n");
098 sqlText.append(" FS_DOC_HEADER_T fshd, \n");
099 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T sh, \n");
100 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T ph, \n");
101 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T ah \n");
102 sqlText.append("WHERE pull.pull_flag > 0 \n");
103 sqlText.append(" AND pull.person_unvl_id = ? \n");
104 sqlText.append(" AND sh.org_fin_coa_cd = pull.fin_coa_cd \n");
105 sqlText.append(" AND sh.org_cd = pull.org_cd \n");
106 sqlText.append(" AND sh.univ_fiscal_yr = ? \n");
107 sqlText.append(" AND ph.univ_fiscal_yr = sh.univ_fiscal_yr \n");
108 sqlText.append(" AND ph.fin_coa_cd = sh.fin_coa_cd \n");
109 sqlText.append(" AND ph.account_nbr = sh.account_nbr \n");
110 sqlText.append(" AND ph.org_fin_coa_cd = ? \n");
111 sqlText.append(" AND ph.org_cd = ? \n");
112 sqlText.append(" AND head.univ_fiscal_yr = ph.univ_fiscal_yr \n");
113 sqlText.append(" AND head.fin_coa_cd = ph.fin_coa_cd \n");
114 sqlText.append(" AND head.account_nbr = ph.account_nbr \n");
115 sqlText.append(" AND head.org_level_cd > ph.org_level_cd \n");
116 sqlText.append(" AND fshd.fdoc_nbr = head.fdoc_nbr \n");
117 sqlText.append(" AND ah.univ_fiscal_yr = head.univ_fiscal_yr \n");
118 sqlText.append(" AND ah.fin_coa_cd = head.fin_coa_cd \n");
119 sqlText.append(" AND ah.account_nbr = head.account_nbr \n");
120 sqlText.append(" AND ah.org_level_cd = head.org_level_cd \n");
121 buildBudgetedAccountsAbovePointsOfView[0] = sqlText.toString();
122 sqlText.delete(0, sqlText.length());
123
124 // build list of accounts where user is fiscal officer or delegate
125 sqlText.append("INSERT INTO LD_BCN_ACCTSEL_T \n");
126 sqlText.append("SELECT ?, \n");
127 sqlText.append(" head.univ_fiscal_yr, \n");
128 sqlText.append(" head.fin_coa_cd, \n");
129 sqlText.append(" head.account_nbr, \n");
130 sqlText.append(" head.sub_acct_nbr, \n");
131 sqlText.append(" head.fdoc_nbr, \n");
132 sqlText.append(" 1, \n");
133 sqlText.append(" head.org_level_cd, \n");
134 sqlText.append(" NULL, \n");
135 sqlText.append(" NULL, \n");
136 sqlText.append(" fshd.fdoc_status_cd, \n");
137 sqlText.append(" '', \n");
138 sqlText.append(" fshd.temp_doc_fnl_dt \n");
139 sqlText.append("FROM LD_BCNSTR_HDR_T head, \n");
140 sqlText.append(" CA_ACCT_DELEGATE_T adel, \n");
141 sqlText.append(" FS_DOC_HEADER_T fshd \n");
142 sqlText.append("WHERE head.univ_fiscal_yr = ? \n");
143 sqlText.append(" AND adel.acct_dlgt_unvl_id = ? \n");
144 sqlText.append(" AND adel.acct_dlgt_actv_cd = 'Y' \n");
145 sqlText.append(" AND adel.fdoc_typ_cd in (?, ?) \n");
146 sqlText.append(" AND head.fin_coa_cd = adel.fin_coa_cd \n");
147 sqlText.append(" AND head.account_nbr = adel.account_nbr \n");
148 sqlText.append(" AND fshd.fdoc_nbr = head.fdoc_nbr \n");
149 sqlText.append("UNION \n");
150 sqlText.append("SELECT ?, \n");
151 sqlText.append(" head.univ_fiscal_yr, \n");
152 sqlText.append(" head.fin_coa_cd, \n");
153 sqlText.append(" head.account_nbr, \n");
154 sqlText.append(" head.sub_acct_nbr, \n");
155 sqlText.append(" head.fdoc_nbr, \n");
156 sqlText.append(" 1, \n");
157 sqlText.append(" head.org_level_cd, \n");
158 sqlText.append(" NULL, \n");
159 sqlText.append(" NULL, \n");
160 sqlText.append(" fshd.fdoc_status_cd, \n");
161 sqlText.append(" '', \n");
162 sqlText.append(" fshd.temp_doc_fnl_dt \n");
163 sqlText.append("FROM LD_BCNSTR_HDR_T head, \n");
164 sqlText.append(" CA_ACCOUNT_T acct, \n");
165 sqlText.append(" FS_DOC_HEADER_T fshd \n");
166 sqlText.append("WHERE head.univ_fiscal_yr = ? \n");
167 sqlText.append(" AND acct.acct_fsc_ofc_uid = ? \n");
168 sqlText.append(" AND head.fin_coa_cd = acct.fin_coa_cd \n");
169 sqlText.append(" AND head.account_nbr = acct.account_nbr \n");
170 sqlText.append(" AND fshd.fdoc_nbr = head.fdoc_nbr \n");
171
172 buildAccountManagerDelegateListTemplates[0] = sqlText.toString();
173 sqlText.delete(0, sqlText.length());
174
175 // assign org for the account's current level
176 sqlText.append("UPDATE LD_BCN_ACCTSEL_T asel \n");
177 sqlText.append("SET org_fin_coa_cd = \n");
178 sqlText.append(" (SELECT h1.org_fin_coa_cd \n");
179 sqlText.append(" FROM LD_BCN_ACCT_ORG_HIER_T h1 \n");
180 sqlText.append(" WHERE asel.univ_fiscal_yr = h1.univ_fiscal_yr \n");
181 sqlText.append(" AND asel.fin_coa_cd = h1.fin_coa_cd \n");
182 sqlText.append(" AND asel.account_nbr = h1.account_nbr \n");
183 sqlText.append(" AND asel.org_level_cd = h1.org_level_cd), \n");
184 sqlText.append(" org_cd = \n");
185 sqlText.append(" (SELECT h1.org_cd \n");
186 sqlText.append(" FROM LD_BCN_ACCT_ORG_HIER_T h1 \n");
187 sqlText.append(" WHERE asel.univ_fiscal_yr = h1.univ_fiscal_yr \n");
188 sqlText.append(" AND asel.fin_coa_cd = h1.fin_coa_cd \n");
189 sqlText.append(" AND asel.account_nbr = h1.account_nbr \n");
190 sqlText.append(" AND asel.org_level_cd = h1.org_level_cd) \n");
191 sqlText.append("WHERE asel.person_unvl_id = ? \n");
192 sqlText.append("AND EXISTS (SELECT * \n");
193 sqlText.append(" FROM LD_BCN_ACCT_ORG_HIER_T h2 \n");
194 sqlText.append(" WHERE asel.univ_fiscal_yr = h2.univ_fiscal_yr \n");
195 sqlText.append(" AND asel.fin_coa_cd = h2.fin_coa_cd \n");
196 sqlText.append(" AND asel.account_nbr = h2.account_nbr \n");
197 sqlText.append(" AND asel.org_level_cd = h2.org_level_cd) \n");
198
199 buildAccountManagerDelegateListTemplates[1] = sqlText.toString();
200 sqlText.delete(0, sqlText.length());
201
202 // assign org for accounts at level 0
203 sqlText.append("UPDATE LD_BCN_ACCTSEL_T asel \n");
204 sqlText.append("SET org_fin_coa_cd = \n");
205 sqlText.append(" (SELECT r1.rpts_to_fin_coa_cd \n");
206 sqlText.append(" FROM LD_BCN_ACCT_RPTS_T r1 \n");
207 sqlText.append(" WHERE asel.fin_coa_cd = r1.fin_coa_cd \n");
208 sqlText.append(" AND asel.account_nbr = r1.account_nbr), \n");
209 sqlText.append(" org_cd = \n");
210 sqlText.append(" (SELECT r1.rpts_to_org_cd \n");
211 sqlText.append(" FROM LD_BCN_ACCT_RPTS_T r1 \n");
212 sqlText.append(" WHERE asel.fin_coa_cd = r1.fin_coa_cd \n");
213 sqlText.append(" AND asel.account_nbr = r1.account_nbr) \n");
214 sqlText.append("WHERE asel.person_unvl_id = ? \n");
215 sqlText.append(" AND asel.org_level_cd = 0 \n");
216 sqlText.append(" AND EXISTS (select * \n");
217 sqlText.append(" FROM LD_BCN_ACCT_RPTS_T r2 \n");
218 sqlText.append(" WHERE asel.fin_coa_cd = r2.fin_coa_cd \n");
219 sqlText.append(" AND asel.account_nbr = r2.account_nbr) \n");
220
221 buildAccountManagerDelegateListTemplates[2] = sqlText.toString();
222 sqlText.delete(0, sqlText.length());
223 }
224
225 /**
226 * @see org.kuali.kfs.module.bc.document.dataaccess.OrganizationBCDocumentSearchDao#buildAccountSelectPullList(java.lang.String,
227 * java.lang.Integer)
228 */
229 public int buildAccountSelectPullList(String principalName, Integer universityFiscalYear) {
230 LOG.debug("buildAccountSelectPullList() started");
231
232 int rowsAffected = getSimpleJdbcTemplate().update(buildAccountSelectPullListTemplates[0], principalName, universityFiscalYear, principalName, universityFiscalYear);
233 return rowsAffected;
234 }
235
236 /**
237 * @see org.kuali.kfs.module.bc.document.dataaccess.OrganizationBCDocumentSearchDao#buildBudgetedAccountsAbovePointsOfView(java.lang.String,
238 * java.lang.Integer, java.lang.String, java.lang.String)
239 */
240 public int buildBudgetedAccountsAbovePointsOfView(String principalName, Integer universityFiscalYear, String chartOfAccountsCode, String organizationCode) {
241 LOG.debug("buildBudgetedAccountsAbovePointsOfView() started");
242
243 int rowsAffected = getSimpleJdbcTemplate().update(buildBudgetedAccountsAbovePointsOfView[0], principalName, principalName, universityFiscalYear, chartOfAccountsCode, organizationCode);
244 return rowsAffected;
245 }
246
247 /**
248 * @see org.kuali.kfs.module.bc.document.dataaccess.OrganizationBCDocumentSearchDao#buildAccountManagerDelegateList(java.lang.String,
249 * java.lang.Integer)
250 */
251 public int buildAccountManagerDelegateList(String principalName, Integer universityFiscalYear) {
252 int rowsAffected = getSimpleJdbcTemplate().update(buildAccountManagerDelegateListTemplates[0], principalName, universityFiscalYear, principalName, KFSConstants.FinancialDocumentTypeCodes.BUDGET_CONSTRUCTION, BCConstants.DOCUMENT_TYPE_CODE_ALL, principalName, universityFiscalYear, principalName);
253
254 // update level chart and org
255 getSimpleJdbcTemplate().update(buildAccountManagerDelegateListTemplates[1], principalName);
256 getSimpleJdbcTemplate().update(buildAccountManagerDelegateListTemplates[2], principalName);
257
258 return rowsAffected;
259 }
260
261 /**
262 * @see org.kuali.kfs.module.bc.document.dataaccess.OrganizationBCDocumentSearchDao#cleanAccountSelectPullList(java.lang.String)
263 */
264 public void cleanAccountSelectPullList(String principalName) {
265 clearTempTableByUnvlId("LD_BCN_ACCTSEL_T", "PERSON_UNVL_ID", principalName);
266 }
267
268 /**
269 * Gets the dataDictionaryService attribute.
270 * @return Returns the dataDictionaryService.
271 */
272 public DataDictionaryService getDataDictionaryService() {
273 return dataDictionaryService;
274 }
275
276 /**
277 * Sets the dataDictionaryService attribute value.
278 * @param dataDictionaryService The dataDictionaryService to set.
279 */
280 public void setDataDictionaryService(DataDictionaryService dataDictionaryService) {
281 this.dataDictionaryService = dataDictionaryService;
282 }
283
284 }
285