[Home] [Help]
PACKAGE BODY: APPS.PA_FORECAST_ITEMS_UTILS
Source
1 PACKAGE BODY PA_FORECAST_ITEMS_UTILS AS
2 /* $Header: PARFIUTB.pls 120.11.12020000.2 2012/07/19 09:57:34 admarath 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 -- Bug#13576983 start
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,
729 x_EndDateTab,
730 x_PAPeriodNameTab
731 FROM pa_implementations_all imp,
732 gl_sets_of_books gl,
733 gl_periods glper
734 WHERE imp.org_id = p_org_id
735 AND imp.set_of_books_id = gl.set_of_books_id
736 AND gl.period_set_name = glper.period_set_name
737 AND gl.accounted_period_type = glper.period_type
738 AND (glper.start_date <= TRUNC(p_end_date)
739 AND glper.end_date >= TRUNC(p_start_date))
740 ORDER BY glper.start_date;
741 /*
742 AND imp.set_of_books_id = gl.set_of_books_id
743 AND gl.period_set_name = glper.period_set_name
744 AND gl.accounted_period_type = glper.period_type
745 AND glmaps.period_type = glper.period_type
746 AND glmaps.period_name = glper.period_name
747 --AND glmaps.accounting_date in (p_start_date,p_end_date) -- bug#9325153, commented by bug9558375
748 AND glmaps.period_set_name = glper.period_set_name
749 --Bug 4276273 - trunc added
750 AND ( (trunc(p_start_date) BETWEEN glper.start_date AND
751 glper.end_date)
752 OR (trunc(p_end_date) BETWEEN glper.start_date AND
753 glper.end_date)
754 OR ( trunc(p_start_date) < glper.start_date AND
755 trunc(p_end_date) > glper.end_date )) */
756 -- Bug#13576983 end.
757 END;
758
759 ELSIF p_start_date IS NOT NULL THEN
760
761 BEGIN
762 --R12: MOAC Changes: Bug 4363092: Removed nvl usage with org_id
763 SELECT glper.start_date,
764 glper.end_date,
765 glper.period_name
766 BULK COLLECT
767 INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
768 FROM pa_implementations_all imp,
769 gl_sets_of_books gl,
770 gl_periods glper,
771 gl_date_period_map glmaps
772 WHERE imp.org_id = p_org_id
773 AND imp.set_of_books_id = gl.set_of_books_id
774 AND gl.period_set_name = glper.period_set_name
775 AND gl.accounted_period_type = glper.period_type
776 AND glmaps.period_type = glper.period_type
777 AND glmaps.period_name = glper.period_name
778 AND glmaps.period_set_name = glper.period_set_name
779 --Bug 4276273 - trunc added
780 AND trunc(p_start_date) BETWEEN glper.start_date AND
781 glper.end_date
782 order by glper.start_date;
783
784 END;
785 END IF;
786 END IF;
787 EXCEPTION
788 WHEN NO_DATA_FOUND THEN
789 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND');
790 NULL;
791 WHEN OTHERS THEN -- 4537865 : Included this block
792 x_StartDateTab.delete;
793 x_EndDateTab.delete;
794 x_PAPeriodNameTab.delete ;
795 FND_MSG_PUB.add_exc_msg
796 ( p_pkg_name => 'PA_FORECAST_ITEMS_UTILS'
797 , p_procedure_name => 'Get_GL_Period_Name'
798 , p_error_text => SUBSTRB(SQLERRM,1,240));
799 RAISE ;
800 END Get_GL_Period_Name;
801
802
803
804 --------------------------------------------------------------------------------------------------------
805 -- Procedure Get_Resource_OU
806 -- Purpose To get the Resource OU for a Period
807 -- Input parameters
808 -- Parameters Type Required Description
809 -- p_resource_id NUMBER YES Resource id
810 -- p_start_date DATE YES Start date
811 -- p_end_date DATE YES End date
812 -- Out parameters
813 -- x_StartDateTab DateTabTyp YES Used to store start date in bulk
814 -- x_EndDateTab DateTabTyp YES Used to store end date in bulk
815 -- x_ResourceOUTab NumberTabTyp YES Used to store resource id
816 ----------------------------------------------------------------------------------------------------------
817 PROCEDURE Get_Resource_OU(p_resource_id IN NUMBER,
818 p_start_date IN DATE,
819 p_end_date IN DATE,
820 x_StartDateTab OUT NOCOPY PA_FORECAST_GLOB.DateTabTyp, /* 2674619 - Nocopy change */
821 x_EndDateTab OUT NOCOPY PA_FORECAST_GLOB.DateTabTyp, /* 2674619 - Nocopy change */
822 x_ResourceOUTab OUT NOCOPY PA_FORECAST_GLOB.NumberTabTyp) /* 2674619 - Nocopy change */
823 IS
824
825 ld_start_date DATE;
826 ld_end_date DATE;
827 li_count NUMBER;
828 li_first_index NUMBER;
829 li_last_index NUMBER;
830 li_new_first_index NUMBER;
831 li_new_last_index NUMBER;
832 ld_first_start_date DATE;
833 li_first_ou NUMBER;
834 ld_last_end_date DATE;
835 li_last_ou NUMBER;
836
837 g_TimelineProfileSetup PA_TIMELINE_GLOB.TimelineProfileSetup;
838 AVAILABILITY_DURATION NUMBER;
839
840 l_new_StartDateTab PA_FORECAST_GLOB.DateTabTyp;
841 l_new_EndDateTab PA_FORECAST_GLOB.DateTabTyp;
842 l_new_ResourceOUTab PA_FORECAST_GLOB.NumberTabTyp;
843 li_new_index NUMBER;
844 ld_prev_end_date DATE;
845 l_msg_index_out NUMBER;
846
847 BEGIN
848 PA_FORECASTITEM_PVT.print_message('Inside Get_Resource_OU');
849 g_TimelineProfileSetup := PA_TIMELINE_UTIL.get_timeline_profile_setup;
850 availability_duration := g_TimelineProfileSetup.availability_duration;
851
852 ld_start_date := NVL(p_start_date, ADD_MONTHS(sysdate, -12));
853 ld_end_date := NVL(p_end_date, ADD_MONTHS(sysdate, availability_duration * (12)));
854
855 PA_FORECASTITEM_PVT.print_message('p_resource_id: ' || p_resource_id);
856 PA_FORECASTITEM_PVT.print_message('p_start_date: ' || p_start_date);
857 PA_FORECASTITEM_PVT.print_message('p_end_date: ' || p_end_date);
858 PA_FORECASTITEM_PVT.print_message('ld_start_date: ' || ld_start_date);
859 PA_FORECASTITEM_PVT.print_message('ld_end_date: ' || ld_end_date);
860
861 -- 2196924: Added logic so it wouldn't raise NO_DATA_FOUND
862 BEGIN
863 --Bug 4207110 :Added equalto condition while performing date check
864
865 SELECT nvl(rou.resource_org_id,-99),
866 rou.resource_effective_start_date,
867 NVL(rou.resource_effective_end_date,SYSDATE)
868 BULK COLLECT INTO
869 x_ResourceOUTab,x_StartDateTab,x_EndDateTab
870 FROM pa_resources_denorm rou
871 WHERE rou.resource_id= p_resource_id
872 AND ld_start_date <= NVL(rou.resource_effective_end_date,SYSDATE)
873 AND ld_end_date >= rou.resource_effective_start_date
874 ORDER BY rou.resource_effective_start_date;
875
876 --Bug 4207110 END
877
878 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND ok, exception not raised');
879 EXCEPTION
880 WHEN NO_DATA_FOUND THEN
881 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND ok, exception not raised');
882 END;
883
884
885 -- 2196924: Added logic so that all dates have a record in out table.
886
887 li_count := NVL(x_ResourceOUTab.count,0);
888 if (li_count = 0) then
889 x_ResourceOUTab(1) := -88;
890 x_StartDateTab(1) := ld_start_date;
891 x_EndDateTab(1) := ld_end_date;
892 li_count := 1;
893 end if;
894
895 li_first_index := x_ResourceOUTab.first;
896 li_new_first_index := li_first_index - 1;
897 li_last_index := x_ResourceOUTab.last;
898 li_new_last_index := li_last_index + 1;
899 ld_first_start_date := x_StartDateTab(li_first_index);
900 li_first_ou := x_ResourceOUTab(li_first_index);
901 ld_last_end_date := x_EndDateTab(li_last_index);
902 li_last_ou := x_ResourceOUTab(li_last_index);
903
904 PA_FORECASTITEM_PVT.print_message('li_first_index: ' || li_first_index);
905 PA_FORECASTITEM_PVT.print_message('li_new_first_index: ' || li_new_first_index);
906 PA_FORECASTITEM_PVT.print_message('li_last_index: ' || li_last_index);
907 PA_FORECASTITEM_PVT.print_message('li_new_last_index: ' || li_new_last_index);
908 PA_FORECASTITEM_PVT.print_message('ld_first_start_date: ' || ld_first_start_date);
909 PA_FORECASTITEM_PVT.print_message('ld_last_end_date: ' || ld_last_end_date);
910 PA_FORECASTITEM_PVT.print_message('li_first_ou: ' || li_first_ou);
911 PA_FORECASTITEM_PVT.print_message('li_last_ou: ' || li_last_ou);
912
913 if (ld_first_start_date > ld_start_date) then
914 -- Insert a record into table
915 PA_FORECASTITEM_PVT.print_message('ld_first_start_date > ld_start_date');
916 x_ResourceOUTab(li_new_first_index) := -88;
917 x_StartDateTab(li_new_first_index) := ld_start_date;
918 x_EndDateTab(li_new_first_index) := ld_first_start_date - 1;
919 end if;
920
921 if (ld_last_end_date < ld_end_date) then
922 -- Insert a record into table
923 PA_FORECASTITEM_PVT.print_message('ld_last_end_date < ld_end_date');
924 x_ResourceOUTab(li_new_last_index) := -88;
925 x_StartDateTab(li_new_last_index) := ld_last_end_date + 1;
926 x_EndDateTab(li_new_last_index) := ld_end_date;
927 end if;
928
929 -- Fix holes (x_StartDateTab is definitely not empty here,
930 -- so no need to check)
931 li_new_index := 1;
932 ld_prev_end_date := ld_start_date-1;
933 for i IN x_StartDateTab.first .. x_StartDateTab.last LOOP
934 if (x_StartDateTab(i) > ld_prev_end_date+1) then
935 -- Insert record for hole.
936 l_new_StartDateTab(li_new_index) := ld_prev_end_date + 1;
937 l_new_EndDateTab(li_new_index) := x_StartDateTab(i)-1;
938 l_new_ResourceOUTab(li_new_index) := -88;
939 li_new_index := li_new_index + 1;
940 end if;
941 l_new_StartDateTab(li_new_index) := x_StartDateTab(i);
942 l_new_EndDateTab(li_new_index) := x_EndDateTab(i);
943 l_new_ResourceOUTab(li_new_index) := x_ResourceOUTab(i);
944
945 li_new_index := li_new_index + 1;
946 ld_prev_end_date := x_EndDateTab(i);
947 end loop;
948
949 x_ResourceOUTab := l_new_ResourceOUTab;
950 x_StartDateTab := l_new_StartDateTab;
951 x_EndDateTab := l_new_EndDateTab;
952 -- 4537865 : EXCEPTION BLOCK INCLUDED
953 EXCEPTION
954 WHEN NO_DATA_FOUND THEN
955 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND ok, exception not raised');
956 NULL ;
957 WHEN OTHERS THEN
958 x_ResourceOUTab.delete;
959 x_StartDateTab.delete;
960 x_EndDateTab.delete;
961 FND_MSG_PUB.add_exc_msg
962 ( p_pkg_name => 'PA_FORECAST_ITEMS_UTILS'
963 , p_procedure_name => 'Get_Resource_OU'
964 , p_error_text => SUBSTRB(SQLERRM,1,240));
965 RAISE ;
966 END Get_Resource_OU;
967
968
969
970 ----------------------------------------------------------------------------------------------------------
971 -- Procedure Get_Res_Org_And_Job
972 -- Purpose To get the Resource Organization for Period
973 -- Input parameters
974 -- Parameters Type Required Description
975 -- p_person_id NUMBER YES Persion id
976 -- p_start_date DATE YES Start date
977 -- p_end_date DATE YES End date
978 -- Out parameters
979 -- x_StartDateTab DateTabTyp YES Used to store start date in bulk
980 -- x_EndDateTab DateTabTyp YES Used to store end date in bulk
981 -- x_ResourceOrganizationIDTab NumberTabTyp YES Used to store organization id
982 -- x_ResourceJobIDTab NumberTabTyp YES Used to store resource job id
983 ---------------------------------------------------------------------------------------------------------------
984 PROCEDURE Get_Res_Org_And_Job(p_person_id IN NUMBER,
985 p_start_date IN DATE,
986 p_end_date IN DATE,
987 x_StartDateTab OUT NOCOPY PA_FORECAST_GLOB.DateTabTyp, /* 2674619 - Nocopy change */
988 x_EndDateTab OUT NOCOPY PA_FORECAST_GLOB.DateTabTyp,/* 2674619 - Nocopy change */
989 x_ResourceOrganizationIDTab OUT NOCOPY PA_FORECAST_GLOB.NumberTabTyp, /* 2674619 - Nocopy change */
990 x_ResourceJobIDTab OUT NOCOPY PA_FORECAST_GLOB.NumberTabTyp) /* 2674619 - Nocopy change */
991 IS
992
993 l_new_ResOrganizationIDTab PA_FORECAST_GLOB.NumberTabTyp;
994 l_new_StartDateTab PA_FORECAST_GLOB.DateTabTyp;
995 l_new_EndDateTab PA_FORECAST_GLOB.DateTabTyp;
996 l_new_ResourceJobIDTab PA_FORECAST_GLOB.NumberTabTyp;
997 li_new_index NUMBER;
998 ld_prev_end_date DATE;
999
1000 ld_start_date DATE;
1001 ld_end_date DATE;
1002 li_count NUMBER;
1003 li_first_index NUMBER;
1004 li_last_index NUMBER;
1005 li_new_first_index NUMBER;
1006 li_new_last_index NUMBER;
1007 ld_first_start_date DATE;
1008 ld_last_end_date DATE;
1009
1010 g_TimelineProfileSetup PA_TIMELINE_GLOB.TimelineProfileSetup;
1011 AVAILABILITY_DURATION NUMBER;
1012 l_msg_index_out NUMBER;
1013
1014 BEGIN
1015
1016 PA_FORECASTITEM_PVT.print_message('Get_Res_Org_And_Job');
1017
1018 g_TimelineProfileSetup := PA_TIMELINE_UTIL.get_timeline_profile_setup;
1019 availability_duration := g_TimelineProfileSetup.availability_duration;
1020
1021 ld_start_date := NVL(p_start_date, ADD_MONTHS(sysdate, -12));
1022 ld_end_date := NVL(p_end_date, ADD_MONTHS(sysdate, availability_duration * (12)));
1023
1024 PA_FORECASTITEM_PVT.print_message('p_person_id: ' || p_person_id);
1025 PA_FORECASTITEM_PVT.print_message('p_start_date: ' || p_start_date);
1026 PA_FORECASTITEM_PVT.print_message('p_end_date: ' || p_end_date);
1027 PA_FORECASTITEM_PVT.print_message('ld_start_date: ' || ld_start_date);
1028 PA_FORECASTITEM_PVT.print_message('ld_end_date: ' || ld_end_date);
1029
1030 --- | 18-sep-01 jmarques 2001160: modified per_people_x select
1031 --- | statement to select from per_people_f
1032 --- | also added new date criteria since
1033 --- | per_people_f could contain multiple records
1034 --- | per resource.
1035
1036 --- Modified select statements to select directly off of pa_resources_denorm
1037 --- This is for better performance and it is safer.
1038
1039 BEGIN
1040 --Bug 4207110 :Added equalto condition while performing date check
1041
1042 select nvl(RESOURCE_ORGANIZATION_ID, -99) resource_Organization_id,
1043 RESOURCE_EFFECTIVE_START_DATE effective_start_date,
1044 RESOURCE_EFFECTIVE_END_DATE effective_end_date,
1045 job_id job_id
1046 BULK COLLECT INTO
1047 x_ResourceOrganizationIDTab,
1048 x_StartDateTab,
1049 x_EndDateTab,
1050 x_ResourceJobIDTab
1051 from pa_resources_denorm rou
1052 where person_id = p_person_id
1053 AND ld_start_date <= NVL(rou.resource_effective_end_date,SYSDATE)
1054 AND ld_end_date >= rou.resource_effective_start_date
1055 ORDER BY rou.resource_effective_start_date;
1056
1057 --Bug 4207110 END
1058
1059
1060 EXCEPTION
1061 WHEN NO_DATA_FOUND THEN
1062 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND ok, exception not raised');
1063 END;
1064
1065 PA_FORECASTITEM_PVT.print_message('JM: 1');
1066
1067 -- 2196924: Added logic so that all dates have a record in out table.
1068 li_count := NVL(x_StartDateTab.count,0);
1069 if (li_count = 0) then
1070 PA_FORECASTITEM_PVT.print_message('JM: 2');
1071 x_StartDateTab(1) := ld_start_date;
1072 x_EndDateTab(1) := ld_end_date;
1073 x_ResourceOrganizationIDTab(1) := -77;
1074 x_ResourceJobIDTab(1) := null;
1075 li_count := 1;
1076 end if;
1077
1078 PA_FORECASTITEM_PVT.print_message('JM: 3');
1079 li_first_index := x_StartDateTab.first;
1080 li_new_first_index := li_first_index - 1;
1081 li_last_index := x_StartDateTab.last;
1082 li_new_last_index := li_last_index + 1;
1083 ld_first_start_date := x_StartDateTab(li_first_index);
1084 ld_last_end_date := x_EndDateTab(li_last_index);
1085
1086 PA_FORECASTITEM_PVT.print_message('JM: 4');
1087 PA_FORECASTITEM_PVT.print_message('li_first_index: ' || li_first_index);
1088 PA_FORECASTITEM_PVT.print_message('li_new_first_index: ' || li_new_first_index);
1089 PA_FORECASTITEM_PVT.print_message('li_last_index: ' || li_last_index);
1090 PA_FORECASTITEM_PVT.print_message('li_new_last_index: ' || li_new_last_index);
1091 PA_FORECASTITEM_PVT.print_message('ld_first_start_date: ' || ld_first_start_date);
1092 PA_FORECASTITEM_PVT.print_message('ld_last_end_date: ' || ld_last_end_date);
1093
1094 if (ld_first_start_date > ld_start_date) then
1095 -- Insert a record into table
1096 PA_FORECASTITEM_PVT.print_message('ld_first_start_date > ld_start_date');
1097 x_ResourceOrganizationIDTab(li_new_first_index) := -77;
1098 x_ResourceJobIDTab(li_new_first_index) := null;
1099 x_StartDateTab(li_new_first_index) := ld_start_date;
1100 x_EndDateTab(li_new_first_index) := ld_first_start_date - 1;
1101 end if;
1102
1103 if (ld_last_end_date < ld_end_date) then
1104 -- Insert a record into table
1105 PA_FORECASTITEM_PVT.print_message('ld_last_end_date < ld_end_date');
1106 x_ResourceOrganizationIDTab(li_new_last_index) := -77;
1107 x_ResourceJobIDTab(li_new_last_index) := null;
1108 x_StartDateTab(li_new_last_index) := ld_last_end_date + 1;
1109 x_EndDateTab(li_new_last_index) := ld_end_date;
1110 end if;
1111
1112 -- Fix holes (x_StartDateTab is definitely not empty here,
1113 -- so no need to check)
1114 li_new_index := 1;
1115 ld_prev_end_date := ld_start_date-1;
1116 for i IN x_StartDateTab.first .. x_StartDateTab.last LOOP
1117 if (x_StartDateTab(i) > ld_prev_end_date+1) then
1118 -- Insert record for hole.
1119 l_new_StartDateTab(li_new_index) := ld_prev_end_date + 1;
1120 l_new_EndDateTab(li_new_index) := x_StartDateTab(i)-1;
1121 l_new_ResOrganizationIDTab(li_new_index) := -77;
1122 l_new_ResourceJobIDTab(li_new_index) := null;
1123 li_new_index := li_new_index + 1;
1124 end if;
1125 l_new_StartDateTab(li_new_index) := x_StartDateTab(i);
1126 l_new_EndDateTab(li_new_index) := x_EndDateTab(i);
1127 l_new_ResOrganizationIDTab(li_new_index)
1128 := x_ResourceOrganizationIDTab(i);
1129 l_new_ResourceJobIDTab(li_new_index) := x_ResourceJobIDTab(i);
1130 li_new_index := li_new_index + 1;
1131 ld_prev_end_date := x_EndDateTab(i);
1132 end loop;
1133
1134 x_ResourceOrganizationIDTab := l_new_ResOrganizationIDTab;
1135 x_ResourceJobIDTab := l_new_ResourceJobIDTab;
1136 x_StartDateTab := l_new_StartDateTab;
1137 x_EndDateTab := l_new_EndDateTab;
1138 -- 4537865 : EXCEPTION BLOCK INCLUDED
1139 EXCEPTION
1140 WHEN NO_DATA_FOUND THEN
1141 PA_FORECASTITEM_PVT.print_message('NO_DATA_FOUND ok, exception not raised');
1142 NULL ;
1143 WHEN OTHERS THEN
1144 x_ResourceOrganizationIDTab.delete;
1145 x_ResourceJobIDTab.delete;
1146 x_StartDateTab.delete;
1147 x_EndDateTab.delete;
1148 FND_MSG_PUB.add_exc_msg
1149 ( p_pkg_name => 'PA_FORECAST_ITEMS_UTILS'
1150 , p_procedure_name => 'Get_Res_Org_And_Job'
1151 , p_error_text => SUBSTRB(SQLERRM,1,240));
1152 RAISE ;
1153 END Get_Res_Org_And_Job;
1154
1155
1156 -----------------------------------------------------------------------------------
1157 -- Function Get_Person_Id
1158 -- Purpose To get the Person ID for resource Id
1159 -- Parameters Resource Id NUMBER YES Resource Id
1160 ------------------------------------------------------------------------------------
1161 FUNCTION Get_Person_Id(p_resource_id NUMBER) RETURN NUMBER IS
1162 li_person_id NUMBER;
1163 BEGIN
1164 PA_FORECASTITEM_PVT.print_message('p_resource_id: ' || p_resource_id);
1165 SELECT person_id
1166 INTO li_person_id
1167 FROM pa_resource_txn_attributes
1168 WHERE resource_id = p_resource_id
1169 AND rownum = 1; --Bug 3086960. Adde by Sachin.
1170 RETURN (li_person_id);
1171 END Get_Person_id;
1172
1173
1174 -----------------------------------------------------------------------------------
1175 -- Function Get_Resource_Id
1176 -- Purpose To get the resource Id for person id
1177 -- Parameters Person Id NUMBER YES Person Id
1178 ------------------------------------------------------------------------------------
1179 FUNCTION Get_resource_Id(p_person_id NUMBER) RETURN NUMBER IS
1180 li_resource_id NUMBER;
1181 BEGIN
1182 SELECT resource_id
1183 INTO li_resource_id
1184 FROM pa_resource_txn_attributes
1185 WHERE person_id = p_person_id;
1186 RETURN (li_resource_id);
1187 END Get_resource_id;
1188 ------------------------------------------------------------------------------------------
1189 -- Function Get_Resource_Type
1190 -- Purpose To get the Resource Type for resource Id
1191 -- Parameters Resource Id NUMBER YES Resource id for its type
1192 ------------------------------------------------------------------------------------------
1193 FUNCTION Get_Resource_Type(p_resource_id NUMBER) RETURN VARCHAR2 IS
1194 lv_resource_type VARCHAR2(30);
1195 BEGIN
1196
1197 SELECT typ.RESOURCE_TYPE_CODE
1198 INTO lv_resource_type
1199 FROM pa_resource_types typ,
1200 pa_resources res
1201 WHERE res.resource_type_id= typ.resource_type_id
1202 AND res.resource_id= p_resource_id;
1203
1204 RETURN (lv_resource_type);
1205 END Get_Resource_Type;
1206
1207
1208
1209 ----------------------------------------------------------------------------------------------------------------------
1210 -- Procedure Get_ForecastOptions
1211 -- Purpose To get the all forecast options from pa_forecasting_options_all table
1212 -- Input parameters
1213 -- Parameters Type Required Description
1214 -- p_org_id NUMBER YES Org id for which all the necessary information is needed
1215 -- Out parameters
1216 -- x_include_admin_proj_flag VARCHAR2 YES Used to store admin project flag
1217 -- x_util_cal_method VARCHAR2 YES Used to store util cal method
1218 -- x_bill_unassign_proj_id NUMBER YES Used to store bill unassigned project id
1219 -- x_bill_unassign_exp_type_class VARCHAR2 YES Used to store bill unassigned expenditure type class
1220 -- x_bill_unassign_exp_type VARCHAR2 YES Used to store bill unassigned expenditure type
1221 -- x_nobill_unassign_proj_id NUMBER YES Used to store without bill unassigned project id
1222 -- x_nobill_unassign_exp_type_class VARCHAR2 YES Used to store without bill unassigned expenditure
1223 -- type class
1224 -- x_nobill_unassign_exp_type VARCHAR2 YES Used to store without bill unassigned expenditure type
1225 -- x_default_tp_amount_type VARCHAR2 YES Used to store default tp amount type
1226 -----------------------------------------------------------------------------------------------------------------------
1227 PROCEDURE Get_ForecastOptions( p_org_id IN NUMBER,
1228 -- x_include_admin_proj_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895, 4576715
1229 x_util_cal_method OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1230 x_bill_unassign_proj_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1231 x_bill_unassign_exp_type_class OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1232 x_bill_unassign_exp_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1233 x_nonbill_unassign_proj_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1234 x_nonbill_unassign_exp_typ_cls OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1235 x_nonbill_unassign_exp_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1236 x_default_tp_amount_type OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1237 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1238 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1239 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1240 IS
1241 l_msg_index_out NUMBER;
1242
1243 BEGIN
1244
1245 -- 4537865 : Initialize x_return_status to SUCCESS
1246 x_return_status := FND_API.G_RET_STS_SUCCESS;
1247
1248 PA_DEBUG.Init_err_stack(
1249 'PA_FORECAST_ITEMS_UTILS.Get_forecastoptions');
1250 -- Selecting columns corresponding to the given org id
1251
1252 PA_FORECASTITEM_PVT.print_message('p_org_id: ' || p_org_id );
1253
1254 -- 2196924: Adding case when p_org_id is null
1255 -- This may occur when there's no HR assignment for
1256 -- part of the resources time, so no ou.
1257 if (p_org_id = -88) then
1258 -- x_include_admin_proj_flag := null; Bug 4576715
1259 x_util_cal_method := null;
1260 x_bill_unassign_proj_id := -66;
1261 x_bill_unassign_exp_type_class := '-99';
1262 x_bill_unassign_exp_type := '-99';
1263 x_nonbill_unassign_proj_id := -66;
1264 x_nonbill_unassign_exp_typ_cls := '-99';
1265 x_nonbill_unassign_exp_type := '-99';
1266 x_default_tp_amount_type := '-99';
1267 else
1268
1269 --R12: MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1270 BEGIN
1271 SELECT -- include_admin_proj_flag, Bug 4576715
1272 bill_unassign_proj_id,
1273 bill_unassign_exp_type_class,bill_unassign_exp_type,
1274 nonbill_unassign_proj_id,nonbill_unassign_exp_typ_cls,
1275 nonbill_unassign_exp_type,default_tp_amount_type,
1276 util_calc_method
1277 INTO -- x_include_admin_proj_flag, Bug 4576715
1278 x_bill_unassign_proj_id,
1279 x_bill_unassign_exp_type_class,x_bill_unassign_exp_type,
1280 x_nonbill_unassign_proj_id,x_nonbill_unassign_exp_typ_cls,
1281 x_nonbill_unassign_exp_type,x_default_tp_amount_type,
1282 x_util_cal_method
1283 FROM pa_forecasting_options_all
1284 WHERE org_id = p_org_id;
1285
1286 /* Bug 2458198 -- Begin */
1287 IF (
1288 -- x_include_admin_proj_flag = 'N' OR Bug 4576715
1289 x_nonbill_unassign_proj_id is null OR x_bill_unassign_proj_id is null) THEN
1290 PA_UTILS.Add_Message(
1291 p_app_short_name => 'PA'
1292 ,p_msg_name =>'PA_UNASSIGNED_PROJ_NO_DEFN');
1293 x_return_status := FND_API.G_RET_STS_ERROR;
1294 x_msg_data := 'PA_UNASSIGNED_PROJ_NO_DEFN';
1295 x_msg_count := FND_MSG_PUB.Count_Msg;
1296
1297 END IF;
1298 /* Bug 2458198 -- End */
1299 exception
1300 WHEN NO_DATA_FOUND THEN
1301 PA_UTILS.Add_Message(
1302 p_app_short_name => 'PA'
1303 ,p_msg_name =>'PA_FORECAST_OPTIONS_NOT_SETUP');
1304 x_return_status := FND_API.G_RET_STS_ERROR;
1305 x_msg_data := 'PA_FORECAST_OPTIONS_NOT_SETUP';
1306 x_msg_count := FND_MSG_PUB.Count_Msg;
1307 end;
1308 end if;
1309 PA_DEBUG.Reset_Err_Stack;
1310
1311 EXCEPTION
1312 WHEN NO_DATA_FOUND THEN
1313
1314 x_msg_count := 1;
1315 x_msg_data := sqlerrm;
1316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1317
1318 FND_MSG_PUB.add_exc_msg
1319 (p_pkg_name => 'PA_FORECAST_ITEMS_UTILS.Get_forecastoptions',
1320 p_procedure_name => PA_DEBUG.G_Err_Stack);
1321
1322 RAISE;
1323
1324 WHEN OTHERS THEN
1325 x_msg_count := 1;
1326 x_msg_data := sqlerrm;
1327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1328
1329 FND_MSG_PUB.add_exc_msg
1330 (p_pkg_name => 'PA_FORECAST_ITEMS_UTILS.Get_forecastoptions',
1331 p_procedure_name => PA_DEBUG.G_Err_Stack);
1332 RAISE;
1333
1334
1335 END Get_ForecastOptions;
1336
1337
1338 ----------------------------------------------------------------------------------------------------------------------
1339 -- Procedure Get_Week_Dates_Range_Fc
1340 -- Purpose To get the global week end date
1341 -- Input parameters
1342 -- Parameters Type Required Description
1343 -- P_Start_Date DATE YES Start date for the week date range
1344 -- P_End_Date DATE YES End date for the week date range
1345 -- Out parameters
1346 -- X_Week_Date_Range_Tab WEEKDATESRANGEFCTABTYP YES Used to store week start and end date
1347 -----------------------------------------------------------------------------------------------------------------------
1348 PROCEDURE Get_Week_Dates_Range_Fc( p_start_date IN DATE,
1349 p_end_date IN DATE,
1350 x_week_date_range_tab OUT NOCOPY PA_FORECAST_GLOB.WeekDatesRangeFcTabTyp , /* 2674619 - Nocopy change */
1351 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1352 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1353 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1354
1355 IS
1356 l_week_ending_date DATE;
1357 l_week_starting_date DATE;
1358 l_week_ending_day VARCHAR2(120);
1359 l_week_starting_day_index NUMBER := 2;
1360 li_cnt INTEGER :=1;
1361 l_msg_index_out NUMBER;
1362 l_end_date DATE; /* Added for bug#2462076 */
1363 l_week_starting_day VARCHAR2(120); /*Bug 5549814 */
1364
1365 BEGIN
1366
1367
1368 PA_DEBUG.Init_err_stack(
1369 'PA_FORECAST_ITEMS_UTILS.Get_Week_Dates_Range_Fc');
1370 -- Taking value of the day from the profile option
1371
1372 l_week_starting_day_index := TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'));
1373
1374 /*Commenting below for Bug 7012687 : not use Select from dual to evaluate expression*/
1375 /* Added for Bug 5549814*/
1376 /* Utilizing the fact that 01-01-1950 was a Sunday and PA lookups value for a Sunday is 1 */
1377 --Select (trim(to_char((to_date('01-01-1950','dd-mm-yyyy')+(l_week_starting_day_index - 1)),'DAY')))
1378 --into l_week_starting_day
1379 --from dual;
1380
1381 /*Adding below for Bug 7012687*/
1382 l_week_starting_day := (trim(to_char((to_date('01-01-1950','dd-mm-yyyy')+(l_week_starting_day_index - 1)),'DAY')));
1383
1384
1385 /* Bug#2462076 Added code for using trunc on the start and end date parameters */
1386 l_week_starting_date := trunc(p_start_date);
1387 l_end_date := trunc(p_end_date);
1388 LOOP
1389 /*Commenting below for Bug 7012687 : not use Select from dual to evaluate expression*/
1390 --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*/
1391 --INTO l_week_ending_date
1392 --FROM dual;
1393
1394 /*Adding below for Bug 7012687*/
1395 l_week_ending_date := (NEXT_DAY(l_week_starting_date,l_week_starting_day)-1);
1396
1397 l_week_starting_date := l_week_ending_date -6;
1398 x_week_date_range_tab(li_cnt).week_start_date := l_week_starting_date;
1399 x_week_date_range_tab(li_cnt).week_end_date := l_week_ending_date;
1400 l_week_starting_date := l_week_ending_date +1;
1401 EXIT WHEN l_week_starting_date > l_end_date; /* Bug#2462076 Changed p_end_date to l_end_date */
1402 li_cnt := li_cnt +1;
1403
1404 END LOOP;
1405 x_return_status := FND_API.G_RET_STS_SUCCESS;
1406 PA_DEBUG.Reset_Err_Stack;
1407
1408 EXCEPTION
1409 WHEN OTHERS THEN
1410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1411 x_msg_count := 1;
1412 x_msg_data := SQLERRM;
1413 FND_MSG_PUB.add_exc_msg
1414 (p_pkg_name => 'PA_FORECAST_ITEMS_UTILS.Get_Week_Dates_Range_Fc',
1415 p_procedure_name => PA_DEBUG.G_Err_Stack);
1416 RAISE;
1417 END Get_Week_Dates_Range_Fc;
1418
1419
1420
1421 ----------------------------------------------------------------------------------------------------------------------
1422 -- Procedure Check_TPAmountType
1423 -- Purpose To validate the tp amount type and code or description
1424 -- Input parameters
1425 -- Parameters Type Required Description
1426 -- p_tp_amount_type_code VARCHAR2 YES Tp amount type code
1427 -- p_tp_amount_type_desc VARCHAR2 YES Tp amount type desc
1428 -- p_check_id_flag VARCHAR2 YES Check id flage
1429 -- Out parameters
1430 -- x_tp_amount_type_code VARCHAR2 YES Tp amount type code
1431 -- x_tp_amount_type_desc VARCHAR2 YES Tp amount type desc
1432 -----------------------------------------------------------------------------------------------------------------------
1433 PROCEDURE Check_TPAmountType(
1434 p_tp_amount_type_code IN VARCHAR2,
1435 p_tp_amount_type_desc IN VARCHAR2,
1436 p_check_id_flag IN VARCHAR2,
1437 x_tp_amount_type_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1438 x_tp_amount_type_desc OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1439 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1440 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1441 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1442
1443 lv_error_msg VARCHAR2(30);
1444
1445 BEGIN
1446
1447 PA_DEBUG.Init_err_stack(
1448 'PA_FORECAST_ITEMS_UTILS.Check_TPAmountType');
1449 IF p_tp_amount_type_code IS NOT NULL AND
1450 p_tp_amount_type_code<>FND_API.G_MISS_CHAR THEN
1451
1452 IF p_check_id_flag = 'Y' THEN
1453
1454 lv_error_msg := 'PA_AMOUNT_TYPE_CODE_AMBIGUOUS';
1455
1456 SELECT lookup_code, meaning
1457 INTO x_tp_amount_type_code,
1458 x_tp_amount_type_desc
1459 FROM pa_lookups
1460 WHERE lookup_type = 'TP_AMOUNT_TYPE'
1461 AND lookup_code = p_tp_amount_type_code;
1462
1463 ELSE
1464 x_tp_amount_type_code := p_tp_amount_type_code;
1465
1466 END IF;
1467
1468 ELSE
1469
1470 lv_error_msg := 'PA_AMOUNT_TYPE_DESC_AMBIGUOUS';
1471
1472 SELECT lookup_code
1473 INTO x_tp_amount_type_code
1474 FROM pa_lookups
1475 WHERE lookup_type = 'TP_AMOUNT_TYPE'
1476 AND meaning = p_tp_amount_type_desc;
1477
1478
1479 END IF;
1480
1481 PA_DEBUG.Reset_Err_Stack;
1482
1483 x_return_status := FND_API.G_RET_STS_SUCCESS;
1484 EXCEPTION
1485 WHEN NO_DATA_FOUND THEN
1486 x_return_status := FND_API.G_RET_STS_ERROR;
1487 x_msg_data := lv_error_msg;
1488
1489 -- 4537865 : Start
1490 x_tp_amount_type_code := NULL ;
1491 x_tp_amount_type_desc := NULL ;
1492 --4537865 : End
1493
1494 FND_MSG_PUB.add_exc_msg
1495 (p_pkg_name =>
1496 'PA_FORECAST_ITEMS_UTILS.Check_TPAmountType',
1497 p_procedure_name => PA_DEBUG.G_Err_Stack);
1498 WHEN TOO_MANY_ROWS THEN
1499 x_return_status := FND_API.G_RET_STS_ERROR;
1500 x_msg_data := lv_error_msg;
1501
1502 -- 4537865 : Start
1503 x_tp_amount_type_code := NULL ;
1504 x_tp_amount_type_desc := NULL ;
1505 --4537865 : End
1506
1507 FND_MSG_PUB.add_exc_msg
1508 (p_pkg_name =>
1509 'PA_FORECAST_ITEMS_UTILS.Check_TPAmountType',
1510 p_procedure_name => PA_DEBUG.G_Err_Stack);
1511 WHEN OTHERS THEN
1512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1513
1514 -- 4537865 : Start
1515 x_tp_amount_type_code := NULL ;
1516 x_tp_amount_type_desc := NULL ;
1517 --4537865 : End
1518
1519 FND_MSG_PUB.add_exc_msg
1520 (p_pkg_name =>
1521 'PA_FORECAST_ITEMS_UTILS.Check_TPAmountType',
1522 p_procedure_name => PA_DEBUG.G_Err_Stack);
1523 --PA_Error_Utils.Set_Error_Stack
1524 -- (`pa_resource_utils.check_resourcename_or_id');
1525 -- This sets the current program unit name in the
1526 -- error stack. Helpful in Debugging
1527 raise;
1528
1529 END Check_TPAmountType;
1530
1531
1532
1533 ----------------------------------------------------------------------------------------------------------
1534 -- Description This procedure will get the defautl values for the Assignment
1535 --
1536 -- Procedure Name Get_Assignment_Default
1537 -- Used Subprograms None
1538 -- Input parameters Type Required Description
1539 -- p_assignment_type VARCHAR2 Yes The assignment type, can be either
1540 -- 'Open assignment'or 'Staffed assignment'.
1541 -- p_project_id NUMBER Yes Project ID
1542 -- p_project_role_id NUMBER Yes Project role ID
1543
1544 -- Output parameters Type Description
1545 -- x_work_type_id NUMBER Default Work Type ID
1546 -- x_default_tp_amount_type VARCHAR2 Default transfer price amount type
1547 -- x_default_job_group_id NUMBER Default job group ID
1548 -- x_default_job_id NUMBER Default jog ID
1549 -- x_org_id NUMBER Default Expenditure OU ID
1550 -- x_carrying_out_organization_id NUMBER Default Expenditure Org ID
1551 -- x_default_assign_exp_type VARCHAR2 Default Expenditure Type
1552 -- x_default_assign_exp_type_cls VARCHAR2 Default Expenditure Type Class
1553 -- x_return_status VARCHAR2 The return status of this procedure
1554 -------------------------------------------------------------------------------------------------------------
1555 PROCEDURE Get_Assignment_Default (p_assignment_type IN VARCHAR2,
1556 p_project_id IN NUMBER,
1557 p_project_role_id IN NUMBER,
1558 p_work_type_id IN NUMBER,
1559 x_work_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1560 x_default_tp_amount_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1561 x_default_job_group_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1562 x_default_job_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1563 x_org_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1564 x_carrying_out_organization_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1565 x_default_assign_exp_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1566 x_default_assign_exp_type_cls OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1567 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1568 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1569 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1570 )
1571 IS
1572 BEGIN
1573 x_return_status := FND_API.G_RET_STS_SUCCESS;
1574
1575 Get_Project_Default( p_assignment_type => p_assignment_type,
1576 p_project_id => p_project_id,
1577 x_work_type_id => x_work_type_id,
1578 x_default_tp_amount_type => x_default_tp_amount_type,
1579 x_org_id => x_org_id,
1580 x_carrying_out_organization_id => x_carrying_out_organization_id,
1581 x_default_assign_exp_type => x_default_assign_exp_type,
1582 x_default_assign_exp_type_cls => x_default_assign_exp_type_cls,
1583 x_return_status => x_return_status,
1584 x_msg_count => x_msg_count,
1585 x_msg_data => x_msg_data );
1586
1587 Get_Project_Role_Default (p_assignment_type => p_assignment_type,
1588 p_project_role_id => p_project_role_id,
1589 x_default_job_group_id => x_default_job_group_id,
1590 x_default_job_id => x_default_job_id,
1591 x_return_status => x_return_status,
1592 x_msg_count => x_msg_count,
1593 x_msg_data => x_msg_data );
1594
1595 IF FND_MSG_PUB.Count_Msg > 0 THEN
1596 x_return_status := FND_API.G_RET_STS_ERROR;
1597 x_msg_count := FND_MSG_PUB.Count_Msg;
1598 x_msg_data := NULL;
1599 END IF;
1600
1601 EXCEPTION
1602 WHEN OTHERS THEN
1603 -- 4537865 : Start
1604
1605 x_work_type_id := NULL ;
1606 x_default_tp_amount_type := NULL ;
1607 x_default_job_group_id := NULL ;
1608 x_default_job_id := NULL ;
1609 x_org_id := NULL ;
1610 x_carrying_out_organization_id := NULL ;
1611 x_default_assign_exp_type := NULL ;
1612 x_default_assign_exp_type_cls := NULL ;
1613 -- 4537865 : End
1614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1615 x_msg_count := 1;
1616 x_msg_data := SQLERRM;
1617 FND_MSG_PUB.add_exc_msg
1618 (p_pkg_name => 'PA_FORECAST_ITEMS_UTILS.Get_assignment_default',
1619 p_procedure_name => PA_DEBUG.G_Err_Stack);
1620 raise;
1621
1622 END Get_Assignment_Default;
1623
1624 PROCEDURE Get_Project_Default ( p_assignment_type IN VARCHAR2,
1625 p_project_id IN NUMBER,
1626 x_work_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1627 x_default_tp_amount_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1628 x_org_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1629 x_carrying_out_organization_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1630 x_default_assign_exp_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1631 x_default_assign_exp_type_cls OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1632 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1633 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1634 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1635 )
1636 IS
1637
1638 BEGIN
1639 x_return_status := FND_API.G_RET_STS_SUCCESS;
1640
1641 SELECT work_type_id,
1642 org_id,
1643 carrying_out_organization_id
1644 INTO x_work_type_id,
1645 x_org_id,
1646 x_carrying_out_organization_id
1647 FROM pa_projects_all
1648 WHERE project_id = p_project_id;
1649
1650 begin
1651 --R12: MOAC Changes: Bug 4363092: Removed nvl usage with org_id
1652 SELECT default_assign_exp_type,
1653 default_assign_exp_type_class
1654 INTO x_default_assign_exp_type,
1655 x_default_assign_exp_type_cls
1656 FROM pa_forecasting_options_all
1657 WHERE org_id = x_org_id;
1658
1659 exception
1660 WHEN NO_DATA_FOUND THEN
1661 PA_UTILS.Add_Message(
1662 p_app_short_name => 'PA'
1663 ,p_msg_name =>'PA_FORECAST_OPTIONS_NOT_SETUP');
1664 x_return_status := FND_API.G_RET_STS_ERROR;
1665 end;
1666
1667 -- Populate expenditure_organization_id only for requirement
1668 IF p_assignment_type <> 'OPEN_ASSIGNMENT' THEN
1669 x_org_id := NULL;
1670 x_carrying_out_organization_id := NULL;
1671 END IF;
1672
1673 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1674 Pa_Fp_Org_Fcst_Utils.Get_Tp_Amount_Type(
1675 p_project_id => p_project_id,
1676 p_work_type_id => x_work_type_id,
1677 x_tp_amount_type => x_default_tp_amount_type,
1678 x_return_status => x_return_status,
1679 x_msg_count => x_msg_count,
1680 x_msg_data => x_msg_data);
1681 END IF;
1682
1683 EXCEPTION
1684 WHEN OTHERS THEN
1685 -- 4537865 : Start
1686
1687 x_work_type_id := NULL ;
1688 x_default_tp_amount_type := NULL ;
1689 x_org_id := NULL ;
1690 x_carrying_out_organization_id := NULL ;
1691 x_default_assign_exp_type := NULL ;
1692 x_default_assign_exp_type_cls := NULL ;
1693 -- 4537865 : End
1694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1695 x_msg_count := 1;
1696 x_msg_data := SQLERRM;
1697 FND_MSG_PUB.add_exc_msg
1698 (p_pkg_name => 'PA_FORECAST_ITEMS_UTILS.Get_project_default',
1699 p_procedure_name => PA_DEBUG.G_Err_Stack);
1700 raise;
1701
1702 END Get_Project_Default;
1703
1704 PROCEDURE Get_Project_Role_Default (p_assignment_type IN VARCHAR2,
1705 p_project_role_id IN NUMBER,
1706 x_default_job_group_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1707 x_default_job_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1708 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1709 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1710 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1711 )
1712 IS
1713 BEGIN
1714 x_return_status := FND_API.G_RET_STS_SUCCESS;
1715
1716 begin
1717 SELECT b.job_group_id,
1718 a.default_job_id
1719 INTO x_default_job_group_id,
1720 x_default_job_id
1721 FROM (select project_role_id,
1722 pa_role_job_bg_utils.get_job_id(project_role_id) default_job_id
1723 from pa_project_role_types_b
1724 where role_party_class = 'PERSON'
1725 and project_role_id = p_project_role_id) a,
1726 per_jobs b
1727 WHERE b.job_id = a.default_job_id;
1728 exception
1729 WHEN NO_DATA_FOUND THEN
1730 PA_UTILS.Add_Message(
1731 p_app_short_name => 'PA'
1732 ,p_msg_name => 'PA_JOB_NOT_FOUND');
1733 end;
1734
1735 IF p_assignment_type <> 'OPEN_ASSIGNMENT' THEN
1736 x_default_job_id := NULL;
1737 END IF;
1738
1739 EXCEPTION
1740 WHEN OTHERS THEN
1741
1742 -- 4537865 : Start
1743 x_default_job_group_id := NULL ;
1744 x_default_job_id := NULL ;
1745 -- 4537865 : End
1746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1747 x_msg_count := 1;
1748 x_msg_data := SQLERRM;
1749 FND_MSG_PUB.add_exc_msg
1750 (p_pkg_name => 'PA_FORECAST_ITEMS_UTILS.Get_Project_Role_default',
1751 p_procedure_name => PA_DEBUG.G_Err_Stack);
1752 raise;
1753
1754 END Get_Project_Role_Default;
1755
1756 END PA_FORECAST_ITEMS_UTILS;
1757