[Home] [Help]
PACKAGE BODY: APPS.PA_CI_TYPE_USAGE_PUB
Source
1 PACKAGE BODY pa_ci_type_usage_pub AS
2 /* $Header: PACITUPB.pls 120.1 2005/08/19 16:18:47 mwasowic noship $ */
3
4 PROCEDURE create_ci_type_usage (
5 p_api_version IN NUMBER := 1.0,
6 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
7 p_commit IN VARCHAR2 := FND_API.g_false,
8 p_validate_only IN VARCHAR2 := FND_API.g_true,
9 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
10 p_project_type IN VARCHAR2 := NULL,
11 p_project_type_id IN NUMBER := NULL,
12 p_ci_type_name IN VARCHAR2 := NULL,
13 p_ci_type_id IN NUMBER := NULL,
14 p_created_by IN NUMBER DEFAULT fnd_global.user_id,
15 p_creation_date IN DATE DEFAULT SYSDATE,
16 p_last_update_login IN NUMBER DEFAULT fnd_global.login_id,
17 x_ci_type_usage_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
18 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
19 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
20 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21 )
22 IS
23 l_error_message_code VARCHAR2(100);
24 l_project_type_id NUMBER := p_project_type_id;
25 l_ci_type_id NUMBER := p_ci_type_id;
26 BEGIN
27 pa_debug.set_err_stack ('PA_CI_TYPE_USAGE_PUB.CREATE_CI_TYPE_USAGE');
28
29 IF p_commit = FND_API.G_TRUE THEN
30 SAVEPOINT create_ci_type_usage;
31 END IF;
32
33 IF p_init_msg_list = FND_API.G_TRUE THEN
34 fnd_msg_pub.initialize;
35 END IF;
36
37 x_return_status := 'S';
38 x_msg_count := 0;
39 x_msg_data := '';
40
41 IF p_project_type_id IS NULL THEN
42 BEGIN
43 SELECT project_type_id
44 INTO l_project_type_id
45 FROM pa_project_types_all
46 WHERE project_type like p_project_type
47 AND TRUNC(sysdate) BETWEEN TRUNC(start_date_active)
48 AND TRUNC(NVL(end_date_active, sysdate));
49 EXCEPTION
50 WHEN NO_DATA_FOUND THEN
51 x_return_status := 'E';
52 fnd_message.set_name('PA', 'PA_PROJ_TYPE_INV_AMBIGUOUS');
53 fnd_msg_pub.add();
54 WHEN TOO_MANY_ROWS THEN
55 x_return_status := 'E';
56 fnd_message.set_name('PA', 'PA_PROJ_TYPE_INV_AMBIGUOUS');
57 fnd_msg_pub.add();
58 END;
59 END IF;
60
61 IF x_return_status = 'S' THEN
62 pa_ci_types_util.check_ci_type_name_or_id(
63 p_name => p_ci_type_name,
64 p_ci_type_id => p_ci_type_id,
65 p_check_id_flag => 'N',
66 x_ci_type_id => l_ci_type_id,
67 x_return_status => x_return_status,
68 x_error_message_code => l_error_message_code);
69
70 IF x_return_status <> 'S' THEN
71 fnd_message.set_name('PA', l_error_message_code);
72 fnd_msg_pub.add();
73 END IF;
74
75 END IF;
76
77 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
78 pa_ci_type_usage_pvt.create_ci_type_usage (
79 p_init_msg_list => 'F',
80 p_commit => 'F',
81 p_validate_only => 'F',
82 p_max_msg_count => p_max_msg_count,
83 p_project_type_id => l_project_type_id,
84 p_ci_type_id => l_ci_type_id,
85 p_created_by => p_created_by,
86 p_creation_date => p_creation_date,
87 p_last_update_login => p_last_update_login,
88 x_ci_type_usage_id => x_ci_type_usage_id,
89 x_return_status => x_return_status,
90 x_msg_count => x_msg_count,
91 x_msg_data => x_msg_data);
92 END IF;
93
94 IF p_commit = fnd_api.g_true THEN
95 IF x_return_status = 'S' THEN
96 COMMIT;
97 ELSE
98 ROLLBACK TO create_ci_type_usage;
99 END IF;
100 END IF;
101
102 fnd_msg_pub.count_and_get(p_count => x_msg_count,
103 p_data => x_msg_data);
104
105 pa_debug.reset_err_stack;
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 IF p_commit = fnd_api.g_true THEN
110 ROLLBACK TO create_ci_type_usage;
111 END IF;
112
113 x_return_status := 'U';
114 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_TYPE_USAGE_PUB',
115 p_procedure_name => 'CREATE_CI_TYPE_USAGE',
116 p_error_text => SUBSTRB(SQLERRM,1,240));
117
118 fnd_msg_pub.count_and_get(p_count => x_msg_count,
119 p_data => x_msg_data);
120 END create_ci_type_usage;
121
122 PROCEDURE update_ci_type_usage (
123 p_api_version IN NUMBER := 1.0,
124 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
125 p_commit IN VARCHAR2 := FND_API.g_false,
126 p_validate_only IN VARCHAR2 := FND_API.g_true,
127 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
128 p_ci_type_usage_id IN NUMBER,
129 p_project_type IN VARCHAR2 := NULL,
130 p_project_type_id IN NUMBER := NULL,
131 p_ci_type_name IN VARCHAR2 := NULL,
132 p_ci_type_id IN NUMBER := NULL,
133 p_last_updated_by IN NUMBER DEFAULT fnd_global.user_id,
134 p_last_update_date IN DATE DEFAULT SYSDATE,
135 p_last_update_login IN NUMBER DEFAULT fnd_global.user_id,
136 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
137 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
138 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
139 )
140 IS
141 l_error_message_code VARCHAR2(100);
142 l_project_type_id NUMBER := p_project_type_id;
143 l_ci_type_id NUMBER := p_ci_type_id;
144 BEGIN
145 pa_debug.set_err_stack ('PA_CI_TYPE_USAGE_PUB.UPDATE_CI_TYPE_USAGE');
146
147 IF p_commit = FND_API.G_TRUE THEN
148 SAVEPOINT update_ci_type_usage;
149 END IF;
150
151 IF p_init_msg_list = FND_API.G_TRUE THEN
152 fnd_msg_pub.initialize;
153 END IF;
154
155 x_return_status := 'S';
156 x_msg_count := 0;
157 x_msg_data := '';
158
159 IF p_project_type_id IS NULL THEN
160 BEGIN
161 SELECT project_type_id
162 INTO l_project_type_id
163 FROM pa_project_types_all
164 WHERE project_type like p_project_type
165 AND TRUNC(sysdate) BETWEEN TRUNC(start_date_active)
166 AND TRUNC(NVL(end_date_active, sysdate));
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 x_return_status := 'E';
170 fnd_message.set_name('PA', 'PA_PROJ_TYPE_INV_AMBIGUOUS');
171 fnd_msg_pub.add();
172 WHEN TOO_MANY_ROWS THEN
173 x_return_status := 'E';
174 fnd_message.set_name('PA', 'PA_PROJ_TYPE_INV_AMBIGUOUS');
175 fnd_msg_pub.add();
176 END;
177 END IF;
178
179 IF x_return_status = 'S' THEN
180 pa_ci_types_util.check_ci_type_name_or_id(
181 p_name => p_ci_type_name,
182 p_ci_type_id => p_ci_type_id,
183 p_check_id_flag => 'N',
184 x_ci_type_id => l_ci_type_id,
185 x_return_status => x_return_status,
186 x_error_message_code => l_error_message_code);
187
188 IF x_return_status <> 'S' THEN
189 fnd_message.set_name('PA', l_error_message_code);
190 fnd_msg_pub.add();
191 END IF;
192
193 END IF;
194
195 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
196 pa_ci_type_usage_pvt.update_ci_type_usage (
197 p_init_msg_list => 'F',
198 p_commit => 'F',
199 p_validate_only => 'F',
200 p_max_msg_count => p_max_msg_count,
201 p_ci_type_usage_id => p_ci_type_usage_id,
202 p_project_type_id => l_project_type_id,
203 p_ci_type_id => l_ci_type_id,
204 p_last_update_date => p_last_update_date,
205 p_last_updated_by => p_last_updated_by,
206 p_last_update_login => p_last_update_login,
207 x_return_status => x_return_status,
208 x_msg_count => x_msg_count,
209 x_msg_data => x_msg_data);
210 END IF;
211
212 IF p_commit = fnd_api.g_true THEN
213 IF x_return_status = 'S' THEN
214 COMMIT;
215 ELSE
216 ROLLBACK TO update_ci_type_usage;
217 END IF;
218 END IF;
219
220 fnd_msg_pub.count_and_get(p_count => x_msg_count,
221 p_data => x_msg_data);
222
223 pa_debug.reset_err_stack;
224
225 EXCEPTION
226 WHEN OTHERS THEN
227 IF p_commit = fnd_api.g_true THEN
228 ROLLBACK TO update_ci_type_usage;
229 END IF;
230
231 x_return_status := 'U';
232 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_TYPE_USAGE_PUB',
233 p_procedure_name => 'UPDATE_CI_TYPE_USAGE',
234 p_error_text => SUBSTRB(SQLERRM,1,240));
235
236 fnd_msg_pub.count_and_get(p_count => x_msg_count,
237 p_data => x_msg_data);
238 END update_ci_type_usage;
239
240 PROCEDURE delete_ci_type_usage (
241 p_api_version IN NUMBER := 1.0,
242 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
243 p_commit IN VARCHAR2 := FND_API.g_false,
244 p_validate_only IN VARCHAR2 := FND_API.g_true,
245 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
246 p_ci_type_usage_id IN NUMBER,
247 p_project_type IN VARCHAR2 := NULL,
248 p_project_type_id IN NUMBER := NULL,
249 p_ci_type_name IN VARCHAR2 := NULL,
250 p_ci_type_id IN NUMBER := NULL,
251 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
252 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
253 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
254 )
255 IS
256 l_error_message_code VARCHAR2(100);
257 l_project_type_id NUMBER := p_project_type_id;
258 l_ci_type_id NUMBER := p_ci_type_id;
259 BEGIN
260 pa_debug.set_err_stack ('PA_CI_TYPE_USAGE_PUB.DELETE_CI_TYPE_USAGE');
261
262 IF p_commit = FND_API.G_TRUE THEN
263 SAVEPOINT delete_ci_type_usage;
264 END IF;
265
266 IF p_init_msg_list = FND_API.G_TRUE THEN
267 fnd_msg_pub.initialize;
268 END IF;
269
270 x_return_status := 'S';
271 x_msg_count := 0;
272 x_msg_data := '';
273
274
275 IF p_project_type_id IS NULL THEN
276 BEGIN
277 SELECT project_type_id
278 INTO l_project_type_id
279 FROM pa_project_types_all
280 WHERE project_type like p_project_type
281 AND TRUNC(sysdate) BETWEEN TRUNC(start_date_active)
282 AND TRUNC(NVL(end_date_active, sysdate));
283 EXCEPTION
284 WHEN NO_DATA_FOUND THEN
285 x_return_status := 'E';
286 fnd_message.set_name('PA', 'PA_PROJ_TYPE_INV_AMBIGUOUS');
287 fnd_msg_pub.add();
288 WHEN TOO_MANY_ROWS THEN
289 x_return_status := 'E';
290 fnd_message.set_name('PA', 'PA_PROJ_TYPE_INV_AMBIGUOUS');
291 fnd_msg_pub.add();
292 END;
293 END IF;
294
295 IF x_return_status = 'S' THEN
296 pa_ci_types_util.check_ci_type_name_or_id(
297 p_name => p_ci_type_name,
298 p_ci_type_id => p_ci_type_id,
299 p_check_id_flag => 'N',
300 x_ci_type_id => l_ci_type_id,
301 x_return_status => x_return_status,
302 x_error_message_code => l_error_message_code);
303
304 IF x_return_status <> 'S' THEN
305 fnd_message.set_name('PA', l_error_message_code);
306 fnd_msg_pub.add();
307 END IF;
308
309 END IF;
310
311 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
312 pa_ci_type_usage_pvt.delete_ci_type_usage (
313 p_init_msg_list => 'F',
314 p_commit => 'F',
315 p_validate_only => 'F',
316 p_max_msg_count => p_max_msg_count,
317 p_ci_type_usage_id => p_ci_type_usage_id,
318 p_project_type_id => l_project_type_id,
319 p_ci_type_id => l_ci_type_id,
320 x_return_status => x_return_status,
321 x_msg_count => x_msg_count,
322 x_msg_data => x_msg_data);
323 END IF;
324
325 IF p_commit = fnd_api.g_true THEN
326 IF x_return_status = 'S' THEN
327 COMMIT;
328 ELSE
329 ROLLBACK TO delete_ci_type_usage;
330 END IF;
331 END IF;
332
333 fnd_msg_pub.count_and_get(p_count => x_msg_count,
334 p_data => x_msg_data);
335
336 pa_debug.reset_err_stack;
337
338 EXCEPTION
339 WHEN OTHERS THEN
340 IF p_commit = fnd_api.g_true THEN
341 ROLLBACK TO delete_ci_type_usage;
342 END IF;
343
344 x_return_status := 'U';
345 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_TYPES_PUB',
346 p_procedure_name => 'DELETE_CI_TYPE_USAGE',
347 p_error_text => SUBSTRB(SQLERRM,1,240));
348
349 fnd_msg_pub.count_and_get(p_count => x_msg_count,
350 p_data => x_msg_data);
351 END delete_ci_type_usage;
352
353 END pa_ci_type_usage_pub;