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.BCConstants.OrgSelControlOption;
020 import org.kuali.kfs.module.bc.document.dataaccess.BudgetOrganizationPushPullDao;
021 import org.kuali.rice.kns.util.Guid;
022
023 /**
024 * Implements BudgetOrganizationPushPullDao using raw SQL and populating temporary tables with the potential set of documents to
025 * push down or pull up. The temporary tables are then used to drive the entire push down or pull up process. First, an attempt is
026 * made to place budget locks on each document. Successfully locked documents are then pushed down or pulled up by setting the
027 * associated BudgetConstructionHeader (LD_BCNSTR_HDR_T) row with the appropriate level attribute values and releasing the locks.
028 */
029 public class BudgetOrganizationPushPullDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetOrganizationPushPullDao {
030
031 private static String[] pullupSelectedOrganizationDocumentsTemplates = new String[8];
032 private static String[] pushdownSelectedOrganizationDocumentsTemplates = new String[11];
033 private static String[] accountSelectBudgetedDocumentsPullUpTemplates = new String[2];
034 private static String[] accountSelectBudgetedDocumentsPushDownTemplates = new String[1];
035
036 public BudgetOrganizationPushPullDaoJdbc() {
037
038 // get accounts for selected orgs and attach the pull_flag setting
039 StringBuilder sqlText = new StringBuilder(1000);
040 sqlText.append("INSERT INTO LD_BCN_DOC_PULLUP01_MT \n");
041 sqlText.append(" (SESID, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SORG_FIN_COA_CD, SORG_CD, PULL_FLAG) \n");
042 sqlText.append("SELECT ?, hier.univ_fiscal_yr, hier.fin_coa_cd, hier.account_nbr, \n");
043 sqlText.append(" hier.org_fin_coa_cd, hier.org_cd, pull.pull_flag \n");
044 sqlText.append("FROM LD_BCN_PULLUP_T pull, LD_BCN_ACCT_ORG_HIER_T hier \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 pullupSelectedOrganizationDocumentsTemplates[0] = sqlText.toString();
051 sqlText.delete(0, sqlText.length());
052
053 // get the point of view record for each account and attach pull_flag again
054 sqlText.append("INSERT INTO LD_BCN_DOC_PULLUP02_MT \n");
055 sqlText.append(" (SESID, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, ORG_LEVEL_CD, \n");
056 sqlText.append(" ORG_FIN_COA_CD, ORG_CD, SORG_FIN_COA_CD, SORG_CD, PULL_FLAG) \n");
057 sqlText.append("SELECT ?, hier.univ_fiscal_yr, hier.fin_coa_cd, hier.account_nbr, hier.org_level_cd, \n");
058 sqlText.append(" hier.org_fin_coa_cd, hier.org_cd, sel.sorg_fin_coa_cd, sel.sorg_cd, sel.pull_flag \n");
059 sqlText.append("FROM LD_BCN_ACCT_ORG_HIER_T hier, LD_BCN_DOC_PULLUP01_MT sel \n");
060 sqlText.append("WHERE sel.SESID = ? \n");
061 sqlText.append(" AND hier.org_fin_coa_cd = ? \n");
062 sqlText.append(" AND hier.org_cd = ? \n");
063 sqlText.append(" AND hier.univ_fiscal_yr = sel.univ_fiscal_yr \n");
064 sqlText.append(" AND hier.fin_coa_cd = sel.fin_coa_cd \n");
065 sqlText.append(" AND hier.account_nbr = sel.account_nbr \n");
066 pullupSelectedOrganizationDocumentsTemplates[1] = sqlText.toString();
067 sqlText.delete(0, sqlText.length());
068
069 // populate list of accounts to update based on pull_flag setting
070 // doc numbers and acct,subacct pairs are candidate keys
071 // build list with doc numbers since it starts the clustered index
072
073 // get list for BOTH direct reports and org subtree pull_flag = 3
074 sqlText.append("INSERT INTO LD_BCN_DOC_PULLUP03_MT \n");
075 sqlText.append(" (SESID, FDOC_NBR, ORG_LEVEL_CD, ORG_FIN_COA_CD, ORG_CD) \n");
076 sqlText.append("SELECT ?, head.fdoc_nbr, pv.org_level_cd, pv.org_fin_coa_cd, pv.org_cd \n");
077 sqlText.append("FROM LD_BCN_DOC_PULLUP02_MT pv, LD_BCNSTR_HDR_T head \n");
078 sqlText.append("WHERE pv.SESID = ? \n");
079 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
080 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
081 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
082 sqlText.append(" AND head.org_level_cd < pv.org_level_cd \n");
083 sqlText.append(" AND pv.pull_flag = ? \n");
084 pullupSelectedOrganizationDocumentsTemplates[2] = sqlText.toString();
085 sqlText.delete(0, sqlText.length());
086
087
088 // add to list for direct reports only pull_flag = 1
089 sqlText.append("INSERT INTO LD_BCN_DOC_PULLUP03_MT \n");
090 sqlText.append(" (SESID, FDOC_NBR, ORG_LEVEL_CD, ORG_FIN_COA_CD, ORG_CD) \n");
091 sqlText.append("SELECT ?, head.fdoc_nbr, pv.org_level_cd, pv.org_fin_coa_cd, pv.org_cd \n");
092 sqlText.append("FROM LD_BCN_DOC_PULLUP02_MT pv, LD_BCN_ACCT_RPTS_T bar, LD_BCNSTR_HDR_T head \n");
093 sqlText.append("WHERE pv.SESID = ? \n");
094 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
095 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
096 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
097 sqlText.append(" AND head.org_level_cd < pv.org_level_cd \n");
098 sqlText.append(" AND pv.pull_flag = ? \n");
099 sqlText.append(" AND bar.fin_coa_cd = pv.fin_coa_cd \n");
100 sqlText.append(" AND bar.account_nbr = pv.account_nbr \n");
101 sqlText.append(" AND bar.rpts_to_fin_coa_cd = pv.sorg_fin_coa_cd \n");
102 sqlText.append(" AND bar.rpts_to_org_cd = pv.sorg_cd \n");
103 pullupSelectedOrganizationDocumentsTemplates[3] = sqlText.toString();
104 sqlText.delete(0, sqlText.length());
105
106
107 // add to list for org subtree only - pull_flag = 2
108 sqlText.append("INSERT INTO LD_BCN_DOC_PULLUP03_MT \n");
109 sqlText.append(" (SESID, FDOC_NBR, ORG_LEVEL_CD, ORG_FIN_COA_CD, ORG_CD) \n");
110 sqlText.append("SELECT ?, head.fdoc_nbr, pv.org_level_cd, pv.org_fin_coa_cd, pv.org_cd \n");
111 sqlText.append("FROM LD_BCN_DOC_PULLUP02_MT pv, LD_BCN_ACCT_RPTS_T bar, LD_BCNSTR_HDR_T head \n");
112 sqlText.append("WHERE pv.SESID = ? \n");
113 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
114 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
115 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
116 sqlText.append(" AND head.org_level_cd < pv.org_level_cd \n");
117 sqlText.append(" AND pv.pull_flag = ? \n");
118 sqlText.append(" AND bar.fin_coa_cd = pv.fin_coa_cd \n");
119 sqlText.append(" AND bar.account_nbr = pv.account_nbr \n");
120 sqlText.append(" AND (bar.rpts_to_org_cd <> pv.sorg_cd \n");
121 sqlText.append(" OR bar.rpts_to_fin_coa_cd <> pv.sorg_fin_coa_cd) \n");
122 pullupSelectedOrganizationDocumentsTemplates[4] = sqlText.toString();
123 sqlText.delete(0, sqlText.length());
124
125
126 // issue budget locks
127 sqlText.append("UPDATE LD_BCNSTR_HDR_T head \n");
128 sqlText.append("SET bdgt_lock_usr_id = ? \n");
129 sqlText.append("WHERE exists \n");
130 sqlText.append(" (SELECT * \n");
131 sqlText.append(" FROM LD_BCN_DOC_PULLUP03_MT ul \n");
132 sqlText.append(" WHERE ul.SESID = ? \n");
133 sqlText.append(" AND head.fdoc_nbr = ul.fdoc_nbr \n");
134 sqlText.append(" AND head.bdgt_lock_usr_id IS NULL) \n");
135 pullupSelectedOrganizationDocumentsTemplates[5] = sqlText.toString();
136 sqlText.delete(0, sqlText.length());
137
138
139 // release budget locks where funding locks found - adhere to BC lock tree protocol
140 sqlText.append("UPDATE LD_BCNSTR_HDR_T head \n");
141 sqlText.append("SET bdgt_lock_usr_id = NULL \n");
142 sqlText.append("WHERE exists \n");
143 sqlText.append(" (SELECT * \n");
144 sqlText.append(" FROM LD_BCN_DOC_PULLUP03_MT ul \n");
145 sqlText.append(" WHERE ul.SESID = ? \n");
146 sqlText.append(" AND head.fdoc_nbr = ul.fdoc_nbr \n");
147 sqlText.append(" AND head.bdgt_lock_usr_id = ? \n");
148 sqlText.append(" AND EXISTS \n");
149 sqlText.append(" (SELECT * \n");
150 sqlText.append(" FROM LD_BCN_FND_LOCK_T fl \n");
151 sqlText.append(" WHERE fl.univ_fiscal_yr = head.univ_fiscal_yr \n");
152 sqlText.append(" AND fl.fin_coa_cd = head.fin_coa_cd \n");
153 sqlText.append(" AND fl.account_nbr = head.account_nbr \n");
154 sqlText.append(" AND fl.sub_acct_nbr = head.sub_acct_nbr)) \n");
155 pullupSelectedOrganizationDocumentsTemplates[6] = sqlText.toString();
156 sqlText.delete(0, sqlText.length());
157
158 // pullup and release budget locks - SQL92 version
159 sqlText.append("UPDATE LD_BCNSTR_HDR_T head \n");
160 sqlText.append("SET org_level_cd = \n");
161 sqlText.append(" (SELECT ul.org_level_cd \n");
162 sqlText.append(" FROM LD_BCN_DOC_PULLUP03_MT ul \n");
163 sqlText.append(" WHERE ul.SESID = ? \n");
164 sqlText.append(" AND head.fdoc_nbr = ul.fdoc_nbr \n");
165 sqlText.append(" AND head.bdgt_lock_usr_id = ?), \n");
166 sqlText.append(" org_coa_of_lvl_cd = \n");
167 sqlText.append(" (SELECT ul.org_fin_coa_cd \n");
168 sqlText.append(" FROM LD_BCN_DOC_PULLUP03_MT ul \n");
169 sqlText.append(" WHERE ul.SESID = ? \n");
170 sqlText.append(" AND head.fdoc_nbr = ul.fdoc_nbr \n");
171 sqlText.append(" AND head.bdgt_lock_usr_id = ?), \n");
172 sqlText.append(" org_of_lvl_cd = \n");
173 sqlText.append(" (SELECT ul.org_cd \n");
174 sqlText.append(" FROM LD_BCN_DOC_PULLUP03_MT ul \n");
175 sqlText.append(" WHERE ul.SESID = ? \n");
176 sqlText.append(" AND head.fdoc_nbr = ul.fdoc_nbr \n");
177 sqlText.append(" AND head.bdgt_lock_usr_id = ?), \n");
178 sqlText.append(" bdgt_lock_usr_id = NULL \n");
179 sqlText.append("WHERE exists \n");
180 sqlText.append(" (SELECT * \n");
181 sqlText.append(" FROM LD_BCN_DOC_PULLUP03_MT ul \n");
182 sqlText.append(" WHERE ul.SESID = ? \n");
183 sqlText.append(" AND head.fdoc_nbr = ul.fdoc_nbr \n");
184 sqlText.append(" AND head.bdgt_lock_usr_id = ?) \n");
185 pullupSelectedOrganizationDocumentsTemplates[7] = sqlText.toString();
186 sqlText.delete(0, sqlText.length());
187
188 // Pushdown steps start here
189
190 // get accounts for selected orgs. and attach the pull_flag setting
191 sqlText.append("INSERT INTO LD_BCN_DOC_PUSHDOWN01_MT \n");
192 sqlText.append(" (SESID, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, ORG_LEVEL_CD, ORG_FIN_COA_CD, ORG_CD, PULL_FLAG) \n");
193 sqlText.append("SELECT ?, hier.univ_fiscal_yr, hier.fin_coa_cd, hier.account_nbr, hier.org_level_cd, \n");
194 sqlText.append(" hier.org_fin_coa_cd, hier.org_cd, push.pull_flag \n");
195 sqlText.append("FROM LD_BCN_PULLUP_T push, LD_BCN_ACCT_ORG_HIER_T hier \n");
196 sqlText.append("WHERE push.pull_flag > 0 \n");
197 sqlText.append(" AND push.person_unvl_id = ? \n");
198 sqlText.append(" AND hier.univ_fiscal_yr = ? \n");
199 sqlText.append(" AND hier.org_fin_coa_cd = push.fin_coa_cd \n");
200 sqlText.append(" AND hier.org_cd = push.org_cd \n");
201 pushdownSelectedOrganizationDocumentsTemplates[0] = sqlText.toString();
202 sqlText.delete(0, sqlText.length());
203
204 // get selection coa, orgs and levels at point of view */
205 sqlText.append("INSERT INTO LD_BCN_DOC_PUSHDOWN02_MT \n");
206 sqlText.append(" (SESID, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, ORG_LEVEL_CD, \n");
207 sqlText.append(" SEL_ORG_LVL, SEL_ORGFIN_COA, SEL_ORG, SEL_PULLFLAG) \n");
208 sqlText.append("SELECT DISTINCT ?, hier.univ_fiscal_yr, hier.fin_coa_cd, hier.account_nbr, hier.org_level_cd, \n");
209 sqlText.append(" sel.org_level_cd, sel.org_fin_coa_cd, sel.org_cd, sel.pull_flag \n");
210 sqlText.append("FROM LD_BCN_ACCT_ORG_HIER_T hier, LD_BCN_DOC_PUSHDOWN01_MT sel \n");
211 sqlText.append("WHERE sel.SESID = ? \n");
212 sqlText.append(" AND hier.org_fin_coa_cd = ? \n");
213 sqlText.append(" AND hier.org_cd = ? \n");
214 sqlText.append(" AND hier.univ_fiscal_yr = sel.univ_fiscal_yr \n");
215 sqlText.append(" AND hier.fin_coa_cd = sel.fin_coa_cd \n");
216 sqlText.append(" AND hier.account_nbr = sel.account_nbr \n");
217 pushdownSelectedOrganizationDocumentsTemplates[1] = sqlText.toString();
218 sqlText.delete(0, sqlText.length());
219
220 // add the level 1 coa and org to the selection list
221 sqlText.append("INSERT INTO LD_BCN_DOC_PUSHDOWN03_MT \n");
222 sqlText.append(" (SESID,UNIV_FISCAL_YR,FIN_COA_CD,ACCOUNT_NBR,ORG_LEVEL_CD, \n");
223 sqlText.append(" SEL_ORG_LVL,SEL_ORGFIN_COA,SEL_ORG,SEL_PULLFLAG,LONE_ORGFIN_COA,LONE_ORG) \n");
224 sqlText.append("SELECT DISTINCT ?, sel.univ_fiscal_yr, sel.fin_coa_cd, sel.account_nbr, sel.org_level_cd, \n");
225 sqlText.append(" sel.sel_org_lvl, sel.sel_orgfin_coa, sel.sel_org, sel.sel_pullflag, hier.org_fin_coa_cd, hier.org_cd \n");
226 sqlText.append("FROM LD_BCN_ACCT_ORG_HIER_T hier, LD_BCN_DOC_PUSHDOWN02_MT sel \n");
227 sqlText.append("WHERE sel.SESID = ? \n");
228 sqlText.append(" AND hier.univ_fiscal_yr = sel.univ_fiscal_yr \n");
229 sqlText.append(" AND hier.fin_coa_cd = sel.fin_coa_cd \n");
230 sqlText.append(" AND hier.account_nbr = sel.account_nbr \n");
231 sqlText.append(" AND hier.org_level_cd = 1 \n");
232 pushdownSelectedOrganizationDocumentsTemplates[2] = sqlText.toString();
233 sqlText.delete(0, sqlText.length());
234
235 // Note: (may need to/should as we scale) replace fy,coa,acct,sacct with fdoc_nbr in LD_BCN_DOC_PUSHDOWN04_MT
236 // and use it in later steps instead of the candidate key
237 // not sure why it uses the candidate key in the first place, may have been due to
238 // the use of origin code as the first field in the pkey index, a secondary index exists on coa,acct.
239 // Since origin code is no longer used fdoc_nbr may be the better choice
240 // since it now starts the pkey index in KFS
241
242 /* get list of accounts to push for sel_pullflag in 1,3,4,5 */
243 sqlText.append("INSERT INTO LD_BCN_DOC_PUSHDOWN04_MT \n");
244 sqlText.append(" (SESID, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, ORG_LEVEL_CD, \n");
245 sqlText.append(" SEL_ORG_LVL, SEL_ORGFIN_COA, SEL_ORG, SEL_PULLFLAG, LONE_ORGFIN_COA, LONE_ORG) \n");
246 sqlText.append("SELECT ?, head.univ_fiscal_yr, head.fin_coa_cd, head.account_nbr, head.sub_acct_nbr, pv.org_level_cd, \n");
247 sqlText.append(" pv.sel_org_lvl, pv.sel_orgfin_coa, pv.sel_org, pv.sel_pullflag, pv.lone_orgfin_coa, pv.lone_org \n");
248 sqlText.append("FROM LD_BCN_DOC_PUSHDOWN03_MT pv, LD_BCNSTR_HDR_T head \n");
249 sqlText.append("WHERE pv.SESID = ? \n");
250 sqlText.append(" AND pv.sel_pullflag IN (?, ?, ?, ?) \n");
251 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
252 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
253 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
254 sqlText.append(" AND head.org_level_cd = pv.org_level_cd \n");
255 pushdownSelectedOrganizationDocumentsTemplates[3] = sqlText.toString();
256 sqlText.delete(0, sqlText.length());
257
258 // get list of accounts to push for sel_pullflag = 2
259 sqlText.append("INSERT INTO LD_BCN_DOC_PUSHDOWN04_MT \n");
260 sqlText.append(" (SESID, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, ORG_LEVEL_CD, \n");
261 sqlText.append(" SEL_ORG_LVL, SEL_ORGFIN_COA, SEL_ORG, SEL_PULLFLAG, LONE_ORGFIN_COA, LONE_ORG) \n");
262 sqlText.append("SELECT ?, head.univ_fiscal_yr, head.fin_coa_cd, head.account_nbr, head.sub_acct_nbr, pv.org_level_cd, \n");
263 sqlText.append(" pv.sel_org_lvl, pv.sel_orgfin_coa, pv.sel_org, pv.sel_pullflag, pv.lone_orgfin_coa, pv.lone_org \n");
264 sqlText.append("FROM LD_BCN_DOC_PUSHDOWN03_MT pv, LD_BCNSTR_HDR_T head, LD_BCN_ACCT_RPTS_T rpts \n");
265 sqlText.append("WHERE pv.SESID = ? \n");
266 sqlText.append(" AND pv.sel_pullflag = ? \n");
267 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
268 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
269 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
270 sqlText.append(" AND head.org_level_cd = pv.org_level_cd \n");
271 sqlText.append(" AND pv.fin_coa_cd = rpts.fin_coa_cd \n");
272 sqlText.append(" AND pv.account_nbr = rpts.account_nbr \n");
273 sqlText.append(" AND pv.sel_orgfin_coa = rpts.rpts_to_fin_coa_cd \n");
274 sqlText.append(" AND pv.sel_org = rpts.rpts_to_org_cd \n");
275 pushdownSelectedOrganizationDocumentsTemplates[4] = sqlText.toString();
276 sqlText.delete(0, sqlText.length());
277
278
279 // issue budget locks on the set to update
280 sqlText.append("UPDATE LD_BCNSTR_HDR_T head \n");
281 sqlText.append("SET bdgt_lock_usr_id = ? \n");
282 sqlText.append("WHERE exists \n");
283 sqlText.append(" (SELECT * \n");
284 sqlText.append(" FROM LD_BCN_DOC_PUSHDOWN04_MT pv \n");
285 sqlText.append(" WHERE pv.SESID = ? \n");
286 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
287 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
288 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
289 sqlText.append(" AND head.sub_acct_nbr = pv.sub_acct_nbr \n");
290 sqlText.append(" AND head.bdgt_lock_usr_id IS NULL) \n");
291 pushdownSelectedOrganizationDocumentsTemplates[5] = sqlText.toString();
292 sqlText.delete(0, sqlText.length());
293
294 /* release budget locks where funding locks are found */
295 sqlText.append("UPDATE LD_BCNSTR_HDR_T head \n");
296 sqlText.append("SET bdgt_lock_usr_id = NULL \n");
297 sqlText.append("WHERE exists \n");
298 sqlText.append(" (SELECT * \n");
299 sqlText.append(" FROM LD_BCN_DOC_PUSHDOWN04_MT pv \n");
300 sqlText.append(" WHERE pv.SESID = ? \n");
301 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
302 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
303 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
304 sqlText.append(" AND head.sub_acct_nbr = pv.sub_acct_nbr \n");
305 sqlText.append(" AND head.bdgt_lock_usr_id = ? \n");
306 sqlText.append(" AND EXISTS \n");
307 sqlText.append(" (SELECT * \n");
308 sqlText.append(" FROM LD_BCN_FND_LOCK_T fl \n");
309 sqlText.append(" WHERE fl.univ_fiscal_yr = head.univ_fiscal_yr \n");
310 sqlText.append(" AND fl.fin_coa_cd = head.fin_coa_cd \n");
311 sqlText.append(" AND fl.account_nbr = head.account_nbr \n");
312 sqlText.append(" AND fl.sub_acct_nbr = head.sub_acct_nbr)) \n");
313 pushdownSelectedOrganizationDocumentsTemplates[6] = sqlText.toString();
314 sqlText.delete(0, sqlText.length());
315
316 // push the selected org's direct report accts to 0 and unlock as we go - sel_pullflag in 2, 3
317 sqlText.append("UPDATE LD_BCNSTR_HDR_T head \n");
318 sqlText.append("SET org_level_cd = 0, \n");
319 sqlText.append(" org_coa_of_lvl_cd = NULL, \n");
320 sqlText.append(" org_of_lvl_cd = NULL, \n");
321 sqlText.append(" bdgt_lock_usr_id = NULL \n");
322 sqlText.append("WHERE EXISTS \n");
323 sqlText.append(" (SELECT * \n");
324 sqlText.append(" FROM LD_BCN_DOC_PUSHDOWN04_MT pv, \n");
325 sqlText.append(" LD_BCN_ACCT_RPTS_T rpts \n");
326 sqlText.append(" WHERE pv.SESID = ? \n");
327 sqlText.append(" AND pv.sel_pullflag IN (?, ?) \n");
328 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
329 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
330 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
331 sqlText.append(" AND head.sub_acct_nbr = pv.sub_acct_nbr \n");
332 sqlText.append(" AND head.bdgt_lock_usr_id = ? \n");
333 sqlText.append(" AND pv.fin_coa_cd = rpts.fin_coa_cd \n");
334 sqlText.append(" AND pv.account_nbr = rpts.account_nbr \n");
335 sqlText.append(" AND pv.sel_orgfin_coa = rpts.rpts_to_fin_coa_cd \n");
336 sqlText.append(" AND pv.sel_org = rpts.rpts_to_org_cd) \n");
337 pushdownSelectedOrganizationDocumentsTemplates[7] = sqlText.toString();
338 sqlText.delete(0, sqlText.length());
339
340 // push the selected org(s) subtree accounts to its (selected org's) level - sel_pullflag IN (1, 3)
341 // unlock as we go */
342 sqlText.append("UPDATE LD_BCNSTR_HDR_T head \n");
343 sqlText.append("SET org_level_cd = \n");
344 sqlText.append(" (SELECT pv.sel_org_lvl \n");
345 sqlText.append(" FROM LD_BCN_DOC_PUSHDOWN04_MT pv \n");
346 sqlText.append(" WHERE pv.SESID = ? \n");
347 sqlText.append(" AND pv.sel_pullflag IN (?, ?) \n");
348 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
349 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
350 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
351 sqlText.append(" AND head.sub_acct_nbr = pv.sub_acct_nbr \n");
352 sqlText.append(" AND head.bdgt_lock_usr_id = ?), \n");
353 sqlText.append(" org_coa_of_lvl_cd = \n");
354 sqlText.append(" (SELECT pv.sel_orgfin_coa \n");
355 sqlText.append(" FROM LD_BCN_DOC_PUSHDOWN04_MT pv \n");
356 sqlText.append(" WHERE pv.SESID = ? \n");
357 sqlText.append(" AND pv.sel_pullflag IN (?, ?) \n");
358 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
359 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
360 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
361 sqlText.append(" AND head.sub_acct_nbr = pv.sub_acct_nbr \n");
362 sqlText.append(" AND head.bdgt_lock_usr_id = ?), \n");
363 sqlText.append(" org_of_lvl_cd = \n");
364 sqlText.append(" (SELECT pv.sel_org \n");
365 sqlText.append(" FROM LD_BCN_DOC_PUSHDOWN04_MT pv \n");
366 sqlText.append(" WHERE pv.SESID = ? \n");
367 sqlText.append(" AND pv.sel_pullflag IN (?, ?) \n");
368 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
369 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
370 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
371 sqlText.append(" AND head.sub_acct_nbr = pv.sub_acct_nbr \n");
372 sqlText.append(" AND head.bdgt_lock_usr_id = ?), \n");
373 sqlText.append(" bdgt_lock_usr_id = NULL \n");
374 sqlText.append("WHERE EXISTS \n");
375 sqlText.append(" (SELECT * \n");
376 sqlText.append(" FROM LD_BCN_DOC_PUSHDOWN04_MT pv \n");
377 sqlText.append(" WHERE pv.SESID = ? \n");
378 sqlText.append(" AND pv.sel_pullflag IN (?, ?) \n");
379 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
380 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
381 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
382 sqlText.append(" AND head.sub_acct_nbr = pv.sub_acct_nbr \n");
383 sqlText.append(" AND head.bdgt_lock_usr_id = ?) \n");
384 pushdownSelectedOrganizationDocumentsTemplates[8] = sqlText.toString();
385 sqlText.delete(0, sqlText.length());
386
387 // push the selected org(s) subtree accts to level 1 - sel_pullflag = 4
388 // unlock as we go
389 sqlText.append("UPDATE LD_BCNSTR_HDR_T head \n");
390 sqlText.append("SET org_level_cd = 1, \n");
391 sqlText.append(" org_coa_of_lvl_cd = \n");
392 sqlText.append(" (SELECT pv.lone_orgfin_coa \n");
393 sqlText.append(" FROM LD_BCN_DOC_PUSHDOWN04_MT pv \n");
394 sqlText.append(" WHERE pv.SESID = ? \n");
395 sqlText.append(" AND pv.sel_pullflag = ? \n");
396 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
397 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
398 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
399 sqlText.append(" AND head.sub_acct_nbr = pv.sub_acct_nbr \n");
400 sqlText.append(" AND head.bdgt_lock_usr_id = ?), \n");
401 sqlText.append(" org_of_lvl_cd = \n");
402 sqlText.append(" (SELECT pv.lone_org \n");
403 sqlText.append(" FROM LD_BCN_DOC_PUSHDOWN04_MT pv \n");
404 sqlText.append(" WHERE pv.SESID = ? \n");
405 sqlText.append(" AND pv.sel_pullflag = ? \n");
406 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
407 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
408 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
409 sqlText.append(" AND head.sub_acct_nbr = pv.sub_acct_nbr \n");
410 sqlText.append(" AND head.bdgt_lock_usr_id = ?), \n");
411 sqlText.append(" bdgt_lock_usr_id = NULL \n");
412 sqlText.append("WHERE EXISTS \n");
413 sqlText.append(" (SELECT * \n");
414 sqlText.append(" FROM LD_BCN_DOC_PUSHDOWN04_MT pv \n");
415 sqlText.append(" WHERE pv.SESID = ? \n");
416 sqlText.append(" AND pv.sel_pullflag = ? \n");
417 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
418 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
419 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
420 sqlText.append(" AND head.sub_acct_nbr = pv.sub_acct_nbr \n");
421 sqlText.append(" AND head.bdgt_lock_usr_id = ?) \n");
422 pushdownSelectedOrganizationDocumentsTemplates[9] = sqlText.toString();
423 sqlText.delete(0, sqlText.length());
424
425
426 // push the selected org(s) subtree accts to level 0 - sel_pullflag = 5
427 // unlock as we go */
428 sqlText.append("UPDATE LD_BCNSTR_HDR_T head \n");
429 sqlText.append("SET org_level_cd = 0, \n");
430 sqlText.append(" org_coa_of_lvl_cd = NULL, \n");
431 sqlText.append(" org_of_lvl_cd = NULL, \n");
432 sqlText.append(" bdgt_lock_usr_id = NULL \n");
433 sqlText.append("WHERE EXISTS \n");
434 sqlText.append(" (SELECT * \n");
435 sqlText.append(" FROM LD_BCN_DOC_PUSHDOWN04_MT pv \n");
436 sqlText.append(" WHERE pv.SESID = ? \n");
437 sqlText.append(" AND pv.sel_pullflag = ? \n");
438 sqlText.append(" AND head.univ_fiscal_yr = pv.univ_fiscal_yr \n");
439 sqlText.append(" AND head.fin_coa_cd = pv.fin_coa_cd \n");
440 sqlText.append(" AND head.account_nbr = pv.account_nbr \n");
441 sqlText.append(" AND head.sub_acct_nbr = pv.sub_acct_nbr \n");
442 sqlText.append(" AND head.bdgt_lock_usr_id = ?) \n");
443 pushdownSelectedOrganizationDocumentsTemplates[10] = sqlText.toString();
444 sqlText.delete(0, sqlText.length());
445
446 // build list of budget documents for selected orgs and below user's point of view (documents that will be pulled up by org
447 // pullup)
448 sqlText.append("INSERT INTO LD_BCN_ACCTSEL_T \n");
449 sqlText.append("SELECT ?,\n");
450 sqlText.append(" head.univ_fiscal_yr,\n");
451 sqlText.append(" head.fin_coa_cd, \n");
452 sqlText.append(" head.account_nbr, \n");
453 sqlText.append(" head.sub_acct_nbr, \n");
454 sqlText.append(" head.fdoc_nbr, \n");
455 sqlText.append(" 1, \n");
456 sqlText.append(" head.org_level_cd, \n");
457 sqlText.append(" head.org_coa_of_lvl_cd, \n");
458 sqlText.append(" head.org_of_lvl_cd, \n");
459 sqlText.append(" fphd.fdoc_status_cd, \n");
460 sqlText.append(" '', \n");
461 sqlText.append(" fphd.temp_doc_fnl_dt \n");
462 sqlText.append("FROM LD_BCNSTR_HDR_T head, \n");
463 sqlText.append(" FS_DOC_HEADER_T fphd, \n");
464 sqlText.append(" (SELECT head2.fdoc_nbr \n");
465 sqlText.append(" FROM LD_BCNSTR_HDR_T head2, \n");
466 sqlText.append(" LD_BCN_PULLUP_T pull, \n");
467 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T hs, \n");
468 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T hp \n");
469 sqlText.append(" WHERE pull.pull_flag = ? \n");
470 sqlText.append(" AND pull.person_unvl_id = ? \n");
471 sqlText.append(" AND hs.univ_fiscal_yr = ? \n");
472 sqlText.append(" AND hs.org_fin_coa_cd = pull.fin_coa_cd \n");
473 sqlText.append(" AND hs.org_cd = pull.org_cd \n");
474 sqlText.append(" AND hp.org_fin_coa_cd = ? \n");
475 sqlText.append(" AND hp.org_cd= ? \n");
476 sqlText.append(" AND hp.univ_fiscal_yr = hs.univ_fiscal_yr \n");
477 sqlText.append(" AND hp.fin_coa_cd = hs.fin_coa_cd \n");
478 sqlText.append(" AND hp.account_nbr = hs.account_nbr \n");
479 sqlText.append(" AND head2.univ_fiscal_yr = hp.univ_fiscal_yr \n");
480 sqlText.append(" AND head2.fin_coa_cd = hp.fin_coa_cd \n");
481 sqlText.append(" AND head2.account_nbr = hp.account_nbr \n");
482 sqlText.append(" AND head2.org_level_cd < hp.org_level_cd \n");
483 sqlText.append(" UNION \n");
484 sqlText.append(" SELECT head2.fdoc_nbr \n");
485 sqlText.append(" FROM LD_BCNSTR_HDR_T head2, \n");
486 sqlText.append(" LD_BCN_PULLUP_T pull, \n");
487 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T hs, \n");
488 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T hp, \n");
489 sqlText.append(" LD_BCN_ACCT_RPTS_T bar \n");
490 sqlText.append(" WHERE pull.pull_flag = ? \n");
491 sqlText.append(" AND pull.person_unvl_id = ? \n");
492 sqlText.append(" AND hs.univ_fiscal_yr = ? \n");
493 sqlText.append(" AND hs.org_fin_coa_cd = pull.fin_coa_cd \n");
494 sqlText.append(" AND hs.org_cd = pull.org_cd \n");
495 sqlText.append(" AND hp.org_fin_coa_cd = ? \n");
496 sqlText.append(" AND hp.org_cd= ? \n");
497 sqlText.append(" AND hp.univ_fiscal_yr = hs.univ_fiscal_yr \n");
498 sqlText.append(" AND hp.fin_coa_cd = hs.fin_coa_cd \n");
499 sqlText.append(" AND hp.account_nbr = hs.account_nbr \n");
500 sqlText.append(" AND head2.univ_fiscal_yr = hp.univ_fiscal_yr \n");
501 sqlText.append(" AND head2.fin_coa_cd = hp.fin_coa_cd \n");
502 sqlText.append(" AND head2.account_nbr = hp.account_nbr \n");
503 sqlText.append(" AND head2.org_level_cd < hp.org_level_cd \n");
504 sqlText.append(" AND bar.fin_coa_cd = hs.fin_coa_cd \n");
505 sqlText.append(" AND bar.account_nbr = hs.account_nbr \n");
506 sqlText.append(" AND bar.rpts_to_fin_coa_cd = hs.org_fin_coa_cd \n");
507 sqlText.append(" AND bar.rpts_to_org_cd = hs.org_cd \n");
508 sqlText.append(" UNION \n");
509 sqlText.append(" SELECT head2.fdoc_nbr \n");
510 sqlText.append(" FROM LD_BCNSTR_HDR_T head2, \n");
511 sqlText.append(" LD_BCN_PULLUP_T pull, \n");
512 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T hs, \n");
513 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T hp, \n");
514 sqlText.append(" LD_BCN_ACCT_RPTS_T bar \n");
515 sqlText.append(" WHERE pull.pull_flag = ? \n");
516 sqlText.append(" AND pull.person_unvl_id = ? \n");
517 sqlText.append(" AND hs.univ_fiscal_yr = ? \n");
518 sqlText.append(" AND hs.org_fin_coa_cd = pull.fin_coa_cd \n");
519 sqlText.append(" AND hs.org_cd = pull.org_cd \n");
520 sqlText.append(" AND hp.org_fin_coa_cd = ? \n");
521 sqlText.append(" AND hp.org_cd= ? \n");
522 sqlText.append(" AND hp.univ_fiscal_yr = hs.univ_fiscal_yr \n");
523 sqlText.append(" AND hp.fin_coa_cd = hs.fin_coa_cd \n");
524 sqlText.append(" AND hp.account_nbr = hs.account_nbr \n");
525 sqlText.append(" AND head2.univ_fiscal_yr = hp.univ_fiscal_yr \n");
526 sqlText.append(" AND head2.fin_coa_cd = hp.fin_coa_cd \n");
527 sqlText.append(" AND head2.account_nbr = hp.account_nbr \n");
528 sqlText.append(" AND head2.org_level_cd < hp.org_level_cd \n");
529 sqlText.append(" AND bar.fin_coa_cd = hs.fin_coa_cd \n");
530 sqlText.append(" AND bar.account_nbr = hs.account_nbr \n");
531 sqlText.append(" AND (bar.rpts_to_fin_coa_cd <> hs.org_fin_coa_cd \n");
532 sqlText.append(" OR bar.rpts_to_org_cd <> hs.org_cd) \n");
533 sqlText.append(" ) s \n");
534 sqlText.append(" WHERE head.fdoc_nbr = s.fdoc_nbr \n");
535 sqlText.append(" AND fphd.fdoc_nbr = head.fdoc_nbr \n");
536
537 accountSelectBudgetedDocumentsPullUpTemplates[0] = sqlText.toString();
538 sqlText.delete(0, sqlText.length());
539
540 // update org for accounts at level 0
541 sqlText.append(" UPDATE LD_BCN_ACCTSEL_T \n");
542 sqlText.append(" SET org_fin_coa_cd = \n");
543 sqlText.append(" (SELECT rpts2.rpts_to_fin_coa_cd \n");
544 sqlText.append(" FROM LD_BCN_ACCT_RPTS_T rpts2 \n");
545 sqlText.append(" WHERE LD_BCN_ACCTSEL_T.fin_coa_cd = rpts2.fin_coa_cd \n");
546 sqlText.append(" AND LD_BCN_ACCTSEL_T.account_nbr = rpts2.account_nbr), \n");
547 sqlText.append(" org_cd = \n");
548 sqlText.append(" (SELECT rpts2.rpts_to_org_cd \n");
549 sqlText.append(" FROM LD_BCN_ACCT_RPTS_T rpts2 \n");
550 sqlText.append(" WHERE LD_BCN_ACCTSEL_T.fin_coa_cd = rpts2.fin_coa_cd \n");
551 sqlText.append(" AND LD_BCN_ACCTSEL_T.account_nbr = rpts2.account_nbr) \n");
552 sqlText.append(" WHERE LD_BCN_ACCTSEL_T.person_unvl_id = ? \n");
553 sqlText.append(" AND LD_BCN_ACCTSEL_T.univ_fiscal_yr = ? \n");
554 sqlText.append(" AND LD_BCN_ACCTSEL_T.org_level_cd = 0 \n");
555 sqlText.append(" AND EXISTS (select * \n");
556 sqlText.append(" FROM LD_BCN_ACCT_RPTS_T rpts \n");
557 sqlText.append(" WHERE LD_BCN_ACCTSEL_T.fin_coa_cd = rpts.fin_coa_cd \n");
558 sqlText.append(" AND LD_BCN_ACCTSEL_T.account_nbr = rpts.account_nbr) \n");
559
560 accountSelectBudgetedDocumentsPullUpTemplates[1] = sqlText.toString();
561 sqlText.delete(0, sqlText.length());
562
563 // build list of budget documents for selected orgs at user's point of view (documents that will be pushed down by org
564 // pushdown)
565 sqlText.append("INSERT INTO LD_BCN_ACCTSEL_T \n");
566 sqlText.append("SELECT ?, \n");
567 sqlText.append(" head.univ_fiscal_yr, \n");
568 sqlText.append(" head.fin_coa_cd, \n");
569 sqlText.append(" head.account_nbr, \n");
570 sqlText.append(" head.sub_acct_nbr, \n");
571 sqlText.append(" head.fdoc_nbr, \n");
572 sqlText.append(" 1, \n");
573 sqlText.append(" head.org_level_cd, \n");
574 sqlText.append(" head.org_coa_of_lvl_cd, \n");
575 sqlText.append(" head.org_of_lvl_cd, \n");
576 sqlText.append(" fphd.fdoc_status_cd, \n");
577 sqlText.append(" '', \n");
578 sqlText.append(" fphd.temp_doc_fnl_dt \n");
579 sqlText.append("FROM LD_BCN_PULLUP_T pull, \n");
580 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T hier, \n");
581 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T hier2, \n");
582 sqlText.append(" LD_BCNSTR_HDR_T head, \n");
583 sqlText.append(" FS_DOC_HEADER_T fphd \n");
584 sqlText.append("WHERE pull.pull_flag in (?,?,?,?) \n");
585 sqlText.append(" AND pull.person_unvl_id = ? \n");
586 sqlText.append(" AND hier.univ_fiscal_yr = ? \n");
587 sqlText.append(" AND hier.org_fin_coa_cd = pull.fin_coa_cd \n");
588 sqlText.append(" AND hier.org_cd = pull.org_cd \n");
589 sqlText.append(" AND hier.univ_fiscal_yr = hier2.univ_fiscal_yr \n");
590 sqlText.append(" AND hier.fin_coa_cd = hier2.fin_coa_cd \n");
591 sqlText.append(" AND hier.account_nbr = hier2.account_nbr \n");
592 sqlText.append(" AND hier2.org_fin_coa_cd = ? \n");
593 sqlText.append(" AND hier2.org_cd = ? \n");
594 sqlText.append(" AND head.univ_fiscal_yr = hier2.univ_fiscal_yr \n");
595 sqlText.append(" AND head.fin_coa_cd = hier2.fin_coa_cd \n");
596 sqlText.append(" AND head.account_nbr = hier2.account_nbr \n");
597 sqlText.append(" AND head.org_level_cd = hier2.org_level_cd \n");
598 sqlText.append(" AND fphd.fdoc_nbr = head.fdoc_nbr \n");
599 sqlText.append("UNION \n");
600 sqlText.append("SELECT ?, \n");
601 sqlText.append(" head.univ_fiscal_yr, \n");
602 sqlText.append(" head.fin_coa_cd, \n");
603 sqlText.append(" head.account_nbr, \n");
604 sqlText.append(" head.sub_acct_nbr, \n");
605 sqlText.append(" head.fdoc_nbr, \n");
606 sqlText.append(" 1, \n");
607 sqlText.append(" head.org_level_cd, \n");
608 sqlText.append(" head.org_coa_of_lvl_cd, \n");
609 sqlText.append(" head.org_of_lvl_cd, \n");
610 sqlText.append(" fphd.fdoc_status_cd, \n");
611 sqlText.append(" '', \n");
612 sqlText.append(" fphd.temp_doc_fnl_dt \n");
613 sqlText.append("FROM LD_BCN_PULLUP_T pull, \n");
614 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T hier, \n");
615 sqlText.append(" LD_BCN_ACCT_ORG_HIER_T hier2, \n");
616 sqlText.append(" LD_BCN_ACCT_RPTS_T rpts, \n");
617 sqlText.append(" LD_BCNSTR_HDR_T head, \n");
618 sqlText.append(" FS_DOC_HEADER_T fphd \n");
619 sqlText.append("WHERE pull.pull_flag = ? \n");
620 sqlText.append(" AND pull.person_unvl_id = ? \n");
621 sqlText.append(" AND hier.univ_fiscal_yr = ? \n");
622 sqlText.append(" AND hier.org_fin_coa_cd = pull.fin_coa_cd \n");
623 sqlText.append(" AND hier.org_cd = pull.org_cd \n");
624 sqlText.append(" AND hier.fin_coa_cd = rpts.fin_coa_cd \n");
625 sqlText.append(" AND hier.account_nbr = rpts.account_nbr \n");
626 sqlText.append(" AND hier.org_fin_coa_cd = rpts.rpts_to_fin_coa_cd \n");
627 sqlText.append(" AND hier.org_cd = rpts.rpts_to_org_cd \n");
628 sqlText.append(" AND hier.univ_fiscal_yr = hier2.univ_fiscal_yr \n");
629 sqlText.append(" AND hier.fin_coa_cd = hier2.fin_coa_cd \n");
630 sqlText.append(" AND hier.account_nbr = hier2.account_nbr \n");
631 sqlText.append(" AND hier2.org_fin_coa_cd = ? \n");
632 sqlText.append(" AND hier2.org_cd = ? \n");
633 sqlText.append(" AND head.univ_fiscal_yr = hier2.univ_fiscal_yr \n");
634 sqlText.append(" AND head.fin_coa_cd = hier2.fin_coa_cd \n");
635 sqlText.append(" AND head.account_nbr = hier2.account_nbr \n");
636 sqlText.append(" AND head.org_level_cd = hier2.org_level_cd \n");
637 sqlText.append(" AND fphd.fdoc_nbr = head.fdoc_nbr \n");
638
639 accountSelectBudgetedDocumentsPushDownTemplates[0] = sqlText.toString();
640 sqlText.delete(0, sqlText.length());
641 }
642
643 /**
644 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetOrganizationPushPullDao#pullupSelectedOrganizationDocuments(java.lang.String,
645 * java.lang.Integer, java.lang.String, java.lang.String)
646 */
647 public void pullupSelectedOrganizationDocuments(String principalId, Integer fiscalYear, String pointOfViewCharOfAccountsCode, String pointOfViewOrganizationCode) {
648
649 String sessionId = new Guid().toString();
650
651 // run the steps
652 this.getSimpleJdbcTemplate().update(pullupSelectedOrganizationDocumentsTemplates[0], sessionId, principalId, fiscalYear);
653 this.getSimpleJdbcTemplate().update(pullupSelectedOrganizationDocumentsTemplates[1], sessionId, sessionId, pointOfViewCharOfAccountsCode, pointOfViewOrganizationCode);
654 this.getSimpleJdbcTemplate().update(pullupSelectedOrganizationDocumentsTemplates[2], sessionId, sessionId, BCConstants.OrgSelControlOption.BOTH.getKey());
655 this.getSimpleJdbcTemplate().update(pullupSelectedOrganizationDocumentsTemplates[3], sessionId, sessionId, BCConstants.OrgSelControlOption.ORG.getKey());
656 this.getSimpleJdbcTemplate().update(pullupSelectedOrganizationDocumentsTemplates[4], sessionId, sessionId, BCConstants.OrgSelControlOption.SUBORG.getKey());
657 this.getSimpleJdbcTemplate().update(pullupSelectedOrganizationDocumentsTemplates[5], principalId, sessionId);
658 this.getSimpleJdbcTemplate().update(pullupSelectedOrganizationDocumentsTemplates[6], sessionId, principalId);
659 this.getSimpleJdbcTemplate().update(pullupSelectedOrganizationDocumentsTemplates[7], sessionId, principalId, sessionId, principalId, sessionId, principalId, sessionId, principalId);
660
661 // cleanup temp table space
662 this.clearTempTableBySesId("LD_BCN_DOC_PULLUP01_MT", "SESID", sessionId);
663 this.clearTempTableBySesId("LD_BCN_DOC_PULLUP02_MT", "SESID", sessionId);
664 this.clearTempTableBySesId("LD_BCN_DOC_PULLUP03_MT", "SESID", sessionId);
665
666 }
667
668 /**
669 * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetOrganizationPushPullDao#pushdownSelectedOrganizationDocuments(java.lang.String,
670 * java.lang.Integer, java.lang.String, java.lang.String)
671 */
672 public void pushdownSelectedOrganizationDocuments(String principalId, Integer fiscalYear, String pointOfViewCharOfAccountsCode, String pointOfViewOrganizationCode) {
673
674 String sessionId = new Guid().toString();
675
676 // use some local vars to improve readability
677 Integer orgLev = OrgSelControlOption.ORGLEV.getKey();
678 Integer mgrLev = OrgSelControlOption.MGRLEV.getKey();
679 Integer orgMgrLev = OrgSelControlOption.ORGMGRLEV.getKey();
680 Integer levOne = OrgSelControlOption.LEVONE.getKey();
681 Integer levZero = OrgSelControlOption.LEVZERO.getKey();
682 String puid = principalId;
683
684 // run the steps
685 this.getSimpleJdbcTemplate().update(pushdownSelectedOrganizationDocumentsTemplates[0], sessionId, puid, fiscalYear);
686 this.getSimpleJdbcTemplate().update(pushdownSelectedOrganizationDocumentsTemplates[1], sessionId, sessionId, pointOfViewCharOfAccountsCode, pointOfViewOrganizationCode);
687 this.getSimpleJdbcTemplate().update(pushdownSelectedOrganizationDocumentsTemplates[2], sessionId, sessionId);
688 this.getSimpleJdbcTemplate().update(pushdownSelectedOrganizationDocumentsTemplates[3], sessionId, sessionId, orgLev, orgMgrLev, levOne, levZero);
689 this.getSimpleJdbcTemplate().update(pushdownSelectedOrganizationDocumentsTemplates[4], sessionId, sessionId, mgrLev);
690 this.getSimpleJdbcTemplate().update(pushdownSelectedOrganizationDocumentsTemplates[5], puid, sessionId);
691 this.getSimpleJdbcTemplate().update(pushdownSelectedOrganizationDocumentsTemplates[6], sessionId, puid);
692 this.getSimpleJdbcTemplate().update(pushdownSelectedOrganizationDocumentsTemplates[7], sessionId, mgrLev, orgMgrLev, puid);
693 this.getSimpleJdbcTemplate().update(pushdownSelectedOrganizationDocumentsTemplates[8], sessionId, orgLev, orgMgrLev, puid, sessionId, orgLev, orgMgrLev, puid, sessionId, orgLev, orgMgrLev, puid, sessionId, orgLev, orgMgrLev, puid);
694 this.getSimpleJdbcTemplate().update(pushdownSelectedOrganizationDocumentsTemplates[9], sessionId, levOne, puid, sessionId, levOne, puid, sessionId, levOne, puid);
695 this.getSimpleJdbcTemplate().update(pushdownSelectedOrganizationDocumentsTemplates[10], sessionId, levZero, puid);
696
697 // cleanup temp table space
698 this.clearTempTableBySesId("LD_BCN_DOC_PUSHDOWN01_MT", "SESID", sessionId);
699 this.clearTempTableBySesId("LD_BCN_DOC_PUSHDOWN02_MT", "SESID", sessionId);
700 this.clearTempTableBySesId("LD_BCN_DOC_PUSHDOWN03_MT", "SESID", sessionId);
701 this.clearTempTableBySesId("LD_BCN_DOC_PUSHDOWN04_MT", "SESID", sessionId);
702 }
703
704 /**
705 * Uses sql jdbc call to populate the account select table for the set of pull up documents.
706 *
707 * @see org.kuali.kfs.module.bc.document.dataaccess..BudgetOrganizationPushPullDao#buildPullUpBudgetedDocuments(java.lang.String,
708 * java.lang.Integer, java.lang.String, java.lang.String)
709 */
710 public int buildPullUpBudgetedDocuments(String principalId, Integer fiscalYear, String pointOfViewCharOfAccountsCode, String pointOfViewOrganizationCode) {
711 // clear temp records for users
712 this.clearTempTableByUnvlId("LD_BCN_ACCTSEL_T", "person_unvl_id", principalId);
713
714 Integer org = OrgSelControlOption.ORG.getKey();
715 Integer subOrg = OrgSelControlOption.SUBORG.getKey();
716 Integer both = OrgSelControlOption.BOTH.getKey();
717
718 // build account select
719 int rowCount = this.getSimpleJdbcTemplate().update(accountSelectBudgetedDocumentsPullUpTemplates[0], principalId, both, principalId, fiscalYear, pointOfViewCharOfAccountsCode, pointOfViewOrganizationCode, org, principalId, fiscalYear, pointOfViewCharOfAccountsCode, pointOfViewOrganizationCode, subOrg, principalId, fiscalYear, pointOfViewCharOfAccountsCode, pointOfViewOrganizationCode);
720
721 // update org for accounts at level zero
722 this.getSimpleJdbcTemplate().update(accountSelectBudgetedDocumentsPullUpTemplates[1], principalId, fiscalYear);
723
724 return rowCount;
725 }
726
727 /**
728 * Uses sql jdbc call to populate the account select table for the set of push up documents.
729 *
730 * @see org.kuali.kfs.module.bc.document.dataaccess..BudgetOrganizationPushPullDao#buildPushDownBudgetedDocuments(java.lang.String,
731 * java.lang.Integer, java.lang.String, java.lang.String)
732 */
733 public int buildPushDownBudgetedDocuments(String principalId, Integer fiscalYear, String pointOfViewCharOfAccountsCode, String pointOfViewOrganizationCode) {
734 // clear temp records for users
735 this.clearTempTableByUnvlId("LD_BCN_ACCTSEL_T", "person_unvl_id", principalId);
736
737 Integer orgLev = OrgSelControlOption.ORGLEV.getKey();
738 Integer mgrLev = OrgSelControlOption.MGRLEV.getKey();
739 Integer orgMgrLev = OrgSelControlOption.ORGMGRLEV.getKey();
740 Integer levOne = OrgSelControlOption.LEVONE.getKey();
741 Integer levZero = OrgSelControlOption.LEVZERO.getKey();
742
743 // build account select
744 int rowCount = this.getSimpleJdbcTemplate().update(accountSelectBudgetedDocumentsPushDownTemplates[0], principalId, orgLev, orgMgrLev, levOne, levZero, principalId, fiscalYear, pointOfViewCharOfAccountsCode, pointOfViewOrganizationCode, principalId, mgrLev, principalId, fiscalYear, pointOfViewCharOfAccountsCode, pointOfViewOrganizationCode);
745
746 return rowCount;
747 }
748
749 }