[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.0 2005/06/03 13:52:05 appldev 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.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 x_msg_count IN OUT NOCOPY NUMBER,
93 x_return_status OUT NOCOPY VARCHAR2,
94 x_error_msg_data OUT NOCOPY VARCHAR2 )
95 IS
96 BEGIN
97 x_return_status := FND_API.G_RET_STS_SUCCESS;
98
99 INSERT INTO PA_RESOURCE_LIST_MEMBERS(
100 RESOURCE_LIST_MEMBER_ID,
101 RESOURCE_LIST_ID,
102 RESOURCE_ID,
103 ALIAS,
104 DISPLAY_FLAG,
105 ENABLED_FLAG,
106 TRACK_AS_LABOR_FLAG,
107 PERSON_ID,
108 JOB_ID,
109 ORGANIZATION_ID,
110 VENDOR_ID,
111 EXPENDITURE_TYPE,
112 EVENT_TYPE,
113 NON_LABOR_RESOURCE,
114 EXPENDITURE_CATEGORY,
115 REVENUE_CATEGORY,
116 PROJECT_ROLE_ID,
117 OBJECT_TYPE,
118 OBJECT_ID,
119 RESOURCE_CLASS_ID,
120 RESOURCE_CLASS_CODE,
121 RES_FORMAT_ID,
122 SPREAD_CURVE_ID,
123 ETC_METHOD_CODE,
124 MFC_COST_TYPE_ID,
125 COPY_FROM_RL_FLAG,
126 RESOURCE_CLASS_FLAG,
127 FC_RES_TYPE_CODE,
128 INVENTORY_ITEM_ID,
129 ITEM_CATEGORY_ID,
130 MIGRATION_CODE,
131 ATTRIBUTE_CATEGORY,
132 ATTRIBUTE1,
133 ATTRIBUTE2,
134 ATTRIBUTE3 ,
135 ATTRIBUTE4 ,
136 ATTRIBUTE5 ,
137 ATTRIBUTE6 ,
138 ATTRIBUTE7 ,
139 ATTRIBUTE8 ,
140 ATTRIBUTE9 ,
141 ATTRIBUTE10 ,
142 ATTRIBUTE11 ,
143 ATTRIBUTE12 ,
144 ATTRIBUTE13 ,
145 ATTRIBUTE14 ,
146 ATTRIBUTE15 ,
147 ATTRIBUTE16 ,
148 ATTRIBUTE17 ,
149 ATTRIBUTE18 ,
150 ATTRIBUTE19 ,
151 ATTRIBUTE20 ,
152 ATTRIBUTE21 ,
153 ATTRIBUTE22 ,
154 ATTRIBUTE23 ,
155 ATTRIBUTE24 ,
156 ATTRIBUTE25 ,
157 ATTRIBUTE26 ,
158 ATTRIBUTE27 ,
159 ATTRIBUTE28 ,
160 ATTRIBUTE29 ,
161 ATTRIBUTE30 ,
162 RECORD_VERSION_NUMBER,
163 PERSON_TYPE_CODE,
164 BOM_RESOURCE_ID,
165 TEAM_ROLE,
166 INCURRED_BY_RES_FLAG,
167 INCUR_BY_RES_CLASS_CODE,
168 INCUR_BY_ROLE_ID,
169 WP_ELIGIBLE_FLAG,
170 UNIT_OF_MEASURE,
171 LAST_UPDATED_BY,
172 LAST_UPDATE_DATE,
173 CREATION_DATE,
174 CREATED_BY,
175 LAST_UPDATE_LOGIN)
176 VALUES
177 (p_resource_list_member_id,
178 p_resource_list_id,
179 nvl(p_resource_id, -99),
180 p_resource_alias,
181 'Y',
182 'Y',
183 NULL,
184 p_person_id,
185 p_job_id ,
186 p_organization_id ,
187 p_vendor_id ,
188 p_expenditure_type ,
189 p_event_type ,
190 p_non_labor_resource ,
191 p_expenditure_category ,
192 p_revenue_category ,
193 p_role_id ,
194 p_object_type ,
195 p_object_id ,
196 p_resource_class_id ,
197 p_res_class_code ,
198 p_res_format_id ,
199 p_spread_curve_id ,
200 p_etc_method_code ,
201 p_mfc_cost_type_id ,
202 'N' ,
203 p_res_class_flag ,
204 p_fc_res_type_code ,
205 p_inventory_item_id ,
206 p_item_category_id ,
207 'N' ,
208 p_attribute_category ,
209 p_attribute1 ,
210 p_attribute2 ,
211 p_attribute3 ,
212 p_attribute4 ,
213 p_attribute5 ,
214 p_attribute6 ,
215 p_attribute7 ,
216 p_attribute8 ,
217 p_attribute9 ,
218 p_attribute10 ,
219 p_attribute11 ,
220 p_attribute12 ,
221 p_attribute13 ,
222 p_attribute14 ,
223 p_attribute15 ,
224 p_attribute16 ,
225 p_attribute17 ,
226 p_attribute18 ,
227 p_attribute19 ,
228 p_attribute20 ,
229 p_attribute21 ,
230 p_attribute22 ,
231 p_attribute23 ,
232 p_attribute24 ,
233 p_attribute25 ,
234 p_attribute26 ,
235 p_attribute27 ,
236 p_attribute28 ,
237 p_attribute29 ,
238 p_attribute30 ,
239 1,
240 p_person_type_code,
241 p_bom_resource_id,
242 p_team_role,
243 nvl(p_incur_by_res_flag, 'N'),
244 p_incur_by_res_class_code,
245 p_incur_by_role_id,
246 p_wp_eligible_flag,
247 p_unit_of_measure,
248 FND_GLOBAL.USER_ID,
249 Sysdate,
250 Sysdate,
251 FND_GLOBAL.USER_ID,
252 FND_GLOBAL.LOGIN_ID);
253 EXCEPTION
254 WHEN OTHERS THEN
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
256 x_msg_count := x_msg_count + 1;
257 RETURN;
258 END Insert_Row;
259 /*************************************************/
260
261 /*******************************************************
262 * Procedure : Update_Row
263 * Description : This procedure is used to take in the parameters
264 * passed from pa_planning_resource_pub.update_planning_resource
265 * Procedure and Update the pa_resource_list_members
266 * table.
267 ***********************************************************/
268 PROCEDURE Update_Row
269 (p_alias IN VARCHAR2,
270 p_enabled_flag IN VARCHAR2,
271 p_resource_list_member_id IN
272 pa_resource_list_members.resource_list_member_id%TYPE,
273 p_spread_curve_id IN pa_resource_list_members.spread_curve_id%TYPE,
274 p_etc_method_code IN pa_resource_list_members.etc_method_code%TYPE,
275 p_mfc_cost_type_id IN pa_resource_list_members.MFC_COST_TYPE_ID%TYPE ,
276 p_attribute_category IN pa_resource_list_members.attribute_category%TYPE,
277 p_attribute1 IN pa_resource_list_members.attribute1%TYPE,
278 p_attribute2 IN pa_resource_list_members.attribute2%TYPE,
279 p_attribute3 IN pa_resource_list_members.attribute3%TYPE,
280 p_attribute4 IN pa_resource_list_members.attribute4%TYPE,
281 p_attribute5 IN pa_resource_list_members.attribute5%TYPE,
282 p_attribute6 IN pa_resource_list_members.attribute6%TYPE,
283 p_attribute7 IN pa_resource_list_members.attribute7%TYPE,
284 p_attribute8 IN pa_resource_list_members.attribute8%TYPE,
285 p_attribute9 IN pa_resource_list_members.attribute9%TYPE,
286 p_attribute10 IN pa_resource_list_members.attribute10%TYPE,
287 p_attribute11 IN pa_resource_list_members.attribute11%TYPE,
288 p_attribute12 IN pa_resource_list_members.attribute12%TYPE,
289 p_attribute13 IN pa_resource_list_members.attribute13%TYPE,
290 p_attribute14 IN pa_resource_list_members.attribute14%TYPE,
291 p_attribute15 IN pa_resource_list_members.attribute15%TYPE,
292 p_attribute16 IN pa_resource_list_members.attribute16%TYPE,
293 p_attribute17 IN pa_resource_list_members.attribute17%TYPE,
294 p_attribute18 IN pa_resource_list_members.attribute18%TYPE,
295 p_attribute19 IN pa_resource_list_members.attribute19%TYPE,
296 p_attribute20 IN pa_resource_list_members.attribute20%TYPE,
297 p_attribute21 IN pa_resource_list_members.attribute21%TYPE,
298 p_attribute22 IN pa_resource_list_members.attribute22%TYPE,
299 p_attribute23 IN pa_resource_list_members.attribute23%TYPE,
300 p_attribute24 IN pa_resource_list_members.attribute24%TYPE,
301 p_attribute25 IN pa_resource_list_members.attribute25%TYPE,
302 p_attribute26 IN pa_resource_list_members.attribute26%TYPE,
303 p_attribute27 IN pa_resource_list_members.attribute27%TYPE,
304 p_attribute28 IN pa_resource_list_members.attribute28%TYPE,
305 p_attribute29 IN pa_resource_list_members.attribute29%TYPE,
306 p_attribute30 IN pa_resource_list_members.attribute30%TYPE,
307 p_record_version_number IN
308 pa_resource_list_members.RECORD_VERSION_NUMBER%TYPE,
309 x_msg_count IN OUT NOCOPY NUMBER,
310 x_return_status OUT NOCOPY VARCHAR2,
311 x_error_msg_data OUT NOCOPY VARCHAR2)
312 IS
313 l_mfc_cost_type_id Number;
314 BEGIN
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316 /*****************************************************************
317 * Bug - 3571205
318 * Desc - For MFC Cost Type ID, We need to do an extra chedck to
319 * determine if the user has explicitely Nulled out the
320 * field or not. This can be determined by checking the
321 * FND_API.G_MISS_NUM field. If the user wants to explicitely
322 * Null out the field then he should pass FND_API.G_MISS_NUM
323 * to the p_mfc_cost_type_id parameter.
324 *****************************************************************/
325 IF p_mfc_cost_type_id IS NOT NULL AND p_mfc_cost_type_id <> FND_API.G_MISS_NUM THEN
326 l_mfc_cost_type_id := p_mfc_cost_type_id;
327 END IF;
328
329 IF p_mfc_cost_type_id IS NULL THEN
330 BEGIN
331 SELECT mfc_cost_type_id
332 INTO l_mfc_cost_type_id
333 FROM pa_resource_list_members
334 WHERE resource_list_member_id = p_resource_list_member_id;
335 EXCEPTION
336 WHEN OTHERS THEN
337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
338 Return;
339 END;
340 END IF;
341
342 IF p_mfc_cost_type_id = FND_API.G_MISS_NUM THEN
343 l_mfc_cost_type_id := Null;
344 END IF;
345 /************************************************************
346 * Bug : 3494646
347 * Description : When a user disables a planning resource, the
348 * attributes should not be removed.
352 * attribute1 etc are passed in as Null then we just
349 * Therefore if the values for most of the attributes
350 * like spread_curve_id, etc_method_code,
351 * mfc_cost_type_id, attribute_category,
353 * default it with the value that was originally present
354 * in the DB.
355 *****************************************************************/
356 UPDATE PA_RESOURCE_LIST_MEMBERS
357 SET alias = p_alias,
358 enabled_flag = p_enabled_flag,
359 spread_curve_id = nvl(p_spread_curve_id,spread_curve_id),
360 etc_method_code = nvl(p_etc_method_code,etc_method_code),
361 mfc_cost_type_id = l_mfc_cost_type_id,
362 attribute_category = nvl(p_attribute_category,attribute_category),
363 attribute1 = nvl(p_attribute1,attribute1),
364 attribute2 = nvl(p_attribute2,attribute2),
365 attribute3 = nvl(p_attribute3,attribute3),
366 attribute4 = nvl(p_attribute4,attribute4),
367 attribute5 = nvl(p_attribute5,attribute5),
368 attribute6 = nvl(p_attribute6,attribute6),
369 attribute7 = nvl(p_attribute7,attribute7),
370 attribute8 = nvl(p_attribute8,attribute8),
371 attribute9 = nvl(p_attribute9,attribute9),
372 attribute10 = nvl(p_attribute10,attribute10),
373 attribute11 = nvl(p_attribute11,attribute11),
374 attribute12 = nvl(p_attribute12,attribute12),
375 attribute13 = nvl(p_attribute13,attribute13),
376 attribute14 = nvl(p_attribute14,attribute14),
377 attribute15 = nvl(p_attribute15,attribute15),
378 attribute16 = nvl(p_attribute16,attribute16),
379 attribute17 = nvl(p_attribute17,attribute17),
380 attribute18 = nvl(p_attribute18,attribute18),
381 attribute19 = nvl(p_attribute19,attribute19),
382 attribute20 = nvl(p_attribute20,attribute20),
383 attribute21 = nvl(p_attribute21,attribute21),
384 attribute22 = nvl(p_attribute22,attribute22),
385 attribute23 = nvl(p_attribute23,attribute23),
386 attribute24 = nvl(p_attribute24,attribute24),
387 attribute25 = nvl(p_attribute25,attribute25),
388 attribute26 = nvl(p_attribute26,attribute26),
389 attribute27 = nvl(p_attribute27,attribute27),
390 attribute28 = nvl(p_attribute28,attribute28),
391 attribute29 = nvl(p_attribute29,attribute29),
392 attribute30 = nvl(p_attribute30,attribute30),
393 record_version_number = nvl(RECORD_VERSION_NUMBER,0) + 1,
394 last_update_date = sysdate
395 WHERE resource_list_member_id =
396 p_resource_list_member_id
397 AND nvl(record_version_number, 0) =
398 nvl(p_record_version_number, 0);
399
400 IF (SQL%NOTFOUND) THEN
401 /************************************************
402 * If we couldn't find a matching record for Updation.
403 ***************************************************/
404 PA_UTILS.Add_message(p_app_short_name => 'PA'
405 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
406 x_msg_count :=
407 x_msg_count + 1;
408 x_return_status :=
409 FND_API.G_RET_STS_ERROR;
410 x_error_msg_data :=
411 'PA_XC_RECORD_CHANGED';
412 RETURN;
413 END IF;
414 END Update_Row;
415 /********************************************/
416
417 /*******************************************************
418 * Procedure : Delete_Row
419 * Description : This procedure is used to take in the parameters
420 * passed from pa_planning_resource_pub.delete_planning_resource
421 * Procedure and delete from the pa_resource_list_members
422 * table.
423 ***********************************************************/
424 PROCEDURE Delete_Row
425 (p_resource_list_member_id IN VARCHAR2,
426 p_exist_res_list IN VARCHAR2,
427 x_msg_count IN OUT NOCOPY VARCHAR2,
428 x_return_status OUT NOCOPY VARCHAR2)
429 IS
430 BEGIN
431 x_return_status := FND_API.G_RET_STS_SUCCESS;
432 IF p_exist_res_list = 'Y' THEN
433 /*************************************************
434 * If 'Y' is returned from the above select, then
435 * we cannot Delete the resource list member. So
436 * we are just disabling it by setting the enabled flag = 'N'.
437 **************************************************/
438 BEGIN
439 UPDATE pa_resource_list_members
440 SET enabled_flag = 'N',
441 last_update_date = sysdate,
442 record_version_number = nvl(record_version_number,0) + 1
443 WHERE resource_list_member_id = p_resource_list_member_id;
444 EXCEPTION
445 WHEN OTHERS THEN
446 FND_MSG_PUB.add_exc_msg( p_pkg_name =>
447 'pa_create_resource_pub.delete_planning_resource'
448 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
449 x_msg_count := x_msg_count+1;
450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
451 END;
452 ELSE
453 /****************************************************
454 * If 'N' is returned from the above select. Then it means no
455 * CHILD recs existing, so we can go ahead and delete from
456 * pa_resource_list_members table.
457 ********************************************************/
458 BEGIN
459 DELETE FROM pa_resource_list_members
460 WHERE resource_list_member_id = p_resource_list_member_id;
461 EXCEPTION
462 WHEN OTHERS THEN
463 FND_MSG_PUB.add_exc_msg( p_pkg_name =>
464 'pa_create_resource_pub.delete_planning_resource'
465 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
466 x_msg_count := x_msg_count+1;
467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
468 END;
469 END IF;
470
471 END Delete_Row;
472 /***************************/
473
474 END Pa_Res_List_Members_Pkg;
475 /**************************************/