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