[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_SETS_PKG
Source
1 PACKAGE BODY pa_project_sets_pkg AS
2 /*$Header: PAPPSPKB.pls 120.1 2005/08/19 16:43:34 mwasowic noship $*/
3 --+
4
5 PROCEDURE insert_row
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_attribute_category IN pa_project_sets_b.attribute_category%TYPE
13 ,p_attribute1 IN pa_project_sets_b.attribute1%TYPE
14 ,p_attribute2 IN pa_project_sets_b.attribute2%TYPE
15 ,p_attribute3 IN pa_project_sets_b.attribute3%TYPE
16 ,p_attribute4 IN pa_project_sets_b.attribute4%TYPE
17 ,p_attribute5 IN pa_project_sets_b.attribute5%TYPE
18 ,p_attribute6 IN pa_project_sets_b.attribute6%TYPE
19 ,p_attribute7 IN pa_project_sets_b.attribute7%TYPE
20 ,p_attribute8 IN pa_project_sets_b.attribute8%TYPE
21 ,p_attribute9 IN pa_project_sets_b.attribute9%TYPE
22 ,p_attribute10 IN pa_project_sets_b.attribute10%TYPE
23 ,p_attribute11 IN pa_project_sets_b.attribute11%TYPE
24 ,p_attribute12 IN pa_project_sets_b.attribute12%TYPE
25 ,p_attribute13 IN pa_project_sets_b.attribute13%TYPE
26 ,p_attribute14 IN pa_project_sets_b.attribute14%TYPE
27 ,p_attribute15 IN pa_project_sets_b.attribute15%TYPE
28 ,x_project_set_id OUT NOCOPY pa_project_sets_b.project_set_id%TYPE --File.Sql.39 bug 4440895
29 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
30 )
31 IS
32 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
33 BEGIN
34
35 -- Initialize the return status to success
36 x_return_status := FND_API.G_RET_STS_SUCCESS;
37
38 --Log Message
39 IF l_enable_log = 'Y' THEN
40 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_PROJECT_SET_Pkg.insert_row.begin'
41 ,x_msg => 'Beginning of insert row'
42 ,x_log_level => 5);
43 END IF;
44
45 INSERT INTO pa_project_sets_b
46 (project_set_id
47 ,party_id
48 ,effective_start_date
49 ,effective_end_date
50 ,access_level
51 ,attribute_category
52 ,attribute1
53 ,attribute2
54 ,attribute3
55 ,attribute4
56 ,attribute5
57 ,attribute6
58 ,attribute7
59 ,attribute8
60 ,attribute9
61 ,attribute10
62 ,attribute11
63 ,attribute12
64 ,attribute13
65 ,attribute14
66 ,attribute15
67 ,record_version_number
68 ,creation_date
69 ,created_by
70 ,last_update_date
71 ,last_updated_by
72 ,last_update_login)
73 VALUES
74 ( pa_project_sets_b_s.NEXTVAL
75 ,p_party_id
76 ,p_effective_start_date
77 ,p_effective_end_date
78 ,p_access_level
79 ,p_attribute_category
80 ,p_attribute1
81 ,p_attribute2
82 ,p_attribute3
83 ,p_attribute4
84 ,p_attribute5
85 ,p_attribute6
86 ,p_attribute7
87 ,p_attribute8
88 ,p_attribute9
89 ,p_attribute10
90 ,p_attribute11
91 ,p_attribute12
92 ,p_attribute13
93 ,p_attribute14
94 ,p_attribute15
95 ,1
96 ,sysdate
97 ,fnd_global.user_id
98 ,sysdate
99 ,fnd_global.user_id
100 ,fnd_global.login_id
101 )
102 RETURNING project_set_id INTO x_project_set_id;
103
104 INSERT INTO pa_project_sets_tl
105 ( project_set_id
106 ,language
107 ,source_lang
108 ,name
109 ,description
110 ,creation_date
111 ,created_by
112 ,last_update_date
113 ,last_updated_by
114 ,last_update_login)
115 SELECT
116 x_project_set_id
117 ,L.language_code
118 ,userenv('LANG')
119 ,p_project_set_name
120 ,p_description
121 ,sysdate
122 ,fnd_global.user_id
123 ,sysdate
124 ,fnd_global.user_id
125 ,fnd_global.login_id
126 FROM fnd_languages L
127 WHERE l.installed_flag IN ('I', 'B')
128 AND NOT EXISTS
129 (select null
130 from pa_project_sets_tl T
131 where T.project_set_id = x_project_set_id
132 and T.language = L.language_code);
133
134
135 -- Put any message text from message stack into the Message ARRAY
136 EXCEPTION
137 WHEN OTHERS THEN
138 -- Set the excetption Message and the stack
139 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SETS_PKG.Insert_row'
140 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
141 --
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
143 RAISE;
144
145 END Insert_Row;
146
147
148 PROCEDURE update_row
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_attribute_category IN pa_project_sets_b.attribute_category%TYPE
157 ,p_attribute1 IN pa_project_sets_b.attribute1%TYPE
158 ,p_attribute2 IN pa_project_sets_b.attribute2%TYPE
159 ,p_attribute3 IN pa_project_sets_b.attribute3%TYPE
160 ,p_attribute4 IN pa_project_sets_b.attribute4%TYPE
161 ,p_attribute5 IN pa_project_sets_b.attribute5%TYPE
162 ,p_attribute6 IN pa_project_sets_b.attribute6%TYPE
163 ,p_attribute7 IN pa_project_sets_b.attribute7%TYPE
164 ,p_attribute8 IN pa_project_sets_b.attribute8%TYPE
165 ,p_attribute9 IN pa_project_sets_b.attribute9%TYPE
166 ,p_attribute10 IN pa_project_sets_b.attribute10%TYPE
167 ,p_attribute11 IN pa_project_sets_b.attribute11%TYPE
168 ,p_attribute12 IN pa_project_sets_b.attribute12%TYPE
169 ,p_attribute13 IN pa_project_sets_b.attribute13%TYPE
170 ,p_attribute14 IN pa_project_sets_b.attribute14%TYPE
171 ,p_attribute15 IN pa_project_sets_b.attribute15%TYPE
172 ,p_record_version_number IN pa_project_sets_b.record_version_number%TYPE
173 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
174 )
175 IS
176 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
177 BEGIN
178
179 -- Initialize the return status to success
180 x_return_status := FND_API.G_RET_STS_SUCCESS;
181
182 --Log Message
183 IF l_enable_log = 'Y' THEN
184 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_PROJECT_SETS_Pkg.update_row.begin'
185 ,x_msg => 'Beginning of update row'
186 ,x_log_level => 5);
187 END IF;
188
189 UPDATE pa_project_sets_b
190 SET party_id = decode(p_party_id, FND_API.G_MISS_NUM, party_id, p_party_id)
191 ,effective_start_date = decode(p_effective_start_date, FND_API.G_MISS_DATE, effective_start_date, p_effective_start_date)
192 ,effective_end_date = decode(p_effective_end_date, FND_API.G_MISS_DATE, effective_end_date, p_effective_end_date)
193 ,access_level = decode(p_access_level, FND_API.G_MISS_NUM, access_level, p_access_level)
194 ,record_version_number = record_version_number + 1
195 ,attribute_category = decode(p_attribute_category, FND_API.G_MISS_CHAR, attribute_category, p_attribute_category)
196 ,attribute1 = decode(p_attribute1, FND_API.G_MISS_CHAR, attribute1, p_attribute1)
197 ,attribute2 = decode(p_attribute2, FND_API.G_MISS_CHAR, attribute2, p_attribute2)
198 ,attribute3 = decode(p_attribute3, FND_API.G_MISS_CHAR, attribute3, p_attribute3)
199 ,attribute4 = decode(p_attribute4, FND_API.G_MISS_CHAR, attribute4, p_attribute4)
200 ,attribute5 = decode(p_attribute5, FND_API.G_MISS_CHAR, attribute5, p_attribute5)
201 ,attribute6 = decode(p_attribute6, FND_API.G_MISS_CHAR, attribute6, p_attribute6)
202 ,attribute7 = decode(p_attribute7, FND_API.G_MISS_CHAR, attribute7, p_attribute7)
203 ,attribute8 = decode(p_attribute8, FND_API.G_MISS_CHAR, attribute8, p_attribute8)
204 ,attribute9 = decode(p_attribute9, FND_API.G_MISS_CHAR, attribute9, p_attribute9)
205 ,attribute10 = decode(p_attribute10, FND_API.G_MISS_CHAR, attribute10, p_attribute10)
206 ,attribute11 = decode(p_attribute11, FND_API.G_MISS_CHAR, attribute11, p_attribute11)
207 ,attribute12 = decode(p_attribute12, FND_API.G_MISS_CHAR, attribute12, p_attribute12)
208 ,attribute13 = decode(p_attribute13, FND_API.G_MISS_CHAR, attribute13, p_attribute13)
209 ,attribute14 = decode(p_attribute14, FND_API.G_MISS_CHAR, attribute14, p_attribute14)
210 ,attribute15 = decode(p_attribute15, FND_API.G_MISS_CHAR, attribute15, p_attribute15)
211 ,last_update_date = sysdate
212 ,last_updated_by = fnd_global.user_id
213 ,last_update_login = fnd_global.login_id
214 WHERE project_set_id = p_project_set_id
215 AND record_version_number = nvl(p_record_version_number, record_version_number);
216
217 IF (sql%notfound) THEN
218 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
219 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
220 x_return_status := FND_API.G_RET_STS_ERROR;
221 ELSE
222
223 UPDATE pa_project_sets_tl
224 SET name = decode(p_project_set_name, FND_API.G_MISS_CHAR, name, p_project_set_name)
225 ,description = decode(p_description, FND_API.G_MISS_CHAR, description, p_description)
226 ,last_update_date = sysdate
227 ,last_updated_by = fnd_global.user_id
228 ,last_update_login = fnd_global.login_id
229 WHERE project_set_id = p_project_set_id
230 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
231
232 IF (sql%notfound) THEN
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 END IF;
235
236 END IF;
237
238 -- Put any message text from message stack into the Message ARRAY
239 EXCEPTION
240 WHEN OTHERS THEN
241 -- Set the excetption Message and the stack
242 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SETS_PKG.update_row'
243 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
244 --
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
246 RAISE;
247
248 END update_row;
249
250
251 PROCEDURE delete_row
252 ( p_project_set_id IN pa_project_sets_b.project_set_id%TYPE
253 ,p_record_version_number IN pa_project_sets_b.record_version_number%TYPE
254 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
255 )
256 IS
257 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
258 BEGIN
259
260 -- Initialize the return status to success
261 x_return_status := FND_API.G_RET_STS_SUCCESS;
262
263 DELETE FROM pa_project_sets_b
264 WHERE project_set_id = p_project_set_id
265 AND nvl(p_record_version_number, record_version_number) = record_version_number;
266
267 IF (SQL%NOTFOUND) THEN
268 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
269 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 ELSE
272
273 DELETE FROM pa_project_sets_tl
274 WHERE project_set_id = p_project_set_id;
275
276 IF (SQL%NOTFOUND) THEN
277 x_return_status := FND_API.G_RET_STS_ERROR;
278 END IF;
279
280 END IF;
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 -- Set the exception Message and the stack
285 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_SETS_PKG.Delete_Row'
286 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
288 RAISE;
289 END Delete_Row;
290
291
292 PROCEDURE insert_row_lines
293 ( p_project_set_id IN pa_project_set_lines.project_set_id%TYPE
294 ,p_project_id IN pa_project_set_lines.project_id%TYPE
295 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
296 )
297 IS
298 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
299 BEGIN
300
301 -- Initialize the return status to success
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303
304 --Log Message
305 IF l_enable_log = 'Y' THEN
306 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_PROJECT_SET_Pkg.insert_row_lines.begin'
307 ,x_msg => 'Beginning of insert row lines'
308 ,x_log_level => 5);
309 END IF;
310
311 INSERT INTO pa_project_set_lines(
312 project_set_id
313 ,project_id
314 ,creation_date
315 ,created_by
316 ,last_update_date
317 ,last_updated_by
318 ,last_update_login)
319 VALUES ( p_project_set_id
320 ,p_project_id
321 ,sysdate
322 ,fnd_global.user_id
323 ,sysdate
324 ,fnd_global.user_id
325 ,fnd_global.login_id);
326
327 -- Put any message text from message stack into the Message ARRAY
328 EXCEPTION
329 WHEN OTHERS THEN
330 -- Set the exception Message and the stack
331 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SET_LINES_PKG.Insert_row_lines'
332 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
333 --
334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
335 RAISE;
336
337 END Insert_Row_Lines;
338
339
340 PROCEDURE delete_row_lines
341 ( p_project_set_id IN pa_project_set_lines.project_set_id%TYPE
342 ,p_project_id IN pa_project_set_lines.project_id%TYPE
343 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
344 )
345 IS
346 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
347 BEGIN
348
349 -- Initialize the return status to success
350 x_return_status := FND_API.G_RET_STS_SUCCESS;
351
352 --Log Message
353 IF l_enable_log = 'Y' THEN
354 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_PROJECT_SET_Pkg.delete_row_lines.begin'
355 ,x_msg => 'Beginning of delete row lines'
356 ,x_log_level => 5);
357 END IF;
358
359 DELETE FROM pa_project_set_lines
360 WHERE project_set_id = p_project_set_id
361 AND project_id = p_project_id;
362
363
364 -- Put any message text from message stack into the Message ARRAY
365 EXCEPTION
366 WHEN OTHERS THEN
367 -- Set the exception Message and the stack
368 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SET_LINES_PKG.Delete_row_lines'
369 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
370 --
371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
372 RAISE;
373
374 END Delete_Row_Lines;
375
376
377 --
378 -- The following procedure is generated by utility /fnddev/fnd/11.5/bin/tltblgen
379 -- This is needed by MLS processing. See bug 3024610 for details.
380 --
381 procedure ADD_LANGUAGE
382 is
383 begin
384 delete from PA_PROJECT_SETS_TL T
385 where not exists
386 (select NULL
387 from PA_PROJECT_SETS_B B
388 where B.PROJECT_SET_ID = T.PROJECT_SET_ID
389 );
390
391 update PA_PROJECT_SETS_TL T set (
392 NAME,
393 DESCRIPTION
394 ) = (select
395 B.NAME,
396 B.DESCRIPTION
397 from PA_PROJECT_SETS_TL B
398 where B.PROJECT_SET_ID = T.PROJECT_SET_ID
399 and B.LANGUAGE = T.SOURCE_LANG)
400 where (
401 T.PROJECT_SET_ID,
402 T.LANGUAGE
403 ) in (select
404 SUBT.PROJECT_SET_ID,
405 SUBT.LANGUAGE
406 from PA_PROJECT_SETS_TL SUBB, PA_PROJECT_SETS_TL SUBT
407 where SUBB.PROJECT_SET_ID = SUBT.PROJECT_SET_ID
408 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
409 and (SUBB.NAME <> SUBT.NAME
410 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
411 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
412 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
413 ));
414
415 insert into PA_PROJECT_SETS_TL (
416 PROJECT_SET_ID,
417 NAME,
418 DESCRIPTION,
419 CREATED_BY,
420 CREATION_DATE,
421 LAST_UPDATED_BY,
422 LAST_UPDATE_DATE,
423 LAST_UPDATE_LOGIN,
424 LANGUAGE,
425 SOURCE_LANG
426 ) select
427 B.PROJECT_SET_ID,
428 B.NAME,
429 B.DESCRIPTION,
430 B.CREATED_BY,
431 B.CREATION_DATE,
432 B.LAST_UPDATED_BY,
433 B.LAST_UPDATE_DATE,
434 B.LAST_UPDATE_LOGIN,
435 L.LANGUAGE_CODE,
436 B.SOURCE_LANG
437 from PA_PROJECT_SETS_TL B, FND_LANGUAGES L
438 where L.INSTALLED_FLAG in ('I', 'B')
439 and B.LANGUAGE = userenv('LANG')
440 and not exists
441 (select NULL
442 from PA_PROJECT_SETS_TL T
443 where T.PROJECT_SET_ID = B.PROJECT_SET_ID
444 and T.LANGUAGE = L.LANGUAGE_CODE);
445 end ADD_LANGUAGE;
446
447 end PA_PROJECT_SETS_PKG;