1 PACKAGE PA_PLANNING_RESOURCE_UTILS AS
2 /* $Header: PARPRLUS.pls 120.2 2006/02/22 16:28:46 ramurthy noship $*/
3
4 -- Standard who
5 g_last_updated_by NUMBER(15) := FND_GLOBAL.USER_ID;
6 g_last_update_date DATE := SYSDATE;
7 g_creation_date DATE := SYSDATE;
8 g_created_by NUMBER(15) := FND_GLOBAL.USER_ID;
9 g_last_update_login NUMBER(15) := FND_GLOBAL.LOGIN_ID;
10
11 FUNCTION chk_spread_curve_in_use(p_spread_curve_id IN NUMBER) return BOOLEAN;
12
13 FUNCTION chk_nlr_resource_exists(p_non_labor_resource IN Varchar2) return BOOLEAN;
14
15 /*************************************************************
16 * Function : get_Res_member_code
17 * **********************************************************/
18 FUNCTION Get_res_member_code(p_resource_list_member_id IN NUMBER)
19 return VARCHAR2;
20 /*************************************************************
21 * Function : get_member_fin_cat_code
22 * **********************************************************/
23 FUNCTION Get_member_fin_cat_code(p_resource_list_member_id IN NUMBER)
24 return VARCHAR2;
25 /*************************************************************
26 * Function : get_member_incur_by_res_code
27 * **********************************************************/
28 FUNCTION Get_member_incur_by_res_code(p_resource_list_member_id IN NUMBER)
29 return VARCHAR2;
30
31 /**************************************************************
32 * FUNCTION : Get_res_type_code
33 * ************************************************************/
34 FUNCTION Get_res_type_code(p_res_format_id IN NUMBER)
35 RETURN VARCHAR2;
36
37 /****************************************************************
38 * Purpose of Get_Resource_Code Function is to return the resource code
39 * for a given resource assignment.
40 * ***************************************************************/
41 FUNCTION Get_resource_Code(p_resource_assignment_id IN NUMBER) return VARCHAR2;
42
43
44 /***************************************************************************
45 * Purpose of Get_Incur_By_Res_Code Function is to return the Incur
46 * by resource code for a given resource assignment.
47 * **************************************************************/
48 FUNCTION Get_Incur_By_Res_Code(p_resource_assignment_id IN NUMBER) return VARCHAR2;
49
50 /*********************************************************************
51 * Purpose of Get_Fin_Category_Code Function is to return the Financial
52 * category code for a given resource assignment.
53 * *******************************************************************/
54 FUNCTION Get_Fin_Category_Code(p_resource_assignment_id IN NUMBER) return VARCHAR2;
55
56
57 /******************************************************************
58 * Purpose of the Validate_Organization procedure is to validate
59 * organization for the planning resource.
60 * **********************************************************/
61 PROCEDURE Validate_organization(
62 p_organization_name IN VARCHAR2,
63 p_organization_id IN NUMBER,
64 x_organization_id OUT NOCOPY NUMBER,
65 x_return_status OUT NOCOPY VARCHAR2,
66 x_error_msg_code OUT NOCOPY VARCHAR2);
67
68
69 /************************************************************************
70 * Procedure : Check_SupplierName_Or_Id
71 * Description : This Subprog validates the supplier name
72 * and ID combination
73 *********************************************************************/
74 PROCEDURE Check_SupplierName_Or_Id
75 ( p_supplier_id IN NUMBER
76 ,p_supplier_name IN VARCHAR2
77 ,p_check_id_flag IN VARCHAR2
78 ,x_supplier_id OUT NOCOPY NUMBER
79 ,x_return_status OUT NOCOPY VARCHAR2
80 ,x_error_msg_code OUT NOCOPY VARCHAR2 );
81
82 /****************************************************************
83 * The purpose of the Validate_Supplier procedure is to validate the
84 * supplier
85 *****************************************************************/
86 PROCEDURE Validate_Supplier(
87 p_resource_class_code IN VARCHAR2,
88 p_person_id IN NUMBER,
89 p_supplier_id IN NUMBER DEFAULT NULL,
90 p_supplier_name IN VARCHAR2 DEFAULT NULL,
91 x_supplier_id OUT NOCOPY NUMBER,
92 x_return_status OUT NOCOPY VARCHAR2,
93 x_error_msg_code OUT NOCOPY VARCHAR2);
94 /*********************************************************
95 * Procedure : Check_PersonName_or_ID
96 * Description : Used to validate the Person ID
97 * Name combination.
98 ********************************************************/
99 PROCEDURE Check_PersonName_or_ID(
100 p_person_id IN VARCHAR2,
101 p_person_name IN VARCHAR2,
102 p_check_id_flag IN VARCHAR2,
103 x_person_id OUT NOCOPY NUMBER,
104 x_return_status OUT NOCOPY VARCHAR2,
105 x_error_msg_code OUT NOCOPY VARCHAR2);
106
107 /****************************************************
108 * Procedure : Check_JobName_or_ID
109 * Description : Used to validate the Job ID
110 * Name combination.
111 ****************************************************/
112 PROCEDURE Check_JobName_or_ID(
113 p_job_id IN VARCHAR2,
114 p_job_name IN VARCHAR2,
115 p_check_id_flag IN VARCHAR2,
116 x_job_id OUT NOCOPY NUMBER,
117 x_return_status OUT NOCOPY VARCHAR2,
118 x_error_msg_code OUT NOCOPY VARCHAR2);
119 /******************************************************
120 * Procedure : Check_BOM_EqLabor_or_ID
121 * Description : Used to validate the BOM ID
122 * Name combination.
123 *******************************************************/
124 PROCEDURE Check_BOM_EqLabor_or_ID
125 ( p_bom_eqlabor_id IN NUMBER
126 , p_bom_eqlabor_name IN VARCHAR2
127 , p_res_type_code IN VARCHAR2
128 , p_check_id_flag IN VARCHAR2
129 , x_bom_resource_id OUT NOCOPY NUMBER
130 , x_return_status OUT NOCOPY VARCHAR2
131 , x_error_msg_code OUT NOCOPY VARCHAR2 );
132
133 /******************************************************
134 * Procedure : Check_ItemCat_or_ID
135 * Description : Used to validate the Item Category ID
136 * Category name combination.
137 *******************************************************/
138 PROCEDURE Check_ItemCat_or_ID
139 ( p_item_cat_id IN NUMBER
140 ,p_item_cat_name IN VARCHAR2
141 , P_item_category_set_id IN NUMBER
142 , p_check_id_flag IN VARCHAR2
143 , x_item_category_id OUT NOCOPY NUMBER
144 , x_return_status OUT NOCOPY VARCHAR2
145 , x_error_msg_code OUT NOCOPY VARCHAR2 );
146
147 /******************************************************
148 * Procedure : Check_InventoryItem_or_ID
149 * Description : Used to validate the Inventory Item ID
150 * Inv Item name combination.
151 *******************************************************/
152 PROCEDURE Check_InventoryItem_or_ID
153 ( p_item_id IN NUMBER
154 ,p_item_name IN VARCHAR2
155 , P_item_master_id IN NUMBER
156 , p_check_id_flag IN VARCHAR2
157 , x_item_id OUT NOCOPY NUMBER
158 , x_return_status OUT NOCOPY VARCHAR2
159 , x_error_msg_code OUT NOCOPY VARCHAR2 );
160 /*****************************************************
161 * procedure : Validate_resource
162 * *************************************************/
163 PROCEDURE Validate_Resource(
164 p_resource_code IN VARCHAR2 DEFAULT NULL,
165 p_resource_name IN VARCHAR2 DEFAULT NULL,
166 p_resource_class_code IN VARCHAR2,
167 p_res_type_code IN VARCHAR2 DEFAULT NULL,
168 x_person_id OUT NOCOPY NUMBER,
169 x_bom_resource_id OUT NOCOPY NUMBER,
170 x_job_id OUT NOCOPY NUMBER,
171 x_person_type_code OUT NOCOPY VARCHAR2,
172 x_non_labor_resource OUT NOCOPY VARCHAR2,
173 x_inventory_item_id OUT NOCOPY NUMBER,
174 x_item_category_id OUT NOCOPY NUMBER,
175 x_resource_class_code OUT NOCOPY VARCHAR2,
176 x_resource_class_flag OUT NOCOPY VARCHAR2,
177 x_return_status OUT NOCOPY VARCHAR2,
178 x_error_msg_code OUT NOCOPY VARCHAR2);
179
180 /*******************************************************
181 * Procedure : Default_expenditure_type
182 ********************************************************/
183 PROCEDURE Default_expenditure_type;
184
185 /*******************************************************
186 * Procedure : Default_rate_expenditure_type
187 ********************************************************/
188 PROCEDURE Default_rate_expenditure_type;
189
190 /*******************************************************
191 * Procedure : Default_Supplier
192 * ****************************************************/
193 PROCEDURE Default_Supplier;
194
195 /*********************************************************
196 * PROCEDURE : default_job
197 *******************************************************/
198 PROCEDURE Default_job;
199
200 /**********************************************************
201 * Procedure : Default_Organization
202 *********************************************************/
203 PROCEDURE Default_Organization(p_project_id IN PA_PROJECTS_ALL.PROJECT_ID%TYPE);
204
205 /*******************************************************************
206 * Procedure : Default_rate_based
207 * *****************************************************************/
208 PROCEDURE Default_rate_based;
209
210 /*******************************************************************
211 * Procedure : Default_ou
212 ******************************************************************/
213 PROCEDURE Default_ou(p_project_id IN PA_PROJECTS_ALL.PROJECT_ID%TYPE);
214
215 /*******************************************************************
216 * Procedure : Default_currency_code
217 ******************************************************************/
218 PROCEDURE Default_currency_code;
219
220 /*******************************************************************
221 * Procedure : Default_UOM
222 ******************************************************************/
223 PROCEDURE Default_UOM;
224
225 /********************************************************************
226 * Procedure : get_resource_defaults
227 * *****************************************************************/
228 PROCEDURE get_resource_defaults (
229 P_resource_list_members IN SYSTEM.PA_NUM_TBL_TYPE,
230 P_project_id IN PA_PROJECTS_ALL.PROJECT_ID%TYPE,
231 X_resource_class_flag OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE,
232 X_resource_class_code OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
233 X_resource_class_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
234 X_res_type_code OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
235 X_incur_by_res_type OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
236 X_person_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
237 X_job_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
238 X_person_type_code OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
239 X_named_role OUT NOCOPY SYSTEM.PA_VARCHAR2_80_TBL_TYPE,
240 X_bom_resource_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
241 X_non_labor_resource OUT NOCOPY SYSTEM.PA_VARCHAR2_20_TBL_TYPE,
245 X_organization_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
242 X_inventory_item_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
243 X_item_category_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
244 X_project_role_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
246 X_fc_res_type_code OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
247 X_expenditure_type OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
248 X_expenditure_category OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
249 X_event_type OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
250 X_revenue_category_code OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
251 X_supplier_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
252 X_spread_curve_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
253 X_etc_method_code OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
254 X_mfc_cost_type_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
255 X_incurred_by_res_flag OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE,
256 X_incur_by_res_class_code OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
257 X_incur_by_role_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
258 X_unit_of_measure OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
259 X_org_id OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
260 X_rate_based_flag OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE,
261 X_rate_expenditure_type OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
262 X_rate_func_curr_code OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
263 X_msg_data OUT NOCOPY VARCHAR2,
264 X_msg_count OUT NOCOPY NUMBER,
265 X_return_status OUT NOCOPY VARCHAR2);
266
267 --Eugenes proc and Funcs
268 /*****************************************************************
269 * Type (Procedure or Procedure): Function
270 * Package Object Name : Ret_Resource_Name
271 * Purpose : Return the resource name from a given
272 * resource type code and id.
273 * Public or Private API?
274 * Public
275 ******************************************************************/
276 Function Ret_Resource_Name ( P_Res_Type_Code IN Varchar2,
277 P_Person_Id IN Number,
278 P_Bom_Resource_Id IN Number,
279 P_Job_Id IN Number,
280 P_Person_Type_Code IN Varchar2,
281 P_Non_Labor_Resource IN Varchar2,
282 P_Inventory_Item_Id IN Number,
283 P_Resource_Class_Id IN Number,
284 P_Item_Category_Id IN Number,
285 p_res_assignment_id IN NUMBER DEFAULT NULL)
286 Return VARCHAR2;
287
288 /**********************************************************************
289 *Type (Procedure or Procedure): Procedure
290 *Package Object Name: Get_Resource_Name
291 *Purpose: Return the resource name from a given
292 * resource type code/id.
293 *Public or Private API? Public
294 *Note: The parameter P_Proc_Func_Flag indicates where
295 * this procedure is being called from a procedure
296 * or a function. If called from a function then no data
297 * found will be supressed and the id/code is null will not get
298 * and raise exception and returns null for displayed parameter. All
299 * other errors will be raise;
300 * If called form a procedure then
301 * raise exception on no data found and when needed id/code is null.
302 ************************************************************/
303 Procedure Get_Resource_Name ( P_Res_Type_Code IN Varchar2,
304 P_Person_Id IN Number,
305 P_Bom_Resource_Id IN Number,
306 P_Job_Id IN Number,
307 P_Person_Type_Code IN Varchar2,
308 P_Non_Labor_Resource IN Varchar2,
309 P_Inventory_Item_Id IN Number,
310 P_Item_Category_Id IN Number,
311 P_Resource_Class_Id IN Number,
312 P_Proc_Func_Flag IN Varchar2,
313 p_res_assignment_id IN NUMBER DEFAULT NULL,
314 X_Resource_Displayed OUT NOCOPY Varchar2,
315 X_Return_Status OUT NOCOPY Varchar2,
316 X_Msg_Data OUT NOCOPY Varchar2
317 );
318
319 /***********************************************************************
320 *Type (Procedure or Procedure): Function
321 *Package Object Name: Ret_Fin_Category_Name
322 *Purpose: Return the resource name from a given
323 * resource type code.
324 *Public or Private API?
325 * Public
326 ***************************************************************/
327 Function Ret_Fin_Category_Name ( P_FC_Res_Type_Code IN Varchar2,
328 P_Expenditure_Type IN Varchar2,
329 P_Expenditure_Category IN Varchar2,
330 P_Event_Type IN Varchar2,
331 P_Revenue_Category_Code IN Varchar2,
332 p_res_assignment_id IN NUMBER default null
333 ) Return varchar2;
334
335 /******************************************************************
336 *Type (Procedure or Procedure): Procedure
337 *Package Object Name: Get_Fin_Category_Name
338 *Purpose: Return the resource name from a given
339 * resource type code.
340 ********************************************************************/
341 Procedure Get_Fin_Category_Name ( P_FC_Res_Type_Code IN Varchar2,
342 P_Expenditure_Type IN Varchar2,
343 P_Expenditure_Category IN Varchar2,
344 P_Event_Type IN Varchar2,
345 P_Revenue_Category_Code IN Varchar2,
346 P_Proc_Func_Flag IN Varchar2,
347 p_res_assignment_id IN NUMBER default null,
348 X_Fin_Cat_Displayed OUT NOCOPY Varchar2,
352
349 X_Return_Status OUT NOCOPY Varchar2,
350 X_Msg_Data OUT NOCOPY Varchar2
351 );
353 /***********************************************************************
354 * Function : ret_Organization_Name
355 * *******************************************************************/
356 Function Ret_Organization_Name ( P_Organization_Id IN Number ) Return Varchar2;
357
358 /******************************************************************
359 * Procedure : Get_Organization_Name
360 * ************************************************************/
361 Procedure Get_Organization_Name ( P_Organization_Id IN Number,
362 P_Proc_Func_Flag IN Varchar2,
363 X_Org_Displayed OUT NOCOPY Varchar2,
364 X_Return_Status OUT NOCOPY Varchar2,
365 X_Msg_Data OUT NOCOPY Varchar2 );
366
367 /*****************************************************************
368 * Function : Ret_Supplier_Name
369 * **************************************************************/
370 Function Ret_Supplier_Name ( P_Supplier_Id IN Number ) Return Varchar2;
371
372 /**************************************************************
373 * Procedure : Get_Supplier_Name
374 * **********************************************************/
375 Procedure Get_Supplier_Name ( P_Supplier_Id IN Number,
376 P_Proc_Func_Flag IN Varchar2,
377 X_Supplier_Displayed OUT NOCOPY Varchar2,
378 X_Return_Status OUT NOCOPY Varchar2,
379 X_Msg_Data OUT NOCOPY Varchar2 );
380 /****************************************************************
381 * Function : Ret_role_name
382 * *************************************************************/
383 Function Ret_Role_Name ( P_Role_Id IN Number ) Return Varchar2;
384
385 /*************************************************************
386 * Procedure : Get_Role_Name
387 * ***********************************************************/
388 Procedure Get_Role_Name ( P_Role_Id IN Number,
389 P_Proc_Func_Flag IN Varchar2,
390 X_Role_Displayed OUT NOCOPY Varchar2,
391 X_Return_Status OUT NOCOPY Varchar2,
392 X_Msg_Data OUT NOCOPY Varchar2 );
393
394 /************************************************************
395 * Function : Ret_Incur_By_Res_Name
396 * *********************************************************/
397 Function Ret_Incur_By_Res_Name ( P_Person_Id IN Number,
398 P_Job_Id IN Number,
399 P_Incur_By_Role_Id IN Number,
400 P_Person_Type_Code IN Varchar2,
401 P_Inc_By_Res_Class_Code IN Varchar2,
402 p_res_assignment_id IN NUMBER default null)
403 Return varchar2;
404
405 /*************************************************************
406 * Procedure : Get_Incur_By_Res_Name
407 * ***********************************************************/
408 Procedure Get_Incur_By_Res_Name ( P_Person_Id IN Number,
409 P_Job_Id IN Number,
410 P_Incur_By_Role_Id IN Number,
411 P_Person_Type_Code IN Varchar2,
412 P_Inc_By_Res_Class_Code IN Varchar2,
413 P_Proc_Func_Flag IN Varchar2,
414 p_res_assignment_id IN NUMBER default null,
415 X_Inc_By_Displayed OUT NOCOPY Varchar2,
416 X_Return_Status OUT NOCOPY Varchar2,
417 X_Msg_Data OUT NOCOPY Varchar2
418 );
419
420
421 /************************************************************
422 * Procedure : Get_Plan_Res_Combination
423 * *********************************************************/
424 Procedure Get_Plan_Res_Combination(
425 P_Resource_List_Member_Id IN Number,
426 X_Resource_Alias OUT NOCOPY Varchar2,
427 X_Plan_Res_Combination OUT NOCOPY Varchar2,
428 X_Return_Status OUT NOCOPY Varchar2,
429 X_Msg_Count OUT NOCOPY Number,
430 X_Msg_Data OUT NOCOPY Varchar2);
431
432 /*****************************************************************
433 * Function : Get_plan_res_combination
434 * ************************************************************/
435 FUNCTION Get_plan_res_combination( p_resource_list_member_id IN NUMBER) return
436 VARCHAR2;
437
438 /**************************************************************
439 * Procedure : Validate_fin_category
440 * ************************************************************/
441 Procedure Validate_Fin_Category(
442 P_FC_Res_Type_Code IN Varchar2,
443 P_Resource_Class_Code IN Varchar2,
444 P_Fin_Category_Name IN Varchar2,
445 P_migration_code IN Varchar2,
446 X_Expenditure_Type OUT NOCOPY Varchar2,
447 x_Expenditure_Category OUT NOCOPY Varchar2,
448 X_Event_Type OUT NOCOPY Varchar2,
449 X_Revenue_Category OUT NOCOPY Varchar2,
450 X_Return_Status OUT NOCOPY Varchar2,
451 X_Error_Message_Code OUT NOCOPY Varchar2);
452
453 /***************************************************************
454 * Procedure : Validate_Incur_by_resource
455 * ***********************************************************/
456 Procedure Validate_Incur_by_Resource(
457 P_Resource_Class_Code IN Varchar2,
458 P_Res_Type_Code IN Varchar2 Default Null,
459 P_Incur_By_Res_Code IN varchar2 Default Null,
460 X_Person_Id OUT NOCOPY Number,
461 X_Incur_By_Role_Id OUT NOCOPY Number,
462 X_Job_Id OUT NOCOPY Number,
463 X_Person_Type_Code OUT NOCOPY varchar2,
464 X_Incur_By_Res_Class_Code OUT NOCOPY varchar2,
465 X_Return_Status OUT NOCOPY Varchar2,
466 X_Error_Message_Code OUT NOCOPY Varchar2);
467
468 /**************************************************************
472 P_Task_Name IN VARCHAR2 Default Null,
469 * Procedure : Validate_Planning_Resource
470 * ************************************************************/
471 Procedure Validate_Planning_Resource(
473 P_Task_Number IN Varchar2 Default Null,
474 P_Planning_Resource_Alias IN Varchar2 Default Null,
475 P_Resource_List_Member_Id IN Number Default Null,
476 P_Resource_List_Id IN Number Default Null,
477 P_Res_Format_Id IN Number Default Null,
478 P_Resource_Class_Code IN Varchar2,
479 P_Res_Type_Code IN Varchar2 Default Null,
480 P_Resource_Code IN Varchar2 Default Null,
481 P_Resource_Name IN Varchar2 Default Null,
482 P_Project_Role_Id IN Number Default Null,
483 P_Project_Role_Name IN Varchar2 Default Null,
484 P_Team_role IN Varchar2 Default Null,
485 P_Organization_Id IN Number Default Null,
486 P_Organization_Name IN Varchar2 Default Null,
487 P_FC_Res_Type_Code IN Varchar2 Default Null,
488 P_Fin_Category_Name IN Varchar2 Default Null,
489 P_Supplier_Id IN Number Default Null,
490 P_Supplier_Name IN Varchar2 Default Null,
491 P_Incur_By_Resource_Code IN varchar2 Default Null,
492 P_Incur_By_resource_Type IN Varchar2 Default Null,
493 X_Resource_List_Member_Id OUT NOCOPY Number,
494 X_Person_Id OUT NOCOPY Number,
495 X_Bom_Resource_Id OUT NOCOPY Number,
496 X_Job_Id OUT NOCOPY Number,
497 X_Person_Type_Code OUT NOCOPY varchar2,
498 X_Non_Labor_Resource OUT NOCOPY varchar2,
499 X_Inventory_Item_Id OUT NOCOPY Number,
500 X_Item_Category_Id OUT NOCOPY Number,
501 X_Project_Role_Id OUT NOCOPY Number,
502 X_team_role OUT NOCOPY Varchar2,
503 X_Organization_Id OUT NOCOPY Number,
504 X_Expenditure_Type OUT NOCOPY Varchar2,
505 X_Expenditure_Category OUT NOCOPY Varchar2,
506 X_Event_Type OUT NOCOPY Varchar2,
507 X_Revenue_Category_Code OUT NOCOPY Varchar2,
508 X_Supplier_Id OUT NOCOPY Number,
509 X_Resource_Class_Id OUT NOCOPY Number,
510 X_resource_class_flag OUT NOCOPY varchar2,
511 X_Incur_By_Role_Id OUT NOCOPY Number,
512 X_Incur_By_res_Class_Code OUT NOCOPY varchar2,
513 X_Incur_By_Res_Flag OUT NOCOPY varchar2,
514 X_Return_Status OUT NOCOPY Varchar2,
515 X_Msg_Data OUT NOCOPY Varchar2,
516 X_Msg_Count OUT NOCOPY Number);
517
518
519 /* -----------------------------------------------------------------------
520 Type (Procedure or Procedure): Procedure
521 Package Object Name: Get_Resource_Cost_Rate
522 Purpose: Return the resource raw/burden rate and the
523 currency values.
524 Public or Private API? Public
525
526 ------------------------------------------------------------------------- */
527 Procedure Get_Resource_Cost_Rate
528 (P_eligible_rlm_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
529 ,P_project_id IN pa_projects_all.project_id%type
530 ,p_structure_type IN VARCHAR2
531 ,p_fin_plan_type_id IN NUMBER DEFAULT NULL
532 ,P_resource_curr_code_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_15_TBL_TYPE
533 ,P_resource_raw_rate_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
534 ,P_resource_burden_rate_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
535 ) ;
536
537 Function Derive_Resource_List_Member
538 (p_project_id IN NUMBER,
539 p_res_format_id IN NUMBER,
540 p_person_id IN NUMBER DEFAULT NULL,
541 p_job_id IN NUMBER DEFAULT NULL,
542 p_organization_id IN NUMBER DEFAULT NULL,
543 p_expenditure_type IN Varchar2 DEFAULT NULL,
544 p_expenditure_category IN Varchar2 DEFAULT NULL,
545 p_project_role_id IN Number DEFAULT NULL,
546 p_person_type_code IN Varchar2 DEFAULT NULL,
547 p_named_role IN Varchar2 DEFAULT NULL)
548 RETURN NUMBER;
549
550 Procedure Get_Res_Format_For_Team_Role
551 (p_resource_list_id IN NUMBER,
552 x_asgmt_res_format_id OUT NOCOPY NUMBER,
553 x_req_res_format_id OUT NOCOPY NUMBER,
554 x_return_status OUT NOCOPY Varchar2);
555
556
557 /* ----------------------------------------------------------------
558 * API for populating a resource list into the new TL tables. This API
559 * is called by the resource list upgrade concurrent program.
560 * ----------------------------------------------------------------*/
561 PROCEDURE Populate_list_into_tl(
562 p_resource_list_id IN NUMBER,
563 x_return_status OUT NOCOPY VARCHAR2,
564 x_msg_count OUT NOCOPY VARCHAR2,
565 x_msg_data OUT NOCOPY VARCHAR2 );
566
567 /*******************************************************************
568 * Procedure : Delete_proj_specific_resource
569 * Desc : This API is used to delete the project specific resources
570 * once the project is deleted.
571 *******************************************************************/
572 PROCEDURE Delete_Proj_Specific_Resource(
573 p_project_id IN NUMBER,
574 x_return_status OUT NOCOPY VARCHAR2,
575 x_msg_count OUT NOCOPY NUMBER);
576
577 /*****************************************************************
578 * Function : Get_class_member_id
579 * Given a resource list, project and resource class, this function
580 * returns the class level resource list member for that class, for
581 * both centrally and non-centrally controlled resource lists.
582 * ************************************************************/
583 FUNCTION Get_class_member_id(p_project_id IN NUMBER,
584 p_resource_list_id IN NUMBER,
585 p_resource_class_code IN VARCHAR2) return NUMBER;
586
587 /*****************************************************************
588 * Function : get_rate_based_flag
589 * Given a resource list member, this function returns a flag
590 * to indicate whether the list member is rate based or not.
591 * ************************************************************/
592 FUNCTION Get_rate_based_flag(p_resource_list_member_id IN NUMBER) return VARCHAR2;
593
594 /*****************************************************************
595 * Function : check_enable_allowed
596 * Given a disabled resource list member, this function checks to see if
597 * enabling it is allowed - meaning that it won't result in a duplicate
598 * resource list member. Hence it checks to see if there are any enabled
599 * list members with the same format and attributes. Returns Y if the
600 * given list member is unique; and N if not.
601 * ************************************************************/
602
603 FUNCTION check_enable_allowed(p_resource_list_member_id IN NUMBER)
604 return VARCHAR2;
605
606 /*****************************************************************
607 * Procedure : check_list_member_on_list
608 * Given a resource list member and a resource list, this procedure checks
609 * to see if the list member is on the list (looking at the project specific
610 * case as well) and returns an error message if it isn't.
611 * If p_chk_enabled is passed in as 'Y', an additional check is done
612 * to see whether the list member is enabled or not.
613 * ************************************************************/
614 PROCEDURE check_list_member_on_list(
615 p_resource_list_id IN NUMBER,
616 p_resource_list_member_id IN NUMBER,
617 p_project_id IN NUMBER,
618 p_chk_enabled IN VARCHAR2 DEFAULT 'N',
619 p_alias IN VARCHAR2 DEFAULT NULL,
620 x_resource_list_member_id OUT NOCOPY NUMBER,
621 x_valid_member_flag OUT NOCOPY VARCHAR2,
622 x_return_status OUT NOCOPY VARCHAR2,
623 x_msg_count OUT NOCOPY VARCHAR2,
624 x_msg_data OUT NOCOPY VARCHAR2 );
625
626 /********************************************************************
627 * Procedure : default_other_elements
628 * This procedure derives other segments for a planning resource, based
629 * on the resource format, if it is possible to derive anything. For
630 * example, if the format provided is Named Person - Organization, and
631 * the person ID or name is specified, the person's HR Organization is derived
632 * and passed back as X_organization_id and X_organization_name. Please
633 * see the functional design for all the values that can be derived.
634 * *****************************************************************/
635 PROCEDURE default_other_elements (
636 P_res_format_id IN NUMBER,
637 P_person_id IN NUMBER DEFAULT NULL,
638 P_person_name IN VARCHAR2 DEFAULT NULL,
639 p_bom_resource_id IN NUMBER DEFAULT NULL,
640 p_bom_resource_name IN VARCHAR2 DEFAULT NULL,
641 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
642 X_organization_id OUT NOCOPY NUMBER,
643 x_organization_name OUT NOCOPY VARCHAR2,
644 X_expenditure_type OUT NOCOPY VARCHAR2,
645 X_msg_data OUT NOCOPY VARCHAR2,
646 X_msg_count OUT NOCOPY NUMBER,
647 X_return_status OUT NOCOPY VARCHAR2);
648
649 END PA_PLANNING_RESOURCE_UTILS;
[Home] [Help]