[Home] [Help]
PACKAGE BODY: APPS.JTF_BRMWFATTRVALUE_PVT
Source
1 PACKAGE BODY JTF_BRMWFAttrValue_PVT AS
2 /* $Header: jtfvbwab.pls 120.2 2005/07/05 07:45:10 abraina ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_BRMWFAttrValue_PVT';
5
6 --------------------------------------------------------------------------
7 -- Start of comments
8 -- API name : Create_WFAttrValue
9 -- Type : Private
10 -- Function : Create record in JTF_BRM_WF_ATTR_VALUES table.
11 -- Pre-reqs : None.
12 -- Parameters :
13 -- name direction type required?
14 -- ---- --------- ---- ---------
15 -- p_api_version IN NUMBER required
16 -- p_init_msg_list IN VARCHAR2 optional
17 -- p_commit IN VARCHAR2 optional
18 -- p_validation_level IN NUMBER optional
19 -- x_return_status OUT VARCHAR2 required
20 -- x_msg_count OUT NUMBER required
21 -- x_msg_data OUT VARCHAR2 required
22 -- p_bwa_rec IN brm_wf_attr_value_rec_type required
23 -- x_record_id OUT NUMBER required
24 --
25 -- Version : Current version 1.1
26 -- Previous version 1.0
27 -- Initial version 1.0
28 --
29 -- End of comments
30 --------------------------------------------------------------------------
31 PROCEDURE Create_WFAttrValue
32 ( p_api_version IN NUMBER
33 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
34 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
35 , p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
36 , x_return_status OUT NOCOPY VARCHAR2
37 , x_msg_count OUT NOCOPY NUMBER
38 , x_msg_data OUT NOCOPY VARCHAR2
39 , p_bwa_rec IN brm_wf_attr_value_rec_type
40 , x_record_id OUT NOCOPY NUMBER
41 )
42 IS
43 l_api_name CONSTANT VARCHAR2(30) := 'Create_WFAttrValue';
44 l_api_version CONSTANT NUMBER := 1.1;
45 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
46 l_object_version_number NUMBER := 1;
47 l_return_status VARCHAR2(1);
48 l_rowid ROWID;
49 l_wf_attr_value_id NUMBER;
50
51 CURSOR c IS SELECT ROWID
52 FROM JTF_BRM_WF_ATTR_VALUES
53 WHERE WF_ATTR_VALUE_ID = l_wf_attr_value_id;
54
55 BEGIN
56 --
57 -- Standard start of API savepoint
58 --
59 SAVEPOINT Create_WFAttrValue_PVT;
60
61 --
62 -- Standard call to check for call compatibility
63 --
64 IF NOT FND_API.Compatible_API_Call( l_api_version
65 , p_api_version
66 , l_api_name
67 , G_PKG_NAME)
68 THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71
72 --
73 -- Initialize message list if p_init_msg_list is set to TRUE
74 --
75 IF FND_API.To_Boolean(p_init_msg_list)
76 THEN
77 FND_MSG_PUB.Initialize;
78 END IF;
79
80 --
81 -- Initialize API return status to success
82 --
83 x_return_status := FND_API.G_RET_STS_SUCCESS;
84
85 -- ----------------------------------------------------------------------
86 -- Perform the database operation. Generate the ID from the
87 -- sequence, then insert the passed in attributes into the
88 -- JTF_BRM_WF_ATTR_VALUES table.
89 ----------------------------------------------------------------------
90 IF (p_bwa_rec.wf_attr_value_id IS NULL)
91 THEN
92 SELECT jtf_brm_wf_attr_values_s.NEXTVAL INTO l_wf_attr_value_id
93 FROM DUAL;
94 ELSE
95 l_wf_attr_value_id := p_bwa_rec.wf_attr_value_id;
96 END IF;
97
98 --
99 -- Insert into table
100 --
101 INSERT INTO JTF_BRM_WF_ATTR_VALUES
102 ( WF_ATTR_VALUE_ID
103 , PROCESS_ID
104 , WF_ITEM_TYPE
105 , WF_ACTIVITY_NAME
106 , WF_ACTIVITY_VERSION
107 , WF_ACTIVITY_ATTRIBUTE_NAME
108 , CREATED_BY
109 , CREATION_DATE
110 , LAST_UPDATED_BY
111 , LAST_UPDATE_DATE
112 , LAST_UPDATE_LOGIN
113 , TEXT_VALUE
114 , NUMBER_VALUE
115 , DATE_VALUE
116 , ATTRIBUTE1
117 , ATTRIBUTE2
118 , ATTRIBUTE3
119 , ATTRIBUTE4
120 , ATTRIBUTE5
121 , ATTRIBUTE6
122 , ATTRIBUTE7
123 , ATTRIBUTE8
124 , ATTRIBUTE9
125 , ATTRIBUTE10
126 , ATTRIBUTE11
127 , ATTRIBUTE12
128 , ATTRIBUTE13
129 , ATTRIBUTE14
130 , ATTRIBUTE15
131 , ATTRIBUTE_CATEGORY
132 , SECURITY_GROUP_ID
133 , OBJECT_VERSION_NUMBER
134 , APPLICATION_ID
135 ) VALUES
136 ( l_wf_attr_value_id
137 , p_bwa_rec.PROCESS_ID
138 , p_bwa_rec.WF_ITEM_TYPE
139 , p_bwa_rec.WF_ACTIVITY_NAME
140 , p_bwa_rec.WF_ACTIVITY_VERSION
141 , p_bwa_rec.WF_ACTIVITY_ATTRIBUTE_NAME
142 , p_bwa_rec.CREATED_BY
143 , p_bwa_rec.CREATION_DATE
144 , p_bwa_rec.LAST_UPDATED_BY
145 , p_bwa_rec.LAST_UPDATE_DATE
146 , p_bwa_rec.LAST_UPDATE_LOGIN
147 , p_bwa_rec.TEXT_VALUE
148 , p_bwa_rec.NUMBER_VALUE
149 , p_bwa_rec.DATE_VALUE
150 , p_bwa_rec.ATTRIBUTE1
151 , p_bwa_rec.ATTRIBUTE2
152 , p_bwa_rec.ATTRIBUTE3
153 , p_bwa_rec.ATTRIBUTE4
154 , p_bwa_rec.ATTRIBUTE5
155 , p_bwa_rec.ATTRIBUTE6
156 , p_bwa_rec.ATTRIBUTE7
157 , p_bwa_rec.ATTRIBUTE8
158 , p_bwa_rec.ATTRIBUTE9
159 , p_bwa_rec.ATTRIBUTE10
160 , p_bwa_rec.ATTRIBUTE11
161 , p_bwa_rec.ATTRIBUTE12
162 , p_bwa_rec.ATTRIBUTE13
163 , p_bwa_rec.ATTRIBUTE14
164 , p_bwa_rec.ATTRIBUTE15
165 , p_bwa_rec.ATTRIBUTE_CATEGORY
166 , p_bwa_rec.SECURITY_GROUP_ID
167 , l_object_version_number
168 , p_bwa_rec.APPLICATION_ID
169 );
170
171 --
172 -- Check whether insert was succesful
173 --
174 OPEN c;
175 FETCH c INTO l_rowid;
176 IF (c%NOTFOUND)
177 THEN
178 CLOSE c;
179 RAISE no_data_found;
180 END IF;
181 CLOSE c;
182
183 --
184 -- Return the key value to the caller
185 --
186 x_record_id := l_wf_attr_value_id;
187
188 --
189 -- Standard check of p_commit
190 --
191 IF FND_API.To_Boolean(p_commit)
192 THEN
193 COMMIT WORK;
194 END IF;
195
196 --
197 -- Standard call to get message count and if count is 1, get message info
198 --
199 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
200 , p_data => x_msg_data
201 );
202
203 EXCEPTION
204 WHEN FND_API.G_EXC_ERROR
205 THEN
206 ROLLBACK TO Create_WFAttrValue_PVT;
207 x_return_status := FND_API.G_RET_STS_ERROR;
208 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
209 , p_data => x_msg_data
210 );
211
212 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
213 THEN
214 ROLLBACK TO Create_WFAttrValue_PVT;
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
216 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
217 , p_data => x_msg_data
218 );
219
220 WHEN OTHERS
221 THEN
222 ROLLBACK TO Create_WFAttrValue_PVT;
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
225 THEN
226 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
227 , l_api_name
228 );
229 END IF;
230 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
231 , p_data => x_msg_data
232 );
233
234 END Create_WFAttrValue;
235
236 --------------------------------------------------------------------------
237 -- Start of comments
238 -- API name : Update_WFAttrValue
239 -- Type : Private
240 -- Function : Update record in JTF_BRM_WF_ATTR_VALUES table.
241 -- Pre-reqs : None.
242 -- Parameters :
243 -- name direction type required?
244 -- ---- --------- ---- ---------
245 -- p_api_version IN NUMBER required
246 -- p_init_msg_list IN VARCHAR2 optional
247 -- p_commit IN VARCHAR2 optional
248 -- p_validation_level IN NUMBER optional
249 -- x_return_status OUT VARCHAR2 required
250 -- x_msg_count OUT NUMBER required
251 -- x_msg_data OUT VARCHAR2 required
252 -- p_bel_rec IN brm_wf_attr_value_rec_type required
253 --
254 -- Version : Current version 1.1
255 -- Previous version 1.0
256 -- Initial Version 1.0
257 --
258 -- Notes:
259 --
260 -- End of comments
261 --------------------------------------------------------------------------
262 PROCEDURE Update_WFAttrValue
263 ( p_api_version IN NUMBER
264 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
265 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
266 , p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
267 , x_return_status OUT NOCOPY VARCHAR2
268 , x_msg_count OUT NOCOPY NUMBER
269 , x_msg_data OUT NOCOPY VARCHAR2
270 , p_bwa_rec IN brm_wf_attr_value_rec_type
271 )
272 IS
273 l_api_name CONSTANT VARCHAR2(30) := 'Update_WFAttrValue';
274 l_api_version CONSTANT NUMBER := 1.1;
275 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
276 l_object_version_number NUMBER;
277 l_return_status VARCHAR2(1);
278 l_msg_count NUMBER;
279 l_msg_data VARCHAR2(2000);
280
281 BEGIN
282 --
283 -- Standard start of API savepoint
284 --
285 SAVEPOINT Update_WFAttrValue_PVT;
286
287 --
288 -- Standard call to check for call compatibility
289 --
290 IF NOT FND_API.Compatible_API_Call( l_api_version
291 , p_api_version
292 , l_api_name
293 , G_PKG_NAME
294 )
295 THEN
296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
297 END IF;
298
299 --
300 -- Initialize message list if p_init_msg_list is set to TRUE
301 --
302 IF FND_API.To_Boolean(p_init_msg_list)
303 THEN
304 FND_MSG_PUB.Initialize;
305 END IF;
306
307 --
308 -- Initialize API return status to success
309 --
310 x_return_status := FND_API.G_RET_STS_SUCCESS;
311
312 --
313 -- Get a new object version number
314 --
315 SELECT JTF_BRM_OBJECT_VERSION_S.NEXTVAL
316 INTO l_object_version_number
317 FROM DUAL;
318
319 UPDATE JTF_BRM_WF_ATTR_VALUES
320 SET PROCESS_ID = p_bwa_rec.PROCESS_ID
321 , WF_ITEM_TYPE = p_bwa_rec.WF_ITEM_TYPE
322 , WF_ACTIVITY_NAME = p_bwa_rec.WF_ACTIVITY_NAME
323 , WF_ACTIVITY_VERSION = p_bwa_rec.WF_ACTIVITY_VERSION
324 , WF_ACTIVITY_ATTRIBUTE_NAME = p_bwa_rec.WF_ACTIVITY_ATTRIBUTE_NAME
325 , LAST_UPDATED_BY = p_bwa_rec.LAST_UPDATED_BY
326 , LAST_UPDATE_DATE = p_bwa_rec.LAST_UPDATE_DATE
327 , LAST_UPDATE_LOGIN = p_bwa_rec.LAST_UPDATE_LOGIN
328 , TEXT_VALUE = p_bwa_rec.TEXT_VALUE
329 , NUMBER_VALUE = p_bwa_rec.NUMBER_VALUE
330 , DATE_VALUE = p_bwa_rec.DATE_VALUE
331 , ATTRIBUTE1 = p_bwa_rec.ATTRIBUTE1
332 , ATTRIBUTE2 = p_bwa_rec.ATTRIBUTE2
333 , ATTRIBUTE3 = p_bwa_rec.ATTRIBUTE3
334 , ATTRIBUTE4 = p_bwa_rec.ATTRIBUTE4
335 , ATTRIBUTE5 = p_bwa_rec.ATTRIBUTE5
336 , ATTRIBUTE6 = p_bwa_rec.ATTRIBUTE6
337 , ATTRIBUTE7 = p_bwa_rec.ATTRIBUTE7
338 , ATTRIBUTE8 = p_bwa_rec.ATTRIBUTE8
339 , ATTRIBUTE9 = p_bwa_rec.ATTRIBUTE9
340 , ATTRIBUTE10 = p_bwa_rec.ATTRIBUTE10
341 , ATTRIBUTE11 = p_bwa_rec.ATTRIBUTE11
342 , ATTRIBUTE12 = p_bwa_rec.ATTRIBUTE12
343 , ATTRIBUTE13 = p_bwa_rec.ATTRIBUTE13
344 , ATTRIBUTE14 = p_bwa_rec.ATTRIBUTE14
345 , ATTRIBUTE15 = p_bwa_rec.ATTRIBUTE15
346 , ATTRIBUTE_CATEGORY = p_bwa_rec.ATTRIBUTE_CATEGORY
347 , SECURITY_GROUP_ID = p_bwa_rec.SECURITY_GROUP_ID
348 , OBJECT_VERSION_NUMBER = l_object_version_number
349 , APPLICATION_ID = p_bwa_rec.APPLICATION_ID
350 WHERE WF_ATTR_VALUE_ID = p_bwa_rec.WF_ATTR_VALUE_ID;
351
352 --
353 -- Check whether update was succesful
354 --
355 IF (SQL%NOTFOUND)
356 THEN
357 RAISE no_data_found;
358 END IF;
359
360 --
361 -- Standard check of p_commit
362 --
363 IF FND_API.To_Boolean(p_commit)
364 THEN
365 COMMIT WORK;
366 END IF;
367
368 --
369 -- Standard call to get message count and if count is 1, get message info
370 --
371 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
372 , p_data => x_msg_data
373 );
374
375 EXCEPTION
376 WHEN FND_API.G_EXC_ERROR
377 THEN
378 ROLLBACK TO Update_WFAttrValue_PVT;
379 x_return_status := FND_API.G_RET_STS_ERROR;
380 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
381 , p_data => x_msg_data
382 );
383
384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
385 THEN
386 ROLLBACK TO Update_WFAttrValue_PVT;
387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
388 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
389 , p_data => x_msg_data
390 );
391
392 WHEN OTHERS
393 THEN
394 ROLLBACK TO Update_WFAttrValue_PVT;
395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
396 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
397 THEN
398 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
399 , l_api_name
400 );
401 END IF;
402 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
403 , p_data => x_msg_data
404 );
405
406 END Update_WFAttrValue;
407
408 --------------------------------------------------------------------------
409 -- Start of comments
410 -- API Name : Delete_WFAttrValue
411 -- Type : Private
412 -- Description : Delete record in JTF_BRM_WF_ATTR_VALUES table.
413 -- Pre-reqs : None
414 -- Parameters :
415 -- name direction type required?
416 -- ---- --------- ---- ---------
417 -- p_api_version IN NUMBER required
421 -- x_return_status OUT VARCHAR2 required
418 -- p_init_msg_list IN VARCHAR2 optional
419 -- p_commit IN VARCHAR2 optional
420 -- p_validation_level IN NUMBER optional
422 -- x_msg_count OUT NUMBER required
423 -- x_msg_data OUT VARCHAR2 required
424 -- p_wf_attr_value_id IN NUMBER required
425 --
426 -- Version : Current version 1.1
427 -- Previous version 1.0
428 -- Initial version 1.0
429 --
430 -- Notes: :
431 --
432 -- End of comments
433 --------------------------------------------------------------------------
434 PROCEDURE Delete_WFAttrValue
435 ( p_api_version IN NUMBER
436 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
437 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
438 , p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
439 , x_return_status OUT NOCOPY VARCHAR2
440 , x_msg_count OUT NOCOPY NUMBER
441 , x_msg_data OUT NOCOPY VARCHAR2
442 , p_wf_attr_value_id IN NUMBER
443 )
444 IS
445 l_api_name CONSTANT VARCHAR2(30) := 'Delete_WFAttrValue';
446 l_api_version CONSTANT NUMBER := 1.1;
447 l_api_name_full CONSTANT VARCHAR2(62) := G_PKG_NAME||'.'||l_api_name;
448 l_return_status VARCHAR2(1);
449 l_msg_count NUMBER;
450 l_msg_data VARCHAR2(2000);
451 BEGIN
452 --
453 -- Establish save point
454 --
455 SAVEPOINT Delete_WFAttrValue_PVT;
456
457 --
458 -- Check version number
459 --
460 IF NOT FND_API.Compatible_API_Call( l_api_version
461 , p_api_version
462 , l_api_name
463 , G_PKG_NAME
464 )
465 THEN
466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
467 END IF;
468
469 --
470 -- Initialize message list if requested
471 --
472 IF FND_API.to_Boolean( p_init_msg_list )
473 THEN
474 FND_MSG_PUB.initialize;
475 END IF;
476
477 --
478 -- Initialize return status to SUCCESS
479 --
480 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
481
482 DELETE FROM JTF_BRM_WF_ATTR_VALUES
483 WHERE WF_ATTR_VALUE_ID = p_wf_attr_value_id;
484
485 --
486 -- Check whether delete was succesful
487 --
488 IF (SQL%NOTFOUND)
489 THEN
490 RAISE no_data_found;
491 END IF;
492
493 IF FND_API.To_Boolean( p_commit )
494 THEN
495 COMMIT WORK;
496 END IF;
497
498 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
499 , p_data => X_MSG_DATA
500 );
501
502 EXCEPTION
503 WHEN FND_API.G_EXC_ERROR
504 THEN
505 ROLLBACK TO Delete_WFAttrValue_PVT;
506 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
507 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
508 , p_data => X_MSG_DATA
509 );
510
511 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
512 THEN
513 ROLLBACK TO Delete_WFAttrValue_PVT;
514 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
515 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
516 , p_data => X_MSG_DATA
517 );
518
519 WHEN OTHERS
520 THEN
521 ROLLBACK TO Delete_WFAttrValue_PVT;
522 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
523 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
524 THEN
525 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
526 , l_api_name
527 );
528 END IF;
529 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
530 , p_data => X_MSG_DATA
531 );
532
533 END Delete_WFAttrValue;
534
535 END JTF_BRMWFAttrValue_PVT;