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