[Home] [Help]
PACKAGE BODY: APPS.JTS_CONFIGURATION_PVT
Source
1 PACKAGE BODY JTS_CONFIGURATION_PVT as
2 /* $Header: jtsvcfgb.pls 115.12 2002/06/07 11:53:25 pkm ship $ */
3
4
5 -- --------------------------------------------------------------------
6 -- Package name : JTS_CONFIGURATION_PVT
7 -- Purpose : Configurations.
8 -- History : 21-Feb-02 SHuh Created.
9 -- 06-Jun-02 SHUH DELETE_ROW moved to JTS_CONFIGURATIONS_PKG
10 -- --------------------------------------------------------------------
11
12 -- get next sequence for configuration id
13 FUNCTION GET_NEXT_CONFIG_ID RETURN NUMBER IS
14 l_new_config_id JTS_CONFIGURATIONS_B.configuration_id%TYPE;
15 BEGIN
16 SELECT jts_configurations_b_s.nextval
17 INTO l_new_config_id
18 FROM sys.dual;
19
20 return (l_new_config_id);
21 EXCEPTION
22 WHEN OTHERS THEN
23 APP_EXCEPTION.RAISE_EXCEPTION;
24 END GET_NEXT_CONFIG_ID;
25
26 FUNCTION GET_CONFIG_ID(p_config_name IN VARCHAR2) RETURN NUMBER IS
27 l_config_id JTS_CONFIGURATIONS_B.configuration_id%TYPE;
28 BEGIN
29 SELECT configuration_id
30 INTO l_config_id
31 FROM jts_configurations_b
32 WHERE config_name = p_config_name;
33
34 return l_config_id;
35 EXCEPTION
36 WHEN OTHERS THEN
37 return NULL;
38 END GET_CONFIG_ID;
39
40 FUNCTION GET_CONFIG_NAME(p_config_id IN NUMBER) RETURN VARCHAR2 IS
41 l_config_name JTS_CONFIGURATIONS_B.config_name%TYPE;
42 BEGIN
43 SELECT config_name
44 INTO l_config_name
45 FROM jts_configurations_b
46 WHERE configuration_id = p_config_id;
47
48 return l_config_name;
49 EXCEPTION
50 WHEN OTHERS THEN
51 return NULL;
52 END GET_CONFIG_NAME;
53
54 -- Inserts a row into jts_configurations table
55 PROCEDURE INSERT_ROW(p_config_rec IN Config_Rec_Type,
56 x_config_id OUT NUMBER
57 ) IS
58 BEGIN
59
60 x_config_id := GET_NEXT_CONFIG_ID;
61
62 insert into JTS_CONFIGURATIONS_B (
63 CONFIGURATION_ID,
64 CONFIG_NAME,
65 FLOW_ID,
66 RECORD_MODE,
67 OBJECT_VERSION_NUMBER,
68 ATTRIBUTE_CATEGORY,
69 ATTRIBUTE1,
70 ATTRIBUTE2,
71 ATTRIBUTE3,
72 ATTRIBUTE4,
73 ATTRIBUTE5,
74 ATTRIBUTE6,
75 ATTRIBUTE7,
76 ATTRIBUTE8,
77 ATTRIBUTE9,
78 ATTRIBUTE10,
79 ATTRIBUTE11,
80 ATTRIBUTE12,
81 ATTRIBUTE13,
82 ATTRIBUTE14,
83 ATTRIBUTE15,
84 CREATION_DATE,
85 CREATED_BY,
86 LAST_UPDATE_DATE,
87 LAST_UPDATED_BY,
88 LAST_UPDATE_LOGIN
89 ) values (
90 X_CONFIG_ID,
91 p_config_rec.CONFIG_NAME,
92 p_config_rec.FLOW_ID,
93 p_config_rec.RECORD_MODE,
94 1,
95 p_config_rec.ATTRIBUTE_CATEGORY,
96 p_config_rec.ATTRIBUTE1,
97 p_config_rec.ATTRIBUTE2,
98 p_config_rec.ATTRIBUTE3,
99 p_config_rec.ATTRIBUTE4,
100 p_config_rec.ATTRIBUTE5,
101 p_config_rec.ATTRIBUTE6,
102 p_config_rec.ATTRIBUTE7,
103 p_config_rec.ATTRIBUTE8,
104 p_config_rec.ATTRIBUTE9,
105 p_config_rec.ATTRIBUTE10,
106 p_config_rec.ATTRIBUTE11,
107 p_config_rec.ATTRIBUTE12,
108 p_config_rec.ATTRIBUTE13,
109 p_config_rec.ATTRIBUTE14,
110 p_config_rec.ATTRIBUTE15,
111 sysdate,
112 FND_GLOBAL.user_id,
113 sysdate,
114 FND_GLOBAL.user_id,
115 FND_GLOBAL.user_id
116 );
117
118 insert into JTS_CONFIGURATIONS_TL (
119 CONFIGURATION_ID,
120 DESCRIPTION,
121 CREATION_DATE,
122 CREATED_BY,
123 LAST_UPDATE_DATE,
124 LAST_UPDATED_BY,
125 LAST_UPDATE_LOGIN,
126 LANGUAGE,
127 SOURCE_LANG
128 ) select
129 X_CONFIG_ID,
130 p_config_rec.DESCRIPTION,
131 sysdate,
132 FND_GLOBAL.user_id,
133 sysdate,
134 FND_GLOBAL.user_id,
135 FND_GLOBAL.user_id,
136 L.LANGUAGE_CODE,
137 userenv('LANG')
138 from FND_LANGUAGES L
139 where L.INSTALLED_FLAG in ('I', 'B')
140 and not exists
141 (select NULL
142 from JTS_CONFIGURATIONS_TL T
143 where T.CONFIGURATION_ID = X_CONFIG_ID
144 and T.LANGUAGE = L.LANGUAGE_CODE);
145
146 EXCEPTION
147 WHEN OTHERS THEN
148 APP_EXCEPTION.RAISE_EXCEPTION;
149 END INSERT_ROW;
150
151 -- Checks for Uniqueness of configuration name against existing -- rows
152 FUNCTION CHECK_CONFIG_NAME_UNIQUE(p_config_name IN VARCHAR2,
153 p_config_id IN NUMBER
154 ) return BOOLEAN IS
155 l_count NUMBER := 0;
156 l_config_id JTS_CONFIGURATIONS_B.config_name%TYPE;
157 BEGIN
158 SELECT count(*)
159 INTO l_count
160 FROM jts_configurations_b
161 WHERE config_name = p_config_name;
162
163 IF l_count = 0 THEN
164 return TRUE;
165 ELSIF l_count = 1 THEN
166 l_config_id := get_config_id(p_config_name);
167
168 IF (p_config_id IS NOT NULL AND p_config_id = l_config_id) THEN
169 return TRUE;
170 END IF;
171 return FALSE;
172 ELSE
173 return FALSE;
174 END IF;
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 APP_EXCEPTION.RAISE_EXCEPTION;
179 END CHECK_CONFIG_NAME_UNIQUE;
180
181 --
182 -- Check if the selected flow exists in the tables. If it
183 -- doesn't, this is an unexpected error (programmer or setup
184 -- error)
185 FUNCTION CHECK_FLOW_EXISTS(p_flow_id IN NUMBER) return BOOLEAN IS
186 l_count NUMBER := 0;
187 BEGIN
188
189 SELECT count(*)
190 INTO l_count
191 FROM jts_setup_flows_b
192 WHERE flow_id = p_flow_id;
193
194 IF l_count = 0 THEN
195 return FALSE;
196 ELSE
197 return TRUE;
198 END IF;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 APP_EXCEPTION.RAISE_EXCEPTION;
203 END CHECK_FLOW_EXISTS;
204
205 -- Performs Validation
206 -- Checks for: Unique Configuration Name
207 --
208 PROCEDURE VALIDATE_ROW(p_api_version IN NUMBER,
209 p_configuration_rec IN Config_Rec_Type,
210 x_return_status OUT VARCHAR2
211 ) IS
212 l_api_version CONSTANT NUMBER := 1.0;
213 l_api_name CONSTANT VARCHAR2 (30) := 'VALIDATE_ROW';
214 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
215 BEGIN
216
217 x_return_status := fnd_api.g_ret_sts_success;
218
219 IF NOT fnd_api.compatible_api_call ( l_api_version,
220 p_api_version,
221 l_api_name,
222 G_PKG_NAME
223 )
224 THEN
225 RAISE fnd_api.g_exc_unexpected_error;
226 END IF;
227
228 IF (NOT CHECK_CONFIG_NAME_UNIQUE(p_configuration_rec.config_name, p_configuration_rec.configuration_id)) THEN
229 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
230 fnd_message.set_name('JTS', 'JTS_CONFIG_NAME_EXISTS');
231 fnd_msg_pub.add;
232 END IF;
233 x_return_status := fnd_api.g_ret_sts_error;
234 END IF;
235
236 IF (NOT CHECK_FLOW_EXISTS(p_configuration_rec.flow_id)) THEN
237 raise FND_API.g_exc_unexpected_error;
238 END IF;
239
240 EXCEPTION
241 WHEN fnd_api.g_exc_unexpected_error THEN
242 x_return_status := fnd_api.g_ret_sts_unexp_error;
243 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name || ': flow_id not found');
244 WHEN OTHERS THEN
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
246 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
247 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
248 END IF;
249 END VALIDATE_ROW;
250
251 -----------------------------------------------------------
252 --Creates a configuration and an initial version
253 --Values passed in:
254 --config_name config_configName,
255 --description config_desc,
256 --flow_id config_flowId,
257 --flow_type setupFlow_flowType,
258 --record_mode config_recordMode,
259 -----------------------------------------------------------
260 PROCEDURE CREATE_CONFIGURATION(
261 p_api_version IN NUMBER,
262 p_configuration_rec IN Config_Rec_Type,
263 x_config_id OUT NUMBER,
264 x_return_status OUT VARCHAR2,
265 x_msg_count OUT NUMBER,
266 x_msg_data OUT VARCHAR2
267 ) IS
268 l_api_version CONSTANT NUMBER := 1.0;
269 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_CONFIGURATION';
270 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
271 l_version_id JTS_CONFIG_VERSIONS_B.version_id%TYPE;
272 --l_version_rec JTS_CONFIG_VERSION_PVT.Config_Version_Rec_Type;
273 l_flows JTS_SETUP_FLOW_PVT.Setup_Flow_Tbl_Type;
274 BEGIN
275 x_return_status := fnd_api.G_RET_STS_SUCCESS;
276
277 -- Standard Start of API savepoint
278 SAVEPOINT create_configuration;
279
280 fnd_msg_pub.initialize;
281
282 IF NOT fnd_api.compatible_api_call ( l_api_version,
283 p_api_version,
284 l_api_name,
285 G_PKG_NAME
286 )
287 THEN
288 RAISE fnd_api.g_exc_unexpected_error;
289 END IF;
290
291 VALIDATE_ROW(p_api_version,
292 p_configuration_rec,
293 x_return_status ); --server-side validation for unique name
294
295 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
296 INSERT_ROW(p_configuration_rec,
297 x_config_id);
298 --Create the initial version
299 --FND_MESSAGE.set_name('JTS', 'JTS_INITIAL_VERSION');
300 --l_version_rec.version_name := FND_MESSAGE.get;
301 --FND_MESSAGE.set_name('JTS', 'JTS_VERSION_DESCRIPTION');
302 --FND_MESSAGE.set_token('VERSION', l_version_rec.version_name);
303 --FND_MESSAGE.set_token('FLOWNAME', JTS_SETUP_FLOW_PVT.get_flow_name(p_configuration_rec.flow_id));
304 --l_version_rec.description := FND_MESSAGE.get;
305
306 JTS_CONFIG_VERSION_PVT.CREATE_VERSION(
307 p_api_version => p_api_version,
308 p_commit => FND_API.G_FALSE,
309 p_configuration_id => x_config_id,
310 p_init_version => FND_API.G_TRUE,
311 x_version_id => l_version_id,
312 x_return_status => x_return_status,
313 x_msg_count => x_msg_count,
314 x_msg_data => x_msg_data);
315 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
316 Commit;
317 ELSE
318 raise fnd_api.g_exc_unexpected_error;
319 END IF;
320 END IF;
321
322 fnd_msg_pub.count_and_get (
323 p_encoded=> fnd_api.g_false
324 ,p_count=> x_msg_count
325 ,p_data=> x_msg_data
326 );
327
328 EXCEPTION
329 WHEN FND_API.G_EXC_ERROR THEN
330 ROLLBACK TO create_configuration;
331 x_return_status := FND_API.G_RET_STS_ERROR;
332 -- Standard call to get message count and if count=1, get the message
333 FND_MSG_PUB.Count_And_Get (
334 p_encoded => FND_API.G_FALSE,
335 p_count => x_msg_count,
336 p_data => x_msg_data
337 );
338 WHEN OTHERS THEN
339 ROLLBACK TO create_configuration;
340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
341 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
342 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
343 END IF;
344 -- Standard call to get message count and if count=1, get the message
345 FND_MSG_PUB.Count_And_Get (
346 p_encoded => FND_API.G_FALSE,
347 p_count => x_msg_count,
348 p_data => x_msg_data
349 );
350 END CREATE_CONFIGURATION;
351
352 -----------------------------------------------------------
353 --Updates a configuration
354 --Values passed in:
355 --config_name config_configName,
356 --description config_desc,
357 --Updated: config_name, description, last_update_date,
358 --last_updated_by, last_update_login
359 -----------------------------------------------------------
360 PROCEDURE UPDATE_NAME_DESC (
361 p_api_version IN NUMBER,
362 p_config_id IN NUMBER,
363 p_config_name IN VARCHAR2,
364 p_config_desc IN VARCHAR2,
365 x_return_status OUT VARCHAR2,
366 x_msg_count OUT NUMBER,
367 x_msg_data OUT VARCHAR2
368 ) IS
369 l_api_version CONSTANT NUMBER := 1.0;
370 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_ NAME_DESC';
371 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
372 l_config_rec Config_Rec_Type;
373 BEGIN
374 x_return_status := fnd_api.g_ret_sts_success;
375
376 -- Standard Start of API savepoint
377 SAVEPOINT update_name_and_desc;
378
379 Fnd_msg_pub.initialize;
380
381 IF NOT fnd_api.compatible_api_call ( l_api_version,
382 p_api_version,
383 l_api_name,
384 G_PKG_NAME
385 )
386 THEN
387 RAISE fnd_api.g_exc_unexpected_error;
388 END IF;
389
390 l_config_rec.configuration_id := p_config_id;
391 l_config_rec.config_name := p_config_name;
392 l_config_rec.description := p_config_desc;
393 GET_FLOW_ID(p_config_id, l_config_rec.flow_id);
394
395 VALIDATE_ROW(p_api_version => p_api_version,
396 p_configuration_rec => l_config_rec,
397 x_return_status => x_return_status);
398
399 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
400 UPDATE jts_configurations_b
401 SET config_name = p_config_name,
402 last_update_date = sysdate,
403 last_updated_by = FND_GLOBAL.user_id,
404 last_update_login = FND_GLOBAL.user_id
405 WHERE configuration_id = p_config_id;
406
407 --take care of translation
408 UPDATE jts_configurations_tl
409 SET description = p_config_desc,
410 last_update_date = sysdate,
411 last_updated_by = FND_GLOBAL.user_id,
412 last_update_login = FND_GLOBAL.user_id,
413 source_lang = USERENV('LANG')
414 WHERE configuration_id = p_config_id
415 AND USERENV('LANG') IN (language, source_lang);
416 COMMIT;
417 END IF;
418
419 FND_MSG_PUB.Count_And_Get (
420 p_encoded => FND_API.G_FALSE,
421 p_count => x_msg_count,
422 p_data => x_msg_data
423 );
424 EXCEPTION
425 WHEN FND_API.G_EXC_ERROR THEN
426 ROLLBACK TO update_name_and_desc;
427 x_return_status := FND_API.G_RET_STS_ERROR;
428 -- Standard call to get message count and if count=1, get the message
429 FND_MSG_PUB.Count_And_Get (
430 p_encoded => FND_API.G_FALSE,
431 p_count => x_msg_count,
432 p_data => x_msg_data
433 );
434 WHEN OTHERS THEN
435 ROLLBACK TO update_name_and_desc;
436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
437 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
438 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
439 END IF;
440 -- Standard call to get message count and if count=1, get the message
441 FND_MSG_PUB.Count_And_Get (
442 p_encoded => FND_API.G_FALSE,
443 p_count => x_msg_count,
444 p_data => x_msg_data
445 );
446 END UPDATE_NAME_DESC;
447
448
449 -- Deletes a configuration and its versions
450 PROCEDURE DELETE_CONFIGURATION(
451 p_api_version IN NUMBER,
452 p_config_id IN NUMBER,
453 x_return_status OUT VARCHAR2,
454 x_msg_count OUT NUMBER,
455 x_msg_data OUT VARCHAR2
456 ) IS
457 l_api_version CONSTANT NUMBER := 1.0;
458 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_CONFIGURATION';
459 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
460 BEGIN
461 x_return_status := FND_API.G_RET_STS_SUCCESS;
462
463 -- Standard Start of API savepoint
464 SAVEPOINT delete_configuration;
465
466 fnd_msg_pub.initialize;
467
468 IF NOT fnd_api.compatible_api_call ( l_api_version,
469 p_api_version,
470 l_api_name,
471 G_PKG_NAME
472 )
473 THEN
474 RAISE fnd_api.g_exc_unexpected_error;
475 END IF;
476
477 JTS_CONFIG_VERSION_PVT.DELETE_VERSIONS(p_api_version,
478 p_config_id);
479 JTS_CONFIGURATIONS_PKG.DELETE_ROW(p_config_id);
480 Commit;
481
482 FND_MSG_PUB.Count_And_Get (
483 p_encoded => FND_API.G_FALSE,
484 p_count => x_msg_count,
485 p_data => x_msg_data
486 );
487 EXCEPTION
488 WHEN OTHERS THEN
489 ROLLBACK TO delete_configuration;
490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
491 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
492 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
493 END IF;
494 -- Standard call to get message count and if count=1, get the message
495 FND_MSG_PUB.Count_And_Get (
496 p_encoded => FND_API.G_FALSE,
497 p_count => x_msg_count,
498 p_data => x_msg_data
499 );
500 END DELETE_CONFIGURATION;
501
502 -- Retrieves a configuration given a config_id
503 PROCEDURE GET_CONFIGURATION(
504 p_api_version IN NUMBER,
505 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
506 p_config_id IN NUMBER,
507 x_configuration_rec OUT NOCOPY Config_Rec_Type,
508 x_return_status OUT VARCHAR2,
509 x_msg_count OUT NUMBER,
510 x_msg_data OUT VARCHAR2
511 ) IS
512 l_api_version CONSTANT NUMBER := 1.0;
513 l_api_name CONSTANT VARCHAR2 (30) := 'GET_CONFIGURATION';
514 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
515
516 BEGIN
517 x_return_status := fnd_api.G_RET_STS_SUCCESS;
518
519 IF (FND_API.to_boolean(p_init_msg_list)) THEN
520 fnd_msg_pub.initialize;
521 END IF;
522
523 IF NOT fnd_api.compatible_api_call ( l_api_version,
524 p_api_version,
525 l_api_name,
526 G_PKG_NAME
527 )
528 THEN
529 RAISE fnd_api.g_exc_unexpected_error;
530 END IF;
531
532 SELECT c.configuration_id, c.config_name, c.description, c.flow_id, fl.flow_name, fl.flow_type,
533 lf.meaning, c.record_mode, l.meaning, c.attribute_category, c.attribute1, c.attribute2,
534 c.attribute3, c.attribute4, c.attribute5, c.attribute6,
535 c.attribute7, c.attribute8, c.attribute9, c.attribute10,
536 c.attribute11, c.attribute12, c.attribute13, c.attribute14,
537 c.attribute15, c.creation_date, c.created_by, c.last_update_date,
538 c.last_updated_by, c.last_update_login, u1.user_name, u2.user_name
539 INTO x_configuration_rec
540 FROM jts_configurations_vl c,
541 fnd_lookup_values_vl l,
542 fnd_lookup_values_vl lf,
543 jts_setup_flows_vl fl,
544 fnd_user u1,
545 fnd_user u2
546 WHERE c.configuration_id = p_config_id
547 AND fl.flow_id = c.flow_id
548 AND l.lookup_type = C_RECORD_MODE_TYPE
549 AND l.lookup_code = c.record_mode
550 AND lf.lookup_type = C_FLOW_TYPE
551 AND lf.lookup_code = fl.flow_type
552 AND u1.user_id (+) = c.created_by
553 AND u2.user_id (+) = c.last_updated_by;
554
555 FND_MSG_PUB.Count_And_Get (
556 p_encoded => FND_API.G_FALSE,
557 p_count => x_msg_count,
558 p_data => x_msg_data
559 );
560
561 EXCEPTION
562 WHEN OTHERS THEN
563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
564 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
565 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
566 END IF;
567 -- Standard call to get message count and if count=1, get the message
568 FND_MSG_PUB.Count_And_Get (
569 p_encoded => FND_API.G_FALSE,
570 p_count => x_msg_count,
571 p_data => x_msg_data
572 );
573 END GET_CONFIGURATION;
574
575 -- Retrieves all configurations with a certain order by clause
576 -- Uses Dynamic SQL
577 PROCEDURE GET_CONFIGURATIONS(
578 p_api_version IN NUMBER,
579 p_where_clause IN VARCHAR2,
580 p_order_by IN VARCHAR2,
581 p_how_to_order IN VARCHAR2,
582 x_configuration_tbl OUT NOCOPY Config_Rec_Tbl_Type,
583 x_return_status OUT VARCHAR2,
584 x_msg_count OUT NUMBER,
585 x_msg_data OUT VARCHAR2
586 ) IS
587 l_api_version CONSTANT NUMBER := 1.0;
588 l_api_name CONSTANT VARCHAR2 (30) := 'GET_CONFIGURATIONS';
589 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
590
591 TYPE Config_Cur_Typ IS REF CURSOR;
592 config_csr Config_Cur_Typ;
593 i NUMBER := 1;
594 sqlStmt VARCHAR2(2000);
595 l_how_to_order VARCHAR2(30);
596 l_order_by VARCHAR2(30) := upper(p_order_by);
597 BEGIN
598
599 x_return_status := fnd_api.G_RET_STS_SUCCESS;
600
601 fnd_msg_pub.initialize;
602
603 IF NOT fnd_api.compatible_api_call ( l_api_version,
604 p_api_version,
605 l_api_name,
606 G_PKG_NAME
607 )
608 THEN
609 RAISE fnd_api.g_exc_unexpected_error;
610 END IF;
611
612 l_how_to_order := substrb(upper(p_how_to_order), 1,30);
613 IF (l_how_to_order <> 'ASC' AND l_how_to_order <> 'DESC') THEN
614 l_how_to_order := 'ASC';
615 END IF;
616
617 IF (l_order_by = 'DESCRIPTION') THEN
618 l_order_by := 'c.description';
619 ELSIF (l_order_by = 'CREATION_DATE') THEN
620 l_order_by := 'c.creation_date';
621 ELSIF (l_order_by = 'CREATED_BY') THEN
622 l_order_by := 'u1.user_name';
623 ELSIF (l_order_by = 'TYPE') THEN
624 l_order_by := 'lf.meaning, fl.flow_name';
625 ELSIF (l_order_by = 'MODE') THEN
626 l_order_by := 'l.meaning';
627 END IF;
628
629 sqlStmt := 'SELECT c.configuration_id, c.config_name, c.description, ' ||
630 ' c.flow_id, fl.flow_name, fl.flow_type, lf.meaning, c.record_mode, ' ||
631 ' l.meaning, c.attribute_category, c.attribute1, c.attribute2, ' ||
632 ' c.attribute3, c.attribute4, c.attribute5, c.attribute6, ' ||
633 ' c.attribute7, c.attribute8, c.attribute9, c.attribute10, ' ||
634 ' c.attribute11, c.attribute12, c.attribute13, c.attribute14, ' ||
635 ' c.attribute15, c.creation_date, c.created_by, ' ||
636 ' c.last_update_date, c.last_updated_by, c.last_update_login, ' ||
637 ' u1.user_name, u2.user_name ' ||
638 ' FROM jts_configurations_vl c, ' ||
639 ' fnd_lookup_values_vl l, ' ||
640 ' fnd_lookup_values_vl lf, ' ||
641 ' jts_setup_flows_vl fl, ' ||
642 ' fnd_user u1, ' ||
643 ' fnd_user u2 ' ||
644 ' WHERE fl.flow_id = c.flow_id ' ||
645 ' AND l.lookup_type = ''' || C_RECORD_MODE_TYPE ||
646 ''' AND l.lookup_code = c.record_mode ' ||
647 ' AND lf.lookup_type = ''' || C_FLOW_TYPE ||
648 ''' AND lf.lookup_code = fl.flow_type ' ||
649 ' AND u1.user_id = c.created_by ' ||
650 ' AND u2.user_id = c.last_updated_by ' ||
651 p_where_clause ||
652 ' ORDER BY ' || l_order_by || ' ' || l_how_to_order;
653
654 i := 1;
655 OPEN config_csr FOR sqlStmt;
656 LOOP
657 FETCH config_csr INTO
658 x_configuration_tbl(i).configuration_id,
659 x_configuration_tbl(i).config_name,
660 x_configuration_tbl(i).description,
661 x_configuration_tbl(i).flow_id,
662 x_configuration_tbl(i).flow_name,
663 x_configuration_tbl(i).flow_type_code,
664 x_configuration_tbl(i).flow_type,
665 x_configuration_tbl(i).record_mode,
666 x_configuration_tbl(i).displayed_record_mode,
667 x_configuration_tbl(i).attribute_category,
668 x_configuration_tbl(i).attribute1,
669 x_configuration_tbl(i).attribute2,
670 x_configuration_tbl(i).attribute3,
671 x_configuration_tbl(i).attribute4,
672 x_configuration_tbl(i).attribute5,
673 x_configuration_tbl(i).attribute6,
674 x_configuration_tbl(i).attribute7,
675 x_configuration_tbl(i).attribute8,
676 x_configuration_tbl(i).attribute9,
677 x_configuration_tbl(i).attribute10,
678 x_configuration_tbl(i).attribute11,
679 x_configuration_tbl(i).attribute12,
680 x_configuration_tbl(i).attribute13,
681 x_configuration_tbl(i).attribute14,
682 x_configuration_tbl(i).attribute15,
683 x_configuration_tbl(i).creation_date,
684 x_configuration_tbl(i).created_by,
685 x_configuration_tbl(i).last_update_date,
686 x_configuration_tbl(i).last_updated_by,
687 x_configuration_tbl(i).last_update_login,
688 x_configuration_tbl(i).created_by_name,
689 x_configuration_tbl(i).last_updated_by_name;
690 EXIT WHEN config_csr%NOTFOUND;
691 i := i + 1;
692 END LOOP;
693 CLOSE config_csr;
694
695 FND_MSG_PUB.Count_And_Get (
696 p_encoded => FND_API.G_FALSE,
697 p_count => x_msg_count,
698 p_data => x_msg_data
699 );
700
701 EXCEPTION
702 WHEN OTHERS THEN
703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
704 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
705 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
706 END IF;
707 -- Standard call to get message count and if count=1, get the message
708 FND_MSG_PUB.Count_And_Get (
709 p_encoded => FND_API.G_FALSE,
710 p_count => x_msg_count,
711 p_data => x_msg_data
712 );
713 END GET_CONFIGURATIONS;
714
715 -- Retrieves flow_id for a particular configuration
716 PROCEDURE GET_FLOW_ID(
717 p_config_id IN NUMBER,
718 x_flow_id OUT NUMBER
719 ) IS
720
721 BEGIN
722 SELECT flow_id
723 INTO x_flow_id
724 FROM jts_configurations_b
725 WHERE configuration_id = p_config_id;
726
727 EXCEPTION
728 WHEN NO_DATA_FOUND THEN
729 x_flow_id := NULL;
730 WHEN OTHERS THEN
731 APP_EXCEPTION.raise_exception;
732 END GET_FLOW_ID;
733
734 END JTS_CONFIGURATION_PVT;