[Home] [Help]
PACKAGE BODY: APPS.AHL_ENIGMA_ROUTE_OP_PUB
Source
1 PACKAGE BODY AHL_ENIGMA_ROUTE_OP_PUB AS
2 /* $Header: AHLPEROB.pls 120.0.12010000.3 2008/12/14 09:17:20 bachandr noship $ */
3 ------------------------------------
4 -- Common constants and variables --
5 ------------------------------------
6 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
7 l_log_statement NUMBER := fnd_log.level_statement;
8 l_log_procedure NUMBER := fnd_log.level_procedure;
9 l_log_error NUMBER := fnd_log.level_error;
10 l_log_unexpected NUMBER := fnd_log.level_unexpected;
11 G_PKG_NAME VARCHAR2(30) := 'AHL_ENIGMA_ROUTE_OP_PUB';
12
13 -----------------------------------
14 -- Process Routes Details --
15 ------------------------------------
16 PROCEDURE Process_Route_Details
17 (
18 p_api_version IN NUMBER := '1.0',
19 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
20 p_commit IN VARCHAR2 := FND_API.G_FALSE,
21 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
22 p_default IN VARCHAR2 := FND_API.G_FALSE,
23 p_module_type IN VARCHAR2 := NULL,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_count OUT NOCOPY NUMBER,
26 x_msg_data OUT NOCOPY VARCHAR2,
27 p_enigma_route_rec IN enigma_route_rec_type,
28 p_enigma_op_tbl IN enigma_op_tbl_type,
29 p_context IN VARCHAR2,
30 p_pub_date IN DATE
31 );
32
33 -----------------------------------
34 -- Process Operations Details--
35 ------------------------------------
36 PROCEDURE Process_OP_Details
37 (
38 p_api_version IN NUMBER := '1.0',
39 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
40 p_commit IN VARCHAR2 := FND_API.G_FALSE,
41 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
42 p_default IN VARCHAR2 := FND_API.G_FALSE,
43 p_module_type IN VARCHAR2 := NULL,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2,
47 p_enigma_op_tbl IN enigma_op_tbl_type,
48 p_context IN VARCHAR2,
49 p_pub_date IN DATE,
50 p_parent_route_id IN VARCHAR2
51 );
52
53 PROCEDURE upload_revision_report(
54 p_file_name IN VARCHAR2,
55 x_file_id OUT NOCOPY NUMBER,
56 x_return_status OUT NOCOPY VARCHAR2
57 );
58
59
60 ------------------------------------------------------------------------------------------------------------------
61 -- Start of Comments --
62 -- Procedure name : Process_Route_Operations
63 -- Type : Public
64 -- Function : Processes the Routes and operations from Enigma.
65 -- Pre-reqs :
66 -- End of Comments.
67 ------------------------------------------------------------------------------------------------------------------
68 PROCEDURE Process_Route_Operations
69 (
70 p_api_version IN NUMBER := 1.0,
71 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
72 p_commit IN VARCHAR2 := FND_API.G_FALSE,
73 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
74 p_module_type IN VARCHAR2,
75 p_context IN VARCHAR2,
76 p_pub_date IN DATE,
77 p_enigma_route_rec IN enigma_route_rec_type,
78 p_enigma_op_tbl IN enigma_op_tbl_type,
79 x_return_status OUT NOCOPY VARCHAR2,
80 x_msg_count OUT NOCOPY NUMBER,
81 x_msg_data OUT NOCOPY VARCHAR2
82 )
83 IS
84
85
86 /*-- This cursor checks the existence of the Enigma Route in the staging table with status as pending.
87 CURSOR check_route_exists (p_route_id IN VARCHAR2)
88 IS
89 SELECT 'X'
90 FROM AHL_RT_OPER_INTERFACE
91 WHERE ROUTE_ID = p_route_id
92 AND STATUS = 'PENDING';*/
93
94 -- This cursor is used to fetch the details of the routes that exists in the staging table with status PENDING
95 CURSOR get_route_data
96 IS
97 SELECT
98 ROUTE_ID,
99 STATUS,
100 ATA_CODE,
101 DESCRIPTION,
102 REVISION_DATE,
103 ENIGMA_ID,
104 CHANGE_FLAG,
105 PDF
106 FROM
107 AHL_RT_OPER_INTERFACE
108 WHERE
109 STATUS = 'PENDING'
110 AND PARENT_ROUTE_ID IS NULL;
111
112 -- This cursor is used to fetch the details of the operartion that exists in the staging table
113 -- which is assocaited to a the route and with status PENDING
114 CURSOR get_oper_data (p_route_id IN VARCHAR2)
115 IS
116 SELECT
117 OPERATION_ID,
118 STATUS,
119 ATA_CODE,
120 DESCRIPTION,
121 PARENT_ROUTE_ID,
122 ENIGMA_ID,
123 CHANGE_FLAG
124 FROM
125 AHL_RT_OPER_INTERFACE
126 WHERE PARENT_ROUTE_ID = p_route_id
127 AND STATUS = 'PENDING';
128
129 -- Declare local variables
130 l_api_name CONSTANT VARCHAR2(30) := 'Process_Route_Operations';
131 l_api_version CONSTANT NUMBER := 1.0;
132 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||l_api_name;
133
134 l_dummy VARCHAR2(1);
135 l_route_exists VARCHAR2(1) := FND_API.G_FALSE;
136 l_route_id VARCHAR2(2000);
137
138
139 l_route_data_rec get_route_data%ROWTYPE;
140 l_oper_data_tbl enigma_op_tbl_type;
141
142
143 BEGIN
144 -- Standard start of API savepoint
145 SAVEPOINT Process_Route_Operations_SP;
146
147 -- Initialize return status to success before any code logic/validation
148 x_return_status:= FND_API.G_RET_STS_SUCCESS;
149
150 -- Standard call to check for call compatibility
151 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
152 THEN
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 END IF;
155
156 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
157 IF FND_API.TO_BOOLEAN(p_init_msg_list)
158 THEN
159 FND_MSG_PUB.INITIALIZE;
160 END IF;
161
162 -- initialise it only if it is a OA Adapter !
163 fnd_global.APPS_INITIALIZE (1003259,62211, 867);
164
165 -- Log API entry point
166 IF (l_log_procedure >= l_log_current_level) THEN
167 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.begin','At the start of PL SQL procedure ');
168 END IF;
169
170 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
171 THEN
172 fnd_log.string
173 (
174 fnd_log.level_procedure,
175 l_debug_module ||'.begin',
176 'Inside Process_Route_Operations'
177 );
178 END IF;
179
180 -- Check if the route is found in the staging table
181 -- Then , query and get all the routes from the staging table which are in status pending.
182 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
183 THEN
184 fnd_log.string
185 (
186 fnd_log.level_statement,
187 l_debug_module,
188 'Polling for pending routes'
189 );
190 END IF;
191
192 OPEN get_route_data;
193 LOOP
194 FETCH get_route_data INTO l_route_data_rec;
195 EXIT WHEN get_route_data%NOTFOUND;
196
197 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
198 THEN
199 fnd_log.string
200 (
201 fnd_log.level_statement,
202 l_debug_module,
203 'Pending Routes foung in staging table'
204 );
205 END IF;
206
207 l_route_id := l_route_data_rec.route_id;
208
209 -- Check if the route has any associated operations.
210 -- If so collect them into l_oper_data_tbl .
211 OPEN get_oper_data (l_route_id);
212 FETCH get_oper_data INTO l_oper_data_tbl(0);
213
214 IF (l_log_statement >= l_log_current_level) THEN
215 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling PROCESS_ROUTE_DETAILS');
216 END IF;
217
218 -- Delete all the routes in pending status from the stagin table
219 -- as they have already been queried by the cursor
220 DELETE FROM AHL_RT_OPER_INTERFACE
221 WHERE STATUS = 'PENDING' AND PARENT_ROUTE_ID IS NULL;
222
223 -- Delete all the operations from the stagin table that are in the pending status
224 -- and that correspond to the parent route
225
226 DELETE FROM AHL_RT_OPER_INTERFACE
227 WHERE PARENT_ROUTE_ID = l_route_id AND STATUS = 'PENDING';
228
229 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
230 THEN
231 fnd_log.string
232 (
233 fnd_log.level_statement,
234 l_debug_module,
235 'before calling process_route_details'
236 );
237 fnd_log.string
238 (
239 fnd_log.level_statement,
240 l_debug_module,
241 'l_route_data_rec.route_id -> '|| l_route_data_rec.route_id
242 );
243 END IF;
244
245 -- Calling the procedure to process the route and operation details
246 PROCESS_ROUTE_DETAILS
247 (
248 '1.0',
249 FND_API.G_TRUE,
250 FND_API.G_FALSE,
251 FND_API.G_VALID_LEVEL_FULL,
252 FND_API.G_FALSE,
253 NULL,
254 x_return_status,
255 x_msg_count,
256 x_msg_data,
257 l_route_data_rec,
258 l_oper_data_tbl,
259 p_context,
260 p_pub_date
261 );
262
263 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
264 THEN
265 fnd_log.string
266 (
267 fnd_log.level_statement,
268 l_debug_module,
269 'after calling process_route_details'
270 );
271 END IF;
272 CLOSE get_oper_data;
273 END LOOP;
274 CLOSE get_route_data;
275
276 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
277 THEN
278 fnd_log.string
279 (
280 fnd_log.level_statement,
281 l_debug_module,
282 'Dumping all the paramters...'
283 );
284 fnd_log.string
285 (
286 fnd_log.level_statement,
287 l_debug_module,
288 'p_api_version -> '||p_api_version
289 );
290 fnd_log.string
291 (
292 fnd_log.level_statement,
293 l_debug_module,
294 'p_init_msg_list -> '||p_init_msg_list
295 );
296 fnd_log.string
297 (
298 fnd_log.level_statement,
299 l_debug_module,
300 'p_commit -> '||p_commit
301 );
302 fnd_log.string
303 (
304 fnd_log.level_statement,
305 l_debug_module,
306 'p_validation_level -> '||p_validation_level
307 );
308 fnd_log.string
309 (
310 fnd_log.level_statement,
311 l_debug_module,
312 'p_module_type -> '||p_module_type
313 );
314 fnd_log.string
315 (
316 fnd_log.level_statement,
317 l_debug_module,
318 'p_context -> '||p_context
319 );
320 fnd_log.string
321 (
322 fnd_log.level_statement,
323 l_debug_module,
324 'p_pub_date -> '||p_pub_date
325 );
326 END IF;
327
328 -- Process the incoming enigma record.
329 -- Validate if the Enigma Route Id is null throw an error if the id is null.
330 IF (p_enigma_route_rec.route_ID IS NULL OR p_enigma_route_rec.ROUTE_ID = FND_API.G_MISS_CHAR)
331 THEN
332 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_ID_NULL');
333 FND_MSG_PUB.ADD;
334 RAISE FND_API.G_EXC_ERROR;
335 END IF;
336
337 -- Calling the procedure to process the route and operation details passing the input record
338 -- p_enigma_route_rec, irrespective of whether the route is in the staging table or not.
339 -- Call the procedure with the passed inout records only if the same record is not found in
340 -- staging table and the operation is not delete ?!?!
341 PROCESS_ROUTE_DETAILS
342 (
343 '1.0',
344 FND_API.G_TRUE,
345 FND_API.G_FALSE,
346 FND_API.G_VALID_LEVEL_FULL,
347 FND_API.G_FALSE,
348 NULL,
349 x_return_status,
350 x_msg_count,
351 x_msg_data,
352 p_enigma_route_rec,
353 p_enigma_op_tbl,
354 p_context,
355 p_pub_date
356 );
357
358
359 -- Check Error Message stack.
360 x_msg_count := FND_MSG_PUB.count_msg;
361 IF x_msg_count > 0
362 THEN
363 RAISE FND_API.G_EXC_ERROR;
364 END IF;
365
366 -- Standard check for p_commit
367 IF FND_API.To_Boolean (p_commit)
368 THEN
369 COMMIT WORK;
370 END IF;
371
372 -- Standard call to get message count and if count is 1, get message info
373 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
374 p_data => x_msg_data,
375 p_encoded => fnd_api.g_false );
376
377 EXCEPTION
378 WHEN FND_API.G_EXC_ERROR THEN
379 x_return_status := FND_API.G_RET_STS_ERROR;
380 Rollback to Process_Route_Operations_SP;
384
381 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
382 p_data => x_msg_data,
383 p_encoded => fnd_api.g_false);
385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 Rollback to Process_Route_Operations_SP;
388 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
389 p_data => x_msg_data,
390 p_encoded => fnd_api.g_false);
391
392 WHEN OTHERS THEN
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 Rollback to Process_Route_Operations_SP;
395 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
396 THEN
397 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
398 p_procedure_name => 'Process_Route_Operations',
399 p_error_text => SUBSTR(SQLERRM,1,240));
400 END IF;
401 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
402 p_data => x_msg_data,
403 p_encoded => fnd_api.g_false);
404
405 END Process_Route_Operations;
406
407
408 PROCEDURE Process_Route_Details
409 (
410 p_api_version IN NUMBER := '1.0',
411 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
412 p_commit IN VARCHAR2 := FND_API.G_FALSE,
413 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
414 p_default IN VARCHAR2 := FND_API.G_FALSE,
415 p_module_type IN VARCHAR2 := NULL,
416 x_return_status OUT NOCOPY VARCHAR2,
417 x_msg_count OUT NOCOPY NUMBER,
418 x_msg_data OUT NOCOPY VARCHAR2,
419 p_enigma_route_rec IN enigma_route_rec_type,
420 p_enigma_op_tbl IN enigma_op_tbl_type,
421 p_context IN VARCHAR2,
422 p_pub_date IN DATE
423 )
424 IS
425
426 CURSOR get_latest_route_rev (p_route_id VARCHAR2)
427 IS
428 SELECT
429 route_id,
430 object_version_number,
431 revision_status_code revision_status
432 FROM AHL_ROUTES_V
433 WHERE UPPER(TRIM(ENIGMA_ROUTE_ID)) = UPPER(TRIM(p_route_id))
434 AND REVISION_NUMBER =
435 ( SELECT
436 MAX( revision_number )
437 FROM
438 AHL_ROUTES_V
439 WHERE
440 UPPER(TRIM(ENIGMA_ROUTE_ID)) = UPPER(TRIM(p_route_id)) );
441
442 -- Declare local variables
443 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ROUTE_DETAILS';
444 l_api_version CONSTANT NUMBER := 1.0;
445 l_debug_module CONSTANT VARCHAR2(100) := 'AHL.PLSQL.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||'PROCESS_ROUTE_DETAILS';
446
447 x_route_id NUMBER;
448 l_ovn NUMBER;
449 x_file_id NUMBER;
450
451 l_get_latest_route_rev get_latest_route_rev%ROWTYPE;
452 p_process_route_input_rec AHL_RM_ROUTE_PVT.route_rec_type;
453
454 BEGIN
455 -- Standard start of API savepoint
456 SAVEPOINT Process_Route_Details_SP;
457
458 -- Initialize return status to success before any code logic/validation
459 x_return_status:= FND_API.G_RET_STS_SUCCESS;
460
461 -- Standard call to check for call compatibility
462 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
463 THEN
464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
465 END IF;
466
467 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
468 IF FND_API.TO_BOOLEAN(p_init_msg_list)
469 THEN
470 FND_MSG_PUB.INITIALIZE;
471 END IF;
472
473 -- Log API entry point
474 IF (l_log_procedure >= l_log_current_level) THEN
475 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.begin','At the start of PL SQL procedure ');
476 END IF;
477
478 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
479
480 fnd_log.string
481 (
482 fnd_log.level_procedure,
483 l_debug_module ||'.begin',
484 'Inside process_route_details'
485 );
486 END IF;
487
488 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
489 THEN
490 fnd_log.string
491 (
492 fnd_log.level_statement,
493 l_debug_module,
494 'Calling from BPEL service Parameters p_enigma_route_rec.route_id -> '||p_enigma_route_rec.route_id
495 );
496 fnd_log.string
497 (
498 fnd_log.level_statement,
499 l_debug_module,
500 'Calling from BPEL service Parameters p_enigma_route_rec.change_flag -> '||p_enigma_route_rec.change_flag
501 );
502 fnd_log.string
503 (
504 fnd_log.level_statement,
505 l_debug_module,
506 'Calling from BPEL service Parameters p_enigma_route_rec.ATA_CODE-> '||p_enigma_route_rec.ATA_CODE
507 );
508 fnd_log.string
509 (
510 fnd_log.level_statement,
511 l_debug_module,
512 'Calling from BPEL service Parameters p_enigma_route_rec.DESCRIPTION-> '||p_enigma_route_rec.DESCRIPTION
513 );
514 fnd_log.string
515 (
516 fnd_log.level_statement,
517 l_debug_module,
518 'Calling from BPEL service Parameters p_enigma_route_rec.REVISION_DATE-> '||p_enigma_route_rec.REVISION_DATE
519 );
520 fnd_log.string
521 (
522 fnd_log.level_statement,
523 l_debug_module,
524 'Calling from BPEL service Parameters p_enigma_route_rec.PDF-> '||p_enigma_route_rec.PDF
525 );
526 END IF;
527 -- Start of validations
528 -- validate if all the mandatory parameters are passed for the route rec
529
530 -- Verify if the Change flag is right...
531 IF (p_enigma_route_rec.change_flag NOT IN ( 'C', 'D', 'U'))
532 THEN
533 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_DML');
537 RAISE FND_API.G_EXC_ERROR;
534 FND_MESSAGE.SET_TOKEN('FIELD', p_enigma_route_rec.change_flag);
535 FND_MESSAGE.SET_TOKEN('RECORD', p_enigma_route_rec.route_id);
536 FND_MSG_PUB.ADD;
538 END IF;
539
540 -- When all clear, process the route rec by checking the change_flag (DML Operation).
541 -- Check if the flag id delete and if so delete the route from the CMRO system .
542 IF (p_enigma_route_rec.change_flag = 'D') THEN
543
544 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
545 THEN
546 fnd_log.string
547 (
548 fnd_log.level_statement,
549 l_debug_module,
550 'Inside Delete'
551 );
552 END IF;
553
554 -- Query for the latest revision of the route existing in CMRO end
555 OPEN get_latest_route_rev (p_enigma_route_rec.route_id);
556 FETCH get_latest_route_rev
557 INTO l_get_latest_route_rev.route_id,
558 l_get_latest_route_rev.object_version_number,
559 l_get_latest_route_rev.revision_status;
560
561 IF get_latest_route_rev%FOUND THEN
562
563 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
564 THEN
565 fnd_log.string
566 (
567 fnd_log.level_statement,
568 l_debug_module,
569 'Latest Revision of route found'
570 );
571 END IF;
572
573 -- IF the route is in Approval Pending status , then insert the Enigma Record into the
574 -- staging table with status as pending.
575 IF ( upper(l_get_latest_route_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
576
577 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
578 THEN
579 fnd_log.string
580 (
581 fnd_log.level_statement,
582 l_debug_module,
583 'Route is in Approval Pending'
584 );
585 fnd_log.string
586 (
587 fnd_log.level_statement,
588 l_debug_module,
589 'Before Inserting into the staging table'
590 );
591 END IF;
592
593 INSERT INTO AHL_RT_OPER_INTERFACE
594 (
595 CONTEXT,
596 PUBLISH_DATE,
597 ROUTE_ID,
598 PDF,
599 REVISION_DATE,
600 CHANGE_FLAG,
601 ATA_CODE,
602 DESCRIPTION,
603 STATUS,
604 REASON,
605 ENIGMA_ID
606 )
607 VALUES
608 (
609 p_context,
610 p_pub_date,
611 p_enigma_route_rec.route_id,
612 p_enigma_route_rec.pdf,
613 p_enigma_route_rec.revision_date,
614 p_enigma_route_rec.change_flag,
615 p_enigma_route_rec.ata_code,
616 p_enigma_route_rec.description,
617 'PENDING',
618 'Route is in approval pending status',
619 p_enigma_route_rec.enigma_id
620 );
621
622 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
623 THEN
624 fnd_log.string
625 (
626 fnd_log.level_statement,
627 l_debug_module,
628 'After Inserting into the staging table'
629 );
630 END IF;
631
632 ELSE
633 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
634 THEN
635 fnd_log.string
636 (
637 fnd_log.level_statement,
638 l_debug_module,
639 'Before calling delete_routes'
640 );
641 fnd_log.string
642 (
643 fnd_log.level_statement,
644 l_debug_module,
645 'l_get_latest_route_rev.route_id -> ' || l_get_latest_route_rev.route_id
646 );
647 fnd_log.string
648 (
649 fnd_log.level_statement,
650 l_debug_module,
651 'l_get_latest_route_rev.object_version_number -> ' || l_get_latest_route_rev.object_version_number
652 );
653 END IF;
654
655 -- Call delete_route procedure to delete the route from CMRO End.
656 AHL_RM_ROUTE_PVT.delete_route
657 (
658 '1.0',
659 FND_API.G_TRUE,
660 FND_API.G_FALSE,
661 FND_API.G_VALID_LEVEL_FULL,
662 FND_API.G_FALSE,
663 'ENIGMA',
664 x_return_status,
665 x_msg_count,
666 x_msg_data,
667 l_get_latest_route_rev.route_id,
668 l_get_latest_route_rev.object_version_number
669 );
670
671 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
672 THEN
673 fnd_log.string
674 (
675 fnd_log.level_statement,
676 l_debug_module,
677 'After calling delete_routes'
678 );
679 fnd_log.string
680 (
681 fnd_log.level_statement,
682 l_debug_module,
683 'x_return_status -> ' || x_return_status
684 );
685 END IF;
686
687 IF (l_log_statement >= l_log_current_level) THEN
688 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_ROUTE_PVT.create_route_revision');
689 END IF;
690
691 -- Check the return status , and if the status is not success , then raise an error
692 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
693 IF (l_log_statement >= l_log_current_level) THEN
694 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.delete_route Error');
695 END IF;
696 RAISE FND_API.G_EXC_ERROR;
697 END IF;
698
699 IF (l_log_statement >= l_log_current_level) THEN
700 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.delete_route -> Deleted');
701 END IF;
705 -- Raise an error if the route is not found in CMRO..
702
703 END IF;
704 ELSE
706 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_DONOT_EXIST');
707 FND_MSG_PUB.ADD;
708 CLOSE get_latest_route_rev;
709 RAISE FND_API.G_EXC_ERROR;
710 END IF; -- Cursor
711 CLOSE get_latest_route_rev;
712 END IF; -- change Flag
713
714 IF (p_enigma_route_rec.change_flag = 'U') THEN
715
716 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
717 THEN
718 fnd_log.string
719 (
720 fnd_log.level_statement,
721 l_debug_module,
722 'Inside update'
723 );
724 END IF;
725
726 -- Query for the latest revision of the route existing in CMRO end
727 OPEN get_latest_route_rev (p_enigma_route_rec.route_id);
728 FETCH get_latest_route_rev
729 INTO l_get_latest_route_rev.route_id,
730 l_get_latest_route_rev.object_version_number,
731 l_get_latest_route_rev.revision_status;
732
733 IF get_latest_route_rev%FOUND THEN
734 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
735 THEN
736 fnd_log.string
737 (
738 fnd_log.level_statement,
739 l_debug_module,
740 'latest revision of route Found'
741 );
742 fnd_log.string
743 (
744 fnd_log.level_statement,
745 l_debug_module,
746 'l_get_latest_route_rev.route_id -> ' || l_get_latest_route_rev.route_id
747 );
748 fnd_log.string
749 (
750 fnd_log.level_statement,
751 l_debug_module,
752 'l_get_latest_route_rev.revision_status_code-> ' || l_get_latest_route_rev.revision_status
753 );
754 END IF;
755
756 -- IF the route is in Approval Pending status , then inser the Enigma Record into the
757 -- staging table with status as pending.
758 IF ( upper(l_get_latest_route_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
759
760 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
761 THEN
762 fnd_log.string
763 (
764 fnd_log.level_statement,
765 l_debug_module,
766 'Route is in Approval pending'
767 );
768 END IF;
769
770 INSERT INTO AHL_RT_OPER_INTERFACE
771 (
772 CONTEXT,
773 PUBLISH_DATE,
774 ROUTE_ID,
775 PDF,
776 REVISION_DATE,
777 CHANGE_FLAG,
778 ATA_CODE,
779 DESCRIPTION,
780 STATUS,
781 REASON,
782 ENIGMA_ID
783 )
784 VALUES
785 (
786 p_context,
787 p_pub_date,
788 p_enigma_route_rec.route_id,
789 p_enigma_route_rec.pdf,
790 p_enigma_route_rec.revision_date,
791 p_enigma_route_rec.change_flag,
792 p_enigma_route_rec.ata_code,
793 p_enigma_route_rec.description,
794 'PENDING',
795 'Route is in approval pending status',
796 p_enigma_route_rec.enigma_id
797 );
798
799 -- If the route has any assocaited operations, add them also to the stating table
800 -- marking the status as pending.
801 IF ( p_enigma_op_tbl.COUNT > 0) THEN
802
803 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
804 THEN
805 fnd_log.string
806 (
807 fnd_log.level_statement,
808 l_debug_module,
809 'There are operations attached'
810 );
811 END IF;
812
813 FOR i IN p_enigma_op_tbl.FIRST..p_enigma_op_tbl.LAST
814 LOOP
815 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
816 THEN
817 fnd_log.string
818 (
819 fnd_log.level_statement,
820 l_debug_module,
821 'Inserting the operations into the staging table'
822 );
823 END IF;
824
825 INSERT INTO AHL_RT_OPER_INTERFACE
826 (
827 CONTEXT,
828 PUBLISH_DATE,
829 OPERATION_ID,
830 PARENT_ROUTE_ID,
831 CHANGE_FLAG,
832 ATA_CODE,
833 DESCRIPTION,
834 STATUS,
835 REASON,
836 ENIGMA_ID
837 )
838 VALUES
839 (
840 p_context,
841 p_pub_date,
842 p_enigma_op_tbl(i).operation_id,
843 p_enigma_op_tbl(i).parent_route_id,
844 p_enigma_op_tbl(i).change_flag,
845 p_enigma_op_tbl(i).ata_code,
846 p_enigma_op_tbl(i).description,
847 'PENDING',
848 'Parent Route is in approval pending status',
849 p_enigma_op_tbl(i).enigma_id
850 );
851 END LOOP;
852 END IF;
853
854 ELSIF (upper(l_get_latest_route_rev.revision_status) = 'COMPLETE') THEN
855
856 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
857 THEN
858 fnd_log.string
859 (
860 fnd_log.level_statement,
861 l_debug_module,
862 'Route is in complete'
863 );
864 fnd_log.string
865 (
866 fnd_log.level_statement,
867 l_debug_module,
868 'Before calling create_route_revision'
869 );
870 fnd_log.string
871 (
872 fnd_log.level_statement,
873 l_debug_module,
877 (
874 'l_get_latest_route_rev.route_id -> ' || l_get_latest_route_rev.route_id
875 );
876 fnd_log.string
878 fnd_log.level_statement,
879 l_debug_module,
880 'l_get_latest_route_rev.object_version_number -> ' || l_get_latest_route_rev.object_version_number
881 );
882 END IF;
883
884 -- Call the API to create a new revision of the route
885 AHL_RM_ROUTE_PVT.create_route_revision
886 (
887 '1.0',
888 FND_API.G_TRUE,
889 FND_API.G_FALSE,
890 FND_API.G_VALID_LEVEL_FULL,
891 FND_API.G_FALSE,
892 NULL,
893 x_return_status,
894 x_msg_count,
895 x_msg_data,
896 l_get_latest_route_rev.route_id,
897 l_get_latest_route_rev.object_version_number,
898 x_route_id
899 );
900
901 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
902 THEN
903
904 fnd_log.string
905 (
906 fnd_log.level_statement,
907 l_debug_module,
908 'After calling create_route_revision'
909 );
910 fnd_log.string
911 (
912 fnd_log.level_statement,
913 l_debug_module,
914 'x_route_id -> ' || x_route_id
915 );
916 END IF;
917
918 IF (l_log_statement >= l_log_current_level) THEN
919 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_ROUTE_PVT.create_route_revision');
920 END IF;
921
922 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
923 IF (l_log_statement >= l_log_current_level) THEN
924 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.create_route_revision Error');
925 END IF;
926 RAISE FND_API.G_EXC_ERROR;
927 END IF;
928
929 IF (l_log_statement >= l_log_current_level) THEN
930 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.create_route_revision -> revision Created');
931 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.create_route_revision -> x_route_id = ' || x_route_id);
932 END IF;
933
934 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
935 THEN
936
937 fnd_log.string
938 (
939 fnd_log.level_statement,
940 l_debug_module,
941 'Populate the input record for updation'
942 );
943 END IF;
944
945 -- If the return status is success, populate the input rec for process_route for updation
946 IF ( x_route_id <> FND_API.G_MISS_NUM AND x_route_id IS NOT NULL) THEN
947 p_process_route_input_rec.ROUTE_ID := x_route_id ;
948 END IF;
949
950 IF p_enigma_route_rec.pdf IS NOT NULL
951 THEN
952 -- Call the procedure to upload the file
953 UPLOAD_REVISION_REPORT
954 (
955 p_enigma_route_rec.pdf,
956 x_file_id,
957 x_return_status
958 );
959 END IF;
960
961 IF (l_log_statement >= l_log_current_level) THEN
962 fnd_log.string(fnd_log.level_statement,l_debug_module,'After UPLOAD_REVISION_REPORT Call ');
963 END IF;
964
965 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
966 IF (l_log_statement >= l_log_current_level) THEN
967 fnd_log.string(fnd_log.level_statement,l_debug_module,'UPLOAD_REVISION_REPORT Error');
968 END IF;
969 RAISE FND_API.G_EXC_ERROR;
970 END IF;
971
972 IF (l_log_statement >= l_log_current_level) THEN
973 fnd_log.string(fnd_log.level_statement,l_debug_module,'UPLOAD_REVISION_REPORT-> File upload done');
974 fnd_log.string(fnd_log.level_statement,l_debug_module,'File Id -> = ' || x_file_id);
975 END IF;
976
977 -- If the return status is success, populate the input rec for process_route for updation
978 IF ( x_file_id <> FND_API.G_MISS_NUM AND x_file_id IS NOT NULL) THEN
979
980 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
981 THEN
982
983 fnd_log.string
984 (
985 fnd_log.level_statement,
986 l_debug_module,
987 'File id ->' || x_file_id
988 );
989 END IF;
990
991 p_process_route_input_rec.FILE_ID := x_file_id ;
992 -- Update the route revision with file_id
993 /*
994 UPDATE
995 AHL_ROUTES_B
996 SET
997 FILE_ID = x_file_id,
998 OBJECT_VERSION_NUMBER = l_get_latest_route_rev.object_version_number + 1
999 WHERE
1000 ROUTE_ID = l_get_latest_route_rev.route_id
1001 AND OBJECT_VERSION_NUMBER = l_get_latest_route_rev.object_version_number;
1002 */
1003 END IF;
1004
1005 SELECT object_version_number INTO l_ovn from
1006 AHL_ROUTES_V where route_id = x_route_id;
1007
1008 IF (l_ovn <> FND_API.G_MISS_NUM AND l_ovn IS NOT NULL ) THEN
1009 p_process_route_input_rec.object_version_number := l_ovn;
1010 END IF;
1011
1012 IF (p_context <> FND_API.G_MISS_CHAR AND p_context IS NOT NULL ) THEN
1013 p_process_route_input_rec.model_code := p_context;
1014 END IF;
1015
1016 IF (p_pub_date <> FND_API.G_MISS_DATE AND p_pub_date IS NOT NULL ) THEN
1017 p_process_route_input_rec.enigma_publish_date := p_pub_date ;
1018 END IF;
1019 /*
1023 END IF;
1020 Route No is not updatable
1021 IF (p_enigma_route_rec.ata_code <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.ata_code IS NOT NULL ) THEN
1022 p_process_route_input_rec.route_no := p_enigma_route_rec.ata_code;
1024 */
1025
1026 IF (p_enigma_route_rec.description <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.description IS NOT NULL ) THEN
1027 p_process_route_input_rec.TITLE := p_enigma_route_rec.description;
1028 END IF;
1029
1030 IF (p_enigma_route_rec.revision_date <> FND_API.G_MISS_DATE AND p_enigma_route_rec.revision_date IS NOT NULL ) THEN
1031 p_process_route_input_rec.ACTIVE_START_DATE := p_enigma_route_rec.revision_date;
1032 END IF;
1033
1034 IF (p_enigma_route_rec.enigma_id <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.enigma_id IS NOT NULL ) THEN
1035 p_process_route_input_rec.enigma_doc_id := p_enigma_route_rec.enigma_id;
1036 END IF;
1037
1038 IF (p_enigma_route_rec.route_id <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.route_id IS NOT NULL ) THEN
1039 p_process_route_input_rec.enigma_route_id := p_enigma_route_rec.route_id;
1040 END IF;
1041
1042 p_process_route_input_rec.dml_operation := 'U';
1043
1044 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1045 THEN
1046
1047 fnd_log.string
1048 (
1049 fnd_log.level_statement,
1050 l_debug_module,
1051 'Before calling process_route in update mode'
1052 );
1053 END IF;
1054
1055 -- Call the API for update
1056 AHL_RM_ROUTE_PVT.process_route
1057 (
1058 '1.0',
1059 FND_API.G_TRUE,
1060 FND_API.G_FALSE,
1061 FND_API.G_VALID_LEVEL_FULL,
1062 FND_API.G_FALSE,
1063 NULL,
1064 x_return_status,
1065 x_msg_count,
1066 x_msg_data,
1067 p_process_route_input_rec
1068 );
1069
1070 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1071 THEN
1072
1073 fnd_log.string
1074 (
1075 fnd_log.level_statement,
1076 l_debug_module,
1077 'After calling process_route in update mode'
1078 );
1079 fnd_log.string
1080 (
1081 fnd_log.level_statement,
1082 l_debug_module,
1083 'x_return_status -> ' || x_return_status
1084 );
1085 END IF;
1086
1087 IF (l_log_statement >= l_log_current_level) THEN
1088 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_ROUTE_PVT.process_route');
1089 END IF;
1090
1091 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1092 IF (l_log_statement >= l_log_current_level) THEN
1093 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.process_route Error');
1094 END IF;
1095 RAISE FND_API.G_EXC_ERROR;
1096 END IF;
1097
1098 IF (l_log_statement >= l_log_current_level) THEN
1099 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.process_route -> updation Successful');
1100 END IF;
1101
1102 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1103 THEN
1104
1105 fnd_log.string
1106 (
1107 fnd_log.level_statement,
1108 l_debug_module,
1109 'before insertion into staging table'
1110 );
1111 END IF;
1112
1113 -- Insert the transaction record into the staging table, with status as success
1114 INSERT INTO AHL_RT_OPER_INTERFACE
1115 (
1116 CONTEXT,
1117 PUBLISH_DATE,
1118 ROUTE_ID,
1119 PDF,
1120 REVISION_DATE,
1121 CHANGE_FLAG,
1122 ATA_CODE,
1123 DESCRIPTION,
1124 STATUS,
1125 REASON,
1126 ENIGMA_ID
1127 )
1128 VALUES
1129 (
1130 p_context,
1131 p_pub_date,
1132 p_enigma_route_rec.route_id,
1133 p_enigma_route_rec.pdf,
1134 p_enigma_route_rec.revision_date,
1135 p_enigma_route_rec.change_flag,
1136 p_enigma_route_rec.ata_code,
1137 p_enigma_route_rec.description,
1138 'SUCCESS',
1139 'Route updated Successfully',
1140 p_enigma_route_rec.enigma_id
1141 );
1142
1143 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1144 THEN
1145
1146 fnd_log.string
1147 (
1148 fnd_log.level_statement,
1149 l_debug_module,
1150 'After insertion into staging table'
1151 );
1152 END IF;
1153
1154 -- Check if the route has any operations, if then call the process operation procedure
1155 IF ( p_enigma_op_tbl.COUNT > 0) THEN
1156 FOR i IN p_enigma_op_tbl.FIRST..p_enigma_op_tbl.LAST
1157 LOOP
1158
1159 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1160 THEN
1161
1162 fnd_log.string
1163 (
1164 fnd_log.level_statement,
1165 l_debug_module,
1166 'Process the operations , Calling Process_OP_Details '
1167 );
1168 END IF;
1169
1170 Process_OP_Details
1171 (
1172 '1.0',
1173 FND_API.G_TRUE,
1174 FND_API.G_FALSE,
1175 FND_API.G_VALID_LEVEL_FULL,
1176 FND_API.G_FALSE,
1177 NULL,
1178 x_return_status,
1179 x_msg_count,
1180 x_msg_data,
1181 p_enigma_op_tbl,
1182 p_context,
1183 p_pub_date,
1187 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1184 p_enigma_route_rec.route_id
1185 );
1186
1188 THEN
1189
1190 fnd_log.string
1191 (
1192 fnd_log.level_statement,
1193 l_debug_module,
1194 'After Calling Process_OP_Details '
1195 );
1196 END IF;
1197
1198 IF (l_log_statement >= l_log_current_level) THEN
1199 fnd_log.string(fnd_log.level_statement,l_debug_module,'After Process_OP_Details');
1200 END IF;
1201
1202 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1203 IF (l_log_statement >= l_log_current_level) THEN
1204 fnd_log.string(fnd_log.level_statement,l_debug_module,'Process_OP_Details Error');
1205 END IF;
1206 RAISE FND_API.G_EXC_ERROR;
1207 END IF;
1208 END LOOP;
1209 END IF; -- oper table count
1210
1211 ELSIF ( l_get_latest_route_rev.revision_status = 'DRAFT'
1212 OR l_get_latest_route_rev.revision_status = 'APPROVAL_REJECTED' ) THEN
1213 -- Start of Validations for all the mandatory common attributes.
1214 -- Validate is publish_date is Null and If so throw an error
1215
1216 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1217 THEN
1218
1219 fnd_log.string
1220 (
1221 fnd_log.level_statement,
1222 l_debug_module,
1223 'Inside Draft'
1224 );
1225 END IF;
1226
1227 IF (p_pub_date IS NULL AND p_pub_date = FND_API.G_MISS_DATE)
1228 THEN
1229 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_PUB_DATE_NULL');
1230 FND_MSG_PUB.ADD;
1231 END IF;
1232
1233 -- Validate is context is Null and If so throw an error
1234 IF (p_context IS NULL AND p_context = FND_API.G_MISS_CHAR)
1235 THEN
1236 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_CONTEXT_NULL');
1237 FND_MSG_PUB.ADD;
1238 END IF;
1239
1240 -- Validate is Enigma_Id is Null and If so throw an error
1241 IF (p_enigma_route_rec.enigma_ID IS NULL AND p_enigma_route_rec.enigma_ID = FND_API.G_MISS_CHAR)
1242 THEN
1243 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ENIGMA_ID_NULL');
1244 FND_MSG_PUB.ADD;
1245 END IF;
1246
1247 -- Check the error stack and raise error messages , if any
1248 x_msg_count := FND_MSG_PUB.count_msg;
1249 IF x_msg_count > 0
1250 THEN
1251 RAISE FND_API.G_EXC_ERROR;
1252 END IF;
1253
1254 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1255 THEN
1256
1257 fnd_log.string
1258 (
1259 fnd_log.level_statement,
1260 l_debug_module,
1261 'populate the records'
1262 );
1263 END IF;
1264
1265 IF p_enigma_route_rec.pdf IS NOT NULL
1266 THEN
1267
1268 -- Call the procedure to upload the file
1269 UPLOAD_REVISION_REPORT
1270 (
1271 p_enigma_route_rec.pdf,
1272 x_file_id,
1273 x_return_status
1274 );
1275 END IF;
1276
1277 IF (l_log_statement >= l_log_current_level) THEN
1278 fnd_log.string(fnd_log.level_statement,l_debug_module,'After UPLOAD_REVISION_REPORT Call ');
1279 END IF;
1280
1281 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1282 IF (l_log_statement >= l_log_current_level) THEN
1283 fnd_log.string(fnd_log.level_statement,l_debug_module,'UPLOAD_REVISION_REPORT Error');
1284 END IF;
1285 RAISE FND_API.G_EXC_ERROR;
1286 END IF;
1287
1288 IF (l_log_statement >= l_log_current_level) THEN
1289 fnd_log.string(fnd_log.level_statement,l_debug_module,'UPLOAD_REVISION_REPORT-> File upload done');
1290 fnd_log.string(fnd_log.level_statement,l_debug_module,'File Id -> = ' || x_file_id);
1291 END IF;
1292
1293 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1294 THEN
1295
1296 fnd_log.string
1297 (
1298 fnd_log.level_statement,
1299 l_debug_module,
1300 'file upload done'
1301 );
1302 END IF;
1303
1304 -- If the return status is success, populate the input rec for process_route for updation
1305 IF ( x_file_id <> FND_API.G_MISS_NUM AND x_file_id IS NOT NULL) THEN
1306 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1307 THEN
1308
1309 fnd_log.string
1310 (
1311 fnd_log.level_statement,
1312 l_debug_module,
1313 'File id ->' || x_file_id
1314 );
1315 END IF;
1316 p_process_route_input_rec.FILE_ID := x_file_id ;
1317 END IF;
1318
1319 IF ( l_get_latest_route_rev.route_id <> FND_API.G_MISS_NUM AND l_get_latest_route_rev.route_id IS NOT NULL) THEN
1320 p_process_route_input_rec.ROUTE_ID := l_get_latest_route_rev.route_id;
1321 END IF;
1322
1323 IF (l_get_latest_route_rev.object_version_number <> FND_API.G_MISS_NUM AND l_get_latest_route_rev.object_version_number IS NOT NULL ) THEN
1324 p_process_route_input_rec.object_version_number := l_get_latest_route_rev.object_version_number;
1325 END IF;
1326
1327 IF (p_context <> FND_API.G_MISS_CHAR AND p_context IS NOT NULL ) THEN
1328 p_process_route_input_rec.model_code := p_context;
1329 END IF;
1330
1331 IF (p_pub_date <> FND_API.G_MISS_DATE AND p_pub_date IS NOT NULL ) THEN
1332 p_process_route_input_rec.enigma_publish_date := p_pub_date;
1333 END IF;
1334
1338 END IF;
1335 /* Route no is not updatable
1336 IF (p_enigma_route_rec.ata_code <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.ata_code IS NOT NULL ) THEN
1337 p_process_route_input_rec.route_no := p_enigma_route_rec.ata_code;
1339 */
1340 IF (p_enigma_route_rec.description <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.description IS NOT NULL ) THEN
1341 p_process_route_input_rec.title := p_enigma_route_rec.description;
1342 END IF;
1343
1344 IF (p_enigma_route_rec.revision_date <> FND_API.G_MISS_DATE AND p_enigma_route_rec.revision_date IS NOT NULL ) THEN
1345 p_process_route_input_rec.ACTIVE_START_DATE := p_enigma_route_rec.revision_date;
1346 END IF;
1347
1348 IF (p_enigma_route_rec.enigma_id <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.enigma_id IS NOT NULL ) THEN
1349 p_process_route_input_rec.enigma_doc_id := p_enigma_route_rec.enigma_id;
1350 END IF;
1351
1352 IF (p_enigma_route_rec.route_id <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.route_id IS NOT NULL ) THEN
1353 p_process_route_input_rec.enigma_route_id := p_enigma_route_rec.route_id;
1354 END IF;
1355
1356 p_process_route_input_rec.dml_operation := 'U';
1357
1358 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1359 THEN
1360
1361 fnd_log.string
1362 (
1363 fnd_log.level_statement,
1364 l_debug_module,
1365 'calling process_route'
1366 );
1367 END IF;
1368
1369 -- Call the API for update.
1370 AHL_RM_ROUTE_PVT.process_route
1371 (
1372 '1.0',
1373 FND_API.G_TRUE,
1374 FND_API.G_FALSE,
1375 FND_API.G_VALID_LEVEL_FULL,
1376 FND_API.G_FALSE,
1377 NULL,
1378 x_return_status,
1379 x_msg_count,
1380 x_msg_data,
1381 p_process_route_input_rec
1382 );
1383
1384 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1385 THEN
1386
1387 fnd_log.string
1388 (
1389 fnd_log.level_statement,
1390 l_debug_module,
1391 'after calling process_route'
1392 );
1393 END IF;
1394
1395 IF (l_log_statement >= l_log_current_level) THEN
1396 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_ROUTE_PVT.process_route');
1397 END IF;
1398
1399 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1400 IF (l_log_statement >= l_log_current_level) THEN
1401 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.process_route Error');
1402 END IF;
1403 RAISE FND_API.G_EXC_ERROR;
1404 END IF;
1405
1406 -- If the return status is success, then add the transaction to the stating table and status as "Success"
1407 INSERT INTO AHL_RT_OPER_INTERFACE
1408 (
1409 CONTEXT,
1410 PUBLISH_DATE,
1411 ROUTE_ID,
1412 PDF,
1413 REVISION_DATE,
1414 CHANGE_FLAG,
1415 ATA_CODE,
1416 DESCRIPTION,
1417 STATUS,
1418 REASON,
1419 ENIGMA_ID
1420 )
1421 VALUES
1422 (
1423 p_context,
1424 p_pub_date,
1425 p_enigma_route_rec.route_id,
1426 p_enigma_route_rec.pdf,
1427 p_enigma_route_rec.revision_date,
1428 p_enigma_route_rec.change_flag,
1429 p_enigma_route_rec.ata_code,
1430 p_enigma_route_rec.description,
1431 'SUCCESS',
1432 'Route updates Successfully',
1433 p_enigma_route_rec.enigma_id
1434 );
1435
1436 -- Check if the route has any operations, if then call the process operation procedure
1437 IF ( p_enigma_op_tbl.COUNT > 0) THEN
1438 --FOR i IN p_enigma_op_tbl.FIRST..p_enigma_op_tbl.LAST
1439 --LOOP
1440
1441 Process_OP_Details
1442 (
1443 '1.0',
1444 FND_API.G_TRUE,
1445 FND_API.G_FALSE,
1446 FND_API.G_VALID_LEVEL_FULL,
1447 FND_API.G_FALSE,
1448 NULL,
1449 x_return_status,
1450 x_msg_count,
1451 x_msg_data,
1452 p_enigma_op_tbl,
1453 p_context,
1454 p_pub_date,
1455 p_enigma_route_rec.route_id
1456 );
1457
1458 IF (l_log_statement >= l_log_current_level) THEN
1459 fnd_log.string(fnd_log.level_statement,l_debug_module,'After Process_OP_Details');
1460 END IF;
1461
1462 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1463 IF (l_log_statement >= l_log_current_level) THEN
1464 fnd_log.string(fnd_log.level_statement,l_debug_module,'Process_OP_Details Error');
1465 END IF;
1466 RAISE FND_API.G_EXC_ERROR;
1467 END IF;
1468 --END LOOP;
1469 END IF; -- oper table count
1470
1471 END IF; -- Status check
1472 ELSE
1473 CLOSE get_latest_route_rev;
1474 -- Raise an error if the route is not found in CMRO..
1475 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_DONOT_EXIST1');
1476 FND_MSG_PUB.ADD;
1477 RAISE FND_API.G_EXC_ERROR;
1478 END IF; -- Cursor
1479 CLOSE get_latest_route_rev;
1480 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1481 THEN
1482
1483 fnd_log.string
1484 (
1485 fnd_log.level_statement,
1486 l_debug_module,
1487 'End if of change Flag U'
1488 );
1489 END IF;
1490
1491 END IF; -- Change Flag U
1492
1493 IF (p_enigma_route_rec.change_flag = 'C') THEN
1494
1498 fnd_log.string
1495 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1496 THEN
1497
1499 (
1500 fnd_log.level_statement,
1501 l_debug_module,
1502 'Inside Create'
1503 );
1504 END IF;
1505
1506 -- Start of Validations for all the mandatory common attributes.
1507 -- Validate is publish_date is Null and If so throw an error
1508
1509 IF (p_pub_date IS NULL AND p_pub_date = FND_API.G_MISS_DATE)
1510 THEN
1511 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_PUB_DATE_NULL');
1512 FND_MSG_PUB.ADD;
1513 END IF;
1514
1515 -- Validate is context is Null and If so throw an error
1516 IF (p_context IS NULL AND p_context = FND_API.G_MISS_CHAR)
1517 THEN
1518 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_CONTEXT_NULL');
1519 FND_MSG_PUB.ADD;
1520 END IF;
1521
1522 -- Validate is Enigma_Id is Null and If so throw an error
1523 IF (p_enigma_route_rec.enigma_ID IS NULL AND p_enigma_route_rec.enigma_ID = FND_API.G_MISS_CHAR)
1524 THEN
1525 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ENIGMA_ID_NULL');
1526 FND_MSG_PUB.ADD;
1527 END IF;
1528
1529 -- Check the error stack and raise error messages , if any
1530 x_msg_count := FND_MSG_PUB.count_msg;
1531 IF x_msg_count > 0
1532 THEN
1533 RAISE FND_API.G_EXC_ERROR;
1534 END IF;
1535
1536 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1537 THEN
1538
1539 fnd_log.string
1540 (
1541 fnd_log.level_statement,
1542 l_debug_module,
1543 'Passed all validations'
1544 );
1545 fnd_log.string
1546 (
1547 fnd_log.level_statement,
1548 l_debug_module,
1549 'p_context -> ' || p_context
1550 );
1551 fnd_log.string
1552 (
1553 fnd_log.level_statement,
1554 l_debug_module,
1555 'p_enigma_route_rec.revision_date -> ' || p_enigma_route_rec.revision_date
1556 );
1557 fnd_log.string
1558 (
1559 fnd_log.level_statement,
1560 l_debug_module,
1561 'p_pub_date -> ' || p_pub_date
1562 );
1563 fnd_log.string
1564 (
1565 fnd_log.level_statement,
1566 l_debug_module,
1567 'p_enigma_route_rec.ata_code -> ' || p_enigma_route_rec.ata_code
1568 );
1569 fnd_log.string
1570 (
1571 fnd_log.level_statement,
1572 l_debug_module,
1573 'p_enigma_route_rec.description -> ' || p_enigma_route_rec.description
1574 );
1575 fnd_log.string
1576 (
1577 fnd_log.level_statement,
1578 l_debug_module,
1579 'p_enigma_route_rec.enigma_id -> ' || p_enigma_route_rec.enigma_id
1580 );
1581 fnd_log.string
1582 (
1583 fnd_log.level_statement,
1584 l_debug_module,
1585 'p_enigma_route_rec.route_id -> ' || p_enigma_route_rec.route_id
1586 );
1587 fnd_log.string
1588 (
1589 fnd_log.level_statement,
1590 l_debug_module,
1591 'p_enigma_route_rec.change_flag -> ' || p_enigma_route_rec.change_flag
1592 );
1593 END IF;
1594
1595 -- Populate the input record with values.
1596 IF (p_context <> FND_API.G_MISS_CHAR AND p_context IS NOT NULL ) THEN
1597 p_process_route_input_rec.model_code := p_context;
1598 END IF;
1599
1600 -- If revision date is not being passed, then set it to sysdate
1601 IF (p_enigma_route_rec.revision_date IS NULL AND p_enigma_route_rec.revision_date = FND_API.G_MISS_DATE)
1602 THEN
1603 p_process_route_input_rec.ACTIVE_START_DATE := sysdate;
1604 ELSE
1605 p_process_route_input_rec.ACTIVE_START_DATE := p_enigma_route_rec.revision_date;
1606 END IF;
1607
1608 IF (p_pub_date <> FND_API.G_MISS_DATE AND p_pub_date IS NOT NULL ) THEN
1609 p_process_route_input_rec.enigma_publish_date := p_pub_date;
1610 END IF;
1611
1612 IF (p_enigma_route_rec.ata_code <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.ata_code IS NOT NULL ) THEN
1613 p_process_route_input_rec.route_no := p_enigma_route_rec.ata_code;
1614 END IF;
1615
1616 IF (p_enigma_route_rec.description <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.description IS NOT NULL ) THEN
1617 p_process_route_input_rec.title := p_enigma_route_rec.description;
1618 ELSE
1619 p_process_route_input_rec.title := p_enigma_route_rec.ata_code;
1620 END IF;
1621
1622 IF (p_enigma_route_rec.enigma_id <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.enigma_id IS NOT NULL ) THEN
1623 p_process_route_input_rec.enigma_doc_id := p_enigma_route_rec.enigma_id;
1624 END IF;
1625
1626 IF (p_enigma_route_rec.route_id <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.route_id IS NOT NULL ) THEN
1627 p_process_route_input_rec.enigma_route_id := p_enigma_route_rec.route_id;
1628 END IF;
1629
1630 IF (p_enigma_route_rec.change_flag <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.change_flag IS NOT NULL ) THEN
1631 p_process_route_input_rec.dml_operation := p_enigma_route_rec.change_flag;
1632 END IF;
1633
1634 IF p_enigma_route_rec.pdf IS NOT NULL
1635 THEN
1636
1637 -- Call the procedure to upload the file
1638 UPLOAD_REVISION_REPORT
1639 (
1640 p_enigma_route_rec.pdf,
1641 x_file_id,
1642 x_return_status
1643 );
1644 END IF;
1645
1646 IF (l_log_statement >= l_log_current_level) THEN
1647 fnd_log.string(fnd_log.level_statement,l_debug_module,'After UPLOAD_REVISION_REPORT Call ');
1648 END IF;
1649
1653 END IF;
1650 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1651 IF (l_log_statement >= l_log_current_level) THEN
1652 fnd_log.string(fnd_log.level_statement,l_debug_module,'UPLOAD_REVISION_REPORT Error');
1654 RAISE FND_API.G_EXC_ERROR;
1655 END IF;
1656
1657 IF (l_log_statement >= l_log_current_level) THEN
1658 fnd_log.string(fnd_log.level_statement,l_debug_module,'UPLOAD_REVISION_REPORT-> File upload done');
1659 fnd_log.string(fnd_log.level_statement,l_debug_module,'File Id -> = ' || x_file_id);
1660 END IF;
1661
1662 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1663 THEN
1664
1665 fnd_log.string
1666 (
1667 fnd_log.level_statement,
1668 l_debug_module,
1669 'file upload done'
1670 );
1671 END IF;
1672
1673 -- If the return status is success, populate the input rec for process_route for updation
1674 IF ( x_file_id <> FND_API.G_MISS_NUM AND x_file_id IS NOT NULL) THEN
1675 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1676 THEN
1677
1678 fnd_log.string
1679 (
1680 fnd_log.level_statement,
1681 l_debug_module,
1682 'File id ->' || x_file_id
1683 );
1684 END IF;
1685 p_process_route_input_rec.FILE_ID := x_file_id ;
1686 END IF;
1687
1688 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1689 THEN
1690
1691 fnd_log.string
1692 (
1693 fnd_log.level_statement,
1694 l_debug_module,
1695 'Populated values '
1696 );
1697 fnd_log.string
1698 (
1699 fnd_log.level_statement,
1700 l_debug_module,
1701 'p_process_route_input_rec.model_code -> ' || p_process_route_input_rec.model_code
1702 );
1703 fnd_log.string
1704 (
1705 fnd_log.level_statement,
1706 l_debug_module,
1707 'p_process_route_input_rec.ACTIVE_START_DATE -> ' || p_process_route_input_rec.ACTIVE_START_DATE
1708 );
1709 fnd_log.string
1710 (
1711 fnd_log.level_statement,
1712 l_debug_module,
1713 'p_process_route_input_rec.enigma_publish_date -> ' || p_process_route_input_rec.enigma_publish_date
1714 );
1715 fnd_log.string
1716 (
1717 fnd_log.level_statement,
1718 l_debug_module,
1719 'p_process_route_input_rec.route_no -> ' || p_process_route_input_rec.route_no
1720 );
1721 fnd_log.string
1722 (
1723 fnd_log.level_statement,
1724 l_debug_module,
1725 'p_process_route_input_rec.title -> ' || p_process_route_input_rec.title
1726 );
1727 fnd_log.string
1728 (
1729 fnd_log.level_statement,
1730 l_debug_module,
1731 'p_process_route_input_rec.enigma_doc_id -> ' || p_process_route_input_rec.enigma_doc_id
1732 );
1733 fnd_log.string
1734 (
1735 fnd_log.level_statement,
1736 l_debug_module,
1737 'p_process_route_input_rec.enigma_route_id -> ' || p_process_route_input_rec.enigma_route_id
1738 );
1739 fnd_log.string
1740 (
1741 fnd_log.level_statement,
1742 l_debug_module,
1743 'p_process_route_input_rec.dml_operation -> ' || p_process_route_input_rec.dml_operation
1744 );
1745 fnd_log.string
1746 (
1747 fnd_log.level_statement,
1748 l_debug_module,
1749 'Calling process_route in Create mode'
1750 );
1751 END IF;
1752
1753 -- Call the API for update.
1754 AHL_RM_ROUTE_PVT.process_route
1755 (
1756 '1.0',
1757 FND_API.G_TRUE,
1758 FND_API.G_FALSE,
1759 FND_API.G_VALID_LEVEL_FULL,
1760 FND_API.G_FALSE,
1761 NULL,
1762 x_return_status,
1763 x_msg_count,
1764 x_msg_data,
1765 p_process_route_input_rec
1766 );
1767
1768 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1769 THEN
1770
1771 fnd_log.string
1772 (
1773 fnd_log.level_statement,
1774 l_debug_module,
1775 'After Calling process_route in Create mode'
1776 );
1777 END IF;
1778
1779 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1780 IF (l_log_statement >= l_log_current_level) THEN
1781 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.process_route Error');
1782 END IF;
1783 RAISE FND_API.G_EXC_ERROR;
1784 END IF;
1785
1786 IF (l_log_statement >= l_log_current_level) THEN
1787 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.process_route -> creation Successful');
1788 END IF;
1789
1790 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1791 THEN
1792
1793 fnd_log.string
1794 (
1795 fnd_log.level_statement,
1796 l_debug_module,
1797 'Before inserting into the staging table'
1798 );
1799 END IF;
1800
1801 -- If the return status is success, then add the transaction to the stating table and status as "Success"
1802 INSERT INTO AHL_RT_OPER_INTERFACE
1803 (
1804 CONTEXT,
1805 PUBLISH_DATE,
1806 ROUTE_ID,
1807 PDF,
1808 REVISION_DATE,
1809 CHANGE_FLAG,
1810 ATA_CODE,
1811 DESCRIPTION,
1812 STATUS,
1813 REASON,
1814 ENIGMA_ID
1815 )
1816 VALUES
1817 (
1818 p_context,
1819 p_pub_date,
1823 p_enigma_route_rec.change_flag,
1820 p_enigma_route_rec.route_id,
1821 p_enigma_route_rec.pdf,
1822 p_enigma_route_rec.revision_date,
1824 p_enigma_route_rec.ata_code,
1825 p_enigma_route_rec.description,
1826 'SUCCESS',
1827 'Route created Successfully',
1828 p_enigma_route_rec.enigma_id
1829 );
1830
1831 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1832 THEN
1833
1834 fnd_log.string
1835 (
1836 fnd_log.level_statement,
1837 l_debug_module,
1838 'After inserting into the staging table'
1839 );
1840 END IF;
1841
1842 -- Check if the route has any operations, if then call the process operation procedure
1843 IF ( p_enigma_op_tbl.COUNT > 0) THEN
1844 --FOR i IN p_enigma_op_tbl.FIRST..p_enigma_op_tbl.LAST
1845 --LOOP
1846
1847 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1848 THEN
1849
1850 fnd_log.string
1851 (
1852 fnd_log.level_statement,
1853 l_debug_module,
1854 'Operations Found Calling Process_OP_Details '
1855 );
1856 END IF;
1857
1858 Process_OP_Details
1859 (
1860 '1.0',
1861 FND_API.G_TRUE,
1862 FND_API.G_FALSE,
1863 FND_API.G_VALID_LEVEL_FULL,
1864 FND_API.G_FALSE,
1865 NULL,
1866 x_return_status,
1867 x_msg_count,
1868 x_msg_data,
1869 p_enigma_op_tbl,
1870 p_context,
1871 p_pub_date,
1872 p_enigma_route_rec.route_id
1873 );
1874
1875 IF (l_log_statement >= l_log_current_level) THEN
1876 fnd_log.string(fnd_log.level_statement,l_debug_module,'After Process_OP_Details');
1877 END IF;
1878
1879 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1880 IF (l_log_statement >= l_log_current_level) THEN
1881 fnd_log.string(fnd_log.level_statement,l_debug_module,'Process_OP_Details Error');
1882 END IF;
1883 RAISE FND_API.G_EXC_ERROR;
1884 END IF;
1885 --END LOOP;
1886 END IF; -- oper table count
1887
1888
1889 END IF; -- status check 'C'
1890
1891 -- Check Error Message stack.
1892 x_msg_count := FND_MSG_PUB.count_msg;
1893 IF x_msg_count > 0
1894 THEN
1895 RAISE FND_API.G_EXC_ERROR;
1896 END IF;
1897
1898 -- Standard check for p_commit
1899 IF FND_API.To_Boolean (p_commit)
1900 THEN
1901 COMMIT WORK;
1902 END IF;
1903
1907 p_encoded => fnd_api.g_false );
1904 -- Standard call to get message count and if count is 1, get message info
1905 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1906 p_data => x_msg_data,
1908
1909 EXCEPTION
1910 WHEN FND_API.G_EXC_ERROR THEN
1911 x_return_status := FND_API.G_RET_STS_ERROR;
1912 Rollback to Process_Route_Details_SP;
1913 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1914 p_data => x_msg_data,
1915 p_encoded => fnd_api.g_false);
1916
1917 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1918 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1919 Rollback to Process_Route_Details_SP;
1920 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1921 p_data => x_msg_data,
1922 p_encoded => fnd_api.g_false);
1923
1924 WHEN OTHERS THEN
1925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1926 Rollback to Process_Route_Details_SP;
1927 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1928 THEN
1929 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1930 p_procedure_name => 'Process_Route_Details',
1931 p_error_text => SUBSTR(SQLERRM,1,240));
1932 END IF;
1933 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1934 p_data => x_msg_data,
1935 p_encoded => fnd_api.g_false);
1936
1937 END Process_Route_Details;
1938
1939
1940 PROCEDURE Process_OP_Details
1941 (
1942 p_api_version IN NUMBER := '1.0',
1943 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1944 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1945 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1946 p_default IN VARCHAR2 := FND_API.G_FALSE,
1947 p_module_type IN VARCHAR2 := NULL,
1948 x_return_status OUT NOCOPY VARCHAR2,
1949 x_msg_count OUT NOCOPY NUMBER,
1950 x_msg_data OUT NOCOPY VARCHAR2,
1951 p_enigma_op_tbl IN enigma_op_tbl_type,
1952 p_context IN VARCHAR2,
1953 p_pub_date IN DATE,
1954 p_parent_route_id IN VARCHAR2
1955 )
1956 IS
1957
1958 CURSOR get_latest_oper_rev (c_operation_id IN VARCHAR2)
1959 IS
1960 SELECT
1961 operation_id,
1962 object_version_number,
1963 revision_status_code revision_status
1964 FROM AHL_OPERATIONS_B
1965 WHERE UPPER(TRIM(ENIGMA_OP_ID)) = UPPER(TRIM(c_operation_id))
1966 AND REVISION_NUMBER =
1967 ( SELECT
1968 MAX( revision_number )
1969 FROM
1970 AHL_OPERATIONS_B
1971 WHERE
1972 UPPER(TRIM(ENIGMA_OP_ID)) = UPPER(TRIM(c_operation_id))
1973 );
1974
1975
1976 -- Declare local variables
1977 l_api_name CONSTANT VARCHAR2(30) := 'Process_OP_Details';
1978 l_api_version CONSTANT NUMBER := 1.0;
1979 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||l_api_name;
1980
1981 l_get_latest_oper_rev get_latest_oper_rev%ROWTYPE;
1982 p_process_oper_input_rec AHL_RM_OPERATION_PVT.operation_rec_type;
1983 route_operation_tbl_type AHL_RM_OP_ROUTE_AS_PVT.route_operation_tbl_type;
1984
1985 x_operation_id NUMBER;
1986 parent_route_id VARCHAR2(2000);
1987 x_revision_number VARCHAR2(30);
1988 p_route_id NUMBER;
1989
1990 l_operation_id NUMBER;
1991 l_object_version_number NUMBER;
1992 l_revision_status VARCHAR2(100);
1993
1994 l_step_count NUMBER;
1995
1996 BEGIN
1997 -- Standard start of API savepoint
1998 SAVEPOINT Process_OP_Details_SP;
1999
2000 -- Initialize return status to success before any code logic/validation
2001 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2002
2003 -- Standard call to check for call compatibility
2004 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2005 THEN
2006 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2007 END IF;
2008
2009 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
2010 IF FND_API.TO_BOOLEAN(p_init_msg_list)
2011 THEN
2012 FND_MSG_PUB.INITIALIZE;
2013 END IF;
2014
2015 -- initialise it only if it is a OA Adapter !
2019 IF (l_log_procedure >= l_log_current_level) THEN
2016 fnd_global.APPS_INITIALIZE (1003259,62211, 867);
2017
2018 -- Log API entry point
2020 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.begin','At the start of PL SQL procedure ');
2021 END IF;
2022
2023 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2024 THEN
2025
2026 fnd_log.string
2027 (
2028 fnd_log.level_statement,
2029 l_debug_module,
2030 'Inside Process_OP_Details... p_enigma_op_tbl.COUNT ->'||p_enigma_op_tbl.COUNT
2031 );
2032 END IF;
2033
2034 l_step_count := 1;
2035
2036 -- Check if the tbale has any operations,
2037 -- If so process them depending on their change flag .
2038 IF ( p_enigma_op_tbl.COUNT > 0) THEN
2039 FOR i IN p_enigma_op_tbl.FIRST..p_enigma_op_tbl.LAST
2040 LOOP
2041
2042 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2043 THEN
2044
2045 fnd_log.string
2046 (
2047 fnd_log.level_statement,
2048 l_debug_module,
2049 'Inside Process_OP_Details -> Operations Found i->'||i
2050 );
2051 END IF;
2052
2053 IF ( p_enigma_op_tbl(i).parent_route_id IS NULL OR p_enigma_op_tbl(i).parent_route_id = FND_API.G_MISS_CHAR )
2054 THEN
2055 parent_route_id := p_parent_route_id;
2056 ELSE
2057 parent_route_id := p_enigma_op_tbl(i).parent_route_id;
2058 END IF;
2059
2060 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2061 THEN
2062
2063 fnd_log.string
2064 (
2065 fnd_log.level_statement,
2066 l_debug_module,
2067 'Parent RouteId ' || parent_route_id
2068 );
2069 END IF;
2070
2071 -- Verify if the Change flag is right...
2072 IF (p_enigma_op_tbl(i).change_flag NOT IN ( 'C', 'D', 'U'))
2073 THEN
2074 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_DML');
2075 FND_MESSAGE.SET_TOKEN('FIELD', p_enigma_op_tbl(i).change_flag);
2076 FND_MESSAGE.SET_TOKEN('RECORD', p_enigma_op_tbl(i).operation_id);
2077 FND_MSG_PUB.ADD;
2078 RAISE FND_API.G_EXC_ERROR;
2079 END IF;
2080
2081 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2082 THEN
2083
2084 fnd_log.string
2085 (
2086 fnd_log.level_statement,
2087 l_debug_module,
2088 'Change Flag set right'
2089 );
2090 END IF;
2091
2092 -- Vefify is the operation_id null, if so throw an error .
2093 IF ( p_enigma_op_tbl(i).operation_id IS NULL OR p_enigma_op_tbl(i).operation_id = FND_API.G_MISS_CHAR)
2094 THEN
2095 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OPER_ID_NULL');
2096 FND_MSG_PUB.ADD;
2097 RAISE FND_API.G_EXC_ERROR;
2098 END IF;
2099
2100 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2101 THEN
2102
2103 fnd_log.string
2104 (
2105 fnd_log.level_statement,
2106 l_debug_module,
2107 'Operation Id is not null'
2108 );
2109 END IF;
2110
2111 -- If the operation change flag is "D" then process the operation accordingly .
2112 IF (p_enigma_op_tbl(i).change_flag = 'D') THEN
2113
2114 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2115 THEN
2116
2117 fnd_log.string
2118 (
2119 fnd_log.level_statement,
2120 l_debug_module,
2121 'Operation Flag is D'
2122 );
2123 END IF;
2124
2125 OPEN get_latest_oper_rev (p_enigma_op_tbl(i).operation_id);
2126 FETCH get_latest_oper_rev INTO
2127 l_get_latest_oper_rev.operation_id,
2128 l_get_latest_oper_rev.object_version_number,
2129 l_get_latest_oper_rev.revision_status;
2130
2131 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2132 THEN
2133
2134 fnd_log.string
2135 (
2136 fnd_log.level_statement,
2137 l_debug_module,
2138 'l_get_latest_oper_rev.operation_id ->' || l_get_latest_oper_rev.operation_id
2139 );
2140 fnd_log.string
2141 (
2142 fnd_log.level_statement,
2143 l_debug_module,
2144 'l_get_latest_oper_rev.object_version_number ->' || l_get_latest_oper_rev.object_version_number
2145 );
2146 fnd_log.string
2147 (
2148 fnd_log.level_statement,
2149 l_debug_module,
2150 'l_get_latest_oper_rev.revision_status ->' || l_get_latest_oper_rev.revision_status
2151 );
2152 END IF;
2153
2154 IF get_latest_oper_rev%FOUND THEN
2155
2156
2157 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2158 THEN
2159 fnd_log.string
2160 (
2161 fnd_log.level_statement,
2162 l_debug_module,
2163 'Cursor Found '
2164 );
2165 END IF;
2166
2167 -- IF the operation is in Approval Pending status , then insert the operation record into the
2168 -- staging table with status as pending.
2169 IF ( upper(l_get_latest_oper_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
2170 INSERT INTO AHL_RT_OPER_INTERFACE
2171 (
2172 CONTEXT,
2173 PUBLISH_DATE,
2174 OPERATION_ID,
2175 PARENT_ROUTE_ID,
2176 CHANGE_FLAG,
2177 ATA_CODE,
2178 DESCRIPTION,
2179 STATUS,
2180 REASON,
2181 ENIGMA_ID
2182 )
2183 VALUES
2184 (
2185 p_context,
2186 p_pub_date,
2187 p_enigma_op_tbl(i).operation_id,
2191 p_enigma_op_tbl(i).description,
2188 parent_route_id,
2189 p_enigma_op_tbl(i).change_flag,
2190 p_enigma_op_tbl(i).ata_code,
2192 'PENDING',
2193 'Operation is in approval pending status',
2194 p_enigma_op_tbl(i).enigma_id
2195 );
2196
2197 ELSE
2198 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2199 THEN
2200 fnd_log.string
2201 (
2202 fnd_log.level_statement,
2203 l_debug_module,
2204 'Inside else , calling delete operation'
2205 );
2206 END IF;
2207
2208 -- Call the delete operation API
2209 AHL_RM_OPERATION_PVT.delete_operation
2210 (
2211 1.0,
2212 FND_API.G_TRUE,
2213 FND_API.G_FALSE,
2214 FND_API.G_VALID_LEVEL_FULL,
2215 FND_API.G_FALSE,
2216 NULL,
2217 x_return_status,
2218 x_msg_count,
2219 x_msg_data,
2220 l_get_latest_oper_rev.operation_id,
2221 l_get_latest_oper_rev.object_version_number
2222 );
2223
2224 IF (l_log_statement >= l_log_current_level) THEN
2225 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OPERATION_PVT.delete_operation');
2226 END IF;
2227
2228 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
2229 IF (l_log_statement >= l_log_current_level) THEN
2230 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.delete_operation Error');
2231 END IF;
2232 RAISE FND_API.G_EXC_ERROR;
2233 END IF;
2234
2235 IF (l_log_statement >= l_log_current_level) THEN
2236 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.delete_operation -> deletion Successful');
2237 END IF;
2238
2239 CLOSE get_latest_oper_rev;
2240 END IF;
2241 ELSE
2242 -- If the operation is not found, then raise an error
2243 CLOSE get_latest_oper_rev;
2244 FND_MESSAGE.SET_NAME('AHL', 'AHL_ENIGMA_OPER_DONOT_EXIST');
2245 FND_MSG_PUB.ADD;
2246 RAISE FND_API.G_EXC_ERROR;
2247 END IF; -- Cursor Found
2248 END IF; -- Change Flag "D"
2249
2250 -- If the operation change flag is "U" then process the operation accordingly .
2251 IF (p_enigma_op_tbl(i).change_flag = 'U') THEN
2252
2253 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2254 THEN
2255
2256 fnd_log.string
2257 (
2258 fnd_log.level_statement,
2259 l_debug_module,
2260 'Operation Flag is U'
2261 );
2262 fnd_log.string
2263 (
2264 fnd_log.level_statement,
2265 l_debug_module,
2266 'p_enigma_op_tbl(i).operation_id -> ' || p_enigma_op_tbl(i).operation_id
2267 );
2268 END IF;
2269
2270 OPEN get_latest_oper_rev (p_enigma_op_tbl(i).operation_id);
2271 FETCH get_latest_oper_rev INTO
2272 l_get_latest_oper_rev.operation_id,
2273 l_get_latest_oper_rev.object_version_number,
2274 l_get_latest_oper_rev.revision_status;
2275
2276 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2277 THEN
2278
2279 fnd_log.string
2280 (
2281 fnd_log.level_statement,
2282 l_debug_module,
2283 'l_get_latest_oper_rev.operation_id ->' || l_get_latest_oper_rev.operation_id
2284 );
2285 fnd_log.string
2286 (
2287 fnd_log.level_statement,
2288 l_debug_module,
2289 'l_get_latest_oper_rev.object_version_number ->' || l_get_latest_oper_rev.object_version_number
2290 );
2291 fnd_log.string
2292 (
2293 fnd_log.level_statement,
2294 l_debug_module,
2295 'l_get_latest_oper_rev.revision_status ->' || l_get_latest_oper_rev.revision_status
2296 );
2297 END IF;
2298
2299 IF get_latest_oper_rev%FOUND THEN
2300 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2301 THEN
2302
2303 fnd_log.string
2304 (
2305 fnd_log.level_statement,
2306 l_debug_module,
2307 'Latest Operation Found'
2308 );
2309 END IF;
2310
2311 -- If the operation is in Approval Pending status , then insert the operation record into the
2312 -- staging table with status as pending.
2313 IF ( upper(l_get_latest_oper_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
2314 INSERT INTO AHL_RT_OPER_INTERFACE
2315 (
2316 CONTEXT,
2317 PUBLISH_DATE,
2318 OPERATION_ID,
2319 PARENT_ROUTE_ID,
2320 CHANGE_FLAG,
2321 ATA_CODE,
2322 DESCRIPTION,
2323 STATUS,
2324 REASON,
2325 ENIGMA_ID
2326 )
2327 VALUES
2328 (
2329 p_context,
2330 p_pub_date,
2331 p_enigma_op_tbl(i).operation_id,
2332 parent_route_id,
2333 p_enigma_op_tbl(i).change_flag,
2334 p_enigma_op_tbl(i).ata_code,
2335 p_enigma_op_tbl(i).description,
2336 'PENDING',
2337 'Operation is in approval pending status',
2338 p_enigma_op_tbl(i).enigma_id
2339 );
2340
2341 -- When the operation is in complete, do the following process
2342 ELSIF (upper(l_get_latest_oper_rev.revision_status) = 'COMPLETE' ) THEN
2343 -- Call the API to create a new revision of the operation.
2344 AHL_RM_OPERATION_PVT.create_oper_revision
2345 (
2346 '1.0',
2347 FND_API.G_TRUE,
2348 FND_API.G_FALSE,
2349 FND_API.G_VALID_LEVEL_FULL,
2353 x_msg_count,
2350 FND_API.G_FALSE,
2351 NULL,
2352 x_return_status,
2354 x_msg_data,
2355 l_get_latest_oper_rev.operation_id,
2356 l_get_latest_oper_rev.object_version_number,
2357 x_operation_id
2358 );
2359
2360 IF (l_log_statement >= l_log_current_level) THEN
2361 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OPERATION_PVT.create_oper_revision');
2362 END IF;
2363
2364 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
2365 IF (l_log_statement >= l_log_current_level) THEN
2366 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.create_oper_revision Error');
2367 END IF;
2368 RAISE FND_API.G_EXC_ERROR;
2369 END IF;
2370
2371 IF (l_log_statement >= l_log_current_level) THEN
2372 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.create_oper_revision -> revision Created');
2373 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.create_oper_revision -> x_operation_id= ' || x_operation_id);
2374 END IF;
2375
2376 -- If the return status is success, populate the input rec for process_operation for updation
2377 -- Do the mandatory validations
2378
2379 IF (p_enigma_op_tbl(i).operation_id IS NULL AND p_enigma_op_tbl(i).operation_id = FND_API.G_MISS_DATE)
2380 THEN
2381 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OPER_ID_NULL');
2382 FND_MSG_PUB.ADD;
2383 END IF;
2384
2385 IF (p_context IS NULL AND p_context = FND_API.G_MISS_DATE)
2386 THEN
2387 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_CONTEXT_NULL');
2388 FND_MSG_PUB.ADD;
2389 END IF;
2390
2391 -- Check the error stack and raise error messages , if any
2392 x_msg_count := FND_MSG_PUB.count_msg;
2393 IF x_msg_count > 0
2394 THEN
2395 RAISE FND_API.G_EXC_ERROR;
2396 END IF;
2397
2398 IF ( x_operation_id <> FND_API.G_MISS_NUM AND x_operation_id IS NOT NULL) THEN
2399 p_process_oper_input_rec.OPERATION_ID := x_operation_id ;
2400 END IF;
2401
2402 IF (l_get_latest_oper_rev.object_version_number <> FND_API.G_MISS_NUM AND l_get_latest_oper_rev.object_version_number IS NOT NULL ) THEN
2403 p_process_oper_input_rec.OBJECT_VERSION_NUMBER := l_get_latest_oper_rev.object_version_number;
2404 END IF;
2405
2406 IF (p_context <> FND_API.G_MISS_CHAR AND p_context IS NOT NULL ) THEN
2407 p_process_oper_input_rec.MODEL_CODE := p_context;
2408 p_process_oper_input_rec.SEGMENT1 := p_context;
2409 END IF;
2410
2411 IF (p_enigma_op_tbl(i).description <> FND_API.G_MISS_CHAR AND p_enigma_op_tbl(i).description IS NOT NULL ) THEN
2412 p_process_oper_input_rec.DESCRIPTION := p_enigma_op_tbl(i).description;
2413 END IF;
2414
2415 IF (p_enigma_op_tbl(i).operation_id <> FND_API.G_MISS_CHAR AND p_enigma_op_tbl(i).operation_id IS NOT NULL ) THEN
2416 p_process_oper_input_rec.ENIGMA_OP_ID := p_enigma_op_tbl(i).operation_id;
2417 END IF;
2418
2419 IF (p_enigma_op_tbl(i).ATA_CODE <> FND_API.G_MISS_CHAR AND p_enigma_op_tbl(i).ATA_CODE IS NOT NULL ) THEN
2420 p_process_oper_input_rec.SEGMENT2 := p_enigma_op_tbl(i).ATA_CODE;
2421 END IF;
2422
2423
2424 p_process_oper_input_rec.DML_OPERATION := 'U';
2425
2426 -- Call the API for update
2427 AHL_RM_OPERATION_PVT.process_operation
2428 (
2429 '1.0',
2430 FND_API.G_TRUE,
2431 FND_API.G_FALSE,
2432 FND_API.G_VALID_LEVEL_FULL,
2433 FND_API.G_FALSE,
2434 NULL,
2435 x_return_status,
2436 x_msg_count,
2437 x_msg_data,
2438 p_process_oper_input_rec
2439 );
2440
2441 IF (l_log_statement >= l_log_current_level) THEN
2442 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OPERATION_PVT.process_operation');
2443 END IF;
2444
2445 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
2446 IF (l_log_statement >= l_log_current_level) THEN
2447 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.process_operation Error');
2448 END IF;
2449 RAISE FND_API.G_EXC_ERROR;
2450 END IF;
2451
2452 IF (l_log_statement >= l_log_current_level) THEN
2453 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.process_operation -> updation Successful');
2454 END IF;
2455
2456 -- Insert the transaction record into the staging table, with status as success
2457 INSERT INTO AHL_RT_OPER_INTERFACE
2458 (
2459 CONTEXT,
2460 PUBLISH_DATE,
2461 OPERATION_ID,
2462 PARENT_ROUTE_ID,
2463 CHANGE_FLAG,
2464 ATA_CODE,
2465 DESCRIPTION,
2466 STATUS,
2467 REASON,
2468 ENIGMA_ID
2469 )
2470 VALUES
2471 (
2472 p_context,
2473 p_pub_date,
2474 p_enigma_op_tbl(i).operation_id,
2475 parent_route_id,
2476 p_enigma_op_tbl(i).change_flag,
2477 p_enigma_op_tbl(i).ata_code,
2478 p_enigma_op_tbl(i).description,
2479 'SUCCESS',
2480 'Operation updated successfully',
2481 p_enigma_op_tbl(i).enigma_id
2482 );
2483
2484 -- If the status is Draft or Approval Rejected , do the following.
2485 ELSIF ( UPPER(l_get_latest_oper_rev.revision_status) = 'DRAFT'
2486 OR UPPER(l_get_latest_oper_rev.revision_status) = 'APPROVAL_REJECTED' ) THEN
2487
2488 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2489 THEN
2490
2491 fnd_log.string
2495 'Update Operation-> Draft'
2492 (
2493 fnd_log.level_statement,
2494 l_debug_module,
2496 );
2497 END IF;
2498
2499 -- Do the mandatory validations
2500 IF (p_enigma_op_tbl(i).operation_id IS NULL AND p_enigma_op_tbl(i).operation_id = FND_API.G_MISS_DATE)
2501 THEN
2502 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OPER_ID_NULL');
2503 FND_MSG_PUB.ADD;
2504 END IF;
2505
2506 IF (p_context IS NULL AND p_context = FND_API.G_MISS_DATE)
2507 THEN
2508 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_CONTEXT_NULL');
2509 FND_MSG_PUB.ADD;
2510 END IF;
2511
2512 -- Check the error stack and raise error messages , if any
2513 x_msg_count := FND_MSG_PUB.count_msg;
2514 IF x_msg_count > 0
2515 THEN
2516 RAISE FND_API.G_EXC_ERROR;
2517 END IF;
2518
2519 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2520 THEN
2521
2522 fnd_log.string
2523 (
2524 fnd_log.level_statement,
2525 l_debug_module,
2526 'populate the input rec'
2527 );
2528 END IF;
2529
2530 -- If the return status is success, populate the input rec for process_operation for updation
2531 IF ( l_get_latest_oper_rev.operation_id <> FND_API.G_MISS_NUM AND l_get_latest_oper_rev.operation_id IS NOT NULL) THEN
2532 p_process_oper_input_rec.OPERATION_ID := l_get_latest_oper_rev.operation_id ;
2533 END IF;
2534
2535 IF (l_get_latest_oper_rev.object_version_number <> FND_API.G_MISS_NUM AND l_get_latest_oper_rev.object_version_number IS NOT NULL ) THEN
2536 p_process_oper_input_rec.OBJECT_VERSION_NUMBER := l_get_latest_oper_rev.object_version_number;
2537 END IF;
2538
2539
2540 IF (p_context <> FND_API.G_MISS_CHAR AND p_context IS NOT NULL ) THEN
2541 p_process_oper_input_rec.MODEL_CODE := p_context;
2542 p_process_oper_input_rec.SEGMENT1 := p_context;
2543 END IF;
2544
2545 IF (p_enigma_op_tbl(i).description <> FND_API.G_MISS_CHAR AND p_enigma_op_tbl(i).description IS NOT NULL ) THEN
2546 p_process_oper_input_rec.DESCRIPTION := p_enigma_op_tbl(i).description;
2547 END IF;
2548
2549 IF (p_enigma_op_tbl(i).operation_id <> FND_API.G_MISS_CHAR AND p_enigma_op_tbl(i).operation_id IS NOT NULL ) THEN
2550 p_process_oper_input_rec.ENIGMA_OP_ID := p_enigma_op_tbl(i).operation_id;
2551 END IF;
2552
2553 IF (p_enigma_op_tbl(i).ATA_CODE <> FND_API.G_MISS_CHAR AND p_enigma_op_tbl(i).ATA_CODE IS NOT NULL ) THEN
2554 p_process_oper_input_rec.SEGMENT2 := p_enigma_op_tbl(i).ATA_CODE;
2555 END IF;
2556
2557 p_process_oper_input_rec.DML_OPERATION := 'U';
2558
2559
2560 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2561 THEN
2562
2563 fnd_log.string
2564 (
2565 fnd_log.level_statement,
2566 l_debug_module,
2567 'p_process_oper_input_rec.OPERATION_ID-> ' || p_process_oper_input_rec.OPERATION_ID
2568 );
2569 fnd_log.string
2570 (
2571 fnd_log.level_statement,
2572 l_debug_module,
2573 'l_get_latest_oper_rev.object_version_number -> ' || l_get_latest_oper_rev.object_version_number
2574 );
2575 fnd_log.string
2576 (
2577 fnd_log.level_statement,
2578 l_debug_module,
2579 'p_enigma_op_tbl(i).description -> ' || p_enigma_op_tbl(i).description
2580 );
2581 fnd_log.string
2582 (
2583 fnd_log.level_statement,
2584 l_debug_module,
2585 'p_process_oper_input_rec.ENIGMA_OP_ID -> ' || p_process_oper_input_rec.ENIGMA_OP_ID
2586 );
2587 fnd_log.string
2588 (
2589 fnd_log.level_statement,
2590 l_debug_module,
2591 'p_process_oper_input_rec.SEGMENT2 -> ' || p_process_oper_input_rec.SEGMENT2
2592 );
2593 END IF;
2594
2595 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2596 THEN
2597
2598 fnd_log.string
2599 (
2600 fnd_log.level_statement,
2601 l_debug_module,
2602 'Calling AHL_RM_OPERATION_PVT.process_operation '
2603 );
2604 END IF;
2605
2606 -- Call the API for update
2607 AHL_RM_OPERATION_PVT.process_operation
2608 (
2609 '1.0',
2610 FND_API.G_TRUE,
2611 FND_API.G_FALSE,
2612 FND_API.G_VALID_LEVEL_FULL,
2613 FND_API.G_FALSE,
2614 NULL,
2615 x_return_status,
2616 x_msg_count,
2617 x_msg_data,
2618 p_process_oper_input_rec
2619 );
2620
2621 IF (l_log_statement >= l_log_current_level) THEN
2622 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OPERATION_PVT.process_operation');
2623 END IF;
2624
2625 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
2626 IF (l_log_statement >= l_log_current_level) THEN
2630 END IF;
2627 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.process_operation Error');
2628 END IF;
2629 RAISE FND_API.G_EXC_ERROR;
2631
2632 IF (l_log_statement >= l_log_current_level) THEN
2633 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.process_operation -> updation Successful');
2634 END IF;
2635
2636 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2637 THEN
2638
2639 fnd_log.string
2640 (
2641 fnd_log.level_statement,
2642 l_debug_module,
2643 'Before inserting into the staging table'
2644 );
2645 END IF;
2646
2647 -- Insert the transaction record into the staging table, with status as success
2648 INSERT INTO AHL_RT_OPER_INTERFACE
2649 (
2650 CONTEXT,
2651 PUBLISH_DATE,
2652 OPERATION_ID,
2653 PARENT_ROUTE_ID,
2654 CHANGE_FLAG,
2655 ATA_CODE,
2656 DESCRIPTION,
2657 STATUS,
2658 REASON,
2659 ENIGMA_ID
2660 )
2661 VALUES
2662 (
2663 p_context,
2664 p_pub_date,
2665 p_enigma_op_tbl(i).operation_id,
2666 parent_route_id,
2667 p_enigma_op_tbl(i).change_flag,
2668 p_enigma_op_tbl(i).ata_code,
2669 p_enigma_op_tbl(i).description,
2670 'SUCCESS',
2671 'Operation updated successfully',
2672 p_enigma_op_tbl(i).enigma_id
2673 );
2674
2675 END IF ; -- Status check
2676 CLOSE get_latest_oper_rev;
2677
2678 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2679 THEN
2680
2681 fnd_log.string
2682 (
2683 fnd_log.level_statement,
2684 l_debug_module,
2685 'After inserting into the staging table'
2686 );
2687 END IF;
2688 ELSE
2689 -- If the operation is not found, then raise an error
2690 CLOSE get_latest_oper_rev;
2691 FND_MESSAGE.SET_NAME('AHL', 'AHL_ENIGMA_OPER_DONOT_EXIST');
2692 FND_MSG_PUB.ADD;
2693 RAISE FND_API.G_EXC_ERROR;
2694 END IF; -- Cursor Found
2695 END IF; -- Change Flag "U"
2696
2697 -- If the change flag is C then create a new operation.
2698 IF (p_enigma_op_tbl(i).change_flag = 'C') THEN
2699
2700
2701 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2702 THEN
2703
2704 fnd_log.string
2705 (
2706 fnd_log.level_statement,
2707 l_debug_module,
2708 'Inside Process_OP_Details -> Inside C '
2709 );
2710 END IF;
2711
2712 -- Populate the input records
2713 IF (p_context <> FND_API.G_MISS_CHAR AND p_context IS NOT NULL ) THEN
2714 p_process_oper_input_rec.MODEL_CODE := p_context;
2715 p_process_oper_input_rec.SEGMENT1 := p_context;
2716 END IF;
2717
2718 IF (p_enigma_op_tbl(i).description <> FND_API.G_MISS_CHAR AND p_enigma_op_tbl(i).description IS NOT NULL ) THEN
2719 p_process_oper_input_rec.DESCRIPTION := p_enigma_op_tbl(i).description;
2720 ELSE
2721 p_process_oper_input_rec.DESCRIPTION := p_enigma_op_tbl(i).ATA_CODE;
2722 END IF;
2723
2724 IF (p_enigma_op_tbl(i).operation_id <> FND_API.G_MISS_CHAR AND p_enigma_op_tbl(i).operation_id IS NOT NULL ) THEN
2725 p_process_oper_input_rec.ENIGMA_OP_ID := p_enigma_op_tbl(i).operation_id;
2726 END IF;
2727
2728 IF (p_enigma_op_tbl(i).ATA_CODE <> FND_API.G_MISS_CHAR AND p_enigma_op_tbl(i).ATA_CODE IS NOT NULL ) THEN
2729 p_process_oper_input_rec.SEGMENT2 := p_enigma_op_tbl(i).ATA_CODE;
2730 END IF;
2731
2732 p_process_oper_input_rec.ACTIVE_START_DATE := sysdate;
2733 p_process_oper_input_rec.DML_OPERATION := p_enigma_op_tbl(i).change_flag;
2734 p_process_oper_input_rec.STANDARD_OPERATION_FLAG := 'N';
2735
2736 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2737 THEN
2738
2739 fnd_log.string
2740 (
2741 fnd_log.level_statement,
2742 l_debug_module,
2743 'Inside Process_OP_Details calling process_operations'
2744 );
2745 END IF;
2746
2747 -- Call the API for update
2751 FND_API.G_TRUE,
2748 AHL_RM_OPERATION_PVT.process_operation
2749 (
2750 '1.0',
2752 FND_API.G_FALSE,
2753 FND_API.G_VALID_LEVEL_FULL,
2754 FND_API.G_FALSE,
2755 NULL,
2756 x_return_status,
2757 x_msg_count,
2758 x_msg_data,
2759 p_process_oper_input_rec
2760 );
2761
2762 x_operation_id := p_process_oper_input_rec.operation_id;
2763 x_revision_number := p_process_oper_input_rec.REVISION_NUMBER;
2764
2765 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2766 THEN
2767
2768 fnd_log.string
2769 (
2770 fnd_log.level_statement,
2771 l_debug_module,
2772 'Operation Created ->' || x_operation_id
2773 );
2774 fnd_log.string
2775 (
2776 fnd_log.level_statement,
2777 l_debug_module,
2778 'Operation Created Revision->' || x_revision_number
2779 );
2780 fnd_log.string
2781 (
2782 fnd_log.level_statement,
2783 l_debug_module,
2784 'Process_OP_Details after calling process_operations'
2785 );
2786 END IF;
2787
2788 IF (l_log_statement >= l_log_current_level) THEN
2789 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OPERATION_PVT.process_operation');
2790 END IF;
2791
2792 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
2793 IF (l_log_statement >= l_log_current_level) THEN
2794 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.process_operation Error');
2795 END IF;
2796 RAISE FND_API.G_EXC_ERROR;
2797 END IF;
2798
2799 IF (l_log_statement >= l_log_current_level) THEN
2800 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.process_operation -> creation Successful');
2801 END IF;
2802
2803 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2804 THEN
2805
2806 fnd_log.string
2807 (
2808 fnd_log.level_statement,
2809 l_debug_module,
2810 'before inserting the operations into staging table '
2811 );
2812 END IF;
2813
2814 -- Insert the transaction record into the staging table, with status as success
2815 INSERT INTO AHL_RT_OPER_INTERFACE
2816 (
2820 PARENT_ROUTE_ID,
2817 CONTEXT,
2818 PUBLISH_DATE,
2819 OPERATION_ID,
2821 CHANGE_FLAG,
2822 ATA_CODE,
2823 DESCRIPTION,
2824 STATUS,
2825 REASON,
2826 ENIGMA_ID
2827 )
2828 VALUES
2829 (
2830 p_context,
2831 p_pub_date,
2832 p_enigma_op_tbl(i).operation_id,
2833 parent_route_id,
2834 p_enigma_op_tbl(i).change_flag,
2835 p_enigma_op_tbl(i).ata_code,
2839 p_enigma_op_tbl(i).enigma_id
2836 p_enigma_op_tbl(i).description,
2837 'SUCCESS',
2838 'Operation created successfully',
2840 );
2841
2842 -- Populate the input record to pass to the procedure to assocaite the operation to the route
2843
2844 SELECT route_id INTO p_route_id FROM AHL_ROUTES_B
2845 WHERE ENIGMA_ROUTE_ID = parent_route_id;
2846
2847 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2848 THEN
2849
2850 fnd_log.string
2851 (
2852 fnd_log.level_statement,
2853 l_debug_module,
2854 'p_route_id -> '|| p_route_id
2855 );
2856 END IF;
2857
2858 route_operation_tbl_type(1) := null;
2859
2860 route_operation_tbl_type(1).OPERATION_ID := x_operation_id;
2861 route_operation_tbl_type(1).STEP := l_step_count;
2862 route_operation_tbl_type(1).DML_OPERATION := 'C';
2863 route_operation_tbl_type(1).REVISION_NUMBER := x_revision_number;
2864 route_operation_tbl_type(1).check_point_flag := 'N';
2865
2866 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2867 THEN
2868
2869 fnd_log.string
2870 (
2871 fnd_log.level_statement,
2872 l_debug_module,
2873 'route_operation_tbl_type(i).OPERATION_ID -> '|| route_operation_tbl_type(1).OPERATION_ID
2874 );
2875 fnd_log.string
2876 (
2877 fnd_log.level_statement,
2878 l_debug_module,
2879 'route_operation_tbl_type(i).STEP -> ' || route_operation_tbl_type(1).STEP
2880 );
2881 fnd_log.string
2882 (
2883 fnd_log.level_statement,
2884 l_debug_module,
2885 'route_operation_tbl_type(i).DML_OPERATION -> ' || route_operation_tbl_type(1).DML_OPERATION
2886 );
2887 fnd_log.string
2888 (
2889 fnd_log.level_statement,
2890 l_debug_module,
2891 'route_operation_tbl_type(i).REVISION_NUMBER -> '|| route_operation_tbl_type(1).REVISION_NUMBER
2892 );
2893 END IF;
2894
2895 AHL_RM_OP_ROUTE_AS_PVT.process_route_operation_as
2896 (
2897 1.0,
2898 FND_API.G_TRUE,
2899 FND_API.G_FALSE,
2900 FND_API.G_VALID_LEVEL_FULL,
2901 FND_API.G_TRUE,
2902 NULL,
2903 x_return_status,
2904 x_msg_count ,
2905 x_msg_data ,
2906 route_operation_tbl_type,
2907 p_route_id
2908 );
2909
2910 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2911 THEN
2912
2913 fnd_log.string
2914 (
2915 fnd_log.level_statement,
2916 l_debug_module,
2917 'Association ID x_return_status-> '|| x_return_status
2918 );
2919 END IF;
2920
2921 IF (l_log_statement >= l_log_current_level) THEN
2922 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OP_ROUTE_AS_PVT.process_route_operation_as');
2923 END IF;
2924
2925 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
2926 IF (l_log_statement >= l_log_current_level) THEN
2927 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OP_ROUTE_AS_PVT.process_route_operation_as Error');
2928 END IF;
2929 RAISE FND_API.G_EXC_ERROR;
2930 END IF;
2931
2932 l_step_count := l_step_count + 1;
2933
2934 IF (l_log_statement >= l_log_current_level) THEN
2935 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OP_ROUTE_AS_PVT.process_route_operation_as -> association Successful');
2936 END IF;
2937
2938 END IF; -- Change Flag "C"
2939 END LOOP;
2940 END IF; -- Oper Tbl Count
2941
2942 -- Check Error Message stack.
2943 x_msg_count := FND_MSG_PUB.count_msg;
2944 IF x_msg_count > 0
2945 THEN
2946 RAISE FND_API.G_EXC_ERROR;
2947 END IF;
2948
2949 -- Standard check for p_commit
2950 IF FND_API.To_Boolean (p_commit)
2951 THEN
2952 COMMIT WORK;
2953 END IF;
2954
2955 -- Standard call to get message count and if count is 1, get message info
2956 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2957 p_data => x_msg_data,
2958 p_encoded => fnd_api.g_false );
2959
2960 EXCEPTION
2961 WHEN FND_API.G_EXC_ERROR THEN
2962 x_return_status := FND_API.G_RET_STS_ERROR;
2963 Rollback to Process_OP_Details_SP;
2964 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2965 p_data => x_msg_data,
2966 p_encoded => fnd_api.g_false);
2967
2968 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2969 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2970 Rollback to Process_OP_Details_SP;
2971 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2972 p_data => x_msg_data,
2973 p_encoded => fnd_api.g_false);
2974
2975 WHEN OTHERS THEN
2976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2977 Rollback to Process_OP_Details_SP;
2978 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2979 THEN
2980 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2981 p_procedure_name => 'Process_OP_Details_SP',
2982 p_error_text => SUBSTR(SQLERRM,1,240));
2983 END IF;
2984 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2985 p_data => x_msg_data,
2986 p_encoded => fnd_api.g_false);
2987
2988
2989 END Process_OP_Details;
2990
2991
2995 x_return_status OUT NOCOPY VARCHAR2
2992 PROCEDURE UPLOAD_REVISION_REPORT(
2993 p_file_name IN VARCHAR2,
2994 x_file_id OUT NOCOPY NUMBER,
2996 )
2997 IS
2998
2999 l_contentBlob BLOB;
3000 l_inputFilePtr BFILE;
3001 seqNo NUMBER;
3002 l_api_name CONSTANT VARCHAR2(30) := 'UPLOAD_REVISION_REPORT';
3003 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||l_api_name;
3004
3005 BEGIN
3006
3007 x_return_status := FND_API.G_RET_STS_SUCCESS;
3008
3009 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3010 THEN
3011
3012 fnd_log.string
3013 (
3014 fnd_log.level_statement,
3015 l_debug_module,
3016 'inside UPLOAD_REVISION_REPORT->p_file_name->'||p_file_name
3017 );
3018 END IF;
3019
3020
3021 IF p_file_name IS NULL THEN
3022 FND_MESSAGE.set_name( 'AHL','AHL_COM_REQD_PARAM_MISSING' );
3023 FND_MSG_PUB.add;
3024 x_return_status := FND_API.G_RET_STS_ERROR;
3025
3026 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3027 fnd_log.string
3028 (
3029 fnd_log.level_error,
3030 'ahl.plsql.'||g_pkg_name||'.'||'upload_revision_report'||':',
3031 'Revision report file name is null'
3032 );
3033 END IF;
3034 RETURN;
3035 END IF;
3036
3037 l_inputFilePtr := BFILENAME('INPUTDIR', p_file_name);
3038 dbms_lob.open(l_inputFilePtr, dbms_lob.lob_readonly);
3039
3040 dbms_lob.createtemporary(l_contentBlob,TRUE);
3041 dbms_lob.open(l_contentBlob,dbms_lob.lob_readWrite);
3042 dbms_lob.loadfromfile(l_contentBlob,l_inputFilePtr,dbms_lob.getlength(l_inputFilePtr));
3043
3044 dbms_lob.fileclose(l_inputFilePtr);
3045 dbms_lob.close(l_contentBlob);
3046
3047 select fnd_lobs_s.nextval into seqNo from dual;
3048
3049 insert into fnd_lobs(
3050 file_id,
3051 file_name,
3052 file_content_type,
3053 language,
3054 file_data,
3055 file_format,
3056 upload_date
3057 )
3058 values(seqNo,
3059 p_file_name,
3060 'application/octet-stream',
3061 'US',
3062 l_contentBlob,
3063 'binary',
3064 sysdate
3065 );
3066
3067 --File upload is successfull assign file_id to x_file_id output variable
3068 x_file_id := seqNo;
3069
3070
3071 EXCEPTION
3072 WHEN OTHERS THEN
3073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3074 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3075 THEN
3076 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3077 p_procedure_name => 'UPLOAD_REVISION_REPORT',
3078 p_error_text => SUBSTR(SQLERRM,1,240));
3079
3080 END IF;
3081
3082 END UPLOAD_REVISION_REPORT;
3083
3084 END AHL_ENIGMA_ROUTE_OP_PUB;