[Home] [Help]
PACKAGE BODY: APPS.PA_CI_IMPACT_TYPE_USAGE_PVT
Source
1 PACKAGE BODY PA_CI_IMPACT_TYPE_USAGE_pvt AS
2 /* $Header: PACIIMVB.pls 115.2 2002/11/23 19:22:56 syao noship $ */
3
4 PROCEDURE create_ci_impact_type_usage (
5 p_api_version IN NUMBER := 1.0,
6 p_init_msg_list IN VARCHAR2 := 'T',
7 p_commit IN VARCHAR2 := 'F',
8 p_validate_only IN VARCHAR2 := 'T',
9 p_max_msg_count IN NUMBER := null,
10
11 p_impact_type_code IN VARCHAR2 := null,
12 p_ci_type_class_code IN VARCHAR2 := null,
13 p_CI_TYPE_ID in NUMBER := null,
14
15 p_created_by IN NUMBER DEFAULT fnd_global.user_id,
16 p_creation_date IN DATE DEFAULT SYSDATE,
17 p_last_update_login IN NUMBER DEFAULT fnd_global.login_id,
18
19 x_ci_impact_type_usage_id OUT NOCOPY NUMBER,
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_msg_count OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2
23 )
24 IS
25 l_rowid VARCHAR2(30);
26 CURSOR check_exists is
27 SELECT 'Y' FROM dual
28 WHERE exists (SELECT ci_impact_type_usage_id FROM
29 pa_ci_impact_type_usage
30 WHERE ci_type_class_code = p_ci_type_class_code
31 AND ci_type_id = p_ci_type_id
32 AND impact_type_code = p_impact_type_code);
33
34 l_dummy VARCHAR2(1);
35
36 BEGIN
37 pa_debug.set_err_stack ('PA_CI_IMPACT_TYPE_USAGE_PVT.CREATE_CI_IMPACT_TYPE_USAGE');
38
39 IF p_commit = 'T' THEN
40 SAVEPOINT create_ci_impact_type_usage;
41 END IF;
42
43 IF p_init_msg_list = 'T' THEN
44 fnd_msg_pub.initialize;
45 END IF;
46
47 x_return_status := 'S';
48 x_msg_count := 0;
49 x_msg_data := '';
50
51 OPEN check_exists;
52 FETCH check_exists INTO l_dummy;
53 IF check_exists%found THEN
54 -- record already exists
55 PA_UTILS.Add_Message( p_app_short_name => 'PA'
56 ,p_msg_name => 'PA_CI_IMPACT_TU_EXIST');
57
58 x_return_status := FND_API.G_RET_STS_ERROR;
59
60 END IF;
61 CLOSE check_exists;
62
63 IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
64 PA_CI_IMPACT_TYPE_USAGE_pkg.insert_row(
65 x_rowid => l_rowid,
66 x_ci_impact_type_usage_id => x_ci_impact_type_usage_id,
67 x_impact_type_code => p_impact_type_code,
68 x_ci_type_class_code => p_ci_type_class_code,
69 x_ci_type_id => p_ci_type_id,
70 x_creation_date => p_creation_date,
71 x_created_by => p_created_by,
72 x_last_update_date => p_creation_date,
73 x_last_updated_by => p_created_by,
74 x_last_update_login => p_last_update_login);
75 END IF;
76
77 IF p_commit = 'T' THEN
78 IF x_return_status = 'S' THEN
79 COMMIT;
80 ELSE
81 ROLLBACK TO create_ci_impact_type_usage;
82 END IF;
83 END IF;
84
85 fnd_msg_pub.count_and_get(p_count => x_msg_count,
86 p_data => x_msg_data);
87
88 pa_debug.reset_err_stack;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 IF p_commit = 'T' THEN
93 ROLLBACK TO create_ci_impact_type_usage;
94 END IF;
95
96 x_return_status := 'U';
97 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_IMPACT_TYPE_USAGE_PVT',
98 p_procedure_name => 'CREATE_CI_IMPACT_TYPE_USAGE',
99 p_error_text => SUBSTRB(SQLERRM,1,240));
100
101 fnd_msg_pub.count_and_get(p_count => x_msg_count,
102 p_data => x_msg_data);
103 END create_ci_impact_type_usage;
104
105 PROCEDURE delete_ci_impact_type_usage (
106 p_api_version IN NUMBER := 1.0,
107 p_init_msg_list IN VARCHAR2 := 'T',
108 p_commit IN VARCHAR2 := 'F',
109 p_validate_only IN VARCHAR2 := 'T',
110 p_max_msg_count IN NUMBER := null,
111
112 p_ci_impact_type_usage_id IN NUMBER := null,
113 p_impact_type_code IN VARCHAR2 := null,
114 p_ci_type_class_code IN VARCHAR2 := null,
115 p_ci_type_id IN NUMBER := null,
116 x_return_status OUT NOCOPY VARCHAR2,
117 x_msg_count OUT NOCOPY NUMBER,
118 x_msg_data OUT NOCOPY VARCHAR2
119 )
120 IS
121 l_temp VARCHAR2(1);
122 CURSOR check_exists is
123 SELECT 'Y' FROM dual
124 WHERE exists (SELECT ci_impact_type_usage_id FROM
125 pa_ci_impact_type_usage
126 WHERE ci_impact_type_usage_id = p_ci_impact_type_usage_id
127 );
128
129
130 l_dummy VARCHAR2(1);
131 BEGIN
132 pa_debug.set_err_stack ('PA_CI_IMPACT_TYPE_USAGE_PVT.DELETE_CI_IMPACT_TYPE_USAGE');
133
134 IF p_commit = 'T' THEN
135 SAVEPOINT delete_ci_impact_type_usage;
136 END IF;
137
138 IF p_init_msg_list = 'T' THEN
139 fnd_msg_pub.initialize;
140 END IF;
141
142 x_return_status := 'S';
143 x_msg_count := 0;
144 x_msg_data := '';
145
146 OPEN check_exists;
147 FETCH check_exists INTO l_dummy;
148 IF check_exists%notfound THEN
149 -- record already exists
150 PA_UTILS.Add_Message( p_app_short_name => 'PA'
151 ,p_msg_name => 'PA_CI_IMPACT_TU_NO_EXIST');
152
153 x_return_status := FND_API.G_RET_STS_ERROR;
154
155 END IF;
156 CLOSE check_exists;
157
158 -- bug 2606472
159 -- if the impact is in use in a control item, the user cannot remove the
160 -- impact type usage
161
162 IF p_ci_type_id IS NOT NULL
163 AND p_impact_type_code IS NOT NULL
164 THEN
165
166 l_dummy := pa_ci_impact_type_usage_pub.delete_impact_type_usage_ok
167 (p_impact_type_code, p_ci_type_id);
168
169 IF l_dummy = 'N' THEN
170 PA_UTILS.Add_Message( p_app_short_name => 'PA'
171 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE');
172
173 x_return_status := FND_API.G_RET_STS_ERROR;
174
175 END IF;
176
177
178 END IF;
179
180
181 IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
182 PA_CI_IMPACT_TYPE_USAGE_pkg.delete_row(
183 x_ci_impact_type_usage_id => p_ci_impact_type_usage_id);
184 END IF;
185
186
187
188 IF p_commit = 'T' THEN
189 IF x_return_status = 'S' THEN
190 COMMIT;
191 ELSE
192 ROLLBACK TO delete_ci_impact_type_usage;
193 END IF;
194 END IF;
195
196 fnd_msg_pub.count_and_get(p_count => x_msg_count,
197 p_data => x_msg_data);
198
199 pa_debug.reset_err_stack;
200
201 EXCEPTION
202 WHEN OTHERS THEN
203 IF p_commit = 'T' THEN
204 ROLLBACK TO delete_ci_impact_type_usage;
205 END IF;
206
207 x_return_status := 'U';
208 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_IMPACT_TYPES_PVT',
209 p_procedure_name => 'DELETE_CI_IMPACT_TYPE_USAGE',
210 p_error_text => SUBSTRB(SQLERRM,1,240));
211
212 fnd_msg_pub.count_and_get(p_count => x_msg_count,
213 p_data => x_msg_data);
214 END delete_ci_impact_type_usage;
215
216
217
218
219 END PA_CI_IMPACT_TYPE_USAGE_pvt;