[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_SETS_PVT
Source
1 PACKAGE BODY pa_project_sets_pvt AS
2 /*$Header: PAPPSPVB.pls 120.2 2005/08/23 05:29:31 avaithia noship $*/
3
4 --
5 PROCEDURE create_project_set
6 ( p_project_set_name IN pa_project_sets_tl.name%TYPE
7 ,p_party_id IN pa_project_sets_b.party_id%TYPE
8 ,p_effective_start_date IN pa_project_sets_b.effective_start_date%TYPE
9 ,p_effective_end_date IN pa_project_sets_b.effective_end_date%TYPE
10 ,p_access_level IN pa_project_sets_b.access_level%TYPE
11 ,p_description IN pa_project_sets_tl.description%TYPE
12 ,p_party_name IN hz_parties.party_name%TYPE
13 ,p_attribute_category IN pa_project_sets_b.attribute_category%TYPE
14 ,p_attribute1 IN pa_project_sets_b.attribute1%TYPE
15 ,p_attribute2 IN pa_project_sets_b.attribute2%TYPE
16 ,p_attribute3 IN pa_project_sets_b.attribute3%TYPE
17 ,p_attribute4 IN pa_project_sets_b.attribute4%TYPE
18 ,p_attribute5 IN pa_project_sets_b.attribute5%TYPE
19 ,p_attribute6 IN pa_project_sets_b.attribute6%TYPE
20 ,p_attribute7 IN pa_project_sets_b.attribute7%TYPE
21 ,p_attribute8 IN pa_project_sets_b.attribute8%TYPE
22 ,p_attribute9 IN pa_project_sets_b.attribute9%TYPE
23 ,p_attribute10 IN pa_project_sets_b.attribute10%TYPE
24 ,p_attribute11 IN pa_project_sets_b.attribute11%TYPE
25 ,p_attribute12 IN pa_project_sets_b.attribute12%TYPE
26 ,p_attribute13 IN pa_project_sets_b.attribute13%TYPE
27 ,p_attribute14 IN pa_project_sets_b.attribute14%TYPE
28 ,p_attribute15 IN pa_project_sets_b.attribute15%TYPE
29 ,x_project_set_id OUT NOCOPY pa_project_sets_b.project_set_id%TYPE --File.Sql.39 bug 4440895
30 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
31 )
32 IS
33 l_party_id pa_project_sets_b.party_id%TYPE := p_party_id;
34 l_return_status VARCHAR2(1);
35 l_unique VARCHAR2(1);
36 l_msg_count NUMBER;
37 l_msg_data VARCHAR2(240);
38 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
39
40 BEGIN
41
42 -- Initialize the return status to success
43 x_return_status := FND_API.G_RET_STS_SUCCESS;
44
45 --Log Message
46 IF l_enable_log = 'Y' THEN
47 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_PROJECT_SETS_PVT.Create_Project_Set.begin'
48 ,x_msg => 'Beginning of Create_Project_Set pvt'
49 ,x_log_level => 5);
50 END IF;
51 -- have to get the party_id of the owner if party_id passed in is NULL
52 IF p_party_id IS NULL THEN
53 PA_PROJECT_SET_UTILS.Check_PartyName_Or_Id (
54 p_party_id => p_party_id,
55 p_party_name => p_party_name,
56 p_check_id_flag => 'Y',
57 x_party_id => l_party_id,
58 x_return_status => l_return_status,
59 x_error_msg_code => l_msg_data);
60 END IF;
61
62 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
63 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
64 ,p_msg_name => l_msg_data);
65 END IF;
66
67 -- perform a check that the project set name must be unique
68 l_unique := PA_PROJECT_SET_UTILS.is_name_unique(p_project_set_name);
69
70 IF l_unique = 'N' THEN
71 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
72 ,p_msg_name => 'PA_PROJECT_SET_NAME_NOT_UNIQUE');
73 END IF;
74
75 -- check the dates
76 IF p_effective_end_date IS NOT NULL THEN
77 IF p_effective_start_date > p_effective_end_date THEN
78 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
79 ,p_msg_name => 'PA_INVALID_START_DATE');
80 END IF;
81 END IF;
82
83
84 IF FND_MSG_PUB.Count_Msg =0 THEN
85
86 IF l_enable_log = 'Y' THEN
87 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_PROJECT_SETS_PVT.Create_Project_Set'
88 ,x_msg => 'before pa_project_sets_pkg.insert_row'
89 ,x_log_level => 5);
90 END IF;
91
92 PA_PROJECT_SETS_PKG.insert_row
93 (p_project_set_name => p_project_set_name
94 ,p_party_id => l_party_id
95 ,p_effective_start_date => p_effective_start_date
96 ,p_effective_end_date => p_effective_end_date
97 ,p_access_level => p_access_level
98 ,p_description => p_description
99 ,p_attribute_category => p_attribute_category
100 ,p_attribute1 => p_attribute1
101 ,p_attribute2 => p_attribute2
102 ,p_attribute3 => p_attribute3
103 ,p_attribute4 => p_attribute4
104 ,p_attribute5 => p_attribute5
105 ,p_attribute6 => p_attribute6
106 ,p_attribute7 => p_attribute7
107 ,p_attribute8 => p_attribute8
108 ,p_attribute9 => p_attribute9
109 ,p_attribute10 => p_attribute10
110 ,p_attribute11 => p_attribute11
111 ,p_attribute12 => p_attribute12
112 ,p_attribute13 => p_attribute13
113 ,p_attribute14 => p_attribute14
114 ,p_attribute15 => p_attribute15
115 ,x_project_set_id => x_project_set_id
116 ,x_return_status => l_return_status);
117
118 END IF;
119
120 IF l_enable_log = 'Y' THEN
121 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_PROJECT_SETS_PVT.Create_Project_Set'
122 ,x_msg => 'x_project_set_id = '|| x_project_set_id
123 ,x_log_level => 5);
124 END IF;
125
126 IF FND_MSG_PUB.Count_Msg > 0 THEN
127 x_return_status := FND_API.G_RET_STS_ERROR;
128 END IF;
129
130 EXCEPTION
131 WHEN OTHERS THEN
132
133 -- Set the excetption Message and the stack
134 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SETS_PVT.Create_Project_Set'
135 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
136 --
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
138
139 -- Start : RESET other OUT param too : 4537865
140 x_project_set_id := NULL ;
141 -- ENd : 4537865
142
143 --RAISE;
144
145 END Create_Project_Set;
146
147
148 PROCEDURE update_project_set
149 ( p_project_set_id IN pa_project_sets_b.project_set_id%TYPE
150 ,p_project_set_name IN pa_project_sets_tl.name%TYPE
151 ,p_party_id IN pa_project_sets_b.party_id%TYPE
152 ,p_effective_start_date IN pa_project_sets_b.effective_start_date%TYPE
153 ,p_effective_end_date IN pa_project_sets_b.effective_end_date%TYPE
154 ,p_access_level IN pa_project_sets_b.access_level%TYPE
155 ,p_description IN pa_project_sets_tl.description%TYPE
156 ,p_party_name IN hz_parties.party_name%TYPE
157 ,p_attribute_category IN pa_project_sets_b.attribute_category%TYPE
158 ,p_attribute1 IN pa_project_sets_b.attribute1%TYPE
159 ,p_attribute2 IN pa_project_sets_b.attribute2%TYPE
160 ,p_attribute3 IN pa_project_sets_b.attribute3%TYPE
161 ,p_attribute4 IN pa_project_sets_b.attribute4%TYPE
162 ,p_attribute5 IN pa_project_sets_b.attribute5%TYPE
163 ,p_attribute6 IN pa_project_sets_b.attribute6%TYPE
164 ,p_attribute7 IN pa_project_sets_b.attribute7%TYPE
165 ,p_attribute8 IN pa_project_sets_b.attribute8%TYPE
166 ,p_attribute9 IN pa_project_sets_b.attribute9%TYPE
167 ,p_attribute10 IN pa_project_sets_b.attribute10%TYPE
168 ,p_attribute11 IN pa_project_sets_b.attribute11%TYPE
169 ,p_attribute12 IN pa_project_sets_b.attribute12%TYPE
170 ,p_attribute13 IN pa_project_sets_b.attribute13%TYPE
171 ,p_attribute14 IN pa_project_sets_b.attribute14%TYPE
172 ,p_attribute15 IN pa_project_sets_b.attribute15%TYPE
173 ,p_record_version_number IN pa_project_sets_b.record_version_number%TYPE
174 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
175 )
176 IS
177 l_party_id pa_project_sets_b.party_id%TYPE := p_party_id;
178 l_return_status VARCHAR2(1);
179 l_unique VARCHAR2(1);
180 l_msg_count NUMBER;
181 l_msg_data VARCHAR2(240);
182 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
183
184 BEGIN
185
186 -- Initialize the return status to success
187 x_return_status := FND_API.G_RET_STS_SUCCESS;
188
189 --Log Message
190 IF l_enable_log = 'Y' THEN
191 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_PROJECT_SETS_PVT.Update_Project_Set.begin'
192 ,x_msg => 'Beginning of Update_Project_Set pvt'
193 ,x_log_level => 5);
194 END IF;
195
196 -- have to get the party_id of the owner if party_id passed in is NULL
197 IF p_party_id IS NULL THEN
198 PA_PROJECT_SET_UTILS.Check_PartyName_Or_Id (
199 p_party_id => p_party_id,
200 p_party_name => p_party_name,
201 p_check_id_flag => 'Y',
202 x_party_id => l_party_id,
203 x_return_status => l_return_status,
204 x_error_msg_code => l_msg_data);
205 END IF;
206
207 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
208 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
209 ,p_msg_name => l_msg_data);
210 END IF;
211
212 l_unique := PA_PROJECT_SET_UTILS.is_name_unique(p_project_set_name, p_project_set_id);
213
214 IF l_unique = 'N' THEN
215 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
216 ,p_msg_name => 'PA_PROJECT_SET_NAME_NOT_UNIQUE');
217 END IF;
218
219 IF p_effective_end_date IS NOT NULL THEN
220 IF p_effective_start_date > p_effective_end_date THEN
221 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
222 ,p_msg_name => 'PA_INVALID_START_DATE');
223 END IF;
224 END IF;
225
226
227 IF FND_MSG_PUB.Count_Msg =0 THEN
228 IF l_enable_log = 'Y' THEN
229 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_PROJECT_SETS_PVT.Update_Project_Set'
230 ,x_msg => 'before pa_project_sets_pkg.insert_row'
231 ,x_log_level => 5);
232 END IF;
233
234 PA_PROJECT_SETS_PKG.update_row
235 (p_project_set_id => p_project_set_id
236 ,p_project_set_name => p_project_set_name
237 ,p_party_id => l_party_id
238 ,p_effective_start_date => p_effective_start_date
239 ,p_effective_end_date => p_effective_end_date
240 ,p_access_level => p_access_level
241 ,p_description => p_description
242 ,p_attribute_category => p_attribute_category
243 ,p_attribute1 => p_attribute1
244 ,p_attribute2 => p_attribute2
245 ,p_attribute3 => p_attribute3
246 ,p_attribute4 => p_attribute4
247 ,p_attribute5 => p_attribute5
248 ,p_attribute6 => p_attribute6
249 ,p_attribute7 => p_attribute7
250 ,p_attribute8 => p_attribute8
251 ,p_attribute9 => p_attribute9
252 ,p_attribute10 => p_attribute10
253 ,p_attribute11 => p_attribute11
254 ,p_attribute12 => p_attribute12
255 ,p_attribute13 => p_attribute13
256 ,p_attribute14 => p_attribute14
257 ,p_attribute15 => p_attribute15
258 ,p_record_version_number => p_record_version_number
259 ,x_return_status => l_return_status);
260
261 END IF;
262
263 IF FND_MSG_PUB.Count_Msg > 0 THEN
264 x_return_status := FND_API.G_RET_STS_ERROR;
265 END IF;
266
267 EXCEPTION
268 WHEN OTHERS THEN
269
270 -- Set the excetption Message and the stack
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
271 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SETS_PVT.Update_Project_Set'
272 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
273 --
275 --RAISE;
276
277 END Update_Project_Set;
278
279
280 PROCEDURE delete_project_set
281 ( p_project_set_id IN pa_project_sets_b.project_set_id%TYPE
282 ,p_record_version_number IN pa_project_sets_b.record_version_number%TYPE
283 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
284 )
285 IS
286 l_return_status VARCHAR2(1);
287 l_do_lines_exist VARCHAR2(1);
288 l_project_set_lines_tbl pa_project_set_utils.project_set_lines_tbl_type;
289 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
290
291 BEGIN
292
293 -- Initialize the return status to success
294 x_return_status := FND_API.G_RET_STS_SUCCESS;
295
296 --Log Message
297 IF l_enable_log = 'Y' THEN
298 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_PROJECT_SETS_PVT.Delete_Project_Set'
299 ,x_msg => 'Beginning of Delete_Project_Set pvt'
300 ,x_log_level => 5);
301 END IF;
302
303 l_project_set_lines_tbl := pa_project_set_utils.get_project_set_lines(p_project_set_id => p_project_set_id);
304
305 IF l_project_set_lines_tbl.COUNT > 0 THEN
306
307 FOR i IN l_project_set_lines_tbl.FIRST .. l_project_set_lines_tbl.LAST LOOP
308
309 delete_project_set_line(p_project_set_id => l_project_set_lines_tbl(i).project_set_id
310 ,p_project_id => l_project_set_lines_tbl(i).project_id
311 ,x_return_status => l_return_status);
312
313 END LOOP;
314
315 END IF;
316
317 IF FND_MSG_PUB.Count_Msg = 0 THEN
318
319 l_do_lines_exist := PA_PROJECT_SET_UTILS.do_lines_exist(p_project_set_id);
320
321 IF l_do_lines_exist = 'N' THEN
322
323 PA_PROJECT_SETS_PKG.delete_row
324 (p_project_set_id => p_project_set_id,
325 p_record_version_number => p_record_version_number,
326 x_return_status => l_return_status);
327
328 END IF;
329
330 END IF;
331
332 IF FND_MSG_PUB.Count_Msg > 0 THEN
333 x_return_status := FND_API.G_RET_STS_ERROR;
334 END IF;
335
336 EXCEPTION
337 WHEN OTHERS THEN
338
339 -- Set the excetption Message and the stack
340 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SETS_PVT.Delete_Project_Set'
341 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
342 --
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
344 --RAISE;
345
346 END Delete_Project_Set;
347
348 PROCEDURE create_project_set_line
349 ( p_project_set_id IN pa_project_set_lines.project_set_id%TYPE
350 ,p_project_id IN pa_project_set_lines.project_id%TYPE
351 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
352 )
353 IS
354 l_return_status VARCHAR2(1);
355 l_exists VARCHAR2(1);
356 l_project_set_id PA_PROJECT_SETS_B.project_set_id%TYPE;
357 e_row_is_locked EXCEPTION;
358 PRAGMA EXCEPTION_INIT(e_row_is_locked, -54);
359 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
360
361 BEGIN
362
363 -- Initialize the return status to success
364 x_return_status := FND_API.G_RET_STS_SUCCESS;
365
366 --Log Message
367 IF l_enable_log = 'Y' THEN
368 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_PROJECT_SETS_PVT.Create_Project_Set_Line.begin'
369 ,x_msg => 'Beginning of Create_Project_Set_Line pvt'
370 ,x_log_level => 5);
371 END IF;
372
373 SELECT project_set_id
374 INTO l_project_set_id
375 FROM pa_project_sets_b
376 WHERE project_set_id = p_project_set_id
377 FOR UPDATE NOWAIT;
378
379 l_exists := PA_PROJECT_SET_UTILS.check_projects_in_set(
380 p_project_set_id => p_project_set_id
381 ,p_project_id => p_project_id);
382
383 IF l_exists = 'N' THEN
384
385 PA_PROJECT_SETS_PKG.insert_row_lines(
386 p_project_set_id => p_project_set_id
387 ,p_project_id => p_project_id
388 ,x_return_status => x_return_status);
389
390 END IF;
391
392 EXCEPTION
393 WHEN e_row_is_locked THEN
394 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
395 ,p_msg_name => 'PA_PROJECT_SET_LOCKED');
396 x_return_status := FND_API.G_RET_STS_ERROR;
397
398 WHEN OTHERS THEN
399 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SETS_PVT.create_project_set_line'
400 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
402 RAISE;
403
404 END create_project_set_line;
405
406
407 PROCEDURE delete_project_set_line
408 ( p_project_set_id IN pa_project_set_lines.project_set_id%TYPE
409 ,p_project_id IN pa_project_set_lines.project_id%TYPE
410 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
411 )
412 IS
413 l_return_status VARCHAR2(1);
414 l_exists VARCHAR2(1);
415 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
416 BEGIN
417
418 -- Initialize the return status to success
419 x_return_status := FND_API.G_RET_STS_SUCCESS;
420
421 --Log Message
425 ,x_log_level => 5);
422 IF l_enable_log = 'Y' THEN
423 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_PROJECT_SETS_PVT.Delete_Project_Set_Line.begin'
424 ,x_msg => 'Beginning of Delete_Project_Set_Line pvt'
426 END IF;
427
428 l_exists := PA_PROJECT_SET_UTILS.check_projects_in_set(
429 p_project_set_id => p_project_set_id
430 ,p_project_id => p_project_id);
431
432 IF l_exists = 'Y' THEN
433
434 -- if the project exists in project set, delete the row
435 PA_PROJECT_SETS_PKG.delete_row_lines(
436 p_project_set_id => p_project_set_id
437 ,p_project_id => p_project_id
438 ,x_return_status => x_return_status);
439 END IF;
440 -- 4537865 : Included Exception Block
441 EXCEPTION
442 WHEN OTHERS THEN
443
444 -- Set the exception Message and the stack
445 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SETS_PVT'
446 ,p_procedure_name => 'Delete_Project_Set_line',
447 p_error_text => SUBSTRB(SQLERRM,1,120)
448 );
449 --
450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
451 -- Not RAISING because caller doesnt expect a RAISE
452
453 END delete_project_set_line;
454
455 PROCEDURE delete_proj_from_proj_set
456 ( p_project_id IN pa_project_set_lines.project_id%TYPE
457 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
458 )
459 IS
460 BEGIN
461 -- Initialize the return status to success
462 x_return_status := FND_API.G_RET_STS_SUCCESS;
463
464 DELETE FROM pa_project_set_lines
465 WHERE project_id = p_project_id;
466
467 EXCEPTION
468 WHEN OTHERS THEN
469 -- Set the exception Message and the stack
470 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SET_LINES_PKG.Delete_project_from_project_sets'
471 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
472
473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
474 RAISE;
475
476 END delete_proj_from_proj_set;
477
478 END;