[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_MR_PUB
Source
1 PACKAGE BODY AHL_FMP_MR_PUB AS
2 /* $Header: AHLPMRHB.pls 120.3.12020000.2 2012/12/07 00:42:57 sareepar ship $ */
3
4 PROCEDURE Create_Mr
5 (
6 -- default IN params
7 p_api_version IN NUMBER,
8 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
9 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
10 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
11 p_default IN VARCHAR2 :=FND_API.G_FALSE,
12 p_module_type IN VARCHAR2 :=NULL,
13 -- default OUT params
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2,
17 -- functionality specific params
18 p_x_mr_header_rec IN OUT NOCOPY AHL_FMP_MR_HEADER_PVT.mr_header_rec,
19 p_x_mr_doc_tbl IN OUT NOCOPY AHL_FMP_MR_DOC_ASSO_PVT.doc_association_tbl,
20 p_x_mr_route_tbl IN OUT NOCOPY AHL_FMP_MR_ROUTE_PVT.mr_route_tbl,
21 p_x_mr_visit_type_tbl IN OUT NOCOPY AHL_FMP_MR_VISIT_TYPES_PVT.mr_visit_type_tbl_type,
22 p_x_effectivity_tbl IN OUT NOCOPY AHL_FMP_MR_EFFECTIVITY_PVT.effectivity_tbl_type,
23 p_x_mr_relation_tbl IN OUT NOCOPY AHL_FMP_MR_RELATION_PVT.mr_relation_tbl,
24 -- pdoki added for SBE Project
25 p_x_accomtriggers_tbl IN OUT NOCOPY AHL_FMP_MR_RELATION_PVT.mr_relation_tbl
26 )
27 IS
28 -- 1. Declare local variables
29 l_api_name CONSTANT VARCHAR2(30) := 'Create_Mr';
30 l_api_version CONSTANT NUMBER := 2.0;
31 l_return_status VARCHAR2(1);
32 l_msg_count NUMBER;
33 l_msg_data VARCHAR2(2000);
34 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
35
36 BEGIN
37 -- Standard start of API savepoint
38 SAVEPOINT Create_Mr_SP;
39
40 -- Standard call to check for call compatibility
41 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
42 THEN
43 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
44 END IF;
45
46 -- Initialize message, depending on p_init_msg_list flag
47 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
48 FND_MSG_PUB.Initialize;
49 END IF;
50
51 -- Initialize API return status to success
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53
54 -- Log API entry point
55 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
56 THEN
57 fnd_log.string
58 (
59 fnd_log.level_procedure,
60 L_DEBUG_MODULE||'.begin',
61 'At the start of PLSQL procedure'
62 );
63 END IF;
64
65 -- API body starts here
66 IF p_x_mr_header_rec.DML_OPERATION = 'C'
67 THEN
68 AHL_FMP_MR_HEADER_PVT.CREATE_MR_HEADER
69 (
70 p_api_version => 1.0,
71 p_init_msg_list => FND_API.G_FALSE,
72 p_commit => FND_API.G_FALSE,
73 p_validation_level => p_validation_level,
74 p_default => p_default,
75 p_module_type => p_module_type,
76 x_return_status => x_return_status,
77 x_msg_count => x_msg_count,
78 x_msg_data => x_msg_data,
79 p_x_mr_header_rec => p_x_mr_header_rec
80 );
81
82 x_msg_count := FND_MSG_PUB.count_msg;
83 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
84 THEN
85 RAISE FND_API.G_EXC_ERROR;
86 END IF;
87
88 IF (p_x_mr_header_rec.mr_header_id IS NULL)
89 THEN
90 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
91 THEN
92 fnd_log.string
93 (
94 fnd_log.level_error,
95 L_DEBUG_MODULE,
96 'After AHL_FMP_MR_HEADER_PVT.CREATE_MR_HEADER, mr_header_id is NULL'
97 );
98 END IF;
99
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END IF;
102
103 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
104 THEN
105 fnd_log.string
106 (
107 fnd_log.level_statement,
108 L_DEBUG_MODULE,
109 'AHL_FMP_MR_HEADER_PVT.CREATE_MR_HEADER successful with p_x_mr_header_rec.mr_header_id = '||TO_CHAR(p_x_mr_header_rec.mr_header_id)
110 );
111 END IF;
112 ElSE
113 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_DML_FLAG');
114 FND_MSG_PUB.ADD;
115 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
116 THEN
117 fnd_log.string
118 (
119 fnd_log.level_error,
120 L_DEBUG_MODULE,
121 'Invalid DML Operation specified'
122 );
123 END IF;
124
125 RAISE FND_API.G_EXC_ERROR;
126 END IF;
127
128 IF p_x_mr_doc_tbl.COUNT > 0
129 THEN
130 FOR i IN p_x_mr_doc_tbl.FIRST..p_x_mr_doc_tbl.LAST
131 LOOP
132 p_x_mr_doc_tbl(i).OBJECT_TYPE_CODE := 'MR';
133 p_x_mr_doc_tbl(i).MR_HEADER_ID := p_x_mr_header_rec.mr_header_id;
134 p_x_mr_doc_tbl(i).MR_TITLE := p_x_mr_header_rec.title;
135 p_x_mr_doc_tbl(i).MR_VERSION_NUMBER := p_x_mr_header_rec.version_number;
136 p_x_mr_doc_tbl(i).DML_OPERATION := 'C';
137 END LOOP;
138
139 AHL_FMP_MR_DOC_ASSO_PVT.Process_Doc_Association
140 (
141 p_api_version => 1.0,
142 p_init_msg_list => FND_API.G_FALSE,
143 p_commit => FND_API.G_FALSE,
144 p_validation_level => p_validation_level,
145 p_default => p_default,
146 p_module_type => p_module_type,
147 x_return_status => x_return_status,
148 x_msg_count => x_msg_count,
149 x_msg_data => x_msg_data,
150 p_x_association_tbl => p_x_mr_doc_tbl
151 );
152
153 x_msg_count := FND_MSG_PUB.count_msg;
154 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
155 THEN
156 RAISE FND_API.G_EXC_ERROR;
157 END IF;
158
159 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
160 THEN
161 fnd_log.string
162 (
163 fnd_log.level_statement,
164 L_DEBUG_MODULE,
165 'AHL_FMP_MR_DOC_ASSO_PVT.Process_Doc_Association is successful'
166 );
167 END IF;
168 END IF;
169
170 IF p_x_mr_route_tbl.COUNT > 0
171 THEN
172 FOR i IN p_x_mr_route_tbl.FIRST..p_x_mr_route_tbl.LAST
173 LOOP
174 p_x_mr_route_tbl(i).MR_HEADER_ID := p_x_mr_header_rec.mr_header_id;
175 p_x_mr_route_tbl(i).MR_TITLE := p_x_mr_header_rec.title;
176 p_x_mr_route_tbl(i).MR_VERSION_NUMBER := p_x_mr_header_rec.version_number;
177 p_x_mr_route_tbl(i).DML_OPERATION := 'C';
178 END LOOP;
179
180 AHL_FMP_MR_ROUTE_PVT.PROCESS_MR_ROUTE
181 (
182 p_api_version => 1.0,
183 p_init_msg_list => FND_API.G_FALSE,
184 p_commit => FND_API.G_FALSE,
185 p_validation_level => p_validation_level,
186 p_default => p_default,
187 p_module_type => p_module_type,
188 x_return_status => x_return_status,
189 x_msg_count => x_msg_count,
190 x_msg_data => x_msg_data,
191 p_x_mr_route_tbl => p_x_mr_route_tbl
192 );
193
194 x_msg_count := FND_MSG_PUB.count_msg;
195 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
196 THEN
197 RAISE FND_API.G_EXC_ERROR;
198 END IF;
199
200 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
201 THEN
202 fnd_log.string
203 (
204 fnd_log.level_statement,
205 L_DEBUG_MODULE,
206 'AHL_FMP_MR_ROUTE_PVT.PROCESS_MR_ROUTE is successful'
207 );
208 END IF;
209 END IF;
210
211 IF p_x_mr_visit_type_tbl.COUNT > 0
212 THEN
213 FOR i IN p_x_mr_visit_type_tbl.FIRST..p_x_mr_visit_type_tbl.LAST
214 LOOP
215 p_x_mr_visit_type_tbl(i).MR_HEADER_ID := p_x_mr_header_rec.mr_header_id;
216 p_x_mr_visit_type_tbl(i).DML_OPERATION := 'C';
217 END LOOP;
218
219 AHL_FMP_MR_VISIT_TYPES_PVT.PROCESS_MR_VISIT_TYPES
220 (
221 p_api_version => 1.0,
222 p_init_msg_list => FND_API.G_FALSE,
223 p_commit => FND_API.G_FALSE,
224 p_validation_level => p_validation_level,
225 p_default => p_default,
226 p_module_type => p_module_type,
227 x_return_status => x_return_status,
228 x_msg_count => x_msg_count,
229 x_msg_data => x_msg_data,
230 p_x_mr_visit_type_tbl => p_x_mr_visit_type_tbl
231 );
232
233 x_msg_count := FND_MSG_PUB.count_msg;
234 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
235 THEN
236 RAISE FND_API.G_EXC_ERROR;
237 END IF;
238
239 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
240 THEN
241 fnd_log.string
242 (
243 fnd_log.level_statement,
244 L_DEBUG_MODULE,
245 'AHL_FMP_MR_VISIT_TYPES_PVT.PROCESS_MR_VISIT_TYPES is successful'
246 );
247 END IF;
248 END IF;
249
250 IF p_x_effectivity_tbl.COUNT > 0
251 THEN
252 FOR i IN p_x_effectivity_tbl.FIRST..p_x_effectivity_tbl.LAST
253 LOOP
254 p_x_effectivity_tbl(i).DML_OPERATION := 'C';
255 END LOOP;
256
257 AHL_FMP_MR_EFFECTIVITY_PVT.process_effectivity
258 (
259 p_api_version => 1.0,
260 p_init_msg_list => FND_API.G_FALSE,
261 p_commit => FND_API.G_FALSE,
262 p_validation_level => p_validation_level,
263 p_default => p_default,
264 p_module_type => p_module_type,
265 x_return_status => x_return_status,
266 x_msg_count => x_msg_count,
267 x_msg_data => x_msg_data,
268 p_x_effectivity_tbl => p_x_effectivity_tbl,
269 p_mr_header_id => p_x_mr_header_rec.mr_header_id,
270 p_super_user => p_x_mr_header_rec.superuser_role
271 );
272
273 x_msg_count := FND_MSG_PUB.count_msg;
274 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
275 THEN
276 RAISE FND_API.G_EXC_ERROR;
277 END IF;
278
279 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
280 THEN
281 fnd_log.string
282 (
283 fnd_log.level_statement,
284 L_DEBUG_MODULE,
285 'AHL_FMP_MR_EFFECTIVITY_PVT.process_effectivity is successful'
286 );
287 END IF;
288 END IF;
289
290 IF p_x_mr_relation_tbl.COUNT > 0
291 THEN
292 FOR i IN p_x_mr_relation_tbl.FIRST..p_x_mr_relation_tbl.LAST
293 LOOP
294 p_x_mr_relation_tbl(i).MR_HEADER_ID := p_x_mr_header_rec.mr_header_id;
295 p_x_mr_relation_tbl(i).MR_TITLE := p_x_mr_header_rec.title;
296 p_x_mr_relation_tbl(i).MR_VERSION_NUMBER := p_x_mr_header_rec.version_number;
297 p_x_mr_relation_tbl(i).DML_OPERATION := 'C';
298 END LOOP;
299
300 AHL_FMP_MR_RELATION_PVT.PROCESS_MR_RELATION
301 (
302 p_api_version => 1.0,
303 p_init_msg_list => FND_API.G_FALSE,
304 p_commit => FND_API.G_FALSE,
305 p_validation_level => p_validation_level,
306 p_default => p_default,
307 p_module_type => p_module_type,
308 x_return_status => x_return_status,
309 x_msg_count => x_msg_count,
310 x_msg_data => x_msg_data,
311 p_x_mr_relation_tbl => p_x_mr_relation_tbl
312 );
313
314 x_msg_count := FND_MSG_PUB.count_msg;
315 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
316 THEN
317 RAISE FND_API.G_EXC_ERROR;
318 END IF;
319
320 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
321 THEN
322 fnd_log.string
323 (
324 fnd_log.level_statement,
325 L_DEBUG_MODULE,
326 'AHL_FMP_MR_RELATION_PVT.PROCESS_MR_RELATION is successful'
327 );
328 END IF;
329 END IF;
330
331 -- pdoki added for SBE Project, Start.
332 IF p_x_accomtriggers_tbl.COUNT > 0
333 THEN
334 FOR i IN p_x_accomtriggers_tbl.FIRST..p_x_accomtriggers_tbl.LAST
335 LOOP
336 p_x_accomtriggers_tbl(i).MR_HEADER_ID := p_x_mr_header_rec.mr_header_id;
337 p_x_accomtriggers_tbl(i).MR_TITLE := p_x_mr_header_rec.title;
338 p_x_accomtriggers_tbl(i).MR_VERSION_NUMBER := p_x_mr_header_rec.version_number;
339 p_x_accomtriggers_tbl(i).DML_OPERATION := 'C';
340 END LOOP;
341
342 AHL_FMP_MR_RELATION_PVT.PROCESS_MR_RELATION
343 (
344 p_api_version => 1.0,
345 p_init_msg_list => FND_API.G_FALSE,
346 p_commit => FND_API.G_FALSE,
347 p_validation_level => p_validation_level,
348 p_default => p_default,
349 p_module_type => p_module_type,
350 x_return_status => x_return_status,
351 x_msg_count => x_msg_count,
352 x_msg_data => x_msg_data,
353 p_x_mr_relation_tbl => p_x_accomtriggers_tbl
354 );
355
356 x_msg_count := FND_MSG_PUB.count_msg;
357 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
358 THEN
359 RAISE FND_API.G_EXC_ERROR;
360 END IF;
361
362 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
363 THEN
364 fnd_log.string
365 (
366 fnd_log.level_statement,
367 L_DEBUG_MODULE,
368 'AHL_FMP_MR_RELATION_PVT.PROCESS_MR_RELATION is successful'
369 );
370 END IF;
371 END IF;
372 -- pdoki added for SBE Project, End.
373
374 -- API body ends here
375
376 -- Log API exit point
377 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
378 THEN
379 fnd_log.string
380 (
381 fnd_log.level_procedure,
382 L_DEBUG_MODULE||'.end',
383 'At the end of PLSQL procedure'
384 );
385 END IF;
386
387 -- Check Error Message stack.
388 x_msg_count := FND_MSG_PUB.count_msg;
389 IF x_msg_count > 0
390 THEN
391 RAISE FND_API.G_EXC_ERROR;
392 END IF;
393
394 -- Commit, depending on p_commit flag
395 IF FND_API.TO_BOOLEAN(p_commit) THEN
396 COMMIT WORK;
397 END IF;
398
399 -- Standard call to get message count and if count is 1, get message info
400 FND_MSG_PUB.count_and_get
401 (
402 p_count => x_msg_count,
403 p_data => x_msg_data,
404 p_encoded => FND_API.G_FALSE
405 );
406
407 EXCEPTION
408 WHEN FND_API.G_EXC_ERROR THEN
409 x_return_status := FND_API.G_RET_STS_ERROR;
410 Rollback to Create_Mr_SP;
411 FND_MSG_PUB.count_and_get
412 (
413 p_count => x_msg_count,
414 p_data => x_msg_data,
415 p_encoded => FND_API.G_FALSE
416 );
417
418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
420 Rollback to Create_Mr_SP;
421 FND_MSG_PUB.count_and_get
422 (
423 p_count => x_msg_count,
424 p_data => x_msg_data,
425 p_encoded => FND_API.G_FALSE
426 );
427
428 WHEN OTHERS THEN
429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
430 Rollback to Create_Mr_SP;
431 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
432 THEN
433 FND_MSG_PUB.add_exc_msg
434 (
435 p_pkg_name => G_PKG_NAME,
436 p_procedure_name => 'Create_Mr',
437 p_error_text => SUBSTR(SQLERRM,1,240)
438 );
439 END IF;
440 FND_MSG_PUB.count_and_get
441 (
442 p_count => x_msg_count,
443 p_data => x_msg_data,
444 p_encoded => FND_API.G_FALSE
445 );
446
447 END Create_Mr;
448
449 -- Overloaded procedure retained for backaward compatibility
450 -- This procedure will call the above procedure with api_version = 2.
451 PROCEDURE Create_Mr
452 (
453 -- default IN params
454 p_api_version IN NUMBER,
455 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
456 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
457 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
458 p_default IN VARCHAR2 :=FND_API.G_FALSE,
459 p_module_type IN VARCHAR2 :=NULL,
460 -- default OUT params
461 x_return_status OUT NOCOPY VARCHAR2,
462 x_msg_count OUT NOCOPY NUMBER,
463 x_msg_data OUT NOCOPY VARCHAR2,
464 -- functionality specific params
465 p_x_mr_header_rec IN OUT NOCOPY AHL_FMP_MR_HEADER_PVT.mr_header_rec,
466 p_x_mr_doc_tbl IN OUT NOCOPY AHL_FMP_MR_DOC_ASSO_PVT.doc_association_tbl,
467 p_x_mr_route_tbl IN OUT NOCOPY AHL_FMP_MR_ROUTE_PVT.mr_route_tbl,
468 p_x_mr_visit_type_tbl IN OUT NOCOPY AHL_FMP_MR_VISIT_TYPES_PVT.mr_visit_type_tbl_type,
469 p_x_effectivity_tbl IN OUT NOCOPY AHL_FMP_MR_EFFECTIVITY_PVT.effectivity_tbl_type,
470 p_x_mr_relation_tbl IN OUT NOCOPY AHL_FMP_MR_RELATION_PVT.mr_relation_tbl
471 )
472 IS
473 -- 1. Declare local variables
474 l_api_name CONSTANT VARCHAR2(30) := 'Create_Mr';
475 l_api_version CONSTANT NUMBER := 1.0;
476
477 l_x_accomtriggers_tbl AHL_FMP_MR_RELATION_PVT.mr_relation_tbl;
478
479 BEGIN
480
481 -- Initialize API return status to success
482 x_return_status := FND_API.G_RET_STS_SUCCESS;
483
484 -- Standard call to check for call compatibility
485 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
486 THEN
487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
488 END IF;
489
490 AHL_FMP_MR_PUB.Create_Mr
491 (
492 -- standard IN params
493 p_api_version => 2.0,
494 p_init_msg_list => p_init_msg_list,
495 p_commit => p_commit,
496 p_validation_level => p_validation_level,
497 p_default => p_default,
498 p_module_type => p_module_type,
499 -- standard OUT params
500 x_return_status => x_return_status,
501 x_msg_count => x_msg_count,
502 x_msg_data => x_msg_data,
503 -- procedure params
504 p_x_mr_header_rec => p_x_mr_header_rec,
505 p_x_mr_doc_tbl => p_x_mr_doc_tbl,
506 p_x_mr_route_tbl => p_x_mr_route_tbl,
507 p_x_mr_visit_type_tbl => p_x_mr_visit_type_tbl,
508 p_x_effectivity_tbl => p_x_effectivity_tbl,
509 p_x_mr_relation_tbl => p_x_mr_relation_tbl,
510 p_x_accomtriggers_tbl => l_x_accomtriggers_tbl);
511
512
513
514 EXCEPTION
515 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
517 FND_MSG_PUB.count_and_get
518 (
519 p_count => x_msg_count,
520 p_data => x_msg_data,
521 p_encoded => FND_API.G_FALSE
522 );
523
524 WHEN OTHERS THEN
525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
526 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
527 THEN
528 FND_MSG_PUB.add_exc_msg
529 (
530 p_pkg_name => G_PKG_NAME,
531 p_procedure_name => 'Create_Mr',
532 p_error_text => SUBSTR(SQLERRM,1,240)
533 );
534 END IF;
535 FND_MSG_PUB.count_and_get
536 (
537 p_count => x_msg_count,
538 p_data => x_msg_data,
539 p_encoded => FND_API.G_FALSE
540 );
541
542 END Create_Mr;
543
544 PROCEDURE Modify_Mr
545 (
546 -- default IN params
547 p_api_version IN NUMBER,
548 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
549 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
550 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
551 p_default IN VARCHAR2 :=FND_API.G_FALSE,
552 p_module_type IN VARCHAR2 :=NULL,
553 -- default OUT params
554 x_return_status OUT NOCOPY VARCHAR2,
555 x_msg_count OUT NOCOPY NUMBER,
556 x_msg_data OUT NOCOPY VARCHAR2,
557 -- functionality specific params
558 p_mr_header_rec IN AHL_FMP_MR_HEADER_PVT.mr_header_rec,
559 p_x_mr_doc_tbl IN OUT NOCOPY AHL_FMP_MR_DOC_ASSO_PVT.doc_association_tbl,
560 p_x_mr_route_tbl IN OUT NOCOPY AHL_FMP_MR_ROUTE_PVT.mr_route_tbl,
561 p_x_mr_visit_type_tbl IN OUT NOCOPY AHL_FMP_MR_VISIT_TYPES_PVT.mr_visit_type_tbl_type,
562 p_x_effectivity_tbl IN OUT NOCOPY AHL_FMP_MR_EFFECTIVITY_PVT.effectivity_tbl_type,
563 p_x_mr_relation_tbl IN OUT NOCOPY AHL_FMP_MR_RELATION_PVT.mr_relation_tbl,
564 -- pdoki added for SBE Project
565 p_x_accomtriggers_tbl IN OUT NOCOPY AHL_FMP_MR_RELATION_PVT.mr_relation_tbl
566 )
567 IS
568 -- 1. Declare local variables
569 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Mr';
570 l_api_version CONSTANT NUMBER := 2.0;
571 l_return_status VARCHAR2(1);
572 l_msg_count NUMBER;
573 l_msg_data VARCHAR2(2000);
574 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
575
576 l_mr_header_rec AHL_FMP_MR_HEADER_PVT.mr_header_rec := p_mr_header_rec;
577
578 BEGIN
579 -- Standard start of API savepoint
580 SAVEPOINT Modify_Mr_SP;
581
582 -- Standard call to check for call compatibility
583 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
584 THEN
585 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
586 END IF;
587
588 -- Initialize message, depending on p_init_msg_list flag
589 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
590 FND_MSG_PUB.Initialize;
591 END IF;
592
593 -- Initialize API return status to success
594 x_return_status := FND_API.G_RET_STS_SUCCESS;
595
596 -- Log API entry point
597 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
598 THEN
599 fnd_log.string
600 (
601 fnd_log.level_procedure,
602 L_DEBUG_MODULE||'.begin',
603 'At the start of PLSQL procedure'
604 );
605 END IF;
606
607 -- API body starts here
608 IF (l_mr_header_rec.mr_header_id IS NULL)
609 THEN
610 AHL_FMP_COMMON_PVT.mr_title_version_to_id
611 (
612 p_mr_title => l_mr_header_rec.title,
613 p_mr_version_number => l_mr_header_rec.version_number,
614 x_mr_header_id => l_mr_header_rec.mr_header_id,
615 x_return_status => x_return_status
616 );
617
618 x_msg_count := FND_MSG_PUB.count_msg;
619 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
620 THEN
621 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
622 THEN
623 fnd_log.string
624 (
625 fnd_log.level_error,
626 L_DEBUG_MODULE||'.end',
627 'AHL_FMP_COMMON_PVT.mr_title_version_to_id returned error'
628 );
629 END IF;
630 RAISE FND_API.G_EXC_ERROR;
631 END IF;
632 END IF;
633
634 IF l_mr_header_rec.DML_OPERATION = 'U'
635 THEN
636 AHL_FMP_MR_HEADER_PVT.UPDATE_MR_HEADER
637 (
638 p_api_version => 1.0,
639 p_init_msg_list => FND_API.G_FALSE,
640 p_commit => FND_API.G_FALSE,
641 p_validation_level => p_validation_level,
642 p_default => p_default,
643 p_module_type => p_module_type,
644 x_return_status => x_return_status,
645 x_msg_count => x_msg_count,
646 x_msg_data => x_msg_data,
647 p_x_mr_header_rec => l_mr_header_rec
648 );
649
650 x_msg_count := FND_MSG_PUB.count_msg;
651 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
652 THEN
653 RAISE FND_API.G_EXC_ERROR;
654 END IF;
655
656 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
657 THEN
658 fnd_log.string
659 (
660 fnd_log.level_statement,
661 L_DEBUG_MODULE,
662 'AHL_FMP_MR_HEADER_PVT.UPDATE_MR_HEADER successful'
663 );
664 END IF;
665 ElSIF (l_mr_header_rec.DML_OPERATION IS NOT NULL)
666 THEN
667 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_DML_FLAG');
668 FND_MSG_PUB.ADD;
669 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
670 THEN
671 fnd_log.string
672 (
673 fnd_log.level_error,
674 L_DEBUG_MODULE,
675 'Invalid DML Operation specified'
676 );
677 END IF;
678
679 RAISE FND_API.G_EXC_ERROR;
680 END IF;
681
682 IF p_x_mr_doc_tbl.COUNT > 0
683 THEN
684 FOR i IN p_x_mr_doc_tbl.FIRST..p_x_mr_doc_tbl.LAST
685 LOOP
686 p_x_mr_doc_tbl(i).OBJECT_TYPE_CODE := 'MR';
687 p_x_mr_doc_tbl(i).MR_HEADER_ID := l_mr_header_rec.mr_header_id;
688 p_x_mr_doc_tbl(i).MR_TITLE := l_mr_header_rec.title;
689 p_x_mr_doc_tbl(i).MR_VERSION_NUMBER := l_mr_header_rec.version_number;
690 END LOOP;
691
692 AHL_FMP_MR_DOC_ASSO_PVT.Process_Doc_Association
693 (
694 p_api_version => 1.0,
695 p_init_msg_list => FND_API.G_FALSE,
696 p_commit => FND_API.G_FALSE,
697 p_validation_level => p_validation_level,
698 p_default => p_default,
699 p_module_type => p_module_type,
700 x_return_status => x_return_status,
701 x_msg_count => x_msg_count,
702 x_msg_data => x_msg_data,
703 p_x_association_tbl => p_x_mr_doc_tbl
704 );
705
706 x_msg_count := FND_MSG_PUB.count_msg;
707 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
708 THEN
709 RAISE FND_API.G_EXC_ERROR;
710 END IF;
711
712 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
713 THEN
714 fnd_log.string
715 (
716 fnd_log.level_statement,
717 L_DEBUG_MODULE,
718 'AHL_FMP_MR_DOC_ASSO_PVT.Process_Doc_Association is successful'
719 );
720 END IF;
721 END IF;
722
723 IF p_x_mr_route_tbl.COUNT > 0
724 THEN
725 FOR i IN p_x_mr_route_tbl.FIRST..p_x_mr_route_tbl.LAST
726 LOOP
727 p_x_mr_route_tbl(i).MR_HEADER_ID := l_mr_header_rec.mr_header_id;
728 p_x_mr_route_tbl(i).MR_TITLE := l_mr_header_rec.title;
729 p_x_mr_route_tbl(i).MR_VERSION_NUMBER := l_mr_header_rec.version_number;
730 END LOOP;
731
732 AHL_FMP_MR_ROUTE_PVT.PROCESS_MR_ROUTE
733 (
734 p_api_version => 1.0,
735 p_init_msg_list => FND_API.G_FALSE,
736 p_commit => FND_API.G_FALSE,
737 p_validation_level => p_validation_level,
738 p_default => p_default,
739 p_module_type => p_module_type,
740 x_return_status => x_return_status,
741 x_msg_count => x_msg_count,
742 x_msg_data => x_msg_data,
743 p_x_mr_route_tbl => p_x_mr_route_tbl
744 );
745
746 x_msg_count := FND_MSG_PUB.count_msg;
747 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
748 THEN
749 RAISE FND_API.G_EXC_ERROR;
750 END IF;
751
752 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
753 THEN
754 fnd_log.string
755 (
756 fnd_log.level_statement,
757 L_DEBUG_MODULE,
758 'AHL_FMP_MR_ROUTE_PVT.PROCESS_MR_ROUTE is successful'
759 );
760 END IF;
761 END IF;
762
763 IF p_x_mr_visit_type_tbl.COUNT > 0
764 THEN
765 FOR i IN p_x_mr_visit_type_tbl.FIRST..p_x_mr_visit_type_tbl.LAST
766 LOOP
767 p_x_mr_visit_type_tbl(i).MR_HEADER_ID := l_mr_header_rec.mr_header_id;
768 END LOOP;
769
770 AHL_FMP_MR_VISIT_TYPES_PVT.PROCESS_MR_VISIT_TYPES
771 (
772 p_api_version => 1.0,
773 p_init_msg_list => FND_API.G_FALSE,
774 p_commit => FND_API.G_FALSE,
775 p_validation_level => p_validation_level,
776 p_default => p_default,
777 p_module_type => p_module_type,
778 x_return_status => x_return_status,
779 x_msg_count => x_msg_count,
780 x_msg_data => x_msg_data,
781 p_x_mr_visit_type_tbl => p_x_mr_visit_type_tbl
782 );
783
784 x_msg_count := FND_MSG_PUB.count_msg;
785 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
786 THEN
787 RAISE FND_API.G_EXC_ERROR;
788 END IF;
789
790 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
791 THEN
792 fnd_log.string
793 (
794 fnd_log.level_statement,
795 L_DEBUG_MODULE,
796 'AHL_FMP_MR_VISIT_TYPES_PVT.PROCESS_MR_VISIT_TYPES is successful'
797 );
798 END IF;
799 END IF;
800
801 IF p_x_effectivity_tbl.COUNT > 0
802 THEN
803 AHL_FMP_MR_EFFECTIVITY_PVT.process_effectivity
804 (
805 p_api_version => 1.0,
806 p_init_msg_list => FND_API.G_FALSE,
807 p_commit => FND_API.G_FALSE,
808 p_validation_level => p_validation_level,
809 p_default => p_default,
810 p_module_type => p_module_type,
811 x_return_status => x_return_status,
812 x_msg_count => x_msg_count,
813 x_msg_data => x_msg_data,
814 p_x_effectivity_tbl => p_x_effectivity_tbl,
815 p_mr_header_id => l_mr_header_rec.mr_header_id,
816 p_super_user => l_mr_header_rec.superuser_role
817 );
818
819 x_msg_count := FND_MSG_PUB.count_msg;
820 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
821 THEN
822 RAISE FND_API.G_EXC_ERROR;
823 END IF;
824
825 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
826 THEN
827 fnd_log.string
828 (
829 fnd_log.level_statement,
830 L_DEBUG_MODULE,
831 'AHL_FMP_MR_EFFECTIVITY_PVT.process_effectivity is successful'
832 );
833 END IF;
834 END IF;
835
836 IF p_x_mr_relation_tbl.COUNT > 0
837 THEN
838 FOR i IN p_x_mr_relation_tbl.FIRST..p_x_mr_relation_tbl.LAST
839 LOOP
840 p_x_mr_relation_tbl(i).MR_HEADER_ID := l_mr_header_rec.mr_header_id;
841 p_x_mr_relation_tbl(i).MR_TITLE := l_mr_header_rec.title;
842 p_x_mr_relation_tbl(i).MR_VERSION_NUMBER := l_mr_header_rec.version_number;
843 END LOOP;
844
845 AHL_FMP_MR_RELATION_PVT.PROCESS_MR_RELATION
846 (
847 p_api_version => 1.0,
848 p_init_msg_list => FND_API.G_FALSE,
849 p_commit => FND_API.G_FALSE,
850 p_validation_level => p_validation_level,
851 p_default => p_default,
852 p_module_type => p_module_type,
853 x_return_status => x_return_status,
854 x_msg_count => x_msg_count,
855 x_msg_data => x_msg_data,
856 p_x_mr_relation_tbl => p_x_mr_relation_tbl
857 );
858
859 x_msg_count := FND_MSG_PUB.count_msg;
860 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
861 THEN
862 RAISE FND_API.G_EXC_ERROR;
863 END IF;
864
865 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
866 THEN
867 fnd_log.string
868 (
869 fnd_log.level_statement,
870 L_DEBUG_MODULE,
871 'AHL_FMP_MR_RELATION_PVT.PROCESS_MR_RELATION is successful'
872 );
873 END IF;
874 END IF;
875
876 -- pdoki added for SBE Project, Start.
877 IF p_x_accomtriggers_tbl.COUNT > 0
878 THEN
879 FOR i IN p_x_accomtriggers_tbl.FIRST..p_x_accomtriggers_tbl.LAST
880 LOOP
881 p_x_accomtriggers_tbl(i).MR_HEADER_ID := l_mr_header_rec.mr_header_id;
882 p_x_accomtriggers_tbl(i).MR_TITLE := l_mr_header_rec.title;
883 p_x_accomtriggers_tbl(i).MR_VERSION_NUMBER := l_mr_header_rec.version_number;
884 END LOOP;
885
886 AHL_FMP_MR_RELATION_PVT.PROCESS_MR_RELATION
887 (
888 p_api_version => 1.0,
889 p_init_msg_list => FND_API.G_FALSE,
890 p_commit => FND_API.G_FALSE,
891 p_validation_level => p_validation_level,
892 p_default => p_default,
893 p_module_type => p_module_type,
894 x_return_status => x_return_status,
895 x_msg_count => x_msg_count,
896 x_msg_data => x_msg_data,
897 p_x_mr_relation_tbl => p_x_accomtriggers_tbl
898 );
899
900 x_msg_count := FND_MSG_PUB.count_msg;
901 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
902 THEN
903 RAISE FND_API.G_EXC_ERROR;
904 END IF;
905
906 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
907 THEN
908 fnd_log.string
909 (
910 fnd_log.level_statement,
911 L_DEBUG_MODULE,
912 'AHL_FMP_MR_RELATION_PVT.PROCESS_MR_RELATION is successful'
913 );
914 END IF;
915 END IF;
916 -- pdoki added for SBE Project, End.
917 -- API body ends here
918
919 -- Log API exit point
920 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
921 THEN
922 fnd_log.string
923 (
924 fnd_log.level_procedure,
925 L_DEBUG_MODULE||'.end',
926 'At the end of PLSQL procedure'
927 );
928 END IF;
929
930 -- Check Error Message stack.
931 x_msg_count := FND_MSG_PUB.count_msg;
932 IF x_msg_count > 0
933 THEN
934 RAISE FND_API.G_EXC_ERROR;
935 END IF;
936
937 -- Commit, depending on p_commit flag
938 IF FND_API.TO_BOOLEAN(p_commit) THEN
939 COMMIT WORK;
940 END IF;
941
942 -- Standard call to get message count and if count is 1, get message info
943 FND_MSG_PUB.count_and_get
944 (
945 p_count => x_msg_count,
946 p_data => x_msg_data,
947 p_encoded => FND_API.G_FALSE
948 );
949
950 EXCEPTION
951 WHEN FND_API.G_EXC_ERROR THEN
952 x_return_status := FND_API.G_RET_STS_ERROR;
953 Rollback to Modify_Mr_SP;
954 FND_MSG_PUB.count_and_get
955 (
956 p_count => x_msg_count,
957 p_data => x_msg_data,
958 p_encoded => FND_API.G_FALSE
959 );
960
961 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
962 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
963 Rollback to Modify_Mr_SP;
964 FND_MSG_PUB.count_and_get
965 (
966 p_count => x_msg_count,
967 p_data => x_msg_data,
968 p_encoded => FND_API.G_FALSE
969 );
970
971 WHEN OTHERS THEN
972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
973 Rollback to Modify_Mr_SP;
974 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
975 THEN
976 FND_MSG_PUB.add_exc_msg
977 (
978 p_pkg_name => G_PKG_NAME,
979 p_procedure_name => 'Modify_Mr',
980 p_error_text => SUBSTR(SQLERRM,1,240)
981 );
982 END IF;
983 FND_MSG_PUB.count_and_get
984 (
985 p_count => x_msg_count,
986 p_data => x_msg_data,
987 p_encoded => FND_API.G_FALSE
988 );
989
990 END Modify_Mr;
991 -- Overloaded procedure retained for backaward compatibility.
992 -- This procedure will call the above procedure with api_version = 2.
993 PROCEDURE Modify_Mr
994 (
995 -- default IN params
996 p_api_version IN NUMBER,
997 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
998 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
999 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1000 p_default IN VARCHAR2 :=FND_API.G_FALSE,
1001 p_module_type IN VARCHAR2 :=NULL,
1002 -- default OUT params
1003 x_return_status OUT NOCOPY VARCHAR2,
1004 x_msg_count OUT NOCOPY NUMBER,
1005 x_msg_data OUT NOCOPY VARCHAR2,
1006 -- functionality specific params
1007 p_mr_header_rec IN AHL_FMP_MR_HEADER_PVT.mr_header_rec,
1008 p_x_mr_doc_tbl IN OUT NOCOPY AHL_FMP_MR_DOC_ASSO_PVT.doc_association_tbl,
1009 p_x_mr_route_tbl IN OUT NOCOPY AHL_FMP_MR_ROUTE_PVT.mr_route_tbl,
1010 p_x_mr_visit_type_tbl IN OUT NOCOPY AHL_FMP_MR_VISIT_TYPES_PVT.mr_visit_type_tbl_type,
1011 p_x_effectivity_tbl IN OUT NOCOPY AHL_FMP_MR_EFFECTIVITY_PVT.effectivity_tbl_type,
1012 p_x_mr_relation_tbl IN OUT NOCOPY AHL_FMP_MR_RELATION_PVT.mr_relation_tbl
1013 )
1014 IS
1015 -- 1. Declare local variables
1016 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Mr';
1017 l_api_version CONSTANT NUMBER := 1.0;
1018
1019 l_x_accomtriggers_tbl AHL_FMP_MR_RELATION_PVT.mr_relation_tbl;
1020
1021 BEGIN
1022
1023 -- Initialize API return status to success
1024 x_return_status := FND_API.G_RET_STS_SUCCESS;
1025
1026 -- Standard call to check for call compatibility
1027 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1028 THEN
1029 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1030 END IF;
1031
1032 AHL_FMP_MR_PUB.Modify_Mr
1033 (
1034 -- standard IN params
1035 p_api_version => 2.0,
1036 p_init_msg_list => p_init_msg_list,
1037 p_commit => p_commit,
1038 p_validation_level => p_validation_level,
1039 p_default => p_default,
1040 p_module_type => p_module_type,
1041 -- standard OUT params
1042 x_return_status => x_return_status,
1043 x_msg_count => x_msg_count,
1044 x_msg_data => x_msg_data,
1045 -- procedure params
1046 p_mr_header_rec => p_mr_header_rec,
1047 p_x_mr_doc_tbl => p_x_mr_doc_tbl,
1048 p_x_mr_route_tbl => p_x_mr_route_tbl,
1049 p_x_mr_visit_type_tbl => p_x_mr_visit_type_tbl,
1050 p_x_effectivity_tbl => p_x_effectivity_tbl,
1051 p_x_mr_relation_tbl => p_x_mr_relation_tbl,
1052 p_x_accomtriggers_tbl => l_x_accomtriggers_tbl);
1053
1054
1055
1056 EXCEPTION
1057 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1058 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1059 FND_MSG_PUB.count_and_get
1060 (
1061 p_count => x_msg_count,
1062 p_data => x_msg_data,
1063 p_encoded => FND_API.G_FALSE
1064 );
1065
1066 WHEN OTHERS THEN
1067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1068 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1069 THEN
1070 FND_MSG_PUB.add_exc_msg
1071 (
1072 p_pkg_name => G_PKG_NAME,
1073 p_procedure_name => 'Modify_Mr',
1074 p_error_text => SUBSTR(SQLERRM,1,240)
1075 );
1076 END IF;
1077 FND_MSG_PUB.count_and_get
1078 (
1079 p_count => x_msg_count,
1080 p_data => x_msg_data,
1081 p_encoded => FND_API.G_FALSE
1082 );
1083
1084 END Modify_Mr;
1085
1086 PROCEDURE Delete_Mr
1087 (
1088 -- standard IN params
1089 p_api_version IN NUMBER,
1090 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1091 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1092 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1093 p_default IN VARCHAR2 :=FND_API.G_FALSE,
1094 p_module_type IN VARCHAR2 :=NULL,
1095 -- standard OUT params
1096 x_return_status OUT NOCOPY VARCHAR2,
1097 x_msg_count OUT NOCOPY NUMBER,
1098 x_msg_data OUT NOCOPY VARCHAR2,
1099 -- procedure params
1100 p_mr_header_id IN NUMBER,
1101 p_mr_title IN VARCHAR2,
1102 p_mr_version_number IN NUMBER,
1103 p_mr_object_version IN NUMBER
1104 )
1105 IS
1106 -- 1. Declare local variables
1107 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Mr';
1108 l_api_version CONSTANT NUMBER := 1.0;
1109 l_return_status VARCHAR2(1);
1110 l_msg_count NUMBER;
1111 l_msg_data VARCHAR2(2000);
1112 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1113
1114 l_mr_header_id NUMBER := p_mr_header_id;
1115
1116 BEGIN
1117 -- Standard start of API savepoint
1118 SAVEPOINT Delete_Mr_SP;
1119
1120 -- Standard call to check for call compatibility
1121 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1122 THEN
1123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1124 END IF;
1125
1126 -- Initialize message, depending on p_init_msg_list flag
1127 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
1128 FND_MSG_PUB.Initialize;
1129 END IF;
1130
1131 -- Initialize API return status to success
1132 x_return_status := FND_API.G_RET_STS_SUCCESS;
1133
1134 -- Log API entry point
1135 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1136 THEN
1137 fnd_log.string
1138 (
1139 fnd_log.level_procedure,
1140 L_DEBUG_MODULE||'.begin',
1141 'At the start of PLSQL procedure'
1142 );
1143 END IF;
1144
1145 -- API body starts here
1146 IF (l_mr_header_id IS NULL)
1147 THEN
1148 AHL_FMP_COMMON_PVT.mr_title_version_to_id
1149 (
1150 p_mr_title => p_mr_title,
1151 p_mr_version_number => p_mr_version_number,
1152 x_mr_header_id => l_mr_header_id,
1153 x_return_status => x_return_status
1154 );
1155
1156 x_msg_count := FND_MSG_PUB.count_msg;
1157 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
1158 THEN
1159 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1160 THEN
1161 fnd_log.string
1162 (
1163 fnd_log.level_error,
1164 L_DEBUG_MODULE||'.end',
1165 'AHL_FMP_COMMON_PVT.mr_title_version_to_id returned error'
1166 );
1167 END IF;
1168 RAISE FND_API.G_EXC_ERROR;
1169 END IF;
1170 END IF;
1171
1172 AHL_FMP_MR_HEADER_PVT.DELETE_MR_HEADER
1173 (
1174 p_api_version => 1.0,
1175 p_init_msg_list => FND_API.G_FALSE,
1176 p_commit => FND_API.G_FALSE,
1177 p_validation_level => p_validation_level,
1178 p_default => p_default,
1179 p_module_type => p_module_type,
1180 x_return_status => x_return_status,
1181 x_msg_count => x_msg_count,
1182 x_msg_data => x_msg_data,
1183 p_mr_header_id => l_mr_header_id,
1184 p_OBJECT_VERSION_NUMBER => p_mr_object_version
1185 );
1186
1187 x_msg_count := FND_MSG_PUB.count_msg;
1188 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
1189 THEN
1190 RAISE FND_API.G_EXC_ERROR;
1191 END IF;
1192
1193 -- API body ends here
1194
1195 -- Log API exit point
1196 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1197 THEN
1198 fnd_log.string
1199 (
1200 fnd_log.level_procedure,
1201 L_DEBUG_MODULE||'.end',
1202 'At the end of PLSQL procedure'
1203 );
1204 END IF;
1205
1206 -- Check Error Message stack.
1207 x_msg_count := FND_MSG_PUB.count_msg;
1208 IF x_msg_count > 0
1209 THEN
1210 RAISE FND_API.G_EXC_ERROR;
1211 END IF;
1212
1213 -- Commit, depending on p_commit flag
1214 IF FND_API.TO_BOOLEAN(p_commit) THEN
1215 COMMIT WORK;
1216 END IF;
1217
1218 -- Standard call to get message count and if count is 1, get message info
1219 FND_MSG_PUB.count_and_get
1220 (
1221 p_count => x_msg_count,
1222 p_data => x_msg_data,
1223 p_encoded => FND_API.G_FALSE
1224 );
1225
1226 EXCEPTION
1227 WHEN FND_API.G_EXC_ERROR THEN
1228 x_return_status := FND_API.G_RET_STS_ERROR;
1229 Rollback to Delete_Mr_SP;
1230 FND_MSG_PUB.count_and_get
1231 (
1232 p_count => x_msg_count,
1233 p_data => x_msg_data,
1234 p_encoded => FND_API.G_FALSE
1235 );
1236
1237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1239 Rollback to Delete_Mr_SP;
1240 FND_MSG_PUB.count_and_get
1241 (
1242 p_count => x_msg_count,
1243 p_data => x_msg_data,
1244 p_encoded => FND_API.G_FALSE
1245 );
1246
1247 WHEN OTHERS THEN
1248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1249 Rollback to Delete_Mr_SP;
1250 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1251 THEN
1252 FND_MSG_PUB.add_exc_msg
1253 (
1254 p_pkg_name => G_PKG_NAME,
1255 p_procedure_name => 'Delete_Mr',
1256 p_error_text => SUBSTR(SQLERRM,1,240)
1257 );
1258 END IF;
1259 FND_MSG_PUB.count_and_get
1260 (
1261 p_count => x_msg_count,
1262 p_data => x_msg_data,
1263 p_encoded => FND_API.G_FALSE
1264 );
1265
1266 END Delete_Mr;
1267
1268 PROCEDURE Create_Mr_Revision
1269 (
1270 -- standard IN params
1271 p_api_version IN NUMBER,
1272 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1273 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1274 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1275 p_default IN VARCHAR2 :=FND_API.G_FALSE,
1276 p_module_type IN VARCHAR2 :=NULL,
1277 -- standard OUT params
1278 x_return_status OUT NOCOPY VARCHAR2,
1279 x_msg_count OUT NOCOPY NUMBER,
1280 x_msg_data OUT NOCOPY VARCHAR2,
1281 -- procedure params
1282 p_mr_header_id IN NUMBER,
1283 p_mr_title IN VARCHAR2,
1284 p_mr_version_number IN NUMBER,
1285 p_mr_object_version IN NUMBER,
1286 x_new_mr_header_id OUT NOCOPY NUMBER
1287 )
1288 IS
1289 -- 1. Declare local variables
1290 l_api_name CONSTANT VARCHAR2(30) := 'Create_Mr_Revision';
1291 l_api_version CONSTANT NUMBER := 1.0;
1292 l_return_status VARCHAR2(1);
1293 l_msg_count NUMBER;
1294 l_msg_data VARCHAR2(2000);
1295 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1296
1297 l_mr_header_id NUMBER := p_mr_header_id;
1298
1299 BEGIN
1300 -- Standard start of API savepoint
1301 SAVEPOINT Create_Mr_Revision_SP;
1302
1303 -- Standard call to check for call compatibility
1304 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1305 THEN
1306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1307 END IF;
1308
1309 -- Initialize message, depending on p_init_msg_list flag
1310 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
1311 FND_MSG_PUB.Initialize;
1312 END IF;
1313
1314 -- Initialize API return status to success
1315 x_return_status := FND_API.G_RET_STS_SUCCESS;
1316
1317 -- Log API entry point
1318 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1319 THEN
1320 fnd_log.string
1321 (
1322 fnd_log.level_procedure,
1323 L_DEBUG_MODULE||'.begin',
1324 'At the start of PLSQL procedure'
1325 );
1326 END IF;
1327
1328 -- API body starts here
1329 IF (l_mr_header_id IS NULL)
1330 THEN
1331 AHL_FMP_COMMON_PVT.mr_title_version_to_id
1332 (
1333 p_mr_title => p_mr_title,
1334 p_mr_version_number => p_mr_version_number,
1335 x_mr_header_id => l_mr_header_id,
1336 x_return_status => x_return_status
1337 );
1338
1339 x_msg_count := FND_MSG_PUB.count_msg;
1340 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
1341 THEN
1342 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1343 THEN
1344 fnd_log.string
1345 (
1346 fnd_log.level_error,
1347 L_DEBUG_MODULE||'.end',
1348 'AHL_FMP_COMMON_PVT.mr_title_version_to_id returned error'
1349 );
1350 END IF;
1351 RAISE FND_API.G_EXC_ERROR;
1352 END IF;
1353 END IF;
1354
1355 -- Need to check for object_version_number here...
1356
1357 AHL_FMP_MR_REVISION_PVT.CREATE_MR_REVISION
1358 (
1359 p_api_version => 1.0,
1360 p_init_msg_list => FND_API.G_FALSE,
1361 p_commit => FND_API.G_FALSE,
1362 p_validation_level => p_validation_level,
1363 p_default => p_default,
1364 p_module_type => p_module_type,
1365 x_return_status => x_return_status,
1366 x_msg_count => x_msg_count,
1367 x_msg_data => x_msg_data,
1368 p_source_mr_header_id => l_mr_header_id,
1369 x_new_mr_header_id => x_new_mr_header_id
1370 );
1371
1372 x_msg_count := FND_MSG_PUB.count_msg;
1373 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
1374 THEN
1375 RAISE FND_API.G_EXC_ERROR;
1376 END IF;
1377
1378 -- API body ends here
1379
1380 -- Log API exit point
1381 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1382 THEN
1383 fnd_log.string
1384 (
1385 fnd_log.level_procedure,
1386 L_DEBUG_MODULE||'.end',
1387 'At the end of PLSQL procedure'
1388 );
1389 END IF;
1390
1391 -- Check Error Message stack.
1392 x_msg_count := FND_MSG_PUB.count_msg;
1393 IF x_msg_count > 0
1394 THEN
1395 RAISE FND_API.G_EXC_ERROR;
1396 END IF;
1397
1398 -- Commit, depending on p_commit flag
1399 IF FND_API.TO_BOOLEAN(p_commit) THEN
1400 COMMIT WORK;
1401 END IF;
1402
1403 -- Standard call to get message count and if count is 1, get message info
1404 FND_MSG_PUB.count_and_get
1405 (
1406 p_count => x_msg_count,
1407 p_data => x_msg_data,
1408 p_encoded => FND_API.G_FALSE
1409 );
1410
1411 EXCEPTION
1412 WHEN FND_API.G_EXC_ERROR THEN
1413 x_return_status := FND_API.G_RET_STS_ERROR;
1414 Rollback to Create_Mr_Revision_SP;
1415 FND_MSG_PUB.count_and_get
1416 (
1417 p_count => x_msg_count,
1418 p_data => x_msg_data,
1419 p_encoded => FND_API.G_FALSE
1420 );
1421
1422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1424 Rollback to Create_Mr_Revision_SP;
1425 FND_MSG_PUB.count_and_get
1426 (
1427 p_count => x_msg_count,
1428 p_data => x_msg_data,
1429 p_encoded => FND_API.G_FALSE
1430 );
1431
1432 WHEN OTHERS THEN
1433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1434 Rollback to Create_Mr_Revision_SP;
1435 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1436 THEN
1437 FND_MSG_PUB.add_exc_msg
1438 (
1439 p_pkg_name => G_PKG_NAME,
1440 p_procedure_name => 'Create_Mr_Revision',
1441 p_error_text => SUBSTR(SQLERRM,1,240)
1442 );
1443 END IF;
1444 FND_MSG_PUB.count_and_get
1445 (
1446 p_count => x_msg_count,
1447 p_data => x_msg_data,
1448 p_encoded => FND_API.G_FALSE
1449 );
1450
1451 END Create_Mr_Revision;
1452
1453 PROCEDURE Initiate_Mr_Approval
1454 (
1455 -- standard IN params
1456 p_api_version IN NUMBER,
1457 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1458 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1459 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1460 p_default IN VARCHAR2 :=FND_API.G_FALSE,
1461 p_module_type IN VARCHAR2 :=NULL,
1462 -- standard OUT params
1463 x_return_status OUT NOCOPY VARCHAR2,
1464 x_msg_count OUT NOCOPY NUMBER,
1465 x_msg_data OUT NOCOPY VARCHAR2,
1466 -- procedure params
1467 p_mr_header_id IN NUMBER,
1468 p_mr_title IN VARCHAR2,
1469 p_mr_version_number IN NUMBER,
1470 p_mr_object_version IN NUMBER,
1471 p_apprv_type IN VARCHAR2 :='COMPLETE'
1472 )
1473 IS
1474 -- 1. Declare local variables
1475 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_Mr_Approval';
1476 l_api_version CONSTANT NUMBER := 1.0;
1477 l_return_status VARCHAR2(1);
1478 l_msg_count NUMBER;
1479 l_msg_data VARCHAR2(2000);
1480 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1481
1482 l_mr_header_id NUMBER := p_mr_header_id;
1483
1484 BEGIN
1485 -- Standard start of API savepoint
1486 SAVEPOINT Initiate_Mr_Approval_SP;
1487
1488 -- Standard call to check for call compatibility
1489 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1490 THEN
1491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1492 END IF;
1493
1494 -- Initialize message, depending on p_init_msg_list flag
1495 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
1496 FND_MSG_PUB.Initialize;
1497 END IF;
1498
1499 -- Initialize API return status to success
1500 x_return_status := FND_API.G_RET_STS_SUCCESS;
1501
1502 -- Log API entry point
1503 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1504 THEN
1505 fnd_log.string
1506 (
1507 fnd_log.level_procedure,
1508 L_DEBUG_MODULE||'.begin',
1509 'At the start of PLSQL procedure'
1510 );
1511 END IF;
1512
1513 -- API body starts here
1514 IF (l_mr_header_id IS NULL)
1515 THEN
1516 AHL_FMP_COMMON_PVT.mr_title_version_to_id
1517 (
1518 p_mr_title => p_mr_title,
1519 p_mr_version_number => p_mr_version_number,
1520 x_mr_header_id => l_mr_header_id,
1521 x_return_status => x_return_status
1522 );
1523
1524 x_msg_count := FND_MSG_PUB.count_msg;
1525 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
1526 THEN
1527 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1528 THEN
1529 fnd_log.string
1530 (
1531 fnd_log.level_error,
1532 L_DEBUG_MODULE||'.end',
1533 'AHL_FMP_COMMON_PVT.mr_title_version_to_id returned error'
1534 );
1535 END IF;
1536 RAISE FND_API.G_EXC_ERROR;
1537 END IF;
1538 END IF;
1539
1540 AHL_FMP_MR_REVISION_PVT.INITIATE_MR_APPROVAL
1541 (
1542 p_api_version => 1.0,
1543 p_init_msg_list => FND_API.G_FALSE,
1544 p_commit => FND_API.G_FALSE,
1545 p_validation_level => p_validation_level,
1546 p_default => p_default,
1547 p_module_type => p_module_type,
1548 x_return_status => x_return_status,
1549 x_msg_count => x_msg_count,
1550 x_msg_data => x_msg_data,
1551 p_source_mr_header_id => l_mr_header_id,
1552 p_object_Version_number => p_mr_object_version,
1553 p_apprv_type => p_apprv_type
1554 );
1555
1556 x_msg_count := FND_MSG_PUB.count_msg;
1557 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
1558 THEN
1559 RAISE FND_API.G_EXC_ERROR;
1560 END IF;
1561
1562 -- API body ends here
1563
1564 -- Log API exit point
1565 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1566 THEN
1567 fnd_log.string
1568 (
1569 fnd_log.level_procedure,
1570 L_DEBUG_MODULE||'.end',
1571 'At the end of PLSQL procedure'
1572 );
1573 END IF;
1574
1575 -- Check Error Message stack.
1576 x_msg_count := FND_MSG_PUB.count_msg;
1577 IF x_msg_count > 0
1578 THEN
1579 RAISE FND_API.G_EXC_ERROR;
1580 END IF;
1581
1582 -- Commit, depending on p_commit flag
1583 IF FND_API.TO_BOOLEAN(p_commit) THEN
1584 COMMIT WORK;
1585 END IF;
1586
1587 -- Standard call to get message count and if count is 1, get message info
1588 FND_MSG_PUB.count_and_get
1589 (
1590 p_count => x_msg_count,
1591 p_data => x_msg_data,
1592 p_encoded => FND_API.G_FALSE
1593 );
1594
1595 EXCEPTION
1596 WHEN FND_API.G_EXC_ERROR THEN
1597 x_return_status := FND_API.G_RET_STS_ERROR;
1598 Rollback to Initiate_Mr_Approval_SP;
1599 FND_MSG_PUB.count_and_get
1600 (
1601 p_count => x_msg_count,
1602 p_data => x_msg_data,
1603 p_encoded => FND_API.G_FALSE
1604 );
1605
1606 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1608 Rollback to Initiate_Mr_Approval_SP;
1609 FND_MSG_PUB.count_and_get
1610 (
1611 p_count => x_msg_count,
1612 p_data => x_msg_data,
1613 p_encoded => FND_API.G_FALSE
1614 );
1615
1616 WHEN OTHERS THEN
1617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1618 Rollback to Initiate_Mr_Approval_SP;
1619 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1620 THEN
1621 FND_MSG_PUB.add_exc_msg
1622 (
1623 p_pkg_name => G_PKG_NAME,
1624 p_procedure_name => 'Initiate_Mr_Approval',
1625 p_error_text => SUBSTR(SQLERRM,1,240)
1626 );
1627 END IF;
1628 FND_MSG_PUB.count_and_get
1629 (
1630 p_count => x_msg_count,
1631 p_data => x_msg_data,
1632 p_encoded => FND_API.G_FALSE
1633 );
1634
1635 END Initiate_Mr_Approval;
1636
1637 PROCEDURE Process_Mr_Route_Seq
1638 (
1639 -- default IN params
1640 p_api_version IN NUMBER,
1641 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1642 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1643 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1644 p_default IN VARCHAR2 :=FND_API.G_FALSE,
1645 p_module_type IN VARCHAR2 :=NULL,
1646 -- default OUT params
1647 x_return_status OUT NOCOPY VARCHAR2,
1648 x_msg_count OUT NOCOPY NUMBER,
1649 x_msg_data OUT NOCOPY VARCHAR2,
1650 -- functionality specific params
1651 p_x_mr_route_seq_tbl IN OUT NOCOPY AHL_FMP_MR_ROUTE_SEQNCE_PVT.mr_route_seq_tbl
1652 )
1653 IS
1654 -- 1. Declare local variables
1655 l_api_name CONSTANT VARCHAR2(30) := 'Process_Mr_Route_Seq';
1656 l_api_version CONSTANT NUMBER := 1.0;
1657 l_return_status VARCHAR2(1);
1658 l_msg_count NUMBER;
1659 l_msg_data VARCHAR2(2000);
1660 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1661
1662 BEGIN
1663 -- Standard start of API savepoint
1664 SAVEPOINT Process_Mr_Route_Seq_SP;
1665
1666 -- Standard call to check for call compatibility
1667 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1668 THEN
1669 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1670 END IF;
1671
1672 -- Initialize message, depending on p_init_msg_list flag
1673 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
1674 FND_MSG_PUB.Initialize;
1675 END IF;
1676
1677 -- Initialize API return status to success
1678 x_return_status := FND_API.G_RET_STS_SUCCESS;
1679
1680 -- Log API entry point
1681 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1682 THEN
1683 fnd_log.string
1684 (
1685 fnd_log.level_procedure,
1686 L_DEBUG_MODULE||'.begin',
1687 'At the start of PLSQL procedure'
1688 );
1689 END IF;
1690
1691 -- API body starts here
1692 IF p_x_mr_route_seq_tbl.COUNT > 0
1693 THEN
1694 AHL_FMP_MR_ROUTE_SEQNCE_PVT.PROCESS_MR_ROUTE_SEQ
1695 (
1696 p_api_version => 1.0,
1697 p_init_msg_list => FND_API.G_FALSE,
1698 p_commit => FND_API.G_FALSE,
1699 p_validation_level => p_validation_level,
1700 p_default => p_default,
1701 p_module_type => p_module_type,
1702 x_return_status => x_return_status,
1703 x_msg_count => x_msg_count,
1704 x_msg_data => x_msg_data,
1705 p_x_mr_route_seq_tbl => p_x_mr_route_seq_tbl
1706 );
1707
1708 x_msg_count := FND_MSG_PUB.count_msg;
1709 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
1710 THEN
1711 RAISE FND_API.G_EXC_ERROR;
1712 END IF;
1713 END IF;
1714
1715
1716 -- API body ends here
1717
1718 -- Log API exit point
1719 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1720 THEN
1721 fnd_log.string
1722 (
1723 fnd_log.level_procedure,
1724 L_DEBUG_MODULE||'.end',
1725 'At the end of PLSQL procedure'
1726 );
1727 END IF;
1728
1729 -- Check Error Message stack.
1730 x_msg_count := FND_MSG_PUB.count_msg;
1731 IF x_msg_count > 0
1732 THEN
1733 RAISE FND_API.G_EXC_ERROR;
1734 END IF;
1735
1736 -- Commit, depending on p_commit flag
1737 IF FND_API.TO_BOOLEAN(p_commit) THEN
1738 COMMIT WORK;
1739 END IF;
1740
1741 -- Standard call to get message count and if count is 1, get message info
1742 FND_MSG_PUB.count_and_get
1743 (
1744 p_count => x_msg_count,
1745 p_data => x_msg_data,
1746 p_encoded => FND_API.G_FALSE
1747 );
1748
1749 EXCEPTION
1750 WHEN FND_API.G_EXC_ERROR THEN
1751 x_return_status := FND_API.G_RET_STS_ERROR;
1752 Rollback to Process_Mr_Route_Seq_SP;
1753 FND_MSG_PUB.count_and_get
1754 (
1755 p_count => x_msg_count,
1756 p_data => x_msg_data,
1757 p_encoded => FND_API.G_FALSE
1758 );
1759
1760 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1762 Rollback to Process_Mr_Route_Seq_SP;
1763 FND_MSG_PUB.count_and_get
1764 (
1765 p_count => x_msg_count,
1766 p_data => x_msg_data,
1767 p_encoded => FND_API.G_FALSE
1768 );
1769
1770 WHEN OTHERS THEN
1771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1772 Rollback to Process_Mr_Route_Seq_SP;
1773 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1774 THEN
1775 FND_MSG_PUB.add_exc_msg
1776 (
1777 p_pkg_name => G_PKG_NAME,
1778 p_procedure_name => 'Process_Mr_Route_Seq',
1779 p_error_text => SUBSTR(SQLERRM,1,240)
1780 );
1781 END IF;
1782 FND_MSG_PUB.count_and_get
1783 (
1784 p_count => x_msg_count,
1785 p_data => x_msg_data,
1786 p_encoded => FND_API.G_FALSE
1787 );
1788
1789 END Process_Mr_Route_Seq;
1790
1791 PROCEDURE Process_Mr_Effectivities
1792 (
1793 -- standard IN params
1794 p_api_version IN NUMBER,
1795 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1796 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1797 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1798 p_default IN VARCHAR2 :=FND_API.G_FALSE,
1799 p_module_type IN VARCHAR2 :=NULL,
1800 -- standard OUT params
1801 x_return_status OUT NOCOPY VARCHAR2,
1802 x_msg_count OUT NOCOPY NUMBER,
1803 x_msg_data OUT NOCOPY VARCHAR2,
1804 -- procedure params
1805 p_mr_header_id IN NUMBER,
1806 p_mr_title IN VARCHAR2,
1807 p_mr_version_number IN NUMBER,
1808 p_super_user IN VARCHAR2 :='N',
1809 p_mr_effectivity_id IN NUMBER,
1810 p_mr_effectivity_name IN VARCHAR2,
1811 p_x_mr_effectivity_detail_tbl IN OUT NOCOPY AHL_FMP_EFFECTIVITY_DTL_PVT.effectivity_detail_tbl_type,
1812 p_x_effty_ext_detail_tbl IN OUT NOCOPY AHL_FMP_EFFECTIVITY_DTL_PVT.effty_ext_detail_tbl_type,
1813 p_x_mr_threshold_rec IN OUT NOCOPY AHL_FMP_MR_INTERVAL_PVT.threshold_rec_type,
1814 p_x_mr_interval_tbl IN OUT NOCOPY AHL_FMP_MR_INTERVAL_PVT.interval_tbl_type
1815 )
1816 IS
1817 -- 1. Declare local variables
1818 l_api_name CONSTANT VARCHAR2(30) := 'Process_Mr_Effectivities';
1819 l_api_version CONSTANT NUMBER := 1.0;
1820 l_return_status VARCHAR2(1);
1821 l_msg_count NUMBER;
1822 l_msg_data VARCHAR2(2000);
1823 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1824
1825 l_mr_header_id NUMBER := p_mr_header_id;
1826 l_mr_effectivity_id NUMBER := p_mr_effectivity_id;
1827
1828 BEGIN
1829 -- Standard start of API savepoint
1830 SAVEPOINT Process_Mr_Effectivities_SP;
1831
1832 -- Standard call to check for call compatibility
1833 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1834 THEN
1835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1836 END IF;
1837
1838 -- Initialize message, depending on p_init_msg_list flag
1839 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
1840 FND_MSG_PUB.Initialize;
1841 END IF;
1842
1843 -- Initialize API return status to success
1844 x_return_status := FND_API.G_RET_STS_SUCCESS;
1845
1846 -- Log API entry point
1847 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1848 THEN
1849 fnd_log.string
1850 (
1851 fnd_log.level_procedure,
1852 L_DEBUG_MODULE||'.begin',
1853 'At the start of PLSQL procedure'
1854 );
1855 END IF;
1856
1857 -- API body starts here
1858 IF (l_mr_header_id IS NULL)
1859 THEN
1860 AHL_FMP_COMMON_PVT.mr_title_version_to_id
1861 (
1862 p_mr_title => p_mr_title,
1863 p_mr_version_number => p_mr_version_number,
1864 x_mr_header_id => l_mr_header_id,
1865 x_return_status => x_return_status
1866 );
1867
1868 x_msg_count := FND_MSG_PUB.count_msg;
1869 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
1870 THEN
1871 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1872 THEN
1873 fnd_log.string
1874 (
1875 fnd_log.level_error,
1876 L_DEBUG_MODULE||'.end',
1877 'AHL_FMP_COMMON_PVT.mr_title_version_to_id returned error'
1878 );
1879 END IF;
1880 RAISE FND_API.G_EXC_ERROR;
1881 END IF;
1882 END IF;
1883
1884 IF (l_mr_effectivity_id IS NULL)
1885 THEN
1886 AHL_FMP_COMMON_PVT.Mr_Effectivity_Name_To_Id
1887 (
1888 p_mr_header_id => l_mr_header_id,
1889 p_mr_effectivity_name => p_mr_effectivity_name,
1890 x_mr_effectivity_id => l_mr_effectivity_id,
1891 x_return_status => x_return_status
1892 );
1893
1894 x_msg_count := FND_MSG_PUB.count_msg;
1895 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
1896 THEN
1897 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1898 THEN
1899 fnd_log.string
1900 (
1901 fnd_log.level_error,
1902 L_DEBUG_MODULE||'.end',
1903 'AHL_FMP_COMMON_PVT.Mr_Effectivity_Name_To_Id returned error'
1904 );
1905 END IF;
1906 RAISE FND_API.G_EXC_ERROR;
1907 END IF;
1908 END IF;
1909
1910 IF p_x_mr_effectivity_detail_tbl.COUNT > 0 OR p_x_effty_ext_detail_tbl.COUNT > 0
1911 THEN
1912 AHL_FMP_EFFECTIVITY_DTL_PVT.process_effectivity_detail
1913 (
1914 p_api_version => 1.0,
1915 p_init_msg_list => FND_API.G_FALSE,
1916 p_commit => FND_API.G_FALSE,
1917 p_validation_level => p_validation_level,
1918 p_default => p_default,
1919 p_module_type => p_module_type,
1920 x_return_status => x_return_status,
1921 x_msg_count => x_msg_count,
1922 x_msg_data => x_msg_data,
1923 p_x_effectivity_detail_tbl => p_x_mr_effectivity_detail_tbl,
1924 p_x_effty_ext_detail_tbl => p_x_effty_ext_detail_tbl,
1925 p_mr_header_id => l_mr_header_id,
1926 p_mr_effectivity_id => l_mr_effectivity_id
1927 );
1928
1929 x_msg_count := FND_MSG_PUB.count_msg;
1930 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
1931 THEN
1932 RAISE FND_API.G_EXC_ERROR;
1933 END IF;
1934 END IF;
1935
1936 IF (p_x_mr_interval_tbl.COUNT > 0 OR
1937 (p_x_mr_threshold_rec.threshold_date IS NOT NULL AND p_x_mr_threshold_rec.threshold_date <> FND_API.G_MISS_DATE) OR
1938 (p_x_mr_threshold_rec.program_duration IS NOT NULL AND p_x_mr_threshold_rec.program_duration <> FND_API.G_MISS_NUM ))
1939 THEN
1940 AHL_FMP_MR_INTERVAL_PVT.process_interval
1941 (
1942 p_api_version => 1.0,
1943 p_init_msg_list => FND_API.G_FALSE,
1944 p_commit => FND_API.G_FALSE,
1945 p_validation_level => p_validation_level,
1946 p_default => p_default,
1947 p_module_type => p_module_type,
1948 x_return_status => x_return_status,
1949 x_msg_count => x_msg_count,
1950 x_msg_data => x_msg_data,
1951 p_x_threshold_rec => p_x_mr_threshold_rec,
1952 p_x_interval_tbl => p_x_mr_interval_tbl,
1953 p_mr_header_id => l_mr_header_id,
1954 p_super_user => p_super_user
1955 );
1956 END IF;
1957
1958 x_msg_count := FND_MSG_PUB.count_msg;
1959 IF x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS
1960 THEN
1961 RAISE FND_API.G_EXC_ERROR;
1962 END IF;
1963
1964 -- API body ends here
1965
1966 -- Log API exit point
1967 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1968 THEN
1969 fnd_log.string
1970 (
1971 fnd_log.level_procedure,
1972 L_DEBUG_MODULE||'.end',
1973 'At the end of PLSQL procedure'
1974 );
1975 END IF;
1976
1977 -- Check Error Message stack.
1978 x_msg_count := FND_MSG_PUB.count_msg;
1979 IF x_msg_count > 0
1980 THEN
1981 RAISE FND_API.G_EXC_ERROR;
1982 END IF;
1983
1984 -- Commit, depending on p_commit flag
1985 IF FND_API.TO_BOOLEAN(p_commit) THEN
1986 COMMIT WORK;
1987 END IF;
1988
1989 -- Standard call to get message count and if count is 1, get message info
1990 FND_MSG_PUB.count_and_get
1991 (
1992 p_count => x_msg_count,
1993 p_data => x_msg_data,
1994 p_encoded => FND_API.G_FALSE
1995 );
1996
1997 EXCEPTION
1998 WHEN FND_API.G_EXC_ERROR THEN
1999 x_return_status := FND_API.G_RET_STS_ERROR;
2000 Rollback to Process_Mr_Effectivities_SP;
2001 FND_MSG_PUB.count_and_get
2002 (
2003 p_count => x_msg_count,
2004 p_data => x_msg_data,
2005 p_encoded => FND_API.G_FALSE
2006 );
2007
2008 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2009 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2010 Rollback to Process_Mr_Effectivities_SP;
2011 FND_MSG_PUB.count_and_get
2012 (
2013 p_count => x_msg_count,
2014 p_data => x_msg_data,
2015 p_encoded => FND_API.G_FALSE
2016 );
2017
2018 WHEN OTHERS THEN
2019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2020 Rollback to Process_Mr_Effectivities_SP;
2021 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2022 THEN
2023 FND_MSG_PUB.add_exc_msg
2024 (
2025 p_pkg_name => G_PKG_NAME,
2026 p_procedure_name => 'Process_Mr_Effectivities',
2027 p_error_text => SUBSTR(SQLERRM,1,240)
2028 );
2029 END IF;
2030 FND_MSG_PUB.count_and_get
2031 (
2032 p_count => x_msg_count,
2033 p_data => x_msg_data,
2034 p_encoded => FND_API.G_FALSE
2035 );
2036
2037 END Process_Mr_Effectivities;
2038
2039 END AHL_FMP_MR_PUB;