[Home] [Help]
PACKAGE BODY: APPS.PA_RES_LIST_MEMBERS_PKG
Source
1 PACKAGE BODY Pa_Res_List_Members_Pkg AS
2 /* $Header: PAPRESTB.pls 120.3 2012/01/11 12:03:53 djambhek ship $*/
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.LOG_ID;
10
11 /*******************************************************
12 * Procedure : Insert_Row
13 * Description : This procedure is used to take in the parameters
14 * passed from pa_planning_resource_pub.create_planning_resource
15 * Procedure and Insert into pa_resource_list_members
16 * table.
17 ***********************************************************/
18 PROCEDURE Insert_Row
19 ( p_resource_list_member_id IN
20 pa_resource_list_members.resource_list_member_id%TYPE,
21 p_resource_list_id IN pa_resource_list_members.resource_list_id%TYPE,
22 p_resource_id IN pa_resource_list_members.resource_id%TYPE,
23 p_resource_alias IN pa_resource_list_members.alias%TYPE,
24 p_person_id IN pa_resource_list_members.person_id%TYPE,
25 p_job_id IN pa_resource_list_members.job_id%TYPE ,
26 p_organization_id IN pa_resource_list_members.organization_id%TYPE ,
27 p_vendor_id IN pa_resource_list_members.vendor_id%TYPE ,
28 p_expenditure_type IN pa_resource_list_members.expenditure_type%TYPE ,
29 p_event_type IN pa_resource_list_members.event_type%TYPE ,
30 p_non_labor_resource IN
31 pa_resource_list_members.non_labor_resource%TYPE,
32 p_expenditure_category IN
33 pa_resource_list_members.expenditure_category%TYPE,
34 p_revenue_category IN pa_resource_list_members.revenue_category%TYPE ,
35 p_role_id IN
36 pa_resource_list_members.project_role_id%TYPE ,
37 p_resource_class_id IN pa_resource_list_members.resource_class_id%TYPE ,
38 p_res_class_code IN
39 pa_resource_list_members.resource_class_code%TYPE,
40 p_res_format_id IN NUMBER ,
41 p_spread_curve_id IN pa_resource_list_members.spread_curve_id%TYPE ,
42 p_etc_method_code IN pa_resource_list_members.etc_method_code%TYPE ,
43 p_mfc_cost_type_id IN pa_resource_list_members.mfc_cost_type_id%TYPE ,
44 p_res_class_flag IN
45 pa_resource_list_members.resource_class_flag%TYPE ,
46 p_fc_res_type_code IN pa_resource_list_members.fc_res_type_code%TYPE ,
47 p_inventory_item_id IN pa_resource_list_members.inventory_item_id%TYPE ,
48 p_item_category_id IN pa_resource_list_members.item_category_id%TYPE,
49 p_attribute_category IN pa_resource_list_members.attribute_category%TYPE,
50 p_attribute1 IN pa_resource_list_members.attribute1%TYPE,
51 p_attribute2 IN pa_resource_list_members.attribute2%TYPE,
52 p_attribute3 IN pa_resource_list_members.attribute3%TYPE,
53 p_attribute4 IN pa_resource_list_members.attribute4%TYPE,
54 p_attribute5 IN pa_resource_list_members.attribute5%TYPE,
55 p_attribute6 IN pa_resource_list_members.attribute6%TYPE,
56 p_attribute7 IN pa_resource_list_members.attribute7%TYPE,
57 p_attribute8 IN pa_resource_list_members.attribute8%TYPE,
58 p_attribute9 IN pa_resource_list_members.attribute9%TYPE,
59 p_attribute10 IN pa_resource_list_members.attribute10%TYPE,
60 p_attribute11 IN pa_resource_list_members.attribute11%TYPE,
61 p_attribute12 IN pa_resource_list_members.attribute12%TYPE,
62 p_attribute13 IN pa_resource_list_members.attribute13%TYPE,
63 p_attribute14 IN pa_resource_list_members.attribute14%TYPE,
64 p_attribute15 IN pa_resource_list_members.attribute15%TYPE,
65 p_attribute16 IN pa_resource_list_members.attribute16%TYPE,
66 p_attribute17 IN pa_resource_list_members.attribute17%TYPE,
67 p_attribute18 IN pa_resource_list_members.attribute18%TYPE,
68 p_attribute19 IN pa_resource_list_members.attribute19%TYPE,
69 p_attribute20 IN pa_resource_list_members.attribute20%TYPE,
70 p_attribute21 IN pa_resource_list_members.attribute21%TYPE,
71 p_attribute22 IN pa_resource_list_members.attribute22%TYPE,
72 p_attribute23 IN pa_resource_list_members.attribute23%TYPE,
73 p_attribute24 IN pa_resource_list_members.attribute24%TYPE,
74 p_attribute25 IN pa_resource_list_members.attribute25%TYPE,
75 p_attribute26 IN pa_resource_list_members.attribute26%TYPE,
76 p_attribute27 IN pa_resource_list_members.attribute27%TYPE,
77 p_attribute28 IN pa_resource_list_members.attribute28%TYPE,
78 p_attribute29 IN pa_resource_list_members.attribute29%TYPE,
79 p_attribute30 IN pa_resource_list_members.attribute30%TYPE,
80 p_person_type_code IN pa_resource_list_members.person_type_code%TYPE,
81 p_bom_resource_id IN pa_resource_list_members.bom_resource_id%TYPE,
82 p_team_role IN pa_resource_list_members.team_role%TYPE,
83 p_incur_by_res_flag IN
84 pa_resource_list_members.incurred_by_res_flag%TYPE,
85 p_incur_by_res_class_code IN
86 pa_resource_list_members.incur_by_res_class_code%TYPE,
87 p_incur_by_role_id IN pa_resource_list_members.incur_by_role_id%TYPE,
88 p_object_type IN pa_resource_list_members.object_type%TYPE,
89 p_object_id IN pa_resource_list_members.object_id%TYPE,
90 p_wp_eligible_flag IN pa_resource_list_members.wp_eligible_flag%TYPE,
91 p_unit_of_measure IN pa_resource_list_members.unit_of_measure%TYPE,
92 p_expenditure_type_2 IN VARCHAR2 DEFAULT NULL, -- Added for Bug13535688 & 13546557 for CBS project
93 x_msg_count IN OUT NOCOPY NUMBER,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_error_msg_data OUT NOCOPY VARCHAR2 )
96 IS
97 l_resource_list_id pa_resource_list_members.resource_list_id%type;--Added for Bug13535688 & 13546557 for CBS project
98 BEGIN
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100
101 INSERT INTO PA_RESOURCE_LIST_MEMBERS(
102 RESOURCE_LIST_MEMBER_ID,
103 RESOURCE_LIST_ID,
104 RESOURCE_ID,
105 ALIAS,
106 DISPLAY_FLAG,
107 ENABLED_FLAG,
108 TRACK_AS_LABOR_FLAG,
109 PERSON_ID,
110 JOB_ID,
111 ORGANIZATION_ID,
112 VENDOR_ID,
113 EXPENDITURE_TYPE,
114 EVENT_TYPE,
115 NON_LABOR_RESOURCE,
116 EXPENDITURE_CATEGORY,
117 REVENUE_CATEGORY,
118 PROJECT_ROLE_ID,
119 OBJECT_TYPE,
120 OBJECT_ID,
121 RESOURCE_CLASS_ID,
122 RESOURCE_CLASS_CODE,
123 RES_FORMAT_ID,
124 SPREAD_CURVE_ID,
125 ETC_METHOD_CODE,
126 MFC_COST_TYPE_ID,
127 COPY_FROM_RL_FLAG,
128 RESOURCE_CLASS_FLAG,
129 FC_RES_TYPE_CODE,
130 INVENTORY_ITEM_ID,
131 ITEM_CATEGORY_ID,
132 MIGRATION_CODE,
133 ATTRIBUTE_CATEGORY,
134 ATTRIBUTE1,
135 ATTRIBUTE2,
136 ATTRIBUTE3 ,
137 ATTRIBUTE4 ,
138 ATTRIBUTE5 ,
139 ATTRIBUTE6 ,
140 ATTRIBUTE7 ,
141 ATTRIBUTE8 ,
142 ATTRIBUTE9 ,
143 ATTRIBUTE10 ,
144 ATTRIBUTE11 ,
145 ATTRIBUTE12 ,
146 ATTRIBUTE13 ,
147 ATTRIBUTE14 ,
148 ATTRIBUTE15 ,
149 ATTRIBUTE16 ,
150 ATTRIBUTE17 ,
151 ATTRIBUTE18 ,
152 ATTRIBUTE19 ,
153 ATTRIBUTE20 ,
154 ATTRIBUTE21 ,
155 ATTRIBUTE22 ,
156 ATTRIBUTE23 ,
157 ATTRIBUTE24 ,
158 ATTRIBUTE25 ,
159 ATTRIBUTE26 ,
160 ATTRIBUTE27 ,
161 ATTRIBUTE28 ,
162 ATTRIBUTE29 ,
163 ATTRIBUTE30 ,
164 RECORD_VERSION_NUMBER,
165 PERSON_TYPE_CODE,
166 BOM_RESOURCE_ID,
167 TEAM_ROLE,
168 INCURRED_BY_RES_FLAG,
169 INCUR_BY_RES_CLASS_CODE,
170 INCUR_BY_ROLE_ID,
171 WP_ELIGIBLE_FLAG,
172 UNIT_OF_MEASURE,
173 LAST_UPDATED_BY,
174 LAST_UPDATE_DATE,
175 CREATION_DATE,
176 CREATED_BY,
177 LAST_UPDATE_LOGIN,
178 EXPENDITURE_TYPE_2) --Added for Bug13535688 13546557 for CBS project
179 VALUES
180 (p_resource_list_member_id,
181 p_resource_list_id,
182 nvl(p_resource_id, -99),
183 p_resource_alias,
184 'Y',
185 'Y',
186 NULL,
187 p_person_id,
188 p_job_id ,
189 p_organization_id ,
190 p_vendor_id ,
191 p_expenditure_type ,
192 p_event_type ,
193 p_non_labor_resource ,
194 p_expenditure_category ,
195 p_revenue_category ,
196 p_role_id ,
197 p_object_type ,
198 p_object_id ,
199 p_resource_class_id ,
200 p_res_class_code ,
201 p_res_format_id ,
202 p_spread_curve_id ,
203 p_etc_method_code ,
204 p_mfc_cost_type_id ,
205 'N' ,
206 p_res_class_flag ,
207 p_fc_res_type_code ,
208 p_inventory_item_id ,
209 p_item_category_id ,
210 'N' ,
211 p_attribute_category ,
212 p_attribute1 ,
213 p_attribute2 ,
214 p_attribute3 ,
215 p_attribute4 ,
216 p_attribute5 ,
217 p_attribute6 ,
218 p_attribute7 ,
219 p_attribute8 ,
220 p_attribute9 ,
221 p_attribute10 ,
222 p_attribute11 ,
223 p_attribute12 ,
224 p_attribute13 ,
225 p_attribute14 ,
226 p_attribute15 ,
227 p_attribute16 ,
228 p_attribute17 ,
229 p_attribute18 ,
230 p_attribute19 ,
231 p_attribute20 ,
232 p_attribute21 ,
233 p_attribute22 ,
234 p_attribute23 ,
235 p_attribute24 ,
236 p_attribute25 ,
237 p_attribute26 ,
238 p_attribute27 ,
239 p_attribute28 ,
240 p_attribute29 ,
241 p_attribute30 ,
242 1,
243 p_person_type_code,
244 p_bom_resource_id,
245 p_team_role,
246 nvl(p_incur_by_res_flag, 'N'),
247 p_incur_by_res_class_code,
248 p_incur_by_role_id,
249 p_wp_eligible_flag,
250 p_unit_of_measure,
251 FND_GLOBAL.USER_ID,
252 Sysdate,
253 Sysdate,
254 FND_GLOBAL.USER_ID,
255 FND_GLOBAL.LOGIN_ID,
256 p_expenditure_type_2); --Added for Bug13535688 13546557 for CBS project
257
258 /*
259 -- Added for Bug13535688 13546557 for CBS project,
260 Below update is req to ensure both PRL screen and resource screen Expenditure type are same
261 */
262 IF p_resource_alias = 'Project Resource' Then
263 SELECT resource_list_id
264 INTO l_resource_list_id
265 FROM pa_resource_list_members
266 WHERE resource_list_member_id = p_resource_list_member_id;
267
268 UPDATE PA_RESOURCE_LISTS_ALL_BG
269 SET default_expenditure_type = p_expenditure_type_2 ,
270 last_update_date = sysdate
271 WHERE resource_list_id =l_resource_list_id;
272 END IF;
273
274 EXCEPTION
275 WHEN OTHERS THEN
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
277 x_msg_count := x_msg_count + 1;
278 RETURN;
279 END Insert_Row;
280 /*************************************************/
281
282 /*******************************************************
283 * Procedure : Update_Row
284 * Description : This procedure is used to take in the parameters
285 * passed from pa_planning_resource_pub.update_planning_resource
286 * Procedure and Update the pa_resource_list_members
287 * table.
288 ***********************************************************/
289 PROCEDURE Update_Row
290 (p_alias IN VARCHAR2,
291 p_enabled_flag IN VARCHAR2,
292 p_resource_list_member_id IN
293 pa_resource_list_members.resource_list_member_id%TYPE,
294 p_spread_curve_id IN pa_resource_list_members.spread_curve_id%TYPE,
295 p_etc_method_code IN pa_resource_list_members.etc_method_code%TYPE,
296 p_mfc_cost_type_id IN pa_resource_list_members.MFC_COST_TYPE_ID%TYPE ,
297 p_attribute_category IN pa_resource_list_members.attribute_category%TYPE,
298 p_attribute1 IN pa_resource_list_members.attribute1%TYPE,
299 p_attribute2 IN pa_resource_list_members.attribute2%TYPE,
300 p_attribute3 IN pa_resource_list_members.attribute3%TYPE,
301 p_attribute4 IN pa_resource_list_members.attribute4%TYPE,
302 p_attribute5 IN pa_resource_list_members.attribute5%TYPE,
303 p_attribute6 IN pa_resource_list_members.attribute6%TYPE,
304 p_attribute7 IN pa_resource_list_members.attribute7%TYPE,
305 p_attribute8 IN pa_resource_list_members.attribute8%TYPE,
306 p_attribute9 IN pa_resource_list_members.attribute9%TYPE,
307 p_attribute10 IN pa_resource_list_members.attribute10%TYPE,
308 p_attribute11 IN pa_resource_list_members.attribute11%TYPE,
309 p_attribute12 IN pa_resource_list_members.attribute12%TYPE,
310 p_attribute13 IN pa_resource_list_members.attribute13%TYPE,
311 p_attribute14 IN pa_resource_list_members.attribute14%TYPE,
312 p_attribute15 IN pa_resource_list_members.attribute15%TYPE,
313 p_attribute16 IN pa_resource_list_members.attribute16%TYPE,
314 p_attribute17 IN pa_resource_list_members.attribute17%TYPE,
315 p_attribute18 IN pa_resource_list_members.attribute18%TYPE,
316 p_attribute19 IN pa_resource_list_members.attribute19%TYPE,
317 p_attribute20 IN pa_resource_list_members.attribute20%TYPE,
318 p_attribute21 IN pa_resource_list_members.attribute21%TYPE,
319 p_attribute22 IN pa_resource_list_members.attribute22%TYPE,
320 p_attribute23 IN pa_resource_list_members.attribute23%TYPE,
321 p_attribute24 IN pa_resource_list_members.attribute24%TYPE,
322 p_attribute25 IN pa_resource_list_members.attribute25%TYPE,
323 p_attribute26 IN pa_resource_list_members.attribute26%TYPE,
324 p_attribute27 IN pa_resource_list_members.attribute27%TYPE,
325 p_attribute28 IN pa_resource_list_members.attribute28%TYPE,
326 p_attribute29 IN pa_resource_list_members.attribute29%TYPE,
327 p_attribute30 IN pa_resource_list_members.attribute30%TYPE,
328 p_record_version_number IN
329 pa_resource_list_members.RECORD_VERSION_NUMBER%TYPE,
330 p_expenditure_type_2 IN VARCHAR2 DEFAULT NULL, -- Added for Bug13535688 13546557 for CBS project
331 x_msg_count IN OUT NOCOPY NUMBER,
332 x_return_status OUT NOCOPY VARCHAR2,
333 x_error_msg_data OUT NOCOPY VARCHAR2)
334 IS
335 l_mfc_cost_type_id Number;
336 l_resource_list_id pa_resource_list_members.resource_list_id%type; --Added for Bug13535688 13546557 for CBS project
337 BEGIN
338 x_return_status := FND_API.G_RET_STS_SUCCESS;
339 /*****************************************************************
340 * Bug - 3571205
341 * Desc - For MFC Cost Type ID, We need to do an extra chedck to
342 * determine if the user has explicitely Nulled out the
343 * field or not. This can be determined by checking the
344 * FND_API.G_MISS_NUM field. If the user wants to explicitely
345 * Null out the field then he should pass FND_API.G_MISS_NUM
346 * to the p_mfc_cost_type_id parameter.
347 *****************************************************************/
348 IF p_mfc_cost_type_id IS NOT NULL AND p_mfc_cost_type_id <> FND_API.G_MISS_NUM THEN
349 l_mfc_cost_type_id := p_mfc_cost_type_id;
350 END IF;
351
352 IF p_mfc_cost_type_id IS NULL THEN
353 BEGIN
354 SELECT mfc_cost_type_id
355 INTO l_mfc_cost_type_id
359 WHEN OTHERS THEN
356 FROM pa_resource_list_members
357 WHERE resource_list_member_id = p_resource_list_member_id;
358 EXCEPTION
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361 Return;
362 END;
363 END IF;
364
365 IF p_mfc_cost_type_id = FND_API.G_MISS_NUM THEN
366 l_mfc_cost_type_id := Null;
367 END IF;
368 /************************************************************
369 * Bug : 3494646
370 * Description : When a user disables a planning resource, the
371 * attributes should not be removed.
372 * Therefore if the values for most of the attributes
373 * like spread_curve_id, etc_method_code,
374 * mfc_cost_type_id, attribute_category,
375 * attribute1 etc are passed in as Null then we just
376 * default it with the value that was originally present
377 * in the DB.
378 *****************************************************************/
379 UPDATE PA_RESOURCE_LIST_MEMBERS
380 SET alias = p_alias,
381 enabled_flag = p_enabled_flag,
382 spread_curve_id = nvl(p_spread_curve_id,spread_curve_id),
383 etc_method_code = nvl(p_etc_method_code,etc_method_code),
384 mfc_cost_type_id = l_mfc_cost_type_id,
385 attribute_category = nvl(p_attribute_category,attribute_category),
386 attribute1 = p_attribute1,
387 attribute2 = p_attribute2,
388 attribute3 = p_attribute3,
389 attribute4 = p_attribute4,
390 attribute5 = p_attribute5,
391 attribute6 = p_attribute6,
392 attribute7 = p_attribute7,
393 attribute8 = p_attribute8,
394 attribute9 = p_attribute9,
395 attribute10 = p_attribute10,
396 attribute11 = p_attribute11,
397 attribute12 = p_attribute12,
398 attribute13 = p_attribute13,
399 attribute14 = p_attribute14,
400 attribute15 = p_attribute15,
401 attribute16 = p_attribute16,
402 attribute17 = p_attribute17,
403 attribute18 = p_attribute18,
404 attribute19 = p_attribute19,
405 attribute20 = p_attribute20,
406 attribute21 = p_attribute21,
407 attribute22 = p_attribute22,
408 attribute23 = p_attribute23,
409 attribute24 = p_attribute24,
410 attribute25 = p_attribute25,
411 attribute26 = p_attribute26,
412 attribute27 = p_attribute27,
413 attribute28 = p_attribute28,
414 attribute29 = p_attribute29,
415 attribute30 = p_attribute30,
416 record_version_number = nvl(RECORD_VERSION_NUMBER,0) + 1,
417 last_update_date = sysdate ,
418 expenditure_type_2 = p_expenditure_type_2 --Added for Bug13535688 13546557 for CBS project
419 WHERE resource_list_member_id =
420 p_resource_list_member_id
421 AND nvl(record_version_number, 0) =
422 nvl(p_record_version_number, 0);
423
424 IF (SQL%NOTFOUND) THEN
425 /************************************************
426 * If we couldn't find a matching record for Updation.
427 ***************************************************/
428 PA_UTILS.Add_message(p_app_short_name => 'PA'
429 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
430 x_msg_count :=
431 x_msg_count + 1;
432 x_return_status :=
433 FND_API.G_RET_STS_ERROR;
434 x_error_msg_data :=
435 'PA_XC_RECORD_CHANGED';
436 RETURN;
437 END IF;
438 /*
439 Added for Bug13535688 13546557 for CBS project,
440 Below update is req to ensure both PRL screen and resource screen Expenditure type are same
441 */
442 IF p_alias = 'Project Resource' Then
443 SELECT resource_list_id
444 INTO l_resource_list_id
445 FROM pa_resource_list_members
446 WHERE resource_list_member_id = p_resource_list_member_id;
447
448 UPDATE PA_RESOURCE_LISTS_ALL_BG
449 SET default_expenditure_type = p_expenditure_type_2 ,
450 last_update_date = sysdate
451 WHERE resource_list_id =l_resource_list_id;
452 END IF;
453
454 END Update_Row;
455 /********************************************/
456
457 /*******************************************************
458 * Procedure : Delete_Row
459 * Description : This procedure is used to take in the parameters
460 * passed from pa_planning_resource_pub.delete_planning_resource
461 * Procedure and delete from the pa_resource_list_members
462 * table.
463 ***********************************************************/
464 PROCEDURE Delete_Row
465 (p_resource_list_member_id IN VARCHAR2,
466 p_exist_res_list IN VARCHAR2,
467 x_msg_count IN OUT NOCOPY VARCHAR2,
468 x_return_status OUT NOCOPY VARCHAR2)
469 IS
470 BEGIN
471 x_return_status := FND_API.G_RET_STS_SUCCESS;
472 IF p_exist_res_list = 'Y' THEN
473 /*************************************************
474 * If 'Y' is returned from the above select, then
475 * we cannot Delete the resource list member. So
476 * we are just disabling it by setting the enabled flag = 'N'.
477 **************************************************/
478 BEGIN
479 UPDATE pa_resource_list_members
480 SET enabled_flag = 'N',
484 EXCEPTION
481 last_update_date = sysdate,
482 record_version_number = nvl(record_version_number,0) + 1
483 WHERE resource_list_member_id = p_resource_list_member_id;
485 WHEN OTHERS THEN
486 FND_MSG_PUB.add_exc_msg( p_pkg_name =>
487 'pa_create_resource_pub.delete_planning_resource'
488 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
489 x_msg_count := x_msg_count+1;
490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
491 END;
492 ELSE
493 /****************************************************
494 * If 'N' is returned from the above select. Then it means no
495 * CHILD recs existing, so we can go ahead and delete from
496 * pa_resource_list_members table.
497 ********************************************************/
498 BEGIN
499 DELETE FROM pa_resource_list_members
500 WHERE resource_list_member_id = p_resource_list_member_id;
501 EXCEPTION
502 WHEN OTHERS THEN
503 FND_MSG_PUB.add_exc_msg( p_pkg_name =>
504 'pa_create_resource_pub.delete_planning_resource'
505 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
506 x_msg_count := x_msg_count+1;
507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
508 END;
509 END IF;
510
511 END Delete_Row;
512 /***************************/
513
514 END Pa_Res_List_Members_Pkg;
515 /**************************************/