DBA Data[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;