1 PACKAGE pa_resource_utils AS
2 -- $Header: PARSUTLS.pls 120.5 2007/04/19 06:26:17 kjai ship $
3
4 --
5 -- Global Variables.
6 --
7 G_SELECTED_FLAG VARCHAR2(1);
8 G_PRIMARY_CONTACT_NAME VARCHAR2(240);
9 G_PRIMARY_CONTACT_ID NUMBER;
10 G_CURRENT_PROJECT_ID PA_PROJECTS_ALL.PROJECT_ID%TYPE;
11 G_PERSON_ID PA_EMPLOYEES.PERSON_ID%TYPE;
12 G_VERSION_ID PER_ORG_STRUCTURE_ELEMENTS.ORG_STRUCTURE_VERSION_ID%TYPE;
13 G_START_ORG_ID PER_ORG_STRUCTURE_ELEMENTS.ORGANIZATION_ID_PARENT%TYPE;
14 G_PERIOD_DATE DATE ;
15 type PLSQLTAB_NAMEARRAY is table of varchar2(120) index by binary_integer;
16 type PLSQLTAB_INTARRAY is table of NUMBER index by binary_integer;
17 g_provisional_hours NUMBER;
18 g_confirmed_hours NUMBER;
19 /*Bug 3737529 :- Added the below global variables */
20 G_HR_SUPERVISOR_NAME VARCHAR2(240);
21 G_HR_SUPERVISOR_ID NUMBER;
22 G_ASSIGNMENT_ID NUMBER;
23 G_RESOURCE_ID NUMBER;
24 /*Bug 3737529 : Code addtion ends*/
25 G_START_DATE DATE; -- For bug 4443604
26 G_ORGANIZATION_ID HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE; -- 4882876
27 G_ORGANIZATION_NAME HR_ORGANIZATION_UNITS.NAME%TYPE; -- 4882876
28
29 G_TERM_PERSON_ID PA_EMPLOYEES.PERSON_ID%TYPE; -- Bug 5683340
30 G_FTE_DATE DATE; -- Bug 5683340
31 G_FTE_FLAG VARCHAR2(1); -- Bug 5683340
32
33 --
34 -- PROCEDURE
35 -- Check_ResourceName_Or_Id
36 -- PURPOSE
37 -- This procedure does the following
38 -- If Resource name is passed converts it to the id
39 -- If Resource Id is passed,
40 -- based on the check_id_flag validates it
41 -- HISTORY
42 -- 27-JUN-2000 P. Bandla Created
43 -- 05-SEP-2000 P. Bandla Modified
44 -- Added P_DATE parameter
45
46 PROCEDURE Check_ResourceName_Or_Id(
47 p_resource_id IN NUMBER,
48 p_resource_name IN VARCHAR2,
49 p_date IN DATE DEFAULT SYSDATE,
50 p_end_date IN DATE :=null, -- 3235018
51 p_check_id_flag IN VARCHAR2,
52 p_resource_type_id IN NUMBER DEFAULT 101,
53 x_resource_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
54 x_resource_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
55 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
56 x_error_message_code OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
57
58 --
59 -- PROCEDURE
60 -- Get_CRM_Res_id
61 -- PURPOSE
62 -- Returns the CRM Resource_id based on the
63 -- project_player_id
64
65 -- HISTORY
66 -- 27-JUN-2000 P. Bandla Created
67
68 PROCEDURE Get_CRM_Res_id(
69 P_PROJECT_PLAYER_ID IN NUMBER DEFAULT NULL,
70 P_RESOURCE_ID IN NUMBER DEFAULT NULL,
71 X_JTF_RESOURCE_ID OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
72 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
73 X_ERROR_MESSAGE_CODE OUT NOCOPY VARCHAR2 ) ; --File.Sql.39 bug 4440895
74
75 PROCEDURE CHECK_CC_FOR_RESOURCE(
76 P_RESOURCE_ID IN NUMBER,
77 P_PROJECT_ID IN NUMBER,
78 P_START_DATE IN DATE,
79 P_END_DATE IN DATE DEFAULT NULL,
80 X_CC_OK OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
81 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
82 X_ERROR_MESSAGE_CODE OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
83
84 --
85 -- PROCEDURE
86 -- Check_Resource_Belongs_ExpOrg
87 -- PURPOSE
88 -- This procedure does the following
89 -- For the given Resource Id,
90 -- checks if that resource
91 -- belongs to an expenditure organization
92 -- HISTORY
93 -- 22-AUG-2000 P.Bandla Created
94 --
95 PROCEDURE CHECK_RES_BELONGS_EXPORG(
96 P_RESOURCE_ID IN NUMBER,
97 --P_DATE IN DATE DEFAULT 'SYSDATE',
98 X_VALID OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
99 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
100 X_ERROR_MESSAGE_CODE OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
101
102 PROCEDURE set_global_variables( p_selected_flag IN VARCHAR2
103 ,p_person_id IN PA_EMPLOYEES.PERSON_ID%TYPE
104 ,p_version_id IN PER_ORG_STRUCTURE_ELEMENTS.ORG_STRUCTURE_VERSION_ID%TYPE
105 ,p_start_org_id IN PER_ORG_STRUCTURE_ELEMENTS.ORGANIZATION_ID_PARENT%TYPE
106 );
107
108 FUNCTION get_selected_flag RETURN VARCHAR2;
109 pragma RESTRICT_REFERENCES (get_selected_flag, WNDS, WNPS );
110
111 FUNCTION get_person_id RETURN PA_EMPLOYEES.PERSON_ID%TYPE;
112 pragma RESTRICT_REFERENCES (get_person_id, WNDS, WNPS );
113
114 FUNCTION get_version_id RETURN PER_ORG_STRUCTURE_ELEMENTS.ORG_STRUCTURE_VERSION_ID%TYPE;
115 pragma RESTRICT_REFERENCES (get_version_id, WNDS, WNPS );
116
117 FUNCTION get_start_org_id RETURN PER_ORG_STRUCTURE_ELEMENTS.ORGANIZATION_ID_PARENT%TYPE;
118 pragma RESTRICT_REFERENCES (get_start_org_id, WNDS, WNPS );
119
120 FUNCTION get_projected_end_date(p_person_id IN NUMBER) RETURN DATE;
121 pragma RESTRICT_REFERENCES (get_projected_end_date, WNDS, WNPS);
122
123 FUNCTION get_period_date RETURN DATE;
124 pragma RESTRICT_REFERENCES (get_period_date, WNDS, WNPS );
125
126 PROCEDURE populate_role_flags( p_person_id IN PA_EMPLOYEES.PERSON_ID%TYPE
127 ,p_org_id IN PER_ORG_STRUCTURE_ELEMENTS.ORGANIZATION_ID_PARENT%TYPE
128 ,x_res_aut_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
129 ,x_proj_aut_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
130 ,x_prim_ctct_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
131 ,x_frcst_aut_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
132 ,x_frcst_prim_ctct_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
133 ,x_utl_aut_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
134 ) ;
135
136 -- 4882876 : Removed WNPS restriction from get_organization_name
137 FUNCTION get_organization_name ( p_org_id IN HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE ) RETURN HR_ORGANIZATION_UNITS.NAME%TYPE;
138 pragma RESTRICT_REFERENCES (get_organization_name, WNDS);
139
140 PROCEDURE delete_grant( p_person_id IN NUMBER
141 ,p_org_id IN NUMBER
142 ,p_role_name IN VARCHAR2
143 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
144 );
145
146 PROCEDURE insert_grant( p_person_id IN NUMBER
147 ,p_org_id IN NUMBER
148 ,p_role_name IN VARCHAR2
149 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
150 );
151
152
153 FUNCTION GetValJobGradeId (P_job_id IN per_jobs.job_id%TYPE,
154 P_Job_Grp_Id IN per_jobs.job_group_id%TYPE)
155 return per_valid_grades.grade_id%type;
156 pragma RESTRICT_REFERENCES (GetValJobGradeId, WNDS );
157
158 PROCEDURE GetToJobId (P_From_Forecast_JobGrpId IN per_jobs.job_group_id%TYPE,
159 P_From_JobId IN per_jobs.job_id%TYPE,
160 P_To_Proj_Cost_JobGrpId IN per_jobs.job_group_id%TYPE,
161 X_To_JobId OUT NOCOPY per_jobs.job_id%TYPE); --File.Sql.39 bug 4440895
162
163 PROCEDURE GetToJobName (P_From_Forecast_JobGrpId IN per_jobs.job_group_id%TYPE,
164 P_From_JobId IN per_jobs.job_id%TYPE,
165 P_To_Proj_Cost_JobGrpId IN per_jobs.job_group_id%TYPE,
166 X_To_JobName OUT NOCOPY per_jobs.name%TYPE); --File.Sql.39 bug 4440895
167
168
169
170
171 -- PROCEDURE
172 -- get_resource_analyst
173 -- PURPOSE
174 -- This procedure does the following
175 -- If Person Id is passed it retrives the corresponding
176 -- resource analyst Id ,Resource Analyst Name,Primary contact Id ,
177 -- Name.
178 -- HISTORY
179 -- 25-SEP-2000 R Iyengar
180
181 PROCEDURE get_resource_analyst
182 (P_PersonId IN NUMBER,
183 P_ResourceIdTab OUT NOCOPY PLSQLTAB_INTARRAY,
184 P_ResourceAnalystTab OUT NOCOPY PLSQLTAB_NAMEARRAY,
185 P_PrimaryContactId OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
186 P_PrimaryContactName OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
187 X_return_Status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
188 X_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
189
190
191 -- PROCEDURE
192 -- get_org_primary_contact
193 -- PURPOSE
194 -- This procedure does the following
195 -- If Resource Id is passed it retrives the corresponding
196 -- resource primary contact Id , Name,Managerid and manager name.
197 -- HISTORY
198 --
199 -- 29-SEP-2000 R Iyengar created
200 -- 05-SEP-2001 virangan Added p_assignment_id parameter
201 -- 03-OCT-2001 virangan Defaulted p_assignment_id
202
203 PROCEDURE get_org_primary_contact(P_ResourceId IN NUMBER,
204 p_assignment_id IN NUMBER DEFAULT NULL,
205 x_PrimaryContactId OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
206 x_PrimaryContactName OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
207 x_ManagerId OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
208 x_ManagerName OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
209 x_return_Status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
210 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
211 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
212 );
213
214 -- PROCEDURE
215 -- get_resource_id
216 -- PURPOSE
217 -- This function returns back the resource_id
218 -- for a person based on the person_Id passed in.
219 -- HISTORY
220 -- 08-NOV-2000 R Fadia
221
222 FUNCTION get_resource_id(P_Person_Id IN NUMBER)
223 RETURN NUMBER;
224
225 --
226 -- FUNCTION
227 -- Get_Resource_Headcount
228 -- PURPOSE
229 -- This function gets the resource head count for a given
230 -- organization, category, period type, period name, Global
231 -- week end date and year.
232 --
233 -- HISTORY Changes for BUG: 1660614
234 -- Added the following new parameters:
235 -- p_category : This could be one of the following.
236 -- 1. SUBORG_EMP - Includes all subordinate excluding the direct reports
237 -- 2. DIRECT_EMP - Includes all the direct reports
238 -- 3. TOTAL_EMP - Includes all subordinates
239 -- p_period_type: Possible values are GL - gl period, GE - global expenditure
240 -- week, PA - pa period, QR - quarter, YR - year
241 -- p_period_name: Values only for GL and PA periods, Quarter number for QR
242 -- p_end_date: Only for global expenditure week GE
243 -- p_year : For YR and QR types - pass the year
244 FUNCTION Get_Resource_Headcount(p_org_id IN NUMBER,
245 p_category IN VARCHAR2,
246 p_period_type IN VARCHAR2,
247 p_period_name IN VARCHAR2,
248 p_end_date in DATE,
249 p_year in NUMBER) RETURN NUMBER;
250
251 PROCEDURE Set_Period_Date(p_period_type IN VARCHAR2,
252 p_period_name IN VARCHAR2,
253 p_end_date IN DATE,
254 p_year IN NUMBER);
255
256 PROCEDURE get_manager_id_name(P_personid IN NUMBER,
257 p_start_date IN DATE,
258 x_ManagerId OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
259 x_ManagerName OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
260 x_error_message_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
261 x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
262
263 FUNCTION Get_People_Assigned(p_org_id in pa_resources_denorm.resource_organization_id%TYPE,
264 p_date in DATE,
265 p_emp_type IN VARCHAR DEFAULT 'EMP') RETURN number; --Added p_emp_type for bug 5680366
266
267 -- PROCEDURE
268 -- get_resource_manager_id
269 -- PURPOSE
270 -- This function returns the root manager_id for the logged in
271 -- FND user
272 -- HISTORY
273 -- 27-JAN-2001 virangan Created
274 FUNCTION get_resource_manager_id(p_user_id IN NUMBER) RETURN NUMBER;
275
276 -- PROCEDURE
277 -- get_resource_capacity
278 -- PURPOSE
279 -- This function returns the capacity hours for a resource
280 -- for the given week
281 -- HISTORY
282 -- 14-MAR-2001 virangan Created
283 FUNCTION get_resource_capacity(res_id IN NUMBER, week_start_date IN DATE)
284 RETURN NUMBER;
285
286
287 -- PROCEDURE
288 -- Get_Current_Project_NameNumber
289 -- PURPOSE
290 -- This function has been created for CURRENT_PROJECT_NAME_NUMBER column
291 -- of pa_resource_availability_v. This will return the project namd and
292 -- number in the format: project_name(project_number).
293 --
294 -- HISTORY
295 -- 09-APR-2001 snam Created
296 FUNCTION Get_Current_Project_NameNumber(p_resource_id IN NUMBER)
297 RETURN VARCHAR2;
298
299 -- PROCEDURE
300 -- Get_Current_Project_Id
301 -- PURPOSE
302 -- This function has been created for CURRENT_PROJECT_ID column
303 -- of pa_resource_availability_v. This procedure should be called after
304 -- calling 'Get_Current_Project_NameNumber'.
305 -- HISTORY
306 -- 05-Sep-2001 snam Created
307 FUNCTION Get_Current_Project_Id(p_resource_id IN NUMBER)
308 RETURN NUMBER;
309
310 -- PROCEDURE
311 -- Get_Person_name
312 -- PURPOSE
313 -- This procedure returns the persons name for
314 -- a given person_id
315 --
316 -- HISTORY
317 -- 10-MAY-2001 created virangan
318 --
319 PROCEDURE get_person_name ( p_person_id IN NUMBER,
320 x_person_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
321 x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
322
323 -- PROCEDURE
324 -- Get_Location_Details
325 -- PURPOSE
326 -- This procedure returns location details for
327 -- given location id
328 --
329 -- HISTORY
330 -- 10-MAY-2001 created virangan
331 --
332 PROCEDURE get_location_details (p_location_id IN NUMBER,
333 x_address_line_1 OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
334 x_address_line_2 OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
335 x_address_line_3 OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
336 x_town_or_city OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
337 x_postal_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
338 x_country OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
339 x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
340
341 -- PROCEDURE
342 -- Get_Org_Defaults
343 -- PURPOSE
344 -- This procedure returns the default operating unit and default
345 -- calendar for an organization
346 --
347 -- HISTORY
348 -- 10-MAY-2001 created virangan
349 --
350 PROCEDURE get_org_defaults (p_organization_id IN NUMBER,
351 x_default_ou OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
352 x_default_cal_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
353 x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
354
355 -- PROCEDURE
356 -- Check_Exp_Org
357 -- PURPOSE
358 -- This procedure checks if an organization belongs
359 -- to an expenditure hierarchy or not
360 --
361 -- HISTORY
362 -- 10-MAY-2001 created virangan
363 --
364 PROCEDURE Check_Exp_Org (p_organization_id IN NUMBER,
365 x_valid OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
366 x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
367
368 -- PROCEDURE
369 -- Check_Res_Exists
370 -- PURPOSE
371 -- This procedure checks if a person exists in PA
372 -- giver a person_id
373 --
374 -- HISTORY
375 -- 10-MAY-2001 virangan created
376 -- 28-MAR-2001 adabdull Added parameter p_party_id and set
377 -- this and p_person_id with default null
378 PROCEDURE Check_Res_Exists (p_person_id IN NUMBER DEFAULT NULL,
379 p_party_id IN NUMBER DEFAULT NULL,
380 x_valid OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
381 x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
382
383 -- FUNCTION
384 -- Get_Org_Prim_Contact_Name
385 -- PURPOSE
386 -- This function returns the primary contact name of the given organzation
387 -- HISTORY
388 -- 11-JUL-2001 snam Created
389 FUNCTION Get_Org_Prim_Contact_Name(p_org_id IN NUMBER,
390 p_prim_role_name IN VARCHAR2)
391 RETURN VARCHAR2;
392
393
394 -- FUNCTION
395 -- Get_Org_Prim_Contact_Id
396 -- PURPOSE
397 -- This function returns the primary_contact_id of the org which has been queried
398 -- in the function 'Get_Org_Prim_Contact_Name'. This function should be used only
399 -- after calling the funtion 'Get_Org_Prim_Contact_Name'.
400 -- HISTORY
401 -- 27-AUG-2001 snam Created
402 FUNCTION Get_Org_Prim_Contact_Id(p_org_id IN NUMBER,
403 p_prim_role_name IN VARCHAR2)
404 RETURN NUMBER;
405
406
407 -- FUNCTION
408 -- Is_Future_Resource
409 -- PURPOSE
410 -- This procedure checks if a person has only future
411 -- records in pa_resources_denorm
412 --
413 -- HISTORY
414 -- 31-AUG-2001 created virangan
415 --
416 FUNCTION Is_Future_Resource (p_resource_id IN NUMBER)
417 RETURN VARCHAR2;
418
419 -- FUNCTION
420 -- Get_Resource_Start_date
421 -- PURPOSE
422 -- This procedure returns the start date of the resource
423 -- in pa_resources_denorm
424 --
425 -- HISTORY
426 -- 31-AUG-2001 created virangan
427 --
428 FUNCTION Get_Resource_Start_Date (p_resource_id IN NUMBER)
429 RETURN DATE;
430
431
432 -- FUNCTION
433 -- Get_Person_Start_date
434 -- PURPOSE
435 -- This procedure returns the start date of the person
436 -- in per_all_people_f
437 --
438 -- HISTORY
439 -- 21-JAN-2003 created sramesh for bug 2686120
440 --
441 FUNCTION Get_Person_Start_Date (p_person_id IN NUMBER)
442 RETURN DATE;
443 -- FUNCTION
444 -- Get_Resource_Effective_date
445 -- PURPOSE
446 -- This procedure returns the effective date of the resource
450 -- HISTORY
447 -- in pa_resources_denorm. This is the resource_effective_start_date
448 -- for a future resource or sysdate for active resources
449 --
451 -- 17-SEP-2001 created virangan
452 --
453 FUNCTION Get_Resource_Effective_Date (p_resource_id IN NUMBER)
454 RETURN DATE;
455
456 --
457 -- PROCEDURE
458 -- Get_Res_Capacity
459 -- PURPOSE
460 -- This procedure does the following
461 -- For the given Resource Id, start date and end date
462 -- gets the capacity hours for the resource
463 -- HISTORY
464 -- 04-SEP-2001 Vijay Ranganathan created
465 --
466 FUNCTION get_res_capacity( p_resource_id IN NUMBER,
467 p_start_date IN DATE,
468 p_end_date IN DATE)
469 RETURN NUMBER;
470
471 --
472 -- PROCEDURE
473 -- Get_Res_Wk_Capacity
474 -- PURPOSE
475 -- This procedure does the following
476 -- For the given Resource Id, week date date
477 -- gets the capacity hours for the resource
478 -- HISTORY
479 -- 13-SEP-2001 Vijay Ranganathan created
480 --
481 FUNCTION get_res_wk_capacity( p_resource_id IN NUMBER,
482 p_wk_date IN DATE)
483 RETURN NUMBER;
484
485 -- FUNCTION
486 -- get_pa_logged_user
487 -- PURPOSE
488 -- This procedure checks if logged user is
489 -- Project Super User or Resource Manager
490 -- or Staffing Manager
491 --
492 -- HISTORY
493 -- 25-SEP-2001 created virangan
494 --
495 FUNCTION get_pa_logged_user ( p_authority IN VARCHAR2 DEFAULT 'RESOURCE')
496 RETURN VARCHAR2;
497
498 --
499 -- PROCEDURE
500 -- Get_Provisional_hours
501 -- PURPOSE
502 -- This procedure gets the provisional hours
503 -- for a resource on a given date
504 -- HISTORY
505 -- 22-OCT-2001 Vijay Ranganathan created
506 --
507 FUNCTION get_provisional_hours
508 ( p_resource_id IN Number,
509 p_Week_date IN DATE)
510 RETURN NUMBER;
511
512 --
513 -- PROCEDURE
514 -- Get_Confirmed_hours
515 -- PURPOSE
516 -- This procedure gets the confirmed hours
517 -- for a resource based on the date set in
518 -- the get_provisional_hours call
519 -- HISTORY
520 -- 22-OCT-2001 Vijay Ranganathan created
521 --
522 FUNCTION get_confirmed_hours
523 RETURN NUMBER;
524
525 -- FUNCTION
526 -- check_user_has_res_auth
527 -- PURPOSE
528 -- This function checks if the given user has resource authority
529 -- over the specified resource
530 -- HISTORY
531 -- 03-OCT-2001 virangan Created
532 FUNCTION check_user_has_res_auth (p_user_person_id IN NUMBER
533 ,p_resource_id IN NUMBER )
534 RETURN VARCHAR2;
535
536 -- PROCEDURE
537 -- get_person_id
538 -- PURPOSE
539 -- This function returns back the person_id
540 -- for a person based on the resource_id passed in.
541 -- HISTORY
542 -- 13-NOV-2001 shyugen
543
544 FUNCTION get_person_id(p_resource_id IN NUMBER)
545 RETURN NUMBER;
546
547 --
548 -- FUNCTION
549 -- get_person_id_from_party_id
550 -- PURPOSE
551 -- This function returns back the person_id
552 -- for a person based on the party_id passed in.
553 -- HISTORY
554 -- 22-OCT-2002 ramurthy
555
556 FUNCTION get_person_id_from_party_id(p_party_id IN NUMBER)
557 RETURN NUMBER;
558 --
559
560 -- PROCEDURE
561 -- check_res_not_terminated
562 -- PURPOSE
563 -- This function returns true if the person has not been
564 -- terminated and false if it is a terminated employee.
565 -- HISTORY
566 -- 14-FEB-2003 ramurthy Created
567 FUNCTION check_res_not_terminated(p_object_type IN VARCHAR2,
568 p_object_id IN NUMBER,
569 p_effective_start_date IN DATE)
570 RETURN BOOLEAN;
571
572
573 -- PROCEDURE
574 -- validate_person
575 -- PURPOSE
576 -- This procedure checks if the resource is valid as of the
577 -- start date in the pa_resources_denorm table
578 --
579 PROCEDURE validate_person ( p_person_id IN NUMBER,
580 p_start_date IN DATE,
581 x_return_status OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
582
583 -- PROCEDURE
584 -- get_party_id
585 -- PURPOSE
586 -- This function returns back the party_id
587 -- for a person based on the resource_id passed in.
588 -- HISTORY
589 -- 01-APR-2002 adabdull
590
591 FUNCTION get_party_id(p_resource_id IN NUMBER)
592 RETURN NUMBER;
593
594
595 -- PROCEDURE
596 -- get_resource_type
600 -- HISTORY
597 -- PURPOSE
598 -- This function returns back the resource_type
599 -- for a person based on the resource_id passed in.
601 -- 01-APR-2002 adabdull
602
603 FUNCTION get_resource_type(p_resource_id IN NUMBER)
604 RETURN VARCHAR2;
605
606 -- PROCEDURE
607 -- allocate_unique
608 -- PURPOSE
609 -- This procedure returns a lock handle for retrieving
610 -- and releasing a dbms_lock
611 PROCEDURE allocate_unique(p_lock_name IN VARCHAR2,
612 p_lock_handle OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
613
614
615 -- PROCEDURE
616 -- acquire_user_lock
617 -- PURPOSE
618 -- This procedure acquires a user lock and return
619 -- status (whether success or failure)
620 FUNCTION Acquire_User_Lock ( p_source_id IN NUMBER,
621 p_lock_for IN VARCHAR2)
622
623 RETURN NUMBER;
624
625
626 -- PROCEDURE
627 -- release_user_lock
628 -- PURPOSE
629 -- This procedure release a user lock and return
630 -- status (whether success or failure)
631 FUNCTION Release_User_Lock (p_source_id IN NUMBER,
632 p_lock_for IN VARCHAR2)
633 RETURN NUMBER;
634
635
636 -- PROCEDURE
637 -- get_resource_id
638 -- PURPOSE
639 -- This function returns the resource_id of the
640 -- person using the fnd user name passed to the
641 -- function
642 FUNCTION get_resource_id(p_user_name IN VARCHAR2 DEFAULT NULL,
643 p_user_id IN NUMBER DEFAULT NULL)
644 RETURN NUMBER;
645
646 -- PROCEDURE
647 -- get_res_name_from_type
648 -- PURPOSE
649 -- This function returns the name of the
650 -- person using the resource type to determine whether
651 -- it is an HR or HZ resource.
652 FUNCTION get_res_name_from_type(p_resource_type_id IN NUMBER,
653 p_resource_source_id IN NUMBER)
654 RETURN VARCHAR2;
655
656 -- PROCEDURE
657 -- get_resource_name
658 -- PURPOSE
659 -- This function returns the resource_name of the
660 -- resource_id passed in using pa_resources table
661 FUNCTION get_resource_name(p_resource_id IN NUMBER)
662 RETURN VARCHAR2;
663
664 -- FUNCTION
665 -- get_pa_logged_resp
666 -- PURPOSE
667 -- This procedure checks if logged responsibility is
668 -- Project Super User or Resource Manager
669 -- or Staffing Manager
670 --
671 -- HISTORY
672 -- 23-July-2002 created virangan
673 --
674 FUNCTION get_pa_logged_resp
675 RETURN VARCHAR2;
676
677
678 -- PROCEDURE
679 -- Check_ManagerName_Or_Id
680 -- PURPOSE
681 -- Specifically for resource supervisor hierarchy use.
682 -- This procedure validates the manager_id and manager_name passed.
683 -- It also depends on the responsibility value. User needs to pass
684 -- RM if resource manager because it uses another view to validate
685 -- the manager (whether the manager belongs to the login user
686 -- HR supervisor hierarchy).
687 -- HISTORY
688 -- 20-AUG-2002 Created adabdull
689 --+
690 PROCEDURE Check_ManagerName_Or_Id(
691 p_manager_name IN VARCHAR2
692 ,p_manager_id IN NUMBER
693 ,p_responsibility IN VARCHAR2 DEFAULT NULL
694 ,p_check IN VARCHAR2 DEFAULT 'N'
695 ,x_manager_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
696 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
697 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
698 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
699
700 PROCEDURE get_org_id(P_personid IN NUMBER,
701 p_start_date IN DATE,
702 x_orgid OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
703 x_error_message_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
704 x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
705
706
707 -- FUNCTION
708 -- get_person_name_no_date
709 -- PURPOSE
710 -- This function returns the latest person name not
711 -- based on any date.
712 --
713 -- HISTORY
714 -- 28-APR-2003 shyugen
715
716 FUNCTION get_person_name_no_date(p_person_id IN NUMBER)
717 RETURN VARCHAR;
718
719 /*Bug 3737529: Code addition starts*/
720 FUNCTION get_hr_manager_id(p_resource_id IN NUMBER,p_start_date IN DATE DEFAULT NULL)
721 RETURN NUMBER;
722
723 FUNCTION get_hr_manager_name(p_resource_id IN NUMBER,p_start_date IN DATE DEFAULT NULL)
724 RETURN VARCHAR2;
725 /*Bug 3737529: Code addition ends*/
726
727 /* *******************************************************************
728 * This function checks to see if the given supplier ID is used by any
729 * planning resource lists or resource breakdown structures. If it is
730 * in use, it returns 'Y'; if not, it returns 'N'
731 * ******************************************************************* */
732 FUNCTION chk_supplier_in_use(p_supplier_id IN NUMBER)
733 RETURN VARCHAR2;
734
735 --
736 -- FUNCTION
737 -- get_term_type
738 -- PURPOSE
739 -- This function returns the leaving/termination reason type
740 -- of an employee/contingent worker as 'V' or 'I'
741 -- HISTORY
742 -- 05-MAR-207 kjai Created for Bug 5683340
743 --
744 FUNCTION get_term_type( p_person_id IN PA_EMPLOYEES.PERSON_ID%TYPE )
745 RETURN VARCHAR2;
746
747 --
748 -- PROCEDURE
749 -- Init_FTE_Sync_WF
750 -- PURPOSE
751 -- This procedure is used to initiate Timeout_Termination_Process
752 -- workflow for future termination of employee.
753 --
754 -- HISTORY
755 -- 05-MAR-207 kjai Created for Bug 5683340
756 --
757 PROCEDURE Init_FTE_Sync_WF(
758 p_person_id IN PA_EMPLOYEES.PERSON_ID%TYPE,
759 x_invol_term OUT NOCOPY VARCHAR2,
760 x_return_status OUT NOCOPY VARCHAR2,
761 x_msg_data OUT NOCOPY VARCHAR2,
762 x_msg_count OUT NOCOPY NUMBER);
763
764 --
765 -- PROCEDURE
766 -- set_fte_flag
767 -- PURPOSE
768 -- This procedure sets the new future_term_wf_flag
769 -- in table pa_resources for the passed person_id
770 -- HISTORY
771 -- 05-MAR-207 kjai Created for Bug 5683340
772 --
773 PROCEDURE Set_fte_flag(p_person_id IN PA_EMPLOYEES.PERSON_ID%TYPE,
774 p_future_term_wf_flag IN PA_RESOURCES.FUTURE_TERM_WF_FLAG%TYPE,
775 x_return_status OUT NOCOPY VARCHAR2,
776 x_msg_data OUT NOCOPY VARCHAR2,
777 x_msg_count OUT NOCOPY NUMBER);
778 --
779 -- PROCEDURE
780 -- get_fte_flag
781 -- PURPOSE
782 -- This procedure gets the new future_term_wf_flag
783 -- in table pa_resources for the passed person_id
784 -- HISTORY
785 -- 05-MAR-207 kjai Created for Bug 5683340
786 --
787 PROCEDURE Get_fte_flag(p_person_id IN PA_EMPLOYEES.PERSON_ID%TYPE,
788 x_future_term_wf_flag OUT NOCOPY PA_RESOURCES.FUTURE_TERM_WF_FLAG%TYPE,
789 x_return_status OUT NOCOPY VARCHAR2,
790 x_msg_data OUT NOCOPY VARCHAR2,
791 x_msg_count OUT NOCOPY NUMBER);
792
793 --
794 -- PROCEDURE
795 -- is_fte
796 -- PURPOSE
797 -- This procedure checks whether the person is an FTE, as of sysdate.
798 -- If he is, then returns the actual term date , wait days.
799 -- HISTORY
800 -- 05-MAR-207 kjai Created for Bug 5683340
801 --
802 PROCEDURE Is_fte( p_person_id IN PA_EMPLOYEES.PERSON_ID%TYPE ,
803 x_return_end_date OUT NOCOPY DATE ,
804 x_wait_days OUT NOCOPY NUMBER ,
805 x_invol_term OUT NOCOPY VARCHAR2,
806 x_return_status OUT NOCOPY VARCHAR2,
807 x_msg_data OUT NOCOPY VARCHAR2,
808 x_msg_count OUT NOCOPY NUMBER);
809
810 --
811 -- PROCEDURE
812 -- get_valid_enddate
813 -- PURPOSE
814 -- This procedure returns a valid end date if person is an FTE(as of sysdate)
815 --
816 -- HISTORY
817 -- 05-MAR-207 kjai Created for Bug 5683340
818 --
819 PROCEDURE get_valid_enddate(p_person_id IN PA_EMPLOYEES.PERSON_ID%TYPE ,
820 p_actual_term_date IN DATE ,
821 x_valid_end_date OUT NOCOPY DATE,
822 x_return_status OUT NOCOPY VARCHAR2,
823 x_msg_data OUT NOCOPY VARCHAR2,
824 x_msg_count OUT NOCOPY NUMBER);
825
826 --
827 -- PROCEDURE
828 -- is_term_as_of_sys_date
829 -- PURPOSE
830 -- This procedure checks whether the employee / cwk
831 -- is terminated as of sysdate
832 -- HISTORY
833 -- 05-MAR-207 kjai Created for Bug 5683340
834 --
835 PROCEDURE is_term_as_of_sys_date( itemtype IN VARCHAR2
836 , itemkey IN VARCHAR2
837 , actid IN NUMBER
838 , funcmode IN VARCHAR2
839 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
840 );
841
842
843 END pa_resource_utils ;
[Home] [Help]