[Home] [Help]
PACKAGE BODY: APPS.CSD_FLOW_STATUSES_PVT
Source
1 PACKAGE BODY CSD_FLOW_STATUSES_PVT as
2 /* $Header: csdvflsb.pls 120.3 2005/07/29 16:37:00 vkjain noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_FLOW_STATUSES_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvflsb.pls';
6
7 /*--------------------------------------------------*/
8 /* procedure name: Create_Flow_Status */
9 /* description : procedure used to create */
10 /* Flow Status */
11 /* */
12 /*--------------------------------------------------*/
13 PROCEDURE Create_Flow_Status
14 (
15 p_api_version IN NUMBER,
16 p_commit IN VARCHAR2,
17 p_init_msg_list IN VARCHAR2,
18 p_validation_level IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_flow_status_rec IN FLOW_STATUS_REC_TYPE,
23 x_flow_status_id OUT NOCOPY NUMBER
24 ) IS
25
26 -- CONSTANTS --
27 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_FLOW_STATUSES_PVT.create_flow_status';
28 lc_api_name CONSTANT VARCHAR2(30) := 'Create_Flow_Status';
29 lc_api_version CONSTANT NUMBER := 1.0;
30
31 -- VARIABLES --
32 l_msg_count NUMBER;
33 l_msg_data VARCHAR2(100);
34 l_msg_index NUMBER;
35 l_dummy VARCHAR2(1) := null;
36 l_obj_ver_num NUMBER := 1;
37 l_rowid ROWID;
38
39 -- EXCEPTIONS --
40 UNIQUE_CONSTRAINT_VIOLATED Exception;
41
42 -- This will trap all exceptions that have
43 -- SQLCODE = -00001 and name it as 'UNIQUE_CONSTRAINT_VIOLATED'.
44 PRAGMA EXCEPTION_INIT( UNIQUE_CONSTRAINT_VIOLATED, -00001 );
45
46 BEGIN
47 -- Standard Start of API savepoint
48 SAVEPOINT Create_Flow_Status;
49
50 -- Standard call to check for call compatibility.
51 IF NOT FND_API.Compatible_API_Call (lc_api_version,
52 p_api_version,
53 lc_api_name ,
54 G_PKG_NAME )
55 THEN
56 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
57 END IF;
58
59 -- Initialize message list if p_init_msg_list is set to TRUE.
60 IF FND_API.to_Boolean( p_init_msg_list ) THEN
61 FND_MSG_PUB.initialize;
62 END IF;
63
64 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
65 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name || '.BEGIN',
66 'Entered Create_Flow_Status');
67 END IF;
68
69 -- Initialize API return status to success
70 x_return_status := FND_API.G_RET_STS_SUCCESS;
71
72 -- Api body starts
73
74 -- Check the required parameters
75 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
76 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
77 'Checking required parameters');
78 end if;
79
80 -- Check the required parameter
81 CSD_PROCESS_UTIL.Check_Reqd_Param
82 ( p_param_value => p_flow_status_rec.flow_status_code,
83 p_param_name => 'FLOW_STATUS_CODE',
84 p_api_name => lc_api_name);
85
86 -- Check the required parameter
87 CSD_PROCESS_UTIL.Check_Reqd_Param
88 ( p_param_value => p_flow_status_rec.status_code,
89 p_param_name => 'STATUS_CODE',
90 p_api_name => lc_api_name);
91
92 -- Insert row
93 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
94 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
95 'Calling Insert_Row table handler');
96 end if;
97
98 Begin
99
100 -- Insert the new diagnostic code
101 CSD_FLOW_STATUSES_PKG.Insert_Row
102 (
103 px_flow_status_id => x_flow_status_id,
104 p_object_version_number => l_obj_ver_num,
105 p_created_by => FND_GLOBAL.USER_ID,
106 p_creation_date => SYSDATE,
107 p_last_updated_by => FND_GLOBAL.USER_ID,
108 p_last_update_date => SYSDATE,
109 p_last_update_login => FND_GLOBAL.LOGIN_ID,
110 p_flow_status_code => p_flow_status_rec.flow_status_code,
111 p_status_code => p_flow_status_rec.status_code,
112 p_seeded_flag => p_flow_status_rec.seeded_flag,
113 p_external_display_status => p_flow_status_rec.external_display_status,
114 p_attribute_category => p_flow_status_rec.attribute_category,
115 p_attribute1 => p_flow_status_rec.attribute1,
116 p_attribute2 => p_flow_status_rec.attribute2,
117 p_attribute3 => p_flow_status_rec.attribute3,
118 p_attribute4 => p_flow_status_rec.attribute4,
119 p_attribute5 => p_flow_status_rec.attribute5,
120 p_attribute6 => p_flow_status_rec.attribute6,
121 p_attribute7 => p_flow_status_rec.attribute7,
122 p_attribute8 => p_flow_status_rec.attribute8,
123 p_attribute9 => p_flow_status_rec.attribute9,
124 p_attribute10 => p_flow_status_rec.attribute10,
125 p_attribute11 => p_flow_status_rec.attribute11,
126 p_attribute12 => p_flow_status_rec.attribute12,
127 p_attribute13 => p_flow_status_rec.attribute13,
128 p_attribute14 => p_flow_status_rec.attribute14,
129 p_attribute15 => p_flow_status_rec.attribute15
130 );
131 END;
132
133 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
134 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
135 'Returned from Insert_Row table handler');
136 end if;
137
138 -- Api body ends here
139
140 -- Standard check of p_commit.
141 IF FND_API.To_Boolean( p_commit ) THEN
142 COMMIT WORK;
143 END IF;
144
145 -- Standard call to get message count and IF count is get message info.
146 FND_MSG_PUB.Count_And_Get
147 (p_count => x_msg_count,
148 p_data => x_msg_data );
149
150 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
151 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name || '.END',
152 'Leaving Create_Flow_Status');
153 END IF;
154
155 EXCEPTION
156 WHEN UNIQUE_CONSTRAINT_VIOLATED THEN
157 ROLLBACK TO Create_Flow_Status;
158 x_return_status := FND_API.G_RET_STS_ERROR;
159
160 -- The definition already exists. No duplicates are allowed.
161 FND_MESSAGE.set_name('CSD', 'CSD_FLEX_DEFN_EXISTS');
162 FND_MSG_PUB.add;
163
164 FND_MSG_PUB.Count_And_Get
165 (p_count => x_msg_count,
166 p_data => x_msg_data );
167
168 -- save message in debug log
169 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
170 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
171 'EXC_ERROR['||x_msg_data||']');
172 END IF;
173
174 WHEN FND_API.G_EXC_ERROR THEN
175 ROLLBACK TO Create_Flow_Status;
176 x_return_status := FND_API.G_RET_STS_ERROR;
177
178 FND_MSG_PUB.Count_And_Get
179 (p_count => x_msg_count,
180 p_data => x_msg_data );
181
182 -- save message in debug log
183 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
184 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
185 'EXC_ERROR['||x_msg_data||']');
186 END IF;
187
188 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
189 ROLLBACK TO Create_Flow_Status;
190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
191
192 -- save message in fnd stack
193 IF FND_MSG_PUB.Check_Msg_Level
194 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
195 THEN
196 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
197 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name,
198 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
199 end if;
200 FND_MSG_PUB.Add_Exc_Msg
201 (G_PKG_NAME ,
202 lc_api_name );
203 END IF;
204
205 FND_MSG_PUB.Count_And_Get
206 ( p_count => x_msg_count,
207 p_data => x_msg_data );
208
209 -- save message in debug log
210 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
211 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
212 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
213 END IF;
214
215 WHEN OTHERS THEN
216 ROLLBACK TO Create_Flow_Status;
217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
218
219 -- save message in fnd stack
220 IF FND_MSG_PUB.Check_Msg_Level
221 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
222 THEN
223 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
224 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name,
225 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
226 end if;
227 FND_MSG_PUB.Add_Exc_Msg
228 (G_PKG_NAME ,
229 lc_api_name );
230 END IF;
231
232 FND_MSG_PUB.Count_And_Get
233 (p_count => x_msg_count,
234 p_data => x_msg_data );
235
236 -- save message in debug log
237 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
238 -- create a seeded message
239 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
240 'SQL Message['||sqlerrm||']' );
241 END IF;
242
243 END Create_Flow_Status;
244
245 /*--------------------------------------------------*/
246 /* procedure name: Update_Flow_Status */
247 /* description : procedure used to update */
248 /* Flow Status */
249 /* */
250 /*--------------------------------------------------*/
251 PROCEDURE Update_Flow_Status
252 (
253 p_api_version IN NUMBER,
254 p_commit IN VARCHAR2,
255 p_init_msg_list IN VARCHAR2,
256 p_validation_level IN NUMBER,
257 x_return_status OUT NOCOPY VARCHAR2,
258 x_msg_count OUT NOCOPY NUMBER,
259 x_msg_data OUT NOCOPY VARCHAR2,
260 p_flow_status_rec IN FLOW_STATUS_REC_TYPE,
261 x_obj_ver_number OUT NOCOPY NUMBER
262 ) IS
263
264 -- CONSTANTS --
265 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_FLOW_STATUSES_PVT.update_flow_status';
266 lc_api_name CONSTANT VARCHAR2(30) := 'Update_Flow_Status';
267 lc_api_version CONSTANT NUMBER := 1.0;
268
269 -- VARIABLES --
270 l_msg_count NUMBER;
271 l_msg_data VARCHAR2(100);
272 l_msg_index NUMBER;
273 l_dummy VARCHAR2(1) := null;
274 l_obj_ver_num NUMBER;
275 l_rowid ROWID;
276
277 -- EXCEPTIONS --
278 UNIQUE_CONSTRAINT_VIOLATED Exception;
279
280 -- This will trap all exceptions that have
281 -- SQLCODE = -00001 and name it as 'UNIQUE_CONSTRAINT_VIOLATED'.
282 PRAGMA EXCEPTION_INIT( UNIQUE_CONSTRAINT_VIOLATED, -00001 );
283
284 BEGIN
285 -- Standard Start of API savepoint
286 SAVEPOINT Update_Flow_Status;
287
288 -- Standard call to check for call compatibility.
289 IF NOT FND_API.Compatible_API_Call (lc_api_version,
290 p_api_version,
291 lc_api_name ,
292 G_PKG_NAME )
293 THEN
294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
295 END IF;
296
297 -- Initialize message list if p_init_msg_list is set to TRUE.
298 IF FND_API.to_Boolean( p_init_msg_list ) THEN
299 FND_MSG_PUB.initialize;
300 END IF;
301
302 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
303 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name || '.BEGIN',
304 'Entered Update_Flow_Status');
305 END IF;
306
307 -- Initialize API return status to success
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309
310 -- Api body starts
311
312 -- Check the required parameters
313 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
317
314 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
315 'Checking required parameters');
316 end if;
318 -- Check the required parameter
319 CSD_PROCESS_UTIL.Check_Reqd_Param
320 ( p_param_value => p_flow_status_rec.flow_status_id,
321 p_param_name => 'FLOW_STATUS_ID',
322 p_api_name => lc_api_name);
323
324 CSD_PROCESS_UTIL.Check_Reqd_Param
325 ( p_param_value => p_flow_status_rec.flow_status_code,
326 p_param_name => 'FLOW_STATUS_CODE',
327 p_api_name => lc_api_name);
328
329 -- Check the required parameter
330 CSD_PROCESS_UTIL.Check_Reqd_Param
331 ( p_param_value => p_flow_status_rec.status_code,
332 p_param_name => 'STATUS_CODE',
333 p_api_name => lc_api_name);
334
335
336 -- Update row
337 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
338 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
339 'Calling Update_Row table handler');
340 end if;
341
342 Begin
343
344 -- Update the diagnostic code
345 CSD_FLOW_STATUSES_PKG.Update_Row
346 (p_flow_status_id => p_flow_status_rec.flow_status_id,
347 p_object_version_number => p_flow_status_rec.object_version_number,
348 p_last_updated_by => FND_GLOBAL.USER_ID,
349 p_last_update_date => SYSDATE,
350 p_last_update_login => FND_GLOBAL.LOGIN_ID,
351 p_flow_status_code => p_flow_status_rec.flow_status_code,
352 p_status_code => p_flow_status_rec.status_code,
353 p_seeded_flag => p_flow_status_rec.seeded_flag,
354 p_external_display_status => p_flow_status_rec.external_display_status,
355 p_attribute_category => p_flow_status_rec.attribute_category,
356 p_attribute1 => p_flow_status_rec.attribute1,
357 p_attribute2 => p_flow_status_rec.attribute2,
358 p_attribute3 => p_flow_status_rec.attribute3,
359 p_attribute4 => p_flow_status_rec.attribute4,
360 p_attribute5 => p_flow_status_rec.attribute5,
361 p_attribute6 => p_flow_status_rec.attribute6,
362 p_attribute7 => p_flow_status_rec.attribute7,
363 p_attribute8 => p_flow_status_rec.attribute8,
364 p_attribute9 => p_flow_status_rec.attribute9,
365 p_attribute10 => p_flow_status_rec.attribute10,
366 p_attribute11 => p_flow_status_rec.attribute11,
367 p_attribute12 => p_flow_status_rec.attribute12,
368 p_attribute13 => p_flow_status_rec.attribute13,
369 p_attribute14 => p_flow_status_rec.attribute14,
370 p_attribute15 => p_flow_status_rec.attribute15
371 );
372
373 x_obj_ver_number := p_flow_status_rec.object_version_number + 1;
374
375 END;
376
377 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
378 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
379 'Returned from Update_Row table handler');
380 end if;
381
382 -- Api body ends here
383
384 -- Standard check of p_commit.
385 IF FND_API.To_Boolean( p_commit ) THEN
386 COMMIT WORK;
387 END IF;
388
389 -- Standard call to get message count and IF count is get message info.
390 FND_MSG_PUB.Count_And_Get
391 (p_count => x_msg_count,
392 p_data => x_msg_data );
393
394 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
395 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name || '.END',
396 'Leaving Update_Flow_Status');
397 END IF;
398
399 EXCEPTION
400 WHEN UNIQUE_CONSTRAINT_VIOLATED THEN
401 ROLLBACK TO Update_Flow_Status;
402 x_return_status := FND_API.G_RET_STS_ERROR;
403
404 -- The definition already exists. No duplicates are allowed.
405 FND_MESSAGE.set_name('CSD', 'CSD_FLEX_DEFN_EXISTS');
406 FND_MSG_PUB.add;
407
408 FND_MSG_PUB.Count_And_Get
409 (p_count => x_msg_count,
410 p_data => x_msg_data );
411
412 -- save message in debug log
413 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
414 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
415 'EXC_ERROR['||x_msg_data||']');
416 END IF;
417 WHEN FND_API.G_EXC_ERROR THEN
418 ROLLBACK TO Update_Flow_Status;
419 x_return_status := FND_API.G_RET_STS_ERROR;
420
421 FND_MSG_PUB.Count_And_Get
422 (p_count => x_msg_count,
423 p_data => x_msg_data );
424
425 -- save message in debug log
426 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
427 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
428 'EXC_ERROR['||x_msg_data||']');
429 END IF;
430
431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
432 ROLLBACK TO Update_Flow_Status;
433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
434
435 IF FND_MSG_PUB.Check_Msg_Level
436 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
440 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
437 THEN
438 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
439 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name,
441 end if;
442 FND_MSG_PUB.Add_Exc_Msg
443 (G_PKG_NAME ,
444 lc_api_name );
445 END IF;
446
447 FND_MSG_PUB.Count_And_Get
448 ( p_count => x_msg_count,
449 p_data => x_msg_data );
450
451 -- save message in debug log
452 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
453 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
454 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
455 END IF;
456
457 WHEN OTHERS THEN
458 ROLLBACK TO Update_Flow_Status;
459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
460
461 -- save message in fnd stack
465 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
462 IF FND_MSG_PUB.Check_Msg_Level
463 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
464 THEN
466 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name,
467 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
468 end if;
469 FND_MSG_PUB.Add_Exc_Msg
470 (G_PKG_NAME ,
471 lc_api_name );
472 END IF;
473
474 FND_MSG_PUB.Count_And_Get
475 (p_count => x_msg_count,
476 p_data => x_msg_data );
477
478 -- save message in debug log
479 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
480 -- create a seeded message
481 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
482 'SQL Message['||sqlerrm||']' );
483 END IF;
484
485 END Update_Flow_Status;
486
487 /*--------------------------------------------------*/
488 /* procedure name: Delete_Flow_Status */
489 /* description : procedure used to delete */
490 /* Flow Status */
491 /* */
492 /*--------------------------------------------------*/
493 PROCEDURE Delete_Flow_Status
494 (
495 p_api_version IN NUMBER,
496 p_commit IN VARCHAR2,
497 p_init_msg_list IN VARCHAR2,
498 p_validation_level IN NUMBER,
499 x_return_status OUT NOCOPY VARCHAR2,
500 x_msg_count OUT NOCOPY NUMBER,
501 x_msg_data OUT NOCOPY VARCHAR2,
502 p_flow_status_id IN NUMBER
503 ) IS
504
505 -- CONSTANTS --
506 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_FLOW_STATUSES_PVT.Delete_Flow_Status';
507 lc_api_name CONSTANT VARCHAR2(30) := 'Delete_Flow_Status';
508 lc_api_version CONSTANT NUMBER := 1.0;
509
510 -- VARIABLES --
511 l_msg_count NUMBER;
512 l_msg_data VARCHAR2(100);
513 l_msg_index NUMBER;
514
515 BEGIN
516 -- Standard Start of API savepoint
517 SAVEPOINT Delete_Flow_Status;
518
519 -- Standard call to check for call compatibility.
520 IF NOT FND_API.Compatible_API_Call (lc_api_version,
521 p_api_version,
522 lc_api_name ,
523 G_PKG_NAME )
524 THEN
525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
526 END IF;
527
528 -- Initialize message list if p_init_msg_list is set to TRUE.
529 IF FND_API.to_Boolean( p_init_msg_list ) THEN
530 FND_MSG_PUB.initialize;
531 END IF;
532
533 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
534 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name || '.BEGIN',
535 'Entered Delete_Flow_Status');
536 END IF;
537
538 -- Initialize API return status to success
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540
541 -- Api body starts
542
543 -- Check the required parameters
544 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
545 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
546 'Checking required parameter');
547 end if;
548
549 -- Check the required parameter
550 CSD_PROCESS_UTIL.Check_Reqd_Param
554
551 ( p_param_value => p_flow_status_id,
552 p_param_name => 'FLOW_STATUS_ID',
553 p_api_name => lc_api_name);
555 -- Delete row
556 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
557 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
558 'Calling Delete_Row table handler');
559 end if;
560
561 BEGIN
562
563 -- Delete the diagnostic code domain
564 CSD_FLOW_STATUSES_PKG.Delete_Row
565 ( p_flow_status_id => p_flow_status_id);
566
567 END;
568
569 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
570 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
571 'Returned from Delete_Row table handler');
572 end if;
573
574 -- Api body ends here
575
576 -- Standard check of p_commit.
577 IF FND_API.To_Boolean( p_commit ) THEN
578 COMMIT WORK;
579 END IF;
580
581 -- Standard call to get message count and IF count is get message info.
582 FND_MSG_PUB.Count_And_Get
583 (p_count => x_msg_count,
584 p_data => x_msg_data );
585
586 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
587 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name || '.END',
588 'Leaving Delete_Flow_Status');
589 END IF;
590
591 EXCEPTION
592
593 WHEN FND_API.G_EXC_ERROR THEN
594 ROLLBACK TO Delete_Flow_Status;
595
596 x_return_status := FND_API.G_RET_STS_ERROR;
597
598 FND_MSG_PUB.Count_And_Get
599 (p_count => x_msg_count,
600 p_data => x_msg_data );
601
602 -- save message in debug log
603 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
604 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
605 'EXC_ERROR['||x_msg_data||']');
606 END IF;
607
608 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
609 ROLLBACK TO Delete_Flow_Status;
610
611 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
612
613 IF FND_MSG_PUB.Check_Msg_Level
614 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
615 THEN
616 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
617 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name,
618 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
619 end if;
620 FND_MSG_PUB.Add_Exc_Msg
621 (G_PKG_NAME ,
622 lc_api_name );
623 END IF;
624
625 FND_MSG_PUB.Count_And_Get
626 ( p_count => x_msg_count,
627 p_data => x_msg_data );
628
629 -- save message in debug log
633 END IF;
630 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
631 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
632 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
634
635 WHEN OTHERS THEN
636 ROLLBACK TO Delete_Flow_Status;
637
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639
640 -- save message in fnd stack
641 IF FND_MSG_PUB.Check_Msg_Level
642 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
643 THEN
644 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
645 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name,
646 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
647 end if;
648 FND_MSG_PUB.Add_Exc_Msg
649 (G_PKG_NAME ,
650 lc_api_name );
651 END IF;
652
653 FND_MSG_PUB.Count_And_Get
654 (p_count => x_msg_count,
655 p_data => x_msg_data );
656
657 -- save message in debug log
658 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
659 -- create a seeded message
660 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
661 'SQL Message['||sqlerrm||']' );
662 END IF;
663
664 END Delete_Flow_Status;
665
666 /*--------------------------------------------------*/
667 /* procedure name: Lock_Flow_Status */
668 /* description : procedure used to lock */
669 /* Flow Status */
670 /* */
671 /*--------------------------------------------------*/
672 PROCEDURE Lock_Flow_Status
673 (
674 p_api_version IN NUMBER,
675 p_commit IN VARCHAR2,
676 p_init_msg_list IN VARCHAR2,
677 p_validation_level IN NUMBER,
678 x_return_status OUT NOCOPY VARCHAR2,
679 x_msg_count OUT NOCOPY NUMBER,
680 x_msg_data OUT NOCOPY VARCHAR2,
681 p_flow_status_rec IN FLOW_STATUS_REC_TYPE
682 ) IS
683
684 -- CONSTANTS --
685 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_FLOW_STATUSES_PVT.lock_flow_status';
686 lc_api_name CONSTANT VARCHAR2(30) := 'Lock_Flow_Status';
687 lc_api_version CONSTANT NUMBER := 1.0;
688
689 -- VARIABLES --
690 l_msg_count NUMBER;
691 l_msg_data VARCHAR2(100);
692 l_msg_index NUMBER;
693 l_rowid ROWID;
694
695 BEGIN
696 -- Standard Start of API savepoint
697 SAVEPOINT Lock_Flow_Status;
698
699 -- Standard call to check for call compatibility.
700 IF NOT FND_API.Compatible_API_Call (lc_api_version,
701 p_api_version,
702 lc_api_name ,
703 G_PKG_NAME )
704 THEN
705 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
706 END IF;
707
708 -- Initialize message list if p_init_msg_list is set to TRUE.
709 IF FND_API.to_Boolean( p_init_msg_list ) THEN
710 FND_MSG_PUB.initialize;
711 END IF;
712
713 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
714 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name || '.BEGIN',
715 'Entered Lock_Flow_Status');
716 END IF;
717
718 -- Initialize API return status to success
719 x_return_status := FND_API.G_RET_STS_SUCCESS;
720
721 -- Api body starts
722
723 -- Check the required parameters
724 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
725 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
726 'Checking required parameters');
727 end if;
728
729 -- Check the required parameter
730 CSD_PROCESS_UTIL.Check_Reqd_Param
731 ( p_param_value => p_flow_status_rec.flow_status_id,
732 p_param_name => 'FLOW_STATUS_ID',
733 p_api_name => lc_api_name);
734
735 -- Check the required parameter
736 CSD_PROCESS_UTIL.Check_Reqd_Param
737 ( p_param_value => p_flow_status_rec.object_version_number,
738 p_param_name => 'OBJECT_VERSION_NUMBER',
739 p_api_name => lc_api_name);
740
741 -- Lock row
742 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
743 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
744 'Calling Lock_Row table handler');
745 end if;
746
747 Begin
748
749 -- Lock the diagnostic code
750 CSD_FLOW_STATUSES_PKG.Lock_Row
751 (
752 p_flow_status_id => p_flow_status_rec.flow_status_id,
753 p_object_version_number => p_flow_status_rec.object_version_number
754 );
755
756 END;
757
758 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
759 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
760 'Returned from Lock_Row table handler');
761 end if;
762
763 -- Api body ends here
764
765 -- Standard check of p_commit.
766 IF FND_API.To_Boolean( p_commit ) THEN
767 COMMIT WORK;
771 FND_MSG_PUB.Count_And_Get
768 END IF;
769
770 -- Standard call to get message count and IF count is get message info.
772 (p_count => x_msg_count,
773 p_data => x_msg_data );
774
775 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
776 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name || '.END',
777 'Leaving Lock_Flow_Status');
778 END IF;
779
780 EXCEPTION
781
782 WHEN FND_API.G_EXC_ERROR THEN
783 ROLLBACK TO Lock_Flow_Status;
784 x_return_status := FND_API.G_RET_STS_ERROR;
785
786 FND_MSG_PUB.Count_And_Get
787 (p_count => x_msg_count,
788 p_data => x_msg_data );
789
790 -- save message in debug log
791 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
792 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
793 'EXC_ERROR['||x_msg_data||']');
794 END IF;
795
796 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
797 ROLLBACK TO Lock_Flow_Status;
798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
799
800 IF FND_MSG_PUB.Check_Msg_Level
801 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
802 THEN
803 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
804 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name,
805 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
806 end if;
807 FND_MSG_PUB.Add_Exc_Msg
808 (G_PKG_NAME ,
809 lc_api_name );
810 END IF;
811
812 FND_MSG_PUB.Count_And_Get
813 ( p_count => x_msg_count,
814 p_data => x_msg_data );
815
816 -- save message in debug log
817 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
818 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
819 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
820 END IF;
821
822 WHEN OTHERS THEN
823 ROLLBACK TO Lock_Flow_Status;
824 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
825
826 -- save message in fnd stack
827 IF FND_MSG_PUB.Check_Msg_Level
828 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
829 THEN
830 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
831 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name,
832 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
833 end if;
834 FND_MSG_PUB.Add_Exc_Msg
835 (G_PKG_NAME ,
836 lc_api_name );
837 END IF;
838
839 FND_MSG_PUB.Count_And_Get
840 (p_count => x_msg_count,
841 p_data => x_msg_data );
842
843 -- save message in debug log
844 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
845 -- create a seeded message
846 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
847 'SQL Message['||sqlerrm||']' );
848 END IF;
849
850 END Lock_Flow_Status;
851
852 End CSD_FLOW_STATUSES_PVT;