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