[Home] [Help]
PACKAGE BODY: APPS.PA_CI_IMPACTS_PUB
Source
1 PACKAGE BODY PA_CI_IMPACTS_pub AS
2 /* $Header: PACIIPPB.pls 120.0 2005/05/29 13:25:23 appldev noship $ */
3
4 PROCEDURE create_ci_impact (
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_ci_id IN NUMBER := null,
12 p_impact_type_code IN VARCHAR2 := null,
13
14 p_status_code IN VARCHAR2 := null,
15 p_description IN VARCHAR2 := null,
16 p_implementation_date IN DATE := null,
17 p_implemented_by IN NUMBER := NULL,
18 p_implementation_comment IN VARCHAR2 := null,
19 p_impacted_task_id IN NUMBER := NULL,
20 p_impacted_task_name IN VARCHAR2 := NULL,
21
22
23 x_ci_impact_id OUT NOCOPY NUMBER,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_count OUT NOCOPY NUMBER,
26 x_msg_data OUT NOCOPY VARCHAR2
27 )
28 IS
29 l_msg_index_out NUMBER;
30 l_rowid VARCHAR2(30);
31 l_task_id NUMBER;
32
33 CURSOR get_task_id
34 IS
35 select pt.task_id from pa_tasks pt,
36 pa_control_items pc
37 where pt.project_id = pc.project_id
38 and pt.task_name = p_impacted_task_name
39 AND pc.ci_id = p_ci_id;
40
41 BEGIN
42 pa_debug.set_err_stack ('PA_CI_IMPACTS_PUB.CREATE_CI_IMPACTS');
43
44 IF p_commit = 'T' THEN
45 SAVEPOINT create_ci_impact;
46 END IF;
47
48 IF p_init_msg_list = 'T' THEN
49 fnd_msg_pub.initialize;
50 END IF;
51
52 x_return_status := 'S';
53 x_msg_count := 0;
54 x_msg_data := '';
55
56 IF p_impacted_task_name IS NOT NULL THEN
57 OPEN get_task_id;
58 FETCH get_task_id INTO l_task_id;
59 IF get_task_id%notfound THEN
60
61 -- record already exists
62 PA_UTILS.Add_Message( p_app_short_name => 'PA'
63 ,p_msg_name => 'PA_TASK_NAME_INVALID');
64
65 x_return_status := FND_API.G_RET_STS_ERROR;
66 END IF;
67
68 CLOSE get_task_id; --Bug 3868121
69
70 ELSE
71 l_task_id := p_impacted_task_id;
72
73 END IF;
74
75
76 IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
77
78 PA_CI_IMPACTS_pvt.create_ci_impact(
79 p_api_version => p_api_version,
80 p_init_msg_list => p_init_msg_list,
81 p_commit => p_commit,
82 p_validate_only => p_validate_only,
83 p_max_msg_count => p_max_msg_count,
84
85 p_ci_id => p_ci_id,
86 p_impact_type_code => p_impact_type_code,
87 p_status_code => p_status_code,
88 p_description => p_description,
89 p_implementation_date => p_implementation_date,
90 p_implemented_by => p_implemented_by,
91 p_implementation_comment => p_implementation_comment,
92 p_impacted_task_id => l_task_id,
93 x_ci_impact_id => x_ci_impact_id,
94 x_return_status => x_return_status,
95 x_msg_count => x_msg_count,
96 x_msg_data => x_msg_data
97 );
98 END IF;
99 x_msg_count := FND_MSG_PUB.Count_Msg;
100 IF x_msg_count = 1 THEN
101 pa_interface_utils_pub.get_messages ( p_encoded => 'T'
102 ,p_msg_index => 1
103 ,p_data => x_msg_data
104 ,p_msg_index_out => l_msg_index_out
105 );
106 END IF;
107
108 -- Reset the error stack when returning to the calling program
109 PA_DEBUG.Reset_Err_Stack;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 IF p_commit = 'T' THEN
114 ROLLBACK TO create_ci_impact;
115 END IF;
116
117 x_return_status := 'U';
118 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_IMPACTS_PUB',
119 p_procedure_name => 'CREATE_CI_IMPACT',
120 p_error_text => SUBSTRB(SQLERRM,1,240));
121
122 fnd_msg_pub.count_and_get(p_count => x_msg_count,
123 p_data => x_msg_data);
124 END create_ci_impact;
125
126 PROCEDURE delete_ci_impact (
127 p_api_version IN NUMBER := 1.0,
128 p_init_msg_list IN VARCHAR2 := 'T',
129 p_commit IN VARCHAR2 := 'F',
130 p_validate_only IN VARCHAR2 := 'T',
131 p_max_msg_count IN NUMBER := null,
132
133 p_ci_impact_id IN NUMBER := null,
134 p_record_version_number IN NUMBER := null,
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_count OUT NOCOPY NUMBER,
137 x_msg_data OUT NOCOPY VARCHAR2
138 )
139 IS
140 l_temp VARCHAR2(1);
141 l_msg_index_out NUMBER;
142 BEGIN
143 pa_debug.set_err_stack ('PA_CI_IMPACTS_PUB.DELETE_CI_IMPACT');
144
145 IF p_commit = 'T' THEN
146 SAVEPOINT delete_ci_impact;
147 END IF;
148
149 IF p_init_msg_list = 'T' THEN
150 fnd_msg_pub.initialize;
151 END IF;
152
153 x_return_status := 'S';
154 x_msg_count := 0;
155 x_msg_data := '';
156
157 -- Trying to lock the record
158
159 IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
160 PA_CI_IMPACTS_pvt.delete_ci_impact(
161 p_api_version => p_api_version,
162 p_init_msg_list => p_init_msg_list,
163 p_commit => p_commit,
164 p_validate_only => p_validate_only,
165 p_max_msg_count => p_max_msg_count,
166 p_ci_impact_id => p_ci_impact_id,
167 p_record_version_number => p_record_version_number,
168 x_return_status => x_return_status,
169 x_msg_count => x_msg_count,
170 x_msg_data => x_msg_data
171
172 );
173 END IF;
174
175 x_msg_count := FND_MSG_PUB.Count_Msg;
176 IF x_msg_count = 1 THEN
177 pa_interface_utils_pub.get_messages ( p_encoded => 'T'
178 ,p_msg_index => 1
179 ,p_data => x_msg_data
180 ,p_msg_index_out => l_msg_index_out
181 );
182 END IF;
183
184
185 -- Reset the error stack when returning to the calling program
186 PA_DEBUG.Reset_Err_Stack;
187
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 IF p_commit = 'T' THEN
192 ROLLBACK TO delete_ci_impact;
193 END IF;
194
195 x_return_status := 'U';
196 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_IMPACT',
197 p_procedure_name => 'DELETE_CI_IMPACT',
198 p_error_text => SUBSTRB(SQLERRM,1,240));
199
200 fnd_msg_pub.count_and_get(p_count => x_msg_count,
201 p_data => x_msg_data);
202 END delete_ci_impact;
203
204 PROCEDURE update_ci_impact (
205 p_api_version IN NUMBER := 1.0,
206 p_init_msg_list IN VARCHAR2 := 'T',
207 p_commit IN VARCHAR2 := 'F',
208 p_validate_only IN VARCHAR2 := 'T',
209 p_max_msg_count IN NUMBER := null,
210 p_ci_impact_id IN NUMBER:= null,
211 p_ci_id IN NUMBER := null,
212 p_impact_type_code IN VARCHAR2 := null,
213 p_status_code IN VARCHAR2 := null,
214 p_description IN VARCHAR2 := FND_API.g_miss_char,
215 p_implementation_date IN DATE := FND_API.g_miss_date,
216 p_implemented_by IN NUMBER := FND_API.g_miss_num,
217 p_impby_name IN VARCHAR2 := NULL,
218 p_impby_type_id IN NUMBER := null,
219 p_implementation_comment IN VARCHAR2 := FND_API.g_miss_char,
220 p_record_version_number IN NUMBER := null,
221 p_impacted_task_id IN NUMBER := FND_API.g_miss_num,
222 p_impacted_task_name IN VARCHAR2 := NULL,
223
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_msg_count OUT NOCOPY NUMBER,
226 x_msg_data OUT NOCOPY VARCHAR2
227 ) IS
228 l_rowid VARCHAR2(30);
229 l_msg_index_out NUMBER;
230 l_task_id NUMBER;
231
232 l_description VARCHAR2(4000) ;
233 l_implementation_date DATE ;
234 l_implemented_by NUMBER;
235 l_implementation_comment VARCHAR2(4000);
236
237
238
239 CURSOR get_task_id
240 IS
241 select pt.task_id from pa_tasks pt,
242 pa_control_items pc
243 where pt.project_id = pc.project_id
244 and pt.task_name = p_impacted_task_name
245 AND pc.ci_id = p_ci_id;
246
247 CURSOR get_ci_info
248 IS
249 SELECT * FROM pa_ci_impacts
250 WHERE ci_impact_id = p_ci_impact_id;
251
252 BEGIN
253 pa_debug.set_err_stack ('PA_CI_IMPACTS_PUB.CREATE_CI_IMPACTS');
254
255 IF p_commit = 'T' THEN
256 SAVEPOINT update_ci_impact;
257 END IF;
258
259 IF p_init_msg_list = 'T' THEN
260 fnd_msg_pub.initialize;
261 END IF;
262
263 x_return_status := 'S';
264 x_msg_count := 0;
265 x_msg_data := '';
266
267 IF p_impacted_task_name IS NOT NULL THEN
268 OPEN get_task_id;
269 FETCH get_task_id INTO l_task_id;
270 IF get_task_id%notfound THEN
271
272 -- record already exists
273 PA_UTILS.Add_Message( p_app_short_name => 'PA'
274 ,p_msg_name => 'PA_TASK_NAME_INVALID');
275
276 x_return_status := FND_API.G_RET_STS_ERROR;
277 END IF;
278
279 CLOSE get_task_id; --Bug 3868121
280 ELSE
281 l_task_id := p_impacted_task_id;
282
283 END IF;
284 IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
285 l_description := p_description ;
286 l_implementation_date := p_implementation_date;
287 l_implemented_by := p_implemented_by;
288 l_implementation_comment := p_implementation_comment;
289
290
291
292 FOR rec IN get_ci_info LOOP
293 IF p_description = FND_API.g_miss_char then
294 l_description := rec.description ;
295 END IF;
296
297 IF p_implementation_date = FND_API.g_miss_date then
298 l_implementation_date := rec.implementation_date;
299 END IF;
300
301 IF p_implemented_by = FND_API.g_miss_num AND p_impby_name IS NULL then
302 l_implemented_by := rec.implemented_by;
303 END IF;
304
305 IF p_implementation_comment = FND_API.g_miss_char then
306 l_implementation_comment := rec.implementation_comment;
307 END IF;
308
309 IF p_impacted_task_id = FND_API.g_miss_num
310 AND p_impacted_task_name IS NULL then
311 l_task_id := rec.impacted_task_id;
312 END IF;
313
314
315 PA_CI_IMPACTS_pvt.update_ci_impact(
316 p_api_version => p_api_version,
317 p_init_msg_list => p_init_msg_list,
318 p_commit => p_commit,
319 p_validate_only => p_validate_only,
320 p_max_msg_count => p_max_msg_count,
321 p_ci_impact_id => p_ci_impact_id,
322 p_ci_id => p_ci_id,
323 p_impact_type_code => p_impact_type_code,
324 p_status_code => p_status_code,
325 p_description => l_description,
326 p_implementation_date => l_implementation_date,
327 p_implemented_by => l_implemented_by,
328 p_impby_name => p_impby_name,
329 p_impby_type_id => p_impby_type_id,
330 p_implementation_comment => l_implementation_comment,
331 p_record_version_number => p_record_version_number,
332 p_impacted_task_id => l_task_id,
333 x_return_status => x_return_status,
334 x_msg_count => x_msg_count,
335 x_msg_data => x_msg_data
336
337 );
338 END LOOP;
339
340
341 END IF;
342
343 x_msg_count := FND_MSG_PUB.Count_Msg;
344 IF x_msg_count = 1 THEN
345 pa_interface_utils_pub.get_messages ( p_encoded => 'T'
346 ,p_msg_index => 1
347 ,p_data => x_msg_data
348 ,p_msg_index_out => l_msg_index_out
349 );
350 END IF;
351
352
353 -- Reset the error stack when returning to the calling program
354 PA_DEBUG.Reset_Err_Stack;
355
356
357 EXCEPTION
358 WHEN no_data_found THEN
359 IF p_commit = 'T' THEN
360 ROLLBACK TO update_ci_impact;
361 END IF;
362
363 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_XC_RECORD_CHANGED');
364 x_return_status := FND_API.G_RET_STS_ERROR;
365
366
367 WHEN OTHERS THEN
368 IF p_commit = 'T' THEN
369 ROLLBACK TO update_ci_impact;
370 END IF;
371
372 x_return_status := 'U';
373 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_IMPACTS_PUB',
374 p_procedure_name => 'UPDATE_CI_IMPACT',
375 p_error_text => SUBSTRB(SQLERRM,1,240));
376
377 fnd_msg_pub.count_and_get(p_count => x_msg_count,
378 p_data => x_msg_data);
379 END update_ci_impact;
380
381 END PA_CI_IMPACTS_pub;