PACKAGE BODY: APPS.PA_CI_TYPES_UTIL

Source

1 PACKAGE BODY pa_ci_types_util AS
2 /* $Header: PACITYUB.pls 120.2 2005/08/22 05:17:26 sukhanna noship $ */
3 
4 FUNCTION check_ci_type_name_exists(
5   p_name VARCHAR2,
6   p_short_name VARCHAR2,
7   p_ci_type_id NUMBER default NULL
8 ) RETURN BOOLEAN
9 IS
10   l_temp VARCHAR2(1);
11 BEGIN
12   SELECT 'X'
13   INTO l_temp
14   FROM pa_ci_types_vl
15   WHERE (name = p_name
16          OR short_name = p_short_name)
17     AND (p_ci_type_id IS NULL
18          OR ci_type_id <> p_ci_type_id);
19 
20   RETURN TRUE;
21 EXCEPTION
22   WHEN NO_DATA_FOUND THEN
23     RETURN FALSE;
24 END check_ci_type_name_exists;
25 
26 PROCEDURE check_ci_type_name_or_id(
27   p_name IN VARCHAR2,
28   p_ci_type_id IN NUMBER,
29   p_check_id_flag IN VARCHAR2 := pa_startup.G_check_id_flag,
30   x_ci_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
31   x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
32   x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
33 IS
34   l_sysdate DATE := TRUNC(sysdate);
35   l_ci_type_id  NUMBER(15); --Added for bug#4565156
36 BEGIN
37   pa_debug.init_err_stack ('pa_ci_types_util.check_ci_type_name_or_id');
38 
39   x_return_status := 'S';
40   x_error_message_code := NULL;
41 
42   l_ci_type_id := x_ci_type_id; --Added for bug#4565156
43 
44   IF p_ci_type_id IS NOT NULL THEN
45     IF p_check_id_flag <> 'N' THEN
46       SELECT ci_type_id
47       INTO x_ci_type_id
48       FROM pa_ci_types_b
49       WHERE ci_type_id = p_ci_type_id
50         AND l_sysdate BETWEEN TRUNC(start_date_active)
51                           AND TRUNC(NVL(end_date_active, sysdate));
52     ELSE
53       x_ci_type_id := p_ci_type_id;
54     END IF;
55   ELSE
56     SELECT ci_type_id
57     INTO x_ci_type_id
58     FROM pa_ci_types_vl
59     WHERE name like p_name
60       AND l_sysdate BETWEEN TRUNC(start_date_active)
61                         AND TRUNC(NVL(end_date_active, sysdate));
65 EXCEPTION
62   END IF;
63 
64   pa_debug.reset_err_stack;
66   WHEN NO_DATA_FOUND THEN
67     x_return_status := FND_API.G_RET_STS_ERROR;
68     x_error_message_code := 'PA_CI_TYPE_INVALID_AMBIGUOUS';
69   WHEN TOO_MANY_ROWS THEN
70     x_return_status := FND_API.G_RET_STS_ERROR;
71     x_error_message_code := 'PA_CI_TYPE_INVALID_AMBIGUOUS';
72   WHEN OTHERS THEN
73     x_ci_type_id := l_ci_type_id; --Added for bug#4565156
74     fnd_msg_pub.add_exc_msg
75       (p_pkg_name => 'PA_CI_TYPES_UTIL',
76        p_procedure_name => pa_debug.g_err_stack );
77     x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
78     RAISE;
79 END check_ci_type_name_or_id;
80 
81 END pa_ci_types_util;