[Home] [Help]
PACKAGE BODY: APPS.PA_SUMMARIZE_ORG_ROLLUP_PVT
Source
1 PACKAGE BODY PA_SUMMARIZE_ORG_ROLLUP_PVT AS
2 /* $Header: PARRORGB.pls 120.0 2005/05/30 09:37:32 appldev noship $ */
3
4 /*
5 * Amount type ids - locally cached at the
6 * package level.
7 */
8 l_org_dir_hrs_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_dir_hrs_id;
9 l_org_dir_wtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_dir_wtdhrs_org_id;
10 l_org_dir_prvhrs_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_dir_prvhrs_id;
11 l_org_dir_prvwtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_dir_prvwtdhrs_org_id;
12 l_org_dir_cap_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_dir_cap_id;
13 l_org_dir_reducedcap_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_dir_reducedcap_id;
14
15 l_org_sub_hrs_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_sub_hrs_id;
16 l_org_sub_wtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_sub_wtdhrs_org_id;
17 l_org_sub_prvhrs_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_sub_prvhrs_id;
18 l_org_sub_prvwtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_sub_prvwtdhrs_org_id;
19 l_org_sub_cap_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_sub_cap_id;
20 l_org_sub_reducedcap_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_sub_reducedcap_id;
21
22 l_org_tot_hrs_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_tot_hrs_id;
23 l_org_tot_wtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_tot_wtdhrs_org_id;
24 l_org_tot_prvhrs_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_tot_prvhrs_id;
25 l_org_tot_prvwtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_tot_prvwtdhrs_org_id;
26 l_org_tot_cap_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_tot_cap_id;
27 l_org_tot_reducedcap_id pa_amount_types_b.amount_type_id%TYPE := pa_rep_util_glob.G_amt_type_details.G_org_tot_reducedcap_id;
28
29 /*
30 * Object types locally cached at the package level.
31 */
32 l_obj_type_orgwt VARCHAR2(15) := pa_rep_util_glob.G_OBJ_TYPE_C.G_ORGWT_C;
33 l_obj_type_orguc VARCHAR2(15) := pa_rep_util_glob.G_OBJ_TYPE_C.G_ORGUC_C;
34 l_obj_type_org VARCHAR2(15) := pa_rep_util_glob.G_OBJ_TYPE_C.G_ORG_C;
35
36 /*
37 * Org ids locally cached at the package level.
38 */
39 l_start_org_id pa_implementations_all.start_organization_id%TYPE := pa_rep_util_glob.G_implementation_details.G_start_organization_id;
40 l_org_id pa_implementations_all.org_id%TYPE := pa_rep_util_glob.G_implementation_details.G_org_id;
41 l_org_structure_version_id pa_implementations_all.org_id%TYPE := pa_rep_util_glob.G_implementation_details.G_org_structure_version_id;
42
43 /*
44 * Period Set Name locally cached at the package level.
45 * Changed for bug 3434019
46 */
47 -- l_period_set_name gl_sets_of_books.period_set_name%TYPE := pa_rep_util_glob.G_implementation_details.G_period_set_name;
48 l_gl_period_set_name gl_sets_of_books.period_set_name%TYPE := pa_rep_util_glob.G_implementation_details.G_gl_period_set_name;
49 l_pa_period_set_name gl_sets_of_books.period_set_name%TYPE := pa_rep_util_glob.G_implementation_details.G_pa_period_set_name;
50
51 /*
52 * Commit and Fetch Sizes locally cached at the package level.
53 */
54 l_commit_size PLS_INTEGER := pa_rep_util_glob.G_util_fetch_size;
55 l_fetch_size PLS_INTEGER := pa_rep_util_glob.G_util_fetch_size;
56
57 /*
58 * Unit of measure locally cached at the package level.
59 */
60 l_unit_of_measure VARCHAR2(10) := pa_rep_util_glob.G_UNIT_OF_MEASURE_HRS_C;
61
62 /*
63 * Dummy Date locally cached at the package level.
64 */
65 l_dummy_date DATE := pa_rep_util_glob.G_DUMMY_DATE_C;
66 l_dummy_period_set_name VARCHAR2(15) := PA_REP_UTIL_GLOB.G_DUMMY_C;
67
68
69 /*
70 * Level of the start_org_id.
71 */
72 l_maximum_level PLS_INTEGER;
73
74 ----------------------------------------------------------------------------
75 /*
76 * This procedure calls the org_rollup_pagl_period_type procedure for
77 * period types 'PA' and 'GL' and org_rollup_ge_period_type for period
78 * type 'GE'.
79 */
80 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
81
82 PROCEDURE refresh_org_hierarchy_rollup( p_balance_type_code IN VARCHAR2)
83 IS
84 l_pa_period_flag pa_utilization_options.pa_period_flag%TYPE := pa_rep_util_glob.G_util_option_details.G_pa_period_flag;
85 l_gl_period_flag pa_utilization_options.gl_period_flag%TYPE := pa_rep_util_glob.G_util_option_details.G_gl_period_flag;
86 l_ge_period_flag pa_utilization_options.global_exp_period_flag%TYPE := pa_rep_util_glob.G_util_option_details.G_ge_period_flag;
87
88 /*
89 * pa and gl period types.
90 */
91 l_pa_period_type pa_implementations_all.pa_period_type%TYPE := pa_rep_util_glob.G_implementation_details.G_pa_period_type;
92 l_gl_period_type gl_sets_of_books.accounted_period_type%TYPE := pa_rep_util_glob.G_implementation_details.G_gl_period_type;
93
94 /*
95 * Constants for 'PA' and 'GL'.
96 */
97 l_period_type_pa VARCHAR2(3) := pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C;
98 l_period_type_gl VARCHAR2(3) := pa_rep_util_glob.G_PERIOD_TYPE_C.G_GL_C;
99 l_period_type_ge VARCHAR2(3) := pa_rep_util_glob.G_PERIOD_TYPE_C.G_GE_C;
100
101 l_balance_type_code pa_objects.balance_type_code%TYPE;
102 l_eff_start_pa_period_num PLS_INTEGER;
103 l_eff_start_gl_period_num PLS_INTEGER;
104 l_start_date DATE;
105 l_end_date DATE;
106 BEGIN
107 PA_DEBUG.set_curr_function('refresh_org_hierarchy_rollup');
108 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
109 PA_DEBUG.g_err_stage := '50:Inside refresh_org_hierarchy_rollup';
110 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
111 END IF;
112 l_balance_type_code := p_balance_type_code;
113
114 IF (l_balance_type_code = 'ACTUALS') THEN
115 l_start_date := pa_rep_util_glob.G_input_parameters.G_ac_start_date;
116 l_end_date := pa_rep_util_glob.G_input_parameters.G_ac_end_date;
117 l_eff_start_pa_period_num := pa_rep_util_glob.G_eff_ac_start_pa_period_num;
118 l_eff_start_gl_period_num := pa_rep_util_glob.G_eff_ac_start_gl_period_num;
119 ELSIF (l_balance_type_code = 'FORECAST') THEN
120 l_start_date := pa_rep_util_glob.G_input_parameters.G_fc_start_date;
121 l_end_date := pa_rep_util_glob.G_input_parameters.G_fc_end_date;
122 l_eff_start_pa_period_num := pa_rep_util_glob.G_eff_fc_start_pa_period_num;
123 l_eff_start_gl_period_num := pa_rep_util_glob.G_eff_fc_start_gl_period_num;
124 END IF;
125
126 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
127 PA_DEBUG.g_err_stage := '100:Balance Type is [' || l_balance_type_code || ']';
128 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
129 PA_DEBUG.g_err_stage := '100:Start Date is [' || TO_CHAR(l_start_date) || ']';
130 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
131 PA_DEBUG.g_err_stage := '100:End Date is [' || TO_CHAR(l_end_date) || ']';
132 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
133 PA_DEBUG.g_err_stage := '100:Effective PA start period num is [' || TO_CHAR(l_eff_start_pa_period_num) || ']';
134 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
135 PA_DEBUG.g_err_stage := '100:Effective GL start period num is [' || TO_CHAR(l_eff_start_gl_period_num) || ']';
136 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
137 END IF;
138
139 /*
140 * Determining the maximum level in the hierarchy for rollup.
141 */
142 SELECT parent_level
143 INTO l_maximum_level
144 FROM pa_org_hierarchy_denorm
145 WHERE parent_organization_id = l_start_org_id
146 AND pa_org_use_type = 'REPORTING'
147 AND ORG_HIERARCHY_VERSION_ID = l_org_structure_version_id
148 AND NVL(org_id, -99) = l_org_id
149 AND ROWNUM = 1;
150 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
151 PA_DEBUG.g_err_stage := '150:Maximum Level is [' || TO_CHAR(l_maximum_level) || ']';
152 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
153 END IF;
154
155 /*
156 * Create Missing objects pa_objects for organizations.
157 */
158 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
159 PA_DEBUG.g_err_stage := '150:Before Calling create_missing_parent_objects';
160 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
161 END IF;
162
163 create_missing_parent_objects(p_balance_type_code => l_balance_type_code);
164 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
165 PA_DEBUG.g_err_stage := '175:After Calling create_missing_parent_objects';
166
167 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
168 END IF;
169
170 IF (l_pa_period_flag = 'Y') THEN
171
172 /*
173 * Call the org_rollup_pagl_period_type procedure with parameters.
174 */
175 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
176 PA_DEBUG.g_err_stage := '200:Before calling org_rollup_pagl_period_type for [' || l_period_type_pa || ']';
177 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
178 END IF;
179 org_rollup_pagl_period_type( p_balance_type_code => l_balance_type_code
180 ,p_period_type => l_period_type_pa
181 ,p_effective_start_period_num => l_eff_start_pa_period_num
182 );
183 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
184 PA_DEBUG.g_err_stage := '250:After calling org_rollup_pagl_period_type for [' || l_period_type_pa || ']';
185 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
186 END IF;
187
188 END IF; -- Check for PA flag
189
190
191 IF (l_gl_period_flag = 'Y') THEN
192
193 /*
194 * Call the org rollup procedure with parameters.
195 */
196
197 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
198 PA_DEBUG.g_err_stage := '300:Before calling org_rollup_pagl_period_type for [' || l_period_type_gl || ']';
199 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
200 END IF;
201 org_rollup_pagl_period_type( p_balance_type_code => l_balance_type_code
202 ,p_period_type => l_period_type_gl
203 ,p_effective_start_period_num => l_eff_start_gl_period_num
204 );
205 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
206 PA_DEBUG.g_err_stage := '350:After calling org_rollup_pagl_period_type for [' || l_period_type_gl || ']';
207 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
208 END IF;
209
210 END IF; -- Check for GL flag.
211
212 IF (l_ge_period_flag = 'Y') THEN
213 /*
214 * Call the org rollup procedure with parameters.
215 */
216 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
217 PA_DEBUG.g_err_stage := '400:Before calling org_rollup_ge_period_type for [' || l_period_type_ge || ']';
218 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
219 END IF;
220 org_rollup_ge_period_type( p_balance_type_code => l_balance_type_code
221 ,p_period_type => l_period_type_ge
222 ,p_start_date => l_start_date
223 ,p_end_date => l_end_date
224 );
225 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
226 PA_DEBUG.g_err_stage := '450:After calling org_rollup_ge_period_type for [' || l_period_type_ge || ']';
227 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
228 END IF;
229 END IF; -- Check for GE flag.
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 RAISE;
234 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
235 PA_DEBUG.g_err_stage := '500:Leaving refresh_org_hierarchy_rollup';
236 PA_DEBUG.log_message('refresh_org_hierarchy_rollup: ' || PA_DEBUG.g_err_stage);
237 END IF;
238 PA_DEBUG.reset_curr_function;
239 END refresh_org_hierarchy_rollup;
240 ----------------------------------------------------------------
241 /*
242 * If the Generate Utilization process is run in the 'Refresh' mode
243 * While creating the detail level records, the summarization process
244 * creates Objects for only those organization which have direct numbers.
245 *
246 * During the rollup process, balances are to be rolled-up to the
247 * parent organizations, even if that organization doesnt' have
248 * that corresponding object.
249 *
250 * To handle this situation, before entering into the rollup process
251 * we create objects for the parent, grand-parent organizations
252 * depending on the objects their child, grand-child organizations have.
253 *
254 * Rule: If any of the children of an organization has an object
255 * the parent organization must have that object.
256 */
257
258 PROCEDURE create_missing_parent_objects(p_balance_type_code IN VARCHAR2)
259 IS
260
261 l_object_type_code pa_objects.object_type_code%TYPE;
262 l_org_util_category_id pa_objects.org_util_category_id%TYPE;
263 l_work_type_id pa_objects.work_type_id%TYPE;
264 l_parent_organization_id pa_objects.expenditure_organization_id%TYPE;
265
266 l_dummy VARCHAR2(1);
267
268 /*
269 * The following cursor - selects a set of objects the parent organizations
270 * should have based on the objects its child organizations have.
271 */
272
273 CURSOR cur_unique_objects_per_parent( p_level IN PLS_INTEGER
274 ,p_balance_type_code IN pa_objects.balance_type_code%TYPE
275 )
276 IS
277 SELECT obj.object_type_code
278 ,obj.org_util_category_id
279 ,obj.work_type_id
280 ,hier.parent_organization_id
281 FROM pa_objects obj
282 ,pa_org_hierarchy_denorm hier
283 WHERE hier.child_organization_id = obj.expenditure_organization_id
284 AND hier.pa_org_use_type = 'REPORTING'
285 AND hier.ORG_HIERARCHY_VERSION_ID = l_org_structure_version_id
286 AND hier.parent_level = p_level
287 AND hier.parent_level = hier.child_level + 1
288 AND NVL(hier.org_id,-99) = l_org_id
289 AND obj.expenditure_org_id = l_org_id
290 AND obj.balance_type_code = p_balance_type_code
291 AND obj.object_type_code IN ( l_obj_type_orgwt
292 ,l_obj_type_orguc
293 ,l_obj_type_org
294 )
295 AND obj.project_org_id = -1
296 AND obj.project_organization_id = -1
297 AND obj.project_id = -1
298 AND obj.task_id = -1
299 AND obj.person_id = -1
300 GROUP BY obj.object_type_code
301 ,obj.org_util_category_id
302 ,obj.work_type_id
303 ,hier.parent_organization_id
304 ;
305
306 BEGIN
307 pa_debug.set_curr_function('create_missing_parent_objects');
308 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
309 pa_debug.g_err_stage := '50:Inside create_missing_parent_objects';
310 pa_debug.log_message('create_missing_parent_objects: ' || pa_debug.g_err_stage);
311 pa_debug.g_err_stage := '50:l_start_org_id = [' || to_char(l_start_org_id) || ']';
312 pa_debug.log_message('create_missing_parent_objects: ' || pa_debug.g_err_stage);
313 pa_debug.g_err_stage := '50:l_org_id = [' || to_char(l_org_id) || ']';
314 pa_debug.log_message('create_missing_parent_objects: ' || pa_debug.g_err_stage);
315 pa_debug.g_err_stage := '50:max_level = [' || to_char(l_maximum_level) || ']';
316 pa_debug.log_message('create_missing_parent_objects: ' || pa_debug.g_err_stage);
317 pa_debug.g_err_stage := '50:bal_type_code = [' || p_balance_type_code || ']';
318 pa_debug.log_message('create_missing_parent_objects: ' || pa_debug.g_err_stage);
319 pa_debug.g_err_stage := '50:orgwt = [' || l_obj_type_orgwt || ']';
320 pa_debug.log_message('create_missing_parent_objects: ' || pa_debug.g_err_stage);
321 pa_debug.g_err_stage := '50:orguc = [' || l_obj_type_orguc || ']';
322 pa_debug.log_message('create_missing_parent_objects: ' || pa_debug.g_err_stage);
323 pa_debug.g_err_stage := '50:org = [' || l_obj_type_org || ']';
324 pa_debug.log_message('create_missing_parent_objects: ' || pa_debug.g_err_stage);
325 END IF;
326
327 FOR l_level IN 2 .. l_maximum_level
328 LOOP
329 /*
330 * Loop for each level.
331 */
332 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
333 pa_debug.g_err_stage := '100:Opening cursor for level [' || to_char(l_level) || ']';
334 pa_debug.log_message('create_missing_parent_objects: ' || pa_debug.g_err_stage);
335 END IF;
336
337 OPEN cur_unique_objects_per_parent( l_level
338 ,p_balance_type_code
339 );
340 LOOP
341 /*
342 * Loop for all object combinations at this level.
343 * Fetching one object combination for this level.
344 */
345 FETCH cur_unique_objects_per_parent
346 INTO l_object_type_code
347 ,l_org_util_category_id
348 ,l_work_type_id
349 ,l_parent_organization_id ;
350
351 IF cur_unique_objects_per_parent%NOTFOUND
352 THEN
353 /*
354 * No more object combination exists.
355 */
356 EXIT;
357 END IF;
358
359 /*
360 * Individual Pl/sql block because we got to handle exception.
361 */
362 BEGIN
363
364 /*
365 * Check whether the object combination already exist for the parent.
366 * If it exists, do nothing. Else insert.
367 */
368 SELECT 'X'
369 INTO l_dummy
370 FROM pa_objects obj
371 WHERE obj.object_type_code = l_object_type_code
372 AND obj.balance_type_code = p_balance_type_code
373 AND obj.work_type_id = l_work_type_id
374 AND obj.org_util_category_id = l_org_util_category_id
375 AND obj.expenditure_organization_id = l_parent_organization_id;
376
377 EXCEPTION
378 WHEN NO_DATA_FOUND THEN
379
380 /*
381 * If control comes here, object doesnt exists. Insert.
382 */
383
384 INSERT
385 INTO pa_objects( object_id
386 ,object_type_code
387 ,balance_type_code
388 ,project_org_id
389 ,project_organization_id
390 ,project_id
391 ,task_id
392 ,expenditure_org_id
393 ,expenditure_organization_id
394 ,person_id
395 ,assignment_id
396 ,work_type_id
397 ,org_util_category_id
398 ,res_util_category_id
399 ,expenditure_type
400 ,expenditure_type_class
401 ,last_update_date
402 ,last_updated_by
403 ,creation_date
404 ,created_by
405 ,last_update_login
406 ,request_id
407 ,program_application_id
408 ,program_id
409 ,program_update_date
410 )
411 VALUES( pa_objects_s.nextval
412 ,l_object_type_code
413 ,p_balance_type_code
414 ,-1
415 ,-1
416 ,-1
417 ,-1
418 ,l_org_id
419 ,l_parent_organization_id
420 ,-1
421 ,-1
422 ,l_work_type_id
423 ,l_org_util_category_id
424 ,-1
425 ,-1
426 ,-1
427 ,SYSDATE
428 ,-1
429 ,SYSDATE
430 ,-1
431 ,-1
432 ,-1
433 ,-1
434 ,-1
435 ,SYSDATE
436 );
437
438
439 END;
440 END LOOP; -- Loop for records in this level.
441 CLOSE cur_unique_objects_per_parent; -- Closing cursor for this level.
442 END LOOP; -- Loop for level in hierarchy.
443 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
444 pa_debug.g_err_stage := '150:Leaving create_missing_parent_objects';
445 pa_debug.log_message('create_missing_parent_objects: ' || pa_debug.g_err_stage);
446 END IF;
447
448 pa_debug.reset_curr_function;
449 END create_missing_parent_objects;
450 ----------------------------------------------------------------
451 /*
452 * The following procedure does the organization rollup for a
453 * 'PA' and 'GL' period types.
454 */
455 PROCEDURE org_rollup_pagl_period_type( p_balance_type_code IN VARCHAR2
456 ,p_period_type IN VARCHAR2
457 ,p_effective_start_period_num IN PLS_INTEGER
458 )
459 IS
460
461 /*
462 * Cursor to insert tot_num records same as that of
463 * dir_num records.
464 */
465 CURSOR cur_org_dir_balances_pagl( p_start_org_id IN pa_implementations_all.start_organization_id%TYPE
466 ,p_balance_type_code IN pa_objects.balance_type_code%TYPE
467 ,p_maximum_level IN PLS_INTEGER
468 ,p_period_type IN VARCHAR2
469 ,p_effective_start_period_num IN PLS_INTEGER
470 ,p_org_id IN pa_implementations_all.org_id%TYPE
471 )
472 IS SELECT bal.object_id
473 ,bal.object_type_code
474 ,bal.period_name
475 ,bal.period_year
476 ,bal.quarter_or_month_number
477 ,bal.amount_type_id
478 ,bal.period_num
479 ,bal.period_balance
480 FROM pa_org_hierarchy_denorm hier
481 ,pa_summ_balances bal
482 ,pa_objects obj
483 WHERE hier.parent_organization_id = p_start_org_id
484 AND hier.parent_level = p_maximum_level
485 AND NVL(hier.org_id,-99) = p_org_id
486 AND hier.pa_org_use_type = 'REPORTING'
487 AND hier.ORG_HIERARCHY_VERSION_ID = l_org_structure_version_id
488 AND bal.object_id = obj.object_id
489 AND bal.version_id = -1
490 AND bal.period_num >= p_effective_start_period_num
491 AND bal.period_type = p_period_type
492 -- AND bal.period_set_name = l_period_set_name
493 AND bal.period_set_name = decode(bal.period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
494 AND bal.object_type_code = obj.object_type_code
495 AND obj.expenditure_organization_id = hier.child_organization_id
496 AND obj.expenditure_org_id = p_org_id
497 AND obj.balance_type_code = p_balance_type_code
498 AND obj.project_org_id = -1
499 AND obj.project_organization_id = -1
500 AND obj.project_id = -1
501 AND obj.task_id = -1
502 AND obj.assignment_id = -1
503 AND obj.person_id = -1
504 AND obj.object_type_code IN ( l_obj_type_orgwt
505 ,l_obj_type_orguc
506 ,l_obj_type_org
507 )
508 AND bal.amount_type_id IN ( l_org_dir_hrs_id
509 ,l_org_dir_wtdhrs_org_id
510 ,l_org_dir_prvhrs_id
511 ,l_org_dir_prvwtdhrs_org_id
512 ,l_org_dir_cap_id
513 ,l_org_dir_reducedcap_id
514 );
515
516
517 /*
518 * Cursor for getting the level-wise sub-org numbers.
519 */
520 CURSOR cur_org_sub_balances_pagl( p_level_number IN PLS_INTEGER
521 ,p_balance_type_code IN pa_objects.balance_type_code%TYPE
522 ,p_period_type IN VARCHAR2
523 ,p_effective_start_period_num IN PLS_INTEGER
524 ,p_org_id IN pa_implementations_all.org_id%TYPE
525 )
526 IS SELECT MAX(obj1.object_id)
527 ,obj.object_type_code
528 ,bal.period_name
529 ,MAX(bal.period_year)
530 ,MAX(bal.quarter_or_month_number)
531 ,bal.amount_type_id
532 ,MAX(bal.period_num)
533 ,SUM(bal.period_balance)
534 FROM pa_summ_balances bal
535 ,pa_objects obj
536 ,pa_objects obj1
537 ,pa_org_hierarchy_denorm hier
538 WHERE obj1.object_type_code = obj.object_type_code
539 AND obj1.balance_type_code = obj.balance_type_code
540 AND obj1.project_org_id = obj.project_org_id
541 AND obj1.project_organization_id = obj.project_organization_id
542 AND obj1.project_id = obj.project_id
543 AND obj1.task_id = obj.task_id
544 AND obj1.expenditure_organization_id = hier.parent_organization_id
545 AND obj1.expenditure_org_id = obj.expenditure_org_id
546 AND obj1.assignment_id = obj.assignment_id
547 AND obj1.person_id = obj.person_id
548 AND obj1.org_util_category_id = obj.org_util_category_id
549 AND obj1.work_type_id = obj.work_type_id
550 AND obj.balance_type_code = p_balance_type_code
551 AND obj.project_org_id = -1
552 AND obj.project_organization_id = -1
553 AND obj.project_id = -1
554 AND obj.task_id = -1
555 AND obj.expenditure_org_id = p_org_id
556 AND obj.expenditure_organization_id = hier.child_organization_id
557 AND obj.assignment_id = -1
558 AND obj.person_id = -1
559 AND NVL(hier.org_id,-99) = p_org_id
560 AND hier.pa_org_use_type = 'REPORTING'
561 AND hier.ORG_HIERARCHY_VERSION_ID = l_org_structure_version_id
562 AND hier.parent_level = p_level_number
563 AND hier.parent_level = hier.child_level + 1
564 AND bal.object_id = obj.object_id
565 AND bal.object_type_code = obj.object_type_code
566 AND bal.version_id = -1
567 AND bal.period_num >= p_effective_start_period_num
568 AND bal.period_type = p_period_type
569 -- AND bal.period_set_name = l_period_set_name
570 AND bal.period_set_name = decode(bal.period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
571 AND bal.amount_type_id IN ( l_org_tot_hrs_id
572 ,l_org_tot_wtdhrs_org_id
573 ,l_org_tot_prvhrs_id
574 ,l_org_tot_prvwtdhrs_org_id
575 ,l_org_tot_cap_id
576 ,l_org_tot_reducedcap_id
577 )
578 AND obj.object_type_code IN ( l_obj_type_orgwt
579 ,l_obj_type_orguc
580 ,l_obj_type_org
581 )
582 GROUP BY hier.parent_organization_id
583 ,obj.object_type_code
584 ,obj.org_util_category_id
585 ,obj.work_type_id
586 ,bal.period_name
587 -- ,bal.global_exp_period_end_date
588 ,bal.amount_type_id;
589
590 /*
591 * Tables to hold fetched values.
592 */
593 l_object_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
594 l_object_type_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
595 l_period_name_tab PA_PLSQL_DATATYPES.Char15TabTyp;
596 -- l_ge_period_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
597 l_period_year_tab PA_PLSQL_DATATYPES.NumTabTyp;
598 l_quarter_or_month_number_tab PA_PLSQL_DATATYPES.NumTabTyp;
599 l_amount_type_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
600 l_period_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
601 l_period_balance_tab PA_PLSQL_DATATYPES.NumTabTyp;
602 l_sub_org_total_tab PA_PLSQL_DATATYPES.NumTabTyp;
603
604 l_bunch_size PLS_INTEGER := 100;
605 l_this_fetch PLS_INTEGER;
606 l_totally_fetched PLS_INTEGER;
607 l_this_commit_cycle PLS_INTEGER;
608
609 l_rowcount number :=0;
610
611 BEGIN
612
613 PA_DEBUG.set_curr_function('org_rollup_pagl_period_type');
614 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
615 PA_DEBUG.g_err_stage := '50:Inside org_rollup_pagl_period_type';
616 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
617 END IF;
618
619 /*
620 * Delete all total and sub-org numbers from pa_summ_balances.
621 */
622 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
623 PA_DEBUG.g_err_stage := '100:Deleting Total and Sub-org Records';
624 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
625 END IF;
626 LOOP
627 DELETE
628 FROM pa_summ_balances bal
629 WHERE bal.amount_type_id IN ( l_org_sub_hrs_id
630 ,l_org_sub_wtdhrs_org_id
631 ,l_org_sub_prvhrs_id
632 ,l_org_sub_prvwtdhrs_org_id
633 ,l_org_sub_cap_id
634 ,l_org_sub_reducedcap_id
635 ,l_org_tot_hrs_id
636 ,l_org_tot_wtdhrs_org_id
637 ,l_org_tot_prvhrs_id
638 ,l_org_tot_prvwtdhrs_org_id
639 ,l_org_tot_cap_id
640 ,l_org_tot_reducedcap_id
641 )
642 AND bal.object_type_code IN ( l_obj_type_orgwt
643 ,l_obj_type_orguc
644 ,l_obj_type_org
645 )
646 AND bal.period_num >= p_effective_start_period_num
647 AND period_type = p_period_type
648 AND ROWNUM <= l_fetch_size
649 AND EXISTS ( SELECT NULL
650 FROM pa_objects obj
651 WHERE obj.balance_type_code = p_balance_type_code
652 AND obj.object_id = bal.object_id
653 AND obj.expenditure_org_id = l_org_id
654 );
655
656 /*Code Changes for Bug No.2984871 start */
657 l_rowcount:=sql%rowcount;
658 /*Code Changes for Bug No.2984871 end */
659
660 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
661 PA_DEBUG.g_err_stage := '150:Deleted [' || TO_CHAR(l_rowcount) || '] records';
662 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
663 END IF;
664
665 /*
666 * Exit when no more records left to delete.
667 */
668 IF (l_rowcount = 0 OR l_rowcount < l_fetch_size) THEN
669 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
670 PA_DEBUG.g_err_stage := '200:All Total and Sub-org records deleted.';
671 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
672 END IF;
673 EXIT;
674 END IF;
675 COMMIT;
676 END LOOP;
677
678 /*
679 * Set the Total numbers equal to the direct
680 * numbers for all the orgs.
681 */
682 /*
683 * A set of records equalling the bunch_size are dumped
684 * into plsql tables and then they are bulk inserted into
685 * the table.
686 * Since, we have already deleted all the total records,
687 * 'upsert' is NOT needed. ONLY insert.
688 */
689 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
690 PA_DEBUG.g_err_stage := '250:Opening Direct-number Cursor.';
691 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
692 PA_DEBUG.g_err_stage := '250:Start Org id is [' || l_start_org_id || ']';
693 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
694 PA_DEBUG.g_err_stage := '250:Balance Type is [' || p_balance_type_code || ']';
695 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
696 PA_DEBUG.g_err_stage := '250:Effective Start Period Number is [' || p_effective_start_period_num || ']';
697 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
698 PA_DEBUG.g_err_stage := '250:Org Id is [' || l_org_id || ']';
699 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
700 END IF;
701
702 OPEN cur_org_dir_balances_pagl( l_start_org_id
703 ,p_balance_type_code
704 ,l_maximum_level
705 ,p_period_type
706 ,p_effective_start_period_num
707 ,l_org_id
708 );
709 /*
710 * Resetting fetch-related variables.
711 */
712 l_this_fetch := 0;
713 l_this_commit_cycle := 0;
714 l_totally_fetched := 0;
715 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
716 PA_DEBUG.g_err_stage := '300:Fetching Direct-number Cursor.';
717 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
718 END IF;
719
720 LOOP
721 FETCH cur_org_dir_balances_pagl
722 BULK COLLECT
723 INTO l_object_id_tab
724 ,l_object_type_code_tab
725 ,l_period_name_tab
726 ,l_period_year_tab
727 ,l_quarter_or_month_number_tab
728 ,l_amount_type_id_tab
729 ,l_period_num_tab
730 ,l_period_balance_tab
731 LIMIT l_bunch_size;
732
733 l_this_fetch := cur_org_dir_balances_pagl%ROWCOUNT - l_totally_fetched;
734 l_this_commit_cycle := l_this_commit_cycle + l_this_fetch;
735 l_totally_fetched := cur_org_dir_balances_pagl%ROWCOUNT;
736 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
737 PA_DEBUG.g_err_stage := '350:Fetched [' || l_this_fetch || '] Direct-number records';
738 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
739 END IF;
740
741 IF (l_this_fetch = 0) THEN
742 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
743 PA_DEBUG.g_err_stage := '400:No more Direct-number records to fetch. Exiting';
744 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
745 END IF;
746 EXIT;
747 END IF;
748
749
750 FORALL i IN l_object_id_tab.FIRST .. l_object_id_tab.LAST
751 INSERT
752 INTO pa_summ_balances( object_id
753 ,version_id
754 ,object_type_code
755 ,period_type
756 ,period_set_name
757 ,period_name
758 ,global_exp_period_end_date
759 ,period_year
760 ,quarter_or_month_number
761 ,amount_type_id
762 ,period_num
763 ,unit_of_measure
764 ,period_balance
765 ,pvdr_currency_code
766 ,pvdr_period_balance
767 )
768 VALUES( l_object_id_tab(i)
769 ,-1
770 ,l_object_type_code_tab(i)
771 ,p_period_type
772 -- ,l_period_set_name
773 ,decode(p_period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
774 ,l_period_name_tab(i)
775 ,l_dummy_date
776 ,l_period_year_tab(i)
777 ,l_quarter_or_month_number_tab(i)
778 ,DECODE(l_amount_type_id_tab(i)
779 ,l_org_dir_hrs_id,l_org_tot_hrs_id
780 ,l_org_dir_wtdhrs_org_id, l_org_tot_wtdhrs_org_id
781 ,l_org_dir_prvhrs_id, l_org_tot_prvhrs_id
782 ,l_org_dir_prvwtdhrs_org_id, l_org_tot_prvwtdhrs_org_id
783 ,l_org_dir_cap_id, l_org_tot_cap_id
784 ,l_org_dir_reducedcap_id, l_org_tot_reducedcap_id
785 ,-1 --is this ok??
786 )
787 ,l_period_num_tab(i)
788 ,l_unit_of_measure
789 ,l_period_balance_tab(i)
790 ,NULL
791 ,NULL
792 );
793 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
794 PA_DEBUG.g_err_stage := '450:Inserted [' || SQL%ROWCOUNT || '] Total-number records';
795 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
796 END IF;
797
798 /*
799 * Commit if no. of records inserted is more than or
800 * equal to the fetch size.
801 */
802 IF (l_this_commit_cycle >= l_commit_size) THEN
803 COMMIT;
804 l_this_commit_cycle := 0;
805 END IF;
806
807 IF (l_this_fetch < l_bunch_size) THEN
808 /*
809 * Indicates last fetch.
810 */
811 COMMIT;
812 EXIT;
813 END IF;
814 END LOOP; -- End of loop to insert total number records.
815 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
816 PA_DEBUG.g_err_stage := '500:Closing Direct-number Cursor.';
817 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
818 END IF;
819
820 CLOSE cur_org_dir_balances_pagl;
821
822 /*
823 * Insert the sub-org number records.
824 */
825 FOR l_level IN 2 .. l_maximum_level
826 LOOP
827 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
828 PA_DEBUG.g_err_stage := '550:Opening Sub-org Cursor for Level [' || l_level || ']';
829 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
830 PA_DEBUG.g_err_stage := '550:Balance Type is [' || p_balance_type_code || ']';
831 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
832 PA_DEBUG.g_err_stage := '550:Effective Start Period num is [' || p_effective_start_period_num || ']';
833 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
834 PA_DEBUG.g_err_stage := '550:Org Id is [' || l_org_id || ']';
835 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
836 END IF;
837
838 OPEN cur_org_sub_balances_pagl( l_level
839 ,p_balance_type_code
840 ,p_period_type
841 ,p_effective_start_period_num
842 ,l_org_id
843 );
844 /*
845 * Resetting fetch related variables.
846 */
847 l_this_fetch := 0;
848 l_this_commit_cycle := 0;
849 l_totally_fetched := 0;
850
851 LOOP
852 FETCH cur_org_sub_balances_pagl
853 BULK COLLECT
854 INTO l_object_id_tab
855 ,l_object_type_code_tab
856 ,l_period_name_tab
857 ,l_period_year_tab
858 ,l_quarter_or_month_number_tab
859 ,l_amount_type_id_tab
860 ,l_period_num_tab
861 ,l_sub_org_total_tab
862 LIMIT l_bunch_size;
863
864 l_this_fetch := cur_org_sub_balances_pagl%ROWCOUNT - l_totally_fetched;
865 l_this_commit_cycle := l_this_commit_cycle + l_this_fetch;
866 l_totally_fetched := cur_org_sub_balances_pagl%ROWCOUNT;
867 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
868 PA_DEBUG.g_err_stage := '600:Fetched [' || l_this_fetch || '] Sub-org records';
869 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
870 END IF;
871 IF (l_this_fetch = 0) THEN
872 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
873 PA_DEBUG.g_err_stage := '650:No more Sub-org records left for level [' || l_level || '] Exiting';
874 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
875 END IF;
876 EXIT;
877 END IF;
878
879 FORALL i IN l_object_id_tab.FIRST .. l_object_id_tab.LAST
880 INSERT
881 INTO pa_summ_balances( object_id
882 ,version_id
883 ,object_type_code
884 ,period_type
885 ,period_set_name
886 ,period_name
887 ,global_exp_period_end_date
888 ,period_year
889 ,quarter_or_month_number
890 ,amount_type_id
891 ,period_num
892 ,unit_of_measure
893 ,period_balance
894 ,pvdr_currency_code
895 ,pvdr_period_balance
896 )
897 VALUES( l_object_id_tab(i)
898 ,-1
899 ,l_object_type_code_tab(i)
900 ,p_period_type
901 -- ,l_period_set_name
902 ,decode(p_period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
903 ,l_period_name_tab(i)
904 ,l_dummy_date
905 ,l_period_year_tab(i)
906 ,l_quarter_or_month_number_tab(i)
907 ,DECODE(l_amount_type_id_tab(i)
908 ,l_org_tot_hrs_id ,l_org_sub_hrs_id
909 ,l_org_tot_wtdhrs_org_id ,l_org_sub_wtdhrs_org_id
910 ,l_org_tot_prvhrs_id ,l_org_sub_prvhrs_id
911 ,l_org_tot_prvwtdhrs_org_id ,l_org_sub_prvwtdhrs_org_id
912 ,l_org_tot_cap_id ,l_org_sub_cap_id
913 ,l_org_tot_reducedcap_id ,l_org_sub_reducedcap_id
914 ,-1
915 )
916 ,l_period_num_tab(i)
917 ,l_unit_of_measure
918 ,l_sub_org_total_tab(i)
919 ,NULL
920 ,NULL
921 );
922 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
923 PA_DEBUG.g_err_stage := '700:Inserted [' || SQL%ROWCOUNT || '] Sub-org records';
924 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
925 END IF;
926 /*
927 * Update the tot_num records with tot_num = tot_num + sub_org
928 */
929 FORALL i IN l_object_id_tab.FIRST .. l_object_id_tab.LAST
930 UPDATE pa_summ_balances bal
931 SET bal.period_balance = bal.period_balance + l_sub_org_total_tab(i)
932 WHERE bal.object_id = l_object_id_tab(i)
933 AND bal.version_id = -1
934 AND bal.object_type_code = l_object_type_code_tab(i)
935 AND bal.period_type = p_period_type
936 -- AND bal.period_set_name = l_period_set_name
937 AND bal.period_set_name = decode(bal.period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
938 AND bal.period_name = l_period_name_tab(i)
939 -- AND bal.global_exp_period_end_date = l_ge_period_end_date_tab(i)
940 AND bal.global_exp_period_end_date = l_dummy_date
941 AND bal.period_year = l_period_year_tab(i)
942 AND bal.quarter_or_month_number = l_quarter_or_month_number_tab(i)
943 AND bal.amount_type_id = l_amount_type_id_tab(i)
944 AND bal.period_num = l_period_num_tab(i)
945 AND bal.unit_of_measure = l_unit_of_measure
946 AND bal.pvdr_currency_code IS NULL
947 AND bal.pvdr_period_balance IS NULL;
948
949 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
950 PA_DEBUG.g_err_stage := '750:Updated [' || SQL%ROWCOUNT || '] Total Records with T=T+S';
951 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
952 END IF;
953
954 /*
955 * If UPDATE didnt' go thro for a particular combination, that means
956 * that that particular combination doesnt already exist in the table.
957 * so INSERT.
958 */
959 FOR i IN l_object_id_tab.FIRST .. l_object_id_tab.LAST
960 LOOP
961 IF (SQL%BULK_ROWCOUNT(i) = 0) THEN
962 /*
963 * Update didnt' go thro' so, INSERT.
964 */
965 INSERT
966 INTO pa_summ_balances( object_id
967 ,version_id
968 ,object_type_code
969 ,period_type
970 ,period_set_name
971 ,period_name
972 ,global_exp_period_end_date
973 ,period_year
974 ,quarter_or_month_number
975 ,amount_type_id
976 ,period_num
977 ,unit_of_measure
978 ,period_balance
979 ,pvdr_currency_code
980 ,pvdr_period_balance
981 )
982 VALUES( l_object_id_tab(i)
983 ,-1
984 ,l_object_type_code_tab(i)
985 ,p_period_type
986 -- ,l_period_set_name
987 ,decode(p_period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
988 ,l_period_name_tab(i)
989 ,l_dummy_date
990 ,l_period_year_tab(i)
991 ,l_quarter_or_month_number_tab(i)
992 ,l_amount_type_id_tab(i)
993 ,l_period_num_tab(i)
994 ,l_unit_of_measure
995 ,l_sub_org_total_tab(i)
996 ,NULL
997 ,NULL
998 );
999 END IF;
1000 END LOOP; -- Loop to check whether the record was updated.
1001 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1002 PA_DEBUG.g_err_stage := '800:After Inserting Total Records.';
1003 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
1004 END IF;
1005
1006 /*
1007 * Commit if no. of records inserted is more than or
1008 * equal to the fetch size.
1009 */
1010 IF (l_this_commit_cycle >= l_commit_size) THEN
1011 l_this_commit_cycle := 0;
1012 COMMIT;
1013 END IF;
1014
1015 IF (l_this_fetch < l_bunch_size) THEN
1016 COMMIT;
1017 EXIT;
1018 END IF;
1019 END LOOP; -- loop for each of the levels.
1020 CLOSE cur_org_sub_balances_pagl;
1021 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1022 PA_DEBUG.g_err_stage := '850:After Closing Sub-org Cursor.';
1023 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
1024 END IF;
1025
1026 END LOOP; -- End of loop to insert sub-org number records.
1027 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1028 PA_DEBUG.g_err_stage := '900:Finished creating Sub-org and total Records for all Levels.';
1029 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
1030 END IF;
1031
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034 RAISE;
1035 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1036 PA_DEBUG.g_err_stage := '950:Leaving org_rollup_pagl_period_type';
1037 PA_DEBUG.log_message('org_rollup_pagl_period_type: ' || PA_DEBUG.g_err_stage);
1038 END IF;
1039 PA_DEBUG.reset_curr_function;
1040
1041 END org_rollup_pagl_period_type;
1042
1043 ------------------------------------------------------------------
1044 /*
1045 * The following procedure does the organization rollup for
1046 * the 'GE' period type.
1047 */
1048 PROCEDURE org_rollup_ge_period_type( p_balance_type_code IN VARCHAR2
1049 ,p_period_type IN VARCHAR2
1050 ,p_start_date IN DATE
1051 ,p_end_date IN DATE
1052 )
1053 IS
1054 /*
1055 * Cursor to insert tot_num records same as that of
1056 * dir_num records.
1057 */
1058 CURSOR cur_org_dir_balances_ge( p_start_org_id IN pa_implementations_all.start_organization_id%TYPE
1059 ,p_balance_type_code IN pa_objects.balance_type_code%TYPE
1060 ,p_maximum_level IN PLS_INTEGER
1061 ,p_period_type IN VARCHAR2
1062 ,p_start_date IN DATE
1063 ,p_end_date IN DATE
1064 ,p_org_id IN pa_implementations_all.org_id%TYPE
1065 )
1066 IS SELECT bal.object_id
1067 ,bal.object_type_code
1068 ,bal.period_name
1069 ,bal.global_exp_period_end_date
1070 ,bal.period_year
1071 ,bal.quarter_or_month_number
1072 ,bal.amount_type_id
1073 ,bal.period_balance
1074 FROM pa_org_hierarchy_denorm hier
1075 ,pa_summ_balances bal
1076 ,pa_objects obj
1077 WHERE hier.parent_organization_id = p_start_org_id
1078 AND hier.parent_level = p_maximum_level
1079 AND NVL(hier.org_id,-99) = p_org_id
1080 AND hier.pa_org_use_type = 'REPORTING'
1081 AND hier.ORG_HIERARCHY_VERSION_ID = l_org_structure_version_id
1082 AND bal.object_id = obj.object_id
1083 AND bal.version_id = -1
1084 AND bal.global_exp_period_end_date >= p_start_date
1085 AND bal.period_type = p_period_type
1086 AND bal.object_type_code = obj.object_type_code
1087 AND obj.expenditure_organization_id = hier.child_organization_id
1088 AND obj.expenditure_org_id = p_org_id
1089 AND obj.balance_type_code = p_balance_type_code
1090 AND obj.project_org_id = -1
1091 AND obj.project_organization_id = -1
1092 AND obj.project_id = -1
1093 AND obj.task_id = -1
1094 AND obj.assignment_id = -1
1095 AND obj.person_id = -1
1096 AND obj.object_type_code IN ( l_obj_type_orgwt
1097 ,l_obj_type_orguc
1098 ,l_obj_type_org
1099 )
1100 AND bal.amount_type_id IN ( l_org_dir_hrs_id
1101 ,l_org_dir_wtdhrs_org_id
1102 ,l_org_dir_prvhrs_id
1103 ,l_org_dir_prvwtdhrs_org_id
1104 ,l_org_dir_cap_id
1105 ,l_org_dir_reducedcap_id
1106 );
1107
1108
1109 /*
1110 * Cursor for getting the level-wise sub-org numbers.
1111 */
1112 CURSOR cur_org_sub_balances_ge( p_level_number IN PLS_INTEGER
1113 ,p_balance_type_code IN pa_objects.balance_type_code%TYPE
1114 ,p_period_type IN VARCHAR2
1115 ,p_start_date IN DATE
1116 ,p_end_date IN DATE
1117 ,p_org_id IN pa_implementations_all.org_id%TYPE
1118 )
1119 IS SELECT MAX(obj1.object_id)
1120 ,obj.object_type_code
1121 ,bal.period_name
1122 ,bal.global_exp_period_end_date
1123 ,MAX(bal.period_year)
1124 ,MAX(bal.quarter_or_month_number)
1125 ,bal.amount_type_id
1126 ,SUM(bal.period_balance)
1127 FROM pa_summ_balances bal
1128 ,pa_objects obj
1129 ,pa_objects obj1
1130 ,pa_org_hierarchy_denorm hier
1131 WHERE obj1.object_type_code = obj.object_type_code
1132 AND obj1.balance_type_code = obj.balance_type_code
1133 AND obj1.project_org_id = obj.project_org_id
1134 AND obj1.project_organization_id = obj.project_organization_id
1135 AND obj1.project_id = obj.project_id
1136 AND obj1.task_id = obj.task_id
1137 AND obj1.expenditure_organization_id = hier.parent_organization_id
1138 AND obj1.expenditure_org_id = obj.expenditure_org_id
1139 AND obj1.assignment_id = obj.assignment_id
1140 AND obj1.person_id = obj.person_id
1141 AND obj1.org_util_category_id = obj.org_util_category_id
1142 AND obj1.work_type_id = obj.work_type_id
1143 AND obj.balance_type_code = p_balance_type_code
1144 AND obj.project_org_id = -1
1145 AND obj.project_organization_id = -1
1146 AND obj.project_id = -1
1147 AND obj.task_id = -1
1148 AND obj.expenditure_org_id = p_org_id
1149 AND obj.expenditure_organization_id = hier.child_organization_id
1150 AND obj.assignment_id = -1
1151 AND obj.person_id = -1
1152 AND NVL(hier.org_id,-99) = p_org_id
1153 AND hier.pa_org_use_type = 'REPORTING'
1154 AND hier.ORG_HIERARCHY_VERSION_ID = l_org_structure_version_id
1155 AND hier.parent_level = p_level_number
1156 AND hier.parent_level = hier.child_level + 1
1157 AND bal.object_id = obj.object_id
1158 AND bal.object_type_code = obj.object_type_code
1159 AND bal.version_id = -1
1160 AND bal.global_exp_period_end_date >= p_start_date
1161 AND bal.period_type = p_period_type
1162 AND bal.amount_type_id IN ( l_org_tot_hrs_id
1163 ,l_org_tot_wtdhrs_org_id
1164 ,l_org_tot_prvhrs_id
1165 ,l_org_tot_prvwtdhrs_org_id
1166 ,l_org_tot_cap_id
1167 ,l_org_tot_reducedcap_id
1168 )
1169 AND obj.object_type_code IN ( l_obj_type_orgwt
1170 ,l_obj_type_orguc
1171 ,l_obj_type_org
1172 )
1173 GROUP BY hier.parent_organization_id
1174 ,obj.object_type_code
1175 ,obj.org_util_category_id
1176 ,obj.work_type_id
1177 ,bal.period_name
1178 ,bal.global_exp_period_end_date
1179 ,bal.amount_type_id;
1180
1181 /*
1182 * Tables to hold fetched values.
1183 */
1184 l_object_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1185 l_object_type_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1186 l_period_name_tab PA_PLSQL_DATATYPES.Char15TabTyp;
1187 l_ge_period_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1188 l_period_year_tab PA_PLSQL_DATATYPES.NumTabTyp;
1189 l_quarter_or_month_number_tab PA_PLSQL_DATATYPES.NumTabTyp;
1190 l_amount_type_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1191 l_period_balance_tab PA_PLSQL_DATATYPES.NumTabTyp;
1192 l_sub_org_total_tab PA_PLSQL_DATATYPES.NumTabTyp;
1193
1194 l_bunch_size PLS_INTEGER := 100;
1195 l_this_fetch PLS_INTEGER;
1196 l_totally_fetched PLS_INTEGER;
1197 l_this_commit_cycle PLS_INTEGER;
1198
1199 /*Code Changes for Bug No.2984871 start */
1200 l_rowcount number :=0;
1201 /*Code Changes for Bug No.2984871 end */
1202
1203 BEGIN
1204 PA_DEBUG.set_curr_function('org_rollup_ge_period_type');
1205 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1206 PA_DEBUG.g_err_stage := '50:Inside org_rollup_ge_period_type';
1207 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1208 PA_DEBUG.g_err_stage := '100:Deleting Total and Sub-org Records';
1209 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1210 END IF;
1211 /*
1212 * Delete all total and sub-org numbers from pa_summ_balances.
1213 */
1214 LOOP
1215 DELETE
1216 FROM pa_summ_balances bal
1217 WHERE bal.amount_type_id IN ( l_org_sub_hrs_id
1218 ,l_org_sub_wtdhrs_org_id
1219 ,l_org_sub_prvhrs_id
1220 ,l_org_sub_prvwtdhrs_org_id
1221 ,l_org_sub_cap_id
1222 ,l_org_sub_reducedcap_id
1223 ,l_org_tot_hrs_id
1224 ,l_org_tot_wtdhrs_org_id
1225 ,l_org_tot_prvhrs_id
1226 ,l_org_tot_prvwtdhrs_org_id
1227 ,l_org_tot_cap_id
1228 ,l_org_tot_reducedcap_id
1229 )
1230 AND bal.object_type_code IN ( l_obj_type_orgwt
1231 ,l_obj_type_orguc
1232 ,l_obj_type_org
1233 )
1234 AND bal.global_exp_period_end_date >= p_start_date
1235 AND bal.period_type = p_period_type
1236 AND ROWNUM <= l_fetch_size
1237 AND EXISTS ( SELECT NULL
1238 FROM pa_objects obj
1239 WHERE obj.balance_type_code = p_balance_type_code
1240 AND obj.object_id = bal.object_id
1241 AND obj.expenditure_org_id = l_org_id
1242 );
1243 /*Code Changes for Bug No.2984871 start */
1244 l_rowcount:=sql%rowcount;
1245 /*Code Changes for Bug No.2984871 end */
1246
1247 /*
1248 * Exit when no more records left to delete.
1249 */
1250 IF (l_rowcount = 0) THEN
1251 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1252 PA_DEBUG.g_err_stage := '200:All Total and Sub-org records deleted.';
1253 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1254 END IF;
1255 COMMIT;
1256 EXIT;
1257 END IF;
1258 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1259 PA_DEBUG.g_err_stage := '150:Deleted [' || TO_CHAR(l_rowcount) || '] records';
1260 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1261 END IF;
1262 COMMIT;
1263 END LOOP;
1264
1265 /*
1266 * Set the Total numbers equal to the direct
1267 * numbers for all the orgs.
1268 */
1269 /*
1270 * The plan is to dump a set of N records into plsql tables
1271 * and then do a bulk insert into the table.
1272 * Since, we have already deleted all the total records,
1273 * 'upsert' is NOT needed. ONLY insert.
1274 */
1275 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1276 PA_DEBUG.g_err_stage := '250:Opening Direct-number Cursor.';
1277 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1278 PA_DEBUG.g_err_stage := '250:Start Org id is [' || l_start_org_id || ']';
1279 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1280 PA_DEBUG.g_err_stage := '250:Balance Type is [' || p_balance_type_code || ']';
1281 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1282 PA_DEBUG.g_err_stage := '250:Start Date is [' || p_start_date || ']';
1283 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1284 PA_DEBUG.g_err_stage := '250:End Date is [' || p_end_date || ']';
1285 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1286 PA_DEBUG.g_err_stage := '250:Org Id is [' || l_org_id || ']';
1287 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1288 END IF;
1289
1290 OPEN cur_org_dir_balances_ge( l_start_org_id
1291 ,p_balance_type_code
1292 ,l_maximum_level
1293 ,p_period_type
1294 ,p_start_date
1295 ,p_end_date
1296 ,l_org_id
1297 );
1298 /*
1299 * Resetting fetch-related variables.
1300 */
1301 l_this_fetch := 0;
1302 l_this_commit_cycle := 0;
1303 l_totally_fetched := 0;
1304 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1305 PA_DEBUG.g_err_stage := '300:Fetching Direct-number Cursor.';
1306 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1307 END IF;
1308
1309 LOOP
1310 FETCH cur_org_dir_balances_ge
1311 BULK COLLECT
1312 INTO l_object_id_tab
1313 ,l_object_type_code_tab
1314 ,l_period_name_tab
1315 ,l_ge_period_end_date_tab
1316 ,l_period_year_tab
1317 ,l_quarter_or_month_number_tab
1318 ,l_amount_type_id_tab
1319 ,l_period_balance_tab
1320 LIMIT l_bunch_size;
1321
1322 l_this_fetch := cur_org_dir_balances_ge%ROWCOUNT - l_totally_fetched;
1323 l_this_commit_cycle := l_this_commit_cycle + l_this_fetch;
1324 l_totally_fetched := cur_org_dir_balances_ge%ROWCOUNT;
1325
1326 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1327 PA_DEBUG.g_err_stage := '350:Fetched [' || l_this_fetch || '] Direct-number records';
1328 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1329 END IF;
1330
1331 IF (l_this_fetch = 0) THEN
1332 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1333 PA_DEBUG.g_err_stage := '400:No more Direct-number records to fetch. Exiting';
1334 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1335 END IF;
1336 EXIT;
1337 END IF;
1338
1339
1340 FORALL i IN l_object_id_tab.FIRST .. l_object_id_tab.LAST
1341 INSERT
1342 INTO pa_summ_balances( object_id
1343 ,version_id
1344 ,object_type_code
1345 ,period_type
1346 ,period_set_name
1347 ,period_name
1348 ,global_exp_period_end_date
1349 ,period_year
1350 ,quarter_or_month_number
1351 ,amount_type_id
1352 ,period_num
1353 ,unit_of_measure
1354 ,period_balance
1355 ,pvdr_currency_code
1356 ,pvdr_period_balance
1357 )
1358 VALUES( l_object_id_tab(i)
1359 ,-1
1360 ,l_object_type_code_tab(i)
1361 ,p_period_type
1362 -- ,l_dummy_period_set_name
1363 ,decode(p_period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
1364 ,l_period_name_tab(i)
1365 ,l_ge_period_end_date_tab(i)
1366 ,l_period_year_tab(i)
1367 ,l_quarter_or_month_number_tab(i)
1368 ,DECODE(l_amount_type_id_tab(i)
1369 ,l_org_dir_hrs_id,l_org_tot_hrs_id
1370 ,l_org_dir_wtdhrs_org_id, l_org_tot_wtdhrs_org_id
1371 ,l_org_dir_prvhrs_id, l_org_tot_prvhrs_id
1372 ,l_org_dir_prvwtdhrs_org_id, l_org_tot_prvwtdhrs_org_id
1373 ,l_org_dir_cap_id, l_org_tot_cap_id
1374 ,l_org_dir_reducedcap_id, l_org_tot_reducedcap_id
1375 ,-1 --is this ok??
1376 )
1377 ,-1
1378 ,l_unit_of_measure
1379 ,l_period_balance_tab(i)
1380 ,NULL
1381 ,NULL
1382 );
1383
1384 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1385 PA_DEBUG.g_err_stage := '450:Inserted [' || SQL%ROWCOUNT || '] Total-number records';
1386 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1387 END IF;
1388
1389 /*
1390 * Commit if no. of records inserted is more than or
1391 * equal to the fetch size.
1392 */
1393 IF (l_this_commit_cycle >= l_commit_size) THEN
1394 COMMIT;
1395 l_this_commit_cycle := 0;
1396 END IF;
1397
1398 IF (l_this_fetch < l_bunch_size) THEN
1399 /*
1400 * Indicates last fetch.
1401 */
1402 COMMIT;
1403 EXIT;
1404 END IF;
1405 END LOOP; -- End of loop to insert total number records.
1406 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1407 PA_DEBUG.g_err_stage := '500:Closing Direct-number Cursor.';
1408 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1409 END IF;
1410
1411 CLOSE cur_org_dir_balances_ge;
1412
1413 /*
1414 * Insert the sub-org number records.
1415 */
1416 FOR l_level IN 2 .. l_maximum_level
1417 LOOP
1418 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1419 PA_DEBUG.g_err_stage := '550:Opening Sub-org Cursor for Level [' || l_level || ']';
1420 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1421 PA_DEBUG.g_err_stage := '550:Balance Type is [' || p_balance_type_code || ']';
1422 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1423 PA_DEBUG.g_err_stage := '550:Start Date is [' || p_start_date || ']';
1424 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1425 PA_DEBUG.g_err_stage := '550:End Date is [' || p_end_date || ']';
1426 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1427 PA_DEBUG.g_err_stage := '550:Org Id is [' || l_org_id || ']';
1428 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1429 END IF;
1430
1431 OPEN cur_org_sub_balances_ge( l_level
1432 ,p_balance_type_code
1433 ,p_period_type
1434 ,p_start_date
1435 ,p_end_date
1436 ,l_org_id
1437 );
1438 /*
1439 * Resetting fetch related variables.
1440 */
1441 l_this_fetch := 0;
1442 l_this_commit_cycle := 0;
1443 l_totally_fetched := 0;
1444
1445 LOOP
1446 FETCH cur_org_sub_balances_ge
1447 BULK COLLECT
1448 INTO l_object_id_tab
1449 ,l_object_type_code_tab
1450 ,l_period_name_tab
1451 ,l_ge_period_end_date_tab
1452 ,l_period_year_tab
1453 ,l_quarter_or_month_number_tab
1454 ,l_amount_type_id_tab
1455 ,l_sub_org_total_tab
1456 LIMIT l_bunch_size;
1457
1458 l_this_fetch := cur_org_sub_balances_ge%ROWCOUNT - l_totally_fetched;
1459 l_this_commit_cycle := l_this_commit_cycle + l_this_fetch;
1460 l_totally_fetched := cur_org_sub_balances_ge%ROWCOUNT;
1461
1462 IF (l_this_fetch = 0) THEN
1463 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1464 PA_DEBUG.g_err_stage := '650:No more Sub-org records left for level [' || l_level || '] Exiting';
1465 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1466 END IF;
1467 EXIT;
1468 END IF;
1469 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1470 PA_DEBUG.g_err_stage := '600:Fetched [' || l_this_fetch || '] Sub-org records';
1471 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1472 END IF;
1473
1474 FORALL i IN l_object_id_tab.FIRST .. l_object_id_tab.LAST
1475 INSERT
1476 INTO pa_summ_balances( object_id
1477 ,version_id
1478 ,object_type_code
1479 ,period_type
1480 ,period_set_name
1481 ,period_name
1482 ,global_exp_period_end_date
1483 ,period_year
1484 ,quarter_or_month_number
1485 ,amount_type_id
1486 ,period_num
1487 ,unit_of_measure
1488 ,period_balance
1489 ,pvdr_currency_code
1490 ,pvdr_period_balance
1491 )
1492 VALUES( l_object_id_tab(i)
1493 ,-1
1494 ,l_object_type_code_tab(i)
1495 ,p_period_type
1496 ,l_dummy_period_set_name
1497 ,l_period_name_tab(i)
1498 ,l_ge_period_end_date_tab(i)
1499 ,l_period_year_tab(i)
1500 ,l_quarter_or_month_number_tab(i)
1501 ,DECODE(l_amount_type_id_tab(i)
1502 ,l_org_tot_hrs_id ,l_org_sub_hrs_id
1503 ,l_org_tot_wtdhrs_org_id ,l_org_sub_wtdhrs_org_id
1504 ,l_org_tot_prvhrs_id ,l_org_sub_prvhrs_id
1505 ,l_org_tot_prvwtdhrs_org_id ,l_org_sub_prvwtdhrs_org_id
1506 ,l_org_tot_cap_id ,l_org_sub_cap_id
1507 ,l_org_tot_reducedcap_id ,l_org_sub_reducedcap_id
1508 ,-1 -- is this ok??
1509 )
1510 ,-1
1511 ,l_unit_of_measure
1512 ,l_sub_org_total_tab(i)
1513 ,NULL
1514 ,NULL
1515 );
1516 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1517 PA_DEBUG.g_err_stage := '700:Inserted [' || SQL%ROWCOUNT || '] Sub-org records';
1518 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1519 END IF;
1520
1521 /*
1522 * Update the tot_num records with tot_num = tot_num + sub_org
1523 * Some of the checks in the where clause may not be required.
1524 */
1525 FORALL i IN l_object_id_tab.FIRST .. l_object_id_tab.LAST
1526 UPDATE pa_summ_balances bal
1527 SET bal.period_balance = bal.period_balance + l_sub_org_total_tab(i)
1528 WHERE bal.object_id = l_object_id_tab(i)
1529 AND bal.version_id = -1
1530 AND bal.object_type_code = l_object_type_code_tab(i)
1531 AND bal.period_type = p_period_type
1532 AND bal.period_set_name = l_dummy_period_set_name
1533 AND bal.period_name = l_period_name_tab(i)
1534 AND bal.global_exp_period_end_date = l_ge_period_end_date_tab(i)
1535 AND bal.period_year = l_period_year_tab(i)
1536 AND bal.quarter_or_month_number = l_quarter_or_month_number_tab(i)
1537 AND bal.amount_type_id = l_amount_type_id_tab(i)
1538 AND bal.period_num = -1
1539 AND bal.unit_of_measure = l_unit_of_measure
1540 AND bal.pvdr_currency_code IS NULL
1541 AND bal.pvdr_period_balance IS NULL;
1542 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1543 PA_DEBUG.g_err_stage := '750:Updated [' || SQL%ROWCOUNT || '] Total Records with T=T+S';
1544 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1545 END IF;
1546
1547 /*
1548 * If UPDATE didnt' go thro for a particular combination, that means
1549 * that that particular combination doesnt already exist in the table.
1550 * so INSERT.
1551 */
1552 FOR i IN l_object_id_tab.FIRST .. l_object_id_tab.LAST
1553 LOOP
1554 IF (SQL%BULK_ROWCOUNT(i) = 0) THEN
1555 /*
1556 * Update didnt' go thro' so, INSERT.
1557 */
1558 INSERT
1559 INTO pa_summ_balances( object_id
1560 ,version_id
1561 ,object_type_code
1562 ,period_type
1563 ,period_set_name
1564 ,period_name
1565 ,global_exp_period_end_date
1566 ,period_year
1567 ,quarter_or_month_number
1568 ,amount_type_id
1569 ,period_num
1570 ,unit_of_measure
1571 ,period_balance
1572 ,pvdr_currency_code
1573 ,pvdr_period_balance
1574 )
1575 VALUES( l_object_id_tab(i)
1576 ,-1
1577 ,l_object_type_code_tab(i)
1578 ,p_period_type
1579 ,l_dummy_period_set_name
1580 ,l_period_name_tab(i)
1581 ,l_ge_period_end_date_tab(i)
1582 ,l_period_year_tab(i)
1583 ,l_quarter_or_month_number_tab(i)
1584 ,l_amount_type_id_tab(i)
1585 ,-1
1586 ,l_unit_of_measure
1587 ,l_sub_org_total_tab(i)
1588 ,NULL
1589 ,NULL
1590 );
1591 END IF;
1592 END LOOP; -- Loop to chek whether the record was updated.
1593 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1594 PA_DEBUG.g_err_stage := '800:After Inserting Total Records.';
1595 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1596 END IF;
1597
1598 /*
1599 * Commit if no. of records inserted is more than or
1600 * equal to the fetch size.
1601 */
1602 IF (l_this_commit_cycle >= l_commit_size) THEN
1603 l_this_commit_cycle := 0;
1604 COMMIT;
1605 END IF;
1606
1607 IF (l_this_fetch < l_bunch_size) THEN
1608 COMMIT;
1609 EXIT;
1610 END IF;
1611 END LOOP; -- loop for each of the levels.
1612 CLOSE cur_org_sub_balances_ge;
1613 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1614 PA_DEBUG.g_err_stage := '850:After Closing Sub-org Cursor.';
1615 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1616 END IF;
1617
1618 END LOOP; -- End of loop to insert sub-org number records.
1619 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1620 PA_DEBUG.g_err_stage := '900:Finished creating Sub-org and total Records for all Levels.';
1621 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1622 END IF;
1623
1624 EXCEPTION
1625 WHEN OTHERS THEN
1626 RAISE;
1627 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1628 PA_DEBUG.g_err_stage := '950:Leaving org_rollup_ge_period_type';
1629 PA_DEBUG.log_message('org_rollup_ge_period_type: ' || PA_DEBUG.g_err_stage);
1630 END IF;
1631 PA_DEBUG.reset_curr_function;
1632
1633 END org_rollup_ge_period_type;
1634 ------------------------------------------------------------------
1635
1636 END PA_SUMMARIZE_ORG_ROLLUP_PVT;