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