[Home] [Help]
PACKAGE BODY: APPS.PA_FORECAST_ITEMS_UTILS
Source
1 PACKAGE BODY PA_FORECAST_ITEMS_UTILS AS
2 /* $Header: PARFIUTB.pls 120.7.12010000.3 2008/09/24 10:20:47 rthumma ship $ */
3
4 ------------------------------------------------------------------------------------------------------------------
5 -- This function gets the unique identifier for the forecast item
6 -- Input parameters
7 -- Parameters Type Required Description
8 --
9 -- Out parameters
10 -- li_forecast_item_id NUMBER YES It returns the unique identifier for forecast item
11 --
12 --------------------------------------------------------------------------------------------------------------------
13 FUNCTION Get_Next_ForeCast_Item_ID RETURN NUMBER IS
14 li_forecast_item_id NUMBER;
15 BEGIN
16 BEGIN
17
18 SELECT pa_forecast_items_s.NEXTVAL
19 INTO li_forecast_item_id
20 FROM DUAL;
21
22 EXCEPTION
23 WHEN OTHERS then
24 RAISE;
25 END;
26
27 RETURN li_forecast_item_id;
28
29 END Get_Next_ForeCast_Item_ID;
30
31 -- This function returns a lock handle for retrieving
32 -- and releasing a dbms_lock. We have made it as
33 -- an autonomous transaction because it issues a commit.
34 -- However, requesting and releasing a lock does not
35 -- issue a commit;
36 PROCEDURE allocate_unique(p_lock_name IN VARCHAR2,
37 p_lock_handle OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
38 IS
39 PRAGMA AUTONOMOUS_TRANSACTION;
40 BEGIN
41 dbms_lock.allocate_unique(
42 lockname => p_lock_name,
43 lockhandle => p_lock_handle);
44 commit;
45
46 --4537865
47 EXCEPTION
48 WHEN OTHERS THEN
49 p_lock_handle := NULL ;
50 -- RAISE is not needed here . Caller takes care of this scenario by checking against p_lock_handle
51 END allocate_unique;
52
53 ------------------------------------------------------------------------------------------------------------
54 -- This function will set and acquire the user lock
55 --
56 -- Input parameters
57 -- Parameter Type Required Description
58 -- p_assignment_id NUMBER Yes Assignment Id used for locking the corresponding record
59 -- p_lock_commitmode BOOLEAN Yes Parameter to set the condition for releasing the lock
60 --
61 -- Return Values
62 -- 0 Success
63 -- Other Unable to acquire lock
64 --------------------------------------------------------------------------------------------------------------
65
66
67 FUNCTION Set_User_Lock ( p_source_id IN NUMBER,
68 p_lock_for IN VARCHAR2)
69
70 RETURN NUMBER
71 IS
72 lock_status NUMBER;
73 lock_name VARCHAR2(50);
74 lockhndl VARCHAR2(128);
75 lock_mode NUMBER:=6;
76 lock_commitmode BOOLEAN:=TRUE;
77 BEGIN
78
79 lock_name := 'FI-' || p_lock_for || '-' || p_source_id;
80 IF ( p_source_id IS NULL ) THEN
81 Return -99;
82 END IF;
83
84 /* Get lock handle for user lock */
85 pa_forecast_items_utils.allocate_unique(
86 p_lock_name =>lock_name,
87 p_lock_handle =>lockhndl);
88
89 IF ( lockhndl IS NOT NULL ) then
90 /* Request the lock */
91 lock_status := dbms_lock.request( lockhandle => lockhndl,
92 lockmode => lock_mode,
93 release_on_commit => lock_CommitMode);
94
95 IF ( lock_status = 0 ) then -- Got the lock
96 Return 0;
97 ELSE
98 Return (-1*lock_status);
99 -- Return the status obtained on request
100 END IF;
101 ELSE
102 Return -99; -- Failed to allocate lock
103 END IF;
104 RETURN(lock_status);
105
106 END Set_User_Lock;
107
108
109 -------------------------------------------------------
110 -- This procedure will release user lock
111 --
112 -- Input parameters
113 -- Parameter Type Required Description
114 -- p_assignment_id NUMBER Yes Assignment id which was used to lock the transaction
115 --
116 -- Return Values
117 -- 0 Success
118 -- Other Unable to acquire lock
119 ---------------------------------------------------------
120 FUNCTION Release_User_Lock
121 (p_source_id IN NUMBER,
122 p_lock_for IN VARCHAR2)
123 RETURN NUMBER
124 IS
125 lock_status number;
126 lock_name VARCHAR2(50);
127 lockhndl VARCHAR2(128);
128 BEGIN
129 lock_name := 'FI-' || p_lock_for || '-' || p_source_id;
130 IF ( p_source_id IS NULL ) THEN
131 Return -99;
132 END IF;
133
134 /* Get lock handle for user lock */
135 pa_forecast_items_utils.allocate_unique(
136 p_lock_name =>lock_name,
137 p_lock_handle =>lockhndl);
138
139 IF ( lockhndl IS NOT NULL ) then
140 lock_status := dbms_lock.release(lockhandle =>lockhndl);
141
142 IF ( lock_status = 0 ) then -- Got the lock
143 Return 0;
144 ELSE
145 Return (-1*lock_status);
146 -- Return the status obtained on request
147 END IF;
148 ELSE
149 Return -99; -- Failed to allocate lock
150 END IF;
151 RETURN(lock_status);
152
153 END Release_User_Lock;
154
155
156 --------------------------------------------------------------------------------------------------------------
157 -- This procedure prints the text which is being passed as the input
158 -- Input parameters
159 -- Parameters Type Required Description
160 -- p_log_msg VARCHAR2 YES It stores text which you want to print on screen
161 -- Out parameters
162 ----------------------------------------------------------------------------------------------------------------
163 PROCEDURE log_message (p_log_msg IN VARCHAR2)
164 IS
165 BEGIN
166 -- dbms_output.put_line('log: ' || p_log_msg);
167 NULL;
168 END log_message;
169
170
171 ---------------------------------------------------------------------------------------------------------------------
172 -- This procedure gets the schedule related to the resource assignment
173 -- Input parameters
174 -- Parameters Type Required Description
175 -- p_resource_id NUMBER YES Resource id
176 -- p_start_date DATE YES Start date for the resource
177 -- p_end_date DATE YES End date for the resource
178 -- Out parameters
179 -- x_ScheduleTab ScheduleTabTyp YES It stores the resource schedule for the given data range
180 ---------------------------------------------------------------------------------------------------------------------
181 PROCEDURE Get_Resource_Asgn_Schedules (
182 p_resource_id IN NUMBER,
183 p_start_date IN DATE,
184 p_end_date IN DATE,
185 x_ScheduleTab OUT NOCOPY PA_FORECAST_GLOB.ScheduleTabTyp, /* 2674619 - Nocopy change */
186 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
187 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
188 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
189 IS
190
191 CURSOR cur_res_asgn IS SELECT sch.schedule_id schedule_id,
192 sch.monday_hours monday_hours,
193 sch.tuesday_hours tuesday_hours,
194 sch.wednesday_hours wednesday_hours,
195 sch.thursday_hours thursday_hours,
196 sch.friday_hours friday_hours,
197 sch.saturday_hours saturday_hours,
198 sch.sunday_hours sunday_hours,
199 sch.status_code status_code,
200 sch.start_date start_date,
201 sch.end_date end_date,
202 sch.forecast_txn_version_number forecast_txn_version_number,
203 sch.forecast_txn_generated_flag forecast_txn_generated_flag,
204 pst.project_system_status_code system_status_code
205 FROM pa_schedules sch,
206 pa_project_assignments prasgn,
207 pa_project_statuses pst
208 WHERE ( ( p_start_date BETWEEN sch.start_date AND sch.end_date)
209 OR ( p_end_date BETWEEN sch.start_date AND sch.end_date)
210 OR ( p_start_date < sch.start_date AND p_end_date > sch.end_date) )
211 AND sch.assignment_id = prasgn.assignment_id
212 AND prasgn.resource_id = p_resource_id
213 AND sch.status_code = pst.project_status_code
214 AND pst.project_system_status_code <> 'STAFFED_ASGMT_CANCEL'
215 AND PST.STATUS_TYPE = 'STAFFED_ASGMT' --Bug 7301626
216 ORDER BY sch.start_date;
217
218 cur_res_asgn_rec cur_res_asgn%ROWTYPE;
219 li_cnt NUMBER:=0;
220 BEGIN
221 PA_DEBUG.Init_err_stack(
222 'PA_FORECAST_ITEMS_UTILS.Get_Resource_Asgn_Schedules');
223
224 BEGIN
225 li_cnt :=1;
226
227 OPEN cur_res_asgn;
228 LOOP
229
230 FETCH cur_res_asgn INTO cur_res_asgn_rec;
231 EXIT WHEN cur_res_asgn%NOTFOUND;
232
233 x_ScheduleTab(li_cnt).schedule_id := cur_res_asgn_rec.schedule_id;
234 x_ScheduleTab(li_cnt).status_code := cur_res_asgn_rec.status_code;
235 x_ScheduleTab(li_cnt).start_date := cur_res_asgn_rec.start_date;
236 x_ScheduleTab(li_cnt).end_date := cur_res_asgn_rec.end_date;
237 x_ScheduleTab(li_cnt).monday_hours := cur_res_asgn_rec.monday_hours;
238 x_ScheduleTab(li_cnt).tuesday_hours := cur_res_asgn_rec.tuesday_hours;
239 x_ScheduleTab(li_cnt).wednesday_hours := cur_res_asgn_rec.wednesday_hours;
240 x_ScheduleTab(li_cnt).thursday_hours := cur_res_asgn_rec.thursday_hours;
241 x_ScheduleTab(li_cnt).friday_hours := cur_res_asgn_rec.friday_hours;
242 x_ScheduleTab(li_cnt).saturday_hours := cur_res_asgn_rec.saturday_hours;
243 x_ScheduleTab(li_cnt).sunday_hours := cur_res_asgn_rec.sunday_hours;
244 x_ScheduleTab(li_cnt).forecast_txn_version_number := cur_res_asgn_rec.forecast_txn_version_number;
245 x_ScheduleTab(li_cnt).forecast_txn_generated_flag := cur_res_asgn_rec.forecast_txn_generated_flag;
246 x_ScheduleTab(li_cnt).system_status_code := cur_res_asgn_rec.system_status_code;
247 li_cnt := li_cnt +1;
248
249
250 END LOOP;
251
252 CLOSE cur_res_asgn;
253
254 PA_DEBUG.Reset_Err_Stack;
255
256 x_return_status := FND_API.G_RET_STS_SUCCESS;
257 EXCEPTION
258 WHEN OTHERS THEN
259 x_msg_count := 1;
260 x_msg_data := sqlerrm;
261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262 FND_MSG_PUB.add_exc_msg
263 (p_pkg_name =>
264 'PA_FORECAST_ITEMS_UTILS.Get_Resource_Asgn_Schedules',
265 p_procedure_name => PA_DEBUG.G_Err_Stack);
266 raise;
267
268 END;
269
270
271 END Get_Resource_Asgn_Schedules;
272
273
274 -------------------------------------------------------------------------------------------------------------
275 -- This procedure will get all the schedule for the given assignment id
276 -- and having process mode as 'GENERATE'
277 -- Input parameters
278 -- Parameters Type Required Description
279 -- p_assignment_id NUMBER YES Assignment id for which schedule record is to be needed
280 -- p_start_date DATE YES Start date from which the schedule is to be needed
281 -- p_end_date DATE YES End date from which the schedule is to be needed
282 -- p_process_mode VARCHAR2 YES Process mode i.e. wheather the assignment is to be
283 -- Generated or not
284 -- Out parameters
285 -- X_ScheduleTab ScheduleTabTyp YES It stores the schedules record
286 --------------------------------------------------------------------------------------------------------------
287 PROCEDURE Get_Assignment_Schedule(p_assignment_id IN NUMBER,
288 p_start_date IN DATE ,
289 p_end_date IN DATE,
290 p_process_mode IN VARCHAR2,
291 X_ScheduleTab OUT NOCOPY PA_FORECAST_GLOB.ScheduleTabTyp, /* 2674619 - Nocopy change */
292 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
293 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
294 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
295
296 CURSOR cur_asgn_sch IS SELECT sch.schedule_id schedule_id,
297 sch.monday_hours monday_hours,
298 sch.tuesday_hours tuesday_hours,
299 sch.wednesday_hours wednesday_hours,
300 sch.thursday_hours thursday_hours,
301 sch.friday_hours friday_hours,
302 sch.saturday_hours saturday_hours,
303 sch.sunday_hours sunday_hours,
304 sch.status_code status_code,
305 sch.start_date start_date,
306 sch.end_date end_date,
307 sch.forecast_txn_version_number forecast_txn_version_number,
308 sch.forecast_txn_generated_flag forecast_txn_generated_flag,
309 pst.project_system_status_code system_status_code
310 FROM pa_schedules sch, pa_project_statuses pst
311 WHERE p_start_date IS NOT NULL
312 AND sch.status_code = pst.project_status_code
313 AND p_end_date IS NOT NULL
314 AND sch.assignment_id=p_assignment_id
315 /** commented out as the FIs were not generated between the schedules
316 * when two or more wf - process launched concurrently
317 --AND sch.forecast_txn_generated_flag=
318 -- DECODE(p_process_mode,'GENERATE','N',sch.forecast_txn_generated_flag)
319 **/
320 AND ( ( p_start_date BETWEEN sch.start_date AND sch.end_date)
321 OR ( p_end_date BETWEEN sch.start_date AND sch.end_date)
322 OR ( p_start_date < sch.start_date AND p_end_date > sch.end_date) )
323 UNION
324 SELECT sch.schedule_id schedule_id,
325 sch.monday_hours monday_hours,
326 sch.tuesday_hours tuesday_hours,
327 sch.wednesday_hours wednesday_hours,
328 sch.thursday_hours thursday_hours,
329 sch.friday_hours friday_hours,
330 sch.saturday_hours saturday_hours,
331 sch.sunday_hours sunday_hours,
332 sch.status_code status_code,
333 sch.start_date start_date,
334 sch.end_date end_date,
335 sch.forecast_txn_version_number forecast_txn_version_number,
336 sch.forecast_txn_generated_flag forecast_txn_generated_flag,
337 pst.project_system_status_code system_status_code
338 FROM pa_schedules sch, pa_project_statuses pst
339 WHERE p_start_date IS NULL
340 AND p_end_date IS NULL
341 AND sch.assignment_id=p_assignment_id
342 AND sch.status_code = pst.project_status_code
343 /** commented out as the FIs were not generated between the schedules
344 * when two or more wf - process launched concurrently
345 --AND sch.forecast_txn_generated_flag=
346 -- DECODE(p_process_mode,'GENERATE','N',sch.forecast_txn_generated_flag)
347 **/
348 ORDER BY start_date;
349
350 cur_asgn_sch_rec cur_asgn_sch%ROWTYPE;
351 li_cnt NUMBER:=0;
352 BEGIN
353 PA_DEBUG.Init_err_stack(
354 'PA_FORECAST_ITEMS_UTILS.Get_Resource_Asgn_Schedules');
355 BEGIN
356
357 li_cnt :=1;
358
359
360 OPEN cur_asgn_sch;
361 LOOP
362
363 FETCH cur_asgn_sch INTO cur_asgn_sch_rec;
364 EXIT WHEN cur_asgn_sch%NOTFOUND;
365
366 x_ScheduleTab(li_cnt).schedule_id := cur_asgn_sch_rec.schedule_id;
367 x_ScheduleTab(li_cnt).status_code := cur_asgn_sch_rec.status_code;
368 x_ScheduleTab(li_cnt).start_date := cur_asgn_sch_rec.start_date;
369 x_ScheduleTab(li_cnt).end_date := cur_asgn_sch_rec.end_date;
370 x_ScheduleTab(li_cnt).monday_hours := cur_asgn_sch_rec.monday_hours;
371 x_ScheduleTab(li_cnt).tuesday_hours := cur_asgn_sch_rec.tuesday_hours;
372 x_ScheduleTab(li_cnt).wednesday_hours := cur_asgn_sch_rec.wednesday_hours;
373 x_ScheduleTab(li_cnt).thursday_hours := cur_asgn_sch_rec.thursday_hours;
374 x_ScheduleTab(li_cnt).friday_hours := cur_asgn_sch_rec.friday_hours;
375 x_ScheduleTab(li_cnt).saturday_hours := cur_asgn_sch_rec.saturday_hours;
376 x_ScheduleTab(li_cnt).sunday_hours := cur_asgn_sch_rec.sunday_hours;
377 x_ScheduleTab(li_cnt).forecast_txn_version_number := cur_asgn_sch_rec.forecast_txn_version_number;
378 x_ScheduleTab(li_cnt).forecast_txn_generated_flag := cur_asgn_sch_rec.forecast_txn_generated_flag;
379 x_ScheduleTab(li_cnt).system_status_code := cur_asgn_sch_rec.system_status_code;
380
381 li_cnt := li_cnt +1;
382
383
384 END LOOP;
385
386 CLOSE cur_asgn_sch;
387
388 x_return_status := FND_API.G_RET_STS_SUCCESS;
389 PA_DEBUG.Reset_Err_Stack;
390 EXCEPTION
391 WHEN OTHERS THEN
392 x_msg_count := 1;
393 x_msg_data := sqlerrm;
394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
395 FND_MSG_PUB.add_exc_msg
396 (p_pkg_name =>
397 'PA_FORECAST_ITEMS_UTILS.Get_Assignment_Schedule',
398 p_procedure_name => PA_DEBUG.G_Err_Stack);
399 raise;
400 END;
401
402 END Get_Assignment_Schedule;
403
404
405
406 -------------------------------------------------------------------------------
407 -- Function Get_Period_Set_Name
408 -- Purpose To get the Period name for OU
409 -- Input parameters
410 -- Parameters Type Required Description
411 -- p_org_id NUMBER YES Operating Unit id
412 -- Generated or not
413 -- Out parameters
414 ---------------------------------------------------------------------------------
415
416 FUNCTION Get_Period_Set_Name(p_org_id NUMBER) RETURN VARCHAR2 IS
417 lv_period_set_name VARCHAR2(15);
418 BEGIN
419 PA_FORECASTITEM_PVT.print_message('Inside Get_Period_Set_Name');
420
421
422 -- 2196924: Adding case when p_org_id = -88
423 -- This may occur when there's no HR assignment for
424 -- part of the resources time, so no ou for which
425 -- to select work type id.
426
427 if (p_org_id = -88) then
428 return '-99';
429 else
430 BEGIN
431
432 /* Commented for bug 3434019. Period_set_name will be fetched from pa_implementations_all based on OU.
433 SELECT gl.period_set_name
434 INTO lv_period_set_name
435 FROM gl_sets_of_books gl,
436 pa_implementations_all imp
437 WHERE imp.set_of_books_id=gl.set_of_books_id
438 AND nvl(imp.org_id,-99) = nvl(p_org_id,-99);
439 */
440 --R12: MOAC Changes: Bug 4363092: Removed nvl usage with org_id
441 SELECT imp.period_set_name
442 INTO lv_period_set_name
443 FROM pa_implementations_all imp
444 WHERE imp.org_id = p_org_id;
445
446 EXCEPTION
447 WHEN NO_DATA_FOUND THEN
448 lv_period_set_name := 'NO_DATA_FOUND';
449 NULL;
450 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND');
451 WHEN OTHERS THEN
452 lv_period_set_name := 'ERROR';
453 NULL;
454 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND');
455 END;
456 END IF;
457
458 RETURN (lv_period_set_name);
459
460 END Get_Period_Set_Name;
461
462
463
464 ----------------------------------------------------------------------------------------------------
465 -- Procedure Get_Work_Type_Details
466 -- Purpose To get detail for the passed work type
467 -- Input parameters
468 -- Parameters Type Required Description
469 -- p_work_type_id NUMBER YES Work type id
470 -- Out parameters
471 -- x_BillableFlag VARCHAR2 YES Billable flag
472 -- x_ResUtilPercentage NUMBER YES resource util percentage
473 -- x_OrgUtilPercentage NUMBER YES Org util percentage
474 -- x_ResUtilCategoryID NUMBER YES resource util category id
475 -- x_OrgUtilCategoryID NUMBER YES Org uti category id
476 -- x_ReduceCapacityFlag VARCHAR2 YES Reduced capacity Flag
477 ---------------------------------------------------------------------------------------------------------
478 PROCEDURE Get_Work_Type_Details(p_work_type_id IN NUMBER,
479 x_BillableFlag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
480 x_ResUtilPercentage OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
481 x_OrgUtilPercentage OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
482 x_ResUtilCategoryID OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
483 x_OrgUtilCategoryID OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
484 x_ReduceCapacityFlag OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
485
486 l_msg_index_out NUMBER;
487 BEGIN
488
489
490 -- 2196924: Adding case when p_work_type_id is null
491 -- This may occur when there's no HR assignment for
492 -- part of the resources time, so no ou for which
493 -- to select work type id.
494 if (p_work_type_id is null) then
495 x_ResUtilPercentage := null;
496 x_OrgUtilPercentage := null;
497 x_ResUtilCategoryID := null;
498 x_OrgUtilCategoryID := null;
499 x_ReduceCapacityFlag := null;
500 else
501
502
503 SELECT wk.billable_capitalizable_flag,
504 wk.res_utilization_percentage,
505 wk.org_utilization_percentage,
506 wk.res_util_category_id,
507 wk.org_util_category_id,
508 wk.reduce_capacity_flag
509 INTO x_BillableFlag,
510 x_ResUtilPercentage,
511 x_OrgUtilPercentage,
512 x_ResUtilCategoryID,
513 x_OrgUtilCategoryID,
514 x_ReduceCapacityFlag
515 FROM pa_work_types_b wk
516 WHERE wk.work_type_id=p_work_type_id;
517 end if;
518 EXCEPTION
519
520
521 WHEN OTHERS THEN
522 x_BillableFlag := 'N';
523 x_ResUtilPercentage := 0;
524 x_OrgUtilPercentage := 0;
525 x_ResUtilCategoryID := 0;
526 x_OrgUtilCategoryID := 0;
527 x_ReduceCapacityFlag := 'N';
528 NULL;
529
530
531 END Get_Work_Type_Details;
532
533
534
535 ---------------------------------------------------------------------------------------------------------
536 -- Procedure Get_PA_Period_Name
537 -- Purpose To get the PA Period name for OU
538 -- Input parameters
539 -- Parameters Type Required Description
540 -- p_org_id NUMBER YES Org id
541 -- p_start_date DATE YES Start date
542 -- p_end_date DATE YES End date
543 -- Out parameters
544 -- x_StartDateTab DateTabTyp YES Used to store start date in bulk
545 -- x_EndDateTab DateTabTyp YES Used to store end date in bulk
546 -- x_PAPeriodNameTab PeriodNameTabTyp YES Used to store period name
547 ---------------------------------------------------------------------------------------------------------------
548 PROCEDURE Get_PA_Period_Name(p_org_id IN NUMBER,
549 p_start_date IN DATE,
550 p_end_date IN DATE,
551 x_StartDateTab OUT NOCOPY PA_FORECAST_GLOB.DateTabTyp, /* 2674619 - Nocopy change */
552 x_EndDateTab OUT NOCOPY PA_FORECAST_GLOB.DateTabTyp, /* 2674619 - Nocopy change */
553 x_PAPeriodNameTab OUT NOCOPY PA_FORECAST_GLOB.PeriodNameTabTyp) /* 2674619 - Nocopy change */
554 IS
555
556 BEGIN
557
558 PA_FORECASTITEM_PVT.print_message('Inside Get_PA_Period_Name');
559
560 -- 2196924: Adding case when p_org_id = -88
561 -- This may occur when there's no HR assignment for
562 -- part of the resources time, so no ou for which
563 -- to select work type id.
564 if (p_org_id = -88) then
565 x_StartDateTab(1) := p_start_date;
566 x_EndDateTab(1) := p_end_date;
567 x_PAPeriodNameTab(1) := '-99';
568 else
569
570 IF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
571
572 BEGIN
573
574 /* Commented for bug 3434019. Pa_periods_all is used to fetch data.
575 SELECT glper.start_date,
576 glper.end_date,
577 glper.period_name
578 BULK COLLECT INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
579 FROM pa_implementations_all imp,
580 gl_sets_of_books gl,
581 gl_periods glper,
582 gl_date_period_map glmaps
583 WHERE nvl(imp.org_id,-99) = nvl(p_org_id,-99)
584 AND imp.set_of_books_id = gl.set_of_books_id
585 AND gl.period_set_name = glper.period_set_name
586 AND imp.pa_period_type = glper.period_type
587 AND glmaps.period_type = glper.period_type
588 AND glmaps.period_name = glper.period_name
589 AND glmaps.period_set_name = glper.period_set_name
590 AND ( (p_start_date BETWEEN glper.start_date AND glper.end_date)
591 OR (p_end_date BETWEEN glper.start_date AND glper.end_date)
592 OR ( p_start_date < glper.start_date AND
593 p_end_date > glper.end_date ))
594 order by glper.start_date;
595 */
596 --R12: MOAC Changes: Bug 4363092: Removed nvl usage with org_id
597 SELECT pp.start_date,
598 pp.end_date,
599 pp.period_name
600 BULK COLLECT
601 INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
602 FROM pa_periods_all pp
603 WHERE pp.org_id = p_org_id
604 --Bug 4276273 - trunc added
605 AND ( (trunc(p_start_date) BETWEEN pp.start_date AND
606 pp.end_date)
607 OR (trunc(p_end_date) BETWEEN pp.start_date AND pp.end_date)
608 OR ( trunc(p_start_date) < pp.start_date AND
609 trunc(p_end_date) > pp.end_date ))
610 order by pp.start_date;
611
612
613 END;
614
615 ELSIF p_start_date IS NOT NULL THEN
616
617
618 BEGIN
619
620 /* Commented for bug 3434019. Instead pa_periods_all is used to fetch data.
621 SELECT glper.start_date,
622 glper.end_date,
623 glper.period_name
624 BULK COLLECT
625 INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
626 FROM pa_implementations_all imp,
627 gl_sets_of_books gl,
628 gl_periods glper,
629 gl_date_period_map glmaps
630 WHERE nvl(imp.org_id,-99) = nvl(p_org_id,-99)
631 AND imp.set_of_books_id = gl.set_of_books_id
632 AND gl.period_set_name = glper.period_set_name
633 AND imp.pa_period_type = glper.period_type
634 AND glmaps.period_type = glper.period_type
635 AND glmaps.period_name = glper.period_name
636 AND glmaps.period_set_name = glper.period_set_name
637 AND p_start_date BETWEEN glper.start_date AND glper.end_date
638 order by glper.end_date;
639 */
640 --R12: MOAC Changes: Bug 4363092: Removed nvl usage with org_id
641 SELECT pp.start_date,
642 pp.end_date,
643 pp.period_name
644 BULK COLLECT
645 INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
646 FROM pa_periods_all pp
647 WHERE pp.org_id = p_org_id
648 --Bug 4276273 - trunc added
649 AND trunc(p_start_date) BETWEEN pp.start_date and pp.end_date
650 order by pp.end_date;
651
652 END;
653
654 END IF;
655 end if;
656
657 if (NVL(x_StartDateTab.count,0) = 0) then
658 PA_FORECASTITEM_PVT.print_message('No periods found.');
659 else
660 PA_FORECASTITEM_PVT.print_message('x_StartDateTab(first): ' || x_StartDateTab(x_StartDateTab.first));
661 PA_FORECASTITEM_PVT.print_message('x_EndDateTab(first): ' || x_EndDateTab(x_EndDateTab.first));
662 PA_FORECASTITEM_PVT.print_message('x_PAPeriodNameTab(first): ' || x_PAPeriodNameTab(x_PAPeriodNameTab.first));
663 PA_FORECASTITEM_PVT.print_message('x_StartDateTab(last): ' || x_StartDateTab(x_StartDateTab.last));
664 PA_FORECASTITEM_PVT.print_message('x_EndDateTab(last): ' || x_EndDateTab(x_EndDateTab.last));
665 PA_FORECASTITEM_PVT.print_message('x_PAPeriodNameTab(last): ' || x_PAPeriodNameTab(x_PAPeriodNameTab.last));
666
667 end if;
668
669 EXCEPTION
670 WHEN NO_DATA_FOUND THEN
671 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND');
672 NULL;
673 WHEN OTHERS THEN -- 4537865 : Included this block
674 x_StartDateTab.delete;
675 x_EndDateTab.delete;
676 x_PAPeriodNameTab.delete ;
677 FND_MSG_PUB.add_exc_msg
678 ( p_pkg_name => 'PA_FORECAST_ITEMS_UTILS'
679 , p_procedure_name => 'Get_PA_Period_Name'
680 , p_error_text => SUBSTRB(SQLERRM,1,240));
681 RAISE ;
682 END Get_PA_Period_Name;
683
684
685
686 -----------------------------------------------------------------------------------------------------------
687 -- Procedure Get_GL_Period_Name
688 -- Purpose To get the GL Period name for OU
689 -- Input parameters
690 -- Parameters Type Required Description
691 -- p_org_id NUMBER YES Org id
692 -- p_start_date DATE YES Start date
693 -- p_end_date DATE YES End date
694 -- Out parameters
695 -- x_StartDateTab DateTabTyp YES Used to store start date in bulk
696 -- x_EndDateTab DateTabTyp YES Used to store end date in bulk
697 -- x_PAPeriodNameTab PeriodNameTabTyp YES Used to store period name
698 ------------------------------------------------------------------------------------------------------------
699 PROCEDURE Get_GL_Period_Name(p_org_id IN NUMBER,
700 p_start_date IN DATE,
701 p_end_date IN DATE,
702 x_StartDateTab OUT NOCOPY PA_FORECAST_GLOB.DateTabTyp, /* 2674619 - Nocopy change */
703 x_EndDateTab OUT NOCOPY PA_FORECAST_GLOB.DateTabTyp, /* 2674619 - Nocopy change */
704 x_PAPeriodNameTab OUT NOCOPY PA_FORECAST_GLOB.PeriodNameTabTyp) /* 2674619 - Nocopy change */
705 IS
706
707 BEGIN
708 PA_FORECASTITEM_PVT.print_message('Inside Get_GL_Period_Name');
709
710 -- 2196924: Adding case when p_org_id = -88
711 -- This may occur when there's no HR assignment for
712 -- part of the resources time, so no ou for which
713 -- to select work type id.
714 if (p_org_id = -88) then
715 x_StartDateTab(1) := p_start_date;
716 x_EndDateTab(1) := p_end_date;
717 x_PAPeriodNameTab(1) := '-99';
718 else
719
720 IF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
721
722 BEGIN
723 --R12: MOAC Changes: Bug 4363092: Removed nvl usage with org_id
724 SELECT glper.start_date,
725 glper.end_date,
726 glper.period_name
727 BULK COLLECT
728 INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
729 FROM pa_implementations_all imp,
730 gl_sets_of_books gl,
731 gl_periods glper,
732 gl_date_period_map glmaps
733 WHERE imp.org_id = p_org_id
734 AND imp.set_of_books_id = gl.set_of_books_id
735 AND gl.period_set_name = glper.period_set_name
736 AND gl.accounted_period_type = glper.period_type
737 AND glmaps.period_type = glper.period_type
738 AND glmaps.period_name = glper.period_name
739 AND glmaps.period_set_name = glper.period_set_name
740 --Bug 4276273 - trunc added
741 AND ( (trunc(p_start_date) BETWEEN glper.start_date AND
742 glper.end_date)
743 OR (trunc(p_end_date) BETWEEN glper.start_date AND
744 glper.end_date)
745 OR ( trunc(p_start_date) < glper.start_date AND
746 trunc(p_end_date) > glper.end_date ))
747 order by glper.start_date;
748
749
750 END;
751
752 ELSIF p_start_date IS NOT NULL THEN
753
754 BEGIN
755 --R12: MOAC Changes: Bug 4363092: Removed nvl usage with org_id
756 SELECT glper.start_date,
757 glper.end_date,
758 glper.period_name
759 BULK COLLECT
760 INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
761 FROM pa_implementations_all imp,
762 gl_sets_of_books gl,
763 gl_periods glper,
764 gl_date_period_map glmaps
765 WHERE imp.org_id = p_org_id
766 AND imp.set_of_books_id = gl.set_of_books_id
767 AND gl.period_set_name = glper.period_set_name
768 AND gl.accounted_period_type = glper.period_type
769 AND glmaps.period_type = glper.period_type
770 AND glmaps.period_name = glper.period_name
771 AND glmaps.period_set_name = glper.period_set_name
772 --Bug 4276273 - trunc added
773 AND trunc(p_start_date) BETWEEN glper.start_date AND
774 glper.end_date
775 order by glper.start_date;
776
777 END;
778 END IF;
779 END IF;
780 EXCEPTION
781 WHEN NO_DATA_FOUND THEN
782 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND');
783 NULL;
784 WHEN OTHERS THEN -- 4537865 : Included this block
785 x_StartDateTab.delete;
786 x_EndDateTab.delete;
787 x_PAPeriodNameTab.delete ;
788 FND_MSG_PUB.add_exc_msg
789 ( p_pkg_name => 'PA_FORECAST_ITEMS_UTILS'
790 , p_procedure_name => 'Get_GL_Period_Name'
791 , p_error_text => SUBSTRB(SQLERRM,1,240));
792 RAISE ;
793 END Get_GL_Period_Name;
794
795
796
797 --------------------------------------------------------------------------------------------------------
798 -- Procedure Get_Resource_OU
799 -- Purpose To get the Resource OU for a Period
800 -- Input parameters
801 -- Parameters Type Required Description
802 -- p_resource_id NUMBER YES Resource id
803 -- p_start_date DATE YES Start date
804 -- p_end_date DATE YES End date
805 -- Out parameters
806 -- x_StartDateTab DateTabTyp YES Used to store start date in bulk
807 -- x_EndDateTab DateTabTyp YES Used to store end date in bulk
808 -- x_ResourceOUTab NumberTabTyp YES Used to store resource id
809 ----------------------------------------------------------------------------------------------------------
810 PROCEDURE Get_Resource_OU(p_resource_id IN NUMBER,
811 p_start_date IN DATE,
812 p_end_date IN DATE,
813 x_StartDateTab OUT NOCOPY PA_FORECAST_GLOB.DateTabTyp, /* 2674619 - Nocopy change */
814 x_EndDateTab OUT NOCOPY PA_FORECAST_GLOB.DateTabTyp, /* 2674619 - Nocopy change */
815 x_ResourceOUTab OUT NOCOPY PA_FORECAST_GLOB.NumberTabTyp) /* 2674619 - Nocopy change */
816 IS
817
818 ld_start_date DATE;
819 ld_end_date DATE;
820 li_count NUMBER;
821 li_first_index NUMBER;
822 li_last_index NUMBER;
823 li_new_first_index NUMBER;
824 li_new_last_index NUMBER;
825 ld_first_start_date DATE;
826 li_first_ou NUMBER;
827 ld_last_end_date DATE;
828 li_last_ou NUMBER;
829
830 g_TimelineProfileSetup PA_TIMELINE_GLOB.TimelineProfileSetup;
831 AVAILABILITY_DURATION NUMBER;
832
833 l_new_StartDateTab PA_FORECAST_GLOB.DateTabTyp;
834 l_new_EndDateTab PA_FORECAST_GLOB.DateTabTyp;
835 l_new_ResourceOUTab PA_FORECAST_GLOB.NumberTabTyp;
836 li_new_index NUMBER;
837 ld_prev_end_date DATE;
838 l_msg_index_out NUMBER;
839
840 BEGIN
841 PA_FORECASTITEM_PVT.print_message('Inside Get_Resource_OU');
842 g_TimelineProfileSetup := PA_TIMELINE_UTIL.get_timeline_profile_setup;
843 availability_duration := g_TimelineProfileSetup.availability_duration;
844
845 ld_start_date := NVL(p_start_date, ADD_MONTHS(sysdate, -12));
846 ld_end_date := NVL(p_end_date, ADD_MONTHS(sysdate, availability_duration * (12)));
847
848 PA_FORECASTITEM_PVT.print_message('p_resource_id: ' || p_resource_id);
849 PA_FORECASTITEM_PVT.print_message('p_start_date: ' || p_start_date);
850 PA_FORECASTITEM_PVT.print_message('p_end_date: ' || p_end_date);
851 PA_FORECASTITEM_PVT.print_message('ld_start_date: ' || ld_start_date);
852 PA_FORECASTITEM_PVT.print_message('ld_end_date: ' || ld_end_date);
853
854 -- 2196924: Added logic so it wouldn't raise NO_DATA_FOUND
855 BEGIN
856 --Bug 4207110 :Added equalto condition while performing date check
857
858 SELECT nvl(rou.resource_org_id,-99),
859 rou.resource_effective_start_date,
860 NVL(rou.resource_effective_end_date,SYSDATE)
861 BULK COLLECT INTO
862 x_ResourceOUTab,x_StartDateTab,x_EndDateTab
863 FROM pa_resources_denorm rou
864 WHERE rou.resource_id= p_resource_id
865 AND ld_start_date <= NVL(rou.resource_effective_end_date,SYSDATE)
866 AND ld_end_date >= rou.resource_effective_start_date
867 ORDER BY rou.resource_effective_start_date;
868
869 --Bug 4207110 END
870
871 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND ok, exception not raised');
872 EXCEPTION
873 WHEN NO_DATA_FOUND THEN
874 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND ok, exception not raised');
875 END;
876
877
878 -- 2196924: Added logic so that all dates have a record in out table.
879
880 li_count := NVL(x_ResourceOUTab.count,0);
881 if (li_count = 0) then
882 x_ResourceOUTab(1) := -88;
883 x_StartDateTab(1) := ld_start_date;
884 x_EndDateTab(1) := ld_end_date;
885 li_count := 1;
886 end if;
887
888 li_first_index := x_ResourceOUTab.first;
889 li_new_first_index := li_first_index - 1;
890 li_last_index := x_ResourceOUTab.last;
891 li_new_last_index := li_last_index + 1;
892 ld_first_start_date := x_StartDateTab(li_first_index);
893 li_first_ou := x_ResourceOUTab(li_first_index);
894 ld_last_end_date := x_EndDateTab(li_last_index);
895 li_last_ou := x_ResourceOUTab(li_last_index);
896
897 PA_FORECASTITEM_PVT.print_message('li_first_index: ' || li_first_index);
898 PA_FORECASTITEM_PVT.print_message('li_new_first_index: ' || li_new_first_index);
899 PA_FORECASTITEM_PVT.print_message('li_last_index: ' || li_last_index);
900 PA_FORECASTITEM_PVT.print_message('li_new_last_index: ' || li_new_last_index);
901 PA_FORECASTITEM_PVT.print_message('ld_first_start_date: ' || ld_first_start_date);
902 PA_FORECASTITEM_PVT.print_message('ld_last_end_date: ' || ld_last_end_date);
903 PA_FORECASTITEM_PVT.print_message('li_first_ou: ' || li_first_ou);
904 PA_FORECASTITEM_PVT.print_message('li_last_ou: ' || li_last_ou);
905
906 if (ld_first_start_date > ld_start_date) then
907 -- Insert a record into table
908 PA_FORECASTITEM_PVT.print_message('ld_first_start_date > ld_start_date');
909 x_ResourceOUTab(li_new_first_index) := -88;
910 x_StartDateTab(li_new_first_index) := ld_start_date;
911 x_EndDateTab(li_new_first_index) := ld_first_start_date - 1;
912 end if;
913
914 if (ld_last_end_date < ld_end_date) then
915 -- Insert a record into table
916 PA_FORECASTITEM_PVT.print_message('ld_last_end_date < ld_end_date');
917 x_ResourceOUTab(li_new_last_index) := -88;
918 x_StartDateTab(li_new_last_index) := ld_last_end_date + 1;
919 x_EndDateTab(li_new_last_index) := ld_end_date;
920 end if;
921
922 -- Fix holes (x_StartDateTab is definitely not empty here,
923 -- so no need to check)
924 li_new_index := 1;
925 ld_prev_end_date := ld_start_date-1;
926 for i IN x_StartDateTab.first .. x_StartDateTab.last LOOP
927 if (x_StartDateTab(i) > ld_prev_end_date+1) then
928 -- Insert record for hole.
929 l_new_StartDateTab(li_new_index) := ld_prev_end_date + 1;
930 l_new_EndDateTab(li_new_index) := x_StartDateTab(i)-1;
931 l_new_ResourceOUTab(li_new_index) := -88;
932 li_new_index := li_new_index + 1;
933 end if;
934 l_new_StartDateTab(li_new_index) := x_StartDateTab(i);
935 l_new_EndDateTab(li_new_index) := x_EndDateTab(i);
936 l_new_ResourceOUTab(li_new_index) := x_ResourceOUTab(i);
937
938 li_new_index := li_new_index + 1;
939 ld_prev_end_date := x_EndDateTab(i);
940 end loop;
941
942 x_ResourceOUTab := l_new_ResourceOUTab;
943 x_StartDateTab := l_new_StartDateTab;
944 x_EndDateTab := l_new_EndDateTab;
945 -- 4537865 : EXCEPTION BLOCK INCLUDED
946 EXCEPTION
947 WHEN NO_DATA_FOUND THEN
948 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND ok, exception not raised');
949 NULL ;
950 WHEN OTHERS THEN
951 x_ResourceOUTab.delete;
952 x_StartDateTab.delete;
953 x_EndDateTab.delete;
954 FND_MSG_PUB.add_exc_msg
955 ( p_pkg_name => 'PA_FORECAST_ITEMS_UTILS'
956 , p_procedure_name => 'Get_Resource_OU'
957 , p_error_text => SUBSTRB(SQLERRM,1,240));
958 RAISE ;
959 END Get_Resource_OU;
960
961
962
963 ----------------------------------------------------------------------------------------------------------
964 -- Procedure Get_Res_Org_And_Job
965 -- Purpose To get the Resource Organization for Period
966 -- Input parameters
967 -- Parameters Type Required Description
968 -- p_person_id NUMBER YES Persion id
969 -- p_start_date DATE YES Start date
970 -- p_end_date DATE YES End date
971 -- Out parameters
972 -- x_StartDateTab DateTabTyp YES Used to store start date in bulk
973 -- x_EndDateTab DateTabTyp YES Used to store end date in bulk
974 -- x_ResourceOrganizationIDTab NumberTabTyp YES Used to store organization id
975 -- x_ResourceJobIDTab NumberTabTyp YES Used to store resource job id
976 ---------------------------------------------------------------------------------------------------------------
977 PROCEDURE Get_Res_Org_And_Job(p_person_id IN NUMBER,
978 p_start_date IN DATE,
979 p_end_date IN DATE,
980 x_StartDateTab OUT NOCOPY PA_FORECAST_GLOB.DateTabTyp, /* 2674619 - Nocopy change */
981 x_EndDateTab OUT NOCOPY PA_FORECAST_GLOB.DateTabTyp,/* 2674619 - Nocopy change */
982 x_ResourceOrganizationIDTab OUT NOCOPY PA_FORECAST_GLOB.NumberTabTyp, /* 2674619 - Nocopy change */
983 x_ResourceJobIDTab OUT NOCOPY PA_FORECAST_GLOB.NumberTabTyp) /* 2674619 - Nocopy change */
984 IS
985
986 l_new_ResOrganizationIDTab PA_FORECAST_GLOB.NumberTabTyp;
987 l_new_StartDateTab PA_FORECAST_GLOB.DateTabTyp;
988 l_new_EndDateTab PA_FORECAST_GLOB.DateTabTyp;
989 l_new_ResourceJobIDTab PA_FORECAST_GLOB.NumberTabTyp;
990 li_new_index NUMBER;
991 ld_prev_end_date DATE;
992
993 ld_start_date DATE;
994 ld_end_date DATE;
995 li_count NUMBER;
996 li_first_index NUMBER;
997 li_last_index NUMBER;
998 li_new_first_index NUMBER;
999 li_new_last_index NUMBER;
1000 ld_first_start_date DATE;
1001 ld_last_end_date DATE;
1002
1003 g_TimelineProfileSetup PA_TIMELINE_GLOB.TimelineProfileSetup;
1004 AVAILABILITY_DURATION NUMBER;
1005 l_msg_index_out NUMBER;
1006
1007 BEGIN
1008
1009 PA_FORECASTITEM_PVT.print_message('Get_Res_Org_And_Job');
1010
1011 g_TimelineProfileSetup := PA_TIMELINE_UTIL.get_timeline_profile_setup;
1012 availability_duration := g_TimelineProfileSetup.availability_duration;
1013
1014 ld_start_date := NVL(p_start_date, ADD_MONTHS(sysdate, -12));
1015 ld_end_date := NVL(p_end_date, ADD_MONTHS(sysdate, availability_duration * (12)));
1016
1017 PA_FORECASTITEM_PVT.print_message('p_person_id: ' || p_person_id);
1018 PA_FORECASTITEM_PVT.print_message('p_start_date: ' || p_start_date);
1019 PA_FORECASTITEM_PVT.print_message('p_end_date: ' || p_end_date);
1020 PA_FORECASTITEM_PVT.print_message('ld_start_date: ' || ld_start_date);
1021 PA_FORECASTITEM_PVT.print_message('ld_end_date: ' || ld_end_date);
1022
1023 --- | 18-sep-01 jmarques 2001160: modified per_people_x select
1024 --- | statement to select from per_people_f
1025 --- | also added new date criteria since
1026 --- | per_people_f could contain multiple records
1027 --- | per resource.
1028
1029 --- Modified select statements to select directly off of pa_resources_denorm
1030 --- This is for better performance and it is safer.
1031
1032 BEGIN
1033 --Bug 4207110 :Added equalto condition while performing date check
1034
1035 select nvl(RESOURCE_ORGANIZATION_ID, -99) resource_Organization_id,
1036 RESOURCE_EFFECTIVE_START_DATE effective_start_date,
1037 RESOURCE_EFFECTIVE_END_DATE effective_end_date,
1038 job_id job_id
1039 BULK COLLECT INTO
1040 x_ResourceOrganizationIDTab,
1041 x_StartDateTab,
1042 x_EndDateTab,
1043 x_ResourceJobIDTab
1044 from pa_resources_denorm rou
1045 where person_id = p_person_id
1046 AND ld_start_date <= NVL(rou.resource_effective_end_date,SYSDATE)
1047 AND ld_end_date >= rou.resource_effective_start_date
1048 ORDER BY rou.resource_effective_start_date;
1049
1050 --Bug 4207110 END
1051
1052
1053 EXCEPTION
1054 WHEN NO_DATA_FOUND THEN
1055 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND ok, exception not raised');
1056 END;
1057
1058 PA_FORECASTITEM_PVT.print_message('JM: 1');
1059
1060 -- 2196924: Added logic so that all dates have a record in out table.
1061 li_count := NVL(x_StartDateTab.count,0);
1062 if (li_count = 0) then
1063 PA_FORECASTITEM_PVT.print_message('JM: 2');
1064 x_StartDateTab(1) := ld_start_date;
1065 x_EndDateTab(1) := ld_end_date;
1066 x_ResourceOrganizationIDTab(1) := -77;
1067 x_ResourceJobIDTab(1) := null;
1068 li_count := 1;
1069 end if;
1070
1071 PA_FORECASTITEM_PVT.print_message('JM: 3');
1072 li_first_index := x_StartDateTab.first;
1073 li_new_first_index := li_first_index - 1;
1074 li_last_index := x_StartDateTab.last;
1075 li_new_last_index := li_last_index + 1;
1076 ld_first_start_date := x_StartDateTab(li_first_index);
1077 ld_last_end_date := x_EndDateTab(li_last_index);
1078
1079 PA_FORECASTITEM_PVT.print_message('JM: 4');
1080 PA_FORECASTITEM_PVT.print_message('li_first_index: ' || li_first_index);
1081 PA_FORECASTITEM_PVT.print_message('li_new_first_index: ' || li_new_first_index);
1082 PA_FORECASTITEM_PVT.print_message('li_last_index: ' || li_last_index);
1083 PA_FORECASTITEM_PVT.print_message('li_new_last_index: ' || li_new_last_index);
1084 PA_FORECASTITEM_PVT.print_message('ld_first_start_date: ' || ld_first_start_date);
1085 PA_FORECASTITEM_PVT.print_message('ld_last_end_date: ' || ld_last_end_date);
1086
1087 if (ld_first_start_date > ld_start_date) then
1088 -- Insert a record into table
1089 PA_FORECASTITEM_PVT.print_message('ld_first_start_date > ld_start_date');
1090 x_ResourceOrganizationIDTab(li_new_first_index) := -77;
1091 x_ResourceJobIDTab(li_new_first_index) := null;
1092 x_StartDateTab(li_new_first_index) := ld_start_date;
1093 x_EndDateTab(li_new_first_index) := ld_first_start_date - 1;
1094 end if;
1095
1096 if (ld_last_end_date < ld_end_date) then
1097 -- Insert a record into table
1098 PA_FORECASTITEM_PVT.print_message('ld_last_end_date < ld_end_date');
1099 x_ResourceOrganizationIDTab(li_new_last_index) := -77;
1100 x_ResourceJobIDTab(li_new_last_index) := null;
1101 x_StartDateTab(li_new_last_index) := ld_last_end_date + 1;
1102 x_EndDateTab(li_new_last_index) := ld_end_date;
1103 end if;
1104
1105 -- Fix holes (x_StartDateTab is definitely not empty here,
1106 -- so no need to check)
1107 li_new_index := 1;
1108 ld_prev_end_date := ld_start_date-1;
1109 for i IN x_StartDateTab.first .. x_StartDateTab.last LOOP
1110 if (x_StartDateTab(i) > ld_prev_end_date+1) then
1111 -- Insert record for hole.
1112 l_new_StartDateTab(li_new_index) := ld_prev_end_date + 1;
1113 l_new_EndDateTab(li_new_index) := x_StartDateTab(i)-1;
1114 l_new_ResOrganizationIDTab(li_new_index) := -77;
1115 l_new_ResourceJobIDTab(li_new_index) := null;
1116 li_new_index := li_new_index + 1;
1117 end if;
1118 l_new_StartDateTab(li_new_index) := x_StartDateTab(i);
1119 l_new_EndDateTab(li_new_index) := x_EndDateTab(i);
1120 l_new_ResOrganizationIDTab(li_new_index)
1121 := x_ResourceOrganizationIDTab(i);
1122 l_new_ResourceJobIDTab(li_new_index) := x_ResourceJobIDTab(i);
1123 li_new_index := li_new_index + 1;
1124 ld_prev_end_date := x_EndDateTab(i);
1125 end loop;
1126
1127 x_ResourceOrganizationIDTab := l_new_ResOrganizationIDTab;
1128 x_ResourceJobIDTab := l_new_ResourceJobIDTab;
1129 x_StartDateTab := l_new_StartDateTab;
1130 x_EndDateTab := l_new_EndDateTab;
1131 -- 4537865 : EXCEPTION BLOCK INCLUDED
1132 EXCEPTION
1133 WHEN NO_DATA_FOUND THEN
1134 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND ok, exception not raised');
1135 NULL ;
1136 WHEN OTHERS THEN
1137 x_ResourceOrganizationIDTab.delete;
1138 x_ResourceJobIDTab.delete;
1139 x_StartDateTab.delete;
1140 x_EndDateTab.delete;
1141 FND_MSG_PUB.add_exc_msg
1142 ( p_pkg_name => 'PA_FORECAST_ITEMS_UTILS'
1143 , p_procedure_name => 'Get_Res_Org_And_Job'
1144 , p_error_text => SUBSTRB(SQLERRM,1,240));
1145 RAISE ;
1146 END Get_Res_Org_And_Job;
1147
1148
1149 -----------------------------------------------------------------------------------
1150 -- Function Get_Person_Id
1151 -- Purpose To get the Person ID for resource Id
1152 -- Parameters Resource Id NUMBER YES Resource Id
1153 ------------------------------------------------------------------------------------
1154 FUNCTION Get_Person_Id(p_resource_id NUMBER) RETURN NUMBER IS
1155 li_person_id NUMBER;
1156 BEGIN
1157 PA_FORECASTITEM_PVT.print_message('p_resource_id: ' || p_resource_id);
1158 SELECT person_id
1159 INTO li_person_id
1160 FROM pa_resource_txn_attributes
1161 WHERE resource_id = p_resource_id
1162 AND rownum = 1; --Bug 3086960. Adde by Sachin.
1163 RETURN (li_person_id);
1164 END Get_Person_id;
1165
1166
1167 -----------------------------------------------------------------------------------
1168 -- Function Get_Resource_Id
1169 -- Purpose To get the resource Id for person id
1170 -- Parameters Person Id NUMBER YES Person Id
1171 ------------------------------------------------------------------------------------
1172 FUNCTION Get_resource_Id(p_person_id NUMBER) RETURN NUMBER IS
1173 li_resource_id NUMBER;
1174 BEGIN
1175 SELECT resource_id
1176 INTO li_resource_id
1177 FROM pa_resource_txn_attributes
1178 WHERE person_id = p_person_id;
1179 RETURN (li_resource_id);
1180 END Get_resource_id;
1181 ------------------------------------------------------------------------------------------
1182 -- Function Get_Resource_Type
1183 -- Purpose To get the Resource Type for resource Id
1184 -- Parameters Resource Id NUMBER YES Resource id for its type
1185 ------------------------------------------------------------------------------------------
1186 FUNCTION Get_Resource_Type(p_resource_id NUMBER) RETURN VARCHAR2 IS
1187 lv_resource_type VARCHAR2(30);
1188 BEGIN
1189
1190 SELECT typ.RESOURCE_TYPE_CODE
1191 INTO lv_resource_type
1192 FROM pa_resource_types typ,
1193 pa_resources res
1194 WHERE res.resource_type_id= typ.resource_type_id
1195 AND res.resource_id= p_resource_id;
1196
1197 RETURN (lv_resource_type);
1198 END Get_Resource_Type;
1199
1200
1201
1202 ----------------------------------------------------------------------------------------------------------------------
1203 -- Procedure Get_ForecastOptions
1204 -- Purpose To get the all forecast options from pa_forecasting_options_all table
1205 -- Input parameters
1206 -- Parameters Type Required Description
1207 -- p_org_id NUMBER YES Org id for which all the necessary information is needed
1208 -- Out parameters
1209 -- x_include_admin_proj_flag VARCHAR2 YES Used to store admin project flag
1210 -- x_util_cal_method VARCHAR2 YES Used to store util cal method
1211 -- x_bill_unassign_proj_id NUMBER YES Used to store bill unassigned project id
1212 -- x_bill_unassign_exp_type_class VARCHAR2 YES Used to store bill unassigned expenditure type class
1213 -- x_bill_unassign_exp_type VARCHAR2 YES Used to store bill unassigned expenditure type
1214 -- x_nobill_unassign_proj_id NUMBER YES Used to store without bill unassigned project id
1215 -- x_nobill_unassign_exp_type_class VARCHAR2 YES Used to store without bill unassigned expenditure
1216 -- type class
1217 -- x_nobill_unassign_exp_type VARCHAR2 YES Used to store without bill unassigned expenditure type
1218 -- x_default_tp_amount_type VARCHAR2 YES Used to store default tp amount type
1219 -----------------------------------------------------------------------------------------------------------------------
1220 PROCEDURE Get_ForecastOptions( p_org_id IN NUMBER,
1221 -- x_include_admin_proj_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895, 4576715
1222 x_util_cal_method OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1223 x_bill_unassign_proj_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1224 x_bill_unassign_exp_type_class OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1225 x_bill_unassign_exp_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1226 x_nonbill_unassign_proj_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1227 x_nonbill_unassign_exp_typ_cls OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1228 x_nonbill_unassign_exp_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1229 x_default_tp_amount_type OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1230 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1231 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1232 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1233 IS
1234 l_msg_index_out NUMBER;
1235
1236 BEGIN
1237
1238 -- 4537865 : Initialize x_return_status to SUCCESS
1239 x_return_status := FND_API.G_RET_STS_SUCCESS;
1240
1241 PA_DEBUG.Init_err_stack(
1242 'PA_FORECAST_ITEMS_UTILS.Get_forecastoptions');
1243 -- Selecting columns corresponding to the given org id
1244
1245 PA_FORECASTITEM_PVT.print_message('p_org_id: ' || p_org_id );
1246
1247 -- 2196924: Adding case when p_org_id is null
1248 -- This may occur when there's no HR assignment for
1249 -- part of the resources time, so no ou.
1250 if (p_org_id = -88) then
1251 -- x_include_admin_proj_flag := null; Bug 4576715
1252 x_util_cal_method := null;
1253 x_bill_unassign_proj_id := -66;
1254 x_bill_unassign_exp_type_class := '-99';
1255 x_bill_unassign_exp_type := '-99';
1256 x_nonbill_unassign_proj_id := -66;
1257 x_nonbill_unassign_exp_typ_cls := '-99';
1258 x_nonbill_unassign_exp_type := '-99';
1259 x_default_tp_amount_type := '-99';
1260 else
1261
1262 --R12: MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1263 BEGIN
1264 SELECT -- include_admin_proj_flag, Bug 4576715
1265 bill_unassign_proj_id,
1266 bill_unassign_exp_type_class,bill_unassign_exp_type,
1267 nonbill_unassign_proj_id,nonbill_unassign_exp_typ_cls,
1268 nonbill_unassign_exp_type,default_tp_amount_type,
1269 util_calc_method
1270 INTO -- x_include_admin_proj_flag, Bug 4576715
1271 x_bill_unassign_proj_id,
1272 x_bill_unassign_exp_type_class,x_bill_unassign_exp_type,
1273 x_nonbill_unassign_proj_id,x_nonbill_unassign_exp_typ_cls,
1274 x_nonbill_unassign_exp_type,x_default_tp_amount_type,
1275 x_util_cal_method
1276 FROM pa_forecasting_options_all
1277 WHERE org_id = p_org_id;
1278
1279 /* Bug 2458198 -- Begin */
1280 IF (
1281 -- x_include_admin_proj_flag = 'N' OR Bug 4576715
1282 x_nonbill_unassign_proj_id is null OR x_bill_unassign_proj_id is null) THEN
1283 PA_UTILS.Add_Message(
1284 p_app_short_name => 'PA'
1285 ,p_msg_name =>'PA_UNASSIGNED_PROJ_NO_DEFN');
1286 x_return_status := FND_API.G_RET_STS_ERROR;
1287 x_msg_data := 'PA_UNASSIGNED_PROJ_NO_DEFN';
1288 x_msg_count := FND_MSG_PUB.Count_Msg;
1289
1290 END IF;
1291 /* Bug 2458198 -- End */
1292 exception
1293 WHEN NO_DATA_FOUND THEN
1294 PA_UTILS.Add_Message(
1295 p_app_short_name => 'PA'
1296 ,p_msg_name =>'PA_FORECAST_OPTIONS_NOT_SETUP');
1297 x_return_status := FND_API.G_RET_STS_ERROR;
1298 x_msg_data := 'PA_FORECAST_OPTIONS_NOT_SETUP';
1299 x_msg_count := FND_MSG_PUB.Count_Msg;
1300 end;
1301 end if;
1302 PA_DEBUG.Reset_Err_Stack;
1303
1304 EXCEPTION
1305 WHEN NO_DATA_FOUND THEN
1306
1307 x_msg_count := 1;
1308 x_msg_data := sqlerrm;
1309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1310
1311 FND_MSG_PUB.add_exc_msg
1312 (p_pkg_name => 'PA_FORECAST_ITEMS_UTILS.Get_forecastoptions',
1313 p_procedure_name => PA_DEBUG.G_Err_Stack);
1314
1315 RAISE;
1316
1317 WHEN OTHERS THEN
1318 x_msg_count := 1;
1319 x_msg_data := sqlerrm;
1320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1321
1322 FND_MSG_PUB.add_exc_msg
1323 (p_pkg_name => 'PA_FORECAST_ITEMS_UTILS.Get_forecastoptions',
1324 p_procedure_name => PA_DEBUG.G_Err_Stack);
1325 RAISE;
1326
1327
1328 END Get_ForecastOptions;
1329
1330
1331 ----------------------------------------------------------------------------------------------------------------------
1332 -- Procedure Get_Week_Dates_Range_Fc
1333 -- Purpose To get the global week end date
1334 -- Input parameters
1335 -- Parameters Type Required Description
1336 -- P_Start_Date DATE YES Start date for the week date range
1337 -- P_End_Date DATE YES End date for the week date range
1338 -- Out parameters
1339 -- X_Week_Date_Range_Tab WEEKDATESRANGEFCTABTYP YES Used to store week start and end date
1340 -----------------------------------------------------------------------------------------------------------------------
1341 PROCEDURE Get_Week_Dates_Range_Fc( p_start_date IN DATE,
1342 p_end_date IN DATE,
1343 x_week_date_range_tab OUT NOCOPY PA_FORECAST_GLOB.WeekDatesRangeFcTabTyp , /* 2674619 - Nocopy change */
1344 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1345 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1346 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1347
1348 IS
1349 l_week_ending_date DATE;
1350 l_week_starting_date DATE;
1351 l_week_ending_day VARCHAR2(120);
1352 l_week_starting_day_index NUMBER := 2;
1353 li_cnt INTEGER :=1;
1354 l_msg_index_out NUMBER;
1355 l_end_date DATE; /* Added for bug#2462076 */
1356 l_week_starting_day VARCHAR2(120); /*Bug 5549814 */
1357
1358 BEGIN
1359
1360
1361 PA_DEBUG.Init_err_stack(
1362 'PA_FORECAST_ITEMS_UTILS.Get_Week_Dates_Range_Fc');
1363 -- Taking value of the day from the profile option
1364
1365 l_week_starting_day_index := TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'));
1366
1367 /*Commenting below for Bug 7012687 : not use Select from dual to evaluate expression*/
1368 /* Added for Bug 5549814*/
1369 /* Utilizing the fact that 01-01-1950 was a Sunday and PA lookups value for a Sunday is 1 */
1370 --Select (trim(to_char((to_date('01-01-1950','dd-mm-yyyy')+(l_week_starting_day_index - 1)),'DAY')))
1371 --into l_week_starting_day
1372 --from dual;
1373
1374 /*Adding below for Bug 7012687*/
1375 l_week_starting_day := (trim(to_char((to_date('01-01-1950','dd-mm-yyyy')+(l_week_starting_day_index - 1)),'DAY')));
1376
1377
1378 /* Bug#2462076 Added code for using trunc on the start and end date parameters */
1379 l_week_starting_date := trunc(p_start_date);
1380 l_end_date := trunc(p_end_date);
1381 LOOP
1382 /*Commenting below for Bug 7012687 : not use Select from dual to evaluate expression*/
1383 --SELECT (NEXT_DAY(l_week_starting_date,l_week_starting_day)-1) /*Bug 5549814 - Changed l_week_starting_day_index to l_week_starting_day*/
1384 --INTO l_week_ending_date
1385 --FROM dual;
1386
1387 /*Adding below for Bug 7012687*/
1388 l_week_ending_date := (NEXT_DAY(l_week_starting_date,l_week_starting_day)-1);
1389
1390 l_week_starting_date := l_week_ending_date -6;
1391 x_week_date_range_tab(li_cnt).week_start_date := l_week_starting_date;
1392 x_week_date_range_tab(li_cnt).week_end_date := l_week_ending_date;
1393 l_week_starting_date := l_week_ending_date +1;
1394 EXIT WHEN l_week_starting_date > l_end_date; /* Bug#2462076 Changed p_end_date to l_end_date */
1395 li_cnt := li_cnt +1;
1396
1397 END LOOP;
1398 x_return_status := FND_API.G_RET_STS_SUCCESS;
1399 PA_DEBUG.Reset_Err_Stack;
1400
1401 EXCEPTION
1402 WHEN OTHERS THEN
1403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1404 x_msg_count := 1;
1405 x_msg_data := SQLERRM;
1406 FND_MSG_PUB.add_exc_msg
1407 (p_pkg_name => 'PA_FORECAST_ITEMS_UTILS.Get_Week_Dates_Range_Fc',
1408 p_procedure_name => PA_DEBUG.G_Err_Stack);
1409 RAISE;
1410 END Get_Week_Dates_Range_Fc;
1411
1412
1413
1414 ----------------------------------------------------------------------------------------------------------------------
1415 -- Procedure Check_TPAmountType
1416 -- Purpose To validate the tp amount type and code or description
1417 -- Input parameters
1418 -- Parameters Type Required Description
1419 -- p_tp_amount_type_code VARCHAR2 YES Tp amount type code
1420 -- p_tp_amount_type_desc VARCHAR2 YES Tp amount type desc
1421 -- p_check_id_flag VARCHAR2 YES Check id flage
1422 -- Out parameters
1423 -- x_tp_amount_type_code VARCHAR2 YES Tp amount type code
1424 -- x_tp_amount_type_desc VARCHAR2 YES Tp amount type desc
1425 -----------------------------------------------------------------------------------------------------------------------
1426 PROCEDURE Check_TPAmountType(
1427 p_tp_amount_type_code IN VARCHAR2,
1428 p_tp_amount_type_desc IN VARCHAR2,
1429 p_check_id_flag IN VARCHAR2,
1430 x_tp_amount_type_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1431 x_tp_amount_type_desc OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1432 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1433 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1434 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1435
1436 lv_error_msg VARCHAR2(30);
1437
1438 BEGIN
1439
1440 PA_DEBUG.Init_err_stack(
1441 'PA_FORECAST_ITEMS_UTILS.Check_TPAmountType');
1442 IF p_tp_amount_type_code IS NOT NULL AND
1443 p_tp_amount_type_code<>FND_API.G_MISS_CHAR THEN
1444
1445 IF p_check_id_flag = 'Y' THEN
1446
1447 lv_error_msg := 'PA_AMOUNT_TYPE_CODE_AMBIGUOUS';
1448
1449 SELECT lookup_code, meaning
1450 INTO x_tp_amount_type_code,
1451 x_tp_amount_type_desc
1452 FROM pa_lookups
1453 WHERE lookup_type = 'TP_AMOUNT_TYPE'
1454 AND lookup_code = p_tp_amount_type_code;
1455
1456 ELSE
1457 x_tp_amount_type_code := p_tp_amount_type_code;
1458
1459 END IF;
1460
1461 ELSE
1462
1463 lv_error_msg := 'PA_AMOUNT_TYPE_DESC_AMBIGUOUS';
1464
1465 SELECT lookup_code
1466 INTO x_tp_amount_type_code
1467 FROM pa_lookups
1468 WHERE lookup_type = 'TP_AMOUNT_TYPE'
1469 AND meaning = p_tp_amount_type_desc;
1470
1471
1472 END IF;
1473
1474 PA_DEBUG.Reset_Err_Stack;
1475
1476 x_return_status := FND_API.G_RET_STS_SUCCESS;
1477 EXCEPTION
1478 WHEN NO_DATA_FOUND THEN
1479 x_return_status := FND_API.G_RET_STS_ERROR;
1480 x_msg_data := lv_error_msg;
1481
1482 -- 4537865 : Start
1483 x_tp_amount_type_code := NULL ;
1484 x_tp_amount_type_desc := NULL ;
1485 --4537865 : End
1486
1487 FND_MSG_PUB.add_exc_msg
1488 (p_pkg_name =>
1489 'PA_FORECAST_ITEMS_UTILS.Check_TPAmountType',
1490 p_procedure_name => PA_DEBUG.G_Err_Stack);
1491 WHEN TOO_MANY_ROWS THEN
1492 x_return_status := FND_API.G_RET_STS_ERROR;
1493 x_msg_data := lv_error_msg;
1494
1495 -- 4537865 : Start
1496 x_tp_amount_type_code := NULL ;
1497 x_tp_amount_type_desc := NULL ;
1498 --4537865 : End
1499
1500 FND_MSG_PUB.add_exc_msg
1501 (p_pkg_name =>
1502 'PA_FORECAST_ITEMS_UTILS.Check_TPAmountType',
1503 p_procedure_name => PA_DEBUG.G_Err_Stack);
1504 WHEN OTHERS THEN
1505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1506
1507 -- 4537865 : Start
1508 x_tp_amount_type_code := NULL ;
1509 x_tp_amount_type_desc := NULL ;
1510 --4537865 : End
1511
1512 FND_MSG_PUB.add_exc_msg
1513 (p_pkg_name =>
1514 'PA_FORECAST_ITEMS_UTILS.Check_TPAmountType',
1515 p_procedure_name => PA_DEBUG.G_Err_Stack);
1516 --PA_Error_Utils.Set_Error_Stack
1517 -- (`pa_resource_utils.check_resourcename_or_id');
1518 -- This sets the current program unit name in the
1519 -- error stack. Helpful in Debugging
1520 raise;
1521
1522 END Check_TPAmountType;
1523
1524
1525
1526 ----------------------------------------------------------------------------------------------------------
1527 -- Description This procedure will get the defautl values for the Assignment
1528 --
1529 -- Procedure Name Get_Assignment_Default
1530 -- Used Subprograms None
1531 -- Input parameters Type Required Description
1532 -- p_assignment_type VARCHAR2 Yes The assignment type, can be either
1533 -- 'Open assignment'or 'Staffed assignment'.
1534 -- p_project_id NUMBER Yes Project ID
1535 -- p_project_role_id NUMBER Yes Project role ID
1536
1537 -- Output parameters Type Description
1538 -- x_work_type_id NUMBER Default Work Type ID
1539 -- x_default_tp_amount_type VARCHAR2 Default transfer price amount type
1540 -- x_default_job_group_id NUMBER Default job group ID
1541 -- x_default_job_id NUMBER Default jog ID
1542 -- x_org_id NUMBER Default Expenditure OU ID
1543 -- x_carrying_out_organization_id NUMBER Default Expenditure Org ID
1544 -- x_default_assign_exp_type VARCHAR2 Default Expenditure Type
1545 -- x_default_assign_exp_type_cls VARCHAR2 Default Expenditure Type Class
1546 -- x_return_status VARCHAR2 The return status of this procedure
1547 -------------------------------------------------------------------------------------------------------------
1548 PROCEDURE Get_Assignment_Default (p_assignment_type IN VARCHAR2,
1549 p_project_id IN NUMBER,
1550 p_project_role_id IN NUMBER,
1551 p_work_type_id IN NUMBER,
1552 x_work_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1553 x_default_tp_amount_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1554 x_default_job_group_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1555 x_default_job_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1556 x_org_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1557 x_carrying_out_organization_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1558 x_default_assign_exp_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1559 x_default_assign_exp_type_cls OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1560 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1561 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1562 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1563 )
1564 IS
1565 BEGIN
1566 x_return_status := FND_API.G_RET_STS_SUCCESS;
1567
1568 Get_Project_Default( p_assignment_type => p_assignment_type,
1569 p_project_id => p_project_id,
1570 x_work_type_id => x_work_type_id,
1571 x_default_tp_amount_type => x_default_tp_amount_type,
1572 x_org_id => x_org_id,
1573 x_carrying_out_organization_id => x_carrying_out_organization_id,
1574 x_default_assign_exp_type => x_default_assign_exp_type,
1575 x_default_assign_exp_type_cls => x_default_assign_exp_type_cls,
1576 x_return_status => x_return_status,
1577 x_msg_count => x_msg_count,
1578 x_msg_data => x_msg_data );
1579
1580 Get_Project_Role_Default (p_assignment_type => p_assignment_type,
1581 p_project_role_id => p_project_role_id,
1582 x_default_job_group_id => x_default_job_group_id,
1583 x_default_job_id => x_default_job_id,
1584 x_return_status => x_return_status,
1585 x_msg_count => x_msg_count,
1586 x_msg_data => x_msg_data );
1587
1588 IF FND_MSG_PUB.Count_Msg > 0 THEN
1589 x_return_status := FND_API.G_RET_STS_ERROR;
1590 x_msg_count := FND_MSG_PUB.Count_Msg;
1591 x_msg_data := NULL;
1592 END IF;
1593
1594 EXCEPTION
1595 WHEN OTHERS THEN
1596 -- 4537865 : Start
1597
1598 x_work_type_id := NULL ;
1599 x_default_tp_amount_type := NULL ;
1600 x_default_job_group_id := NULL ;
1601 x_default_job_id := NULL ;
1602 x_org_id := NULL ;
1603 x_carrying_out_organization_id := NULL ;
1604 x_default_assign_exp_type := NULL ;
1605 x_default_assign_exp_type_cls := NULL ;
1606 -- 4537865 : End
1607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1608 x_msg_count := 1;
1609 x_msg_data := SQLERRM;
1610 FND_MSG_PUB.add_exc_msg
1611 (p_pkg_name => 'PA_FORECAST_ITEMS_UTILS.Get_assignment_default',
1612 p_procedure_name => PA_DEBUG.G_Err_Stack);
1613 raise;
1614
1615 END Get_Assignment_Default;
1616
1617 PROCEDURE Get_Project_Default ( p_assignment_type IN VARCHAR2,
1618 p_project_id IN NUMBER,
1619 x_work_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1620 x_default_tp_amount_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1621 x_org_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1622 x_carrying_out_organization_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1623 x_default_assign_exp_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1624 x_default_assign_exp_type_cls OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1625 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1626 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1627 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1628 )
1629 IS
1630
1631 BEGIN
1632 x_return_status := FND_API.G_RET_STS_SUCCESS;
1633
1634 SELECT work_type_id,
1635 org_id,
1636 carrying_out_organization_id
1637 INTO x_work_type_id,
1638 x_org_id,
1639 x_carrying_out_organization_id
1640 FROM pa_projects_all
1641 WHERE project_id = p_project_id;
1642
1643 begin
1644 --R12: MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1645 SELECT default_assign_exp_type,
1646 default_assign_exp_type_class
1647 INTO x_default_assign_exp_type,
1648 x_default_assign_exp_type_cls
1649 FROM pa_forecasting_options_all
1650 WHERE org_id = x_org_id;
1651
1652 exception
1653 WHEN NO_DATA_FOUND THEN
1654 PA_UTILS.Add_Message(
1655 p_app_short_name => 'PA'
1656 ,p_msg_name =>'PA_FORECAST_OPTIONS_NOT_SETUP');
1657 x_return_status := FND_API.G_RET_STS_ERROR;
1658 end;
1659
1660 -- Populate expenditure_organization_id only for requirement
1661 IF p_assignment_type <> 'OPEN_ASSIGNMENT' THEN
1662 x_org_id := NULL;
1663 x_carrying_out_organization_id := NULL;
1664 END IF;
1665
1666 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1667 Pa_Fp_Org_Fcst_Utils.Get_Tp_Amount_Type(
1668 p_project_id => p_project_id,
1669 p_work_type_id => x_work_type_id,
1670 x_tp_amount_type => x_default_tp_amount_type,
1671 x_return_status => x_return_status,
1672 x_msg_count => x_msg_count,
1673 x_msg_data => x_msg_data);
1674 END IF;
1675
1676 EXCEPTION
1677 WHEN OTHERS THEN
1678 -- 4537865 : Start
1679
1680 x_work_type_id := NULL ;
1681 x_default_tp_amount_type := NULL ;
1682 x_org_id := NULL ;
1683 x_carrying_out_organization_id := NULL ;
1684 x_default_assign_exp_type := NULL ;
1685 x_default_assign_exp_type_cls := NULL ;
1686 -- 4537865 : End
1687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1688 x_msg_count := 1;
1689 x_msg_data := SQLERRM;
1690 FND_MSG_PUB.add_exc_msg
1691 (p_pkg_name => 'PA_FORECAST_ITEMS_UTILS.Get_project_default',
1692 p_procedure_name => PA_DEBUG.G_Err_Stack);
1693 raise;
1694
1695 END Get_Project_Default;
1696
1697 PROCEDURE Get_Project_Role_Default (p_assignment_type IN VARCHAR2,
1698 p_project_role_id IN NUMBER,
1699 x_default_job_group_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1700 x_default_job_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1701 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1702 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1703 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1704 )
1705 IS
1706 BEGIN
1707 x_return_status := FND_API.G_RET_STS_SUCCESS;
1708
1709 begin
1710 SELECT b.job_group_id,
1711 a.default_job_id
1712 INTO x_default_job_group_id,
1713 x_default_job_id
1714 FROM (select project_role_id,
1715 pa_role_job_bg_utils.get_job_id(project_role_id) default_job_id
1716 from pa_project_role_types_b
1717 where role_party_class = 'PERSON'
1718 and project_role_id = p_project_role_id) a,
1719 per_jobs b
1720 WHERE b.job_id = a.default_job_id;
1721 exception
1722 WHEN NO_DATA_FOUND THEN
1723 PA_UTILS.Add_Message(
1724 p_app_short_name => 'PA'
1725 ,p_msg_name => 'PA_JOB_NOT_FOUND');
1726 end;
1727
1728 IF p_assignment_type <> 'OPEN_ASSIGNMENT' THEN
1729 x_default_job_id := NULL;
1730 END IF;
1731
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734
1735 -- 4537865 : Start
1736 x_default_job_group_id := NULL ;
1737 x_default_job_id := NULL ;
1738 -- 4537865 : End
1739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1740 x_msg_count := 1;
1741 x_msg_data := SQLERRM;
1742 FND_MSG_PUB.add_exc_msg
1743 (p_pkg_name => 'PA_FORECAST_ITEMS_UTILS.Get_Project_Role_default',
1744 p_procedure_name => PA_DEBUG.G_Err_Stack);
1745 raise;
1746
1747 END Get_Project_Role_Default;
1748
1749 END PA_FORECAST_ITEMS_UTILS;
1750