[Home] [Help]
PACKAGE BODY: APPS.PA_RESOURCE_LIST_TBL_PKG
Source
1 PACKAGE BODY PA_Resource_List_tbl_Pkg AS
2 /* $Header: PARELSTB.pls 120.2 2012/01/03 13:21:38 djambhek ship $ */
3 -- Standard Table Handler procedures for PA_RESOURCE_LISTS table
4
5 -- History:
6 --
7 -- 16-MAR-2004 smullapp created
8 -------------------------------------------------------------------
9 PROCEDURE Insert_Row(
10 p_name PA_RESOURCE_LISTS_ALL_BG.name%TYPE,
11 p_description PA_RESOURCE_LISTS_ALL_BG.description%TYPE,
12 p_public_flag PA_RESOURCE_LISTS_ALL_BG.public_flag%TYPE,
13 p_group_resource_type_id NUMBER,
14 p_start_date_active DATE,
15 p_end_date_active DATE,
16 p_uncategorized_flag PA_RESOURCE_LISTS_ALL_BG.uncategorized_flag%TYPE,
17 p_business_group_id NUMBER,
18 p_adw_notify_flag PA_RESOURCE_LISTS_ALL_BG.adw_notify_flag%TYPE,
19 p_job_group_id NUMBER,
20 p_resource_list_type PA_RESOURCE_LISTS_ALL_BG.resource_list_type%TYPE,
21 p_control_flag PA_RESOURCE_LISTS_ALL_BG.control_flag%TYPE,
22 p_use_for_wp_flag PA_RESOURCE_LISTS_ALL_BG.use_for_wp_flag%TYPE,
23 p_migration_code PA_RESOURCE_LISTS_ALL_BG.migration_code%TYPE,
24 p_Enable_Resource_Classes IN VARCHAR2 DEFAULT 'Y', -- Added for CBS Bug13535688
25 x_resource_list_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
26 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
27 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
28 )
29 IS
30
31 l_row_id VARCHAR2(100);
32
33 --This cursor selects rowid from pa_resource_lists_all_bg
34 CURSOR c_res_cur IS
35 SELECT
36 Rowid
37 FROM
38 pa_resource_lists_all_bg
39 WHERE resource_list_id = x_resource_list_id;
40
41 --This sets the value for resource_list_id of pa_resource_lists_all_bg fro pa_resource_lists_s sequence
42 CURSOR c_res_list_seq_csr IS
43 SELECT pa_resource_lists_s.NEXTVAL
44 FROM
45 SYS.DUAL;
46
47 BEGIN
48
49 x_return_status:='S';
50
51 OPEN c_res_list_seq_csr;
52 FETCH c_res_list_seq_csr INTO X_RESOURCE_LIST_ID;
53 CLOSE c_res_list_seq_csr;
54
55
56 --Inserts a record into pa_resource_lists_all_bg
57 Insert Into PA_RESOURCE_LISTS_ALL_BG
58 (
59 RESOURCE_LIST_ID,
60 NAME,
61 DESCRIPTION,
62 PUBLIC_FLAG,
63 GROUP_RESOURCE_TYPE_ID,
64 START_DATE_ACTIVE,
65 END_DATE_ACTIVE,
66 UNCATEGORIZED_FLAG,
67 BUSINESS_GROUP_ID,
68 ADW_NOTIFY_FLAG,
69 JOB_GROUP_ID,
70 RESOURCE_LIST_TYPE,
71 CONTROL_FLAG,
72 USE_FOR_WP_FLAG,
73 MIGRATION_CODE,
74 LAST_UPDATED_BY,
75 LAST_UPDATE_DATE,
76 CREATION_DATE,
77 CREATED_BY,
78 LAST_UPDATE_LOGIN,
79 RECORD_VERSION_NUMBER,
80 RESOURCE_CLASS_FLAG -- Added for CBS Bug13535688
81 )
82 VALUES(
83 X_RESOURCE_LIST_ID,
84 P_NAME,
85 P_DESCRIPTION,
86 P_PUBLIC_FLAG,
87 P_GROUP_RESOURCE_TYPE_ID,
88 P_START_DATE_ACTIVE,
89 P_END_DATE_ACTIVE,
90 P_UNCATEGORIZED_FLAG,
91 P_BUSINESS_GROUP_ID,
92 P_ADW_NOTIFY_FLAG,
93 P_JOB_GROUP_ID,
94 P_RESOURCE_LIST_TYPE,
95 P_CONTROL_FLAG,
96 P_USE_FOR_WP_FLAG,
97 P_MIGRATION_CODE,
98 FND_GLOBAL.USER_ID,
99 SYSDATE,
100 SYSDATE,
101 FND_GLOBAL.USER_ID,
102 FND_GLOBAL.LOGIN_ID,
103 1,
104 p_Enable_Resource_Classes -- Added for CBS Bug13535688
105 );
106
107 --Checks for failure of Insert stmt
108 OPEN c_res_cur;
109 FETCH c_res_cur INTO l_row_id;
110 IF(c_res_cur%NOTFOUND) THEN
111 CLOSE c_res_cur;
112 Raise NO_DATA_FOUND;
113 END IF;
114 CLOSE c_res_cur;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 x_return_status:='U';
119 x_msg_data:=sqlerrm;
120 RAISE;
121
122 END Insert_Row;
123
124
125 PROCEDURE Insert_row (X_ROW_ID IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
126 X_RESOURCE_LIST_ID NUMBER,
127 X_NAME VARCHAR2,
128 X_DESCRIPTION VARCHAR2,
129 X_PUBLIC_FLAG VARCHAR2,
130 X_GROUP_RESOURCE_TYPE_ID NUMBER,
131 X_START_DATE_ACTIVE DATE,
132 X_END_DATE_ACTIVE DATE,
133 X_UNCATEGORIZED_FLAG VARCHAR2,
134 X_BUSINESS_GROUP_ID NUMBER,
135 -- X_ADW_NOTIFY_FLAG VARCHAR2,
136 X_JOB_GROUP_ID NUMBER,
137 X_RESOURCE_LIST_TYPE VARCHAR2,
138 X_LAST_UPDATED_BY NUMBER,
139 X_LAST_UPDATE_DATE DATE,
140 X_CREATION_DATE DATE,
141 X_CREATED_BY NUMBER,
142 X_LAST_UPDATE_LOGIN NUMBER ) IS
143 CURSOR RES_CUR IS
144 Select
145 Rowid
146 from
147 PA_RESOURCE_LISTS_ALL_BG
148 Where Resource_List_Id = X_Resource_List_Id;
149 BEGIN
150 Insert Into PA_RESOURCE_LISTS_ALL_BG
151 (
152 RESOURCE_LIST_ID,
153 NAME ,
154 DESCRIPTION ,
155 PUBLIC_FLAG ,
156 GROUP_RESOURCE_TYPE_ID ,
157 START_DATE_ACTIVE ,
158 END_DATE_ACTIVE ,
159 UNCATEGORIZED_FLAG ,
160 BUSINESS_GROUP_ID ,
161 --ADW_NOTIFY_FLAG ,
162 JOB_GROUP_ID ,
163 RESOURCE_LIST_TYPE,
164 LAST_UPDATED_BY ,
165 LAST_UPDATE_DATE,
166 CREATION_DATE ,
167 CREATED_BY ,
168 LAST_UPDATE_LOGIN,
169 CONTROL_FLAG,
170 USE_FOR_WP_FLAG,
171 MIGRATION_CODE
172 )
173 VALUES
174 (
175 X_RESOURCE_LIST_ID,
176 X_NAME ,
177 X_DESCRIPTION ,
178 X_PUBLIC_FLAG ,
179 X_GROUP_RESOURCE_TYPE_ID ,
180 X_START_DATE_ACTIVE ,
181 X_END_DATE_ACTIVE ,
182 X_UNCATEGORIZED_FLAG ,
183 X_BUSINESS_GROUP_ID ,
184 -- X_ADW_NOTIFY_FLAG ,
185 X_JOB_GROUP_ID ,
186 X_RESOURCE_LIST_TYPE,
187 X_LAST_UPDATED_BY ,
188 X_LAST_UPDATE_DATE,
189 X_CREATION_DATE ,
190 X_CREATED_BY ,
191 X_LAST_UPDATE_LOGIN,
192 'Y',
193 'N', -- open issue
194 NULL);
195
196 insert into pa_resource_lists_tl (
197 LAST_UPDATE_LOGIN,
198 CREATION_DATE,
199 CREATED_BY,
200 LAST_UPDATE_DATE,
201 LAST_UPDATED_BY,
202 RESOURCE_LIST_ID,
203 NAME,
204 DESCRIPTION,
205 LANGUAGE,
206 SOURCE_LANG
207 ) select
208 FND_GLOBAL.LOGIN_ID,
209 sysdate,
210 FND_GLOBAL.USER_ID,
211 sysdate,
212 FND_GLOBAL.USER_ID,
213 X_RESOURCE_LIST_ID,
214 X_NAME,
215 NVL(X_DESCRIPTION,X_NAME),
216 L.LANGUAGE_CODE,
217 userenv('LANG')
218 from FND_LANGUAGES L
219 where L.INSTALLED_FLAG in ('I', 'B')
220 and not exists
221 (select NULL
222 from pa_resource_lists_tl T
223 where T.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID
224 and T.LANGUAGE = L.LANGUAGE_CODE);
225
226 Open Res_Cur;
227 Fetch Res_Cur Into X_Row_Id;
228 If (Res_Cur%NOTFOUND) then
229 Close Res_Cur;
230 Raise NO_DATA_FOUND;
231 End If;
232 Close Res_Cur;
233 /*Commenting the exception block for the bug 3355209 since it is again standards
234 Exception
235 When Others Then
236 Bug2510641 Begin
237 -- FND_MESSAGE.SET_NAME('PA' ,SQLERRM);
238 FND_MESSAGE.SET_NAME('PA' ,'PA_UNEXPECTED_ERROR_WTH_TKNS');
239 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','PA_Resource_List_tbl_Pkg');
240 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','Insert_Row');
241 FND_MESSAGE.SET_TOKEN('ERROR_TEXT ',SQLERRM);
242 Bug2510641 End
243 APP_EXCEPTION.RAISE_EXCEPTION;*/
244 END Insert_Row;
245
246 PROCEDURE Update_Row (X_ROW_ID IN VARCHAR2,
247 X_RESOURCE_LIST_ID NUMBER,
248 X_NAME VARCHAR2,
249 X_DESCRIPTION VARCHAR2,
250 X_PUBLIC_FLAG VARCHAR2,
251 X_GROUP_RESOURCE_TYPE_ID NUMBER,
252 X_START_DATE_ACTIVE DATE,
253 X_END_DATE_ACTIVE DATE,
254 X_UNCATEGORIZED_FLAG VARCHAR2,
255 X_BUSINESS_GROUP_ID NUMBER,
256 -- X_ADW_NOTIFY_FLAG VARCHAR2,
257 X_JOB_GROUP_ID NUMBER,
258 X_RESOURCE_LIST_TYPE VARCHAR2,
259 X_LAST_UPDATED_BY NUMBER,
260 X_LAST_UPDATE_DATE DATE,
261 X_LAST_UPDATE_LOGIN NUMBER ) IS
262 BEGIN
263
264 Update PA_RESOURCE_LISTS_ALL_BG
265 SET
266 -- For bug 4202015
267 -- RESOURCE_LIST_ID = X_RESOURCE_LIST_ID ,
268 NAME = X_NAME ,
269 DESCRIPTION = X_DESCRIPTION ,
270 PUBLIC_FLAG = X_PUBLIC_FLAG ,
271 GROUP_RESOURCE_TYPE_ID = X_GROUP_RESOURCE_TYPE_ID ,
272 START_DATE_ACTIVE = X_START_DATE_ACTIVE ,
273 END_DATE_ACTIVE = X_END_DATE_ACTIVE ,
274 UNCATEGORIZED_FLAG = X_UNCATEGORIZED_FLAG ,
275 BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID ,
276 -- ADW_NOTIFY_FLAG = X_ADW_NOTIFY_FLAG ,
277 JOB_GROUP_ID = X_JOB_GROUP_ID ,
278 RESOURCE_LIST_TYPE = X_RESOURCE_LIST_TYPE ,
279 LAST_UPDATED_BY = X_LAST_UPDATED_BY ,
280 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE ,
281 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
282 WHERE RESOURCE_LIST_ID = X_RESOURCE_LIST_ID;
283
284 If SQL%NOTFOUND Then
285 Raise NO_DATA_FOUND;
286 End If;
287
288 update pa_resource_lists_tl set
289 NAME = X_NAME,
290 DESCRIPTION = X_DESCRIPTION,
291 LAST_UPDATE_DATE = sysdate,
292 LAST_UPDATED_BY = fnd_global.user_id,
293 LAST_UPDATE_LOGIN = fnd_global.login_id,
294 SOURCE_LANG = userenv('LANG')
295 where resource_list_id = X_RESOURCE_LIST_ID
296 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
297
298 if (sql%notfound) then
299 raise no_data_found;
300 end if;
301
302 END Update_row;
303
304 Procedure Lock_Row (X_ROW_ID IN VARCHAR2,
305 X_RESOURCE_LIST_ID NUMBER,
306 X_NAME VARCHAR2,
307 X_DESCRIPTION VARCHAR2,
308 X_PUBLIC_FLAG VARCHAR2,
309 X_GROUP_RESOURCE_TYPE_ID NUMBER,
310 X_START_DATE_ACTIVE DATE,
311 X_END_DATE_ACTIVE DATE,
312 X_UNCATEGORIZED_FLAG VARCHAR2,
313 X_BUSINESS_GROUP_ID NUMBER,
314 X_JOB_GROUP_ID NUMBER,
315 -- X_ADW_NOTIFY_FLAG VARCHAR2,
316 X_LAST_UPDATED_BY NUMBER) IS
317
318 CURSOR C Is
319 Select * From PA_RESOURCE_LISTS_ALL_BG WHERE ROWID = X_ROW_ID
320 For Update of RESOURCE_LIST_ID NOWAIT;
321 Recinfo C%ROWTYPE;
322 Begin
323 --hr_utility.trace_on(NULL, 'RMUPG');
324 --hr_utility.trace('X_ROW_ID is ' || X_ROW_ID);
325 --hr_utility.trace('X_RESOURCE_LIST_ID is ' || X_RESOURCE_LIST_ID);
326 --hr_utility.trace('X_NAME is ' || X_NAME);
327 --hr_utility.trace('X_DESCRIPTION is ' || X_DESCRIPTION);
328 --hr_utility.trace('X_PUBLIC_FLAG is ' || X_PUBLIC_FLAG);
329 --hr_utility.trace('X_GROUP_RESOURCE_TYPE_ID is ' || X_GROUP_RESOURCE_TYPE_ID);
330 --hr_utility.trace('X_START_DATE_ACTIVE is ' || X_START_DATE_ACTIVE);
331 --hr_utility.trace('X_END_DATE_ACTIVE is ' || X_END_DATE_ACTIVE);
332 --hr_utility.trace('X_UNCATEGORIZED_FLAG is ' || X_UNCATEGORIZED_FLAG);
333 --hr_utility.trace('X_BUSINESS_GROUP_ID is ' || X_BUSINESS_GROUP_ID);
334
335 OPEN C;
336 FETCH C INTO Recinfo;
337 If (C%NOTFOUND) THEN
338 --hr_utility.trace('NOT FOUND');
339 Close C;
340 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
341 APP_EXCEPTION.RAISE_EXCEPTION;
342 END If;
343 CLOSE C;
344 IF (
345 (X_RESOURCE_LIST_ID = recinfo.resource_list_id) And
346 ((X_NAME = recinfo.name) or
347 (( X_NAME is null ) and ( recinfo.name is null))) And
348 ((X_PUBLIC_FLAG = recinfo.public_flag) or
349 (( X_PUBLIC_FLAG is null ) and ( recinfo.public_flag is null))) And
350 ((X_GROUP_RESOURCE_TYPE_ID = recinfo.group_resource_type_id) or
351 (( X_GROUP_RESOURCE_TYPE_ID is null ) and
352 ( recinfo.group_resource_type_id is null))) And
353 (X_START_DATE_ACTIVE = recinfo.start_date_active ) And
354 ((X_END_DATE_ACTIVE = recinfo.end_date_active) or
355 (( X_END_DATE_ACTIVE is null ) and
356 ( recinfo.end_date_active is null))) And
357 ((X_UNCATEGORIZED_FLAG = recinfo.uncategorized_flag) or
358 (( X_UNCATEGORIZED_FLAG is null ) and
359 ( recinfo.uncategorized_flag is null))) And
360 (X_BUSINESS_GROUP_ID = recinfo.business_group_id) and
361 ((X_JOB_GROUP_ID = recinfo.job_group_id) or
362 (( X_job_group_id is null ) and
363 ( recinfo.job_group_id is null)))
364 ) Then
365 Return;
366 Else
367 --hr_utility.trace('ELSE IF');
368 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_CHANGED');
369 APP_EXCEPTION.RAISE_EXCEPTION;
370 END If;
371
372 End Lock_Row;
373
374 Procedure Delete_Row (X_ROW_ID IN VARCHAR2) Is
375 Begin
376
377 delete from pa_resource_lists_tl
378 where resource_list_id in (select resource_list_id
379 from PA_RESOURCE_LISTS_ALL_BG
380 Where RowId = X_Row_Id);
381
382 Delete from PA_RESOURCE_LISTS_ALL_BG Where RowId = X_Row_Id;
383 If SQL%NOTFOUND Then
384 Raise NO_DATA_FOUND;
385 End If;
386
387 End Delete_Row;
388
389 End PA_Resource_List_tbl_Pkg;