[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RESOURCE_VALUES_PUB
Source
1 PACKAGE BODY jtf_rs_resource_values_pub AS
2 /* $Header: jtfrspcb.pls 120.0 2005/05/11 08:21:05 appldev ship $ */
3
4 /*****************************************************************************************
5 This is a public API that caller will invoke.
6 It provides procedures for managing resource salesrep territories, like
7 create, update and delete resource salesrep territories from other modules.
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 procedures from jtf_rs_resource_values_pvt
16 to do business validations and to do actual inserts, updates and deletes into tables.
17 ******************************************************************************************/
18
19 --Package variables
20 G_PKG_NAME VARCHAR2(30) := 'JTF_RESOURCE_PARAMS_PUB';
21
22 --Procedure to Create Resource Values based on the input values provided by the calling routines
23
24 PROCEDURE CREATE_RS_RESOURCE_VALUES(
25 P_Api_Version IN NUMBER,
26 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
27 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
28 P_resource_id IN NUMBER,
29 p_resource_param_id IN NUMBER,
30 p_value IN VARCHAR2,
31 P_value_type IN VARCHAR2 DEFAULT NULL,
32 X_Return_Status OUT NOCOPY VARCHAR2,
33 X_Msg_Count OUT NOCOPY NUMBER,
34 X_Msg_Data OUT NOCOPY VARCHAR2,
35 X_resource_param_value_id OUT NOCOPY NUMBER
36 )IS
37 l_api_version CONSTANT NUMBER := 1.0;
38 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RS_RESOURCE_VALUE';
39 l_resource_id NUMBER := p_resource_id;
40 l_resource_param_id NUMBER := p_resource_param_id;
41 l_value VARCHAR2(255) := p_value;
42 l_value_type VARCHAR2(30) := p_value_type;
43 l_resource_param_value_id NUMBER;
44
45 l_resource_id_out NUMBER;
46
47 BEGIN
48
49 SAVEPOINT create_rs_resource_values_pub;
50 x_return_status := fnd_api.g_ret_sts_success;
51 --DBMS_OUTPUT.put_line(' Started Create Resources Values Pub ');
52
53 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
54 RAISE fnd_api.g_exc_unexpected_error;
55 END IF;
56
57 IF fnd_api.to_boolean(p_init_msg_list) THEN
58 fnd_msg_pub.initialize;
59 END IF;
60
61 --Put in all the Validations here
62
63 --Validate the Resource Id
64 jtf_resource_utl.validate_resource_number(
65 p_resource_id => l_resource_id,
66 p_resource_number => null,
67 x_return_status => x_return_status,
68 x_resource_id => l_resource_id_out
69 );
70 -- added for NOCOPY
71 l_resource_id := l_resource_id_out;
72
73 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
74 x_return_status := fnd_api.g_ret_sts_unexp_error;
75 RAISE fnd_api.g_exc_unexpected_error;
76 END IF;
77 --End of Resource Id Validation
78
79 --Validate the Resource Param Id
80 jtf_resource_utl.validate_resource_param_id(
81 p_resource_param_id => l_resource_param_id,
82 x_return_status => x_return_status
83 );
84 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
85 x_return_status := fnd_api.g_ret_sts_unexp_error;
86 RAISE fnd_api.g_exc_unexpected_error;
87 END IF;
88 --End of Resource Param Id Validation
89
90 --Call the Private API for create_rs_resource_values
91 jtf_rs_resource_values_pvt.create_rs_resource_values(
92 P_API_VERSION => 1,
93 P_INIT_MSG_LIST => fnd_api.g_false,
94 P_COMMIT => fnd_api.g_false,
95 P_resource_id => l_resource_id,
96 p_resource_param_id => l_resource_param_id,
97 p_value => l_value,
98 P_value_type => l_value_type,
99 X_RETURN_STATUS => x_return_status,
100 X_MSG_COUNT => x_msg_count,
101 X_MSG_DATA => x_msg_data,
102 X_resource_param_value_id => l_resource_param_value_id
103 );
104 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
105 --dbms_output.put_line('Failed status from call to private procedure');
106 RAISE fnd_api.g_exc_unexpected_error;
107 END IF;
108 IF fnd_api.to_boolean(p_commit) THEN
109 COMMIT WORK;
110 END IF;
111
112 x_resource_param_value_id := l_resource_param_value_id;
113
114 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
115
116 EXCEPTION
117 WHEN fnd_api.g_exc_unexpected_error THEN
118 --DBMS_OUTPUT.put_line (' ========================================== ');
119 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
120 ROLLBACK TO create_rs_resource_values_pub;
121 x_return_status := fnd_api.g_ret_sts_unexp_error;
122 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
123 WHEN OTHERS THEN
124 --DBMS_OUTPUT.put_line (' ========================================== ');
125 --DBMS_OUTPUT.put_line (' =========== Raised Others in Create Resource Values Pub ==============');
126 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
127 ROLLBACK TO create_rs_resource_values_pub;
128 x_return_status := fnd_api.g_ret_sts_unexp_error;
129 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
130
131 END create_rs_resource_values;
132
133 --Procedure to Update Resource Values based on the input values passed by the calling routines
134
135 PROCEDURE UPDATE_RS_RESOURCE_VALUES(
136 P_Api_Version IN NUMBER,
137 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
138 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
139 p_resource_param_value_id IN NUMBER,
140 p_resource_id IN NUMBER,
141 p_resource_param_id IN NUMBER,
142 p_value IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
143 p_value_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
144 p_object_version_number IN OUT NOCOPY JTF_RS_RESOURCE_VALUES.OBJECT_VERSION_NUMBER%TYPE,
145 X_Return_Status OUT NOCOPY VARCHAR2,
146 X_Msg_Count OUT NOCOPY NUMBER,
147 X_Msg_Data OUT NOCOPY VARCHAR2
148 )IS
149
150 l_api_version CONSTANT NUMBER := 1.0;
151 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RS_RESOURCE_VALUES';
152 l_resource_id NUMBER := p_resource_id;
153 l_resource_param_id NUMBER := p_resource_param_id;
154 l_value VARCHAR2(255) := p_value;
155 l_value_type VARCHAR2(30) := p_value_type;
156 l_object_version_number NUMBER := p_object_version_number;
157 l_resource_param_value_id NUMBER := p_resource_param_value_id;
158 l_resource_id_out NUMBER;
159
160 BEGIN
161
162 SAVEPOINT update_rs_resource_values_pub;
163 x_return_status := fnd_api.g_ret_sts_success;
164 --DBMS_OUTPUT.put_line(' Started Update Resources Values Pub ');
165
166 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
167 RAISE fnd_api.g_exc_unexpected_error;
168 END IF;
169
170 IF fnd_api.to_boolean(p_init_msg_list) THEN
171 fnd_msg_pub.initialize;
172 END IF;
173
174 --Put all Validations here
175
176 --Validate the Resource Id
177 jtf_resource_utl.validate_resource_number(
178 p_resource_id => l_resource_id,
179 p_resource_number => null,
180 x_return_status => x_return_status,
181 x_resource_id => l_resource_id_out
182 );
183
184 l_resource_id := l_resource_id_out;
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 RAISE fnd_api.g_exc_unexpected_error;
189 END IF;
190 --End of Resource Id Validation
191
192 --Validate the Resource Param Id
193 jtf_resource_utl.validate_resource_param_id(
194 p_resource_param_id => l_resource_param_id,
195 x_return_status => x_return_status
196 );
197 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
198 x_return_status := fnd_api.g_ret_sts_unexp_error;
199 RAISE fnd_api.g_exc_unexpected_error;
200 END IF;
201 --End of Resource Param Id Validation
202
203 --Validate the Value Type
204 IF l_value_type <> FND_API.G_MISS_CHAR THEN
205 IF l_value_type IS NOT NULL THEN
206 jtf_resource_utl.validate_rs_value_type(
207 p_resource_param_id => l_resource_param_id,
208 p_value_type => l_value_type,
209 x_return_status => x_return_status
210 );
211 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
212 x_return_status := fnd_api.g_ret_sts_unexp_error;
213 RAISE fnd_api.g_exc_unexpected_error;
214 END IF;
215 END IF;
216 END IF;
217 --End of Value Type Validation
218
219 --Get Resource Param Value Id from the database
220 SELECT resource_param_value_id
221 INTO l_resource_param_value_id
222 FROM jtf_rs_resource_values
223 WHERE resource_id = l_resource_id
224 AND resource_param_id = l_resource_param_id
225 AND value_type = l_value_type;
226
227 --Call the private procedure for update
228 jtf_rs_resource_values_pvt.update_rs_resource_values(
229 P_API_VERSION => 1,
230 P_INIT_MSG_LIST => fnd_api.g_false,
231 P_COMMIT => fnd_api.g_false,
232 p_resource_param_value_id => l_resource_param_value_id,
233 p_value => l_value,
234 p_object_version_number => l_object_version_number,
235 X_RETURN_STATUS => x_return_status,
236 X_MSG_COUNT => x_msg_count,
237 X_MSG_DATA => x_msg_data
238 );
239
240 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
241 --dbms_output.put_line('Failed status from call to private procedure');
242 RAISE fnd_api.g_exc_unexpected_error;
243 END IF;
244 IF fnd_api.to_boolean(p_commit) THEN
245 COMMIT WORK;
246 END IF;
247 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
248
249 EXCEPTION
250 WHEN fnd_api.g_exc_unexpected_error THEN
251 --DBMS_OUTPUT.put_line (' ========================================== ');
252 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
253 ROLLBACK TO update_rs_resource_values_pub;
254 x_return_status := fnd_api.g_ret_sts_unexp_error;
255 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
256 WHEN OTHERS THEN
257 --DBMS_OUTPUT.put_line (' ========================================== ');
258 --DBMS_OUTPUT.put_line (' =========== Raised Others in Update Resource Values Pub ============= ');
259 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
260 ROLLBACK TO update_rs_resource_values_pub;
261 x_return_status := fnd_api.g_ret_sts_unexp_error;
262 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
263
264 END update_rs_resource_values;
265
266 /* Procedure to Delete Resource Values based on the
267 input values provided by the calling routines */
268
269 PROCEDURE DELETE_RS_RESOURCE_VALUES(
270 P_Api_Version IN NUMBER,
271 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
272 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
273 p_resource_param_value_id IN NUMBER,
274 p_object_version_number IN JTF_RS_RESOURCE_VALUES.OBJECT_VERSION_NUMBER%TYPE,
275 X_Return_Status OUT NOCOPY VARCHAR2,
276 X_Msg_Count OUT NOCOPY NUMBER,
277 X_Msg_Data OUT NOCOPY VARCHAR2
278 )IS
279
280 l_api_version CONSTANT NUMBER := 1.0;
281 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RS_RESOURCE_VALUES';
282 l_resource_param_value_id NUMBER := p_resource_param_value_id;
283 l_object_version_number NUMBER := p_object_version_number;
284 BEGIN
285
286 SAVEPOINT delete_rs_resource_values_pub;
287 x_return_status := fnd_api.g_ret_sts_success;
288 --DBMS_OUTPUT.put_line(' Started Delete Resources Values Pub ');
289
290 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
291 RAISE fnd_api.g_exc_unexpected_error;
292 END IF;
293
294 IF fnd_api.to_boolean(p_init_msg_list) THEN
295 fnd_msg_pub.initialize;
296 END IF;
297
298 --Put all Validations here
299
300 --Call the private procedure for delete
301
302 jtf_rs_resource_values_pvt.delete_rs_resource_values(
303 P_API_VERSION => 1,
304 P_INIT_MSG_LIST => fnd_api.g_false,
305 P_COMMIT => fnd_api.g_false,
306 P_RESOURCE_PARAM_VALUE_ID => l_resource_param_value_id,
307 P_OBJECT_VERSION_NUMBER => l_object_version_number,
308 X_RETURN_STATUS => x_return_status,
309 X_MSG_COUNT => x_msg_count,
310 X_MSG_DATA => x_msg_data
311 );
312
313 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
314 --dbms_output.put_line('Failed status from call to private procedure');
315 RAISE fnd_api.g_exc_unexpected_error;
316 END IF;
317 IF fnd_api.to_boolean(p_commit) THEN
318 COMMIT WORK;
319 END IF;
320 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
321
322 EXCEPTION
323 WHEN fnd_api.g_exc_unexpected_error THEN
324 --DBMS_OUTPUT.put_line (' ========================================== ');
325 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
326 ROLLBACK TO delete_rs_resource_values_pub;
327 x_return_status := fnd_api.g_ret_sts_unexp_error;
328 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
329 WHEN OTHERS THEN
330 --DBMS_OUTPUT.put_line (' ========================================== ');
331 --DBMS_OUTPUT.put_line (' =========== Raised Others in Delete Resource Values Pub ============= ');
332 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
333 ROLLBACK TO delete_rs_resource_values_pub;
334 x_return_status := fnd_api.g_ret_sts_unexp_error;
335 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
336
337 END delete_rs_resource_values;
338
339 /* Procedure to Delete all Resource Values based on the
340 Resource Id provided by the calling routines */
341
342 PROCEDURE DELETE_ALL_RS_RESOURCE_VALUES(
343 P_Api_Version IN NUMBER,
344 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
345 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
346 p_resource_id IN NUMBER,
347 X_Return_Status OUT NOCOPY VARCHAR2,
348 X_Msg_Count OUT NOCOPY NUMBER,
349 X_Msg_Data OUT NOCOPY VARCHAR2
350 )IS
351
352 l_api_version CONSTANT NUMBER := 1.0;
353 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ALL_RS_RESOURCE_VALUES';
354 l_resource_id NUMBER := p_resource_id;
355 l_object_version_number NUMBER;
356
357 BEGIN
358
359 SAVEPOINT delete_all_rs_values_pub;
360 x_return_status := fnd_api.g_ret_sts_success;
361 --DBMS_OUTPUT.put_line(' Started Delete All Resources Values Pub ');
362
363 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
364 RAISE fnd_api.g_exc_unexpected_error;
365 END IF;
366
367 IF fnd_api.to_boolean(p_init_msg_list) THEN
368 fnd_msg_pub.initialize;
369 END IF;
370
371 --Put all Validations here
372
373 --Call the private procedure for delete
374
375 jtf_rs_resource_values_pvt.delete_all_rs_resource_values(
376 P_API_VERSION => 1,
377 P_INIT_MSG_LIST => fnd_api.g_false,
378 P_COMMIT => fnd_api.g_false,
379 P_RESOURCE_ID => l_resource_id,
380 X_RETURN_STATUS => x_return_status,
381 X_MSG_COUNT => x_msg_count,
382 X_MSG_DATA => x_msg_data
383 );
384
385 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
386 --dbms_output.put_line('Failed status from call to private procedure');
387 RAISE fnd_api.g_exc_unexpected_error;
388 END IF;
389 IF fnd_api.to_boolean(p_commit) THEN
390 COMMIT WORK;
391 END IF;
392 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
393
394 EXCEPTION
395 WHEN fnd_api.g_exc_unexpected_error THEN
396 --DBMS_OUTPUT.put_line (' ========================================== ');
397 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
398 ROLLBACK TO delete_all_rs_values_pub;
399 x_return_status := fnd_api.g_ret_sts_unexp_error;
400 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
401 WHEN OTHERS THEN
402 --DBMS_OUTPUT.put_line (' ========================================== ');
403 --DBMS_OUTPUT.put_line (' =========== Raised Others in Delete All Resource Values Pub =============');
404 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
405 ROLLBACK TO delete_all_rs_values_pub;
406 x_return_status := fnd_api.g_ret_sts_unexp_error;
407 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
408
409 END delete_all_rs_resource_values;
410
411 /* Procedure to Get Resource Values based on the
412 input values passed by the calling routines */
413
414 PROCEDURE GET_RS_RESOURCE_VALUES(
415 P_Api_Version IN NUMBER,
416 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
417 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
418 P_resource_id IN NUMBER,
419 P_value_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
420 p_resource_param_id IN NUMBER,
421 x_resource_param_value_id OUT NOCOPY NUMBER,
422 x_value OUT NOCOPY VARCHAR2,
423 X_Return_Status OUT NOCOPY VARCHAR2,
424 X_Msg_Count OUT NOCOPY NUMBER,
425 X_Msg_Data OUT NOCOPY VARCHAR2
426 )IS
427
428 l_api_version CONSTANT NUMBER := 1.0;
429 l_api_name CONSTANT VARCHAR2(30) := 'GET_RS_RESOURCE_VALUES';
430 l_resource_id NUMBER := p_resource_id;
431 l_value_type VARCHAR2(30) := p_value_type;
432 l_resource_param_id NUMBER := p_resource_param_id;
433 l_resource_param_value_id NUMBER;
434 l_value VARCHAR2(255);
435
436 l_resource_id_out NUMBER;
437
438 BEGIN
439 SAVEPOINT get_rs_resource_values_pub;
440 x_return_status := fnd_api.g_ret_sts_success;
441 --DBMS_OUTPUT.put_line(' Started Get Resources Values Pub ');
442
443 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
444 RAISE fnd_api.g_exc_unexpected_error;
445 END IF;
446
447 IF fnd_api.to_boolean(p_init_msg_list) THEN
448 fnd_msg_pub.initialize;
449 END IF;
450
451 --Put all Validations here
452
453 /* Validate the Resource Id. */
454 jtf_resource_utl.validate_resource_number(
455 p_resource_id => l_resource_id,
456 p_resource_number => null,
457 x_return_status => x_return_status,
458 x_resource_id => l_resource_id_out
459 );
460 -- added for NOCOPY
461 l_resource_id := l_resource_id_out;
462
463 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
464 x_return_status := fnd_api.g_ret_sts_unexp_error;
465 RAISE fnd_api.g_exc_unexpected_error;
466 END IF;
467 /* End of Resource Id Validation */
468
469 /* Validate the Resource Param Id. */
470 jtf_resource_utl.validate_resource_param_id(
471 p_resource_param_id => l_resource_param_id,
472 x_return_status => x_return_status
473 );
474 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
475 x_return_status := fnd_api.g_ret_sts_unexp_error;
476 RAISE fnd_api.g_exc_unexpected_error;
477 END IF;
478 /* End of Resource Param Id Validation */
479
480 --Call the private procedure for get
481
482 jtf_rs_resource_values_pvt.get_rs_resource_values
483 (P_API_VERSION => 1,
484 P_INIT_MSG_LIST => fnd_api.g_false,
485 P_COMMIT => fnd_api.g_false,
486 P_resource_id => l_resource_id,
487 P_value_type => l_value_type,
488 p_resource_param_id => l_resource_param_id,
489 x_resource_param_value_id => x_resource_param_value_id,
490 x_value => x_value,
491 X_RETURN_STATUS => x_return_status,
492 X_MSG_COUNT => x_msg_count,
493 X_MSG_DATA => x_msg_data
494 );
495
496 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
497 --dbms_output.put_line('Failed status from call to private procedure');
498 RAISE fnd_api.g_exc_unexpected_error;
499 END IF;
500 IF fnd_api.to_boolean(p_commit) THEN
501 COMMIT WORK;
502 END IF;
503 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
504
505 EXCEPTION
506 WHEN fnd_api.g_exc_unexpected_error THEN
507 --DBMS_OUTPUT.put_line (' ========================================== ');
508 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
509 ROLLBACK TO get_rs_resource_values_pub;
510 x_return_status := fnd_api.g_ret_sts_unexp_error;
511 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
512 WHEN OTHERS THEN
513 --DBMS_OUTPUT.put_line (' ========================================== ');
514 --DBMS_OUTPUT.put_line (' =========== Raised Others in Get Resource Values Pub =============');
515 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
516 ROLLBACK TO get_rs_resource_values_pub;
517 x_return_status := fnd_api.g_ret_sts_unexp_error;
518 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
519
520 END get_rs_resource_values;
521
522 --Procedure to Get Resource Param List for the given Application Id and Param Type
523
524 PROCEDURE GET_RS_RESOURCE_PARAM_LIST(
525 P_Api_Version IN NUMBER,
526 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
527 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
528 P_APPLICATION_ID IN NUMBER,
529 X_Return_Status OUT NOCOPY VARCHAR2,
530 X_Msg_Count OUT NOCOPY NUMBER,
531 X_Msg_Data OUT NOCOPY VARCHAR2,
532 X_RS_PARAM_Table OUT NOCOPY RS_PARAM_LIST_TBL_TYPE,
533 X_No_Record OUT NOCOPY Number
534 ) IS
535
536 l_api_version CONSTANT NUMBER := 1.0;
537 l_api_name CONSTANT VARCHAR2(30) := 'GET_RS_RESOURCE_PARAM_LIST';
538 l_APPLICATION_ID NUMBER := p_application_id;
539
540 BEGIN
541 SAVEPOINT get_rs_param_list_pub;
542 x_return_status := fnd_api.g_ret_sts_success;
543 --DBMS_OUTPUT.put_line(' Started Get Resources Param List Pub ');
544
545 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
546 RAISE fnd_api.g_exc_unexpected_error;
547 END IF;
548
549 IF fnd_api.to_boolean(p_init_msg_list) THEN
550 fnd_msg_pub.initialize;
551 END IF;
552
553 --Put all Validations here
554
555 --Call the private procedure for get
556
557 jtf_rs_resource_values_pvt.get_rs_resource_param_list
558 (P_API_VERSION => 1,
559 P_INIT_MSG_LIST => fnd_api.g_false,
560 P_COMMIT => fnd_api.g_false,
561 P_APPLICATION_ID => l_application_id,
562 X_RETURN_STATUS => x_return_status,
563 X_MSG_COUNT => x_msg_count,
564 X_MSG_DATA => x_msg_data,
565 X_RS_PARAM_TABLE => x_rs_param_table,
566 X_NO_RECORD => x_no_record
567 );
568
569 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
570 --dbms_output.put_line('Failed status from call to private procedure');
571 RAISE fnd_api.g_exc_unexpected_error;
572 END IF;
573 IF fnd_api.to_boolean(p_commit) THEN
574 COMMIT WORK;
575 END IF;
576 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
577
578 EXCEPTION
579 WHEN fnd_api.g_exc_unexpected_error THEN
580 --DBMS_OUTPUT.put_line (' ========================================== ');
581 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
582 ROLLBACK TO get_rs_param_list_pub;
583 x_return_status := fnd_api.g_ret_sts_unexp_error;
584 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
585 WHEN OTHERS THEN
586 --DBMS_OUTPUT.put_line (' ========================================== ');
587 --DBMS_OUTPUT.put_line (' =========== Raised Others in Get Resource Param List Pub =============');
588 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
589 ROLLBACK TO get_rs_param_list_pub;
590 x_return_status := fnd_api.g_ret_sts_unexp_error;
591 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
592
593 END get_rs_resource_param_list;
594
595 End JTF_RS_RESOURCE_VALUES_PUB;