[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.4.12020000.4 2013/01/03 09:30:43 arunjk ship $ */
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_context IN VARCHAR2,
29 p_pub_date IN DATE
30 );
31
32 -----------------------------------
33 -- Process Operations Details--
34 ------------------------------------
35 PROCEDURE Process_OP_Details
36 (
37 p_api_version IN NUMBER := '1.0',
38 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
39 p_commit IN VARCHAR2 := FND_API.G_FALSE,
40 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
41 p_default IN VARCHAR2 := FND_API.G_FALSE,
42 p_module_type IN VARCHAR2 := NULL,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_msg_data OUT NOCOPY VARCHAR2,
46 p_enigma_op_rec IN enigma_op_rec_type,
47 p_context IN VARCHAR2,
48 p_pub_date IN DATE
49 );
50
51 PROCEDURE upload_revision_report(
52 p_file_name IN VARCHAR2,
53 x_file_id OUT NOCOPY NUMBER,
54 x_return_status OUT NOCOPY VARCHAR2
55 );
56
57
58 -- private function to concatenate multiple error messages into one string
59 FUNCTION Get_Msg_Data(p_msg_count IN NUMBER) RETURN VARCHAR2 IS
60 --
61 l_msg_data VARCHAR2(4000);
62 l_temp_msg_data VARCHAR2(2000);
63 l_msg_index_out NUMBER;
64 l_msg_count NUMBER;
65 --
66
67 BEGIN
68 IF (l_log_procedure >= l_log_current_level) THEN
69 fnd_log.string(fnd_log.level_procedure,'Get_Msg_Data','In Get_msg_data');
70 END IF;
71 l_msg_count := p_msg_count;
72 IF (l_msg_count IS NULL)THEN
73 RETURN NULL;
74 END IF;
75
76 IF (l_msg_count = 1) then
77 FND_MSG_PUB.count_and_get(p_count => l_msg_count,
78 p_data => l_temp_msg_data,
79 p_encoded => FND_API.G_FALSE);
80 l_msg_data := l_temp_msg_data;
81 ELSE
82 IF (l_msg_count > 0) THEN
83 FOR i IN 1..l_msg_count LOOP
84 FND_MSG_PUB.get(
85 p_encoded => FND_API.G_FALSE,
86 p_data => l_temp_msg_data,
87 p_msg_index_out => l_msg_index_out);
88 IF (l_log_procedure >= l_log_current_level) THEN
89 fnd_log.string(fnd_log.level_procedure,'Get_Msg_Data','msg ' || l_temp_msg_data);
90 END IF;
91 IF (i = 1) THEN
92 l_msg_data := l_temp_msg_data;
93 ELSE
94 l_msg_data := l_msg_data || ' | ' || l_temp_msg_data;
95 END IF;
96 END LOOP;
97 END IF;
98 END IF;
99
100 RETURN l_msg_data;
101 END GET_MSG_DATA;
102
103 ------------------------------------------------------------------------------------------------------------------
104 -- Start of Comments --
105 -- Procedure name : Process_Route_Operations
106 -- Type : Public
107 -- Function : Processes the Routes and operations from Enigma.
108 -- Pre-reqs :
109 -- End of Comments.
110 ------------------------------------------------------------------------------------------------------------------
111 PROCEDURE Process_Route_Operations
112 (
113 p_api_version IN NUMBER := 1.0,
114 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
115 p_commit IN VARCHAR2 := FND_API.G_FALSE,
116 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
117 p_module_type IN VARCHAR2,
118 p_context IN VARCHAR2,
119 p_pub_date IN DATE,
120 p_operator IN VARCHAR2,
121 p_model IN VARCHAR2,
122 p_enigma_route_tbl IN enigma_route_tbl_type,
123 p_enigma_op_tbl IN enigma_op_tbl_type,
124 x_return_status OUT NOCOPY VARCHAR2,
125 x_msg_count OUT NOCOPY NUMBER,
126 x_msg_data OUT NOCOPY VARCHAR2
127 )
128 IS
129
130 -- This cursor is used to fetch the details of the routes that exists in the staging table with isrecoverable ='Y'
131 CURSOR get_route_data
132 IS
133 SELECT RT.ENIGMA_ROUTE_ID,
134 RT.ATA_CODE,
135 RT.PDF_FILE_NAME,
136 RT.ENIGMA_DOC_ID,
137 RT.DML_OPERATION,
138 RT.REVISION_DATE,
139 RT.CONTEXT,
140 RT.PUBLISH_DATE,
141 RT.DESCRIPTION
142 FROM AHL_ENIGMA_RT_INTERFACE RT
143 WHERE ISRECOVERABLE = 'Y';
144
145 -- This cursor is used to fetch the details of the operartion that exists in the staging table
146 -- with isrecoverable = 'Y'
147 CURSOR get_op_data
148 IS
149 SELECT OP.ENIGMA_OP_ID,
150 OP.ATA_CODE,
151 OP.ENIGMA_DOC_ID,
152 OP.DML_OPERATION,
153 OP.OPERATOR,
154 OP.EQUIPMENT,
155 OP.OEM_DOC_TYPE,
156 OP.CHAPTER_SECTION_SUBJECT,
157 OP.AMTOSS_FUNCTION,
158 OP.OP_SEQ,
159 OP.CONFIG_LETTER,
160 OP.PUBLISH_DATE,
161 OP.CONTEXT,
162 OP.DESCRIPTION
163 FROM AHL_ENIGMA_OP_INTERFACE OP
164 WHERE ISRECOVERABLE = 'Y';
165
166 -- Declare local variables
167 l_api_name CONSTANT VARCHAR2(30) := 'Process_Route_Operations';
168 l_api_version CONSTANT NUMBER := 1.0;
169 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||l_api_name;
170
171 l_dummy VARCHAR2(1);
172 l_route_exists VARCHAR2(1) := FND_API.G_FALSE;
173 l_enigma_route_id VARCHAR2(80);
174
175
176 l_route_data_rec enigma_route_rec_type;
177 l_op_data_rec enigma_op_rec_type;
178 l_cur_route_data_rec get_route_data%ROWTYPE;
179 l_cur_op_data_rec get_op_data%ROWTYPE;
180
181 -- l_oper_data_tbl enigma_op_tbl_type;
182 -- Enigma Phase II changes start
183 l_ctr NUMBER;
184 i NUMBER;
185 no_records NUMBER;
186 timebefore DATE;
187
188 -- Enigma Phase II changes start
189
190
191 BEGIN
192 -- Standard start of API savepoint
193 --SAVEPOINT Process_Route_Operations_SP;
194
195 -- Initialize return status to success before any code logic/validation
196 x_return_status:= FND_API.G_RET_STS_SUCCESS;
197
198 -- Standard call to check for call compatibility
199 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
201 END IF;
202
203 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
204 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
205 FND_MSG_PUB.INITIALIZE;
206 END IF;
207
208 -- initialise it only if it is a OA Adapter !
209 -- Apps initialise does a commit which causes commit/rollback related issues discussed.
210 -- Hence commenting out this piece of code.
211 --fnd_global.APPS_INITIALIZE (1003259,62211, 867);
212
213 -- Log API entry point
214 IF (l_log_procedure >= l_log_current_level) THEN
215 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.begin','At the start of PL SQL procedure ');
216 END IF;
217
218 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
219 fnd_log.string(fnd_log.level_procedure,l_debug_module ||'.begin','Inside Process_Route_Operations');
220 END IF;
221
222 -- Check if the route is found in the staging table
223 -- Then , query and get all the routes from the staging table which are in status pending.
224 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
225 fnd_log.string(fnd_log.level_statement,l_debug_module,'Polling for pending routes');
226 END IF;
227
228 --snarkhed::ENigma Phase II change in the interface logic
229 SELECT sysdate INTO timebefore from dual;
230 OPEN get_op_data;
231 LOOP
232 FETCH get_op_data INTO l_cur_op_data_rec;
233 EXIT WHEN get_op_data%NOTFOUND;
234 l_op_data_rec.enigma_op_id := l_cur_op_data_rec.enigma_op_id;
235 l_op_data_rec.ata_code := l_cur_op_data_rec.ata_code;
236 l_op_data_rec.description := l_cur_op_data_rec.description;
237 l_op_data_rec.enigma_doc_id := l_cur_op_data_rec.enigma_doc_id;
238 l_op_data_rec.dml_operation := l_cur_op_data_rec.dml_operation;
239 l_op_data_rec.operator := l_cur_op_data_rec.operator;
240 l_op_data_rec.equipment := l_cur_op_data_rec.equipment;
241 l_op_data_rec.oem_doc_type := l_cur_op_data_rec.oem_doc_type;
242 l_op_data_rec.chapter_section_subject := l_cur_op_data_rec.chapter_section_subject;
243 l_op_data_rec.amtoss_function := l_cur_op_data_rec.amtoss_function;
244 l_op_data_rec.op_seq := l_cur_op_data_rec.op_seq;
245 l_op_data_rec.config_letter := l_cur_op_data_rec.config_letter;
246
247 Process_OP_Details
248 (
249 '1.0',
250 FND_API.G_TRUE,
251 FND_API.G_FALSE,
252 FND_API.G_VALID_LEVEL_FULL,
253 FND_API.G_FALSE,
254 p_module_type,--NULL Sthilak for bug #14036337,
255 x_return_status,
256 x_msg_count,
257 x_msg_data,
258 l_op_data_rec,
259 l_cur_op_data_rec.context,
260 l_cur_op_data_rec.publish_date
261 );
262 END LOOP;
263 CLOSE get_op_data;
264
265 DELETE FROM AHL_ENIGMA_OP_INTERFACE
266 WHERE ISRECOVERABLE = 'Y'
267 AND PROCESS_DATE <= timebefore;
268
269 SELECT sysdate INTO timebefore from dual;
270 OPEN get_route_data;
271 LOOP
272 FETCH get_route_data INTO l_cur_route_data_rec;
273 EXIT WHEN get_route_data%NOTFOUND;
274 l_route_data_rec.enigma_route_id := l_cur_route_data_rec.enigma_route_id;
275 l_route_data_rec.ata_code := l_cur_route_data_rec.ata_code;
276 l_route_data_rec.description := l_cur_route_data_rec.description;
277 l_route_data_rec.enigma_doc_id := l_cur_route_data_rec.enigma_doc_id;
278 l_route_data_rec.dml_operation := l_cur_route_data_rec.dml_operation;
279 l_route_data_rec.revision_date := l_cur_route_data_rec.revision_date;
280 l_route_data_rec.pdf_file_name := l_cur_route_data_rec.pdf_file_name;
281
282 PROCESS_ROUTE_DETAILS
283 (
284 '1.0',
285 FND_API.G_TRUE,
286 FND_API.G_FALSE,
287 FND_API.G_VALID_LEVEL_FULL,
288 FND_API.G_FALSE,
289 p_module_type,--NULL Sthilak for bug #14036337,
290 x_return_status,
291 x_msg_count,
292 x_msg_data,
293 l_route_data_rec,
294 l_cur_route_data_rec.context,
295 l_cur_route_data_rec.publish_date
296 );
297 END LOOP;
298 CLOSE get_route_data;
299
300 DELETE FROM AHL_ENIGMA_RT_INTERFACE
301 WHERE ISRECOVERABLE = 'Y'
302 AND PROCESS_DATE <= timebefore;
303
304
305 --Snarkhed Enigma Phase II --Interface Table Use Logic Need to be modified
306
307
308 /*OPEN get_route_data;
309 LOOP
310 --Enigma Phase II changes Start
311 FETCH get_route_data INTO l_route_data_rec.enigma_route_id,
312 l_route_data_rec.status,
313 l_route_data_rec.ata_code,
314 l_route_data_rec.description,
315 l_route_data_rec.revision_date,
316 l_route_data_rec.ENIGMA_DOC_ID,
317 l_route_data_rec.DML_OPERATION,
318 l_route_data_rec.PDF_FILE_NAME;
319 --Phase II changes end
320
321 EXIT WHEN get_route_data%NOTFOUND;
322
323 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
324 THEN
325 fnd_log.string
326 (
327 fnd_log.level_statement,
328 l_debug_module,
329 'Pending Routes found in staging table'
330 );
331 END IF;
332
333 l_enigma_route_id := l_route_data_rec.enigma_route_id;
334
335 -- Check if the route has any associated operations.
336 -- If so collect them into l_oper_data_tbl .
337
338 -- One route can have many operations associated to it.So building operation table.
339 l_ctr := 1;
340 OPEN get_oper_data (l_enigma_route_id);
341 LOOP
342 FETCH get_oper_data INTO l_route_data_rec.op_asso_tbl(l_ctr);
343 EXIT WHEN get_oper_data%NOTFOUND;
344 l_ctr := l_ctr + 1;
345 END LOOP;
346 CLOSE get_oper_data;
347 -- Enigma Phase II changes end
348
349 IF (l_log_statement >= l_log_current_level) THEN
350 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling PROCESS_ROUTE_DETAILS');
351 END IF;
352
353 -- Delete all the routes in pending status from the stagin table
354 -- as they have already been queried by the cursor
355 -- moving this code out side of loop
356
357 -- Delete all the operations from the stagin table that are in the pending status
358 -- and that correspond to the parent route
359
360 DELETE FROM AHL_RT_OPER_INTERFACE
361 WHERE PARENT_ENIGMA_ROUTE_ID = l_enigma_route_id AND STATUS = 'PENDING';
362
363 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
364 THEN
365 fnd_log.string
366 (
367 fnd_log.level_statement,
368 l_debug_module,
369 'before calling process_route_details'
370 );
371 fnd_log.string
372 (
373 fnd_log.level_statement,
374 l_debug_module,
375 'l_route_data_rec.enigma_route_id -> '|| l_route_data_rec.enigma_route_id
376 );
377 END IF;
378
379 -- Calling the procedure to process the route and operation details
380 PROCESS_ROUTE_DETAILS
381 (
382 '1.0',
383 FND_API.G_TRUE,
384 FND_API.G_FALSE,
385 FND_API.G_VALID_LEVEL_FULL,
386 FND_API.G_FALSE,
387 NULL,
388 x_return_status,
389 x_msg_count,
390 x_msg_data,
391 l_route_data_rec,
392 -- l_oper_data_tbl,
393 p_context,
394 p_pub_date
395 );
396
397 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
398 THEN
399 fnd_log.string
400 (
401 fnd_log.level_statement,
402 l_debug_module,
403 'after calling process_route_details'
404 );
405 END IF;
406 END LOOP;
407 CLOSE get_route_data;
408
409 -- Delete the pending route records processed
410 DELETE FROM AHL_RT_OPER_INTERFACE
411 WHERE STATUS = 'PENDING' AND PARENT_enigma_route_id IS NULL;*/
412
413
414 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
415 fnd_log.string(fnd_log.level_statement,l_debug_module,'Dumping all the paramters...');
416 fnd_log.string(fnd_log.level_statement,l_debug_module,'p_api_version -> '||p_api_version);
417 fnd_log.string(fnd_log.level_statement,l_debug_module,'p_init_msg_list -> '||p_init_msg_list);
418 fnd_log.string(fnd_log.level_statement,l_debug_module,'p_commit -> '||p_commit);
419 fnd_log.string(fnd_log.level_statement,l_debug_module,'p_validation_level -> '||p_validation_level);
420 fnd_log.string(fnd_log.level_statement,l_debug_module,'p_module_type -> '||p_module_type);
421 fnd_log.string(fnd_log.level_statement,l_debug_module,'p_context -> '||p_context);
422 fnd_log.string(fnd_log.level_statement,l_debug_module,'p_pub_date -> '||p_pub_date);
423 END IF;
424
425 -- Process the incoming enigma record.
426 -- Validate if the Enigma Route Id is null throw an error if the id is null.
427 -- IF (p_enigma_route_rec.enigma_route_id IS NULL OR p_enigma_route_rec.enigma_route_id = FND_API.G_MISS_CHAR)
428 -- THEN
429 -- FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_ID_NULL');
430 -- FND_MSG_PUB.ADD;
431 -- RAISE FND_API.G_EXC_ERROR;
432 -- END IF;
433
434 no_records := 0;
435
436 --Either of the route rec or operation record will not be null.So check which record is not null
437 --and call the appropriate api.
438
442 IF(l_op_data_rec.dml_operation ='N') THEN
439 FOR i IN 1..p_enigma_op_tbl.COUNT
440 LOOP
441 l_op_data_rec:=p_enigma_op_tbl(i);
443 l_op_data_rec.dml_operation := 'C';
444 END IF;
445 l_op_data_rec.operator := p_operator;
446 l_op_data_rec.equipment:= p_model;
447 Process_OP_Details
448 (
449 '1.0',
450 FND_API.G_TRUE,
451 FND_API.G_FALSE,
452 FND_API.G_VALID_LEVEL_FULL,
453 FND_API.G_FALSE,
454 p_module_type,--NULL Sthilak for bug #14036337,
455 x_return_status,
456 x_msg_count,
457 x_msg_data,
458 l_op_data_rec,
459 p_model, -- In phase II p_model is model code.
460 p_pub_date
461 );
462 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS OR x_msg_count > 0) THEN
463 no_records := no_records +1;
464 END IF;
465 -- x_msg_count := FND_MSG_PUB.count_msg;
466 -- IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS OR x_msg_count > 0) THEN
467 -- IF (l_log_statement >= l_log_current_level) THEN
468 -- fnd_log.string(fnd_log.level_statement,l_debug_module,'Error in Process_OP_Details');
469 -- END IF;
470 -- RAISE FND_API.G_EXC_ERROR;
471 -- END IF;
472 -- END IF;
473 END LOOP;
474
475 FOR i IN 1..p_enigma_route_tbl.COUNT
476 LOOP
477 l_route_data_rec:=p_enigma_route_tbl(i);
478 IF(l_route_data_rec.dml_operation ='N') THEN
479 l_route_data_rec.dml_operation := 'C';
480 END IF;
481
482 PROCESS_ROUTE_DETAILS
483 (
484 '1.0',
485 FND_API.G_TRUE,
486 FND_API.G_FALSE,
487 FND_API.G_VALID_LEVEL_FULL,
488 FND_API.G_FALSE,
489 p_module_type,--NULL Sthilak for bug #14036337,
490 x_return_status,
491 x_msg_count,
492 x_msg_data,
493 l_route_data_rec,
494 p_model, -- In phase II p_model will be used as model.
495 p_pub_date
496 );
497 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS OR x_msg_count > 0) THEN
498 no_records := no_records +1;
499 END IF;
500 -- x_msg_count := FND_MSG_PUB.count_msg;
501 -- IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
502 -- IF (l_log_statement >= l_log_current_level OR x_msg_count > 0) THEN
503 -- fnd_log.string(fnd_log.level_statement,l_debug_module,'Error in PROCESS_ROUTE_DETAILS');
504 -- END IF;
505 -- RAISE FND_API.G_EXC_ERROR;
506 -- END IF;
507 END LOOP;
508
509 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
510 fnd_log.string(fnd_log.level_statement,l_debug_module,'After process calls...');
511 END IF;
512
513
514 -- Check Error Message stack.
515 -- x_msg_count := FND_MSG_PUB.count_msg;
516
517 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
518 fnd_log.string(fnd_log.level_statement,l_debug_module,'After get_message_data... ' || x_msg_data);
519 END IF;
520
521 IF (no_records > 0) THEN
522 x_return_status := FND_API.G_RET_STS_ERROR;
523 x_msg_data := to_char(no_records) || ' Records Encountered An Error During Execution.' ;
524 END IF;
525
526 -- IF x_msg_count > 0 THEN
527 -- RAISE FND_API.G_EXC_ERROR;
528 -- END IF;
529
530 -- Standard check for p_commit
531 -- IF FND_API.To_Boolean (p_commit)THEN
532 -- COMMIT WORK;
533 -- END IF;
534
535 -- Standard call to get message count and if count is 1, get message info
536 -- FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
537 -- p_data => x_msg_data,
538 -- p_encoded => fnd_api.g_false );
539
540 EXCEPTION
541 WHEN FND_API.G_EXC_ERROR THEN
542 x_return_status := FND_API.G_RET_STS_ERROR;
543 --Rollback to Process_Route_Operations_SP;
544 x_msg_count := FND_MSG_PUB.count_msg;
545 x_msg_data := Get_Msg_Data(x_msg_count);
546
547 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 --Rollback to Process_Route_Operations_SP;
550 x_msg_count := FND_MSG_PUB.count_msg;
551 x_msg_data := Get_Msg_Data(x_msg_count);
552
553 WHEN OTHERS THEN
554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
555 --Rollback to Process_Route_Operations_SP;
556 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
557 THEN
558 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
559 p_procedure_name => 'Process_Route_Operations',
560 p_error_text => SUBSTR(SQLERRM,1,240));
561 END IF;
562 x_msg_count := FND_MSG_PUB.count_msg;
563 x_msg_data := Get_Msg_Data(x_msg_count);
564
565 END Process_Route_Operations;
566
567 PROCEDURE Map_Enigma_To_CMRO_Route_Rec
568 (
569 p_enigma_route_rec IN enigma_route_rec_type,
570 x_process_route_input_rec OUT NOCOPY AHL_RM_ROUTE_PVT.route_rec_type,
571 p_route_id IN NUMBER,
572 p_object_version_number IN NUMBER,
573 p_context IN VARCHAR2,
574 p_pub_date IN DATE
575 )
576 IS
577 X_file_id NUMBER;
578 x_return_status VARCHAR2(1);
579 l_debug_module CONSTANT VARCHAR2(100) := 'AHL.PLSQL.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||'MAP_ENIGMA_TO_CMRO_ROUTE_REC';
580
581 BEGIN
582 IF (l_log_statement >= l_log_current_level) THEN
583 fnd_log.string(fnd_log.level_statement,l_debug_module,'In Map_Enigma_To_CMRO_Route_Rec');
584 END IF;
585 IF (p_enigma_route_rec.dml_operation <> 'C' AND p_route_id <> FND_API.G_MISS_NUM AND p_route_id IS NOT NULL) THEN
586 x_process_route_input_rec.route_id := p_route_id;
587 END IF;
588 IF (p_enigma_route_rec.dml_operation <> 'C' AND p_object_version_number <> FND_API.G_MISS_NUM AND p_object_version_number IS NOT NULL) THEN
592 -- Call the procedure to upload the file
589 x_process_route_input_rec.object_version_number := p_object_version_number;
590 END IF;
591 IF p_enigma_route_rec.pdf_file_name IS NOT NULL THEN
593 UPLOAD_REVISION_REPORT
594 (
595 p_enigma_route_rec.pdf_file_name,
596 x_file_id,
597 x_return_status
598 );
599 END IF;
600 IF (l_log_statement >= l_log_current_level) THEN
601 fnd_log.string(fnd_log.level_statement,l_debug_module,'After UPLOAD_REVISION_REPORT Call File Id -> = ' || x_file_id);
602 END IF;
603 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
604 IF (l_log_statement >= l_log_current_level) THEN
605 fnd_log.string(fnd_log.level_statement,l_debug_module,'UPLOAD_REVISION_REPORT Error');
606 END IF;
607 RAISE FND_API.G_EXC_ERROR;
608 END IF;
609 -- If the return status is success, populate the input rec for process_route for updation
610 IF ( x_file_id <> FND_API.G_MISS_NUM AND x_file_id IS NOT NULL) THEN
611 x_process_route_input_rec.file_id := x_file_id ;
612 END IF;
613 IF (p_context <> FND_API.G_MISS_CHAR AND p_context IS NOT NULL ) THEN
614 x_process_route_input_rec.model_code := p_context;
615 END IF;
616 IF (p_pub_date <> FND_API.G_MISS_DATE AND p_pub_date IS NOT NULL ) THEN
617 x_process_route_input_rec.enigma_publish_date := p_pub_date ;
618 END IF;
619 IF (p_enigma_route_rec.description <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.description IS NOT NULL ) THEN
620 x_process_route_input_rec.title := p_enigma_route_rec.description;
621 ELSIF p_enigma_route_rec.dml_operation = 'C' THEN
622 x_process_route_input_rec.title := p_enigma_route_rec.ata_code;
623 END IF;
624 IF (p_enigma_route_rec.revision_date <> FND_API.G_MISS_DATE AND p_enigma_route_rec.revision_date IS NOT NULL ) THEN
625 x_process_route_input_rec.active_start_date := p_enigma_route_rec.revision_date;
626 ELSIF p_enigma_route_rec.dml_operation = 'C' THEN
627 x_process_route_input_rec.active_start_date := sysdate;
628 END IF;
629 IF (p_enigma_route_rec.enigma_doc_id <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.enigma_doc_id IS NOT NULL ) THEN
630 x_process_route_input_rec.enigma_doc_id := p_enigma_route_rec.enigma_doc_id;
631 END IF;
632 IF (p_enigma_route_rec.enigma_route_id <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.enigma_route_id IS NOT NULL ) THEN
633 x_process_route_input_rec.enigma_route_id := p_enigma_route_rec.enigma_route_id;
634 END IF;
635 IF (p_enigma_route_rec.dml_operation = 'C' AND p_enigma_route_rec.ata_code <> FND_API.G_MISS_CHAR AND p_enigma_route_rec.ata_code IS NOT NULL ) THEN
636 x_process_route_input_rec.route_no := p_enigma_route_rec.ata_code;
637 END IF;
638 x_process_route_input_rec.dml_operation := p_enigma_route_rec.dml_operation;
639 IF (l_log_statement >= l_log_current_level) THEN
640 fnd_log.string(fnd_log.level_statement,l_debug_module,'End of Map_Enigma_To_CMRO_Route_Rec');
641 END IF;
642 END Map_Enigma_To_CMRO_Route_Rec;
643
644 PROCEDURE Update_Route_Interface_table
645 (
646 p_enigma_route_rec IN enigma_route_rec_type,
647 p_context IN VARCHAR2,
648 p_pub_date IN DATE,
649 p_isrecoverable IN VARCHAR2,
650 p_reason IN VARCHAR2
651 )
652 IS
653 l_debug_module CONSTANT VARCHAR2(100) := 'AHL.PLSQL.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||'Update_Route_Interface_table';
654
655 BEGIN
656 IF (l_log_statement >= l_log_current_level) THEN
657 fnd_log.string(fnd_log.level_statement,l_debug_module,'Before Inserting into interface table');
658 fnd_log.string(fnd_log.level_statement,l_debug_module,' reason: ' || p_reason);
659 END IF;
660
661 INSERT INTO AHL_ENIGMA_RT_INTERFACE
662 (
663 CONTEXT,
664 PUBLISH_DATE,
665 ENIGMA_ROUTE_ID,
666 PDF_FILE_NAME,
667 REVISION_DATE,
668 DML_OPERATION,
669 ATA_CODE,
670 DESCRIPTION,
671 ERROR_MESSAGE,
672 ENIGMA_DOC_ID,
673 PROCESS_DATE,
674 ISRECOVERABLE
675 )
676 VALUES
677 (
678 p_context,
679 p_pub_date,
680 p_enigma_route_rec.enigma_route_id,
681 p_enigma_route_rec.pdf_file_name,
682 p_enigma_route_rec.revision_date,
683 p_enigma_route_rec.dml_operation,
684 p_enigma_route_rec.ata_code,
685 p_enigma_route_rec.description,
686 p_reason,
687 p_enigma_route_rec.enigma_doc_id,
688 sysdate,
689 p_isrecoverable
690 );
691 IF (l_log_statement >= l_log_current_level) THEN
692 fnd_log.string(fnd_log.level_statement,l_debug_module,'After Inserting into interface table');
693 END IF;
694 END Update_Route_Interface_table;
695
696 PROCEDURE process_rt_op_assoc
697 (
698 x_return_status OUT NOCOPY VARCHAR2,
699 x_msg_count OUT NOCOPY NUMBER,
700 x_msg_data OUT NOCOPY VARCHAR2,
701 p_enigma_op_asso_tbl IN route_op_asso_tbl_type,
702 p_route_id IN NUMBER
703 )
704 IS
705
706 CURSOR get_associated_operations
707 IS
708 SELECT route_operation_id,operation_id,object_version_number
709 FROM ahl_route_operations
710 WHERE route_id = p_route_id;
711
712 CURSOR get_op_id(p_enigma_op_id VARCHAR2)
713 IS
714 SELECT operation_id
715 FROM ahl_operations_b
716 WHERE enigma_op_id = p_enigma_op_id
717 AND TRUNC(NVL(end_date_active, sysdate + 1)) > TRUNC(sysdate)
718 AND (revision_status_code = 'COMPLETE' OR revision_status_code='DRAFT') ;
719
720 CURSOR get_max_step
721 IS
722 SELECT NVL(max(step),0)
723 FROM ahl_route_operations
724 WHERE route_id = p_route_id;
725
726 l_asso_id NUMBER;
727 l_operation_id NUMBER;
728 l_ovn NUMBER;
729 l_old_ctr NUMBER;
730 l_new_ctr NUMBER;
731 l_tbl_count NUMBER;
732 l_ctr NUMBER;
733 l_last_ctr NUMBER;
734 l_step NUMBER;
735 l_route_op_asso_table AHL_RM_OP_ROUTE_AS_PVT.route_operation_tbl_type;
736 l_debug_module CONSTANT VARCHAR2(100) := 'AHL.PLSQL.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||'PROCESS_RT_OP_ASSOC';
737 l_flag BOOLEAN;
738 l_upper_bound NUMBER;
739
740 TYPE ASSO_RECORD IS RECORD (
741 assoc_id NUMBER,
742 op_id NUMBER,
743 ovn NUMBER,
744 change NUMBER
745 );
746 -- change = 0 : Delete
747 -- change = 1 : Retained
748 -- change = 2 : New Addition
749
750 TYPE ASSO_RECORD_TABLE_TYPE IS TABLE OF ASSO_RECORD
751 INDEX BY BINARY_INTEGER;
752
753 TYPE cmro_op_id_table_type IS TABLE OF NUMBER
754 INDEX BY BINARY_INTEGER;
755
756 new_op_assos_table cmro_op_id_table_type; -- This table contains CMRO operation id for the operations passed for association
757 old_op_assos_table asso_record_table_type;
758
759 BEGIN
760 x_return_status:= FND_API.G_RET_STS_SUCCESS;
761 IF (l_log_statement >= l_log_current_level) THEN
762 fnd_log.string(fnd_log.level_statement,l_debug_module,'In process_rt_op_assoc Route Id ' || p_route_id);
763 END IF;
764 -- change = 0 : Delete
765 -- change = 1 : Retained
766 -- change = 2 : New Addition
767
768 -- Get the existing associations details created through enigma.
769 l_old_ctr := 1;
770 OPEN get_associated_operations;
771 LOOP
772 FETCH get_associated_operations INTO l_asso_id,l_operation_id,l_ovn;
773 EXIT WHEN get_associated_operations%NOTFOUND;
774 old_op_assos_table(l_old_ctr).assoc_id := l_asso_id;
775 old_op_assos_table(l_old_ctr).op_id := l_operation_id;
776 old_op_assos_table(l_old_ctr).ovn := l_ovn;
777 old_op_assos_table(l_old_ctr).change := 0;
778 l_old_ctr := l_old_ctr +1;
779 END LOOP;
780 CLOSE get_associated_operations;
781
782 -- For each enigma op_id get operation_id of all revisions of operation which are active
783 l_ctr :=1;
784 FOR l_new_ctr IN 1..p_enigma_op_asso_tbl.COUNT
785 LOOP
786 l_last_ctr := l_ctr;
787 -- For Each enigma_op_id store operation id's of all revisions which can be associated
788 OPEN get_op_id(p_enigma_op_asso_tbl(l_new_ctr));
789 LOOP
790 FETCH get_op_id INTO l_operation_id;
791 EXIT WHEN get_op_id%NOTFOUND;
792 new_op_assos_table(l_ctr) := l_operation_id;
793 l_ctr := l_ctr + 1;
794 END LOOP;
795 CLOSE get_op_id;
796 -- If no operation found with the passed enigma_op_id then throw an error.
797 IF l_last_ctr = l_ctr THEN
798 IF (l_log_statement >= l_log_current_level) THEN
799 fnd_log.string(fnd_log.level_statement,l_debug_module,'Associated Operation Not Found:: ' || p_enigma_op_asso_tbl(l_new_ctr));
800 END IF;
801 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OP_DONOT_EXIST');
802 FND_MESSAGE.SET_TOKEN('FIELD', p_enigma_op_asso_tbl(l_new_ctr));
803 FND_MSG_PUB.ADD;
804 RAISE FND_API.G_EXC_ERROR;
805 END IF;
806 END LOOP;
807
808 -- Determine which associations are to be retained,which to be deleted and which are new associations
809 l_upper_bound := old_op_assos_table.COUNT;
810 FOR l_new_ctr IN 1..new_op_assos_table.COUNT
811 LOOP
812 l_flag := TRUE;
813 FOR l_old_ctr IN 1..l_upper_bound
814 LOOP
815 IF new_op_assos_table(l_new_ctr) = old_op_assos_table(l_old_ctr).op_id THEN
816 l_flag := FALSE;
817 old_op_assos_table(l_old_ctr).change := 1;
818 EXIT;
819 END IF;
820 END LOOP;
821 IF l_flag = TRUE THEN
822 l_tbl_count := old_op_assos_table.COUNT + 1;
823 old_op_assos_table(l_tbl_count).op_id := new_op_assos_table(l_new_ctr);
824 old_op_assos_table(l_tbl_count).change := 2;
825 END IF;
826 END LOOP;
827
828 --Get Max Step to generate step for new records
829 OPEN get_max_step;
830 FETCH get_max_step INTO l_step;
831 CLOSE get_max_step;
832
833 l_ctr :=1;
834
835 --populate the record to pass to process_route_operations_as API.
836 FOR l_old_ctr IN 1..old_op_assos_table.COUNT
837 LOOP
838 --populating the record for delete
839 IF (old_op_assos_table(l_old_ctr).change = 0) THEN
840 l_route_op_asso_table(l_ctr).route_operation_id :=old_op_assos_table(l_old_ctr).assoc_id;
841 l_route_op_asso_table(l_ctr).object_version_number :=old_op_assos_table(l_old_ctr).ovn;
842 l_route_op_asso_table(l_ctr).operation_id :=old_op_assos_table(l_old_ctr).op_id;
843 l_route_op_asso_table(l_ctr).dml_operation :='D';
844 l_ctr := l_ctr + 1;
845
846 --populating the record for create
847 ELSIF(old_op_assos_table(l_old_ctr).change = 2) THEN
848 l_route_op_asso_table(l_ctr).operation_id := old_op_assos_table(l_old_ctr).op_id;
849 l_route_op_asso_table(l_ctr).step := l_step +1;
850 l_route_op_asso_table(l_ctr).dml_operation :='C';
851 l_route_op_asso_table(l_ctr).check_point_flag :='N';
852 l_step := l_step + 1;
853 l_ctr := l_ctr + 1;
854 END IF;
855 END LOOP;
856
857 IF (l_log_statement >= l_log_current_level) THEN
858 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling private API. Count:: ' || l_route_op_asso_table.COUNT);
859 END IF;
860
861 IF (l_route_op_asso_table.COUNT > 0) THEN
862 AHL_RM_OP_ROUTE_AS_PVT.process_route_operation_as
863 (
864 p_api_version => 1.0,
865 p_init_msg_list => FND_API.G_TRUE,
866 p_commit => FND_API.G_FALSE,
867 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
868 p_default => FND_API.G_TRUE,
869 p_module_type => 'BPEL',--NULL Sthilak for bug #14036337,,
870 x_return_status => x_return_status,
871 x_msg_count => x_msg_count ,
872 x_msg_data => x_msg_data ,
873 p_x_route_operation_tbl => l_route_op_asso_table,
874 p_route_id => p_route_id
875 );
876 END IF;
877
878 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
879 IF (l_log_statement >= l_log_current_level) THEN
880 fnd_log.string(fnd_log.level_statement,l_debug_module,'Error in Process Association');
881 END IF;
882 RAISE FND_API.G_EXC_ERROR;
883 END IF;
884
885 EXCEPTION
886 WHEN FND_API.G_EXC_ERROR THEN
887 x_return_status := FND_API.G_RET_STS_ERROR;
888 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
889 p_data => x_msg_data,
890 p_encoded => fnd_api.g_false);
891
892 WHEN OTHERS THEN
893 x_return_status := FND_API.G_RET_STS_ERROR;
894 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
895 p_data => x_msg_data,
896 p_encoded => fnd_api.g_false);
897
898 END process_rt_op_assoc;
899
900 PROCEDURE Process_Route_Details
901 (
902 p_api_version IN NUMBER := '1.0',
903 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
904 p_commit IN VARCHAR2 := FND_API.G_FALSE,
905 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
906 p_default IN VARCHAR2 := FND_API.G_FALSE,
907 p_module_type IN VARCHAR2 := NULL,
908 x_return_status OUT NOCOPY VARCHAR2,
909 x_msg_count OUT NOCOPY NUMBER,
910 x_msg_data OUT NOCOPY VARCHAR2,
911 p_enigma_route_rec IN enigma_route_rec_type,
912 p_context IN VARCHAR2,
913 p_pub_date IN DATE
914 )
915 IS
916
917
918 --pekambar changes for bug # 9004971 --start
919 l_eng_count NUMBER;
920 --pekambar changes for bug # 9004971 --end
921
922 CURSOR get_latest_route_rev (p_enigma_route_id VARCHAR2)
923 IS
924 SELECT route_id,object_version_number,revision_status_code revision_status
925 FROM AHL_ROUTES_B
926 WHERE UPPER(TRIM(ENIGMA_ROUTE_ID)) = UPPER(TRIM(p_enigma_route_id))
927 AND REVISION_NUMBER =(
928 SELECT MAX( revision_number )
929 FROM AHL_ROUTES_B
930 WHERE UPPER(TRIM(ENIGMA_ROUTE_ID)) = UPPER(TRIM(p_enigma_route_id)));
931
932 --pekambar changes for bug # 9004971 --start
933
934 CURSOR validate_enig_route(p_enigma_route_id VARCHAR2)
935 IS
936 SELECT count(*)
937 FROM AHL_ROUTES_B
938 WHERE ENIGMA_ROUTE_ID = p_enigma_route_id;
939 --pekambar changes for bug # 9004971 --end
940
941 -- Declare local variables
942 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ROUTE_DETAILS';
943 l_api_version CONSTANT NUMBER := 1.0;
944 l_debug_module CONSTANT VARCHAR2(100) := 'AHL.PLSQL.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||'PROCESS_ROUTE_DETAILS';
945
946 x_route_id NUMBER;
947 l_ovn NUMBER;
948 x_file_id NUMBER;
949 isrecoverable VARCHAR2(1);
950
951 l_get_latest_route_rev get_latest_route_rev%ROWTYPE;
952 p_process_route_input_rec AHL_RM_ROUTE_PVT.route_rec_type;
953
954
955 BEGIN
956 -- Standard start of API savepoint
957 SAVEPOINT Process_Route_Details_SP;
958
959 -- Initialize return status to success before any code logic/validation
960 x_return_status:= FND_API.G_RET_STS_SUCCESS;
961
962 -- Standard call to check for call compatibility
963 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)THEN
964 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
965 END IF;
966
967 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
968 IF FND_API.TO_BOOLEAN(p_init_msg_list)THEN
969 FND_MSG_PUB.INITIALIZE;
970 END IF;
971
972 -- Log API entry point
973 IF (l_log_procedure >= l_log_current_level) THEN
974 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.begin','At the start of PL SQL procedure ');
975 END IF;
976 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
977 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling from BPEL service Parameters p_enigma_route_rec.enigma_route_id -> '||p_enigma_route_rec.enigma_route_id);
978 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling from BPEL service Parameters p_enigma_route_rec.DML_OPERATION -> '||p_enigma_route_rec.DML_OPERATION);
979 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling from BPEL service Parameters p_enigma_route_rec.ATA_CODE-> '||p_enigma_route_rec.ATA_CODE);
980 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling from BPEL service Parameters p_enigma_route_rec.DESCRIPTION-> '||p_enigma_route_rec.DESCRIPTION);
981 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling from BPEL service Parameters p_enigma_route_rec.REVISION_DATE-> '||p_enigma_route_rec.REVISION_DATE);
982 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling from BPEL service Parameters p_enigma_route_rec.PDF_FILE_NAME-> '||p_enigma_route_rec.PDF_FILE_NAME);
983 END IF;
984
985 -- validations::Verify if the Change flag is right
986 IF (p_enigma_route_rec.DML_OPERATION NOT IN ( 'C', 'D', 'U','NC'))
987 THEN
988 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_DML');
989 FND_MESSAGE.SET_TOKEN('FIELD', p_enigma_route_rec.DML_OPERATION);
990 FND_MESSAGE.SET_TOKEN('RECORD', p_enigma_route_rec.enigma_route_id);
991 FND_MSG_PUB.ADD;
992 RAISE FND_API.G_EXC_ERROR;
993 END IF;
994
995 -- IF (p_enigma_route_rec.enigma_route_id IS NULL OR p_enigma_route_rec.enigma_route_id = FND_API.G_MISS_CHAR)
996 -- THEN
997 -- FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_ID_NULL');
998 -- FND_MSG_PUB.ADD;
999 -- RAISE FND_API.G_EXC_ERROR;
1000 -- END IF;
1001 --if DML_OPERATION <> c then validate that route with this enigma_route_id exists.
1002 IF (p_enigma_route_rec.DML_OPERATION <> 'C') THEN
1003 OPEN get_latest_route_rev (p_enigma_route_rec.enigma_route_id);
1004 FETCH get_latest_route_rev
1005 INTO l_get_latest_route_rev.route_id,
1006 l_get_latest_route_rev.object_version_number,
1007 l_get_latest_route_rev.revision_status;
1008 IF get_latest_route_rev%NOTFOUND THEN
1009 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_DONOT_EXIST');
1010 FND_MESSAGE.SET_TOKEN('FIELD', p_enigma_route_rec.enigma_route_id);
1011 FND_MSG_PUB.ADD;
1012 CLOSE get_latest_route_rev;
1013 RAISE FND_API.G_EXC_ERROR;
1014 END IF;
1015 CLOSE get_latest_route_rev;
1016 END IF;
1017
1018 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1019 fnd_log.string(fnd_log.level_statement,l_debug_module,'Route Latest Revision Details..');
1020 fnd_log.string(fnd_log.level_statement,l_debug_module,'Route Latest Revision Details..Route Id: ' || l_get_latest_route_rev.route_id ||
1021 ' Revision Status ' || l_get_latest_route_rev.revision_status ||
1022 ' DML_FLAG ' || p_enigma_route_rec.DML_OPERATION ||
1023 ' OVN ' || l_get_latest_route_rev.object_version_number
1024 );
1025 END IF;
1026 --New DML_OPERATION Value Added for Enigma Phase II
1027 --New Value: 'NC'
1028 --Functionality:IF DML_OPERATION = 'NC' Then Only pdf will be updated for the route.
1029 --The Pvt API,AHL_RM_ROUTE_PVT.process_route expects DML_OPERATION To be VARCHAR2(1),
1030 --So from this public wrapper,DML_OPERATION will be passed as 'N' to that api
1031 IF (p_enigma_route_rec.DML_OPERATION = 'NC') THEN
1032 -- IF the route is in Approval Pending status , then insert the Enigma Record into the staging table with status as pending.
1033 IF ( upper(l_get_latest_route_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
1034 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_APR_PENDING');
1035 FND_MSG_PUB.ADD;
1036 RAISE FND_API.G_EXC_ERROR;
1037 /*Update_Route_Interface_table
1038 (
1039 p_enigma_route_rec,
1040 p_context,
1041 p_pub_date,
1042 'Y',
1043 'Route is in approval pending status'
1044 );*/
1045 ELSE
1046 p_process_route_input_rec.object_version_number :=l_get_latest_route_rev.object_version_number;
1047 p_process_route_input_rec.route_id := l_get_latest_route_rev.route_id;
1048
1049 IF p_enigma_route_rec.pdf_file_name IS NOT NULL THEN
1050 -- Call the procedure to upload the file
1051 UPLOAD_REVISION_REPORT
1052 (
1053 p_enigma_route_rec.pdf_file_name,
1054 x_file_id,
1055 x_return_status
1056 );
1057 END IF;
1058 IF (l_log_statement >= l_log_current_level) THEN
1059 fnd_log.string(fnd_log.level_statement,l_debug_module,'After UPLOAD_REVISION_REPORT Call File Id -> = ' || x_file_id);
1060 END IF;
1061 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1062 IF (l_log_statement >= l_log_current_level) THEN
1063 fnd_log.string(fnd_log.level_statement,l_debug_module,'UPLOAD_REVISION_REPORT Error');
1064 END IF;
1065 RAISE FND_API.G_EXC_ERROR;
1066 END IF;
1067 -- If the return status is success, populate the input rec for process_route for updation
1068 IF ( x_file_id <> FND_API.G_MISS_NUM AND x_file_id IS NOT NULL) THEN
1069 p_process_route_input_rec.file_id := x_file_id ;
1070 END IF;
1071 p_process_route_input_rec.DML_OPERATION :='N';
1072 -- Call the API for update
1073 AHL_RM_ROUTE_PVT.process_route
1074 (
1075 '1.0',
1076 FND_API.G_TRUE,
1077 FND_API.G_FALSE,
1078 FND_API.G_VALID_LEVEL_FULL,
1079 FND_API.G_FALSE,
1080 p_module_type,--NULL Sthilak for bug #14036337,,
1081 x_return_status,
1082 x_msg_count,
1083 x_msg_data,
1084 p_process_route_input_rec
1085 );
1086 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1087 fnd_log.string(fnd_log.level_statement,l_debug_module,'After calling process_route in update mode x_return_status -> ' || x_return_status);
1088 END IF;
1089
1090 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1091 IF (l_log_statement >= l_log_current_level) THEN
1092 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.process_route Error');
1093 END IF;
1094 /*Update_Route_Interface_table
1095 (
1096 p_enigma_route_rec,
1097 p_context,
1098 p_pub_date,
1099 'N',
1100 x_msg_data
1101 );*/
1102 RAISE FND_API.G_EXC_ERROR;
1103 END IF;
1104
1105 -- Insert the transaction record into the staging table, with status as success
1106 /*Update_Route_Interface_table
1107 (
1108 p_enigma_route_rec,
1109 p_context,
1110 p_pub_date,
1111 'SUCCESS',
1112 'Route updated Successfully'
1113 );*/
1114 END IF;
1115 END IF;
1116 -- Check if the flag id delete and if so delete the route from the CMRO system .
1117 IF (p_enigma_route_rec.DML_OPERATION = 'D') THEN
1118 -- IF the route is in Approval Pending status , then insert the Enigma Record into the staging table with status as pending.
1119 IF ( upper(l_get_latest_route_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
1120 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_APR_PENDING');
1121 FND_MSG_PUB.ADD;
1122 RAISE FND_API.G_EXC_ERROR;
1123 /*Update_Route_Interface_table
1124 (
1125 p_enigma_route_rec,
1126 p_context,
1127 p_pub_date,
1128 'Y',
1129 'Route is in approval pending status'
1130 );*/
1131 ELSE
1132 -- Call delete_route procedure to delete the route from CMRO End.
1133 AHL_RM_ROUTE_PVT.delete_route
1134 (
1135 '1.0',
1136 FND_API.G_TRUE,
1137 FND_API.G_FALSE,
1138 FND_API.G_VALID_LEVEL_FULL,
1139 FND_API.G_FALSE,
1140 p_module_type,--'ENIGMA' Sthilak for bug #14036337,,
1141 x_return_status,
1142 x_msg_count,
1143 x_msg_data,
1144 l_get_latest_route_rev.route_id,
1145 l_get_latest_route_rev.object_version_number
1146 );
1147
1148 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1149 fnd_log.string(fnd_log.level_statement,l_debug_module,'After calling delete_routes..Return Status: ' || x_return_status);
1150 END IF;
1151
1152 -- Check the return status , and if the status is not success , then raise an error
1153 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1154 IF (l_log_statement >= l_log_current_level) THEN
1155 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.delete_route Error');
1156 END IF;
1157 /*Update_Route_Interface_table
1158 (
1159 p_enigma_route_rec,
1160 p_context,
1161 p_pub_date,
1162 'N',
1163 x_msg_data
1164 );*/
1165 RAISE FND_API.G_EXC_ERROR;
1166 END IF;
1167 END IF;
1168 END IF; -- change Flag
1169
1170 IF (p_enigma_route_rec.DML_OPERATION = 'U') THEN
1171 -- IF the route is in Approval Pending status , then insert the Enigma Record into the staging table with status as pending.
1172 IF ( upper(l_get_latest_route_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
1173 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_APR_PENDING');
1174 FND_MSG_PUB.ADD;
1175 RAISE FND_API.G_EXC_ERROR;
1176 /*Update_Route_Interface_table
1177 (
1178 p_enigma_route_rec,
1179 p_context,
1180 p_pub_date,
1181 'Y',
1182 'Route is in approval pending status'
1183 );*/
1184
1185 -- If the route has any assocaited operations, add them also to the stating table marking the status as pending.
1186 /*IF ( p_enigma_route_rec.op_asso_tbl.COUNT > 0) THEN
1187 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1188 fnd_log.string(fnd_log.level_statement,l_debug_module,'Operations Attached No: ' || p_enigma_route_rec.op_asso_tbl.COUNT);
1189 END IF;
1190 FOR i IN p_enigma_route_rec.op_asso_tbl.FIRST..p_enigma_route_rec.op_asso_tbl.LAST
1191 LOOP
1192 INSERT INTO AHL_RT_OPER_INTERFACE
1193 (
1194 CONTEXT,
1195 PUBLISH_DATE,
1196 ENIGMA_OPERATION_ID,
1197 PARENT_ENIGMA_ROUTE_ID,
1198 DML_OPERATION,
1199 ATA_CODE,
1200 DESCRIPTION,
1201 STATUS,
1202 REASON,
1203 ENIGMA_DOC_ID
1204 )
1205 VALUES
1206 (
1207 p_context,
1208 p_pub_date,
1209 p_enigma_route_rec.op_asso_tbl(i),
1210 p_enigma_route_rec.enigma_route_id,
1211 NULL,--p_enigma_op_tbl(i).DML_OPERATION,
1212 NULL,--p_enigma_op_tbl(i).ata_code,
1213 NULL,--p_enigma_op_tbl(i).description,
1214 'PENDING',
1215 'Parent Route is in approval pending status',
1216 NULL--p_enigma_op_tbl(i).ENIGMA_DOC_ID
1217 );
1218 END LOOP;
1219 END IF;*/
1220 ELSIF (upper(l_get_latest_route_rev.revision_status) = 'COMPLETE') THEN
1221
1222 AHL_RM_ROUTE_PVT.create_route_revision
1223 (
1224 '1.0',
1225 FND_API.G_TRUE,
1226 FND_API.G_FALSE,
1227 FND_API.G_VALID_LEVEL_FULL,
1228 FND_API.G_FALSE,
1229 p_module_type,--NULL Sthilak for bug #14036337,,
1230 x_return_status,
1231 x_msg_count,
1232 x_msg_data,
1233 l_get_latest_route_rev.route_id,
1234 l_get_latest_route_rev.object_version_number,
1235 x_route_id
1236 );
1237
1238 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1239 fnd_log.string(fnd_log.level_statement,l_debug_module,'After calling create_route_revision..Return Status: ' || x_return_status || ' New Route Id: ' || x_route_id);
1240 END IF;
1241
1242 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1243 IF (l_log_statement >= l_log_current_level) THEN
1244 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.create_route_revision Error');
1245 END IF;
1246 /*Update_Route_Interface_table
1247 (
1248 p_enigma_route_rec,
1249 p_context,
1250 p_pub_date,
1251 'N',
1252 x_msg_data
1253 );*/
1254 RAISE FND_API.G_EXC_ERROR;
1255 END IF;
1256
1257 --to get the object version number of new revision
1258 SELECT object_version_number INTO l_ovn from
1259 AHL_ROUTES_B where route_id = x_route_id;
1260
1261 Map_Enigma_To_CMRO_Route_Rec
1262 (
1263 p_enigma_route_rec,
1264 p_process_route_input_rec,
1265 x_route_id,
1266 l_ovn,
1267 p_context,
1268 p_pub_date
1269
1270 );
1271
1272 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1273 fnd_log.string(fnd_log.level_statement,l_debug_module,'Before calling process_route in update mode');
1274 END IF;
1275
1276 -- Call the API for update
1277 AHL_RM_ROUTE_PVT.process_route
1278 (
1279 '1.0',
1280 FND_API.G_TRUE,
1281 FND_API.G_FALSE,
1282 FND_API.G_VALID_LEVEL_FULL,
1283 FND_API.G_FALSE,
1284 p_module_type,--NULL Sthilak for bug #14036337,,
1285 x_return_status,
1286 x_msg_count,
1287 x_msg_data,
1288 p_process_route_input_rec
1289 );
1290
1291 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1295 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1292 fnd_log.string(fnd_log.level_statement,l_debug_module,'After calling process_route in update mode x_return_status -> ' || x_return_status);
1293 END IF;
1294
1296 IF (l_log_statement >= l_log_current_level) THEN
1297 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.process_route Error');
1298 END IF;
1299 /*Update_Route_Interface_table
1300 (
1301 p_enigma_route_rec,
1302 p_context,
1303 p_pub_date,
1304 'N',
1305 x_msg_data
1306 );*/
1307 RAISE FND_API.G_EXC_ERROR;
1308 END IF;
1309
1310 -- Insert the transaction record into the staging table, with status as success
1311 /*Update_Route_Interface_table
1312 (
1313 p_enigma_route_rec,
1314 p_context,
1315 p_pub_date,
1316 'SUCCESS',
1317 'Route updated Successfully'
1318 );*/
1319
1320 -- call the process operation association procedure
1321 process_rt_op_assoc
1322 (
1323 x_return_status,
1324 x_msg_count ,
1325 x_msg_data ,
1326 p_enigma_route_rec.op_asso_tbl,
1327 x_route_id
1328 );
1329
1330 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1331 IF (l_log_statement >= l_log_current_level) THEN
1332 fnd_log.string(fnd_log.level_statement,l_debug_module,'process_route_operation_association Error');
1333 END IF;
1334 /*Update_Route_Interface_table
1335 (
1336 p_enigma_route_rec,
1337 p_context,
1338 p_pub_date,
1339 'N',
1340 x_msg_data
1341 );*/
1342 RAISE FND_API.G_EXC_ERROR;
1343 END IF;
1344
1345 ELSIF ( l_get_latest_route_rev.revision_status = 'DRAFT'
1346 OR l_get_latest_route_rev.revision_status = 'APPROVAL_REJECTED' ) THEN
1347
1348 Map_Enigma_To_CMRO_Route_Rec
1349 (
1350 p_enigma_route_rec,
1351 p_process_route_input_rec,
1352 l_get_latest_route_rev.route_id,
1353 l_get_latest_route_rev.object_version_number,
1354 p_context,
1355 p_pub_date
1356 );
1357
1358 -- Call the API for update.
1359 AHL_RM_ROUTE_PVT.process_route
1360 (
1361 '1.0',
1362 FND_API.G_TRUE,
1363 FND_API.G_FALSE,
1364 FND_API.G_VALID_LEVEL_FULL,
1365 FND_API.G_FALSE,
1366 p_module_type,--NULL Sthilak for bug #14036337,,
1367 x_return_status,
1368 x_msg_count,
1369 x_msg_data,
1370 p_process_route_input_rec
1371 );
1372
1373 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1374 fnd_log.string(fnd_log.level_statement,l_debug_module,'after calling process_route');
1375 END IF;
1376
1377 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1378 IF (l_log_statement >= l_log_current_level) THEN
1379 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.process_route Error');
1380 END IF;
1381 /*Update_Route_Interface_table
1382 (
1383 p_enigma_route_rec,
1384 p_context,
1385 p_pub_date,
1386 'N',
1387 x_msg_data
1388 );*/
1389 RAISE FND_API.G_EXC_ERROR;
1390 END IF;
1391
1392 /*Update_Route_Interface_table(
1393 p_enigma_route_rec,
1394 p_context,
1395 p_pub_date,
1396 'SUCCESS',
1397 'Route updated Successfully'
1398 );*/
1399 -- call the process operation association procedure
1400 process_rt_op_assoc
1401 (
1402 x_return_status,
1403 x_msg_count ,
1404 x_msg_data ,
1405 p_enigma_route_rec.op_asso_tbl,
1406 l_get_latest_route_rev.route_id
1407 );
1408
1409 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1410 IF (l_log_statement >= l_log_current_level) THEN
1411 fnd_log.string(fnd_log.level_statement,l_debug_module,'process_route_operation_association Error');
1412 END IF;
1413 /*Update_Route_Interface_table
1414 (
1415 p_enigma_route_rec,
1416 p_context,
1417 p_pub_date,
1418 'N',
1419 x_msg_data
1420 );*/
1421 RAISE FND_API.G_EXC_ERROR;
1422 END IF;
1423 END IF; -- Status check
1424 END IF; -- Change Flag U
1425
1426 IF (p_enigma_route_rec.DML_OPERATION = 'C') THEN
1427 --Validation::Publish Date can not be null
1428 IF (p_pub_date IS NULL OR p_pub_date = FND_API.G_MISS_DATE)THEN
1429 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_PUB_DATE_NULL');
1430 FND_MSG_PUB.ADD;
1431 END IF;
1432
1433 --Validation::p_context can not be null
1434 IF (p_context IS NULL OR p_context = FND_API.G_MISS_CHAR)THEN
1435 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_CONTEXT_NULL');
1436 FND_MSG_PUB.ADD;
1437 END IF;
1438
1439 -- Validation::ENIGMA_DOC_ID can nnot be null
1440 IF (p_enigma_route_rec.ENIGMA_DOC_ID IS NULL OR p_enigma_route_rec.ENIGMA_DOC_ID = FND_API.G_MISS_CHAR)THEN
1441 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_DOC_ID_NULL');
1442 FND_MSG_PUB.ADD;
1443 END IF;
1444
1445 -- validation::Enigma_route_id is unique.
1446 OPEN validate_enig_route(p_enigma_route_rec.enigma_route_id);
1447 FETCH validate_enig_route INTO l_eng_count;
1448 IF (l_eng_count > 0)THEN
1449 FND_MESSAGE.set_name('AHL','AHL_RM_ROUTE_NO_DUP');
1450 FND_MSG_PUB.add;
1451 END IF;
1452 CLOSE validate_enig_route;
1453
1454 x_msg_count := FND_MSG_PUB.count_msg;
1455 IF x_msg_count > 0 THEN
1456 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1457 p_data => x_msg_data,
1458 p_encoded => fnd_api.g_false);
1459 /*Update_Route_Interface_table
1460 (
1461 p_enigma_route_rec,
1462 p_context,
1463 p_pub_date,
1464 'N',
1465 x_msg_data
1466 );*/
1467 RAISE FND_API.G_EXC_ERROR;
1468 END IF;
1469
1470 -- Populate the input record with values.
1471 Map_Enigma_To_CMRO_Route_Rec
1472 (
1473 p_enigma_route_rec,
1474 p_process_route_input_rec,
1475 NULL,
1476 NULL,
1477 p_context,
1478 p_pub_date
1479 );
1480
1481 -- Call the API for Create.
1482 AHL_RM_ROUTE_PVT.process_route
1483 (
1484 '1.0',
1485 FND_API.G_TRUE,
1486 FND_API.G_FALSE,
1487 FND_API.G_VALID_LEVEL_FULL,
1488 FND_API.G_FALSE,
1489 p_module_type,--NULL Sthilak for bug #14036337,
1490 x_return_status,
1491 x_msg_count,
1492 x_msg_data,
1493 p_process_route_input_rec
1494 );
1495 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1496 fnd_log.string
1497 (fnd_log.level_statement,l_debug_module,'After Calling process_route in Create mode Route Id ' || p_process_route_input_rec.enigma_route_id);
1498 END IF;
1499
1500 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1501 IF (l_log_statement >= l_log_current_level) THEN
1502 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.process_route Error');
1503 END IF;
1504 /*Update_Route_Interface_table
1505 (
1506 p_enigma_route_rec,
1507 p_context,
1508 p_pub_date,
1509 'N',
1510 x_msg_data
1511 );*/
1512 RAISE FND_API.G_EXC_ERROR;
1513 END IF;
1514
1515 -- If the return status is success, then add the transaction to the stating table and status as "Success"
1516 /*Update_Route_Interface_table
1517 (
1518 p_enigma_route_rec,
1519 p_context,
1520 p_pub_date,
1521 'SUCCESS',
1522 'Route Created Successfully'
1523 );*/
1524
1525 -- call the process operation association procedure
1526 process_rt_op_assoc
1527 (
1528 x_return_status,
1529 x_msg_count ,
1530 x_msg_data ,
1531 p_enigma_route_rec.op_asso_tbl,
1532 p_process_route_input_rec.route_id
1533 );
1534 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1535 IF (l_log_statement >= l_log_current_level) THEN
1536 fnd_log.string(fnd_log.level_statement,l_debug_module,'process_route_operation_association Error');
1537 END IF;
1538 /*Update_Route_Interface_table
1539 (
1540 p_enigma_route_rec,
1541 p_context,
1542 p_pub_date,
1543 'N',
1544 x_msg_data
1545 );*/
1546 RAISE FND_API.G_EXC_ERROR;
1547 END IF;
1548 END IF; -- status check 'C'
1549
1550 -- Check Error Message stack.
1551 x_msg_count := FND_MSG_PUB.count_msg;
1552 IF x_msg_count > 0 THEN
1553 RAISE FND_API.G_EXC_ERROR;
1554 END IF;
1555
1556 -- Standard check for p_commit
1557 IF FND_API.To_Boolean (p_commit)THEN
1558 COMMIT WORK;
1559 END IF;
1560
1561 -- Standard call to get message count and if count is 1, get message info
1562 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1563 p_data => x_msg_data,
1564 p_encoded => fnd_api.g_false );
1565
1566 EXCEPTION
1567 WHEN FND_API.G_EXC_ERROR THEN
1568 x_return_status := FND_API.G_RET_STS_ERROR;
1569 Rollback to Process_Route_Details_SP;
1570 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1571 p_data => x_msg_data,
1572 p_encoded => fnd_api.g_false);
1573 IF (upper(l_get_latest_route_rev.revision_status) = 'APPROVAL_PENDING') THEN
1574 isrecoverable := 'Y';
1575 ELSE
1576 isrecoverable := 'N';
1577 END IF;
1578 Update_Route_Interface_table
1579 (
1580 p_enigma_route_rec,
1581 p_context,
1585 );
1582 p_pub_date,
1583 isrecoverable,
1584 x_msg_data
1586
1587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1589 Rollback to Process_Route_Details_SP;
1590 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1591 p_data => x_msg_data,
1592 p_encoded => fnd_api.g_false);
1593 isrecoverable := 'N';
1594 Update_Route_Interface_table
1595 (
1596 p_enigma_route_rec,
1597 p_context,
1598 p_pub_date,
1599 isrecoverable,
1600 x_msg_data
1601 );
1602
1603 WHEN OTHERS THEN
1604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1605 Rollback to Process_Route_Details_SP;
1606 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1607 THEN
1608 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1609 p_procedure_name => 'Process_Route_Details',
1610 p_error_text => SUBSTR(SQLERRM,1,240));
1611 END IF;
1612 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1613 p_data => x_msg_data,
1614 p_encoded => fnd_api.g_false);
1615 isrecoverable := 'N';
1616 Update_Route_Interface_table
1617 (
1618 p_enigma_route_rec,
1619 p_context,
1620 p_pub_date,
1621 isrecoverable,
1622 x_msg_data
1623 );
1624 END Process_Route_Details;
1625
1626 PROCEDURE Update_Op_Interface_table
1627 (
1628 p_enigma_op_rec IN enigma_op_rec_type,
1629 p_context IN VARCHAR2,
1630 p_pub_date IN DATE,
1631 p_isrecoverable IN VARCHAR2,
1632 p_reason IN VARCHAR2
1633 )
1634 IS
1635 l_debug_module CONSTANT VARCHAR2(100) := 'AHL.PLSQL.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||'Update_Op_Interface_table';
1636
1637 BEGIN
1638 --IF (l_log_statement >= l_log_current_level) THEN
1639 -- fnd_log.string(fnd_log.level_statement,l_debug_module,'Before Inserting into op interface table');
1640 -- fnd_log.string(fnd_log.level_statement,l_debug_module,' reason: ' || p_reason);
1641 -- END IF;
1642
1643 INSERT INTO AHL_ENIGMA_OP_INTERFACE
1644 (
1645 ENIGMA_OP_ID,
1646 ATA_CODE,
1647 ENIGMA_DOC_ID,
1648 DML_OPERATION,
1649 OPERATOR,
1650 EQUIPMENT,
1651 OEM_DOC_TYPE,
1652 CHAPTER_SECTION_SUBJECT,
1653 AMTOSS_FUNCTION,
1654 OP_SEQ,
1655 CONFIG_LETTER,
1656 PUBLISH_DATE,
1657 PROCESS_DATE,
1658 CONTEXT,
1659 DESCRIPTION,
1660 ERROR_MESSAGE,
1661 ISRECOVERABLE
1662 )
1663 VALUES
1664 (
1665 p_enigma_op_rec.enigma_op_id,
1666 p_enigma_op_rec.ata_code,
1667 p_enigma_op_rec.enigma_doc_id,
1668 p_enigma_op_rec.dml_operation,
1669 p_enigma_op_rec.operator,
1670 p_enigma_op_rec.equipment,
1671 p_enigma_op_rec.oem_doc_type,
1672 p_enigma_op_rec.chapter_section_subject,
1673 p_enigma_op_rec.amtoss_function,
1674 p_enigma_op_rec.op_seq,
1675 p_enigma_op_rec.config_letter,
1676 p_pub_date,
1677 sysdate,
1678 p_context,
1679 p_enigma_op_rec.description,
1680 p_reason,
1681 p_isrecoverable
1682 );
1683
1684 End Update_Op_Interface_table;
1685
1686
1687 PROCEDURE Process_OP_Details
1688 (
1689 p_api_version IN NUMBER := '1.0',
1690 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1691 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1692 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1693 p_default IN VARCHAR2 := FND_API.G_FALSE,
1694 p_module_type IN VARCHAR2 := NULL,
1695 x_return_status OUT NOCOPY VARCHAR2,
1696 x_msg_count OUT NOCOPY NUMBER,
1697 x_msg_data OUT NOCOPY VARCHAR2,
1698 p_enigma_op_rec IN enigma_op_rec_type,
1699 p_context IN VARCHAR2,
1700 p_pub_date IN DATE
1701 )
1702 IS
1703
1704 CURSOR get_latest_oper_rev (c_ENIGMA_OP_ID IN VARCHAR2)
1705 IS
1706 SELECT operation_id,object_version_number,revision_status_code revision_status
1707 FROM AHL_OPERATIONS_B
1708 WHERE UPPER(TRIM(ENIGMA_OP_ID)) = UPPER(TRIM(c_ENIGMA_OP_ID))
1709 AND REVISION_NUMBER =
1710 ( SELECT MAX( revision_number )
1711 FROM AHL_OPERATIONS_B
1712 WHERE UPPER(TRIM(ENIGMA_OP_ID)) = UPPER(TRIM(c_ENIGMA_OP_ID))
1713 );
1714
1715 CURSOR c_get_op_rec (c_oper_id IN NUMBER)
1716 IS
1717 SELECT aop.object_version_number
1718 FROM ahl_operations_b aop
1719 WHERE aop.operation_id = c_oper_id;
1720
1721 -- Declare local variables
1722 l_api_name CONSTANT VARCHAR2(30) := 'Process_OP_Details';
1723 l_api_version CONSTANT NUMBER := 1.0;
1724 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||l_api_name;
1725
1726 -- Bug # 8285733
1727 l_rev_op_rec c_get_op_rec%ROWTYPE;
1728 l_get_latest_oper_rev get_latest_oper_rev%ROWTYPE;
1729 p_process_oper_input_rec AHL_RM_OPERATION_PVT.operation_rec_type;
1730
1731 x_operation_id NUMBER;
1732 x_revision_number VARCHAR2(30);
1733
1734 l_operation_id NUMBER;
1735 l_object_version_number NUMBER;
1736 l_revision_status VARCHAR2(100);
1737
1738 l_step_count NUMBER;
1739 l_op_sq NUMBER;
1740
1741 isrecoverable VARCHAR2(1);
1742
1743 BEGIN
1744 -- Standard start of API savepoint
1745 SAVEPOINT Process_OP_Details_SP;
1746
1747 -- Initialize return status to success before any code logic/validation
1748 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1749
1750 -- Standard call to check for call compatibility
1751 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1752 THEN
1753 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1754 END IF;
1755
1756 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
1757 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1758 THEN
1759 FND_MSG_PUB.INITIALIZE;
1760 END IF;
1761
1762 -- initialise it only if it is a OA Adapter !
1763 --fnd_global.APPS_INITIALIZE (1003259,62211, 867);
1764
1765 -- Log API entry point
1766 IF (l_log_procedure >= l_log_current_level) THEN
1767 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.begin','At the start of PL SQL procedure ');
1768 END IF;
1769
1770 -- Check for operations,If so process them depending on their change flag .
1771
1772
1773 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1774 THEN
1775 fnd_log.string(fnd_log.level_statement,l_debug_module,'Inside Process_OP_Details -> Operations Found');
1776 END IF;
1777
1778 -- Verify if the Change flag is right...
1779 IF (p_enigma_op_rec.DML_OPERATION NOT IN ( 'C', 'D', 'U'))
1780 THEN
1781 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_DML');
1782 FND_MESSAGE.SET_TOKEN('FIELD', p_enigma_op_rec.DML_OPERATION);
1783 FND_MESSAGE.SET_TOKEN('RECORD', p_enigma_op_rec.ENIGMA_OP_ID);
1784 FND_MSG_PUB.ADD;
1785 RAISE FND_API.G_EXC_ERROR;
1786 END IF;
1787
1788 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1789 THEN
1790 fnd_log.string(fnd_log.level_statement,l_debug_module,'Change Flag set right');
1791 END IF;
1792
1793 -- If the change flag is C then create a new operation.
1794 IF (p_enigma_op_rec.DML_OPERATION = 'C') THEN
1795
1796 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1797 THEN
1798 fnd_log.string(fnd_log.level_statement,l_debug_module,'Inside Process_OP_Details -> Inside Create Operation ');
1799 END IF;
1800
1801 -- Validating ALL Mandatory Fields.
1802
1803 --validate Enigma Document Id
1804 IF ( p_enigma_op_rec.ENIGMA_DOC_ID IS NULL OR p_enigma_op_rec.ENIGMA_DOC_ID = FND_API.G_MISS_CHAR)
1805 THEN
1806 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ENIGMA_ID_NULL');
1807 FND_MSG_PUB.ADD;
1808 END IF;
1809 --validate Enigma Operation Id
1810 IF ( p_enigma_op_rec.ENIGMA_OP_ID IS NULL OR p_enigma_op_rec.ENIGMA_OP_ID = FND_API.G_MISS_CHAR)
1811 THEN
1812 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OPER_ID_NULL');
1813 FND_MSG_PUB.ADD;
1814 END IF;
1815 --validate Opearator
1816 IF ( p_enigma_op_rec.OPERATOR IS NULL OR p_enigma_op_rec.OPERATOR = FND_API.G_MISS_CHAR)
1817 THEN
1818 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OPERATOR_ID_NULL');
1819 FND_MSG_PUB.ADD;
1820 END IF;
1821 -- validate Oem Doc Type
1822 IF ( p_enigma_op_rec.OEM_DOC_TYPE IS NULL OR p_enigma_op_rec.OEM_DOC_TYPE = FND_API.G_MISS_CHAR)
1823 THEN
1824 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OEM_DOC_TYPE_NULL');
1825 FND_MSG_PUB.ADD;
1826 END IF;
1827 -- validate Chapter,Section,Subject
1828 IF ( p_enigma_op_rec.CHAPTER_SECTION_SUBJECT IS NULL OR p_enigma_op_rec.CHAPTER_SECTION_SUBJECT = FND_API.G_MISS_CHAR)
1829 THEN
1830 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_CHA_SEC_SUB_NULL');
1831 FND_MSG_PUB.ADD;
1832 END IF;
1833 -- Validate AMTOSS Function
1834 IF ( p_enigma_op_rec.AMTOSS_Function IS NULL OR p_enigma_op_rec.AMTOSS_Function = FND_API.G_MISS_CHAR)
1835 THEN
1836 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_AMTOSS_FUN_NULL');
1837 FND_MSG_PUB.ADD;
1838 END IF;
1839
1840 --Validate Model Code
1841 IF (p_context IS NULL AND p_context = FND_API.G_MISS_CHAR)
1842 THEN
1843 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_CONTEXT_NULL');
1844 FND_MSG_PUB.ADD;
1845 END IF;
1846
1847 x_msg_count := FND_MSG_PUB.count_msg;
1848 IF x_msg_count > 0
1849 THEN
1850 RAISE FND_API.G_EXC_ERROR;
1851 END IF;
1852
1853 -- Populate the input records
1854 IF (p_enigma_op_rec.OPERATOR <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.OPERATOR IS NOT NULL ) THEN
1855 p_process_oper_input_rec.SEGMENT1 := p_enigma_op_rec.OPERATOR;
1856 END IF;
1857
1858 IF (p_enigma_op_rec.EQUIPMENT <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.EQUIPMENT IS NOT NULL ) THEN
1859 p_process_oper_input_rec.SEGMENT2 := p_enigma_op_rec.EQUIPMENT;
1860 END IF;
1861
1862 IF (p_enigma_op_rec.OEM_DOC_TYPE <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.OEM_DOC_TYPE IS NOT NULL ) THEN
1863 p_process_oper_input_rec.SEGMENT3 := p_enigma_op_rec.OEM_DOC_TYPE;
1864 END IF;
1865
1866 IF (p_enigma_op_rec.CHAPTER_SECTION_SUBJECT <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.CHAPTER_SECTION_SUBJECT IS NOT NULL ) THEN
1867 p_process_oper_input_rec.SEGMENT4 := p_enigma_op_rec.CHAPTER_SECTION_SUBJECT;
1868 END IF;
1869
1870 IF (p_enigma_op_rec.AMTOSS_Function <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.AMTOSS_Function IS NOT NULL ) THEN
1871 p_process_oper_input_rec.SEGMENT5 := p_enigma_op_rec.AMTOSS_Function;
1872 END IF;
1873
1874 --If OP_SEQ is not passed from enigma document sequence will be passed.
1878 SELECT AHL_ENIGMA_OP_SEQ_S.NEXTVAL
1875 IF (p_enigma_op_rec.OP_SEQ <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.OP_SEQ IS NOT NULL ) THEN
1876 p_process_oper_input_rec.SEGMENT6 := p_enigma_op_rec.OP_SEQ;
1877 ELSIF ( p_enigma_op_rec.OP_SEQ IS NULL OR p_enigma_op_rec.OP_SEQ = FND_API.G_MISS_CHAR) THEN
1879 INTO l_op_sq
1880 FROM DUAL;
1881 p_process_oper_input_rec.SEGMENT6 := l_op_sq || fnd_profile.value('AHL_ENIGMA_OP_SEQ');
1882 END IF;
1883
1884 IF (p_enigma_op_rec.CONFIG_LETTER <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.CONFIG_LETTER IS NOT NULL ) THEN
1885 p_process_oper_input_rec.SEGMENT7 := p_enigma_op_rec.CONFIG_LETTER;
1886 END IF;
1887
1888 IF (p_enigma_op_rec.description <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.description IS NOT NULL ) THEN
1889 p_process_oper_input_rec.DESCRIPTION := substr(p_enigma_op_rec.description,1,500);
1890 p_process_oper_input_rec.remarks := p_enigma_op_rec.description;
1891 ELSE
1892 p_process_oper_input_rec.DESCRIPTION := p_enigma_op_rec.ATA_CODE;
1893 END IF;
1894
1895 IF (p_enigma_op_rec.ENIGMA_OP_ID <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.ENIGMA_OP_ID IS NOT NULL ) THEN
1896 p_process_oper_input_rec.ENIGMA_OP_ID := p_enigma_op_rec.ENIGMA_OP_ID;
1897 END IF;
1898
1899 IF (p_enigma_op_rec.ENIGMA_DOC_ID <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.ENIGMA_DOC_ID IS NOT NULL ) THEN
1900 p_process_oper_input_rec.ENIGMA_DOC_ID := p_enigma_op_rec.ENIGMA_DOC_ID;
1901 END IF;
1902
1903 IF (p_context <> FND_API.G_MISS_CHAR AND p_context IS NOT NULL ) THEN
1904 p_process_oper_input_rec.MODEL_CODE := p_context;
1905 END IF;
1906
1907 IF (p_pub_date > sysdate ) THEN
1908 p_process_oper_input_rec.ACTIVE_START_DATE := p_pub_date;
1909 ELSE
1910 p_process_oper_input_rec.ACTIVE_START_DATE := sysdate;
1911 END IF;
1912
1913 p_process_oper_input_rec.DML_OPERATION := 'C';
1914 p_process_oper_input_rec.STANDARD_OPERATION_FLAG := 'Y';
1915
1916 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1917 THEN
1918 fnd_log.string(fnd_log.level_statement,l_debug_module,'Inside Process_OP_Details calling process_operations');
1919 END IF;
1920
1921 -- Call the API for update
1922 AHL_RM_OPERATION_PVT.process_operation
1923 (
1924 '1.0',
1925 FND_API.G_TRUE,
1926 FND_API.G_FALSE,
1927 FND_API.G_VALID_LEVEL_FULL,
1928 FND_API.G_FALSE,
1929 p_module_type,--NULL Sthilak for bug #14036337,,
1930 x_return_status,
1931 x_msg_count,
1932 x_msg_data,
1933 p_process_oper_input_rec
1934 );
1935
1936 x_operation_id := p_process_oper_input_rec.operation_id;
1937 x_revision_number := p_process_oper_input_rec.REVISION_NUMBER;
1938
1939 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1940 THEN
1941 fnd_log.string(fnd_log.level_statement,l_debug_module,'Operation Created ->' || x_operation_id);
1942 fnd_log.string(fnd_log.level_statement,l_debug_module,'Operation Created Revision->' || x_revision_number);
1943 fnd_log.string(fnd_log.level_statement,l_debug_module,'Process_OP_Details after calling process_operations');
1944 END IF;
1945
1946 IF (l_log_statement >= l_log_current_level) THEN
1947 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OPERATION_PVT.process_operation');
1948 END IF;
1949
1950 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
1951 IF (l_log_statement >= l_log_current_level) THEN
1952 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.process_operation Error');
1953 END IF;
1954 RAISE FND_API.G_EXC_ERROR;
1955 END IF;
1956
1957 IF (l_log_statement >= l_log_current_level) THEN
1958 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.process_operation -> creation Successful');
1959 END IF;
1960
1961 /*IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1962 THEN
1963 fnd_log.string(fnd_log.level_statement,l_debug_module,'before inserting the operations into staging table ');
1964 END IF;
1965
1966 -- Insert the transaction record into the staging table, with status as success
1967 OP_interface_insert_row(
1968 X_CONTEXT => p_context,
1969 X_PUBLISH_DATE => p_pub_date,
1970 X_ENIGMA_OPERATION_ID => p_enigma_op_rec.ENIGMA_OP_ID,
1971 X_DML_OPERATION => p_enigma_op_rec.DML_OPERATION,
1972 X_DESCRIPTION => p_enigma_op_rec.description,
1973 X_STATUS => 'SUCCESS',
1974 X_REASON => 'Operation created successfully',
1975 X_ENIGMA_DOC_ID => p_enigma_op_rec.ENIGMA_DOC_ID
1976 );*/
1977 -- If the operation change flag is "U" then process the operation accordingly .
1978 ELSIF (p_enigma_op_rec.DML_OPERATION = 'U') THEN
1979
1980 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1981 THEN
1982 fnd_log.string(fnd_log.level_statement,l_debug_module,'Operation Flag is U');
1983 fnd_log.string(fnd_log.level_statement,l_debug_module,'p_enigma_op_rec.operation_id -> ' || p_enigma_op_rec.ENIGMA_OP_ID);
1984 END IF;
1985
1986 l_get_latest_oper_rev := NULL;
1987
1988 --validate Enigma Document Id, It should not be null
1989 IF ( p_enigma_op_rec.ENIGMA_DOC_ID IS NULL OR p_enigma_op_rec.ENIGMA_DOC_ID = FND_API.G_MISS_CHAR)
1990 THEN
1991 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ENIGMA_ID_NULL');
1992 FND_MSG_PUB.ADD;
1993 END IF;
1994
1995 --validate Enigma Operation Id
1996 IF ( p_enigma_op_rec.ENIGMA_OP_ID IS NULL OR p_enigma_op_rec.ENIGMA_OP_ID = FND_API.G_MISS_CHAR)
1997 THEN
1998 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OPER_ID_NULL');
1999 FND_MSG_PUB.ADD;
2000 END IF;
2001
2002 --Validate Model Code
2003 IF (p_context IS NULL AND p_context = FND_API.G_MISS_DATE)
2004 THEN
2005 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_CONTEXT_NULL');
2006 FND_MSG_PUB.ADD;
2007 END IF;
2008
2009 x_msg_count := FND_MSG_PUB.count_msg;
2010 IF x_msg_count > 0
2011 THEN
2012 RAISE FND_API.G_EXC_ERROR;
2013 END IF;
2014
2015 -- Fetch the data from Table
2016 OPEN get_latest_oper_rev (p_enigma_op_rec.ENIGMA_OP_ID);
2017 FETCH get_latest_oper_rev INTO
2018 l_get_latest_oper_rev.operation_id,
2019 l_get_latest_oper_rev.object_version_number,
2020 l_get_latest_oper_rev.revision_status;
2021 CLOSE get_latest_oper_rev;
2022
2023 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2024 THEN
2025 fnd_log.string(fnd_log.level_statement,l_debug_module,'l_get_latest_oper_rev.operation_id ->' || l_get_latest_oper_rev.operation_id);
2026 fnd_log.string(fnd_log.level_statement,l_debug_module,'l_get_latest_oper_rev.object_version_number ->' || l_get_latest_oper_rev.object_version_number);
2027 fnd_log.string(fnd_log.level_statement,l_debug_module,'l_get_latest_oper_rev.revision_status ->' || l_get_latest_oper_rev.revision_status);
2028 END IF;
2029
2030 IF l_get_latest_oper_rev.operation_id IS NOT NULL THEN
2031
2032 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2033 THEN
2034 fnd_log.string(fnd_log.level_statement,l_debug_module,'Latest Operation Found');
2035 END IF;
2036
2037 -- If the operation is in Approval Pending status , then insert the operation record into the
2038 -- staging table with status as pending.
2039 IF ( upper(l_get_latest_oper_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
2040 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OP_APR_PENDING');
2041 FND_MSG_PUB.ADD;
2042 RAISE FND_API.G_EXC_ERROR;
2043 /*OP_interface_insert_row(
2044 X_CONTEXT => p_context,
2045 X_PUBLISH_DATE => p_pub_date,
2046 X_ENIGMA_OPERATION_ID => p_enigma_op_rec.ENIGMA_OP_ID,
2047 X_DML_OPERATION => p_enigma_op_rec.DML_OPERATION,
2048 X_DESCRIPTION => p_enigma_op_rec.description,
2049 X_STATUS => 'PENDING',
2050 X_REASON => 'Operation is in approval pending status',
2051 X_ENIGMA_DOC_ID => p_enigma_op_rec.ENIGMA_DOC_ID
2052 );*/
2053
2054 -- When the operation is in complete, do the following process
2055 ELSIF (upper(l_get_latest_oper_rev.revision_status) = 'COMPLETE' ) THEN
2056 -- Call the API to create a new revision of the operation.
2057 AHL_RM_OPERATION_PVT.create_oper_revision
2058 (
2059 '1.0',
2060 FND_API.G_TRUE,
2061 FND_API.G_FALSE,
2062 FND_API.G_VALID_LEVEL_FULL,
2063 FND_API.G_FALSE,
2064 p_module_type,--NULL Sthilak for bug #14036337,,
2065 x_return_status,
2066 x_msg_count,
2067 x_msg_data,
2068 l_get_latest_oper_rev.operation_id,
2069 l_get_latest_oper_rev.object_version_number,
2070 x_operation_id
2071 );
2072
2073 IF (l_log_statement >= l_log_current_level) THEN
2074 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OPERATION_PVT.create_oper_revision');
2075 END IF;
2076
2077 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
2078 IF (l_log_statement >= l_log_current_level) THEN
2079 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.create_oper_revision Error');
2080 END IF;
2081 RAISE FND_API.G_EXC_ERROR;
2082 END IF;
2083
2084 IF (l_log_statement >= l_log_current_level) THEN
2085 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.create_oper_revision -> revision Created');
2086 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.create_oper_revision -> x_operation_id= ' || x_operation_id);
2087 END IF;
2088
2089 -- If the return status is success, populate the input rec for process_operation for updation
2090
2091 IF ( x_operation_id <> FND_API.G_MISS_NUM AND x_operation_id IS NOT NULL) THEN
2092 p_process_oper_input_rec.OPERATION_ID := x_operation_id ;
2093 END IF;
2094
2095 OPEN c_get_op_rec (p_process_oper_input_rec.OPERATION_ID);
2096 FETCH c_get_op_rec INTO
2097 l_rev_op_rec.object_version_number;
2098 CLOSE c_get_op_rec;
2099
2100 IF (l_rev_op_rec.object_version_number <> FND_API.G_MISS_NUM AND l_rev_op_rec.object_version_number IS NOT NULL ) THEN
2101 p_process_oper_input_rec.OBJECT_VERSION_NUMBER := l_rev_op_rec.object_version_number;
2102 END IF;
2103
2104 IF (p_context <> FND_API.G_MISS_CHAR AND p_context IS NOT NULL ) THEN
2105 p_process_oper_input_rec.MODEL_CODE := p_context;
2106 END IF;
2107
2108 IF (p_enigma_op_rec.description <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.description IS NOT NULL ) THEN
2109 p_process_oper_input_rec.DESCRIPTION := substr(p_enigma_op_rec.description,1,500);
2110 p_process_oper_input_rec.remarks := p_enigma_op_rec.description;
2111 END IF;
2112
2113 p_process_oper_input_rec.DML_OPERATION := 'U';
2114
2115 IF (l_log_statement >= l_log_current_level) THEN
2116 fnd_log.string(fnd_log.level_statement,l_debug_module,' After revision operation_id -> '||p_process_oper_input_rec.OPERATION_ID
2117 );
2118 fnd_log.string(fnd_log.level_statement,l_debug_module,'After revision object ver no -> ' || p_process_oper_input_rec.OBJECT_VERSION_NUMBER);
2119 END IF;
2120 -- Call the API for update
2121 AHL_RM_OPERATION_PVT.process_operation
2122 (
2123 '1.0',
2124 FND_API.G_TRUE,
2125 FND_API.G_FALSE,
2126 FND_API.G_VALID_LEVEL_FULL,
2127 FND_API.G_FALSE,
2128 p_module_type,--NULL Sthilak for bug #14036337,,
2129 x_return_status,
2130 x_msg_count,
2131 x_msg_data,
2132 p_process_oper_input_rec
2133 );
2134
2135 IF (l_log_statement >= l_log_current_level) THEN
2136 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OPERATION_PVT.process_operation');
2137 END IF;
2138
2139 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
2140 IF (l_log_statement >= l_log_current_level) THEN
2141 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.process_operation Error');
2142 END IF;
2143 RAISE FND_API.G_EXC_ERROR;
2144 END IF;
2145
2146 IF (l_log_statement >= l_log_current_level) THEN
2147 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.process_operation:COMPLETE -> updation Successful');
2148 END IF;
2149
2150 -- If the status is Draft or Approval Rejected , do the following.
2151 ELSIF ( UPPER(l_get_latest_oper_rev.revision_status) = 'DRAFT'
2152 OR UPPER(l_get_latest_oper_rev.revision_status) = 'APPROVAL_REJECTED' ) THEN
2153
2154 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2155 THEN
2156 fnd_log.string(fnd_log.level_statement,l_debug_module,'Update Operation-> Draft');
2157 fnd_log.string(fnd_log.level_statement,l_debug_module,'populate the input rec');
2158 END IF;
2159
2160 -- If the return status is success, populate the input rec for process_operation for updation
2161 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
2162 p_process_oper_input_rec.OPERATION_ID := l_get_latest_oper_rev.operation_id ;
2163 END IF;
2164
2165 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
2166 p_process_oper_input_rec.OBJECT_VERSION_NUMBER := l_get_latest_oper_rev.object_version_number;
2167 END IF;
2168
2169 IF (p_context <> FND_API.G_MISS_CHAR AND p_context IS NOT NULL ) THEN
2170 p_process_oper_input_rec.MODEL_CODE := p_context;
2171 END IF;
2172
2173 IF (p_enigma_op_rec.description <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.description IS NOT NULL ) THEN
2174 p_process_oper_input_rec.DESCRIPTION := substr(p_enigma_op_rec.description,1,500);
2175 p_process_oper_input_rec.remarks := p_enigma_op_rec.description;
2176 END IF;
2177
2178 IF (p_enigma_op_rec.ENIGMA_DOC_ID <> FND_API.G_MISS_CHAR AND p_enigma_op_rec.ENIGMA_DOC_ID IS NOT NULL ) THEN
2179 p_process_oper_input_rec.ENIGMA_DOC_ID := p_enigma_op_rec.ENIGMA_DOC_ID;
2180 END IF;
2181
2182 p_process_oper_input_rec.DML_OPERATION := 'U';
2183
2184 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2185 THEN
2186 fnd_log.string(fnd_log.level_statement,l_debug_module,'p_process_oper_input_rec.OPERATION_ID-> ' || p_process_oper_input_rec.OPERATION_ID);
2187 fnd_log.string(fnd_log.level_statement,l_debug_module,'l_get_latest_oper_rev.object_version_number -> ' || l_get_latest_oper_rev.object_version_number);
2188 fnd_log.string(fnd_log.level_statement,l_debug_module,'p_enigma_op_rec.description -> ' || p_enigma_op_rec.description);
2189 fnd_log.string(fnd_log.level_statement,l_debug_module,'p_process_oper_input_rec.ENIGMA_OP_ID -> ' || p_process_oper_input_rec.ENIGMA_OP_ID);
2190 END IF;
2191
2192 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2193 THEN
2194 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling AHL_RM_OPERATION_PVT.process_operation ');
2195 END IF;
2196
2197 -- Call the API for update
2198 AHL_RM_OPERATION_PVT.process_operation
2199 (
2200 '1.0',
2201 FND_API.G_TRUE,
2202 FND_API.G_FALSE,
2203 FND_API.G_VALID_LEVEL_FULL,
2204 FND_API.G_FALSE,
2205 p_module_type,--NULL Sthilak for bug #14036337,,
2206 x_return_status,
2207 x_msg_count,
2208 x_msg_data,
2209 p_process_oper_input_rec
2210 );
2211
2212 IF (l_log_statement >= l_log_current_level) THEN
2213 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OPERATION_PVT.process_operation');
2214 END IF;
2215
2216 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
2217 IF (l_log_statement >= l_log_current_level) THEN
2218 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.process_operation Error');
2219 END IF;
2220 RAISE FND_API.G_EXC_ERROR;
2221 END IF;
2222
2223 IF (l_log_statement >= l_log_current_level) THEN
2224 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.process_operation:DRAFT -> updation Successful');
2225 END IF;
2226
2227 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2228 THEN
2229 fnd_log.string(fnd_log.level_statement,l_debug_module,'Before inserting into the staging table');
2230 END IF;
2231 END IF ; -- Status check
2232
2233 -- Insert the transaction record into the staging table, with status as success
2234 /*OP_interface_insert_row(
2235 X_CONTEXT => p_context,
2236 X_PUBLISH_DATE => p_pub_date,
2237 X_ENIGMA_OPERATION_ID => p_enigma_op_rec.ENIGMA_OP_ID,
2238 X_DML_OPERATION => p_enigma_op_rec.DML_OPERATION,
2239 X_DESCRIPTION => p_enigma_op_rec.description,
2240 X_STATUS => 'SUCCESS',
2241 X_REASON => 'Operation updated successfully',
2242 X_ENIGMA_DOC_ID => p_enigma_op_rec.ENIGMA_DOC_ID
2243 );*/
2244
2245 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2246 THEN
2247 fnd_log.string(fnd_log.level_statement,l_debug_module,'After inserting into the staging table');
2248 END IF;
2249 ELSE
2250 FND_MESSAGE.SET_NAME('AHL', 'AHL_ENIGMA_OPER_DONOT_EXIST');
2251 FND_MSG_PUB.ADD;
2252 RAISE FND_API.G_EXC_ERROR;
2253 END IF; -- Enigma Operation ID is NOT NULL Cursor Found
2254 -- If the operation change flag is "D" then process the operation accordingly .
2255 ELSIF (p_enigma_op_rec.DML_OPERATION = 'D') THEN
2256
2257 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2258 THEN
2259 fnd_log.string(fnd_log.level_statement,l_debug_module,'Operation Flag is D');
2260 END IF;
2261
2262 l_get_latest_oper_rev := NULL;
2263
2264 OPEN get_latest_oper_rev (p_enigma_op_rec.ENIGMA_OP_ID);
2265 FETCH get_latest_oper_rev INTO
2266 l_get_latest_oper_rev.operation_id,
2267 l_get_latest_oper_rev.object_version_number,
2268 l_get_latest_oper_rev.revision_status;
2269 CLOSE get_latest_oper_rev;
2270
2271 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2272 THEN
2273 fnd_log.string(fnd_log.level_statement,l_debug_module,'l_get_latest_oper_rev.operation_id ->' || l_get_latest_oper_rev.operation_id);
2274 fnd_log.string(fnd_log.level_statement,l_debug_module,'l_get_latest_oper_rev.object_version_number ->' || l_get_latest_oper_rev.object_version_number);
2275 fnd_log.string(fnd_log.level_statement,l_debug_module,'l_get_latest_oper_rev.revision_status ->' || l_get_latest_oper_rev.revision_status);
2276 END IF;
2277
2278 IF l_get_latest_oper_rev.operation_id IS NOT NULL THEN
2279
2280 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2281 THEN
2282 fnd_log.string(fnd_log.level_statement,l_debug_module,'Delete :Cursor Found :Operation Exist ');
2283 END IF;
2284
2285 -- IF the operation is in Approval Pending status , then insert the operation record into the staging table with status as pending.
2286
2287 -- This table is going to be restructured and based on new table this part of code will be modified.
2288 IF ( upper(l_get_latest_oper_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
2289 FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OP_APR_PENDING');
2290 FND_MSG_PUB.ADD;
2291 RAISE FND_API.G_EXC_ERROR;
2292 /*OP_interface_insert_row(
2293 X_CONTEXT => p_context,
2294 X_PUBLISH_DATE => p_pub_date,
2295 X_ENIGMA_OPERATION_ID => p_enigma_op_rec.ENIGMA_OP_ID,
2296 X_DML_OPERATION => p_enigma_op_rec.DML_OPERATION,
2297 X_DESCRIPTION => p_enigma_op_rec.description,
2298 X_STATUS => 'PENDING',
2299 X_REASON => 'Operation is in Pending Status',
2300 X_ENIGMA_DOC_ID => p_enigma_op_rec.ENIGMA_DOC_ID
2301 );*/
2302 ELSE
2303 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2304 THEN
2305 fnd_log.string(fnd_log.level_statement,l_debug_module,'Inside else , calling delete operation');
2306 END IF;
2307
2308 -- Call the delete operation API
2309 AHL_RM_OPERATION_PVT.delete_operation
2310 (
2311 1.0,
2312 FND_API.G_TRUE,
2313 FND_API.G_FALSE,
2314 FND_API.G_VALID_LEVEL_FULL,
2315 FND_API.G_FALSE,
2316 NULL,
2317 x_return_status,
2318 x_msg_count,
2319 x_msg_data,
2320 l_get_latest_oper_rev.operation_id,
2321 l_get_latest_oper_rev.object_version_number
2322 );
2323
2324 IF (l_log_statement >= l_log_current_level) THEN
2325 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OPERATION_PVT.delete_operation');
2326 END IF;
2327
2328 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
2329 IF (l_log_statement >= l_log_current_level) THEN
2330 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.delete_operation Error');
2331 END IF;
2332 RAISE FND_API.G_EXC_ERROR;
2333 END IF;
2334
2335 IF (l_log_statement >= l_log_current_level) THEN
2336 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.delete_operation -> deletion Successful');
2337 END IF;
2338 END IF; --Status
2339 ELSE
2340 -- If the operation is not found, then raise an error
2341 FND_MESSAGE.SET_NAME('AHL', 'AHL_ENIGMA_OPER_DONOT_EXIST');
2342 FND_MSG_PUB.ADD;
2343 RAISE FND_API.G_EXC_ERROR;
2344 END IF; -- Enigma operation ID not null
2345 END IF; -- Change Flag "D"
2346
2347
2348 -- Check Error Message stack.
2349 x_msg_count := FND_MSG_PUB.count_msg;
2350 IF x_msg_count > 0
2351 THEN
2352 RAISE FND_API.G_EXC_ERROR;
2353 END IF;
2354
2355 -- Standard check for p_commit
2356 IF FND_API.To_Boolean (p_commit)
2357 THEN
2358 COMMIT WORK;
2359 END IF;
2360
2361 -- Standard call to get message count and if count is 1, get message info
2362 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2363 p_data => x_msg_data,
2364 p_encoded => fnd_api.g_false );
2365
2366 EXCEPTION
2367 WHEN FND_API.G_EXC_ERROR THEN
2368 x_return_status := FND_API.G_RET_STS_ERROR;
2369 Rollback to Process_OP_Details_SP;
2370 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2371 p_data => x_msg_data,
2372 p_encoded => fnd_api.g_false);
2373 IF (upper(l_get_latest_oper_rev.revision_status) = 'APPROVAL_PENDING') THEN
2374 isrecoverable := 'Y';
2375 ELSE
2376 isrecoverable := 'N';
2377 END IF;
2378 Update_OP_Interface_table
2379 (
2380 p_enigma_op_rec,
2381 p_context,
2382 p_pub_date,
2383 isrecoverable,
2384 x_msg_data
2385 );
2386
2387 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2389 Rollback to Process_OP_Details_SP;
2390 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2391 p_data => x_msg_data,
2392 p_encoded => fnd_api.g_false);
2393 isrecoverable := 'N';
2394 Update_OP_Interface_table
2395 (
2396 p_enigma_op_rec,
2397 p_context,
2398 p_pub_date,
2399 isrecoverable,
2400 x_msg_data
2401 );
2402
2403 WHEN OTHERS THEN
2404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2405 Rollback to Process_OP_Details_SP;
2406 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2407 THEN
2408 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2409 p_procedure_name => 'Process_OP_Details_SP',
2410 p_error_text => SUBSTR(SQLERRM,1,240));
2411 END IF;
2412 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2413 p_data => x_msg_data,
2414 p_encoded => fnd_api.g_false);
2415 isrecoverable := 'N';
2416 Update_OP_Interface_table
2417 (
2418 p_enigma_op_rec,
2419 p_context,
2420 p_pub_date,
2421 isrecoverable,
2422 x_msg_data
2423 );
2424
2425
2426 END Process_OP_Details;
2427
2428 PROCEDURE UPLOAD_REVISION_REPORT(
2429 p_file_name IN VARCHAR2,
2430 x_file_id OUT NOCOPY NUMBER,
2431 x_return_status OUT NOCOPY VARCHAR2
2432 )
2433 IS
2434
2435 l_contentBlob BLOB;
2436 l_inputFilePtr BFILE;
2437 seqNo NUMBER;
2438 l_api_name CONSTANT VARCHAR2(30) := 'UPLOAD_REVISION_REPORT';
2439 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||l_api_name;
2440
2441 BEGIN
2442
2443 x_return_status := FND_API.G_RET_STS_SUCCESS;
2444
2445 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2446 THEN
2447
2448 fnd_log.string
2449 (
2450 fnd_log.level_statement,
2451 l_debug_module,
2452 'inside UPLOAD_REVISION_REPORT->p_file_name->'||p_file_name
2453 );
2454 END IF;
2455
2456
2457 IF p_file_name IS NULL THEN
2458 FND_MESSAGE.set_name( 'AHL','AHL_COM_REQD_PARAM_MISSING' );
2459 FND_MSG_PUB.add;
2460 x_return_status := FND_API.G_RET_STS_ERROR;
2461
2462 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2463 fnd_log.string
2464 (
2465 fnd_log.level_error,
2466 'ahl.plsql.'||g_pkg_name||'.'||'upload_revision_report'||':',
2467 'Revision report file name is null'
2468 );
2469 END IF;
2470 RETURN;
2471 END IF;
2472
2473 l_inputFilePtr := BFILENAME('INPUTDIR', p_file_name);
2474 dbms_lob.open(l_inputFilePtr, dbms_lob.lob_readonly);
2475
2476 dbms_lob.createtemporary(l_contentBlob,TRUE);
2477 dbms_lob.open(l_contentBlob,dbms_lob.lob_readWrite);
2478 dbms_lob.loadfromfile(l_contentBlob,l_inputFilePtr,dbms_lob.getlength(l_inputFilePtr));
2479
2480 dbms_lob.fileclose(l_inputFilePtr);
2481 dbms_lob.close(l_contentBlob);
2482
2483 select fnd_lobs_s.nextval into seqNo from dual;
2484
2485 insert into fnd_lobs(
2486 file_id,
2487 file_name,
2488 file_content_type,
2489 language,
2490 file_data,
2491 file_format,
2492 upload_date
2493 )
2494 values(seqNo,
2495 p_file_name,
2496 'application/octet-stream',
2497 'US',
2498 l_contentBlob,
2499 'binary',
2500 sysdate
2501 );
2502
2503 --File upload is successfull assign file_id to x_file_id output variable
2504 x_file_id := seqNo;
2505
2506
2507 EXCEPTION
2508 WHEN OTHERS THEN
2509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2510 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2511 THEN
2512 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2513 p_procedure_name => 'UPLOAD_REVISION_REPORT',
2514 p_error_text => SUBSTR(SQLERRM,1,240));
2515
2516 END IF;
2517
2518 END UPLOAD_REVISION_REPORT;
2519
2520 END AHL_ENIGMA_ROUTE_OP_PUB;