[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RESOURCE_VALUES_PVT
Source
1 PACKAGE BODY jtf_rs_resource_values_pvt AS
2 /* $Header: jtfrsvcb.pls 120.2 2005/08/29 20:08:22 baianand ship $ */
3
4 /*****************************************************************************************
5 This is a private API that caller will invoke.
6 It provides procedures for managing resource values,
7 like create, update delete and query resource values.
8 Its main procedures are as following:
9 Create Resource Values
10 Update Resource Values
11 Delete Resource Values
12 Delete All Resource Values
13 Get Resource Values
14 Get Resource Param List
15 Calls to these procedures will invoke table handlers
16 to do actual inserts, updates, deletes and queries from the tables.
17 ******************************************************************************************/
18
19 /* Package variables. */
20 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_RESOURCE_VALUES_PVT';
21
22 /* Procedure to Create Resource Values based on the
23 input values provided by the calling routines */
24
25 PROCEDURE CREATE_RS_RESOURCE_VALUES(
26 P_Api_Version IN NUMBER,
27 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
28 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
29 P_resource_id IN NUMBER,
30 p_resource_param_id IN NUMBER,
31 p_value IN VARCHAR2,
32 P_value_type IN VARCHAR2,
33 P_ATTRIBUTE1 IN VARCHAR2,
34 P_ATTRIBUTE2 IN VARCHAR2,
35 P_ATTRIBUTE3 IN VARCHAR2,
36 P_ATTRIBUTE4 IN VARCHAR2,
37 P_ATTRIBUTE5 IN VARCHAR2,
38 P_ATTRIBUTE6 IN VARCHAR2,
39 P_ATTRIBUTE7 IN VARCHAR2,
40 P_ATTRIBUTE8 IN VARCHAR2,
41 P_ATTRIBUTE9 IN VARCHAR2,
42 P_ATTRIBUTE10 IN VARCHAR2,
43 P_ATTRIBUTE11 IN VARCHAR2,
44 P_ATTRIBUTE12 IN VARCHAR2,
45 P_ATTRIBUTE13 IN VARCHAR2,
46 P_ATTRIBUTE14 IN VARCHAR2,
47 P_ATTRIBUTE15 IN VARCHAR2,
48 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
49 X_Return_Status OUT NOCOPY VARCHAR2,
50 X_Msg_Count OUT NOCOPY NUMBER,
51 X_Msg_Data OUT NOCOPY VARCHAR2,
52 X_resource_param_value_id OUT NOCOPY NUMBER
53 )IS
54 l_rowid ROWID;
55 l_api_version CONSTANT NUMBER := 1.0;
56 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RS_RESOURCE_VALUES';
57 l_resource_id NUMBER := P_RESOURCE_ID;
58 l_resource_param_id NUMBER := P_RESOURCE_PARAM_ID;
59 l_value VARCHAR2(255) := P_VALUE;
60 l_value_type VARCHAR2(30) := P_VALUE_TYPE;
61 L_ATTRIBUTE1 JTF_RS_RESOURCE_VALUES.ATTRIBUTE1%TYPE := p_attribute1;
62 L_ATTRIBUTE2 JTF_RS_RESOURCE_VALUES.ATTRIBUTE2%TYPE := p_attribute2;
63 L_ATTRIBUTE3 JTF_RS_RESOURCE_VALUES.ATTRIBUTE3%TYPE := p_attribute3;
64 L_ATTRIBUTE4 JTF_RS_RESOURCE_VALUES.ATTRIBUTE4%TYPE := p_attribute4;
65 L_ATTRIBUTE5 JTF_RS_RESOURCE_VALUES.ATTRIBUTE5%TYPE := p_attribute5;
66 L_ATTRIBUTE6 JTF_RS_RESOURCE_VALUES.ATTRIBUTE6%TYPE := p_attribute6;
67 L_ATTRIBUTE7 JTF_RS_RESOURCE_VALUES.ATTRIBUTE7%TYPE := p_attribute7;
68 L_ATTRIBUTE8 JTF_RS_RESOURCE_VALUES.ATTRIBUTE8%TYPE := p_attribute8;
69 L_ATTRIBUTE9 JTF_RS_RESOURCE_VALUES.ATTRIBUTE9%TYPE := p_attribute9;
70 L_ATTRIBUTE10 JTF_RS_RESOURCE_VALUES.ATTRIBUTE10%TYPE := p_attribute10;
71 L_ATTRIBUTE11 JTF_RS_RESOURCE_VALUES.ATTRIBUTE11%TYPE := p_attribute11;
72 L_ATTRIBUTE12 JTF_RS_RESOURCE_VALUES.ATTRIBUTE12%TYPE := p_attribute12;
73 L_ATTRIBUTE13 JTF_RS_RESOURCE_VALUES.ATTRIBUTE13%TYPE := p_attribute13;
74 L_ATTRIBUTE14 JTF_RS_RESOURCE_VALUES.ATTRIBUTE14%TYPE := p_attribute14;
75 L_ATTRIBUTE15 JTF_RS_RESOURCE_VALUES.ATTRIBUTE15%TYPE := p_attribute15;
76 L_ATTRIBUTE_CATEGORY JTF_RS_RESOURCE_VALUES.ATTRIBUTE_CATEGORY%TYPE := p_attribute_category;
77
78 -- added to handle NOCOPY of JTF_RESOURCE_UTL package
79 l_resource_id_out NUMBER;
80
81
82 l_resource_param_value_id NUMBER;
83 l_bind_data_id NUMBER;
84 l_check_char VARCHAR2(1);
85
86 CURSOR c_jtf_rs_resource_values( l_rowid IN ROWID ) IS
87 SELECT 'Y'
88 FROM jtf_rs_resource_values
89 WHERE ROWID = l_rowid;
90
91 CURSOR c_rs_values_dup IS
92 SELECT 'Y'
93 FROM jtf_rs_resource_values
94 WHERE resource_param_id = l_resource_param_id
95 AND resource_id = l_resource_id
96 AND value_type = l_value_type;
97
98 CURSOR c_get_type(c_resource_param_id number) IS
99 select type
100 from jtf_rs_resource_params
101 where resource_param_id = c_resource_param_id;
102
103 l_type jtf_rs_resource_params.type%TYPE;
104 l_value1 jtf_rs_resource_values.value%TYPE;
105
106 BEGIN
107
108 SAVEPOINT create_rs_resource_values_pvt;
109 x_return_status := fnd_api.g_ret_sts_success;
110 --DBMS_OUTPUT.put_line(' Started Create Resources Values Pvt ');
111
112 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
113 RAISE fnd_api.g_exc_unexpected_error;
114 END IF;
115
116 IF fnd_api.to_boolean(p_init_msg_list) THEN
117 fnd_msg_pub.initialize;
118 END IF;
119
120 --Initializing the Internal User Hook Record parameter values
121
122 jtf_rs_resource_values_pub.p_rs_value_user_hook.resource_id :=l_resource_id;
123 jtf_rs_resource_values_pub.p_rs_value_user_hook.resource_param_id := l_resource_param_id;
124 jtf_rs_resource_values_pub.p_rs_value_user_hook.value_type := l_value_type;
125 jtf_rs_resource_values_pub.p_rs_value_user_hook.value := l_value;
126
127 --Make the pre processing call to the user hooks
128
129 --Pre Call to the Customer Type User Hook
130
131 IF jtf_usr_hks.ok_to_execute(
132 'JTF_RS_RESOURCE_VALUES_PVT',
133 'CREATE_RS_RESOURCE_VALUES',
134 'B',
135 'C')
136 THEN
137 jtf_rs_resource_values_cuhk.create_rs_resource_values_pre(
138 P_RESOURCE_ID => l_resource_id,
139 P_RESOURCE_PARAM_ID => l_resource_param_id,
140 P_VALUE => l_value,
141 P_VALUE_TYPE => l_value_type,
142 X_RETURN_STATUS => x_return_status,
143 X_MSG_COUNT => x_msg_count,
144 X_MSG_DATA => x_msg_data
145 );
146
147 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
148 x_return_status := fnd_api.g_ret_sts_unexp_error;
149 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
150 fnd_msg_pub.add;
151 RAISE fnd_api.g_exc_unexpected_error;
152 END IF;
153 END IF;
154
155 --Pre Call to the Vertical Type User Hook
156
157 IF jtf_usr_hks.ok_to_execute(
158 'JTF_RS_RESOURCE_VALUES_PVT',
159 'CREATE_RS_RESOURCE_VALUES',
160 'B',
161 'V')
162 THEN
163 jtf_rs_resource_values_vuhk.create_rs_resource_values_pre(
164 P_RESOURCE_ID => l_resource_id,
165 P_RESOURCE_PARAM_ID => l_resource_param_id,
166 P_VALUE => l_value,
167 P_VALUE_TYPE => l_value_type,
168 X_RETURN_STATUS => x_return_status,
169 X_MSG_COUNT => x_msg_count,
170 X_MSG_DATA => x_msg_data
171 );
172
173 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
174 x_return_status := fnd_api.g_ret_sts_unexp_error;
175 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
176 fnd_msg_pub.add;
177 RAISE fnd_api.g_exc_unexpected_error;
178 END IF;
179 END IF;
180
181 --Pre Call to the Internal Type User Hook
182 jtf_rs_resource_values_iuhk.create_rs_resource_values_pre(
183 X_RETURN_STATUS => x_return_status
184 );
185
186 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
187 x_return_status := fnd_api.g_ret_sts_unexp_error;
188 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
189 fnd_msg_pub.add;
190 RAISE fnd_api.g_exc_unexpected_error;
191 END IF;
192
193 --Put in all the Validations here
194
195 --Validate the Resource Id
196 jtf_resource_utl.validate_resource_number(
197 p_resource_id => l_resource_id,
198 p_resource_number => null,
199 x_return_status => x_return_status,
200 x_resource_id => l_resource_id_out
201 );
202 -- added for NOCOPY
203 l_resource_id := l_resource_id_out ;
204
205 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
206 x_return_status := fnd_api.g_ret_sts_unexp_error;
207 RAISE fnd_api.g_exc_unexpected_error;
208 END IF;
209 --End of Resource Id Validation
210
211 --Validate the Resource Param Id
212 jtf_resource_utl.validate_resource_param_id(
213 p_resource_param_id => l_resource_param_id,
214 x_return_status => x_return_status
215 );
216 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
217 x_return_status := fnd_api.g_ret_sts_unexp_error;
218 RAISE fnd_api.g_exc_unexpected_error;
219 END IF;
220 --End of Resource Param Id Validation
221
222 --Validate Resource Value Type
223 IF l_value_type IS NOT NULL THEN
224 jtf_resource_utl.validate_rs_value_type(
225 p_resource_param_id => l_resource_param_id,
226 p_value_type => l_value_type,
227 x_return_status => x_return_status
228 );
229 END IF;
230
231 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
232 x_return_status := fnd_api.g_ret_sts_unexp_error;
233 RAISE fnd_api.g_exc_unexpected_error;
234 END IF;
235 --End of Resource Value Type Validation
236
237 --Validate Resource Value
238 jtf_resource_utl.validate_resource_value(
239 p_resource_param_id => l_resource_param_id,
240 p_value => l_value,
241 x_return_status => x_return_status
242 );
243 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
244 x_return_status := fnd_api.g_ret_sts_unexp_error;
245 RAISE fnd_api.g_exc_unexpected_error;
246 END IF;
247 --End of Resource Value Validation
248
249 --Validate for Duplicate Resource Param ID, Resource ID, Value Type Combination
250 OPEN c_rs_values_dup;
251 FETCH c_rs_values_dup INTO l_check_char;
252 IF c_rs_values_dup%FOUND THEN
253 fnd_message.set_name ('JTF', 'JTF_RS_MDW_DUP_VALUE');
254 fnd_msg_pub.add;
255 x_return_status := fnd_api.g_ret_sts_unexp_error;
256 END IF;
257 CLOSE c_rs_values_dup;
258
259 -- This is for bug fix # 3870910
260 -- Password will be stored using fnd_vault API
261 OPEN c_get_type(l_resource_param_id);
262 FETCH c_get_type INTO l_type;
263 CLOSE c_get_type;
264
265 IF l_type = 'PASSWORD' then
266 l_value1 := NULL;
267 ELSE
268 l_value1 := p_value;
269 END IF;
270
271 --Get the next value of the Resource_Param_Value_Id from the sequence
272 SELECT jtf_rs_resource_params_s.nextval
273 INTO l_resource_param_value_id
274 FROM dual;
275
276 --Call the Table Handler to Insert Values
277 jtf_rs_resource_values_pkg.insert_row(
278 X_ROWID => l_rowid,
279 X_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
280 X_RESOURCE_ID => l_resource_id,
281 X_RESOURCE_PARAM_ID => l_resource_param_id,
282 X_VALUE => l_value1,
283 X_VALUE_TYPE => l_value_type,
284 X_ATTRIBUTE2 => l_attribute2,
285 X_ATTRIBUTE3 => l_attribute3,
286 X_ATTRIBUTE4 => l_attribute4,
287 X_ATTRIBUTE5 => l_attribute5,
288 X_ATTRIBUTE6 => l_attribute6,
289 X_ATTRIBUTE7 => l_attribute7,
290 X_ATTRIBUTE8 => l_attribute8,
291 X_ATTRIBUTE9 => l_attribute9,
292 X_ATTRIBUTE10 => l_attribute10,
293 X_ATTRIBUTE11 => l_attribute11,
294 X_ATTRIBUTE12 => l_attribute12,
295 X_ATTRIBUTE13 => l_attribute13,
296 X_ATTRIBUTE14 => l_attribute14,
297 X_ATTRIBUTE15 => l_attribute15,
298 X_ATTRIBUTE_CATEGORY => l_attribute_category,
299 X_ATTRIBUTE1 => l_attribute1,
300 X_CREATION_DATE => sysdate,
301 X_CREATED_BY => jtf_resource_utl.created_by,
302 X_LAST_UPDATE_DATE => sysdate,
303 X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
304 X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
305 );
306
307 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
308 --dbms_output.put_line('Failed status from call to table handler');
309 RAISE fnd_api.g_exc_unexpected_error;
310 END IF;
311
312 x_resource_param_value_id := l_resource_param_value_id;
313
314 -- This is for bug fix # 3870910
315 -- Password will be stored using fnd_vault API
316 if (l_resource_param_value_id is NOT NULL and p_value is NOT NULL and l_type = 'PASSWORD') then
317 fnd_vault.put(l_resource_param_value_id, 'JTF_RS_RESOURCE_VALUES', p_value);
318 end if;
319
320 --Make the post processing call to the user hooks
321
322 --Post Call to the Customer Type User Hook
323
324 IF jtf_usr_hks.ok_to_execute(
325 'JTF_RS_RESOURCE_VALUES_PVT',
326 'CREATE_RS_RESOURCE_VALUES',
327 'A',
328 'C')
329 THEN
330 jtf_rs_resource_values_cuhk.create_rs_resource_values_post(
331 P_RESOURCE_ID => l_resource_id,
332 P_RESOURCE_PARAM_ID => l_resource_param_id,
333 P_VALUE => l_value,
334 P_VALUE_TYPE => l_value_type,
335 P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
336 X_RETURN_STATUS => x_return_status,
337 X_MSG_COUNT => x_msg_count,
338 X_MSG_DATA => x_msg_data
339 );
340
341 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
342 x_return_status := fnd_api.g_ret_sts_unexp_error;
343 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
344 fnd_msg_pub.add;
345 RAISE fnd_api.g_exc_unexpected_error;
346 END IF;
347 END IF;
348
349 --Initializing the Internal User Hook Record parameter value
350
351 jtf_rs_resource_values_pub.p_rs_value_user_hook.resource_param_value_id :=l_resource_param_value_id;
352
353 --Post Call to the Vertical Type User Hook
354
355 IF jtf_usr_hks.ok_to_execute(
356 'JTF_RS_RESOURCE_VALUES_PVT',
357 'CREATE_RS_RESOURCE_VALUES',
358 'A',
359 'V')
360 THEN
361 jtf_rs_resource_values_vuhk.create_rs_resource_values_post(
362 P_RESOURCE_ID => l_resource_id,
363 P_RESOURCE_PARAM_ID => l_resource_param_id,
364 P_VALUE => l_value,
365 P_VALUE_TYPE => l_value_type,
366 P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
367 X_RETURN_STATUS => x_return_status,
368 X_MSG_COUNT => x_msg_count,
369 X_MSG_DATA => x_msg_data
370 );
371
372 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
373 x_return_status := fnd_api.g_ret_sts_unexp_error;
374 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
375 fnd_msg_pub.add;
376 RAISE fnd_api.g_exc_unexpected_error;
377 END IF;
378 END IF;
379
380 --Post Call to the Internal Type User Hook
381
382 jtf_rs_resource_values_iuhk.create_rs_resource_values_post(
383 X_RETURN_STATUS => x_return_status
384 );
385 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
386 x_return_status := fnd_api.g_ret_sts_unexp_error;
387 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
388 fnd_msg_pub.add;
389 RAISE fnd_api.g_exc_unexpected_error;
390 END IF;
391
392 /* Standard call for Message Generation */
393
394 IF jtf_usr_hks.ok_to_execute(
395 'JTF_RS_RESOURCE_VALUES_PVT',
396 'CREATE_RS_RESOURCE_VALUES',
397 'M',
398 'M')
399 THEN
400 IF (jtf_rs_resource_values_cuhk.ok_to_generate_msg(
401 p_resource_param_value_id => l_resource_param_value_id,
402 x_return_status => x_return_status) )
403 THEN
404
405 /* Get the bind data id for the Business Object Instance */
406 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
407
408 /* Set bind values for the bind variables in the Business Object SQL */
409 jtf_usr_hks.load_bind_data(l_bind_data_id, 'resource_param_value_id', l_resource_param_value_id, 'S', 'N');
410
411 /* Call the message generation API */
412 jtf_usr_hks.generate_message(
413 p_prod_code => 'JTF',
414 p_bus_obj_code => 'RS_RPV',
415 p_action_code => 'I',
416 p_bind_data_id => l_bind_data_id,
417 x_return_code => x_return_status);
418 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
419 --dbms_output.put_line('Returned Error status from the Message Generation API');
420 x_return_status := fnd_api.g_ret_sts_unexp_error;
421 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
422 fnd_msg_pub.add;
423 RAISE fnd_api.g_exc_unexpected_error;
424 END IF;
425 END IF;
426 END IF;
427
428 IF fnd_api.to_boolean(p_commit) THEN
429 COMMIT WORK;
430 END IF;
431 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
432
433 EXCEPTION
434 WHEN fnd_api.g_exc_unexpected_error THEN
435 --DBMS_OUTPUT.put_line (' ========================================== ');
436 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
437 ROLLBACK TO create_rs_resource_values_pvt;
438 x_return_status := fnd_api.g_ret_sts_unexp_error;
439 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
440 WHEN OTHERS THEN
441 --DBMS_OUTPUT.put_line (' ========================================== ');
442 --DBMS_OUTPUT.put_line (' =========== Raised Others in Create Resource Values Pvt==============');
443 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
444 ROLLBACK TO create_rs_resource_values_pvt;
445 x_return_status := fnd_api.g_ret_sts_unexp_error;
446 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
447
448 END create_rs_resource_values;
449
450 --Procedure to Update Resource Values based on the input values passed by the calling routines
451
452 PROCEDURE UPDATE_RS_RESOURCE_VALUES(
453 P_Api_Version IN NUMBER,
454 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
455 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
456 p_resource_param_value_id IN NUMBER,
457 p_value IN VARCHAR2,
458 P_ATTRIBUTE1 IN VARCHAR2,
459 P_ATTRIBUTE2 IN VARCHAR2,
460 P_ATTRIBUTE3 IN VARCHAR2,
461 P_ATTRIBUTE4 IN VARCHAR2,
462 P_ATTRIBUTE5 IN VARCHAR2,
463 P_ATTRIBUTE6 IN VARCHAR2,
464 P_ATTRIBUTE7 IN VARCHAR2,
465 P_ATTRIBUTE8 IN VARCHAR2,
466 P_ATTRIBUTE9 IN VARCHAR2,
467 P_ATTRIBUTE10 IN VARCHAR2,
468 P_ATTRIBUTE11 IN VARCHAR2,
469 P_ATTRIBUTE12 IN VARCHAR2,
470 P_ATTRIBUTE13 IN VARCHAR2,
471 P_ATTRIBUTE14 IN VARCHAR2,
472 P_ATTRIBUTE15 IN VARCHAR2,
473 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
474 p_object_version_number IN OUT NOCOPY JTF_RS_RESOURCE_VALUES.OBJECT_VERSION_NUMBER%TYPE,
475 X_Return_Status OUT NOCOPY VARCHAR2,
476 X_Msg_Count OUT NOCOPY NUMBER,
477 X_Msg_Data OUT NOCOPY VARCHAR2
478 )IS
479 l_api_version CONSTANT NUMBER := 1.0;
480 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RS_RESOURCE_VALUES';
481 L_RESOURCE_PARAM_VALUE_ID NUMBER := p_resource_param_value_id;
482 L_RESOURCE_PARAM_ID NUMBER;
483 L_RESOURCE_ID NUMBER;
484 L_VALUE_TYPE VARCHAR2 (30);
485 L_VALUE VARCHAR2 (255) := p_value;
486 L_OBJECT_VERSION_NUMBER JTF_RS_RESOURCE_VALUES.OBJECT_VERSION_NUMBER%TYPE := p_object_version_number;
487 L_ATTRIBUTE1 JTF_RS_RESOURCE_VALUES.ATTRIBUTE1%TYPE := p_attribute1;
488 L_ATTRIBUTE2 JTF_RS_RESOURCE_VALUES.ATTRIBUTE2%TYPE := p_attribute2;
489 L_ATTRIBUTE3 JTF_RS_RESOURCE_VALUES.ATTRIBUTE3%TYPE := p_attribute3;
490 L_ATTRIBUTE4 JTF_RS_RESOURCE_VALUES.ATTRIBUTE4%TYPE := p_attribute4;
491 L_ATTRIBUTE5 JTF_RS_RESOURCE_VALUES.ATTRIBUTE5%TYPE := p_attribute5;
492 L_ATTRIBUTE6 JTF_RS_RESOURCE_VALUES.ATTRIBUTE6%TYPE := p_attribute6;
493 L_ATTRIBUTE7 JTF_RS_RESOURCE_VALUES.ATTRIBUTE7%TYPE := p_attribute7;
494 L_ATTRIBUTE8 JTF_RS_RESOURCE_VALUES.ATTRIBUTE8%TYPE := p_attribute8;
495 L_ATTRIBUTE9 JTF_RS_RESOURCE_VALUES.ATTRIBUTE9%TYPE := p_attribute9;
496 L_ATTRIBUTE10 JTF_RS_RESOURCE_VALUES.ATTRIBUTE10%TYPE := p_attribute10;
497 L_ATTRIBUTE11 JTF_RS_RESOURCE_VALUES.ATTRIBUTE11%TYPE := p_attribute11;
498 L_ATTRIBUTE12 JTF_RS_RESOURCE_VALUES.ATTRIBUTE12%TYPE := p_attribute12;
499 L_ATTRIBUTE13 JTF_RS_RESOURCE_VALUES.ATTRIBUTE13%TYPE := p_attribute13;
500 L_ATTRIBUTE14 JTF_RS_RESOURCE_VALUES.ATTRIBUTE14%TYPE := p_attribute14;
501 L_ATTRIBUTE15 JTF_RS_RESOURCE_VALUES.ATTRIBUTE15%TYPE := p_attribute15;
502 L_ATTRIBUTE_CATEGORY JTF_RS_RESOURCE_VALUES.ATTRIBUTE_CATEGORY%TYPE := p_attribute_category;
503 L_BIND_DATA_ID NUMBER;
504
505 CURSOR c_resource_param_value_id( l_resource_param_value_id IN NUMBER ) IS
506 SELECT resource_param_value_id
507 FROM jtf_rs_resource_values
508 WHERE resource_param_value_id = l_resource_param_value_id;
509
510 CURSOR c_resource_param_value_update( l_resource_param_value_id IN NUMBER ) IS
511 SELECT
512 DECODE(p_value, fnd_api.g_miss_char, value, p_value) l_value,
513 DECODE(p_attribute1,fnd_api.g_miss_char, attribute1, p_attribute1) l_attribute1,
514 DECODE(p_attribute2,fnd_api.g_miss_char, attribute2, p_attribute2) l_attribute2,
515 DECODE(p_attribute3,fnd_api.g_miss_char, attribute3, p_attribute3) l_attribute3,
516 DECODE(p_attribute4,fnd_api.g_miss_char, attribute4, p_attribute4) l_attribute4,
517 DECODE(p_attribute5,fnd_api.g_miss_char, attribute5, p_attribute5) l_attribute5,
518 DECODE(p_attribute6,fnd_api.g_miss_char, attribute6, p_attribute6) l_attribute6,
519 DECODE(p_attribute7,fnd_api.g_miss_char, attribute7, p_attribute7) l_attribute7,
520 DECODE(p_attribute8,fnd_api.g_miss_char, attribute8, p_attribute8) l_attribute8,
521 DECODE(p_attribute9,fnd_api.g_miss_char, attribute9, p_attribute9) l_attribute9,
522 DECODE(p_attribute10,fnd_api.g_miss_char, attribute10, p_attribute10) l_attribute10,
523 DECODE(p_attribute11,fnd_api.g_miss_char, attribute11, p_attribute11) l_attribute11,
524 DECODE(p_attribute12,fnd_api.g_miss_char, attribute12, p_attribute12) l_attribute12,
525 DECODE(p_attribute13,fnd_api.g_miss_char, attribute13, p_attribute13) l_attribute13,
526 DECODE(p_attribute14,fnd_api.g_miss_char, attribute14, p_attribute14) l_attribute14,
527 DECODE(p_attribute15,fnd_api.g_miss_char, attribute15, p_attribute15) l_attribute15,
528 DECODE(p_attribute_category,fnd_api.g_miss_char, attribute1, p_attribute_category) l_attribute_category
529 FROM jtf_rs_resource_values
530 WHERE resource_param_value_id = l_resource_param_value_id;
531
532 resource_param_value_rec c_resource_param_value_update%ROWTYPE;
533
534 CURSOR c_get_type(c_resource_param_id number) IS
535 select type
536 from jtf_rs_resource_params
537 where resource_param_id = c_resource_param_id;
538
539 l_type jtf_rs_resource_params.type%TYPE;
540 l_value1 jtf_rs_resource_values.value%TYPE;
541
542 BEGIN
543
544 SAVEPOINT update_rs_resource_values_pvt;
545 x_return_status := fnd_api.g_ret_sts_success;
546 --DBMS_OUTPUT.put_line(' Started Update Resources Values Pvt ');
547
548 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
549 RAISE fnd_api.g_exc_unexpected_error;
550 END IF;
551
552 IF fnd_api.to_boolean(p_init_msg_list) THEN
553 fnd_msg_pub.initialize;
554 END IF;
555
556 --Initializing the Internal User Hook Record parameter values
557
558 jtf_rs_resource_values_pub.p_rs_value_user_hook.resource_param_value_id := l_resource_param_value_id;
559 jtf_rs_resource_values_pub.p_rs_value_user_hook.value := l_value;
560
561
562 --Make the pre processing call to the user hooks
563
564 --Pre Call to the Customer Type User Hook
565
566 IF jtf_usr_hks.ok_to_execute(
567 'JTF_RS_RESOURCE_VALUES_PVT',
568 'UPDATE_RS_RESOURCE_VALUES',
569 'B',
570 'C')
571 THEN
572 jtf_rs_resource_values_cuhk.update_rs_resource_values_pre(
573 P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
574 P_VALUE => l_value,
575 X_RETURN_STATUS => x_return_status,
576 X_MSG_COUNT => x_msg_count,
577 X_MSG_DATA => x_msg_data
578 );
579
580 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
581 x_return_status := fnd_api.g_ret_sts_unexp_error;
582 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
583 fnd_msg_pub.add;
584 RAISE fnd_api.g_exc_unexpected_error;
585 END IF;
586 END IF;
587
588 --Pre Call to the Vertical Type User Hook
589
590 IF jtf_usr_hks.ok_to_execute(
591 'JTF_RS_RESOURCE_VALUES_PVT',
592 'UPDATE_RS_RESOURCE_VALUES',
593 'B',
594 'V')
595 THEN
596 jtf_rs_resource_values_vuhk.update_rs_resource_values_pre(
597 P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
598 P_VALUE => l_value,
599 X_RETURN_STATUS => x_return_status,
600 X_MSG_COUNT => x_msg_count,
601 X_MSG_DATA => x_msg_data
602 );
603
604 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
605 x_return_status := fnd_api.g_ret_sts_unexp_error;
606 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
607 fnd_msg_pub.add;
608 RAISE fnd_api.g_exc_unexpected_error;
609 END IF;
610 END IF;
611
612 --Pre Call to the Internal Type User Hook
613
614 jtf_rs_resource_values_iuhk.update_rs_resource_values_pre(
615 X_RETURN_STATUS => x_return_status
616 );
617 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
618 x_return_status := fnd_api.g_ret_sts_unexp_error;
619 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
620 fnd_msg_pub.add;
621 RAISE fnd_api.g_exc_unexpected_error;
622 END IF;
623
624 --Put all Validations here
625
626 --Validate Resource Param Value Id
627 OPEN c_resource_param_value_id(l_resource_param_value_id);
628 FETCH c_resource_param_value_id INTO l_resource_param_value_id;
629 IF c_resource_param_value_id%NOTFOUND THEN
630 --dbms_output.put_line('Resource Param Value Id not found');
631 x_return_status := fnd_api.g_ret_sts_unexp_error;
632 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RS_PRM_VALUE_ID');
633 fnd_message.set_token('P_RESOURCE_PARAM_VALUE_ID', l_resource_param_value_id);
634 fnd_msg_pub.add;
635 CLOSE c_resource_param_value_id;
636 RAISE fnd_api.g_exc_unexpected_error;
637 END IF;
638
639 --Validate Resource Param Value for Update
640 OPEN c_resource_param_value_update(l_resource_param_value_id);
641 FETCH c_resource_param_value_update INTO resource_param_value_rec;
642 IF c_resource_param_value_update%NOTFOUND THEN
643 CLOSE c_resource_param_value_update;
644 x_return_status := fnd_api.g_ret_sts_unexp_error;
645 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RS_PRM_VALUE_ID');
646 fnd_message.set_token('P_RESOURCE_PARAM_VALUE_ID', l_resource_param_value_id);
647 fnd_msg_pub.add;
648 RAISE fnd_api.g_exc_unexpected_error;
649 END IF;
650
651 --Get the Resource Param Id, Resource Id, Value Type from the database
652 SELECT resource_param_id, resource_id, value_type
653 INTO l_resource_param_id, l_resource_id, l_value_type
654 FROM jtf_rs_resource_values
655 WHERE resource_param_value_id = l_resource_param_value_id;
656
657 --Validate Resource Value
658 IF p_value <> FND_API.G_MISS_CHAR THEN
659 jtf_resource_utl.validate_resource_value(
660 p_resource_param_id => l_resource_param_id,
661 p_value => resource_param_value_rec.l_value,
662 x_return_status => x_return_status
663 );
664 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
665 x_return_status := fnd_api.g_ret_sts_unexp_error;
666 RAISE fnd_api.g_exc_unexpected_error;
667 END IF;
668 END IF;
669 --End of Resource Value Validation
670
671 --Lock the row in the table by calling the table handler
672 jtf_rs_resource_values_pkg.lock_row(
673 X_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
674 X_OBJECT_VERSION_NUMBER => l_object_version_number
675 );
676
677 --Update the Object Version Number By Incrementing it
678 l_object_version_number := p_object_version_number +1;
679
680 -- This is for bug fix # 3870910
681 -- Password will be stored using fnd_vault API
682 OPEN c_get_type(l_resource_param_id);
683 FETCH c_get_type INTO l_type;
684 CLOSE c_get_type;
685
686 IF l_type = 'PASSWORD' then
687 l_value1 := NULL;
688 ELSE
689 l_value1 := resource_param_value_rec.l_value;
690 END IF;
691
692 --Call the Table Handler to Update Values
693 jtf_rs_resource_values_pkg.update_row(
694 X_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
695 X_RESOURCE_ID => l_resource_id,
696 X_RESOURCE_PARAM_ID => l_resource_param_id,
697 X_VALUE => l_value1,
698 X_VALUE_TYPE => l_value_type,
699 X_OBJECT_VERSION_NUMBER => l_object_version_number,
700 X_ATTRIBUTE2 => resource_param_value_rec.l_attribute2,
701 X_ATTRIBUTE3 => resource_param_value_rec.l_attribute3,
702 X_ATTRIBUTE4 => resource_param_value_rec.l_attribute4,
703 X_ATTRIBUTE5 => resource_param_value_rec.l_attribute5,
704 X_ATTRIBUTE6 => resource_param_value_rec.l_attribute6,
705 X_ATTRIBUTE7 => resource_param_value_rec.l_attribute7,
706 X_ATTRIBUTE8 => resource_param_value_rec.l_attribute8,
707 X_ATTRIBUTE9 => resource_param_value_rec.l_attribute9,
708 X_ATTRIBUTE10 => resource_param_value_rec.l_attribute10,
709 X_ATTRIBUTE11 => resource_param_value_rec.l_attribute11,
710 X_ATTRIBUTE12 => resource_param_value_rec.l_attribute12,
711 X_ATTRIBUTE13 => resource_param_value_rec.l_attribute13,
712 X_ATTRIBUTE14 => resource_param_value_rec.l_attribute14,
713 X_ATTRIBUTE15 => resource_param_value_rec.l_attribute15,
714 X_ATTRIBUTE_CATEGORY => resource_param_value_rec.l_attribute_category,
715 X_ATTRIBUTE1 => resource_param_value_rec.l_attribute1,
716 X_LAST_UPDATE_DATE => sysdate,
717 X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
718 X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
719 );
720 p_object_version_number := l_object_version_number;
721
722 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
723 --dbms_output.put_line('Failed status from call to table handler');
724 RAISE fnd_api.g_exc_unexpected_error;
725 END IF;
726
727 -- This is for bug fix # 3870910
728 -- Password will be stored using fnd_vault API
729 if (l_resource_param_value_id is NOT NULL and resource_param_value_rec.l_value is NOT NULL and l_type = 'PASSWORD') then
730 fnd_vault.put(l_resource_param_value_id, 'JTF_RS_RESOURCE_VALUES', resource_param_value_rec.l_value);
731 end if;
732
733 --Make the post processing call to the user hooks
734
735 --Post Call to the Customer Type User Hook
736
737 IF jtf_usr_hks.ok_to_execute(
738 'JTF_RS_RESOURCE_VALUES_PVT',
739 'UPDATE_RS_RESOURCE_VALUES',
740 'A',
741 'C')
742 THEN
743 jtf_rs_resource_values_cuhk.update_rs_resource_values_post(
744 P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
745 P_VALUE => l_value,
746 X_RETURN_STATUS => x_return_status,
747 X_MSG_COUNT => x_msg_count,
748 X_MSG_DATA => x_msg_data
749 );
750
751 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
752 x_return_status := fnd_api.g_ret_sts_unexp_error;
753 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
754 fnd_msg_pub.add;
755 RAISE fnd_api.g_exc_unexpected_error;
756 END IF;
757 END IF;
758
759 --Post Call to the Vertical Type User Hook
760
761 IF jtf_usr_hks.ok_to_execute(
762 'JTF_RS_RESOURCE_VALUES_PVT',
763 'UPDATE_RS_RESOURCE_VALUES',
764 'A',
765 'V')
766 THEN
767 jtf_rs_resource_values_vuhk.update_rs_resource_values_post(
768 P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
769 P_VALUE => l_value,
770 X_RETURN_STATUS => x_return_status,
771 X_MSG_COUNT => x_msg_count,
772 X_MSG_DATA => x_msg_data
773 );
774
775 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
776 x_return_status := fnd_api.g_ret_sts_unexp_error;
777 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
778 fnd_msg_pub.add;
779 RAISE fnd_api.g_exc_unexpected_error;
780 END IF;
781 END IF;
782
783 --Post Call to the Internal Type User Hook
784
785 jtf_rs_resource_values_iuhk.update_rs_resource_values_post(
786 X_RETURN_STATUS => x_return_status
787 );
788 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
789 x_return_status := fnd_api.g_ret_sts_unexp_error;
790 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
791 fnd_msg_pub.add;
792 RAISE fnd_api.g_exc_unexpected_error;
793 END IF;
794
795 /* Standard call for Message Generation */
796
797 IF jtf_usr_hks.ok_to_execute(
798 'JTF_RS_RESOURCE_VALUES_PVT',
799 'UPDATE_RS_RESOURCE_VALUES',
800 'M',
801 'M')
802 THEN
803 IF (jtf_rs_resource_values_cuhk.ok_to_generate_msg(
804 p_resource_param_value_id => l_resource_param_value_id,
805 x_return_status => x_return_status) )
806 THEN
807
808 /* Get the bind data id for the Business Object Instance */
809 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
810
811 /* Set bind values for the bind variables in the Business Object SQL */
812 jtf_usr_hks.load_bind_data(l_bind_data_id, 'resource_param_value_id', p_resource_param_value_id, 'S', 'N');
813
814 /* Call the message generation API */
815 jtf_usr_hks.generate_message(
816 p_prod_code => 'JTF',
817 p_bus_obj_code => 'RS_RPV',
818 p_action_code => 'U',
819 p_bind_data_id => l_bind_data_id,
820 x_return_code => x_return_status);
821 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
822 --dbms_output.put_line('Returned Error status from the Message Generation API');
823 x_return_status := fnd_api.g_ret_sts_unexp_error;
824 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
825 fnd_msg_pub.add;
826 RAISE fnd_api.g_exc_unexpected_error;
827 END IF;
828 END IF;
829 END IF;
830
831 IF fnd_api.to_boolean(p_commit) THEN
832 COMMIT WORK;
833 END IF;
834 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
835
836 EXCEPTION
837 WHEN fnd_api.g_exc_unexpected_error THEN
838 --DBMS_OUTPUT.put_line (' ========================================== ');
839 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
840 ROLLBACK TO update_rs_resource_values_pvt;
841 x_return_status := fnd_api.g_ret_sts_unexp_error;
842 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
843 WHEN OTHERS THEN
844 --DBMS_OUTPUT.put_line (' ========================================== ');
845 --DBMS_OUTPUT.put_line (' =========== Raised Others in Update Resource Values Pvt============= ');
846 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
847 ROLLBACK TO update_rs_resource_values_pvt;
848 x_return_status := fnd_api.g_ret_sts_unexp_error;
849 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
850
851 END update_rs_resource_values;
852
853 --Procedure to Delete Resource Values based on the input values provided by the calling routines
854
855 PROCEDURE DELETE_RS_RESOURCE_VALUES(
856 P_Api_Version IN NUMBER,
857 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
858 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
859 p_resource_param_value_id IN NUMBER,
860 p_object_version_number IN JTF_RS_RESOURCE_VALUES.OBJECT_VERSION_NUMBER%TYPE,
861 X_Return_Status OUT NOCOPY VARCHAR2,
862 X_Msg_Count OUT NOCOPY NUMBER,
863 X_Msg_Data OUT NOCOPY VARCHAR2
864 )IS
865 l_api_version CONSTANT NUMBER := 1.0;
866 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RS_RESOURCE_VALUES';
867 l_resource_param_value_id NUMBER := p_resource_param_value_id;
868 m_resource_param_value_id NUMBER;
869 l_object_version_number NUMBER := p_object_version_number;
870 l_bind_data_id NUMBER;
871
872 CURSOR c_resource_param_value_id( l_resource_param_value_id IN NUMBER ) IS
873 SELECT resource_param_value_id
874 FROM jtf_rs_resource_values
875 WHERE resource_param_value_id = l_resource_param_value_id;
876
877 BEGIN
878 SAVEPOINT delete_rs_resource_values_pvt;
879 x_return_status := fnd_api.g_ret_sts_success;
880 --DBMS_OUTPUT.put_line(' Started Delete Resources Values Pvt ');
881 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
882 RAISE fnd_api.g_exc_unexpected_error;
883 END IF;
884 IF fnd_api.to_boolean(p_init_msg_list) THEN
885 fnd_msg_pub.initialize;
886 END IF;
887
888 --Initializing the Internal User Hook Record parameter values
889
890 jtf_rs_resource_values_pub.p_rs_value_user_hook.resource_param_value_id := l_resource_param_value_id;
891
892 --Make the pre processing call to the user hooks
893
894 --Pre Call to the Customer Type User Hook
895
896 IF jtf_usr_hks.ok_to_execute(
897 'JTF_RS_RESOURCE_VALUES_PVT',
898 'DELETE_RS_RESOURCE_VALUES',
899 'B',
900 'C')
901 THEN
902 jtf_rs_resource_values_cuhk.delete_rs_resource_values_pre(
903 P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
904 X_RETURN_STATUS => x_return_status,
905 X_MSG_COUNT => x_msg_count,
906 X_MSG_DATA => x_msg_data
907 );
908
909 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
910 x_return_status := fnd_api.g_ret_sts_unexp_error;
911 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
912 fnd_msg_pub.add;
913 RAISE fnd_api.g_exc_unexpected_error;
914 END IF;
915 END IF;
916
917 --Pre Call to the Vertical Type User Hook
918
919 IF jtf_usr_hks.ok_to_execute(
920 'JTF_RS_RESOURCE_VALUES_PVT',
921 'DELETE_RS_RESOURCE_VALUES',
922 'B',
923 'V')
924 THEN
925 jtf_rs_resource_values_vuhk.delete_rs_resource_values_pre(
926 P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
927 X_RETURN_STATUS => x_return_status,
928 X_MSG_COUNT => x_msg_count,
929 X_MSG_DATA => x_msg_data
930 );
931
932 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
933 x_return_status := fnd_api.g_ret_sts_unexp_error;
934 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
935 fnd_msg_pub.add;
936 RAISE fnd_api.g_exc_unexpected_error;
937 END IF;
938 END IF;
939
940 --Pre Call to the Internal Type User Hook
941
942 jtf_rs_resource_values_iuhk.delete_rs_resource_values_pre(
943 X_RETURN_STATUS => x_return_status
944 );
945
946 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
947 x_return_status := fnd_api.g_ret_sts_unexp_error;
948 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
949 fnd_msg_pub.add;
950 RAISE fnd_api.g_exc_unexpected_error;
951 END IF;
952
953 --Put all Validations here
954
955 --Validate Resource Param Value Id
956 OPEN c_resource_param_value_id(l_resource_param_value_id);
957 FETCH c_resource_param_value_id INTO m_resource_param_value_id;
958 IF c_resource_param_value_id%NOTFOUND THEN
959 --dbms_output.put_line('Resource Param Value Id not found');
960 x_return_status := fnd_api.g_ret_sts_unexp_error;
961 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RS_PRM_VALUE_ID');
962 fnd_message.set_token('P_RESOURCE_PARAM_VALUE_ID', l_resource_param_value_id);
963 fnd_msg_pub.add;
964 CLOSE c_resource_param_value_id;
965 RAISE fnd_api.g_exc_unexpected_error;
966 END IF;
967
968
969 --Lock the row in the table before delete, by calling the table handler. */
970 jtf_rs_resource_values_pkg.lock_row(
971 X_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
972 X_OBJECT_VERSION_NUMBER => l_object_version_number
973 );
974
975 --Call Table Handler to Delete the Record
976 jtf_rs_resource_values_pkg.delete_row(
977 X_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id
978 );
979 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
980 --dbms_output.put_line('Failed status from call to table handler');
981 RAISE fnd_api.g_exc_unexpected_error;
982 END IF;
983
984 --Make the post processing call to the user hooks
985
986 --Post Call to the Customer Type User Hook
987
988 IF jtf_usr_hks.ok_to_execute(
989 'JTF_RS_RESOURCE_VALUES_PVT',
990 'DELETE_RS_RESOURCE_VALUES',
991 'A',
992 'C')
993 THEN
994 jtf_rs_resource_values_cuhk.delete_rs_resource_values_post(
995 P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
996 X_RETURN_STATUS => x_return_status,
997 X_MSG_COUNT => x_msg_count,
998 X_MSG_DATA => x_msg_data
999 );
1000
1001 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1002 x_return_status := fnd_api.g_ret_sts_unexp_error;
1003 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1004 fnd_msg_pub.add;
1005 RAISE fnd_api.g_exc_unexpected_error;
1006 END IF;
1007 END IF;
1008
1009 --Post Call to the Vertical Type User Hook
1010
1011 IF jtf_usr_hks.ok_to_execute(
1012 'JTF_RS_RESOURCE_VALUES_PVT',
1013 'DELETE_RS_RESOURCE_VALUES',
1014 'A',
1015 'V')
1016 THEN
1017 jtf_rs_resource_values_vuhk.delete_rs_resource_values_post(
1018 P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
1019 X_RETURN_STATUS => x_return_status,
1020 X_MSG_COUNT => x_msg_count,
1021 X_MSG_DATA => x_msg_data
1022 );
1023
1024 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1025 x_return_status := fnd_api.g_ret_sts_unexp_error;
1026 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1027 fnd_msg_pub.add;
1028 RAISE fnd_api.g_exc_unexpected_error;
1029 END IF;
1030 END IF;
1031
1032 --Post Call to the Internal Type User Hook
1033
1034 jtf_rs_resource_values_iuhk.delete_rs_resource_values_post(
1035 X_RETURN_STATUS => x_return_status
1036 );
1037 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1038 x_return_status := fnd_api.g_ret_sts_unexp_error;
1039 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1040 fnd_msg_pub.add;
1041 RAISE fnd_api.g_exc_unexpected_error;
1042 END IF;
1043
1044 /* Standard call for Message Generation */
1045
1046 IF jtf_usr_hks.ok_to_execute(
1047 'JTF_RS_RESOURCE_VALUES_PVT',
1048 'DELETE_RS_RESOURCE_VALUES',
1049 'M',
1050 'M')
1051 THEN
1052 IF (jtf_rs_resource_values_cuhk.ok_to_generate_msg(
1053 p_resource_param_value_id => p_resource_param_value_id,
1054 x_return_status => x_return_status) )
1055 THEN
1056
1057 /* Get the bind data id for the Business Object Instance */
1058 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1059
1060 /* Set bind values for the bind variables in the Business Object SQL */
1061 jtf_usr_hks.load_bind_data(l_bind_data_id, 'resource_param_value_id', p_resource_param_value_id, 'S', 'N');
1062
1063 /* Call the message generation API */
1064 jtf_usr_hks.generate_message(
1065 p_prod_code => 'JTF',
1066 p_bus_obj_code => 'RS_RPV',
1067 p_action_code => 'D',
1068 p_bind_data_id => l_bind_data_id,
1069 x_return_code => x_return_status);
1070 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1071 --dbms_output.put_line('Returned Error status from the Message Generation API');
1072 x_return_status := fnd_api.g_ret_sts_unexp_error;
1073 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1074 fnd_msg_pub.add;
1075 RAISE fnd_api.g_exc_unexpected_error;
1076 END IF;
1077 END IF;
1078 END IF;
1079
1080 IF fnd_api.to_boolean(p_commit) THEN
1081 COMMIT WORK;
1082 END IF;
1083 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1084
1085 EXCEPTION
1086 WHEN fnd_api.g_exc_unexpected_error THEN
1087 --DBMS_OUTPUT.put_line (' ========================================== ');
1088 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
1089 ROLLBACK TO delete_rs_resource_values_pvt;
1090 x_return_status := fnd_api.g_ret_sts_unexp_error;
1091 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1092 WHEN OTHERS THEN
1093 --DBMS_OUTPUT.put_line (' ========================================== ');
1094 --DBMS_OUTPUT.put_line (' =========== Raised Others in Delete Resource Values Pvt=============');
1095 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1096 ROLLBACK TO delete_rs_resource_values_pvt;
1097 x_return_status := fnd_api.g_ret_sts_unexp_error;
1098 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1099
1100 END delete_rs_resource_values;
1101
1102 --Procedure to Delete all Resource Values based on the Resource Id provided by the calling routines
1103
1104 PROCEDURE DELETE_ALL_RS_RESOURCE_VALUES(
1105 P_Api_Version IN NUMBER,
1106 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1107 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1108 p_resource_id IN NUMBER,
1109 X_Return_Status OUT NOCOPY VARCHAR2,
1110 X_Msg_Count OUT NOCOPY NUMBER,
1111 X_Msg_Data OUT NOCOPY VARCHAR2
1112 )IS
1113 l_api_version CONSTANT NUMBER := 1.0;
1114 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ALL_RS_RESOURCE_VALUES';
1115 l_resource_id NUMBER := p_resource_id;
1116 l_resource_param_value_id NUMBER;
1117 l_object_version_number JTF_RS_RESOURCE_VALUES.OBJECT_VERSION_NUMBER%TYPE;
1118 l_bind_data_id NUMBER;
1119 -- added to handle NOCOPY for JTF_RESOURCE_UTL package
1120 l_resource_id_out NUMBER;
1121
1122 CURSOR c_resource_param_value_id (l_resource_id IN NUMBER ) IS
1123 SELECT jrv.resource_param_value_id, jrv.object_version_number
1124 FROM jtf_rs_resource_values jrv, jtf_rs_resource_params jrp
1125 WHERE jrv.resource_param_id = jrp.resource_param_id
1126 AND jrp.application_id in ( 680, 172 )
1127 AND jrv.resource_id = l_resource_id;
1128
1129 BEGIN
1130 SAVEPOINT delete_all_rs_values_pvt;
1131 x_return_status := fnd_api.g_ret_sts_success;
1132 --DBMS_OUTPUT.put_line(' Started Delete All Resources Values Pvt ');
1133 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1134 RAISE fnd_api.g_exc_unexpected_error;
1135 END IF;
1136 IF fnd_api.to_boolean(p_init_msg_list) THEN
1137 fnd_msg_pub.initialize;
1138 END IF;
1139
1140 --Initializing the Internal User Hook Record parameter value
1141
1142 jtf_rs_resource_values_pub.p_rs_value_user_hook.resource_id := l_resource_id;
1143
1144 --Make the pre processing call to the user hooks
1145
1146 --Pre Call to the Customer Type User Hook
1147
1148 IF jtf_usr_hks.ok_to_execute(
1149 'JTF_RS_RESOURCE_VALUES_PVT',
1150 'DELETE_ALL_RS_RESOURCE_VALUES',
1151 'B',
1152 'C')
1153 THEN
1154 jtf_rs_resource_values_cuhk.delete_all_rs_values_pre(
1155 P_RESOURCE_ID => l_resource_id,
1156 X_RETURN_STATUS => x_return_status,
1157 X_MSG_COUNT => x_msg_count,
1158 X_MSG_DATA => x_msg_data
1159 );
1160
1161 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1162 x_return_status := fnd_api.g_ret_sts_unexp_error;
1163 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
1164 fnd_msg_pub.add;
1165 RAISE fnd_api.g_exc_unexpected_error;
1166 END IF;
1167 END IF;
1168
1169 --Pre Call to the Vertical Type User Hook
1170
1171 IF jtf_usr_hks.ok_to_execute(
1172 'JTF_RS_RESOURCE_VALUES_PVT',
1173 'DELETE_ALL_RS_RESOURCE_VALUES',
1174 'B',
1175 'V')
1176 THEN
1177 jtf_rs_resource_values_vuhk.delete_all_rs_values_pre(
1178 P_RESOURCE_ID => l_resource_id,
1179 X_RETURN_STATUS => x_return_status,
1180 X_MSG_COUNT => x_msg_count,
1181 X_MSG_DATA => x_msg_data
1182 );
1183
1184 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1185 x_return_status := fnd_api.g_ret_sts_unexp_error;
1186 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
1187 fnd_msg_pub.add;
1188 RAISE fnd_api.g_exc_unexpected_error;
1189 END IF;
1190 END IF;
1191
1192 --Pre Call to the Internal Type User Hook
1193
1194 jtf_rs_resource_values_iuhk.delete_all_rs_values_pre(
1195 X_RETURN_STATUS => x_return_status
1196 );
1197
1198 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1199 x_return_status := fnd_api.g_ret_sts_unexp_error;
1200 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
1201 fnd_msg_pub.add;
1202 RAISE fnd_api.g_exc_unexpected_error;
1203 END IF;
1204
1205 --Put all Validations here
1206
1207 --Validate Resource Id that was passed to the Procedure
1208 jtf_resource_utl.validate_resource_number(
1209 p_resource_id => l_resource_id,
1210 p_resource_number => null,
1211 x_return_status => x_return_status,
1212 x_resource_id => l_resource_id_out
1213 );
1214 -- added for NOCOPY
1215 l_resource_id := l_resource_id_out;
1216
1217 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1218 x_return_status := fnd_api.g_ret_sts_unexp_error;
1219 RAISE fnd_api.g_exc_unexpected_error;
1220 END IF;
1221
1222 --Loop through the Cursor and Delete all the entries for the given Resource ID
1223 FOR i IN c_resource_param_value_id (l_resource_id) LOOP
1224 --Call to the Lock_Row Table Handlers to lock the record before deleting it
1225 BEGIN
1226 jtf_rs_resource_values_pkg.lock_row(
1227 X_RESOURCE_PARAM_VALUE_ID => i.resource_param_value_id,
1228 X_OBJECT_VERSION_NUMBER => i.object_version_number
1229 );
1230 EXCEPTION
1231 WHEN NO_DATA_FOUND THEN
1232 --dbms_output.put_line('Error in Table Handler');
1233 x_return_status := fnd_api.g_ret_sts_unexp_error;
1234 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
1235 fnd_msg_pub.add;
1236 RAISE fnd_api.g_exc_unexpected_error;
1237 END;
1238 --Call Table Handler to Delete the Selected Records
1239 BEGIN
1240 jtf_rs_resource_values_pkg.delete_row(
1241 X_RESOURCE_PARAM_VALUE_ID => i.resource_param_value_id
1242 );
1243 EXCEPTION
1244 WHEN NO_DATA_FOUND THEN
1245 --dbms_output.put_line('Error in Table Handler');
1246 x_return_status := fnd_api.g_ret_sts_unexp_error;
1247 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
1248 fnd_msg_pub.add;
1249 RAISE fnd_api.g_exc_unexpected_error;
1250 END;
1251 END LOOP;
1252
1253 --Make the post processing call to the user hooks
1254
1255 --Post Call to the Customer Type User Hook
1256
1257 IF jtf_usr_hks.ok_to_execute(
1258 'JTF_RS_RESOURCE_VALUES_PVT',
1259 'DELETE_ALL_RS_RESOURCE_VALUES',
1260 'A',
1261 'C')
1262 THEN
1263 jtf_rs_resource_values_cuhk.delete_all_rs_values_post(
1264 P_RESOURCE_ID => l_resource_id,
1265 X_RETURN_STATUS => x_return_status,
1266 X_MSG_COUNT => x_msg_count,
1267 X_MSG_DATA => x_msg_data
1268 );
1269
1270 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1271 x_return_status := fnd_api.g_ret_sts_unexp_error;
1272 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1273 fnd_msg_pub.add;
1274 RAISE fnd_api.g_exc_unexpected_error;
1275 END IF;
1276 END IF;
1277
1278 --Post Call to the Vertical Type User Hook
1279
1280 IF jtf_usr_hks.ok_to_execute(
1281 'JTF_RS_RESOURCE_VALUES_PVT',
1282 'DELETE_ALL_RS_RESOURCE_VALUES',
1283 'A',
1284 'V')
1285 THEN
1286 jtf_rs_resource_values_vuhk.delete_all_rs_values_post(
1287 P_RESOURCE_ID => l_resource_id,
1288 X_RETURN_STATUS => x_return_status,
1289 X_MSG_COUNT => x_msg_count,
1290 X_MSG_DATA => x_msg_data
1291 );
1292
1293 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1294 x_return_status := fnd_api.g_ret_sts_unexp_error;
1295 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1296 fnd_msg_pub.add;
1297 RAISE fnd_api.g_exc_unexpected_error;
1298 END IF;
1299 END IF;
1300
1301 --Post Call to the Internal Type User Hook
1302
1303 jtf_rs_resource_values_iuhk.delete_all_rs_values_post(
1304 X_RETURN_STATUS => x_return_status
1305 );
1306 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1307 x_return_status := fnd_api.g_ret_sts_unexp_error;
1308 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1309 fnd_msg_pub.add;
1310 RAISE fnd_api.g_exc_unexpected_error;
1311 END IF;
1312
1313 IF fnd_api.to_boolean(p_commit) THEN
1314 COMMIT WORK;
1315 END IF;
1316 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1317
1318 EXCEPTION
1319 WHEN fnd_api.g_exc_unexpected_error THEN
1320 --DBMS_OUTPUT.put_line (' ========================================== ');
1321 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
1322 ROLLBACK TO delete_all_rs_values_pvt;
1323 x_return_status := fnd_api.g_ret_sts_unexp_error;
1324 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1325 WHEN OTHERS THEN
1326 --DBMS_OUTPUT.put_line (' ========================================== ');
1327 --DBMS_OUTPUT.put_line (' =========== Raised Others in Delete All Resource Values Pvt=============');
1328 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1329 ROLLBACK TO delete_all_rs_values_pvt;
1330 x_return_status := fnd_api.g_ret_sts_unexp_error;
1331 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1332
1333 END delete_all_rs_resource_values;
1334
1335 --Procedure to Get Resource Values based on the input values passed by the calling routines
1336
1337 PROCEDURE GET_RS_RESOURCE_VALUES(
1338 P_Api_Version IN NUMBER,
1339 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1340 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1341 P_resource_id IN NUMBER,
1342 P_value_type IN VARCHAR2,
1343 p_resource_param_id IN NUMBER,
1344 x_resource_param_value_id OUT NOCOPY NUMBER,
1345 x_value OUT NOCOPY VARCHAR2,
1346 X_Return_Status OUT NOCOPY VARCHAR2,
1347 X_Msg_Count OUT NOCOPY NUMBER,
1348 X_Msg_Data OUT NOCOPY VARCHAR2
1349 )IS
1350 l_api_version CONSTANT NUMBER := 1.0;
1351 l_api_name CONSTANT VARCHAR2(30) := 'GET_RS_RESOURCE_VALUES';
1352 l_resource_id NUMBER := P_RESOURCE_ID;
1353 l_value_type VARCHAR2(30) := P_VALUE_TYPE;
1354 l_resource_param_id NUMBER := P_RESOURCE_PARAM_ID;
1355 -- addded for NOCOPY
1356 l_resource_id_out NUMBER;
1357
1358
1359 CURSOR c_rs_resource_values IS
1360 SELECT resource_param_value_id, value
1361 FROM jtf_rs_resource_values
1362 WHERE resource_param_id = l_resource_param_id
1363 AND resource_id = l_resource_id
1364 AND ( (value_type = l_value_type) OR (l_value_type is null) );
1365
1366 BEGIN
1367 SAVEPOINT get_rs_resource_values_pvt;
1368 x_return_status := fnd_api.g_ret_sts_success;
1369 --DBMS_OUTPUT.put_line(' Started Get Resources Values Pvt ');
1370 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1371 RAISE fnd_api.g_exc_unexpected_error;
1372 END IF;
1373 IF fnd_api.to_boolean(p_init_msg_list) THEN
1374 fnd_msg_pub.initialize;
1375 END IF;
1376
1377 --Put all Validations here
1378
1379 --Initialize API return status to success
1380 x_return_status := FND_API.G_RET_STS_SUCCESS;
1381
1382 --Validate the Resource Id
1383 jtf_resource_utl.validate_resource_number(
1384 p_resource_id => l_resource_id,
1385 p_resource_number => null,
1386 x_return_status => x_return_status,
1387 x_resource_id => l_resource_id_out
1388 );
1389 -- added for NOCOPY
1390 l_resource_id := l_resource_id_out;
1391
1392 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1393 x_return_status := fnd_api.g_ret_sts_unexp_error;
1394 RAISE fnd_api.g_exc_unexpected_error;
1395 END IF;
1396 --End of Resource Id Validation
1397
1398 --Validate the Resource Param Id
1399 jtf_resource_utl.validate_resource_param_id(
1400 p_resource_param_id => l_resource_param_id,
1401 x_return_status => x_return_status
1402 );
1403 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1404 x_return_status := fnd_api.g_ret_sts_unexp_error;
1405 RAISE fnd_api.g_exc_unexpected_error;
1406 END IF;
1407 --End of Resource Param Id Validation
1408
1409 --Validate the Value Type
1410 IF l_value_type <> FND_API.G_MISS_CHAR THEN
1411 IF l_value_type IS NOT NULL THEN
1412 jtf_resource_utl.validate_rs_value_type(
1413 p_resource_param_id => l_resource_param_id,
1414 p_value_type => l_value_type,
1415 x_return_status => x_return_status
1416 );
1417 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1418 x_return_status := fnd_api.g_ret_sts_unexp_error;
1419 RAISE fnd_api.g_exc_unexpected_error;
1420 END IF;
1421 END IF;
1422 END IF;
1423 --End of Value Type Validation
1424
1425 OPEN c_rs_resource_values;
1426 FETCH c_rs_resource_values into x_resource_param_value_id, x_value;
1427 IF c_rs_resource_values%NOTFOUND THEN
1428 CLOSE c_rs_resource_values;
1429 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RS_PRM_ID');
1430 fnd_message.set_token('P_RESOURCE_PARAM_ID', p_resource_param_id);
1431 fnd_msg_pub.add;
1432 x_return_status := fnd_api.g_ret_sts_unexp_error;
1433 RAISE fnd_api.g_exc_unexpected_error;
1434 END IF;
1435 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1436
1437 EXCEPTION
1438 WHEN fnd_api.g_exc_unexpected_error THEN
1439 --DBMS_OUTPUT.put_line (' ========================================== ');
1440 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
1441 ROLLBACK TO get_rs_resource_values_pvt;
1442 x_return_status := fnd_api.g_ret_sts_unexp_error;
1443 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1444 WHEN OTHERS THEN
1445 --DBMS_OUTPUT.put_line (' ========================================== ');
1446 --DBMS_OUTPUT.put_line (' =========== Raised Others in Get Resource Values Pvt =============');
1447 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1448 ROLLBACK TO get_rs_resource_values_pvt;
1449 x_return_status := fnd_api.g_ret_sts_unexp_error;
1450 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1451
1452 END get_rs_resource_values;
1453
1454 --Procedure to Get Resource Param List for the given Application Id and Param Type
1455
1456 PROCEDURE GET_RS_RESOURCE_PARAM_LIST(
1457 P_Api_Version IN NUMBER,
1458 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1459 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1460 P_APPLICATION_ID IN NUMBER,
1461 X_Return_Status OUT NOCOPY VARCHAR2,
1462 X_Msg_Count OUT NOCOPY NUMBER,
1463 X_Msg_Data OUT NOCOPY VARCHAR2,
1464 X_RS_PARAM_Table OUT NOCOPY JTF_RS_RESOURCE_VALUES_PUB.RS_PARAM_LIST_TBL_TYPE,
1465 X_No_Record OUT NOCOPY NUMBER
1466 )IS
1467 l_api_version CONSTANT NUMBER := 1.0;
1468 l_api_name CONSTANT VARCHAR2(30) := 'GET_RS_RESOURCE_PARAM_LIST';
1469 l_rs_param_rec JTF_RS_RESOURCE_VALUES_PUB.RS_PARAM_LIST_REC_TYPE;
1470 l_tbl_counter NUMBER := 0;
1471 l_APPLICATION_ID NUMBER := P_APPLICATION_ID;
1472
1473 CURSOR c_rs_resource_param_list IS
1474 SELECT jrspm.resource_param_id, fnl.meaning, jrspm.type, jrspm.domain_lookup_type
1475 FROM jtf_rs_resource_params jrspm, fnd_lookups fnl
1476 WHERE jrspm.application_id = l_application_id
1477 AND jrspm.name = fnl.lookup_code
1478 AND fnl.lookup_type = 'IEM_AGENT_PARAMS';
1479 BEGIN
1480 SAVEPOINT get_rs_param_list_pvt;
1481 x_return_status := fnd_api.g_ret_sts_success;
1482 --DBMS_OUTPUT.put_line(' Started Get Resources Param List Pvt ');
1483 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1484 RAISE fnd_api.g_exc_unexpected_error;
1485 END IF;
1486 IF fnd_api.to_boolean(p_init_msg_list) THEN
1487 fnd_msg_pub.initialize;
1488 END IF;
1489
1490 --Put all Validations here
1491
1492 --Initialize message return status to success
1493 x_return_status := FND_API.G_RET_STS_SUCCESS;
1494
1495 OPEN c_rs_resource_param_list;
1496 FETCH c_rs_resource_param_list into l_rs_param_rec;
1497 WHILE c_rs_resource_param_list%FOUND LOOP
1498 l_tbl_counter := l_tbl_counter+1;
1499 x_rs_param_table(l_tbl_counter):= l_rs_param_rec;
1500 FETCH c_rs_resource_param_list into l_rs_param_rec;
1501 END LOOP;
1502 CLOSE c_rs_resource_param_list;
1503 x_no_record :=l_tbl_counter;
1504
1505 IF fnd_api.to_boolean(p_commit) THEN
1506 COMMIT WORK;
1507 END IF;
1508 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1509
1510 EXCEPTION
1511 WHEN fnd_api.g_exc_unexpected_error THEN
1512 --DBMS_OUTPUT.put_line (' ========================================== ');
1513 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
1514 ROLLBACK TO get_rs_param_list_pvt;
1515 x_return_status := fnd_api.g_ret_sts_unexp_error;
1516 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1517 WHEN OTHERS THEN
1518 --DBMS_OUTPUT.put_line (' ========================================== ');
1519 --DBMS_OUTPUT.put_line (' =========== Raised Others in Get Resource Param List Pvt =============');
1520 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1521 ROLLBACK TO get_rs_param_list_pvt;
1522 x_return_status := fnd_api.g_ret_sts_unexp_error;
1523 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1524
1525 END get_rs_resource_param_list;
1526
1527 End JTF_RS_RESOURCE_VALUES_PVT;