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