[Home] [Help]
PACKAGE BODY: APPS.PA_FORECAST_GRC_PVT
Source
1 PACKAGE BODY PA_FORECAST_GRC_PVT AS
2 /* $Header: PARFGRCB.pls 120.2 2005/08/24 04:13:25 avaithia noship $ */
3 --------------------------------------------------------------------------------------
4 -- Description This procedure will calculate the total possible
5 -- working hours of a person between the input start
6 -- date and end date.
7 -- Procedure Name Get_Resource_Capacity
8 -- Used Subprograms PA_SCHEDULE_PVT.get_resource_schedule
9 -- Input parameters Type Required Description
10 -- p_org_id NUMBER Yes Orgnization ID, to derive the
11 -- HR start date and end date
12 -- p_person_id NUMBER Yes Person ID, to derive the HR start
13 -- date and end date
14 -- p_start_date DATE Yes Start date of the person
15 -- p_end_date DATE Yew End date of the person
16 --
17 -- Output parameters Type Description
18 -- x_resource_capacity NUMBER The total hours a person could work between
19 -- p_start_date and p_end_date.
20 -- x_return_status VARCHAR2 The return status of this procedure
21 -------------------------------------------------------------------------------------------------------
22 PROCEDURE Get_Resource_Capacity (p_org_id IN NUMBER,
23 p_person_id IN NUMBER,
24 p_start_date IN DATE,
25 p_end_date IN DATE,
26 x_resource_capacity OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
27 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
28 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
29 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
30 )
31 IS
32
33 l_start_date DATE; --input start date
34 l_end_date DATE; --input end date
35
36 l_hr_start_date DATE; --HR start date
37 l_hr_end_date DATE; --HR end date
38
39 l_real_start_date DATE; --actual start date passed to get_resource_schedule
40 --API
41 l_real_end_date DATE; --actual end date passed to get_resource_schedule API
42
43 l_date DATE; --date variable
44 l_no_of_days NUMBER; --number of days between the l_real_start_date and
45 --l_real_end_date
46 i NUMBER; --loop variable
47 j NUMBER; --loop variable
48
49 l_resource_id NUMBER; --resource id
50 l_resource_type VARCHAR2(30); --resource type
51
52 l_no_of_hours NUMBER;
53 l_total_no_of_hours NUMBER;
54
55 l_x_return_status VARCHAR2(2);
56 l_msg_count number;
57 l_msg_data varchar2(80);
58
59
60 l_monday_hours NUMBER;
61 l_tuesday_hours NUMBER;
62 l_wednesday_hours NUMBER;
63 l_thursday_hours NUMBER;
64 l_friday_hours NUMBER;
65 l_saturday_hours NUMBER;
66 l_sunday_hours NUMBER;
67 l_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
68
69 --Delcare a cursor to store the HR assignment start and end dates for the
70 --person
71
72 CURSOR c_assignment_dates IS
73 /* SELECT EFFECTIVE_START_DATE,EFFECTIVE_END_DATE
74 FROM per_assignments_f
75 WHERE person_id = p_person_id
76 AND organization_id = p_org_id
77 AND (((EFFECTIVE_END_DATE BETWEEN p_start_date AND p_end_date) OR
78 (EFFECTIVE_START_DATE BETWEEN p_start_date AND p_end_date))
79 OR ((p_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)OR
80 (p_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)));
81 */
82
83 SELECT RESOURCE_EFFECTIVE_START_DATE, RESOURCE_EFFECTIVE_END_DATE
84 FROM pa_resources_denorm
85 WHERE person_id = p_person_id
86 AND resource_organization_id = p_org_id
87 AND (((RESOURCE_EFFECTIVE_END_DATE BETWEEN p_start_date AND p_end_date) OR
88 (RESOURCE_EFFECTIVE_START_DATE BETWEEN p_start_date AND p_end_date))
89 OR ((p_start_date BETWEEN RESOURCE_EFFECTIVE_START_DATE AND RESOURCE_EFFECTIVE_END_DATE)OR
90 (p_end_date BETWEEN RESOURCE_EFFECTIVE_START_DATE AND RESOURCE_EFFECTIVE_END_DATE)));
91
92
93
94 BEGIN
95
96 --initialize
97 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
98 l_no_of_hours := 0;
99 l_total_no_of_hours := 0;
100
101 --First derive the resource_id from person_id
102 SELECT resource_id
103 INTO l_resource_id
104 FROM pa_resource_txn_attributes
105 WHERE person_id = p_person_id;
106
107 --Set the resource_type
108 l_resource_type := 'PA_RESOURCE_ID';
109
110 --Open the cursor
111 OPEN c_assignment_dates;
112
113 --Do a fetch loop here to retrive the HR assignment start and end dates for a
114 --person
115 LOOP
116 FETCH c_assignment_dates
117 INTO l_hr_start_date, l_hr_end_date;
118
119 EXIT WHEN c_assignment_dates%NOTFOUND;
120
121 --Process the dates here
122 --Compare the p_start_date and p_end_date with l_hr_start_date and l_hr_end_date
123 IF (NOT (p_start_date >= l_hr_end_date OR
124 p_end_date <= l_hr_start_date)) THEN
125
126 --Condition 1
127 IF ( p_start_date >= l_hr_start_date AND p_end_date <= l_hr_end_date)
128 THEN
132 --Condition 2
129 l_real_start_date := p_start_date;
130 l_real_end_date := p_end_date;
131
133 ELSIF (p_start_date <= l_hr_start_date AND p_end_date >= l_hr_end_date)
134 THEN
135 l_real_start_date := l_hr_start_date;
136 l_real_end_date := l_hr_end_date;
137
138 --Condition 3
139 ELSIF ((p_start_date <= l_hr_start_date) AND (p_end_date <= l_hr_end_date
140 AND p_end_date >= l_hr_start_date)) THEN
141 l_real_start_date := l_hr_start_date;
142 l_real_end_date := p_end_date;
143
144 --Condition 4
145 ELSIF (p_start_date >= l_hr_start_date AND p_start_date <= l_hr_end_date)
146 AND p_end_date >= l_hr_end_date THEN
147 l_real_start_date := p_start_date;
148 l_real_end_date := l_hr_end_date;
149
150 END IF;
151
152 --CALL the get_resource_schedule API here
153 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
154 PA_SCHEDULE_PVT.get_resource_schedule(
155 p_source_id => l_resource_id,
156 p_source_type => l_resource_type,
157 p_start_date => l_real_start_date,
158 p_end_date => l_real_end_date,
159 x_sch_record_tab => l_sch_record_tab,
160 x_return_status => l_x_return_status,
161 x_msg_count => l_msg_count,
162 x_msg_data => l_msg_data);
163 l_date := l_real_start_date;
164 l_no_of_days := l_real_end_date - l_real_start_date + 1 ;
165 FOR i IN 1..l_no_of_days LOOP
166
167 FOR j IN l_sch_record_tab.first..l_sch_record_tab.last LOOP
168 IF (trunc(l_date) BETWEEN
169 trunc(l_sch_record_tab(j).start_date) AND
170 trunc(l_sch_record_tab(j).end_date)) THEN
171
172 IF TO_CHAR(l_date,'DY','NLS_DATE_LANGUAGE=AMERICAN')= 'MON' THEN
173 l_no_of_hours := l_no_of_hours + nvl(l_sch_record_tab(j).monday_hours,0);
174
175 ELSIF TO_CHAR(l_date, 'DY','NLS_DATE_LANGUAGE=AMERICAN') = 'TUE' THEN
176 l_no_of_hours := l_no_of_hours + nvl(l_sch_record_tab(j).tuesday_hours,0);
177
178 ELSIF TO_CHAR(l_date, 'DY','NLS_DATE_LANGUAGE=AMERICAN') = 'WED' THEN
179 l_no_of_hours := l_no_of_hours + nvl(l_sch_record_tab(j).wednesday_hours,0);
180
181 ELSIF TO_CHAR(l_date, 'DY','NLS_DATE_LANGUAGE=AMERICAN') = 'THU' THEN
182 l_no_of_hours := l_no_of_hours + nvl(l_sch_record_tab(j).thursday_hours,0);
183
184 ELSIF TO_CHAR(l_date, 'DY','NLS_DATE_LANGUAGE=AMERICAN') = 'FRI' THEN
185 l_no_of_hours := l_no_of_hours + nvl(l_sch_record_tab(j).friday_hours,0);
186
187 ELSIF TO_CHAR(l_date, 'DY','NLS_DATE_LANGUAGE=AMERICAN') = 'SAT' THEN
188 l_no_of_hours := l_no_of_hours + nvl(l_sch_record_tab(j).saturday_hours,0);
189
190 ELSIF TO_CHAR(l_date, 'DY','NLS_DATE_LANGUAGE=AMERICAN') = 'SUN' THEN
191 l_no_of_hours := l_no_of_hours + nvl(l_sch_record_tab(j).sunday_hours,0);
192
193 END IF;
194 END IF;
195 END LOOP;
196 l_date := l_date + 1 ;
197
198 --end for loop
199 END LOOP;
200
201 END IF;
202
203 --calculate the total hours
204 l_total_no_of_hours := l_total_no_of_hours + l_no_of_hours;
205
206 --end IF loop
207 END IF;
208 --end fetch loop
209 END LOOP;
210
211 CLOSE c_assignment_dates;
212
213 x_resource_capacity := l_total_no_of_hours;
214 x_return_status := l_x_return_status;
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
219 x_msg_count := 1;
220 x_msg_data := SQLERRM;
221 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FORECAST_GRC_PVT',
222 p_procedure_name => 'get_resource_capacity');
223 x_resource_capacity := NULL ; -- RESET the other OUT param also 4537865
224 END Get_Resource_Capacity;
225
226
227
228 ----------------------------------------------------------------------------------------
229 PROCEDURE Get_Capacity_Vector(p_OU_id IN NUMBER,
230 p_exp_org_id_tab IN PA_PLSQL_DATATYPES.IdTabTyp,
231 p_person_id_tab IN PA_PLSQL_DATATYPES.IdTabTyp,
232 p_resource_id_tab IN PA_PLSQL_DATATYPES.IdTabTyp,
233 p_in_res_eff_s_date_tab IN PA_PLSQL_DATATYPES.DateTabTyp,
234 p_in_res_eff_e_date_tab IN PA_PLSQL_DATATYPES.DateTabTyp,
235 p_balance_type_code IN VARCHAR2,
236 p_run_start_date IN DATE,
237 p_run_end_date IN DATE,
238 x_resource_capacity_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
239 x_exp_orgz_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp, --File.Sql.39 bug 4440895
240 x_person_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp, --File.Sql.39 bug 4440895
241 x_period_type_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, --File.Sql.39 bug 4440895
242 x_period_name_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, --File.Sql.39 bug 4440895
246 x_period_num_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
243 x_global_exp_date_tab OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp, --File.Sql.39 bug 4440895
244 x_period_year_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
245 x_qm_number_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
247 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
248 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
249 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
250 )IS
251
252
253 l_OU_id NUMBER;
254 l_exp_orgz_id NUMBER;
255 l_person_id NUMBER;
256 l_balance_type_code VARCHAR2(30);
257 l_period_type VARCHAR2(30);
258 -- l_period_set_name VARCHAR2(30):=PA_REP_UTIL_GLOB.G_implementation_details.G_period_set_name;
259 l_gl_period_set_name VARCHAR2(30):=PA_REP_UTIL_GLOB.G_implementation_details.G_gl_period_set_name; -- bug 3434019
260 l_pa_period_set_name VARCHAR2(30):=PA_REP_UTIL_GLOB.G_implementation_details.G_pa_period_set_name; -- bug 3434019
261 l_period_name VARCHAR2(30);
262 l_GE_end_date DATE;
263 l_resource_capacity NUMBER;
264 l_period_balance NUMBER;
265 l_return_status VARCHAR2(2);
266 l_msg_count NUMBER;
267 l_msg_data VARCHAR2(80);
268 i NUMBER;
269
270 l_pa_period_flag pa_utilization_options.pa_period_flag%TYPE := pa_rep_util_glob.G_util_option_details.G_pa_period_flag;
271 l_gl_period_flag pa_utilization_options.gl_period_flag%TYPE := pa_rep_util_glob.G_util_option_details.G_gl_period_flag;
272 l_ge_period_flag pa_utilization_options.global_exp_period_flag%TYPE := pa_rep_util_glob.G_util_option_details.G_ge_period_flag;
273 l_pa_period_type pa_implementations.pa_period_type%TYPE := pa_rep_util_glob.G_implementation_details.G_pa_period_type;
274 l_gl_period_type gl_sets_of_books.accounted_period_type%TYPE := pa_rep_util_glob.G_implementation_details.G_gl_period_type;
275
276 -- l_global_week_start_day PLS_INTEGER := pa_rep_util_glob.G_global_week_start_day;
277
278 j NUMBER := 0;
279 jj NUMBER := 0;
280
281 -- Cursor to find the periods within PA
282 CURSOR PA_PERIODS_CUR is
283 SELECT
284 pglp.period_name AS PERIOD_NAME
285 ,pglp.start_date AS PERIOD_START_DATE
286 ,pglp.end_date AS PERIOD_END_DATE
287 ,pglp.period_year AS PERIOD_YEAR
288 ,pglp.quarter_num AS PERIOD_QUARTER
289 ,(pglp.period_year*10000) + pglp.period_num AS PERIOD_NUM
290 FROM gl_periods pglp
291 WHERE exists
292 (select null
293 from gl_date_period_map p
294 where pglp.period_set_name = p.period_set_name
295 -- and p.period_set_name = l_period_set_name
296 and p.period_set_name = l_pa_period_set_name -- bug 3434019
297 and p.period_type = l_pa_period_type
298 and pglp.period_name = p.period_name)
299 AND p_run_end_date >= pglp.start_date
300 AND p_run_start_date <= pglp.end_date
301 ;
302
303 -- Cursor to find the periods within GL
304 CURSOR GL_PERIODS_CUR is
305 SELECT
306 gglp.period_name AS PERIOD_NAME
307 ,gglp.start_date AS PERIOD_START_DATE
308 ,gglp.end_date AS PERIOD_END_DATE
309 ,gglp.period_year AS PERIOD_YEAR
310 ,gglp.quarter_num AS PERIOD_QUARTER
311 ,(gglp.period_year*10000) + gglp.period_num AS PERIOD_NUM
312 FROM gl_periods gglp
313 WHERE exists
314 (select null
315 from gl_date_period_map g
316 where gglp.period_set_name = g.period_set_name
317 -- and g.period_set_name = l_period_set_name
318 and g.period_set_name = l_gl_period_set_name -- bug 3434019
319 and g.period_type = l_gl_period_type
320 and gglp.period_name = g.period_name)
321 AND p_run_start_date <= gglp.end_date
322 AND p_run_end_date >= gglp.start_date
323 ;
324
325 CURSOR GE_PERIODS_CUR is
326 SELECT
327 period_year AS PERIOD_YEAR
328 ,mon_or_qtr AS PERIOD_MONTH
329 ,ge_week_dt AS GE_DATE
330 ,period_start_date AS PERIOD_START_DATE
331 FROM pa_rep_periods_v
332 WHERE period_type = 'GE'
333 AND p_run_start_date <= ge_week_dt
334 AND p_run_end_date >= period_start_date
335 AND to_number(to_char(ge_week_dt,'YYYY')) = period_year
336 ;
337
338 pa_periods_cur_rec PA_PERIODS_CUR%ROWTYPE;
339 gl_periods_cur_rec GL_PERIODS_CUR%ROWTYPE;
340 ge_periods_cur_rec GE_PERIODS_CUR%ROWTYPE;
341
342 start_date_to_be_used DATE;
343 end_date_to_be_used DATE;
344
345 BEGIN
346
347 -- 4537865 : Initialize return_status to Success
348 x_return_status := FND_API.G_RET_STS_SUCCESS;
349 l_return_status := FND_API.G_RET_STS_SUCCESS;
350 -- 4537865 : End
351
352 l_balance_type_code := p_balance_type_code;
353 l_OU_id := p_OU_id;
354
355 /*
356 * Clear all PL/SQL table.
357 */
358 PA_DEBUG.g_err_stage := 'Clearing all output PL/SQL Table';
359 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
360 x_resource_capacity_tab.delete;
361 x_exp_orgz_id_tab.delete;
362 x_person_id_tab.delete;
363 x_period_type_tab.delete;
364 x_period_name_tab.delete;
368 x_period_num_tab.delete;
365 x_global_exp_date_tab.delete;
366 x_period_year_tab.delete;
367 x_qm_number_tab.delete;
369
370
371 FOR i IN p_exp_org_id_tab.FIRST .. p_exp_org_id_tab.LAST LOOP
372 l_exp_orgz_id := p_exp_org_id_tab(i);
373 l_person_id := p_person_id_tab(i);
374
375
376
377 -- first loop through the PA periods available
378 IF l_pa_period_flag = 'Y' then
379 FOR pa_periods_cur_rec in PA_PERIODS_CUR LOOP
380 l_return_status := FND_API.G_RET_STS_SUCCESS;
381 IF (
382 ( p_in_res_eff_s_date_tab(i) >= pa_periods_cur_rec.PERIOD_START_DATE
383 and p_in_res_eff_s_date_tab(i) <= pa_periods_cur_rec.PERIOD_END_DATE )
384 OR
385 ( p_in_res_eff_e_date_tab(i) >= pa_periods_cur_rec.PERIOD_START_DATE
386 and p_in_res_eff_e_date_tab(i) <= pa_periods_cur_rec.PERIOD_END_DATE )
387 OR
388 ( pa_periods_cur_rec.PERIOD_START_DATE >= p_in_res_eff_s_date_tab(i)
389 and pa_periods_cur_rec.PERIOD_START_DATE <= p_in_res_eff_e_date_tab(i) )
390 ) THEN
391
392 /*
393 * Bug: 1781913
394 * The start and end dates to be used as inputs for get_resource_capacity
395 * should be such that :
396 * The start_date_to_be_used should be the latest of the following 3 dates:
397 * PERIOD_START_DATE
398 * p_in_res_eff_s_date_tab(i)
399 * p_run_start_date
400 * While the end_date_to_be_used should be the earliest of the following 3 dates:
401 * PERIOD_END_DATE
402 * p_in_res_eff_e_date_tab(i)
403 * p_run_end_date
404 */
405
406 start_date_to_be_used := GREATEST(pa_periods_cur_rec.PERIOD_START_DATE
407 , p_in_res_eff_s_date_tab(i)
408 , p_run_start_date);
409 end_date_to_be_used := LEAST(pa_periods_cur_rec.PERIOD_END_DATE
410 , p_in_res_eff_e_date_tab(i)
411 , p_run_end_date);
412
413 --get the resource capacity
414 --get the resource capacity
415 PA_FORECAST_GRC_PVT.get_resource_capacity(
416 p_org_id => l_exp_orgz_id,
417 p_person_id => l_person_id,
418 p_start_date => start_date_to_be_used,
419 p_end_date => end_date_to_be_used,
420 x_resource_capacity => l_resource_capacity,
421 x_return_status => l_return_status,
422 x_msg_count => l_msg_count,
423 x_msg_data => l_msg_data);
424
425 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
426
427 --get the period_balance
428 begin
429 select bal.period_balance
430 into l_period_balance
431 from pa_objects obj,
432 pa_summ_balances bal
433 where obj.object_id = bal.object_id
434 and bal.version_id = -1
435 and bal.object_type_code = PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_RES_C
436 and bal.period_type = l_pa_period_type
437 -- and bal.period_set_name = l_period_set_name
438 and bal.period_set_name = l_pa_period_set_name -- bug 3434019
439 and bal.period_name = pa_periods_cur_rec.PERIOD_NAME
440 and bal.global_exp_period_end_date = PA_REP_UTIL_GLOB.G_DUMMY_DATE_C
441 and bal.amount_type_id = PA_REP_UTIL_GLOB.G_amt_type_details.G_res_cap_id
442 and obj.object_type_code = PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_RES_C
443 and obj.balance_type_code = l_balance_type_code
444 and obj.project_org_id = -1
445 and obj.project_organization_id = -1
446 and obj.project_id = -1
447 and obj.task_id = -1
448 and obj.expenditure_org_id = l_OU_id
449 and obj.expenditure_organization_id = l_exp_orgz_id
450 and obj.person_id = l_person_id
451 and obj.assignment_id = -1
452 and obj.work_type_id = -1
453 and obj.org_util_category_id = -1
454 and obj.res_util_category_id = -1
455 for update of bal.object_id;
456 exception
457 when no_data_found
458 then
459 l_period_balance := 0;
460 end;
461
462
463 x_resource_capacity_tab(j) := l_resource_capacity - l_period_balance;
464 x_exp_orgz_id_tab(j) := l_exp_orgz_id;
465 x_person_id_tab(j) := l_person_id;
466 x_period_type_tab(j) := PA_REP_UTIL_GLOB.G_PERIOD_TYPE_C.G_PA_C;
467 x_period_name_tab(j) := pa_periods_cur_rec.PERIOD_NAME;
468 x_global_exp_date_tab(j) := PA_REP_UTIL_GLOB.GetDummyDate;
469 x_period_year_tab(j) := pa_periods_cur_rec.PERIOD_YEAR;
470 x_qm_number_tab(j) := pa_periods_cur_rec.PERIOD_QUARTER;
471 x_period_num_tab(j) := pa_periods_cur_rec.PERIOD_NUM;
472
473 j := j+1;
474 jj := j;
475
476 END IF; -- PA_FORECAST_GRC_PVT.get_resource_capacity returns success
477
478 END IF; -- PA_FORECAST_GRC_PVT.checking date effectivity
479
480 END LOOP; -- PA_PERIODS_CUR loop
481
482 END IF; -- l_pa_period_flag = 'Y'
486 -- IF and loop for GL
483
484 j := jj;
485
487 IF l_gl_period_flag = 'Y' then
488 FOR gl_periods_cur_rec in GL_PERIODS_CUR LOOP
489 l_return_status := FND_API.G_RET_STS_SUCCESS;
490 IF (
491 ( p_in_res_eff_s_date_tab(i) >= gl_periods_cur_rec.PERIOD_START_DATE
492 and p_in_res_eff_s_date_tab(i) <= gl_periods_cur_rec.PERIOD_END_DATE )
493 OR
494 ( p_in_res_eff_e_date_tab(i) >= gl_periods_cur_rec.PERIOD_START_DATE
495 and p_in_res_eff_e_date_tab(i) <= gl_periods_cur_rec.PERIOD_END_DATE )
496 OR
497 ( gl_periods_cur_rec.PERIOD_START_DATE >= p_in_res_eff_s_date_tab(i)
498 and gl_periods_cur_rec.PERIOD_START_DATE <= p_in_res_eff_e_date_tab(i) )
499 ) THEN
500
501
502 start_date_to_be_used := GREATEST(gl_periods_cur_rec.PERIOD_START_DATE
503 , p_in_res_eff_s_date_tab(i)
504 , p_run_start_date);
505 end_date_to_be_used := LEAST(gl_periods_cur_rec.PERIOD_END_DATE
506 , p_in_res_eff_e_date_tab(i)
507 , p_run_end_date);
508
509 PA_FORECAST_GRC_PVT.get_resource_capacity(
510 p_org_id => l_exp_orgz_id,
511 p_person_id => l_person_id,
512 p_start_date => start_date_to_be_used,
513 p_end_date => end_date_to_be_used,
514 x_resource_capacity => l_resource_capacity,
515 x_return_status => l_return_status,
516 x_msg_count => l_msg_count,
517 x_msg_data => l_msg_data);
518
519 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
520
521 --get the period_balance
522 begin
523 select bal.period_balance
524 into l_period_balance
525 from pa_objects obj,
526 pa_summ_balances bal
527 where obj.object_id = bal.object_id
528 and bal.version_id = -1
529 and bal.object_type_code = PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_RES_C
530 and bal.period_type = l_gl_period_type
531 -- and bal.period_set_name = l_period_set_name
532 and bal.period_set_name = l_gl_period_set_name -- bug 3434019
533 and bal.period_name = gl_periods_cur_rec.PERIOD_NAME
534 and bal.global_exp_period_end_date = PA_REP_UTIL_GLOB.G_DUMMY_DATE_C
535 and bal.amount_type_id = PA_REP_UTIL_GLOB.G_amt_type_details.G_res_cap_id
536 and obj.object_type_code = PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_RES_C
537 and obj.balance_type_code = l_balance_type_code
538 and obj.project_org_id = -1
539 and obj.project_organization_id = -1
540 and obj.project_id = -1
541 and obj.task_id = -1
542 and obj.expenditure_org_id = l_OU_id
543 and obj.expenditure_organization_id = l_exp_orgz_id
544 and obj.person_id = l_person_id
545 and obj.assignment_id = -1
546 and obj.work_type_id = -1
547 and obj.org_util_category_id = -1
548 and obj.res_util_category_id = -1
549 for update of bal.object_id;
550 exception
551 when no_data_found
552 then
553 l_period_balance := 0;
554 end;
555
556
557 x_resource_capacity_tab(j) := l_resource_capacity - l_period_balance;
558 x_exp_orgz_id_tab(j) := l_exp_orgz_id;
559 x_person_id_tab(j) := l_person_id;
560 x_period_type_tab(j) := PA_REP_UTIL_GLOB.G_PERIOD_TYPE_C.G_GL_C;
561 x_period_name_tab(j) := gl_periods_cur_rec.PERIOD_NAME;
562 x_global_exp_date_tab(j) := PA_REP_UTIL_GLOB.GetDummyDate;
563 x_period_year_tab(j) := gl_periods_cur_rec.PERIOD_YEAR;
564 x_qm_number_tab(j) := gl_periods_cur_rec.PERIOD_QUARTER;
565 x_period_num_tab(j) := gl_periods_cur_rec.PERIOD_NUM;
566
567 j := j+1;
568 jj := j;
569
570 END IF; -- PA_FORECAST_GRC_PVT.get_resource_capacity returns success
571
572 END IF; -- PA_FORECAST_GRC_PVT.checking date effectivity
573
574 END LOOP; -- GL_PERIODS_CUR loop
575
576 END IF; -- l_gl_period_flag = 'Y
577
578 j := jj;
579
580
581 -- IF and loop for GE
582 IF l_ge_period_flag = 'Y' then
583 FOR ge_periods_cur_rec in GE_PERIODS_CUR LOOP
584 l_return_status := FND_API.G_RET_STS_SUCCESS;
585 IF (
586 ( p_in_res_eff_s_date_tab(i) >= ge_periods_cur_rec.PERIOD_START_DATE
587 and p_in_res_eff_s_date_tab(i) <= ge_periods_cur_rec.GE_DATE )
588 OR
589 ( p_in_res_eff_e_date_tab(i) >= ge_periods_cur_rec.PERIOD_START_DATE
590 and p_in_res_eff_e_date_tab(i) <= ge_periods_cur_rec.GE_DATE )
591 OR
592 ( ge_periods_cur_rec.PERIOD_START_DATE >= p_in_res_eff_s_date_tab(i)
593 and ge_periods_cur_rec.PERIOD_START_DATE <= p_in_res_eff_e_date_tab(i) )
594 ) THEN
595
596
597 start_date_to_be_used := GREATEST(ge_periods_cur_rec.PERIOD_START_DATE
598 , p_in_res_eff_s_date_tab(i)
599 , p_run_start_date);
600 end_date_to_be_used := LEAST(ge_periods_cur_rec.GE_DATE
601 , p_in_res_eff_e_date_tab(i)
602 , p_run_end_date);
603
604 PA_FORECAST_GRC_PVT.get_resource_capacity(
605 p_org_id => l_exp_orgz_id,
606 p_person_id => l_person_id,
607 p_start_date => start_date_to_be_used,
608 p_end_date => end_date_to_be_used,
609 x_resource_capacity => l_resource_capacity,
610 x_return_status => l_return_status,
611 x_msg_count => l_msg_count,
612 x_msg_data => l_msg_data);
613
614 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
615
616 --get the period_balance
617 begin
618 select bal.period_balance
619 into l_period_balance
620 from pa_objects obj,
621 pa_summ_balances bal
622 where obj.object_id = bal.object_id
623 and bal.version_id = -1
624 and bal.object_type_code = PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_RES_C
625 and bal.period_type = 'GE'
626 -- and bal.period_set_name = l_period_set_name
627 and bal.period_set_name = PA_REP_UTIL_GLOB.G_DUMMY_C --bug 3434019
628 and bal.period_name = PA_REP_UTIL_GLOB.G_DUMMY_C
629 and bal.global_exp_period_end_date = PA_REP_UTIL_GLOB.G_DUMMY_DATE_C
630 and bal.amount_type_id = PA_REP_UTIL_GLOB.G_amt_type_details.G_res_cap_id
631 and obj.object_type_code = PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_RES_C
632 and obj.balance_type_code = l_balance_type_code
633 and obj.project_org_id = -1
634 and obj.project_organization_id = -1
635 and obj.project_id = -1
636 and obj.task_id = -1
637 and obj.expenditure_org_id = l_OU_id
638 and obj.expenditure_organization_id = l_exp_orgz_id
639 and obj.person_id = l_person_id
640 and obj.assignment_id = -1
641 and obj.work_type_id = -1
642 and obj.org_util_category_id = -1
643 and obj.res_util_category_id = -1
644 for update of bal.object_id;
645 exception
646 when no_data_found
647 then
648 l_period_balance := 0;
649 end;
650
651
652 x_resource_capacity_tab(j) := l_resource_capacity - l_period_balance;
653 x_exp_orgz_id_tab(j) := l_exp_orgz_id;
654 x_person_id_tab(j) := l_person_id;
655 x_period_type_tab(j) := PA_REP_UTIL_GLOB.G_PERIOD_TYPE_C.G_GE_C;
656 x_period_name_tab(j) := PA_REP_UTIL_GLOB.G_DUMMY_C;
657 x_global_exp_date_tab(j) := ge_periods_cur_rec.GE_DATE;
658 x_period_year_tab(j) := ge_periods_cur_rec.PERIOD_YEAR;
659 x_qm_number_tab(j) := ge_periods_cur_rec.PERIOD_MONTH;
660 x_period_num_tab(j) := -1;
661
662 j := j+1;
663 jj := j;
664
665 END IF; -- PA_FORECAST_GRC_PVT.get_resource_capacity returns success
666
667 END IF; -- PA_FORECAST_GRC_PVT.checking date effectivity
668
669 END LOOP; -- GE_PERIODS_CUR loop
670
671 END IF; -- l_ge_period_flag = 'Y'
672
673 j := jj;
674
675 END LOOP; -- looping through the PL/SQL tables p_exp_org_id_tab and p_person_id_tab
676
677 x_return_status := l_return_status;
678
679 EXCEPTION
680 WHEN NO_DATA_FOUND THEN
681 Null;
682 WHEN OTHERS THEN
683 -- 4537865 : RESET other OUT params value also
684 x_resource_capacity_tab.delete;
685 x_exp_orgz_id_tab.delete;
686 x_person_id_tab.delete;
687 x_period_type_tab.delete;
688 x_period_name_tab.delete;
689 x_global_exp_date_tab.delete;
690 x_period_year_tab.delete;
691 x_qm_number_tab.delete;
692 x_period_num_tab.delete;
693 -- 4537865 : End
694
695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
696 x_msg_count := 1;
697 x_msg_data := SQLERRM;
698 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FORECAST_GRC_PVT',
699 p_procedure_name => 'Get_Capacity_Vector');
700 RAISE ; -- 4537865 : Based on usage included RAISE
701 END Get_Capacity_Vector;
702
703 END PA_FORECAST_GRC_PVT;