[Home] [Help]
PACKAGE BODY: APPS.PA_CI_IMPACTS_PVT
Source
1 PACKAGE BODY PA_CI_IMPACTS_pvt AS
2 /* $Header: PACIIPVB.pls 120.1 2005/08/02 03:58:29 raluthra 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
21 x_ci_impact_id OUT NOCOPY NUMBER,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2
25 )
26 IS
27 l_rowid VARCHAR2(30);
28
29
30 CURSOR check_exists is
31 SELECT 'Y' FROM dual
32 WHERE exists (SELECT ci_impact_id FROM
33 pa_ci_impacts
34 WHERE
35 ci_id = p_ci_id
36 AND impact_type_code = p_impact_type_code);
37
38 l_dummy VARCHAR2(1);
39
40 BEGIN
41 pa_debug.set_err_stack ('PA_CI_IMPACTS_PVT.CREATE_CI_IMPACTS');
42
43 IF p_commit = 'T' THEN
44 SAVEPOINT create_ci_impact;
45 END IF;
46
47 IF p_init_msg_list = 'T' THEN
48 fnd_msg_pub.initialize;
49 END IF;
50
51 x_return_status := 'S';
52 x_msg_count := 0;
53 x_msg_data := '';
54
55 OPEN check_exists;
56 FETCH check_exists INTO l_dummy;
57 IF check_exists%found THEN
58 -- record already exists
59 PA_UTILS.Add_Message( p_app_short_name => 'PA'
60 ,p_msg_name => 'PA_CI_IMPACT_EXIST');
61
62 x_return_status := FND_API.G_RET_STS_ERROR;
63
64 END IF;
65 CLOSE check_exists;
66
67 IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
68 PA_CI_IMPACTS_pkg.insert_row(
69 x_rowid => l_rowid,
70 x_ci_impact_id => x_ci_impact_id,
71 x_ci_id => p_ci_id,
72 x_impact_type_code => p_impact_type_code,
73 x_status_code => p_status_code,
74 x_description => p_description,
75 x_implementation_date => p_implementation_date,
76 x_implemented_by => p_implemented_by,
77 x_implementation_comment => p_implementation_comment,
78 x_impacted_task_id => p_impacted_task_id,
79 x_creation_date => sysdate,
80 x_created_by => fnd_global.user_id,
81 x_last_update_date => sysdate,
82 x_last_updated_by => fnd_global.user_id,
83 x_last_update_login => fnd_global.login_id);
84 END IF;
85
86 IF p_commit = 'T' THEN
87 IF x_return_status = 'S' THEN
88 COMMIT;
89 ELSE
90 ROLLBACK TO create_ci_impact;
91 END IF;
92 END IF;
93
94 fnd_msg_pub.count_and_get(p_count => x_msg_count,
95 p_data => x_msg_data);
96
97 pa_debug.reset_err_stack;
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 IF p_commit = 'T' THEN
102 ROLLBACK TO create_ci_impact;
103 END IF;
104
105 x_return_status := 'U';
106 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_IMPACTS_PVT',
107 p_procedure_name => 'CREATE_CI_IMPACT',
108 p_error_text => SUBSTRB(SQLERRM,1,240));
109
110 fnd_msg_pub.count_and_get(p_count => x_msg_count,
111 p_data => x_msg_data);
112 END create_ci_impact;
113
114 PROCEDURE delete_ci_impact (
115 p_api_version IN NUMBER := 1.0,
116 p_init_msg_list IN VARCHAR2 := 'T',
117 p_commit IN VARCHAR2 := 'F',
118 p_validate_only IN VARCHAR2 := 'T',
119 p_max_msg_count IN NUMBER := null,
120
121 p_ci_impact_id IN NUMBER := null,
122 p_record_version_number IN NUMBER := null,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_msg_count OUT NOCOPY NUMBER,
125 x_msg_data OUT NOCOPY VARCHAR2
126 )
127 IS
128 l_temp VARCHAR2(1);
129 BEGIN
130 pa_debug.set_err_stack ('PA_CI_IMPACTS_PVT.DELETE_CI_IMPACT');
131
132 IF p_commit = 'T' THEN
133 SAVEPOINT delete_ci_impact;
134 END IF;
135
136 IF p_init_msg_list = 'T' THEN
137 fnd_msg_pub.initialize;
138 END IF;
139
140 x_return_status := 'S';
141 x_msg_count := 0;
142 x_msg_data := '';
143
144 -- check if it is OK to delete
145 pa_ci_impacts_util.is_delete_impact_ok
146 (
147 p_ci_impact_id,
148 x_return_status ,
149 x_msg_count ,
150 x_msg_data
151 );
152
153 IF x_return_status = 'S' then
154 -- Trying to lock the record
155 /* PA_CI_IMPACTS_pkg.lock_row (
156 x_ci_impact_id => p_ci_impact_id,
157 x_impact_type_code => p_impact_type_code,
158 x_record_version_number => p_record_version_number,
159 x_ci_id => p_ci_id);*/
160
161 IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
162 PA_CI_IMPACTS_pkg.delete_row(
163 x_ci_impact_id => p_ci_impact_id,
164 x_record_version_number => p_record_version_number
165 );
166 END IF;
167
168 END IF;
169
170 IF p_commit = 'T' THEN
171 IF x_return_status = 'S' THEN
172 COMMIT;
173 ELSE
174 ROLLBACK TO delete_ci_impact;
175 END IF;
176 END IF;
177
178 fnd_msg_pub.count_and_get(p_count => x_msg_count,
179 p_data => x_msg_data);
180
181 pa_debug.reset_err_stack;
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 IF p_commit = 'T' THEN
186 ROLLBACK TO delete_ci_impact;
187 END IF;
188
189 x_return_status := 'U';
190 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_IMPACT',
191 p_procedure_name => 'DELETE_CI_IMPACT',
192 p_error_text => SUBSTRB(SQLERRM,1,240));
193
194 fnd_msg_pub.count_and_get(p_count => x_msg_count,
195 p_data => x_msg_data);
196 END delete_ci_impact;
197
198
199 PROCEDURE update_ci_impact (
200 p_api_version IN NUMBER := 1.0,
201 p_init_msg_list IN VARCHAR2 := 'T',
202 p_commit IN VARCHAR2 := 'F',
203 p_validate_only IN VARCHAR2 := 'T',
204 p_max_msg_count IN NUMBER := null,
205 p_ci_impact_id IN NUMBER:= null,
206 p_ci_id IN NUMBER := null,
207 p_impact_type_code IN VARCHAR2 := null,
208 p_status_code IN VARCHAR2 := null,
209 p_description IN VARCHAR2 := null,
210 p_implementation_date IN DATE := null,
211 p_implemented_by IN NUMBER := null,
212 p_impby_name IN VARCHAR2 := null,
213 p_impby_type_id IN NUMBER := null,
214 p_implementation_comment IN VARCHAR2 := null,
215 p_record_version_number IN NUMBER := null,
216 p_impacted_task_id IN NUMBER := null,
217
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2
221 )
222 IS
223 l_rowid VARCHAR2(30);
224 l_party_id NUMBER;
225 l_impact_name VARCHAR2(80);
226
227 CURSOR get_internal_party_id
228 IS
229 select hp.party_id from fnd_user fu,
230 hz_parties hp
231 where
232 user_id = FND_GLOBAL.user_id
233 and employee_id is not null
234 and hp.orig_system_reference = 'PER:' || fu.employee_id;
235
236
237 CURSOR get_external_party_id
238 IS
239 select hp.party_id from fnd_user fu,
240 hz_parties hp
241 where
242 user_id = FND_GLOBAL.user_id
243 and employee_id is null
244 and hp.party_id = fu.person_party_id; -- fu.customer_id; Changed for Bug 4527617
245
246 CURSOR get_party_id is
247 SELECT party_id FROM
248 hz_parties
249 WHERE party_name = p_impby_name;
250
251
252 CURSOR get_impact_name
253 IS SELECT pl.meaning
254 FROM pa_lookups pl
255 WHERE p_impact_type_code = pl.lookup_code
256 and pl.lookup_type = 'PA_CI_IMPACT_TYPES';
257
258 BEGIN
259 pa_debug.set_err_stack ('PA_CI_IMPACTS_PVT.UPDATE_CI_IMPACTS');
260
261 IF p_commit = 'T' THEN
262 SAVEPOINT update_ci_impact;
263 END IF;
264
265 IF p_init_msg_list = 'T' THEN
266 fnd_msg_pub.initialize;
267 END IF;
268
269 x_return_status := 'S';
270 x_msg_count := 0;
271 x_msg_data := '';
272
273 OPEN get_impact_name;
274 FETCH get_impact_name INTO l_impact_name;
275 CLOSE get_impact_name;
276
277 -- debug_msg_s1 ('p_implemented_by = ' ||p_implemented_by);
278 -- debug_msg_s1 ('p_implemented_by = ' ||p_impby_name);
279
280 IF p_implemented_by = 0 THEN
281 IF p_impby_name IS NULL THEN
282 -- we need to use the FND_GLOBAL.user_id;
283 OPEN get_internal_party_id;
284 FETCH get_internal_party_id INTO l_party_id;
285 IF get_internal_party_id%notfound THEN
286 CLOSE get_internal_party_id;
287 OPEN get_external_party_id;
288 FETCH get_external_party_id INTO l_party_id;
289 CLOSE get_internal_party_id;
290
291 ELSE
292 CLOSE get_internal_party_id;
293 END IF;
294 ELSE
295 -- the implemented by is passed in, we need to get the ID
296 OPEN get_party_id;
297 FETCH get_party_id INTO l_party_id;
298 IF get_party_id%notfound THEN
299 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_CI_IMPACT_IMPBY_INV');
300 -- p_token1 => 'IMPACT_TYPE'
301 --, p_value1 => l_impact_name);
302 x_return_status := FND_API.G_RET_STS_ERROR;
303 END IF;
304
305 CLOSE get_party_id;
306
307 END IF;
308
309
310 ELSE
311 IF p_impby_name IS NULL then
312 l_party_id := p_implemented_by;
313 ELSE
314 -- the implemented by is passed in, we need to get the ID
315 OPEN get_party_id;
316 FETCH get_party_id INTO l_party_id;
317 IF get_party_id%notfound THEN
318 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_CI_IMPACT_IMPBY_INV');
319 -- p_token1 => 'IMPACT_TYPE'
320 -- , p_value1 => l_impact_name);
321 x_return_status := FND_API.G_RET_STS_ERROR;
322 END IF;
323
324 CLOSE get_party_id;
325
326 END IF;
327
328 END IF;
329
330
331 IF (p_implementation_date > Sysdate) THEN
332
333
334 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_CI_IMPACT_IMP_DATE_INV');
335 -- p_token1 => 'IMPACT_TYPE'
336 --, p_value1 => l_impact_name
337
338 x_return_status := FND_API.G_RET_STS_ERROR;
339 END IF;
340
341
342 IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
343 PA_CI_IMPACTS_pkg.update_row(
344 x_ci_impact_id => p_ci_impact_id,
345 x_ci_id => p_ci_id,
346 x_impact_type_code => p_impact_type_code,
347 x_status_code => p_status_code,
348 x_description => p_description,
349 x_implementation_date => p_implementation_date,
350 x_implemented_by => l_party_id,
351 x_implementation_comment => p_implementation_comment,
352 x_record_version_number => p_record_version_number,
353 x_impacted_task_id => p_impacted_task_id,
354 x_last_update_date => sysdate,
355 x_last_updated_by => fnd_global.user_id,
356 x_last_update_login => fnd_global.login_id );
357
358 END IF;
359
360 IF p_commit = 'T' THEN
361 IF x_return_status = 'S' THEN
362 COMMIT;
363 ELSE
364 ROLLBACK TO update_ci_impact;
365 END IF;
366 END IF;
367
368 fnd_msg_pub.count_and_get(p_count => x_msg_count,
369 p_data => x_msg_data);
370
371 pa_debug.reset_err_stack;
372
373 EXCEPTION
374 WHEN no_data_found THEN
375 IF p_commit = 'T' THEN
376 ROLLBACK TO update_ci_impact;
377 END IF;
378
379 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_XC_RECORD_CHANGED');
380 x_return_status := FND_API.G_RET_STS_ERROR;
381
382
383 WHEN OTHERS THEN
384 IF p_commit = 'T' THEN
385 ROLLBACK TO update_ci_impact;
386 END IF;
387
388 x_return_status := 'U';
389 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_IMPACTS_PVT',
390 p_procedure_name => 'UPDATE_CI_IMPACT',
391 p_error_text => SUBSTRB(SQLERRM,1,240));
392
393 fnd_msg_pub.count_and_get(p_count => x_msg_count,
394 p_data => x_msg_data);
395 END update_ci_impact;
396
397 END PA_CI_IMPACTS_pvt;