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