[Home] [Help]
PACKAGE BODY: APPS.PA_RESOURCE_LIST_PKG
Source
1 PACKAGE BODY PA_Resource_List_Pkg AS
2 /* $Header: PARELITB.pls 120.1 2005/08/19 16:50:13 mwasowic noship $ */
3 -- Standard Table Handler procedures for PA_RESOURCE_LISTS table
4
5 PROCEDURE Insert_parent_row (X_ROW_ID IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
6 X_RESOURCE_LIST_MEMBER_ID NUMBER,
7 X_RESOURCE_LIST_ID NUMBER,
8 X_RESOURCE_ID NUMBER,
9 X_ALIAS VARCHAR2,
10 X_SORT_ORDER NUMBER,
11 X_MEMBER_LEVEL NUMBER,
12 X_DISPLAY_FLAG VARCHAR2,
13 X_ENABLED_FLAG VARCHAR2,
14 X_TRACK_AS_LABOR_FLAG VARCHAR2,
15 X_LAST_UPDATED_BY NUMBER,
16 X_LAST_UPDATE_DATE DATE,
17 X_CREATION_DATE DATE,
18 X_CREATED_BY NUMBER,
19 X_LAST_UPDATE_LOGIN NUMBER,
20 X_Funds_Control_Level_Code VARCHAR2,
21 p_migration_code VARCHAR2
22 ) IS
23 CURSOR PARENT_RES_CUR IS
24 Select
25 Rowid
26 from
27 PA_RESOURCE_LIST_MEMBERS
28 Where Resource_List_Member_Id = X_Resource_List_Member_Id;
29
30 -- Added for bug 1889671
31
32 l_person_id pa_resource_txn_attributes.person_id%TYPE;
33 l_job_id pa_resource_txn_attributes.job_id%TYPE;
34 l_organization_id pa_resource_txn_attributes.organization_id%TYPE;
35 l_vendor_id pa_resource_txn_attributes.vendor_id%TYPE;
36 l_project_role_id pa_resource_txn_attributes.project_role_id%TYPE;
37 l_expenditure_type pa_resource_txn_attributes.expenditure_type%TYPE;
38 l_event_type pa_resource_txn_attributes.event_type%TYPE;
39 l_expenditure_category pa_resource_txn_attributes.expenditure_category%TYPE;
40 l_revenue_category pa_resource_txn_attributes.revenue_category%TYPE;
41 l_nlr_resource pa_resource_txn_attributes.non_labor_resource%TYPE;
42 l_nlr_res_org_id pa_resource_txn_attributes.non_labor_resource_org_id%TYPE;
43 l_event_type_cls pa_resource_txn_attributes.event_type_classification%TYPE;
44 l_system_link_function pa_resource_txn_attributes.system_linkage_function%TYPE;
45 l_resource_format_id pa_resource_txn_attributes.resource_format_id%TYPE;
46 l_resource_type_id pa_resource_types.resource_type_id%TYPE;
47 l_res_type_code pa_resource_types.resource_type_code%TYPE;
48
49 BEGIN
50
51 /* Added for bug 1889671. This will fetch 13 txn attributed from pa_resource_txn_attributes table
52 along with resource_format_id. Also,resoure_type_id and resource_type_code is also fetched.*/
53
54 SELECT prta.person_id,
55 prta.job_id,
56 prta.organization_id,
57 prta.vendor_id,
58 prta.project_role_id,
59 prta.expenditure_type,
60 prta.event_type,
61 prta.expenditure_category,
62 prta.revenue_category,
63 prta.non_labor_resource,
64 prta.non_labor_resource_org_id,
65 prta.event_type_classification,
66 prta.system_linkage_function,
67 prta.resource_format_id,
68 prt.resource_type_id,
69 prt.resource_type_code
70 INTO l_person_id,
71 l_job_id,
72 l_organization_id,
73 l_vendor_id,
74 l_project_role_id,
75 l_expenditure_type,
76 l_event_type,
77 l_expenditure_category,
78 l_revenue_category,
79 l_nlr_resource,
80 l_nlr_res_org_id,
81 l_event_type_cls,
82 l_system_link_function,
83 l_resource_format_id,
84 l_resource_type_id,
85 l_res_type_code
86 FROM PA_RESOURCE_TXN_ATTRIBUTES PRTA,
87 PA_RESOURCES PR,
88 PA_RESOURCE_TYPES PRT
89 WHERE prta.resource_id = pr.resource_id
90 AND pr.resource_id =X_RESOURCE_ID
91 AND pr.resource_type_id= prt.resource_type_id;
92
93 /* As this select will not be used to insert unclassified resource, so no outer join is kept for
94 prta table */
95
96 Insert Into PA_RESOURCE_LIST_MEMBERS
97 (RESOURCE_LIST_MEMBER_ID,
98 RESOURCE_LIST_ID,
99 RESOURCE_ID ,
100 ALIAS ,
101 SORT_ORDER ,
102 MEMBER_LEVEL,
103 DISPLAY_FLAG ,
104 ENABLED_FLAG,
105 TRACK_AS_LABOR_FLAG,
106 LAST_UPDATED_BY,
107 LAST_UPDATE_DATE,
108 CREATION_DATE,
109 CREATED_BY,
110 LAST_UPDATE_LOGIN,
111 PARENT_MEMBER_ID,
112 Funds_Control_Level_Code,
113 PERSON_ID, /*16 newly added columns-bug 1889671*/
114 JOB_ID,
115 ORGANIZATION_ID,
116 VENDOR_ID,
117 PROJECT_ROLE_ID,
118 EXPENDITURE_TYPE,
119 EVENT_TYPE,
120 EXPENDITURE_CATEGORY,
121 REVENUE_CATEGORY,
122 NON_LABOR_RESOURCE,
123 NON_LABOR_RESOURCE_ORG_ID,
124 EVENT_TYPE_CLASSIFICATION,
125 SYSTEM_LINKAGE_FUNCTION,
126 RESOURCE_FORMAT_ID,
127 RESOURCE_TYPE_ID,
128 RESOURCE_TYPE_CODE,
129 migration_code
130 )
131 VALUES
132 (X_RESOURCE_LIST_MEMBER_ID,
133 X_RESOURCE_LIST_ID,
134 X_RESOURCE_ID,
135 X_ALIAS ,
136 X_SORT_ORDER ,
137 X_MEMBER_LEVEL,
138 X_DISPLAY_FLAG,
139 X_ENABLED_FLAG,
140 X_TRACK_AS_LABOR_FLAG,
141 X_LAST_UPDATED_BY,
142 X_LAST_UPDATE_DATE,
143 X_CREATION_DATE,
144 X_CREATED_BY,
145 X_LAST_UPDATE_LOGIN,
146 NULL,
147 X_Funds_Control_Level_Code,
148 l_person_id, /*16 newly added columns-bug 1889671*/
149 l_job_id,
150 l_organization_id,
151 l_vendor_id,
152 l_project_role_id,
153 l_expenditure_type,
154 l_event_type,
155 l_expenditure_category,
156 l_revenue_category,
157 l_nlr_resource,
158 l_nlr_res_org_id,
159 l_event_type_cls,
160 l_system_link_function,
161 l_resource_format_id,
162 l_resource_type_id,
163 l_res_type_code,
164 p_migration_code
165 );
166
167 Open Parent_Res_Cur;
168 Fetch Parent_Res_Cur Into X_Row_Id;
169 If (Parent_Res_Cur%NOTFOUND) then
170 Close Parent_Res_Cur;
171 Raise NO_DATA_FOUND;
172 End If;
173 Close Parent_Res_Cur;
174 /*Commenting the exception block for the bug 3355209 since 1)it is again standards
175 2) FND_MESSAGE.SET_NAME('PA' ,SQLERRM) is wrong which was returning no_data_found
176 Exception
177 When Others Then
178 FND_MESSAGE.SET_NAME('PA' ,SQLERRM);
179 APP_EXCEPTION.RAISE_EXCEPTION;*/
180 END Insert_Parent_Row;
181
182 PROCEDURE Update_Parent_Row (X_ROW_ID IN VARCHAR2,
183 X_RESOURCE_LIST_MEMBER_ID NUMBER,
184 X_ALIAS VARCHAR2,
185 X_SORT_ORDER NUMBER,
186 X_MEMBER_LEVEL NUMBER,
187 X_DISPLAY_FLAG VARCHAR2,
188 X_ENABLED_FLAG VARCHAR2,
189 X_TRACK_AS_LABOR_FLAG VARCHAR2,
190 X_LAST_UPDATED_BY NUMBER,
191 X_LAST_UPDATE_DATE DATE,
192 X_LAST_UPDATE_LOGIN NUMBER,
193 X_Funds_Control_Level_Code VARCHAR2,
194 p_migration_code VARCHAR2
195 ) IS
196 BEGIN
197
198 Update PA_RESOURCE_LIST_MEMBERS
199 SET
200 ALIAS = X_ALIAS,
201 SORT_ORDER = X_SORT_ORDER,
202 DISPLAY_FLAG = X_DISPLAY_FLAG,
203 ENABLED_FLAG = X_ENABLED_FLAG,
204 TRACK_AS_LABOR_FLAG = X_TRACK_AS_LABOR_FLAG,
205 MIGRATION_CODE = p_migration_code,
206 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
207 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
208 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
209 Funds_Control_Level_Code = X_Funds_Control_Level_Code
210 WHERE ROWID = X_ROW_ID;
211 If SQL%NOTFOUND Then
212 Raise NO_DATA_FOUND;
213 End If;
214 END Update_parent_row;
215
216 Procedure Lock_Parent_Row (X_ROW_ID IN VARCHAR2,
217 X_RESOURCE_LIST_MEMBER_ID NUMBER,
218 X_RESOURCE_ID NUMBER,
219 X_ALIAS VARCHAR2,
220 X_SORT_ORDER NUMBER,
221 X_DISPLAY_FLAG VARCHAR2,
222 X_ENABLED_FLAG VARCHAR2,
223 X_Funds_Control_Level_Code VARCHAR2,
224 p_migration_code VARCHAR2) IS
225 CURSOR C Is
226 Select * From PA_RESOURCE_LIST_MEMBERS WHERE ROWID = X_ROW_ID
227 For Update of RESOURCE_LIST_MEMBER_ID NOWAIT;
228 Recinfo C%ROWTYPE;
229 Begin
230 OPEN C;
231 FETCH C INTO Recinfo;
232 If (C%NOTFOUND) THEN
233 Close C;
234 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
235 APP_EXCEPTION.RAISE_EXCEPTION;
236 END If;
237 CLOSE C;
238 IF (
239 (X_RESOURCE_LIST_MEMBER_ID = Recinfo.RESOURCE_LIST_MEMBER_ID) And
240 (X_RESOURCE_ID = Recinfo.RESOURCE_ID) And
241 ((X_ALIAS = Recinfo.ALIAS) OR
242 (Recinfo.ALIAS Is Null)) AND
243 (nvl(p_migration_code, '-99') = nvl(Recinfo.migration_code, '-99')) AND
244 (X_SORT_ORDER = Recinfo.SORT_ORDER ) AND
245 (X_DISPLAY_FLAG = Recinfo.DISPLAY_FLAG ) AND
246 (X_ENABLED_FLAG = Recinfo.ENABLED_FLAG) AND
247 ( (Recinfo.Funds_Control_Level_Code = X_Funds_Control_Level_Code)
248 OR ( (Recinfo.Funds_Control_Level_Code IS NULL)
249 AND (X_Funds_Control_Level_Code IS NULL) )
250 )
251
252 ) Then
253 Return;
254 Else
255 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_CHANGED');
256 APP_EXCEPTION.RAISE_EXCEPTION;
257 END If;
258
259 End Lock_Parent_Row;
260
261 Procedure Delete_Parent_Row (X_ROW_ID IN VARCHAR2) Is
262 Begin
263 Delete from PA_RESOURCE_LIST_MEMBERS Where RowId = X_Row_Id;
264 If SQL%NOTFOUND Then
265 Raise NO_DATA_FOUND;
266 End If;
267
268 End Delete_Parent_Row;
269
270 PROCEDURE Insert_child_row (X_ROW_ID IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
271 X_RESOURCE_LIST_MEMBER_ID NUMBER,
272 X_RESOURCE_LIST_ID NUMBER,
273 X_RESOURCE_ID NUMBER,
274 X_ALIAS VARCHAR2,
275 X_PARENT_MEMBER_ID NUMBER,
276 X_SORT_ORDER NUMBER,
277 X_MEMBER_LEVEL NUMBER,
278 X_DISPLAY_FLAG VARCHAR2,
279 X_ENABLED_FLAG VARCHAR2,
280 X_TRACK_AS_LABOR_FLAG VARCHAR2,
281 X_LAST_UPDATED_BY NUMBER,
282 X_LAST_UPDATE_DATE DATE,
283 X_CREATION_DATE DATE,
284 X_CREATED_BY NUMBER,
285 X_LAST_UPDATE_LOGIN NUMBER,
286 X_Funds_Control_Level_Code VARCHAR2
287 ) IS
288
289 CURSOR CHILD_RES_CUR IS Select Rowid from PA_RESOURCE_LIST_MEMBERS
290 Where Resource_List_Member_Id = X_Resource_List_Member_Id;
291
292 -- Added for bug 1889671
293
294 l_person_id pa_resource_txn_attributes.person_id%TYPE;
295 l_job_id pa_resource_txn_attributes.job_id%TYPE;
296 l_organization_id pa_resource_txn_attributes.organization_id%TYPE;
297 l_vendor_id pa_resource_txn_attributes.vendor_id%TYPE;
298 l_project_role_id pa_resource_txn_attributes.project_role_id%TYPE;
299 l_expenditure_type pa_resource_txn_attributes.expenditure_type%TYPE;
300 l_event_type pa_resource_txn_attributes.event_type%TYPE;
301 l_expenditure_category pa_resource_txn_attributes.expenditure_category%TYPE;
302 l_revenue_category pa_resource_txn_attributes.revenue_category%TYPE;
306 l_system_link_function pa_resource_txn_attributes.system_linkage_function%TYPE;
303 l_nlr_resource pa_resource_txn_attributes.non_labor_resource%TYPE;
304 l_nlr_res_org_id pa_resource_txn_attributes.non_labor_resource_org_id%TYPE;
305 l_event_type_cls pa_resource_txn_attributes.event_type_classification%TYPE;
307 l_resource_format_id pa_resource_txn_attributes.resource_format_id%TYPE;
308 l_resource_type_id pa_resource_types.resource_type_id%TYPE;
309 l_res_type_code pa_resource_types.resource_type_code%TYPE;
310
311
312 BEGIN
313
314 /* Added for bug 1889671. This will fetch 13 txn attributed from pa_resource_txn_attributes table
315 along with resource_format_id. Also,resoure_type_id and resource_type_code is also fetched.*/
316
317 SELECT prta.person_id,
318 prta.job_id,
319 prta.organization_id,
320 prta.vendor_id,
321 prta.project_role_id,
322 prta.expenditure_type,
323 prta.event_type,
324 prta.expenditure_category,
325 prta.revenue_category,
326 prta.non_labor_resource,
327 prta.non_labor_resource_org_id,
328 prta.event_type_classification,
329 prta.system_linkage_function,
330 prta.resource_format_id,
331 prt.resource_type_id,
332 prt.resource_type_code
333
334 INTO l_person_id,
335 l_job_id,
336 l_organization_id,
337 l_vendor_id,
338 l_project_role_id,
339 l_expenditure_type,
340 l_event_type,
341 l_expenditure_category,
342 l_revenue_category,
343 l_nlr_resource,
344 l_nlr_res_org_id,
345 l_event_type_cls,
346 l_system_link_function,
347 l_resource_format_id,
348 l_resource_type_id,
349 l_res_type_code
350 FROM PA_RESOURCE_TXN_ATTRIBUTES PRTA,
351 PA_RESOURCES PR,
352 PA_RESOURCE_TYPES PRT
353 WHERE prta.resource_id(+) = pr.resource_id
354 AND pr.resource_id =X_RESOURCE_ID
355 AND pr.resource_type_id= prt.resource_type_id;
356
357 /* As this select will not be used to insert unclassified resource, so no outer join is kept for
358 prta table */
359
360 Insert Into PA_RESOURCE_LIST_MEMBERS
361 (RESOURCE_LIST_MEMBER_ID,
362 RESOURCE_LIST_ID,
363 RESOURCE_ID ,
364 ALIAS ,
365 PARENT_MEMBER_ID,
366 SORT_ORDER ,
367 MEMBER_LEVEL,
368 DISPLAY_FLAG ,
369 ENABLED_FLAG,
370 TRACK_AS_LABOR_FLAG,
371 LAST_UPDATED_BY,
372 LAST_UPDATE_DATE,
373 CREATION_DATE,
374 CREATED_BY,
375 LAST_UPDATE_LOGIN,
376 Funds_Control_Level_Code,
377 PERSON_ID, /*16 newly added columns-bug 1889671*/
378 JOB_ID,
379 ORGANIZATION_ID,
380 VENDOR_ID,
381 PROJECT_ROLE_ID,
382 EXPENDITURE_TYPE,
383 EVENT_TYPE,
384 EXPENDITURE_CATEGORY,
385 REVENUE_CATEGORY,
386 NON_LABOR_RESOURCE,
387 NON_LABOR_RESOURCE_ORG_ID,
388 EVENT_TYPE_CLASSIFICATION,
389 SYSTEM_LINKAGE_FUNCTION,
390 RESOURCE_FORMAT_ID,
391 RESOURCE_TYPE_ID,
392 RESOURCE_TYPE_CODE
393 )
394 VALUES
395 (X_RESOURCE_LIST_MEMBER_ID,
396 X_RESOURCE_LIST_ID,
397 X_RESOURCE_ID,
398 X_ALIAS ,
399 X_PARENT_MEMBER_ID,
400 X_SORT_ORDER ,
401 X_MEMBER_LEVEL,
402 X_DISPLAY_FLAG,
403 X_ENABLED_FLAG,
404 X_TRACK_AS_LABOR_FLAG,
405 X_LAST_UPDATED_BY,
406 X_LAST_UPDATE_DATE,
407 X_CREATION_DATE,
408 X_CREATED_BY,
409 X_LAST_UPDATE_LOGIN,
410 X_Funds_Control_Level_Code,
411 l_person_id, /*16 newly added columns-bug 1889671*/
412 l_job_id,
413 l_organization_id,
414 l_vendor_id,
415 l_project_role_id,
416 l_expenditure_type,
417 l_event_type,
418 l_expenditure_category,
419 l_revenue_category,
420 l_nlr_resource,
421 l_nlr_res_org_id,
422 l_event_type_cls,
423 l_system_link_function,
424 l_resource_format_id,
425 l_resource_type_id,
426 l_res_type_code
427 );
428
429 Open CHILD_Res_Cur;
430 Fetch CHILD_Res_Cur Into X_Row_Id;
434 End If;
431 If (CHILD_Res_Cur%NOTFOUND) then
432 Close Child_Res_Cur;
433 Raise NO_DATA_FOUND;
435 Close CHILD_Res_Cur;
436 /*Commenting the exception block for the bug 3355209 since 1)it is again standards
437 2) FND_MESSAGE.SET_NAME('PA' ,SQLERRM) is wrong which was returning no_data_found
438 Exception
439 When Others Then
440 FND_MESSAGE.SET_NAME('PA' ,SQLERRM);
441 APP_EXCEPTION.RAISE_EXCEPTION;*/
442 END Insert_CHILD_Row;
443
444 PROCEDURE Update_CHILD_Row (X_ROW_ID IN VARCHAR2,
445 X_RESOURCE_LIST_MEMBER_ID NUMBER,
446 X_ALIAS VARCHAR2,
447 X_SORT_ORDER NUMBER,
448 X_MEMBER_LEVEL NUMBER,
449 X_DISPLAY_FLAG VARCHAR2,
450 X_ENABLED_FLAG VARCHAR2,
451 X_TRACK_AS_LABOR_FLAG VARCHAR2,
452 X_LAST_UPDATED_BY NUMBER,
453 X_LAST_UPDATE_DATE DATE,
454 X_LAST_UPDATE_LOGIN NUMBER,
455 X_Funds_Control_Level_Code VARCHAR2
456 ) IS
457 BEGIN
458
459 Update PA_RESOURCE_LIST_MEMBERS
460 SET
461 ALIAS = X_ALIAS,
462 SORT_ORDER = X_SORT_ORDER,
463 DISPLAY_FLAG = X_DISPLAY_FLAG,
464 ENABLED_FLAG = X_ENABLED_FLAG,
465 TRACK_AS_LABOR_FLAG = X_TRACK_AS_LABOR_FLAG,
466 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
467 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
468 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
469 Funds_Control_Level_Code = X_Funds_Control_Level_Code
470 Where ROWID = X_ROW_ID;
471 If SQL%NOTFOUND Then
472 Raise NO_DATA_FOUND;
473 End If;
474 END Update_CHILD_row;
475
476 Procedure Lock_CHILD_Row (X_ROW_ID IN VARCHAR2,
477 X_RESOURCE_LIST_MEMBER_ID NUMBER,
478 X_RESOURCE_ID NUMBER,
479 X_ALIAS VARCHAR2,
480 X_SORT_ORDER NUMBER,
481 X_DISPLAY_FLAG VARCHAR2,
482 X_ENABLED_FLAG VARCHAR2,
483 X_Funds_Control_Level_Code VARCHAR2
484 ) IS
485 CURSOR C Is
486 Select * From PA_RESOURCE_LIST_MEMBERS WHERE ROWID = X_ROW_ID
487 For Update of RESOURCE_LIST_MEMBER_ID NOWAIT;
488 Recinfo C%ROWTYPE;
489 Begin
490 OPEN C;
491 FETCH C INTO Recinfo;
492 If (C%NOTFOUND) THEN
493 Close C;
494 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
495 APP_EXCEPTION.RAISE_EXCEPTION;
496 END If;
497 CLOSE C;
498 IF (
499 (X_RESOURCE_LIST_MEMBER_ID = Recinfo.RESOURCE_LIST_MEMBER_ID) And
500 (X_RESOURCE_ID = Recinfo.RESOURCE_ID) And
501 ((X_ALIAS = Recinfo.ALIAS) OR
502 (Recinfo.ALIAS Is Null)) AND
503 (X_SORT_ORDER = Recinfo.SORT_ORDER ) AND
504 (X_DISPLAY_FLAG = Recinfo.DISPLAY_FLAG ) AND
505 (X_ENABLED_FLAG = Recinfo.ENABLED_FLAG) AND
506 ( (Recinfo.Funds_Control_Level_Code = X_Funds_Control_Level_Code)
507 OR ( (Recinfo.Funds_Control_Level_Code IS NULL)
508 AND (X_Funds_Control_Level_Code IS NULL) )
509 )
510
511 ) Then
512 Return;
513 Else
514 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_CHANGED');
515 APP_EXCEPTION.RAISE_EXCEPTION;
516 END If;
517
518 End Lock_CHILD_Row;
519
520 Procedure Delete_CHILD_Row (X_ROW_ID IN VARCHAR2) Is
521 Begin
522 Delete from PA_RESOURCE_LIST_MEMBERS Where RowId = X_Row_Id;
523 If SQL%NOTFOUND Then
524 Raise NO_DATA_FOUND;
525 End If;
526 End Delete_Child_Row;
527
528 /* This procedure is added for bug 1889671. This is part of Resource Mapping Enhancement.
529 This procedure will delete the unclassified resource list member if all the children for
530 parent is deleted */
531
532 Procedure Delete_Unclassified_Child (x_resource_list_id IN
533 PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_ID%TYPE,
534 x_parent_member_id IN
535 PA_RESOURCE_LIST_MEMBERS.Parent_Member_ID%TYPE,
536 X_msg_Count OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
537 X_msg_Data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
538 X_return_Status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
539 ) IS
540
541
542 CURSOR Cur_Child_Count IS
543 SELECT count(*)
544 FROM pa_resource_list_members PRLM
545 WHERE prlm.Parent_member_id=x_parent_member_id
546 AND NVL(prlm.resource_type_code,'xyz') <> 'UNCLASSIFIED';
547
548
549 l_count NUMBER;
550 x_err_code NUMBER;
551 x_err_stage VARCHAR2(100);
552 l_resource_id pa_resource_list_members.resource_id%TYPE;
553 l_group_resource_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
554 l_resource_list_member_id pa_resource_list_members.resource_list_member_id%TYPE;
555
556 Begin
557
558 /* In case when resource list is not grouped then this procedure will not get called.To check this
559 condition, we are checking that there is non zero value for group_resource_type_id for resource list id in table */
560
561 SELECT Group_Resource_Type_ID
565
562 INTO l_group_resource_type_id
563 FROM PA_RESOURCE_LISTS_ALL_BG
564 WHERE resource_list_id = x_resource_list_id;
566 If SQL%NOTFOUND Then
567 Raise NO_DATA_FOUND;
568 End If;
569
570 IF L_group_Resource_type_ID = 0 THEN
571 Return;
572 END IF;
573
574 OPEN cur_child_count;
575 Fetch Cur_Child_Count Into l_count;
576
577 /*IF the number of children for resource parent is zero, delete the unclassified list member also.*/
578 If L_count = 0 THEN
579
580 Delete pa_resource_list_members prlm
581 WHere prlm.parent_member_id=x_parent_member_id
582 AND resource_type_code = 'UNCLASSIFIED';
583
584 END IF;
585 Close Cur_Child_Count;
586
587
588 /*Commenting the exception block for the bug 3355209 since the usage is wrong
589 EXCEPTION
590 When Others Then
591
592 --FND_MESSAGE.SET_NAME('PA' ,SQLERRM);
593 APP_EXCEPTION.RAISE_EXCEPTION;*/
594
595 end DELETE_UNCLASSIFIED_CHILD;
596
597 End PA_Resource_List_Pkg;