[Home] [Help]
PACKAGE BODY: APPS.AHL_MEL_CDL_APPROVALS_PVT
Source
1 PACKAGE BODY AHL_MEL_CDL_APPROVALS_PVT AS
2 /* $Header: AHLVMAPB.pls 120.6.12020000.2 2012/12/10 13:53:15 shnatu ship $ */
3
4 --------------------
5 -- Common cursors --
6 --------------------
7 CURSOR get_mel_cdl_details
8 (
9 p_mel_cdl_header_id number
10 )
11 IS
12 SELECT pcn.name,
13 hdr.pc_node_id,
14 hdr.mel_cdl_type_code,
15 hdr.revision,
16 hdr.version_number,
17 hdr.object_version_number,
18 hdr.revision_date
19 FROM ahl_mel_cdl_headers hdr, ahl_pc_nodes_b pcn
20 WHERE pcn.pc_node_id = hdr.pc_node_id AND
21 hdr.mel_cdl_header_id = p_mel_cdl_header_id;
22
23 -- get mel/cdl details for a NR.
24 CURSOR get_ue_mel_cdl_details
25 (
26 p_unit_deferral_id IN NUMBER
27 )
28 IS
29 SELECT
30 ue.unit_effectivity_id,
31 ue.mel_cdl_type_code,
32 ue.log_series_code,
33 ue.log_series_number,
34 mca.ATA_CODE,
35 csi.serial_number,
36 mtl.concatenated_segments item_number,
37 cs.incident_number,
38 cs.summary,
39 udf.object_version_number
40 FROM ahl_unit_deferrals_b udf, ahl_unit_effectivities_b ue,
41 cs_incidents_all_vl cs, ahl_mel_cdl_ata_sequences mca, csi_item_instances csi,
42 jtf_notes_vl note, mtl_system_items_kfv mtl
43 WHERE udf.unit_effectivity_id = ue.unit_effectivity_id
44 AND ue.csi_item_instance_id = csi.instance_id
45 AND ue.cs_incident_id = cs.incident_id
46 AND udf.ata_sequence_id = mca.MEL_CDL_ATA_SEQUENCE_ID
47 AND note.source_object_code(+) = 'AHL_MEL_CDL'
48 AND note.source_object_id(+) = mca.MEL_CDL_ATA_SEQUENCE_ID
49 AND csi.inventory_item_id = mtl.inventory_item_id
50 AND csi.inv_master_organization_id = mtl.organization_id
51 AND udf.unit_deferral_id = p_unit_deferral_id;
52
53
54 ------------------------------------
55 -- Common constants and variables --
56 ------------------------------------
57 l_dummy_varchar VARCHAR2(1);
58 l_mel_cdl_rec get_mel_cdl_details%rowtype;
59 l_ue_mel_cdl_details_rec get_ue_mel_cdl_details%ROWTYPE;
60
61 G_DEFERRAL_REJECTED CONSTANT VARCHAR2(30) := 'DEFERRAL_REJECTED';
62
63 ----------------------
64 -- Local Procedures --
65 ----------------------
66 -- procedure to revert NR status and workorder status.
67 PROCEDURE NR_Rollback_Status (p_unit_deferral_id IN NUMBER,
68 p_unit_deferral_ovn IN NUMBER,
69 p_new_status IN NUMBER,
70 p_itemtype IN VARCHAR2,
71 p_itemkey IN VARCHAR2,
72 p_actid IN NUMBER,
73 p_funcmode IN VARCHAR2,
74 x_resultout OUT NOCOPY VARCHAR2);
75
76 PROCEDURE SET_ACTIVITY_DETAILS
77 (
78 itemtype IN VARCHAR2,
79 itemkey IN VARCHAR2,
80 actid IN NUMBER,
81 funcmode IN VARCHAR2,
82 resultout OUT NOCOPY VARCHAR2
83 )
84 IS
85 -- Declare local variables
86 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.SET_ACTIVITY_DETAILS';
87
88 l_return_status VARCHAR2(1);
89 l_msg_count NUMBER;
90 l_msg_data VARCHAR2(2000);
91
92 l_object_id NUMBER;
93 l_object_ovn NUMBER;
94 l_object_details AHL_GENERIC_APRV_PVT.OBJRECTYP;
95 l_approval_rule_id NUMBER;
96 l_approver_seq NUMBER;
97 l_subject VARCHAR2(500);
98 l_error_msg VARCHAR2(2000);
99
100 BEGIN
101
102 FND_MSG_PUB.INITIALIZE;
103
104 l_return_status := FND_API.G_RET_STS_SUCCESS;
105
106 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
107 THEN
108 fnd_log.string
109 (
110 fnd_log.level_procedure,
111 l_debug_module||'.begin',
112 'At the start of PLSQL procedure'
113 );
114 END IF;
115
116 l_object_id := wf_engine.getitemattrnumber
117 (
118 itemtype => itemtype,
119 itemkey => itemkey,
120 aname => 'OBJECT_ID'
121 );
122
123 l_object_ovn := wf_engine.getitemattrnumber
124 (
125 itemtype => itemtype,
126 itemkey => itemkey,
127 aname => 'OBJECT_VER'
128 );
129
130 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
131 THEN
132 fnd_log.string
133 (
134 fnd_log.level_statement,
135 l_debug_module,
136 'OBJECT_ID='||l_object_id||' OBJECT_VER='||l_object_ovn
137 );
138 END IF;
139
140 l_object_details.operating_unit_id := NULL;
141 l_object_details.priority := 'STANDARD';
142
143 --
144 -- RUN mode
145 --
146 IF (funcmode = 'RUN')
147 THEN
148
149 OPEN get_mel_cdl_details(l_object_id);
150 FETCH get_mel_cdl_details into l_mel_cdl_rec;
151 IF (get_mel_cdl_details%NOTFOUND OR l_mel_cdl_rec.object_version_number <> l_object_ovn)
152 THEN
153 CLOSE get_mel_cdl_details;
154
155 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ID_INVALID');
156 fnd_message.set_token('HRD_ID', l_object_id, false);
157 fnd_msg_pub.add;
158
159 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
160 THEN
161 fnd_log.message
162 (
163 fnd_log.level_exception,
164 l_debug_module,
165 false
166 );
167 END IF;
168
169 resultout := 'COMPLETE:ERROR';
170 RAISE FND_API.G_EXC_ERROR;
171
172 END IF;
173 CLOSE get_mel_cdl_details;
174
175 /* FORWARD_SUBJECT */
176 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_FORWARD_SUBJ');
177 fnd_message.set_token('HRD_ID',l_object_id, false);
178 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
179 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
180 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
181 l_subject := fnd_message.get;
182
183 wf_engine.setitemattrtext
184 (
185 itemtype => itemtype,
186 itemkey => itemkey,
187 aname => 'FORWARD_SUBJECT',
188 avalue => l_subject
189 );
190
191 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
192 THEN
193 fnd_log.string
194 (
195 fnd_log.level_statement,
196 l_debug_module,
197 'FORWARD_SUBJECT='||l_subject
198 );
199 END IF;
200
201 /* APPROVAL_SUBJECT */
202 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_APPROVAL_SUBJ');
203 fnd_message.set_token('HRD_ID',l_object_id, false);
204 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
205 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
206 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
207 l_subject := fnd_message.get;
208
209 wf_engine.setitemattrtext
210 (
211 itemtype => itemtype,
212 itemkey => itemkey,
213 aname => 'APPROVAL_SUBJECT',
214 avalue => l_subject
215 );
216
217 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
218 THEN
219 fnd_log.string
220 (
221 fnd_log.level_statement,
222 l_debug_module,
223 'APPROVAL_SUBJECT='||l_subject
224 );
225 END IF;
226
227 /* REJECT_SUBJECT */
228 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_REJECT_SUBJ');
229 fnd_message.set_token('HRD_ID',l_object_id, false);
230 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
231 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
232 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
233 l_subject := fnd_message.get;
234
235 wf_engine.setitemattrtext
236 (
237 itemtype => itemtype,
238 itemkey => itemkey,
239 aname => 'REJECT_SUBJECT',
240 avalue => l_subject
241 );
242
243 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
244 THEN
245 fnd_log.string
246 (
247 fnd_log.level_statement,
248 l_debug_module,
249 'REJECT_SUBJECT='||l_subject
250 );
251 END IF;
252
253 /* APPROVED_SUBJECT */
254 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_APPROVED_SUBJ');
255 fnd_message.set_token('HRD_ID',l_object_id, false);
256 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
257 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
258 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
259 l_subject := fnd_message.get;
260
261 wf_engine.setitemattrtext
262 (
263 itemtype => itemtype,
264 itemkey => itemkey,
265 aname => 'APPROVED_SUBJECT',
266 avalue => l_subject
267 );
268
269 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
270 THEN
271 fnd_log.string
272 (
273 fnd_log.level_statement,
274 l_debug_module,
275 'APPROVED_SUBJECT='||l_subject
276 );
277 END IF;
278
279 /* FINAL_SUBJECT */
280 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_FINAL_SUBJ');
281 fnd_message.set_token('HRD_ID',l_object_id, false);
282 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
283 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
284 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
285 l_subject := fnd_message.get;
286
287 wf_engine.setitemattrtext
288 (
289 itemtype => itemtype,
290 itemkey => itemkey,
291 aname => 'FINAL_SUBJECT',
292 avalue => l_subject
293 );
294
295 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
296 THEN
297 fnd_log.string
298 (
302 );
299 fnd_log.level_statement,
300 l_debug_module,
301 'FINAL_SUBJECT='||l_subject
303 END IF;
304
305 /* REMIND_SUBJECT */
306 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_REMIND_SUBJ');
307 fnd_message.set_token('HRD_ID',l_object_id, false);
308 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
309 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
310 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
311 l_subject := fnd_message.get;
312
313 wf_engine.setitemattrtext
314 (
315 itemtype => itemtype,
316 itemkey => itemkey,
317 aname => 'REMIND_SUBJECT',
318 avalue => l_subject
319 );
320
321 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
322 THEN
323 fnd_log.string
324 (
325 fnd_log.level_statement,
326 l_debug_module,
327 'REMIND_SUBJECT='||l_subject
328 );
329 END IF;
330
331 /* ERROR_SUBJECT */
332 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ERROR_SUBJ');
333 fnd_message.set_token('HRD_ID',l_object_id, false);
334 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
335 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
336 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
337 l_subject := fnd_message.get;
338
339 wf_engine.setitemattrtext
340 (
341 itemtype => itemtype,
342 itemkey => itemkey,
343 aname => 'ERROR_SUBJECT',
344 avalue => l_subject
345 );
346
347 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
348 THEN
349 fnd_log.string
350 (
351 fnd_log.level_statement,
352 l_debug_module,
353 'ERROR_SUBJECT='||l_subject
354 );
355 END IF;
356
357 /* Getting approver details */
358 AHL_GENERIC_APRV_PVT.GET_APPROVAL_DETAILS
359 (
360 p_object => G_APPR_OBJ,
361 p_approval_type => G_APPR_TYPE,
362 p_object_details => l_object_details,
363 x_approval_rule_id => l_approval_rule_id,
364 x_approver_seq => l_approver_seq,
365 x_return_status => l_return_status
366 );
367
368 IF l_return_status = FND_API.G_RET_STS_SUCCESS
369 THEN
370 wf_engine.setitemattrnumber
371 (
372 itemtype => itemtype,
373 itemkey => itemkey,
374 aname => 'RULE_ID',
375 avalue => l_approval_rule_id
376 );
377
378 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
379 THEN
380 fnd_log.string
381 (
382 fnd_log.level_statement,
383 l_debug_module,
384 'RULE_ID='||l_approval_rule_id
385 );
386 END IF;
387
388 wf_engine.setitemattrnumber
389 (
390 itemtype => itemtype,
391 itemkey => itemkey,
392 aname => 'APPROVER_SEQ',
393 avalue => l_approver_seq
394 );
395
396 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
397 THEN
398 fnd_log.string
399 (
400 fnd_log.level_statement,
401 l_debug_module,
402 'APPROVER_SEQ='||l_approver_seq
403 );
404 END IF;
405
406 resultout := 'COMPLETE:SUCCESS';
407 RETURN;
408 ELSE
409 resultout := 'COMPLETE:ERROR';
410 RAISE FND_API.G_EXC_ERROR;
411 END IF;
412 END IF;
413
414 --
415 -- CANCEL mode
416 --
417 IF (funcmode = 'CANCEL')
418 THEN
419 resultout := 'COMPLETE:';
420 RETURN;
421 END IF;
422
423 --
424 -- TIMEOUT mode
425 --
426 IF (funcmode = 'TIMEOUT')
427 THEN
428 resultout := 'COMPLETE:';
429 RETURN;
430 END IF;
431
432 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
433 THEN
434 fnd_log.string
435 (
436 fnd_log.level_procedure,
437 l_debug_module||'.end',
438 'At the end of PLSQL procedure'
439 );
440 END IF;
441
442 EXCEPTION
443 WHEN FND_API.G_EXC_ERROR THEN
444 FND_MSG_PUB.Count_And_Get
445 (
446 p_encoded => FND_API.G_FALSE,
447 p_count => l_msg_count,
448 p_data => l_msg_data
449 );
450
451 AHL_GENERIC_APRV_PVT.handle_error
452 (
453 p_itemtype => itemtype,
454 p_itemkey => itemkey,
455 p_msg_count => l_msg_count,
456 p_msg_data => l_msg_data,
457 p_attr_name => 'ERROR_MSG',
458 x_error_msg => l_error_msg
459 );
460
464 (
461 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
462 THEN
463 fnd_log.string
465 fnd_log.level_exception,
466 l_debug_module,
467 l_error_msg
468 );
469 END IF;
470
471 wf_core.context
472 (
473 'AHL_MEL_CDL_APPROVALS_PVT',
474 'SET_ACTIVITY_DETAILS',
475 itemtype,
476 itemkey,
477 actid,
478 funcmode,
479 l_error_msg
480 );
481 resultout := 'COMPLETE:ERROR';
482 RAISE;
483
484 WHEN OTHERS THEN
485 wf_core.context
486 (
487 'AHL_MEL_CDL_APPROVALS_PVT',
488 'SET_ACTIVITY_DETAILS',
489 itemtype,
490 itemkey,
491 actid,
492 'Unexpected Error! '||SUBSTR(SQLERRM,1,240)
493 );
494 RAISE;
495
496 END SET_ACTIVITY_DETAILS;
497
498 PROCEDURE NTF_FORWARD_FYI
499 (
500 document_id IN VARCHAR2,
501 display_type IN VARCHAR2,
502 document IN OUT NOCOPY VARCHAR2,
503 document_type IN OUT NOCOPY VARCHAR2
504 )
505 IS
506 -- Declare local variables
507 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NTF_FORWARD_FYI';
508
509 l_return_status VARCHAR2(1);
510 l_msg_count NUMBER;
511 l_msg_data VARCHAR2(2000);
512
513 l_hyphen_pos1 NUMBER;
514 l_item_type VARCHAR2(30);
515 l_item_key VARCHAR2(30);
516 l_approver VARCHAR2(30);
517 l_body VARCHAR2(3500);
518 l_object_type VARCHAR2(30);
519 l_object_id NUMBER;
520 l_error_msg VARCHAR2(2000);
521
522 BEGIN
523
524 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
525 THEN
526 fnd_log.string
527 (
528 fnd_log.level_procedure,
529 l_debug_module||'.begin',
530 'At the start of PLSQL procedure'
531 );
532 END IF;
533
534 document_type := 'text/plain';
535
536 -- parse document_id for the ':' dividing item type name from item key value
537 l_hyphen_pos1 := INSTR (document_id, ':');
538 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
539 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
540
541 l_object_type := wf_engine.getitemattrtext
542 (
543 itemtype => l_item_type,
544 itemkey => l_item_key,
545 aname => 'OBJECT_TYPE'
546 );
547
548 l_object_id := wf_engine.getitemattrNumber
549 (
550 itemtype => l_item_type,
551 itemkey => l_item_key,
552 aname => 'OBJECT_ID'
553 );
554
555 l_approver := wf_engine.getitemattrtext
556 (
557 itemtype => l_item_type,
558 itemkey => l_item_key,
559 aname => 'APPROVER'
560 );
561
562 OPEN get_mel_cdl_details(l_object_id);
563 FETCH get_mel_cdl_details into l_mel_cdl_rec;
564 IF (get_mel_cdl_details%NOTFOUND)
565 THEN
566 CLOSE get_mel_cdl_details;
567
568 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ID_INVALID');
569 fnd_message.set_token('HRD_ID', l_object_id, false);
570 fnd_msg_pub.add;
571
572 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
573 THEN
574 fnd_log.message
575 (
576 fnd_log.level_exception,
577 l_debug_module,
578 false
579 );
580 END IF;
581
582 RAISE FND_API.G_EXC_ERROR;
583
584 ELSE
585 CLOSE get_mel_cdl_details;
586
587 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_FYI_FWD');
588 fnd_message.set_token('HRD_ID',l_object_id, false);
589 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
590 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
591 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
592 fnd_message.set_token('APPR_NAME',l_approver, false);
593 l_body := fnd_message.get;
594
595 END IF;
596
597 document := document || l_body;
598
599 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
600 THEN
601 fnd_log.string
602 (
603 fnd_log.level_statement,
604 l_debug_module,
605 'document='||document
606 );
607 END IF;
608
609 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
610 THEN
611 fnd_log.string
612 (
613 fnd_log.level_procedure,
614 l_debug_module||'.end',
615 'At the end of PLSQL procedure'
616 );
617 END IF;
618
619 RETURN;
620
621 EXCEPTION
622 WHEN FND_API.G_EXC_ERROR THEN
623 FND_MSG_PUB.Count_And_Get
624 (
625 p_encoded => FND_API.G_FALSE,
626 p_count => l_msg_count,
627 p_data => l_msg_data
628 );
629
630 AHL_GENERIC_APRV_PVT.handle_error
631 (
632 p_itemtype => l_item_type,
636 p_attr_name => 'ERROR_MSG',
633 p_itemkey => l_item_key,
634 p_msg_count => l_msg_count,
635 p_msg_data => l_msg_data,
637 x_error_msg => l_error_msg
638 );
639
640 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
641 THEN
642 fnd_log.string
643 (
644 fnd_log.level_exception,
645 l_debug_module,
646 l_error_msg
647 );
648 END IF;
649
650 wf_core.context
651 (
652 'AHL_MEL_CDL_APPROVALS_PVT',
653 'NTF_FORWARD_FYI',
654 l_item_type,
655 l_item_key,
656 l_error_msg
657 );
658 RAISE;
659
660 WHEN OTHERS THEN
661 wf_core.context
662 (
663 'AHL_MEL_CDL_APPROVALS_PVT',
664 'NTF_FORWARD_FYI',
665 l_item_type,
666 l_item_key
667 );
668 RAISE;
669 END NTF_FORWARD_FYI;
670
671 PROCEDURE NTF_APPROVED_FYI
672 (
673 document_id IN VARCHAR2,
674 display_type IN VARCHAR2,
675 document IN OUT NOCOPY VARCHAR2,
676 document_type IN OUT NOCOPY VARCHAR2
677 )
678 IS
679 -- Declare local variables
680 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NTF_APPROVED_FYI';
681
682 l_return_status VARCHAR2(1);
683 l_msg_count NUMBER;
684 l_msg_data VARCHAR2(2000);
685
686 l_hyphen_pos1 NUMBER;
687 l_item_type VARCHAR2(30);
688 l_item_key VARCHAR2(30);
689 l_approver VARCHAR2(30);
690 l_body VARCHAR2(3500);
691 l_object_type VARCHAR2(30);
692 l_object_id NUMBER;
693 l_error_msg VARCHAR2(2000);
694
695 BEGIN
696
697 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
698 THEN
699 fnd_log.string
700 (
701 fnd_log.level_procedure,
702 l_debug_module||'.begin',
703 'At the start of PLSQL procedure'
704 );
705 END IF;
706
707 document_type := 'text/plain';
708
709 -- parse document_id for the ':' dividing item type name from item key value
710 l_hyphen_pos1 := INSTR (document_id, ':');
711 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
712 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
713
714 l_object_type := wf_engine.getitemattrtext
715 (
716 itemtype => l_item_type,
717 itemkey => l_item_key,
718 aname => 'OBJECT_TYPE'
719 );
720
721 l_object_id := wf_engine.getitemattrNumber
722 (
723 itemtype => l_item_type,
724 itemkey => l_item_key,
725 aname => 'OBJECT_ID'
726 );
727
728 l_approver := wf_engine.getitemattrtext
729 (
730 itemtype => l_item_type,
731 itemkey => l_item_key,
732 aname => 'APPROVER'
733 );
734
735 OPEN get_mel_cdl_details(l_object_id);
736 FETCH get_mel_cdl_details into l_mel_cdl_rec;
737 IF (get_mel_cdl_details%NOTFOUND)
738 THEN
739 CLOSE get_mel_cdl_details;
740
741 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ID_INVALID');
742 fnd_message.set_token('HRD_ID', l_object_id, false);
743 fnd_msg_pub.add;
744
745 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
746 THEN
747 fnd_log.message
748 (
749 fnd_log.level_exception,
750 l_debug_module,
751 false
752 );
753 END IF;
754
755 RAISE FND_API.G_EXC_ERROR;
756
757 ELSE
758 CLOSE get_mel_cdl_details;
759
760 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_FYI_APPRVD');
761 fnd_message.set_token('HRD_ID',l_object_id, false);
762 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
763 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
764 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
765 fnd_message.set_token('APPR_NAME',l_approver, false);
766 l_body := fnd_message.get;
767
768 END IF;
769
770 document := document || l_body;
771
772 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
773 THEN
774 fnd_log.string
775 (
776 fnd_log.level_statement,
777 l_debug_module,
778 'document='||document
779 );
780 END IF;
781
782 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
783 THEN
784 fnd_log.string
785 (
786 fnd_log.level_procedure,
787 l_debug_module||'.end',
788 'At the end of PLSQL procedure'
789 );
790 END IF;
791
792 RETURN;
793
794 EXCEPTION
795 WHEN FND_API.G_EXC_ERROR THEN
796 FND_MSG_PUB.Count_And_Get
797 (
798 p_encoded => FND_API.G_FALSE,
799 p_count => l_msg_count,
800 p_data => l_msg_data
801 );
805 p_itemtype => l_item_type,
802
803 AHL_GENERIC_APRV_PVT.handle_error
804 (
806 p_itemkey => l_item_key,
807 p_msg_count => l_msg_count,
808 p_msg_data => l_msg_data,
809 p_attr_name => 'ERROR_MSG',
810 x_error_msg => l_error_msg
811 );
812
813 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
814 THEN
815 fnd_log.string
816 (
817 fnd_log.level_exception,
818 l_debug_module,
819 l_error_msg
820 );
821 END IF;
822
823 wf_core.context
824 (
825 'AHL_MEL_CDL_APPROVALS_PVT',
826 'NTF_APPROVED_FYI',
827 l_item_type,
828 l_item_key,
829 l_error_msg
830 );
831 RAISE;
832
833 WHEN OTHERS THEN
834 wf_core.context
835 (
836 'AHL_MEL_CDL_APPROVALS_PVT',
837 'NTF_APPROVED_FYI',
838 l_item_type,
839 l_item_key
840 );
841 RAISE;
842 END NTF_APPROVED_FYI;
843
844 PROCEDURE NTF_FINAL_APPROVAL_FYI
845 (
846 document_id IN VARCHAR2,
847 display_type IN VARCHAR2,
848 document IN OUT NOCOPY VARCHAR2,
849 document_type IN OUT NOCOPY VARCHAR2
850 )
851 IS
852 -- Declare local variables
853 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NTF_FINAL_APPROVAL_FYI';
854
855 l_return_status VARCHAR2(1);
856 l_msg_count NUMBER;
857 l_msg_data VARCHAR2(2000);
858
859 l_hyphen_pos1 NUMBER;
860 l_item_type VARCHAR2(30);
861 l_item_key VARCHAR2(30);
862 l_body VARCHAR2(3500);
863 l_object_type VARCHAR2(30);
864 l_object_id NUMBER;
865 l_error_msg VARCHAR2(2000);
866
867 BEGIN
868
869 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
870 THEN
871 fnd_log.string
872 (
873 fnd_log.level_procedure,
874 l_debug_module||'.begin',
875 'At the start of PLSQL procedure'
876 );
877 END IF;
878
879 document_type := 'text/plain';
880
881 -- parse document_id for the ':' dividing item type name from item key value
882 l_hyphen_pos1 := INSTR (document_id, ':');
883 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
884 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
885
886 l_object_type := wf_engine.getitemattrtext
887 (
888 itemtype => l_item_type,
889 itemkey => l_item_key,
890 aname => 'OBJECT_TYPE'
891 );
892
893 l_object_id := wf_engine.getitemattrNumber
894 (
895 itemtype => l_item_type,
896 itemkey => l_item_key,
897 aname => 'OBJECT_ID'
898 );
899
900 OPEN get_mel_cdl_details(l_object_id);
901 FETCH get_mel_cdl_details into l_mel_cdl_rec;
902 IF (get_mel_cdl_details%NOTFOUND)
903 THEN
904 CLOSE get_mel_cdl_details;
905
906 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ID_INVALID');
907 fnd_message.set_token('HRD_ID', l_object_id, false);
908 fnd_msg_pub.add;
909
910 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
911 THEN
912 fnd_log.message
913 (
914 fnd_log.level_exception,
915 l_debug_module,
916 false
917 );
918 END IF;
919
920 RAISE FND_API.G_EXC_ERROR;
921
922 ELSE
923 CLOSE get_mel_cdl_details;
924
925 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_FYI_FINAL');
926 fnd_message.set_token('HRD_ID',l_object_id, false);
927 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
928 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
929 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
930 l_body := fnd_message.get;
931
932 END IF;
933
934 document := document || l_body;
935
936 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
937 THEN
938 fnd_log.string
939 (
940 fnd_log.level_statement,
941 l_debug_module,
942 'document='||document
943 );
944 END IF;
945
946 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
947 THEN
948 fnd_log.string
949 (
950 fnd_log.level_procedure,
951 l_debug_module||'.end',
952 'At the end of PLSQL procedure'
953 );
954 END IF;
955
956 RETURN;
957
958 EXCEPTION
959 WHEN FND_API.G_EXC_ERROR THEN
960 FND_MSG_PUB.Count_And_Get
961 (
962 p_encoded => FND_API.G_FALSE,
963 p_count => l_msg_count,
964 p_data => l_msg_data
965 );
966
967 AHL_GENERIC_APRV_PVT.handle_error
968 (
969 p_itemtype => l_item_type,
970 p_itemkey => l_item_key,
974 x_error_msg => l_error_msg
971 p_msg_count => l_msg_count,
972 p_msg_data => l_msg_data,
973 p_attr_name => 'ERROR_MSG',
975 );
976
977 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
978 THEN
979 fnd_log.string
980 (
981 fnd_log.level_exception,
982 l_debug_module,
983 l_error_msg
984 );
985 END IF;
986
987 wf_core.context
988 (
989 'AHL_MEL_CDL_APPROVALS_PVT',
990 'NTF_FINAL_APPROVAL_FYI',
991 l_item_type,
992 l_item_key,
993 l_error_msg
994 );
995 RAISE;
996
997 WHEN OTHERS THEN
998 wf_core.context
999 (
1000 'AHL_MEL_CDL_APPROVALS_PVT',
1001 'NTF_FINAL_APPROVAL_FYI',
1002 l_item_type,
1003 l_item_key
1004 );
1005 RAISE;
1006 END NTF_FINAL_APPROVAL_FYI;
1007
1008 PROCEDURE NTF_REJECTED_FYI
1009 (
1010 document_id IN VARCHAR2,
1011 display_type IN VARCHAR2,
1012 document IN OUT NOCOPY VARCHAR2,
1013 document_type IN OUT NOCOPY VARCHAR2
1014 )
1015 IS
1016 -- Declare local variables
1017 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NTF_REJECTED_FYI';
1018
1019 l_return_status VARCHAR2(1);
1020 l_msg_count NUMBER;
1021 l_msg_data VARCHAR2(2000);
1022
1023 l_hyphen_pos1 NUMBER;
1024 l_item_type VARCHAR2(30);
1025 l_item_key VARCHAR2(30);
1026 l_approver VARCHAR2(30);
1027 l_body VARCHAR2(3500);
1028 l_object_type VARCHAR2(30);
1029 l_object_id NUMBER;
1030 l_error_msg VARCHAR2(2000);
1031
1032 BEGIN
1033
1034 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1035 THEN
1036 fnd_log.string
1037 (
1038 fnd_log.level_procedure,
1039 l_debug_module||'.begin',
1040 'At the start of PLSQL procedure'
1041 );
1042 END IF;
1043
1044 document_type := 'text/plain';
1045
1046 -- parse document_id for the ':' dividing item type name from item key value
1047 l_hyphen_pos1 := INSTR (document_id, ':');
1048 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1049 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1050
1051 l_object_type := wf_engine.getitemattrtext
1052 (
1053 itemtype => l_item_type,
1054 itemkey => l_item_key,
1055 aname => 'OBJECT_TYPE'
1056 );
1057
1058 l_object_id := wf_engine.getitemattrNumber
1059 (
1060 itemtype => l_item_type,
1061 itemkey => l_item_key,
1062 aname => 'OBJECT_ID'
1063 );
1064
1065 l_approver := wf_engine.getitemattrtext
1066 (
1067 itemtype => l_item_type,
1068 itemkey => l_item_key,
1069 aname => 'APPROVER'
1070 );
1071
1072 OPEN get_mel_cdl_details(l_object_id);
1073 FETCH get_mel_cdl_details into l_mel_cdl_rec;
1074 IF (get_mel_cdl_details%NOTFOUND)
1075 THEN
1076 CLOSE get_mel_cdl_details;
1077
1078 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ID_INVALID');
1079 fnd_message.set_token('HRD_ID', l_object_id, false);
1080 fnd_msg_pub.add;
1081
1082 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1083 THEN
1084 fnd_log.message
1085 (
1086 fnd_log.level_exception,
1087 l_debug_module,
1088 false
1089 );
1090 END IF;
1091
1092 RAISE FND_API.G_EXC_ERROR;
1093
1094 ELSE
1095 CLOSE get_mel_cdl_details;
1096
1097 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_FYI_RJCT');
1098 fnd_message.set_token('HRD_ID',l_object_id, false);
1099 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
1100 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
1101 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
1102 fnd_message.set_token('APPR_NAME',l_approver, false);
1103 l_body := fnd_message.get;
1104
1105 END IF;
1106
1107 document := document || l_body;
1108
1109 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1110 THEN
1111 fnd_log.string
1112 (
1113 fnd_log.level_statement,
1114 l_debug_module,
1115 'document='||document
1116 );
1117 END IF;
1118
1119 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1120 THEN
1121 fnd_log.string
1122 (
1123 fnd_log.level_procedure,
1124 l_debug_module||'.end',
1125 'At the end of PLSQL procedure'
1126 );
1127 END IF;
1128
1129 RETURN;
1130
1131 EXCEPTION
1132 WHEN FND_API.G_EXC_ERROR THEN
1133 FND_MSG_PUB.Count_And_Get
1134 (
1135 p_encoded => FND_API.G_FALSE,
1136 p_count => l_msg_count,
1137 p_data => l_msg_data
1138 );
1139
1140 AHL_GENERIC_APRV_PVT.handle_error
1141 (
1145 p_msg_data => l_msg_data,
1142 p_itemtype => l_item_type,
1143 p_itemkey => l_item_key,
1144 p_msg_count => l_msg_count,
1146 p_attr_name => 'ERROR_MSG',
1147 x_error_msg => l_error_msg
1148 );
1149
1150 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1151 THEN
1152 fnd_log.string
1153 (
1154 fnd_log.level_exception,
1155 l_debug_module,
1156 l_error_msg
1157 );
1158 END IF;
1159
1160 wf_core.context
1161 (
1162 'AHL_MEL_CDL_APPROVALS_PVT',
1163 'NTF_REJECTED_FYI',
1164 l_item_type,
1165 l_item_key,
1166 l_error_msg
1167 );
1168 RAISE;
1169
1170 WHEN OTHERS THEN
1171 wf_core.context
1172 (
1173 'AHL_MEL_CDL_APPROVALS_PVT',
1174 'NTF_REJECTED_FYI',
1175 l_item_type,
1176 l_item_key
1177 );
1178 RAISE;
1179 END NTF_REJECTED_FYI;
1180
1181 PROCEDURE NTF_APPROVAL
1182 (
1183 document_id IN VARCHAR2,
1184 display_type IN VARCHAR2,
1185 document IN OUT NOCOPY VARCHAR2,
1186 document_type IN OUT NOCOPY VARCHAR2
1187 )
1188 IS
1189 -- Declare local variables
1190 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NTF_APPROVAL';
1191
1192 l_return_status VARCHAR2(1);
1193 l_msg_count NUMBER;
1194 l_msg_data VARCHAR2(2000);
1195
1196 l_hyphen_pos1 NUMBER;
1197 l_item_type VARCHAR2(30);
1198 l_item_key VARCHAR2(30);
1199 l_requester VARCHAR2(30);
1200 l_requester_note VARCHAR2(4000);
1201 l_body VARCHAR2(3500);
1202 l_object_type VARCHAR2(30);
1203 l_object_id NUMBER;
1204 l_error_msg VARCHAR2(2000);
1205
1206 BEGIN
1207
1208 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1209 THEN
1210 fnd_log.string
1211 (
1212 fnd_log.level_procedure,
1213 l_debug_module||'.begin',
1214 'At the start of PLSQL procedure'
1215 );
1216 END IF;
1217
1218 document_type := 'text/plain';
1219
1220 -- parse document_id for the ':' dividing item type name from item key value
1221 l_hyphen_pos1 := INSTR (document_id, ':');
1222 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1223 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1224
1225 l_object_type := wf_engine.getitemattrtext
1226 (
1227 itemtype => l_item_type,
1228 itemkey => l_item_key,
1229 aname => 'OBJECT_TYPE'
1230 );
1231
1232 l_object_id := wf_engine.getitemattrNumber
1233 (
1234 itemtype => l_item_type,
1235 itemkey => l_item_key,
1236 aname => 'OBJECT_ID'
1237 );
1238
1239 l_requester := wf_engine.getitemattrtext
1240 (
1241 itemtype => l_item_type,
1242 itemkey => l_item_key,
1243 aname => 'REQUESTER'
1244 );
1245
1246 l_requester_note := wf_engine.getitemattrtext
1247 (
1248 itemtype => l_item_type,
1249 itemkey => l_item_key,
1250 aname => 'REQUESTER_NOTE'
1251 );
1252
1253 OPEN get_mel_cdl_details(l_object_id);
1254 FETCH get_mel_cdl_details into l_mel_cdl_rec;
1255 IF (get_mel_cdl_details%NOTFOUND)
1256 THEN
1257 CLOSE get_mel_cdl_details;
1258
1259 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ID_INVALID');
1260 fnd_message.set_token('HRD_ID', l_object_id, false);
1261 fnd_msg_pub.add;
1262
1263 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1264 THEN
1265 fnd_log.message
1266 (
1267 fnd_log.level_exception,
1268 l_debug_module,
1269 false
1270 );
1271 END IF;
1272
1273 RAISE FND_API.G_EXC_ERROR;
1274
1275 ELSE
1276 CLOSE get_mel_cdl_details;
1277
1278 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_APPROVAL');
1279 fnd_message.set_token('HRD_ID',l_object_id, false);
1280 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
1281 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
1282 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
1283 fnd_message.set_token('REQUESTER',l_requester, false);
1284 fnd_message.set_token('NOTE',l_requester_note, false);
1285 l_body := fnd_message.get;
1286
1287 END IF;
1288
1289 document := document || l_body;
1290
1291 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1292 THEN
1293 fnd_log.string
1294 (
1295 fnd_log.level_statement,
1296 l_debug_module,
1297 'document='||document
1298 );
1299 END IF;
1300
1301 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1302 THEN
1303 fnd_log.string
1304 (
1305 fnd_log.level_procedure,
1306 l_debug_module||'.end',
1307 'At the end of PLSQL procedure'
1308 );
1312
1309 END IF;
1310
1311 RETURN;
1313 EXCEPTION
1314 WHEN FND_API.G_EXC_ERROR THEN
1315 FND_MSG_PUB.Count_And_Get
1316 (
1317 p_encoded => FND_API.G_FALSE,
1318 p_count => l_msg_count,
1319 p_data => l_msg_data
1320 );
1321
1322 AHL_GENERIC_APRV_PVT.handle_error
1323 (
1324 p_itemtype => l_item_type,
1325 p_itemkey => l_item_key,
1326 p_msg_count => l_msg_count,
1327 p_msg_data => l_msg_data,
1328 p_attr_name => 'ERROR_MSG',
1329 x_error_msg => l_error_msg
1330 );
1331
1332 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1333 THEN
1334 fnd_log.string
1335 (
1336 fnd_log.level_exception,
1337 l_debug_module,
1338 l_error_msg
1339 );
1340 END IF;
1341
1342 wf_core.context
1343 (
1344 'AHL_MEL_CDL_APPROVALS_PVT',
1345 'NTF_APPROVAL',
1346 l_item_type,
1347 l_item_key,
1348 l_error_msg
1349 );
1350 RAISE;
1351
1352 WHEN OTHERS THEN
1353 wf_core.context
1354 (
1355 'AHL_MEL_CDL_APPROVALS_PVT',
1356 'NTF_APPROVAL',
1357 l_item_type,
1358 l_item_key
1359 );
1360 RAISE;
1361 END NTF_APPROVAL;
1362
1363 PROCEDURE NTF_APPROVAL_REMINDER
1364 (
1365 document_id IN VARCHAR2,
1366 display_type IN VARCHAR2,
1367 document IN OUT NOCOPY VARCHAR2,
1368 document_type IN OUT NOCOPY VARCHAR2
1369 )
1370 IS
1371 -- Declare local variables
1372 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NTF_APPROVAL_REMINDER';
1373
1374 l_return_status VARCHAR2(1);
1375 l_msg_count NUMBER;
1376 l_msg_data VARCHAR2(2000);
1377
1378 l_hyphen_pos1 NUMBER;
1379 l_item_type VARCHAR2(30);
1380 l_item_key VARCHAR2(30);
1381 l_requester VARCHAR2(30);
1382 l_requester_note VARCHAR2(4000);
1383 l_body VARCHAR2(3500);
1384 l_object_type VARCHAR2(30);
1385 l_object_id NUMBER;
1386 l_error_msg VARCHAR2(2000);
1387
1388 BEGIN
1389
1390 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1391 THEN
1392 fnd_log.string
1393 (
1394 fnd_log.level_procedure,
1395 l_debug_module||'.begin',
1396 'At the start of PLSQL procedure'
1397 );
1398 END IF;
1399
1400 document_type := 'text/plain';
1401
1402 -- parse document_id for the ':' dividing item type name from item key value
1403 l_hyphen_pos1 := INSTR (document_id, ':');
1404 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1405 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1406
1407 l_object_type := wf_engine.getitemattrtext
1408 (
1409 itemtype => l_item_type,
1410 itemkey => l_item_key,
1411 aname => 'OBJECT_TYPE'
1412 );
1413
1414 l_object_id := wf_engine.getitemattrNumber
1415 (
1416 itemtype => l_item_type,
1417 itemkey => l_item_key,
1418 aname => 'OBJECT_ID'
1419 );
1420
1421 l_requester := wf_engine.getitemattrtext
1422 (
1423 itemtype => l_item_type,
1424 itemkey => l_item_key,
1425 aname => 'REQUESTER'
1426 );
1427
1428 l_requester_note := wf_engine.getitemattrtext
1429 (
1430 itemtype => l_item_type,
1431 itemkey => l_item_key,
1432 aname => 'REQUESTER_NOTE'
1433 );
1434
1435 OPEN get_mel_cdl_details(l_object_id);
1436 FETCH get_mel_cdl_details into l_mel_cdl_rec;
1437 IF (get_mel_cdl_details%NOTFOUND)
1438 THEN
1439 CLOSE get_mel_cdl_details;
1440
1441 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ID_INVALID');
1442 fnd_message.set_token('HRD_ID', l_object_id, false);
1443 fnd_msg_pub.add;
1444
1445 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1446 THEN
1447 fnd_log.message
1448 (
1449 fnd_log.level_exception,
1450 l_debug_module,
1451 false
1452 );
1453 END IF;
1454
1455 RAISE FND_API.G_EXC_ERROR;
1456
1457 ELSE
1458 CLOSE get_mel_cdl_details;
1459
1460 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_REMIND');
1461 fnd_message.set_token('HRD_ID',l_object_id, false);
1462 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
1463 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
1464 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
1465 fnd_message.set_token('REQUESTER',l_requester, false);
1466 fnd_message.set_token('NOTE',l_requester_note, false);
1467 l_body := fnd_message.get;
1468
1469 END IF;
1470
1471 document := document || l_body;
1472
1473 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1474 THEN
1475 fnd_log.string
1476 (
1477 fnd_log.level_statement,
1481 END IF;
1478 l_debug_module,
1479 'document='||document
1480 );
1482
1483 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1484 THEN
1485 fnd_log.string
1486 (
1487 fnd_log.level_procedure,
1488 l_debug_module||'.end',
1489 'At the end of PLSQL procedure'
1490 );
1491 END IF;
1492
1493 RETURN;
1494
1495 EXCEPTION
1496 WHEN FND_API.G_EXC_ERROR THEN
1497 FND_MSG_PUB.Count_And_Get
1498 (
1499 p_encoded => FND_API.G_FALSE,
1500 p_count => l_msg_count,
1501 p_data => l_msg_data
1502 );
1503
1504 AHL_GENERIC_APRV_PVT.handle_error
1505 (
1506 p_itemtype => l_item_type,
1507 p_itemkey => l_item_key,
1508 p_msg_count => l_msg_count,
1509 p_msg_data => l_msg_data,
1510 p_attr_name => 'ERROR_MSG',
1511 x_error_msg => l_error_msg
1512 );
1513
1514 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1515 THEN
1516 fnd_log.string
1517 (
1518 fnd_log.level_exception,
1519 l_debug_module,
1520 l_error_msg
1521 );
1522 END IF;
1523
1524 wf_core.context
1525 (
1526 'AHL_MEL_CDL_APPROVALS_PVT',
1527 'NTF_APPROVAL_REMINDER',
1528 l_item_type,
1529 l_item_key,
1530 l_error_msg
1531 );
1532 RAISE;
1533
1534 WHEN OTHERS THEN
1535 wf_core.context
1536 (
1537 'AHL_MEL_CDL_APPROVALS_PVT',
1538 'NTF_APPROVAL_REMINDER',
1539 l_item_type,
1540 l_item_key
1541 );
1542 RAISE;
1543 END NTF_APPROVAL_REMINDER;
1544
1545 PROCEDURE NTF_ERROR_ACT
1546 (
1547 document_id IN VARCHAR2,
1548 display_type IN VARCHAR2,
1549 document IN OUT NOCOPY VARCHAR2,
1550 document_type IN OUT NOCOPY VARCHAR2
1551 )
1552 IS
1553 -- Declare local variables
1554 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NTF_ERROR_ACT';
1555
1556 l_return_status VARCHAR2(1);
1557 l_msg_count NUMBER;
1558 l_msg_data VARCHAR2(2000);
1559
1560 l_hyphen_pos1 NUMBER;
1561 l_item_type VARCHAR2(30);
1562 l_item_key VARCHAR2(30);
1563 l_body VARCHAR2(3500);
1564 l_object_type VARCHAR2(30);
1565 l_object_id NUMBER;
1566 l_error_msg VARCHAR2(2000);
1567
1568 BEGIN
1569
1570 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1571 THEN
1572 fnd_log.string
1573 (
1574 fnd_log.level_procedure,
1575 l_debug_module||'.begin',
1576 'At the start of PLSQL procedure'
1577 );
1578 END IF;
1579
1580 document_type := 'text/plain';
1581
1582 -- parse document_id for the ':' dividing item type name from item key value
1583 l_hyphen_pos1 := INSTR (document_id, ':');
1584 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1585 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1586
1587 l_object_type := wf_engine.getitemattrtext
1588 (
1589 itemtype => l_item_type,
1590 itemkey => l_item_key,
1591 aname => 'OBJECT_TYPE'
1592 );
1593
1594 l_object_id := wf_engine.getitemattrNumber
1595 (
1596 itemtype => l_item_type,
1597 itemkey => l_item_key,
1598 aname => 'OBJECT_ID'
1599 );
1600
1601 l_error_msg := wf_engine.getitemattrText
1602 (
1603 itemtype => l_item_type,
1604 itemkey => l_item_key,
1605 aname => 'ERROR_MSG'
1606 );
1607
1608 OPEN get_mel_cdl_details(l_object_id);
1609 FETCH get_mel_cdl_details into l_mel_cdl_rec;
1610 IF (get_mel_cdl_details%NOTFOUND)
1611 THEN
1612 CLOSE get_mel_cdl_details;
1613
1614 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ID_INVALID');
1615 fnd_message.set_token('HRD_ID', l_object_id, false);
1616 fnd_msg_pub.add;
1617
1618 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1619 THEN
1620 fnd_log.message
1621 (
1622 fnd_log.level_exception,
1623 l_debug_module,
1624 false
1625 );
1626 END IF;
1627
1628 RAISE FND_API.G_EXC_ERROR;
1629
1630 ELSE
1631 CLOSE get_mel_cdl_details;
1632
1633 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ERROR_ACT');
1634 fnd_message.set_token('HRD_ID',l_object_id, false);
1635 fnd_message.set_token('PCN',l_mel_cdl_rec.name, false);
1636 fnd_message.set_token('REV',l_mel_cdl_rec.revision, false);
1637 fnd_message.set_token('TYPE',l_mel_cdl_rec.mel_cdl_type_code, false);
1638 fnd_message.set_token('ERR_MSG',l_error_msg, false);
1639 l_body := fnd_message.get;
1640
1641 END IF;
1642
1643 document := document || l_body;
1644
1645 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1646 THEN
1647 fnd_log.string
1648 (
1652 );
1649 fnd_log.level_statement,
1650 l_debug_module,
1651 'document='||document
1653 END IF;
1654
1655 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1656 THEN
1657 fnd_log.string
1658 (
1659 fnd_log.level_procedure,
1660 l_debug_module||'.end',
1661 'At the end of PLSQL procedure'
1662 );
1663 END IF;
1664
1665 RETURN;
1666
1667 EXCEPTION
1668 WHEN FND_API.G_EXC_ERROR THEN
1669 FND_MSG_PUB.Count_And_Get
1670 (
1671 p_encoded => FND_API.G_FALSE,
1672 p_count => l_msg_count,
1673 p_data => l_msg_data
1674 );
1675
1676 AHL_GENERIC_APRV_PVT.handle_error
1677 (
1678 p_itemtype => l_item_type,
1679 p_itemkey => l_item_key,
1680 p_msg_count => l_msg_count,
1681 p_msg_data => l_msg_data,
1682 p_attr_name => 'ERROR_MSG',
1683 x_error_msg => l_error_msg
1684 );
1685
1686 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1687 THEN
1688 fnd_log.string
1689 (
1690 fnd_log.level_exception,
1691 l_debug_module,
1692 l_error_msg
1693 );
1694 END IF;
1695
1696 wf_core.context
1697 (
1698 'AHL_MEL_CDL_APPROVALS_PVT',
1699 'NTF_ERROR_ACT',
1700 l_item_type,
1701 l_item_key,
1702 l_error_msg
1703 );
1704 RAISE;
1705
1706 WHEN OTHERS THEN
1707 wf_core.context
1708 (
1709 'AHL_MEL_CDL_APPROVALS_PVT',
1710 'NTF_ERROR_ACT',
1711 l_item_type,
1712 l_item_key
1713 );
1714 RAISE;
1715 END NTF_ERROR_ACT;
1716
1717 PROCEDURE UPDATE_STATUS
1718 (
1719 itemtype IN VARCHAR2,
1720 itemkey IN VARCHAR2,
1721 actid IN NUMBER,
1722 funcmode IN VARCHAR2,
1723 resultout OUT NOCOPY VARCHAR2
1724 )
1725 IS
1726 -- Declare local variables
1727 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.UPDATE_STATUS';
1728
1729 l_return_status VARCHAR2(1);
1730 l_msg_count NUMBER;
1731 l_msg_data VARCHAR2(2000);
1732
1733 l_object_id NUMBER;
1734 l_object_ovn NUMBER;
1735 l_next_status VARCHAR2(30);
1736 l_error_msg VARCHAR2(2000);
1737
1738 CURSOR get_prev_rev_details
1739 (
1740 p_pc_node_id number,
1741 p_mel_cdl_type varchar2,
1742 p_version_number number
1743 )
1744 IS
1745 SELECT mel_cdl_header_id,
1746 revision_date
1747 FROM ahl_mel_cdl_headers
1748 WHERE pc_node_id = p_pc_node_id AND
1749 mel_cdl_type_code = p_mel_cdl_type AND
1750 version_number = p_version_number - 1;
1751
1752 l_prev_mel_cdl_header_id NUMBER;
1753 l_prev_revision_date DATE;
1754 l_prev_expired_date DATE;
1755
1756 BEGIN
1757
1758 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1759 THEN
1760 fnd_log.string
1761 (
1762 fnd_log.level_procedure,
1763 l_debug_module||'.begin',
1764 'At the start of PLSQL procedure'
1765 );
1766 END IF;
1767
1768 --
1772 THEN
1769 -- RUN mode
1770 --
1771 IF (funcmode = 'RUN')
1773
1774 l_object_id := wf_engine.getitemattrnumber
1775 (
1776 itemtype => itemtype,
1777 itemkey => itemkey,
1778 aname => 'OBJECT_ID'
1779 );
1780
1781 l_object_ovn := wf_engine.getitemattrnumber
1782 (
1783 itemtype => itemtype,
1784 itemkey => itemkey,
1785 aname => 'OBJECT_VER'
1786 );
1787
1788 OPEN get_mel_cdl_details(l_object_id);
1789 FETCH get_mel_cdl_details into l_mel_cdl_rec;
1790 IF (get_mel_cdl_details%NOTFOUND OR l_mel_cdl_rec.object_version_number <> l_object_ovn)
1791 THEN
1792 CLOSE get_mel_cdl_details;
1793
1794 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ID_INVALID');
1795 fnd_message.set_token('HRD_ID', l_object_id, false);
1796 fnd_msg_pub.add;
1797
1798 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1799 THEN
1800 fnd_log.message
1801 (
1802 fnd_log.level_exception,
1803 l_debug_module,
1804 false
1805 );
1806 END IF;
1807
1808 resultout := 'COMPLETE:ERROR';
1809 RAISE FND_API.G_EXC_ERROR;
1810
1811 END IF;
1812 CLOSE get_mel_cdl_details;
1813
1814 -- Priyan :
1815 -- Fix for Bug #5484980
1816 -- Changed wf_engine.GetItemAttrNumber to wf_engine.GetItemAttrText
1817
1818 l_next_status := wf_engine.GetItemAttrText
1819 (
1820 itemtype => itemtype,
1821 itemkey => itemkey,
1822 aname => 'UPDATE_GEN_STATUS'
1823 );
1824
1825 -- Complete the current revision and expire the old one...
1826 UPDATE ahl_mel_cdl_headers
1827 SET status_code = l_next_status,
1828 object_version_number = l_object_ovn + 1,
1829 last_update_date = sysdate,
1830 last_updated_by = fnd_global.user_id,
1831 last_update_login = fnd_global.login_id
1832 WHERE mel_cdl_header_id = l_object_id;
1833
1834 IF (l_mel_cdl_rec.version_number > 1)
1835 THEN
1836 -- Retrieve previous revision details
1837 OPEN get_prev_rev_details(l_mel_cdl_rec.pc_node_id, l_mel_cdl_rec.mel_cdl_type_code, l_mel_cdl_rec.version_number);
1838 FETCH get_prev_rev_details INTO l_prev_mel_cdl_header_id, l_prev_revision_date;
1839 CLOSE get_prev_rev_details;
1840
1841 -- Calculate previous revision's expired_date
1842 l_prev_expired_date := l_mel_cdl_rec.revision_date - 1;
1843 IF (trunc(l_prev_expired_date) < trunc(l_prev_revision_date))
1844 THEN
1845 l_prev_expired_date := l_prev_revision_date;
1846 END IF;
1847
1848 -- Once the current revision of the MEL/CDL is complete, need to expire the earlier revision
1849 UPDATE ahl_mel_cdl_headers
1850 SET expired_date = l_prev_expired_date,
1851 object_version_number = object_version_number + 1,
1852 last_update_date = sysdate,
1853 last_updated_by = fnd_global.user_id,
1854 last_update_login = fnd_global.login_id
1855 WHERE mel_cdl_header_id = l_prev_mel_cdl_header_id;
1856 END IF;
1857
1858 resultout := 'COMPLETE:SUCCESS';
1859 RETURN;
1860 END IF;
1861
1862 --
1863 -- CANCEL mode
1864 --
1865 IF (funcmode = 'CANCEL')
1866 THEN
1867 resultout := 'COMPLETE:';
1868 RETURN;
1869 END IF;
1870
1871 --
1872 -- TIMEOUT mode
1873 --
1874 IF (funcmode = 'TIMEOUT')
1875 THEN
1876 resultout := 'COMPLETE:';
1877 RETURN;
1878 END IF;
1879
1880 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1881 THEN
1882 fnd_log.string
1883 (
1884 fnd_log.level_procedure,
1885 l_debug_module||'.end',
1886 'At the end of PLSQL procedure'
1887 );
1888 END IF;
1889
1890 EXCEPTION
1891 WHEN FND_API.G_EXC_ERROR THEN
1892 FND_MSG_PUB.Count_And_Get
1893 (
1894 p_encoded => FND_API.G_FALSE,
1898
1895 p_count => l_msg_count,
1896 p_data => l_msg_data
1897 );
1899 AHL_GENERIC_APRV_PVT.handle_error
1900 (
1901 p_itemtype => itemtype,
1902 p_itemkey => itemkey,
1903 p_msg_count => l_msg_count,
1904 p_msg_data => l_msg_data,
1905 p_attr_name => 'ERROR_MSG',
1906 x_error_msg => l_error_msg
1907 );
1908
1909 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1910 THEN
1911 fnd_log.string
1912 (
1913 fnd_log.level_exception,
1914 l_debug_module,
1915 l_error_msg
1916 );
1917 END IF;
1918
1919 wf_core.context
1920 (
1921 'AHL_MEL_CDL_APPROVALS_PVT',
1922 'UPDATE_STATUS',
1923 itemtype,
1924 itemkey,
1925 actid,
1926 funcmode,
1927 l_error_msg
1928 );
1929 resultout := 'COMPLETE:ERROR';
1930 RAISE;
1931
1932 WHEN OTHERS THEN
1933 wf_core.context
1934 (
1935 'AHL_MEL_CDL_APPROVALS_PVT',
1936 'UPDATE_STATUS',
1937 itemtype,
1938 itemkey,
1939 actid,
1940 'Unexpected Error! '||SUBSTR(SQLERRM,1,240)
1941 );
1942 RAISE;
1943 END UPDATE_STATUS;
1944
1945 PROCEDURE REVERT_STATUS
1946 (
1947 itemtype IN VARCHAR2,
1948 itemkey IN VARCHAR2,
1949 actid IN NUMBER,
1950 funcmode IN VARCHAR2,
1951 resultout OUT NOCOPY VARCHAR2
1952 )
1953 IS
1954 -- Declare local variables
1955 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.REVERT_STATUS';
1956
1957 l_return_status VARCHAR2(1);
1958 l_msg_count NUMBER;
1959 l_msg_data VARCHAR2(2000);
1960
1961 l_object_id NUMBER;
1962 l_object_ovn NUMBER;
1963 l_next_status VARCHAR2(30);
1964 l_error_msg VARCHAR2(2000);
1965
1966 BEGIN
1967
1968 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1969 THEN
1970 fnd_log.string
1971 (
1972 fnd_log.level_procedure,
1973 l_debug_module||'.begin',
1974 'At the start of PLSQL procedure'
1975 );
1976 END IF;
1977
1978 --
1979 -- RUN mode
1980 --
1981 IF (funcmode = 'RUN')
1982 THEN
1983
1984 l_object_id := wf_engine.getitemattrnumber
1985 (
1986 itemtype => itemtype,
1987 itemkey => itemkey,
1988 aname => 'OBJECT_ID'
1989 );
1990
1991 l_object_ovn := wf_engine.getitemattrnumber
1992 (
1993 itemtype => itemtype,
1994 itemkey => itemkey,
1995 aname => 'OBJECT_VER'
1996 );
1997
1998 OPEN get_mel_cdl_details(l_object_id);
1999 FETCH get_mel_cdl_details into l_mel_cdl_rec;
2000 IF (get_mel_cdl_details%NOTFOUND OR l_mel_cdl_rec.object_version_number <> l_object_ovn)
2001 THEN
2002 CLOSE get_mel_cdl_details;
2003
2004 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ID_INVALID');
2005 fnd_message.set_token('HRD_ID', l_object_id, false);
2006 fnd_msg_pub.add;
2007
2008 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2009 THEN
2010 fnd_log.message
2011 (
2012 fnd_log.level_exception,
2013 l_debug_module,
2014 false
2015 );
2016 END IF;
2017
2018 resultout := 'COMPLETE:ERROR';
2019 RAISE FND_API.G_EXC_ERROR;
2020
2021 END IF;
2022 CLOSE get_mel_cdl_details;
2023
2024 l_next_status := wf_engine.getitemattrnumber
2025 (
2026 itemtype => itemtype,
2027 itemkey => itemkey,
2028 aname => 'ORG_STATUS_ID'
2029 );
2030
2031 UPDATE ahl_mel_cdl_headers
2032 SET status_code = l_next_status,
2033 object_version_number = l_object_ovn + 1,
2034 last_update_date = sysdate,
2035 last_updated_by = fnd_global.user_id,
2036 last_update_login = fnd_global.login_id
2037 WHERE mel_cdl_header_id = l_object_id;
2038
2039 resultout := 'COMPLETE:SUCCESS';
2040 RETURN;
2041 END IF;
2042
2043 --
2044 -- CANCEL mode
2045 --
2046 IF (funcmode = 'CANCEL')
2047 THEN
2048 resultout := 'COMPLETE:';
2049 RETURN;
2050 END IF;
2051
2052 --
2053 -- TIMEOUT mode
2054 --
2055 IF (funcmode = 'TIMEOUT')
2056 THEN
2057 resultout := 'COMPLETE:';
2058 RETURN;
2059 END IF;
2060
2061 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2062 THEN
2063 fnd_log.string
2064 (
2065 fnd_log.level_procedure,
2066 l_debug_module||'.end',
2067 'At the end of PLSQL procedure'
2068 );
2069 END IF;
2070
2071 EXCEPTION
2075 p_encoded => FND_API.G_FALSE,
2072 WHEN FND_API.G_EXC_ERROR THEN
2073 FND_MSG_PUB.Count_And_Get
2074 (
2076 p_count => l_msg_count,
2077 p_data => l_msg_data
2078 );
2079
2080 AHL_GENERIC_APRV_PVT.handle_error
2081 (
2082 p_itemtype => itemtype,
2083 p_itemkey => itemkey,
2084 p_msg_count => l_msg_count,
2085 p_msg_data => l_msg_data,
2086 p_attr_name => 'ERROR_MSG',
2087 x_error_msg => l_error_msg
2088 );
2089
2090 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2091 THEN
2092 fnd_log.string
2093 (
2094 fnd_log.level_exception,
2095 l_debug_module,
2096 l_error_msg
2097 );
2098 END IF;
2099
2100 wf_core.context
2101 (
2102 'AHL_MEL_CDL_APPROVALS_PVT',
2103 'REVERT_STATUS',
2104 itemtype,
2105 itemkey,
2106 actid,
2107 funcmode,
2108 l_error_msg
2109 );
2110 resultout := 'COMPLETE:ERROR';
2111 RAISE;
2112
2113 WHEN OTHERS THEN
2114 wf_core.context
2115 (
2116 'AHL_MEL_CDL_APPROVALS_PVT',
2117 'REVERT_STATUS',
2118 itemtype,
2119 itemkey,
2120 actid,
2121 'Unexpected Error! '||SUBSTR(SQLERRM,1,240)
2122 );
2123 RAISE;
2124 END REVERT_STATUS;
2125
2126
2127 -- Procedures used by Non-Routine MEL/CDl approval --
2128 -----------------------------------------------------
2129
2130
2131 -- Start of Comments --
2132 --
2133 -- Procedure name : NR_SET_ACTIVITY_DETAILS
2134 -- Type : Private
2135 -- Description : This procedure sets all item attribute details for the NR approval rule
2136 --
2137 -- Version :
2138 -- Initial Version 1.0
2139 --
2140 -- End of Comments --
2141 PROCEDURE NR_SET_ACTIVITY_DETAILS
2142 (
2143 itemtype IN VARCHAR2,
2144 itemkey IN VARCHAR2,
2145 actid IN NUMBER,
2146 funcmode IN VARCHAR2,
2147 resultout OUT NOCOPY VARCHAR2
2148 )
2149
2150 IS
2151
2152 -- Declare cursors
2153 CURSOR get_nr_details_csr(p_deferral_id IN NUMBER) IS
2154 select cs.incident_number incident_number, cit.name name, udf.object_version_number,
2155 mtl.concatenated_segments item, csi.serial_number, ue.mel_cdl_type_code,
2156 cs.expected_resolution_date, seq.ata_code, arc.repair_time,
2157 apn.name node_name, aph.name class_name, cs.summary, ciu.name repair_category,
2158 (select visit_number from ahl_visit_tasks_b tsk, ahl_visits_b vst where
2159 vst.visit_id = tsk.visit_id and tsk.unit_effectivity_id =
2160 ue.unit_effectivity_id and rownum < 2) visit_number
2161 from cs_incidents_all_vl cs, cs_incident_types_vl cit,
2162 ahl_unit_effectivities_b ue, ahl_unit_deferrals_b udf,
2163 csi_item_instances csi, mtl_system_items_kfv mtl,
2164 ahl_mel_cdl_ata_sequences seq, ahl_repair_categories arc,
2165 ahl_mel_cdl_headers mch, ahl_pc_headers_b aph, ahl_pc_nodes_b apn,
2166 cs_incident_urgencies_vl ciu
2167 where udf.unit_effectivity_id = ue.unit_effectivity_id
2168 and ue.cs_incident_id = cs.incident_id
2169 and cs.incident_type_id = cit.incident_type_id
2170 and ue.csi_item_instance_id = csi.instance_id
2171 and mtl.inventory_item_id = csi.inventory_item_id
2172 and mtl.organization_id = csi.inv_master_organization_id
2173 and udf.ata_sequence_id = seq.MEL_CDL_ATA_SEQUENCE_ID
2174 and seq.repair_category_id = arc.repair_category_id
2175 and mch.mel_cdl_header_id = seq.mel_cdl_header_id
2176 and mch.pc_node_id = apn.pc_node_id
2177 and apn.pc_header_id = aph.pc_header_id
2178 and arc.sr_urgency_id = ciu.INCIDENT_URGENCY_ID
2179 and udf.unit_deferral_id = p_deferral_id;
2180
2181 -- Declare local variables
2182 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NR_SET_ACTIVITY_DETAILS';
2183 l_debug_stmt CONSTANT NUMBER := fnd_log.level_statement;
2184 l_debug_proc CONSTANT NUMBER := fnd_log.level_procedure;
2185 l_debug_runtime CONSTANT NUMBER := fnd_log.g_current_runtime_level;
2186 l_debug_exception CONSTANT NUMBER := fnd_log.level_exception;
2187
2188 l_return_status VARCHAR2(1);
2189 l_msg_count NUMBER;
2190 l_msg_data VARCHAR2(2000);
2191
2192 l_object_id NUMBER;
2193 l_object_ovn NUMBER;
2194 l_object_details AHL_GENERIC_APRV_PVT.OBJRECTYP;
2195 l_approval_rule_id NUMBER;
2196 l_approver_seq NUMBER;
2197 l_subject VARCHAR2(500);
2198 l_error_msg VARCHAR2(2000);
2199
2200 l_nr_rec get_nr_details_csr%ROWTYPE;
2201 l_requester_note VARCHAR2(4000);
2202 l_temp_subject VARCHAR2(4000);
2203
2204 BEGIN
2205
2206 FND_MSG_PUB.INITIALIZE;
2207
2208 l_return_status := FND_API.G_RET_STS_SUCCESS;
2209
2210 IF (l_debug_proc >= l_debug_runtime)
2211 THEN
2212 fnd_log.string
2213 (
2214 l_debug_proc,
2215 l_debug_module||'.begin',
2216 'At the start of PLSQL procedure'
2220 l_object_id := wf_engine.getitemattrnumber
2217 );
2218 END IF;
2219
2221 (
2222 itemtype => itemtype,
2223 itemkey => itemkey,
2224 aname => 'OBJECT_ID'
2225 );
2226
2227 l_object_ovn := wf_engine.getitemattrnumber
2228 (
2229 itemtype => itemtype,
2230 itemkey => itemkey,
2231 aname => 'OBJECT_VER'
2232 );
2233
2234 IF (l_debug_stmt >= l_debug_runtime)
2235 THEN
2236 fnd_log.string
2237 (
2238 l_debug_stmt,
2239 l_debug_module,
2240 'OBJECT_ID='||l_object_id||' OBJECT_VER='||l_object_ovn
2241 );
2242 END IF;
2243
2244 l_object_details.operating_unit_id := NULL;
2245 l_object_details.priority := NULL;
2246
2247 --
2248 -- RUN mode
2249 --
2250 IF (funcmode = 'RUN')
2251 THEN
2252
2253 OPEN get_nr_details_csr(l_object_id);
2254 FETCH get_nr_details_csr into l_nr_rec;
2255 IF (get_nr_details_csr%NOTFOUND OR l_nr_rec.object_version_number <> l_object_ovn)
2256 THEN
2257 CLOSE get_nr_details_csr;
2258
2259 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_ID_INVALID');
2260 fnd_message.set_token('HDR_ID', l_object_id, false);
2261 fnd_msg_pub.add;
2262
2263 IF (l_debug_exception >= l_debug_runtime)
2264 THEN
2265 fnd_log.message
2266 (
2267 l_debug_exception,
2268 l_debug_module,
2269 false
2270 );
2271 END IF;
2272
2273 resultout := 'COMPLETE:ERROR';
2274 RAISE FND_API.G_EXC_ERROR;
2275
2276 END IF;
2277 CLOSE get_nr_details_csr;
2278
2279 fnd_message.set_name('AHL','AHL_UMP_NR_APPR_SUBJECT');
2280 fnd_message.set_token('MEL_CDL', l_nr_rec.mel_cdl_type_code);
2281 fnd_message.set_token('NR_NUM', l_nr_rec.incident_number);
2282 fnd_message.set_token('VISIT_NUM', l_nr_rec.visit_number);
2283 l_temp_subject := fnd_message.get;
2284
2285 -- form requester note.
2286 fnd_message.set_name('AHL','AHL_UMP_NR_REQ_NOTE');
2287 fnd_message.set_token('INCIDENT_NUMBER', l_nr_rec.incident_number);
2288 fnd_message.set_token('VISIT_NUMBER',l_nr_rec.visit_number);
2289 fnd_message.set_token('SUMMARY', l_nr_rec.summary);
2290 fnd_message.set_token('TYPE_NAME',l_nr_rec.name);
2291 fnd_message.set_token('ITEM',l_nr_rec.Item);
2292 fnd_message.set_token('SERIAL',l_nr_rec.serial_number);
2293 l_requester_note := fnd_message.get;
2294
2295 fnd_message.set_name('AHL','AHL_UMP_MEL_CDL_REQ_NOTE');
2296 fnd_message.set_token('MEL_CDL', l_nr_rec.mel_cdl_type_code);
2297 fnd_message.set_token('ATA_CODE', l_nr_rec.ata_code);
2298 fnd_message.set_token('PROD_CLASS', l_nr_rec.class_name);
2299 fnd_message.set_token('PROD_CLASS_NODE', l_nr_rec.node_name);
2300 fnd_message.set_token('REP_CAT', l_nr_rec.repair_category);
2301 fnd_message.set_token('REP_TIME', l_nr_rec.repair_time);
2302 fnd_message.set_token('EXP_DATE', to_char(l_nr_rec.expected_resolution_date,
2303 fnd_date.outputDT_mask));
2304
2305 l_requester_note := l_requester_note || fnd_message.get;
2306
2310 l_debug_stmt,
2307 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2308 fnd_log.string
2309 (
2311 l_debug_module,
2312 'SUBJECT : ' || l_temp_subject
2313 );
2314 fnd_log.string
2315 (
2316 l_debug_stmt,
2317 l_debug_module,
2318 'REQUESTER NOTE : ' || l_requester_note
2319 );
2320 END IF;
2321
2322 /* REQUESTER_NOTE */
2326 ,aname => 'REQUESTER_NOTE'
2323 wf_engine.setitemattrtext(
2324 itemtype => itemtype
2325 ,itemkey => itemkey
2327 ,avalue => l_requester_note
2328 );
2329
2330 /* FORWARD_SUBJECT */
2331 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_FORWARD_SUBJ');
2332 l_subject := fnd_message.get || l_temp_subject;
2333
2334 wf_engine.setitemattrtext
2335 (
2336 itemtype => itemtype,
2337 itemkey => itemkey,
2338 aname => 'FORWARD_SUBJECT',
2339 avalue => l_subject
2340 );
2341
2342 IF (l_debug_stmt >= l_debug_runtime)
2343 THEN
2344 fnd_log.string
2345 (
2346 l_debug_stmt,
2347 l_debug_module,
2348 'FORWARD_SUBJECT='||l_subject
2349 );
2350 END IF;
2351
2352 /* APPROVAL_SUBJECT */
2353 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_APPROVAL_SUBJ');
2354 l_subject := fnd_message.get || l_temp_subject;
2355
2356 wf_engine.setitemattrtext
2357 (
2358 itemtype => itemtype,
2359 itemkey => itemkey,
2360 aname => 'APPROVAL_SUBJECT',
2361 avalue => l_subject
2362 );
2363
2364 IF (l_debug_stmt >= l_debug_runtime)
2365 THEN
2366 fnd_log.string
2367 (
2368 l_debug_stmt,
2369 l_debug_module,
2370 'APPROVAL_SUBJECT='||l_subject
2371 );
2372 END IF;
2373
2374 /* REJECT_SUBJECT */
2375 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_REJECT_SUBJ');
2376 l_subject := fnd_message.get || l_temp_subject;
2377
2378 wf_engine.setitemattrtext
2379 (
2380 itemtype => itemtype,
2381 itemkey => itemkey,
2382 aname => 'REJECT_SUBJECT',
2383 avalue => l_subject
2384 );
2385
2386 IF (l_debug_stmt >= l_debug_runtime)
2387 THEN
2388 fnd_log.string
2389 (
2390 l_debug_stmt,
2391 l_debug_module,
2392 'REJECT_SUBJECT='||l_subject
2393 );
2394 END IF;
2395
2396 /* APPROVED_SUBJECT */
2397 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_APPROVED_SUBJ');
2398 l_subject := fnd_message.get || l_temp_subject;
2399
2400 wf_engine.setitemattrtext
2401 (
2402 itemtype => itemtype,
2403 itemkey => itemkey,
2404 aname => 'APPROVED_SUBJECT',
2405 avalue => l_subject
2406 );
2407
2408 IF (l_debug_stmt >= l_debug_runtime)
2409 THEN
2410 fnd_log.string
2411 (
2412 l_debug_stmt,
2413 l_debug_module,
2414 'APPROVED_SUBJECT='||l_subject
2415 );
2416 END IF;
2417
2418 /* FINAL_SUBJECT */
2419 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_FINAL_SUBJ');
2420 l_subject := fnd_message.get || l_temp_subject;
2421
2422 wf_engine.setitemattrtext
2423 (
2424 itemtype => itemtype,
2425 itemkey => itemkey,
2426 aname => 'FINAL_SUBJECT',
2427 avalue => l_subject
2428 );
2429
2430 IF (l_debug_stmt >= l_debug_runtime)
2431 THEN
2432 fnd_log.string
2433 (
2434 l_debug_stmt,
2435 l_debug_module,
2436 'FINAL_SUBJECT='||l_subject
2437 );
2438 END IF;
2439
2440 /* REMIND_SUBJECT */
2441 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_REMIND_SUBJ');
2442 l_subject := fnd_message.get || l_temp_subject;
2443
2444 wf_engine.setitemattrtext
2445 (
2446 itemtype => itemtype,
2447 itemkey => itemkey,
2448 aname => 'REMIND_SUBJECT',
2449 avalue => l_subject
2450 );
2451
2452 IF (l_debug_stmt >= l_debug_runtime)
2453 THEN
2454 fnd_log.string
2455 (
2456 l_debug_stmt,
2457 l_debug_module,
2458 'REMIND_SUBJECT='||l_subject
2459 );
2460 END IF;
2461
2462 /* ERROR_SUBJECT */
2463 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_ERROR_SUBJ');
2464 l_subject := fnd_message.get || l_temp_subject;
2465
2466 wf_engine.setitemattrtext
2467 (
2468 itemtype => itemtype,
2469 itemkey => itemkey,
2470 aname => 'ERROR_SUBJECT',
2471 avalue => l_subject
2472 );
2473
2474 IF (l_debug_stmt >= l_debug_runtime)
2475 THEN
2476 fnd_log.string
2480 'ERROR_SUBJECT='||l_subject
2477 (
2478 l_debug_stmt,
2479 l_debug_module,
2481 );
2482 END IF;
2483
2484 /* Getting approver details */
2485 AHL_GENERIC_APRV_PVT.GET_APPROVAL_DETAILS
2486 (
2487 p_object => G_NR_APPR_OBJ,
2488 p_approval_type => G_APPR_TYPE,
2489 p_object_details => l_object_details,
2490 x_approval_rule_id => l_approval_rule_id,
2491 x_approver_seq => l_approver_seq,
2492 x_return_status => l_return_status
2493 );
2494
2495 IF l_return_status = FND_API.G_RET_STS_SUCCESS
2496 THEN
2497 wf_engine.setitemattrnumber
2498 (
2499 itemtype => itemtype,
2500 itemkey => itemkey,
2501 aname => 'RULE_ID',
2502 avalue => l_approval_rule_id
2503 );
2504
2505 IF (l_debug_stmt >= l_debug_runtime)
2506 THEN
2507 fnd_log.string
2508 (
2509 fnd_log.level_statement,
2510 l_debug_module,
2511 'RULE_ID='||l_approval_rule_id
2512 );
2513 END IF;
2514
2515 wf_engine.setitemattrnumber
2516 (
2517 itemtype => itemtype,
2518 itemkey => itemkey,
2519 aname => 'APPROVER_SEQ',
2523 IF (l_debug_stmt >= l_debug_runtime)
2520 avalue => l_approver_seq
2521 );
2522
2524 THEN
2525 fnd_log.string
2526 (
2527 fnd_log.level_statement,
2528 l_debug_module,
2529 'APPROVER_SEQ='||l_approver_seq
2530 );
2531 END IF;
2532
2533 resultout := 'COMPLETE:SUCCESS';
2534 ELSE
2535 resultout := 'COMPLETE:ERROR';
2536 RAISE FND_API.G_EXC_ERROR;
2537 END IF;
2538 END IF;
2539
2540 --
2541 -- CANCEL mode
2542 --
2543 IF (funcmode = 'CANCEL')
2544 THEN
2545 resultout := 'COMPLETE:';
2546 RETURN;
2547 END IF;
2548
2549 --
2550 -- TIMEOUT mode
2551 --
2552 IF (funcmode = 'TIMEOUT')
2553 THEN
2554 resultout := 'COMPLETE:';
2555 RETURN;
2556 END IF;
2557
2558 IF (l_debug_proc >= l_debug_runtime)
2559 THEN
2560 fnd_log.string
2561 (
2562 l_debug_proc,
2563 l_debug_module||'.end',
2564 'At the end of PLSQL procedure'
2565 );
2566 END IF;
2567
2568 EXCEPTION
2569 WHEN FND_API.G_EXC_ERROR THEN
2570 FND_MSG_PUB.Count_And_Get
2571 (
2572 p_encoded => FND_API.G_FALSE,
2573 p_count => l_msg_count,
2574 p_data => l_msg_data
2575 );
2576
2577 AHL_GENERIC_APRV_PVT.handle_error
2578 (
2579 p_itemtype => itemtype,
2580 p_itemkey => itemkey,
2581 p_msg_count => l_msg_count,
2582 p_msg_data => l_msg_data,
2583 p_attr_name => 'ERROR_MSG',
2584 x_error_msg => l_error_msg
2585 );
2586
2587 IF (l_debug_exception >= l_debug_runtime)
2588 THEN
2589 fnd_log.string
2590 (
2591 l_debug_exception,
2592 l_debug_module,
2593 l_error_msg
2594 );
2595 END IF;
2596
2597 wf_core.context
2598 (
2599 'AHL_MEL_CDL_APPROVALS_PVT',
2600 'NR_SET_ACTIVITY_DETAILS',
2601 itemtype,
2602 itemkey,
2603 actid,
2604 funcmode,
2605 l_error_msg
2606 );
2607 resultout := 'COMPLETE:ERROR';
2608 RAISE;
2609
2613 'AHL_MEL_CDL_APPROVALS_PVT',
2610 WHEN OTHERS THEN
2611 wf_core.context
2612 (
2614 'NR_SET_ACTIVITY_DETAILS',
2615 itemtype,
2616 itemkey,
2617 actid,
2618 'Unexpected Error! '||SUBSTR(SQLERRM,1,240)
2619 );
2620 RAISE;
2621
2622 END NR_SET_ACTIVITY_DETAILS;
2623
2624 -- Start of Comments --
2625 --
2626 -- Procedure name : NR_NTF_FORWARD_FYI
2627 -- Type : Private
2628 -- Description : This procedure generates the FYI document for forwarded workflow notifications
2629 --
2630 -- Version :
2631 -- Initial Version 1.0
2632 --
2633 -- End of Comments --
2634 PROCEDURE NR_NTF_FORWARD_FYI
2635 (
2636 document_id IN VARCHAR2,
2637 display_type IN VARCHAR2,
2638 document IN OUT NOCOPY VARCHAR2,
2639 document_type IN OUT NOCOPY VARCHAR2
2640 ) IS
2641
2642 -- Declare local variables
2643 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NR_NTF_FORWARD_FYI';
2644
2645 l_debug_stmt CONSTANT NUMBER := fnd_log.level_statement;
2646 l_debug_proc CONSTANT NUMBER := fnd_log.level_procedure;
2647 l_debug_runtime CONSTANT NUMBER := fnd_log.g_current_runtime_level;
2648 l_debug_exception CONSTANT NUMBER := fnd_log.level_exception;
2649
2650
2651 l_return_status VARCHAR2(1);
2652 l_msg_count NUMBER;
2653 l_msg_data VARCHAR2(2000);
2654
2655 l_hyphen_pos1 NUMBER;
2656 l_item_type VARCHAR2(30);
2657 l_item_key VARCHAR2(30);
2658 l_approver VARCHAR2(30);
2659 l_body VARCHAR2(3500);
2660 l_object_type VARCHAR2(30);
2661 l_object_id NUMBER;
2662 l_error_msg VARCHAR2(2000);
2663 l_requester_note VARCHAR2(4000);
2664
2665 BEGIN
2666
2667 IF (l_debug_proc >= l_debug_runtime)
2668 THEN
2669 fnd_log.string
2670 (
2671 l_debug_proc,
2672 l_debug_module||'.begin',
2673 'At the start of PLSQL procedure'
2674 );
2675 END IF;
2676
2677 document_type := 'text/plain';
2678
2679 -- parse document_id for the ':' dividing item type name from item key value
2680 l_hyphen_pos1 := INSTR (document_id, ':');
2681 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
2682 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
2683
2684 l_object_type := wf_engine.getitemattrtext
2685 (
2686 itemtype => l_item_type,
2687 itemkey => l_item_key,
2688 aname => 'OBJECT_TYPE'
2689 );
2690
2691 l_object_id := wf_engine.getitemattrNumber
2692 (
2693 itemtype => l_item_type,
2694 itemkey => l_item_key,
2695 aname => 'OBJECT_ID'
2696 );
2697
2698 l_approver := wf_engine.getitemattrtext
2699 (
2700 itemtype => l_item_type,
2701 itemkey => l_item_key,
2702 aname => 'APPROVER'
2703 );
2704
2705 l_requester_note := wf_engine.getitemattrtext
2706 (
2707 itemtype => l_item_type
2708 ,itemkey => l_item_key
2709 ,aname => 'REQUESTER_NOTE'
2710 );
2711
2712 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_FYI_FWD');
2713 fnd_message.set_token('APPROVER',l_approver, false);
2714 l_body := fnd_message.get;
2715
2716 document := l_body || l_requester_note;
2717
2718 IF (l_debug_stmt >= l_debug_runtime)
2719 THEN
2720 fnd_log.string
2721 (
2722 l_debug_stmt,
2723 l_debug_module,
2724 'document='||document
2725 );
2726 END IF;
2727
2728 IF (l_debug_proc >= l_debug_runtime)
2729 THEN
2730 fnd_log.string
2731 (
2732 l_debug_proc,
2733 l_debug_module||'.end',
2734 'At the end of PLSQL procedure'
2735 );
2736 END IF;
2737
2738 RETURN;
2739
2740 EXCEPTION
2741 WHEN FND_API.G_EXC_ERROR THEN
2742 FND_MSG_PUB.Count_And_Get
2743 (
2744 p_encoded => FND_API.G_FALSE,
2745 p_count => l_msg_count,
2746 p_data => l_msg_data
2747 );
2748
2749 AHL_GENERIC_APRV_PVT.handle_error
2750 (
2751 p_itemtype => l_item_type,
2752 p_itemkey => l_item_key,
2753 p_msg_count => l_msg_count,
2754 p_msg_data => l_msg_data,
2755 p_attr_name => 'ERROR_MSG',
2756 x_error_msg => l_error_msg
2757 );
2758
2759 IF (l_debug_exception >= l_debug_runtime)
2760 THEN
2761 fnd_log.string
2762 (
2763 l_debug_exception,
2764 l_debug_module,
2765 l_error_msg
2766 );
2767 END IF;
2768
2769 wf_core.context
2770 (
2771 'AHL_MEL_CDL_APPROVALS_PVT',
2772 'NR_NTF_FORWARD_FYI',
2773 l_item_type,
2774 l_item_key,
2775 l_error_msg
2776 );
2777 RAISE;
2778
2779 WHEN OTHERS THEN
2780 wf_core.context
2781 (
2782 'AHL_MEL_CDL_APPROVALS_PVT',
2783 'NR_NTF_FORWARD_FYI',
2784 l_item_type,
2785 l_item_key
2786 );
2787 RAISE;
2788 END NR_NTF_FORWARD_FYI;
2789
2790
2791 -- Start of Comments --
2792 --
2793 -- Procedure name : NR_NTF_APPROVED_FYI
2794 -- Type : Private
2795 -- Description : This procedure generates the FYI document for approved workflow notifications
2796 --
2797 -- Version :
2798 -- Initial Version 1.0
2799 --
2800 -- End of Comments --
2801 PROCEDURE NR_NTF_APPROVED_FYI
2802 (
2803 document_id IN VARCHAR2,
2804 display_type IN VARCHAR2,
2805 document IN OUT NOCOPY VARCHAR2,
2806 document_type IN OUT NOCOPY VARCHAR2
2807 ) IS
2808
2809 -- Declare local variables
2810 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NR_NTF_APPROVED_FYI';
2811
2812 l_debug_stmt CONSTANT NUMBER := fnd_log.level_statement;
2813 l_debug_proc CONSTANT NUMBER := fnd_log.level_procedure;
2814 l_debug_runtime CONSTANT NUMBER := fnd_log.g_current_runtime_level;
2815 l_debug_exception CONSTANT NUMBER := fnd_log.level_exception;
2816
2817
2818 l_return_status VARCHAR2(1);
2819 l_msg_count NUMBER;
2820 l_msg_data VARCHAR2(2000);
2821
2822 l_hyphen_pos1 NUMBER;
2823 l_item_type VARCHAR2(30);
2824 l_item_key VARCHAR2(30);
2825 l_approver VARCHAR2(30);
2826 l_body VARCHAR2(3500);
2827 l_object_type VARCHAR2(30);
2828 l_object_id NUMBER;
2829 l_error_msg VARCHAR2(2000);
2830 l_requester_note VARCHAR2(4000);
2831
2832 BEGIN
2833
2834 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2835 THEN
2836 fnd_log.string
2837 (
2838 fnd_log.level_procedure,
2839 l_debug_module||'.begin',
2840 'At the start of PLSQL procedure'
2841 );
2842 END IF;
2843
2844 document_type := 'text/plain';
2845
2846 -- parse document_id for the ':' dividing item type name from item key value
2847 l_hyphen_pos1 := INSTR (document_id, ':');
2848 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
2849 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
2850
2851 l_object_type := wf_engine.getitemattrtext
2852 (
2853 itemtype => l_item_type,
2854 itemkey => l_item_key,
2855 aname => 'OBJECT_TYPE'
2856 );
2857
2858 l_object_id := wf_engine.getitemattrNumber
2859 (
2860 itemtype => l_item_type,
2861 itemkey => l_item_key,
2862 aname => 'OBJECT_ID'
2863 );
2864
2865 l_approver := wf_engine.getitemattrtext
2866 (
2867 itemtype => l_item_type,
2868 itemkey => l_item_key,
2869 aname => 'APPROVER'
2870 );
2871
2872 l_requester_note := wf_engine.getitemattrtext
2873 (
2874 itemtype => l_item_type
2875 ,itemkey => l_item_key
2876 ,aname => 'REQUESTER_NOTE'
2877 );
2878
2879 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_FYI_APPRVD');
2880 fnd_message.set_token('APPROVER',l_approver, false);
2881 l_body := fnd_message.get;
2882
2883 document := document || l_body || l_requester_note;
2884
2885 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2886 THEN
2887 fnd_log.string
2888 (
2889 fnd_log.level_statement,
2890 l_debug_module,
2891 'document='||document
2892 );
2893 END IF;
2894
2895 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2896 THEN
2897 fnd_log.string
2898 (
2899 fnd_log.level_procedure,
2900 l_debug_module||'.end',
2901 'At the end of PLSQL procedure'
2902 );
2903 END IF;
2904
2905
2906 EXCEPTION
2907 WHEN FND_API.G_EXC_ERROR THEN
2908 FND_MSG_PUB.Count_And_Get
2909 (
2910 p_encoded => FND_API.G_FALSE,
2911 p_count => l_msg_count,
2912 p_data => l_msg_data
2913 );
2914
2915 AHL_GENERIC_APRV_PVT.handle_error
2916 (
2917 p_itemtype => l_item_type,
2918 p_itemkey => l_item_key,
2919 p_msg_count => l_msg_count,
2920 p_msg_data => l_msg_data,
2921 p_attr_name => 'ERROR_MSG',
2922 x_error_msg => l_error_msg
2923 );
2924
2925 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2926 THEN
2927 fnd_log.string
2928 (
2929 fnd_log.level_exception,
2930 l_debug_module,
2931 l_error_msg
2932 );
2933 END IF;
2934
2935 wf_core.context
2936 (
2937 'AHL_MEL_CDL_APPROVALS_PVT',
2938 'NR_NTF_APPROVED_FYI',
2939 l_item_type,
2940 l_item_key,
2941 l_error_msg
2942 );
2943 RAISE;
2944
2945 WHEN OTHERS THEN
2946 wf_core.context
2947 (
2948 'AHL_MEL_CDL_APPROVALS_PVT',
2949 'NR_NTF_APPROVED_FYI',
2950 l_item_type,
2951 l_item_key
2952 );
2953 RAISE;
2954 END NR_NTF_APPROVED_FYI;
2955
2956
2957
2958 -- Start of Comments --
2959 --
2960 -- Procedure name : NR_NTF_FINAL_APPROVAL_FYI
2961 -- Type : Private
2962 -- Description : This procedure generates the FYI document for final approval workflow notifications
2963 --
2964 -- Version :
2965 -- Initial Version 1.0
2966 --
2967 -- End of Comments --
2968 PROCEDURE NR_NTF_FINAL_APPROVAL_FYI
2969 (
2970 document_id IN VARCHAR2,
2971 display_type IN VARCHAR2,
2972 document IN OUT NOCOPY VARCHAR2,
2973 document_type IN OUT NOCOPY VARCHAR2
2974 )
2975 IS
2976 -- Declare local variables
2977 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NR_NTF_FINAL_APPROVAL_FYI';
2978
2979 l_return_status VARCHAR2(1);
2980 l_msg_count NUMBER;
2981 l_msg_data VARCHAR2(2000);
2982
2983 l_hyphen_pos1 NUMBER;
2984 l_item_type VARCHAR2(30);
2985 l_item_key VARCHAR2(30);
2986 l_body VARCHAR2(3500);
2987 l_object_type VARCHAR2(30);
2988 l_object_id NUMBER;
2989 l_error_msg VARCHAR2(2000);
2990 l_requester_note VARCHAR2(4000);
2991
2992 BEGIN
2993
2994 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2995 THEN
2996 fnd_log.string
2997 (
2998 fnd_log.level_procedure,
2999 l_debug_module||'.begin',
3000 'At the start of PLSQL procedure'
3001 );
3002 END IF;
3003
3004 document_type := 'text/plain';
3005
3006 -- parse document_id for the ':' dividing item type name from item key value
3007 l_hyphen_pos1 := INSTR (document_id, ':');
3008 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
3009 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
3010
3011 l_object_type := wf_engine.getitemattrtext
3012 (
3013 itemtype => l_item_type,
3014 itemkey => l_item_key,
3015 aname => 'OBJECT_TYPE'
3016 );
3017
3018 l_object_id := wf_engine.getitemattrNumber
3019 (
3020 itemtype => l_item_type,
3021 itemkey => l_item_key,
3022 aname => 'OBJECT_ID'
3023 );
3024
3025 l_requester_note := wf_engine.getitemattrtext
3026 (
3027 itemtype => l_item_type
3028 ,itemkey => l_item_key
3029 ,aname => 'REQUESTER_NOTE'
3030 );
3031
3032 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_FYI_FINAL');
3033 l_body := fnd_message.get;
3034
3035 document := l_body || l_requester_note;
3036
3037 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3038 THEN
3039 fnd_log.string
3040 (
3041 fnd_log.level_statement,
3042 l_debug_module,
3043 'document='||document
3044 );
3045 END IF;
3046
3047 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3048 THEN
3049 fnd_log.string
3050 (
3051 fnd_log.level_procedure,
3052 l_debug_module||'.end',
3053 'At the end of PLSQL procedure'
3054 );
3055 END IF;
3056
3057 RETURN;
3058
3059 EXCEPTION
3060 WHEN FND_API.G_EXC_ERROR THEN
3061 FND_MSG_PUB.Count_And_Get
3062 (
3063 p_encoded => FND_API.G_FALSE,
3064 p_count => l_msg_count,
3065 p_data => l_msg_data
3066 );
3067
3068 AHL_GENERIC_APRV_PVT.handle_error
3069 (
3070 p_itemtype => l_item_type,
3071 p_itemkey => l_item_key,
3072 p_msg_count => l_msg_count,
3073 p_msg_data => l_msg_data,
3074 p_attr_name => 'ERROR_MSG',
3075 x_error_msg => l_error_msg
3076 );
3077
3078 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3079 THEN
3080 fnd_log.string
3081 (
3082 fnd_log.level_exception,
3083 l_debug_module,
3084 l_error_msg
3085 );
3086 END IF;
3087
3088 wf_core.context
3089 (
3090 'AHL_MEL_CDL_APPROVALS_PVT',
3091 'NR_NTF_FINAL_APPROVAL_FYI',
3092 l_item_type,
3093 l_item_key,
3094 l_error_msg
3095 );
3096 RAISE;
3097
3098 WHEN OTHERS THEN
3099 wf_core.context
3100 (
3101 'AHL_MEL_CDL_APPROVALS_PVT',
3102 'NR_NTF_FINAL_APPROVAL_FYI',
3103 l_item_type,
3104 l_item_key
3105 );
3106 RAISE;
3107
3108 END NR_NTF_FINAL_APPROVAL_FYI;
3109
3110
3111 -- Start of Comments --
3112 --
3113 -- Procedure name : NR_NTF_REJECTED_FYI
3114 -- Type : Private
3115 -- Description : This procedure generates the FYI document for rejected workflow notifications
3116 --
3117 -- Version :
3118 -- Initial Version 1.0
3119 --
3120 -- End of Comments --
3121
3122 PROCEDURE NR_NTF_REJECTED_FYI
3123 (
3124 document_id IN VARCHAR2,
3125 display_type IN VARCHAR2,
3126 document IN OUT NOCOPY VARCHAR2,
3127 document_type IN OUT NOCOPY VARCHAR2
3128 )
3129 IS
3130 -- Declare local variables
3131 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NR_NTF_REJECTED_FYI';
3132
3133 l_return_status VARCHAR2(1);
3134 l_msg_count NUMBER;
3135 l_msg_data VARCHAR2(2000);
3136
3137 l_hyphen_pos1 NUMBER;
3138 l_item_type VARCHAR2(30);
3139 l_item_key VARCHAR2(30);
3140 l_approver VARCHAR2(30);
3141 l_body VARCHAR2(3500);
3142 l_object_type VARCHAR2(30);
3143 l_object_id NUMBER;
3144 l_error_msg VARCHAR2(2000);
3145 l_requester_note VARCHAR2(4000);
3146 l_approver_note VARCHAR2(4000);
3147
3148 BEGIN
3149
3150 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3151 THEN
3152 fnd_log.string
3153 (
3154 fnd_log.level_procedure,
3155 l_debug_module||'.begin',
3156 'At the start of PLSQL procedure'
3157 );
3158 END IF;
3159
3160 document_type := 'text/plain';
3161
3162 -- parse document_id for the ':' dividing item type name from item key value
3163 l_hyphen_pos1 := INSTR (document_id, ':');
3164 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
3165 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
3166
3167 l_object_type := wf_engine.getitemattrtext
3168 (
3169 itemtype => l_item_type,
3170 itemkey => l_item_key,
3171 aname => 'OBJECT_TYPE'
3172 );
3173
3174 l_object_id := wf_engine.getitemattrNumber
3175 (
3176 itemtype => l_item_type,
3177 itemkey => l_item_key,
3178 aname => 'OBJECT_ID'
3179 );
3180
3181 l_approver := wf_engine.getitemattrtext
3182 (
3183 itemtype => l_item_type,
3184 itemkey => l_item_key,
3185 aname => 'APPROVER'
3186 );
3187
3188 l_requester_note := wf_engine.getitemattrtext
3189 (
3190 itemtype => l_item_type
3191 ,itemkey => l_item_key
3192 ,aname => 'REQUESTER_NOTE'
3193 );
3194
3195
3196 l_approver_note := wf_engine.getitemattrtext(
3197 itemtype => l_item_type,
3198 itemkey => l_item_key,
3199 aname => 'APPROVER NOTE'
3200 );
3201
3202 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_FYI_RJCT');
3203 fnd_message.set_token('APPROVER',l_approver, false);
3204 l_body := fnd_message.get;
3205
3206 document := l_body || l_requester_note;
3207
3208 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3209 THEN
3210 fnd_log.string
3211 (
3212 fnd_log.level_statement,
3213 l_debug_module,
3214 'document='||document
3215 );
3216 END IF;
3217
3218 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3219 THEN
3220 fnd_log.string
3221 (
3222 fnd_log.level_procedure,
3223 l_debug_module||'.end',
3224 'At the end of PLSQL procedure'
3225 );
3226 END IF;
3227
3228 EXCEPTION
3229 WHEN FND_API.G_EXC_ERROR THEN
3230 FND_MSG_PUB.Count_And_Get
3231 (
3232 p_encoded => FND_API.G_FALSE,
3233 p_count => l_msg_count,
3234 p_data => l_msg_data
3235 );
3236
3237 AHL_GENERIC_APRV_PVT.handle_error
3238 (
3239 p_itemtype => l_item_type,
3240 p_itemkey => l_item_key,
3241 p_msg_count => l_msg_count,
3242 p_msg_data => l_msg_data,
3243 p_attr_name => 'ERROR_MSG',
3244 x_error_msg => l_error_msg
3245 );
3246
3247 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3248 THEN
3249 fnd_log.string
3250 (
3251 fnd_log.level_exception,
3252 l_debug_module,
3253 l_error_msg
3254 );
3255 END IF;
3256
3257 wf_core.context
3258 (
3259 'AHL_MEL_CDL_APPROVALS_PVT',
3260 'NR_NTF_REJECTED_FYI',
3261 l_item_type,
3262 l_item_key,
3263 l_error_msg
3264 );
3265 RAISE;
3266
3267 WHEN OTHERS THEN
3268 wf_core.context
3269 (
3270 'AHL_MEL_CDL_APPROVALS_PVT',
3271 'NR_NTF_REJECTED_FYI',
3272 l_item_type,
3273 l_item_key
3274 );
3275 RAISE;
3276
3277 END NR_NTF_REJECTED_FYI;
3278
3279
3280
3281 -- Start of Comments --
3282 --
3283 -- Procedure name : NR_NTF_APPROVAL
3284 -- Type : Private
3285 -- Description : This procedure generates the document for sending to-approve notifications
3286 --
3287 -- Version :
3288 -- Initial Version 1.0
3289 --
3290 -- End of Comments --
3291 PROCEDURE NR_NTF_APPROVAL
3292 (
3293 document_id IN VARCHAR2,
3294 display_type IN VARCHAR2,
3295 document IN OUT NOCOPY VARCHAR2,
3296 document_type IN OUT NOCOPY VARCHAR2
3297 )
3298 IS
3299 -- Declare local variables
3300 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NR_NTF_APPROVAL';
3301
3302 l_return_status VARCHAR2(1);
3303 l_msg_count NUMBER;
3304 l_msg_data VARCHAR2(2000);
3305
3306 l_hyphen_pos1 NUMBER;
3307 l_item_type VARCHAR2(30);
3308 l_item_key VARCHAR2(30);
3309 l_requester VARCHAR2(30);
3310 l_requester_note VARCHAR2(4000);
3311 l_body VARCHAR2(3500);
3312 l_object_type VARCHAR2(30);
3313 l_object_id NUMBER;
3314 l_error_msg VARCHAR2(2000);
3315
3316 BEGIN
3317
3318 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3319 THEN
3320 fnd_log.string
3321 (
3322 fnd_log.level_procedure,
3323 l_debug_module||'.begin',
3324 'At the start of PLSQL procedure'
3325 );
3326 END IF;
3327
3328 document_type := 'text/plain';
3329
3330 -- parse document_id for the ':' dividing item type name from item key value
3331 l_hyphen_pos1 := INSTR (document_id, ':');
3332 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
3333 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
3334
3335 l_object_type := wf_engine.getitemattrtext
3336 (
3337 itemtype => l_item_type,
3338 itemkey => l_item_key,
3339 aname => 'OBJECT_TYPE'
3340 );
3341
3342 l_object_id := wf_engine.getitemattrNumber
3343 (
3344 itemtype => l_item_type,
3345 itemkey => l_item_key,
3346 aname => 'OBJECT_ID'
3347 );
3348
3349 l_requester := wf_engine.getitemattrtext
3350 (
3351 itemtype => l_item_type,
3352 itemkey => l_item_key,
3353 aname => 'REQUESTER'
3354 );
3355
3356 l_requester_note := wf_engine.getitemattrtext
3357 (
3358 itemtype => l_item_type,
3359 itemkey => l_item_key,
3360 aname => 'REQUESTER_NOTE'
3361 );
3362
3363 OPEN get_ue_mel_cdl_details(l_object_id);
3364 FETCH get_ue_mel_cdl_details INTO l_ue_mel_cdl_details_rec;
3365 CLOSE get_ue_mel_cdl_details;
3366
3367 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_APPROVAL');
3368 fnd_message.set_token('REQUESTER',l_requester, false);
3369 fnd_message.set_token('MEL_CDL',l_ue_mel_cdl_details_rec.mel_cdl_type_code);
3370
3371 l_body := fnd_message.get;
3372
3373 document := l_body || l_requester_note;
3374
3375 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3376 THEN
3377 fnd_log.string
3378 (
3379 fnd_log.level_statement,
3380 l_debug_module,
3381 'document='||document
3382 );
3383 END IF;
3384
3385 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3386 THEN
3387 fnd_log.string
3388 (
3389 fnd_log.level_procedure,
3390 l_debug_module||'.end',
3391 'At the end of PLSQL procedure'
3392 );
3393 END IF;
3394
3395 EXCEPTION
3396 WHEN FND_API.G_EXC_ERROR THEN
3397 FND_MSG_PUB.Count_And_Get
3398 (
3399 p_encoded => FND_API.G_FALSE,
3400 p_count => l_msg_count,
3401 p_data => l_msg_data
3402 );
3403
3404 AHL_GENERIC_APRV_PVT.handle_error
3405 (
3406 p_itemtype => l_item_type,
3407 p_itemkey => l_item_key,
3408 p_msg_count => l_msg_count,
3409 p_msg_data => l_msg_data,
3410 p_attr_name => 'ERROR_MSG',
3411 x_error_msg => l_error_msg
3412 );
3413
3414 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3415 THEN
3416 fnd_log.string
3417 (
3418 fnd_log.level_exception,
3419 l_debug_module,
3420 l_error_msg
3421 );
3422 END IF;
3423
3424 wf_core.context
3425 (
3426 'AHL_MEL_CDL_APPROVALS_PVT',
3427 'NR_NTF_APPROVAL',
3428 l_item_type,
3429 l_item_key,
3430 l_error_msg
3431 );
3432 RAISE;
3433
3434 WHEN OTHERS THEN
3435 wf_core.context
3436 (
3437 'AHL_MEL_CDL_APPROVALS_PVT',
3438 'NR_NTF_APPROVAL',
3439 l_item_type,
3440 l_item_key
3441 );
3442 RAISE;
3443
3444 END NR_NTF_APPROVAL;
3445
3446
3447 -- Start of Comments --
3448 --
3449 -- Procedure name : NR_NTF_APPROVAL_REMINDER
3450 -- Type : Private
3451 -- Description : This procedure generates the document for sending reminders
3452 --
3453 -- Version :
3454 -- Initial Version 1.0
3455 --
3456 -- End of Comments --
3457 PROCEDURE NR_NTF_APPROVAL_REMINDER
3458 (
3459 document_id IN VARCHAR2,
3460 display_type IN VARCHAR2,
3461 document IN OUT NOCOPY VARCHAR2,
3462 document_type IN OUT NOCOPY VARCHAR2
3463 )
3464 IS
3465 -- Declare local variables
3466 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NR_NTF_APPROVAL_REMINDER';
3467
3468 l_return_status VARCHAR2(1);
3469 l_msg_count NUMBER;
3470 l_msg_data VARCHAR2(2000);
3471
3472 l_hyphen_pos1 NUMBER;
3473 l_item_type VARCHAR2(30);
3474 l_item_key VARCHAR2(30);
3475 l_requester VARCHAR2(30);
3476 l_requester_note VARCHAR2(4000);
3477 l_body VARCHAR2(3500);
3478 l_object_type VARCHAR2(30);
3479 l_object_id NUMBER;
3480 l_error_msg VARCHAR2(2000);
3481
3482 BEGIN
3483
3484 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3485 THEN
3486 fnd_log.string
3487 (
3488 fnd_log.level_procedure,
3489 l_debug_module||'.begin',
3490 'At the start of PLSQL procedure'
3491 );
3492 END IF;
3493
3494 document_type := 'text/plain';
3495
3496 -- parse document_id for the ':' dividing item type name from item key value
3497 l_hyphen_pos1 := INSTR (document_id, ':');
3498 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
3499 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
3500
3501 l_object_type := wf_engine.getitemattrtext
3502 (
3503 itemtype => l_item_type,
3504 itemkey => l_item_key,
3505 aname => 'OBJECT_TYPE'
3506 );
3507
3508 l_object_id := wf_engine.getitemattrNumber
3509 (
3510 itemtype => l_item_type,
3511 itemkey => l_item_key,
3512 aname => 'OBJECT_ID'
3513 );
3514
3515 l_requester := wf_engine.getitemattrtext
3516 (
3517 itemtype => l_item_type,
3518 itemkey => l_item_key,
3519 aname => 'REQUESTER'
3520 );
3521
3522 l_requester_note := wf_engine.getitemattrtext
3523 (
3524 itemtype => l_item_type,
3525 itemkey => l_item_key,
3526 aname => 'REQUESTER_NOTE'
3527 );
3528
3529 OPEN get_ue_mel_cdl_details(l_object_id);
3530 FETCH get_ue_mel_cdl_details into l_ue_mel_cdl_details_rec;
3531 IF (get_ue_mel_cdl_details%NOTFOUND)
3532 THEN
3533 CLOSE get_ue_mel_cdl_details;
3534 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ID_INVALID');
3535 fnd_message.set_token('HDR_ID', l_object_id, false);
3536 fnd_msg_pub.add;
3537 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3538 THEN
3539 fnd_log.message
3540 (
3541 fnd_log.level_exception,
3542 l_debug_module,
3543 false
3544 );
3545 END IF;
3546 RAISE FND_API.G_EXC_ERROR;
3547
3548 END IF;
3549 CLOSE get_ue_mel_cdl_details;
3550
3551 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_REMIND');
3552 fnd_message.set_token('REQUESTER',l_requester, false);
3553 fnd_message.set_token('MEL_CDL',l_ue_mel_cdl_details_rec.mel_cdl_type_code, false);
3554 l_body := fnd_message.get;
3555
3556 document := l_body || l_requester_note;
3557
3558 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3559 THEN
3560 fnd_log.string
3561 (
3562 fnd_log.level_statement,
3563 l_debug_module,
3564 'document='||document
3565 );
3566 END IF;
3567
3568 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3569 THEN
3570 fnd_log.string
3571 (
3572 fnd_log.level_procedure,
3573 l_debug_module||'.end',
3574 'At the end of PLSQL procedure'
3575 );
3576 END IF;
3577
3578 EXCEPTION
3579 WHEN FND_API.G_EXC_ERROR THEN
3580 FND_MSG_PUB.Count_And_Get
3581 (
3582 p_encoded => FND_API.G_FALSE,
3583 p_count => l_msg_count,
3584 p_data => l_msg_data
3585 );
3586
3587 AHL_GENERIC_APRV_PVT.handle_error
3588 (
3589 p_itemtype => l_item_type,
3590 p_itemkey => l_item_key,
3591 p_msg_count => l_msg_count,
3592 p_msg_data => l_msg_data,
3593 p_attr_name => 'ERROR_MSG',
3594 x_error_msg => l_error_msg
3595 );
3596
3597 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3598 THEN
3599 fnd_log.string
3600 (
3601 fnd_log.level_exception,
3602 l_debug_module,
3603 l_error_msg
3604 );
3605 END IF;
3606
3607 wf_core.context
3608 (
3609 'AHL_MEL_CDL_APPROVALS_PVT',
3610 'NR_NTF_APPROVAL_REMINDER',
3611 l_item_type,
3612 l_item_key,
3613 l_error_msg
3614 );
3615 RAISE;
3616
3617 WHEN OTHERS THEN
3618 wf_core.context
3619 (
3620 'AHL_MEL_CDL_APPROVALS_PVT',
3621 'NR_NTF_APPROVAL_REMINDER',
3622 l_item_type,
3623 l_item_key
3624 );
3625 RAISE;
3626
3627 END NR_NTF_APPROVAL_REMINDER;
3628
3629
3630
3631 -- Start of Comments --
3632 --
3633 -- Procedure name : NR_NTF_ERROR_ACT
3634 -- Type : Private
3635 -- Description : This procedure generates the document for requesting action on error
3636 --
3637 -- Version :
3638 -- Initial Version 1.0
3639 --
3640 -- End of Comments --
3641 PROCEDURE NR_NTF_ERROR_ACT
3642 (
3643 document_id IN VARCHAR2,
3644 display_type IN VARCHAR2,
3645 document IN OUT NOCOPY VARCHAR2,
3646 document_type IN OUT NOCOPY VARCHAR2
3647 )
3648
3649 IS
3650 -- Declare local variables
3651 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NR_NTF_ERROR_ACT';
3652
3653 l_return_status VARCHAR2(1);
3654 l_msg_count NUMBER;
3655 l_msg_data VARCHAR2(2000);
3656
3657 l_hyphen_pos1 NUMBER;
3658 l_item_type VARCHAR2(30);
3659 l_item_key VARCHAR2(30);
3660 l_body VARCHAR2(3500);
3661 l_object_type VARCHAR2(30);
3662 l_object_id NUMBER;
3663 l_error_msg VARCHAR2(2000);
3664 l_requester_note VARCHAR2(4000);
3665
3666 BEGIN
3667
3668 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3669 THEN
3670 fnd_log.string
3671 (
3672 fnd_log.level_procedure,
3673 l_debug_module||'.begin',
3674 'At the start of PLSQL procedure'
3675 );
3676 END IF;
3677
3678 document_type := 'text/plain';
3679
3680 -- parse document_id for the ':' dividing item type name from item key value
3681 l_hyphen_pos1 := INSTR (document_id, ':');
3682 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
3683 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
3684
3685 l_object_type := wf_engine.getitemattrtext
3686 (
3687 itemtype => l_item_type,
3688 itemkey => l_item_key,
3689 aname => 'OBJECT_TYPE'
3690 );
3691
3692 l_object_id := wf_engine.getitemattrNumber
3693 (
3694 itemtype => l_item_type,
3695 itemkey => l_item_key,
3696 aname => 'OBJECT_ID'
3697 );
3698
3699 l_error_msg := wf_engine.getitemattrText
3700 (
3701 itemtype => l_item_type,
3702 itemkey => l_item_key,
3703 aname => 'ERROR_MSG'
3704 );
3705
3706 l_requester_note := wf_engine.getitemattrtext(
3707 itemtype => l_item_type
3708 ,itemkey => l_item_key
3709 ,aname => 'REQUESTER_NOTE'
3710 );
3711
3712 fnd_message.set_name('AHL', 'AHL_UMP_NR_NTF_ERROR_ACT');
3713 fnd_message.set_token('ERR_MSG',l_error_msg, false);
3714 l_body := fnd_message.get;
3715
3716 document := l_body || l_requester_note;
3717
3718 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3719 THEN
3720 fnd_log.string
3721 (
3722 fnd_log.level_statement,
3723 l_debug_module,
3724 'document='||document
3725 );
3726 END IF;
3727
3728 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3729 THEN
3730 fnd_log.string
3731 (
3732 fnd_log.level_procedure,
3733 l_debug_module||'.end',
3734 'At the end of PLSQL procedure'
3735 );
3736 END IF;
3737
3738 RETURN;
3739
3740 EXCEPTION
3741 WHEN FND_API.G_EXC_ERROR THEN
3742 FND_MSG_PUB.Count_And_Get
3743 (
3747 );
3744 p_encoded => FND_API.G_FALSE,
3745 p_count => l_msg_count,
3746 p_data => l_msg_data
3748
3749 AHL_GENERIC_APRV_PVT.handle_error
3750 (
3751 p_itemtype => l_item_type,
3752 p_itemkey => l_item_key,
3753 p_msg_count => l_msg_count,
3754 p_msg_data => l_msg_data,
3755 p_attr_name => 'ERROR_MSG',
3756 x_error_msg => l_error_msg
3757 );
3758
3759 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3760 THEN
3761 fnd_log.string
3762 (
3763 fnd_log.level_exception,
3764 l_debug_module,
3765 l_error_msg
3766 );
3767 END IF;
3768
3769 wf_core.context
3770 (
3771 'AHL_MEL_CDL_APPROVALS_PVT',
3772 'NR_NTF_ERROR_ACT',
3773 l_item_type,
3774 l_item_key,
3775 l_error_msg
3776 );
3777 RAISE;
3778
3779 WHEN OTHERS THEN
3780 wf_core.context
3781 (
3782 'AHL_MEL_CDL_APPROVALS_PVT',
3783 'NR_NTF_ERROR_ACT',
3784 l_item_type,
3785 l_item_key
3786 );
3787 RAISE;
3788
3789 END NR_NTF_ERROR_ACT;
3790
3791
3792 -- Start of Comments --
3793 --
3794 -- Procedure name : NR_UPDATE_STATUS
3795 -- Type : Private
3796 -- Description : This procedure handles the final complete step of the workflow process
3797 --
3798 -- Version :
3799 -- Initial Version 1.0
3800 --
3801 -- End of Comments --
3802 PROCEDURE NR_UPDATE_STATUS
3803 (
3804 itemtype IN VARCHAR2,
3805 itemkey IN VARCHAR2,
3806 actid IN NUMBER,
3807 funcmode IN VARCHAR2,
3808 resultout OUT NOCOPY VARCHAR2
3809 )
3810 IS
3811 -- Declare local variables
3812 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NR_UPDATE_STATUS';
3813
3814 l_error_msg VARCHAR2(4000);
3815 l_approval_status VARCHAR2(30);
3816 l_new_status VARCHAR2(30);
3817 l_object_id NUMBER;
3818 l_object_version_number NUMBER;
3819 l_msg_count NUMBER;
3820 l_msg_data VARCHAR2(4000);
3821 l_return_status VARCHAR2(1);
3822 l_approver_note VARCHAR2(4000);
3823
3824 -- get deferral details.
3825 cursor ue_deferral_csr(p_unit_deferral_id IN NUMBER)
3826 is
3827 select unit_effectivity_id, object_version_number, unit_deferral_type,
3828 approval_status_code, ata_sequence_id
3829 from ahl_unit_deferrals_b
3830 where unit_deferral_id = p_unit_deferral_id and
3831 unit_deferral_type in ('MEL', 'CDL')
3832 for update of object_version_number;
3833
3834 -- get ue details.
3835 cursor unit_effect_csr (p_ue_id IN NUMBER)
3836 is
3837 select unit_effectivity_id, status_code,
3838 cs_incident_id, MEL_CDL_TYPE_CODE, csi_item_instance_id,
3839 unit_config_header_id
3840 from ahl_unit_effectivities_b
3841 where unit_effectivity_id = p_ue_id
3842 and object_type = 'SR'
3843 and (status_code IS NULL or status_code = 'INIT_DUE')
3844 for update of object_version_number;
3845
3846 l_deferral_rec ue_deferral_csr%ROWTYPE;
3847 l_ue_rec unit_effect_csr%ROWTYPE;
3848
3849 BEGIN
3850 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3851 fnd_log.string
3852 (
3853 fnd_log.level_procedure,
3854 l_debug_module || '.begin',
3855 'At the start of PLSQL procedure'
3856 );
3857 END IF;
3858
3859 SAVEPOINT AHL_NR_UPDATE_STATUS;
3860
3861 l_return_status := FND_API.G_RET_STS_SUCCESS;
3862
3863 -- MOAC initialization.
3864 MO_GLOBAL.init('AHL');
3865
3866 IF (funcmode = 'RUN') THEN
3867
3868 l_approval_status := wf_engine.getitemattrtext(
3869 itemtype => itemtype,
3870 itemkey => itemkey,
3871 aname => 'UPDATE_GEN_STATUS'
3872 );
3873
3874 l_object_id := wf_engine.getitemattrnumber(
3875 itemtype => itemtype,
3876 itemkey => itemkey,
3877 aname => 'OBJECT_ID'
3878 );
3879 l_object_version_number := wf_engine.getitemattrnumber(
3880 itemtype => itemtype,
3881 itemkey => itemkey,
3882 aname => 'OBJECT_VER'
3883 );
3884 l_approver_note := wf_engine.getitemattrtext(
3885 itemtype => itemtype,
3886 itemkey => itemkey,
3887 aname => 'APPROVER NOTE'
3888 );
3889
3890 UPDATE AHL_UNIT_DEFERRALS_TL
3891 SET approver_notes = l_approver_note,
3892 SOURCE_LANG = userenv('LANG')
3893 WHERE unit_deferral_id = l_object_id
3894 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
3895
3896 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3897 fnd_log.string
3898 (
3899 fnd_log.level_statement,
3900 l_debug_module,
3901 'unit_deferral_id : ' || l_object_id
3902 );
3903 fnd_log.string
3904 (
3905 fnd_log.level_statement,
3906 l_debug_module,
3907 'object_version_number : ' || l_object_version_number
3908 );
3909 fnd_log.string
3910 (
3911 fnd_log.level_statement,
3912 l_debug_module,
3913 'approval status : ' || l_approval_status
3914 );
3915
3916 END IF;
3917
3918 IF (l_approval_status IN( 'DEFERRED')) THEN
3919
3920 l_new_status := wf_engine.getitemattrText(
3921 itemtype => itemtype,
3922 itemkey => itemkey,
3923 aname => 'NEW_STATUS_ID'
3924 );
3925
3926 -- get deferral details.
3927 OPEN ue_deferral_csr(l_object_id);
3928 FETCH ue_deferral_csr INTO l_deferral_rec;
3929 IF (ue_deferral_csr%NOTFOUND) THEN
3930 CLOSE ue_deferral_csr;
3931 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_NTF_ID_INVALID');
3932 FND_MESSAGE.Set_Token('HDR_ID',l_object_id);
3933 FND_MSG_PUB.ADD;
3934 RAISE FND_API.G_EXC_ERROR;
3935 END IF;
3936 CLOSE ue_deferral_csr;
3937
3938 -- check deferral ovn.
3939
3940 -- get ue details.
3941 OPEN unit_effect_csr(l_deferral_rec.unit_effectivity_id);
3942 FETCH unit_effect_csr INTO l_ue_rec;
3943 IF (unit_effect_csr%NOTFOUND) THEN
3944 CLOSE unit_effect_csr;
3945 FND_MESSAGE.Set_Name('AHL','AHL_UMP_NR_WF_UE_INVALID');
3946 FND_MESSAGE.Set_Token('UE_ID',l_deferral_rec.unit_effectivity_id);
3947 FND_MSG_PUB.ADD;
3948 RAISE FND_API.G_EXC_ERROR;
3949 END IF;
3950 CLOSE unit_effect_csr;
3951
3952 -- Check ue status.
3953
3954 -- Check Unit locked.
3955 IF AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => null,
3956 p_ue_id => l_deferral_rec.unit_effectivity_id,
3957 p_visit_id => null,
3958 p_item_instance_id => null) = FND_API.g_true THEN
3959 -- Unit is locked, therefore cannot proceed for approval.
3960 -- and cannot login to the workorder
3961 FND_MESSAGE.set_name('AHL', 'AHL_UMP_NR_UNITLCKED');
3962 FND_MESSAGE.set_token('UE_ID', l_deferral_rec.unit_effectivity_id);
3966
3963 FND_MSG_PUB.ADD;
3964 RAISE FND_API.G_EXC_ERROR;
3965 END IF;
3967
3968 -- process for M and O procedures.
3969 AHL_UMP_NONROUTINES_PVT.Process_MO_procedures (l_deferral_rec.unit_effectivity_id,
3970 l_object_id,
3971 l_object_version_number,
3972 l_deferral_rec.ata_sequence_id,
3973 l_ue_rec.cs_incident_id,
3974 l_ue_rec.csi_item_instance_id);
3975 ELSE
3976 l_new_status := wf_engine.getitemattrText(
3977 itemtype => itemtype,
3978 itemkey => itemkey,
3979 aname => 'REJECT_STATUS_ID'
3980 );
3981 AHL_PRD_DF_PVT.process_approval_rejected(
3982 p_unit_deferral_id => l_object_id,
3983 p_object_version_number => l_object_version_number,
3984 p_new_status => l_new_status,
3985 x_return_status => l_return_status
3986 );
3987 END IF;
3988
3989 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3990 fnd_log.string
3991 (
3992 fnd_log.level_statement,
3993 l_debug_module,
3994 'new status : ' || l_new_status
3995 );
3996 fnd_log.string
3997 (
3998 fnd_log.level_statement,
3999 l_debug_module,
4000 'return status after process_approval_rejected API call : ' || l_return_status
4001 );
4002 END IF;
4003
4004 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
4005 RAISE FND_API.G_EXC_ERROR;
4006 ELSE
4007 COMMIT WORK;
4008 END IF;
4009 resultout := 'COMPLETE:';
4010 ELSIF (funcmode IN ('CANCEL','TIMEOUT'))THEN
4011 resultout := 'COMPLETE:';
4012 END IF;
4013
4014 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
4015 fnd_log.string
4016 (
4017 fnd_log.level_procedure,
4018 l_debug_module,
4019
4020 'At the end of PLSQL procedure'
4021 );
4022 END IF;
4023
4024
4025 EXCEPTION
4026 WHEN fnd_api.G_EXC_ERROR OR FND_API.G_EXC_UNEXPECTED_ERROR THEN
4027 ROLLBACK TO AHL_NR_UPDATE_STATUS;
4028
4029 FND_MSG_PUB.Count_And_Get (
4030 p_encoded => FND_API.G_FALSE,
4031 p_count => l_msg_count,
4032 p_data => l_msg_data
4033 );
4034
4035 ahl_generic_aprv_pvt.Handle_Error
4036 (p_itemtype => itemtype,
4037 p_itemkey => itemkey ,
4038 p_msg_count => l_msg_count, -- Number of error Messages
4039 p_msg_data => l_msg_data ,
4040 p_attr_name => 'ERROR_MSG',
4041 x_error_msg => l_error_msg
4042 ) ;
4043
4044 wf_core.context('AHL_MEL_CDL_APPROVALS_PVT',
4045 'NR_UPDATE_STATUS',
4046 itemtype,itemkey,l_error_msg);
4047
4048
4049 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4050 fnd_log.string
4051 (
4052 fnd_log.level_statement,
4053 l_debug_module,
4054 'Processing Exception'
4055 );
4056 END IF;
4057
4058 -- update validation errors.
4059 UPDATE AHL_UNIT_DEFERRALS_TL
4060 SET approver_notes = substrb(l_error_msg,1,4000),
4061 last_update_date = sysdate,
4062 last_updated_by = fnd_global.user_id,
4063 last_update_login = fnd_global.login_id,
4064 source_lang = userenv('LANG')
4065 WHERE unit_deferral_id = l_object_id
4066 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
4067
4068 UPDATE AHL_UNIT_DEFERRALS_B
4069 SET approval_status_code = 'DEFERRAL_REJECTED',
4070 object_version_number = object_version_number + 1,
4071 last_update_date = sysdate,
4072 last_updated_by = fnd_global.user_id,
4073 last_update_login = fnd_global.login_id
4074 WHERE unit_deferral_id = l_object_id;
4075 COMMIT WORK;
4076
4077 /*
4078 -- process for deferral rejected status.
4079 NR_Rollback_Status (p_unit_deferral_id => l_object_id,
4080 p_unit_deferral_ovn => l_object_version_number,
4081 p_new_status => G_DEFERRAL_REJECTED,
4082 p_itemtype => itemtype,
4083 p_itemkey => itemkey,
4084 p_actid => actid,
4085 p_funcmode => funcmode,
4086 x_resultout => resultout);
4087 */
4088 WHEN OTHERS THEN
4089 ROLLBACK TO AHL_NR_UPDATE_STATUS;
4090 wf_core.context( 'AHL_MEL_CDL_APPROVALS_PVT', 'NR_UPDATE_STATUS', itemtype, itemkey );
4091
4092 l_error_msg := SQLCODE || ': ' || SQLERRM;
4093
4094 -- update validation errors.
4095 UPDATE AHL_UNIT_DEFERRALS_TL
4096 SET approver_notes = substrb(l_error_msg,1,4000),
4097 last_update_date = sysdate,
4098 last_updated_by = fnd_global.user_id,
4099 last_update_login = fnd_global.login_id,
4100 source_lang = userenv('LANG')
4101 WHERE unit_deferral_id = l_object_id
4105 SET approval_status_code = 'DEFERRAL_REJECTED',
4102 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
4103
4104 UPDATE AHL_UNIT_DEFERRALS_B
4106 object_version_number = object_version_number + 1,
4107 last_update_date = sysdate,
4108 last_updated_by = fnd_global.user_id,
4109 last_update_login = fnd_global.login_id
4110 WHERE unit_deferral_id = l_object_id;
4111
4112 COMMIT WORK;
4113
4114 RAISE;
4115
4116
4117 END NR_UPDATE_STATUS;
4118
4119
4120 -- Start of Comments --
4121 --
4122 -- Procedure name : NR_REVERT_STATUS
4123 -- Type : Private
4124 -- Description : This procedure handles revert of the workflow process on any error
4125 --
4126 -- Version :
4127 -- Initial Version 1.0
4128 --
4129 -- End of Comments --
4130 PROCEDURE NR_REVERT_STATUS
4131 (
4132 itemtype IN VARCHAR2,
4133 itemkey IN VARCHAR2,
4134 actid IN NUMBER,
4135 funcmode IN VARCHAR2,
4136 resultout OUT NOCOPY VARCHAR2
4137 )
4138 IS
4139 -- Declare local variables
4140 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.REVERT_STATUS';
4141
4142 l_return_status VARCHAR2(1);
4143 l_msg_count NUMBER;
4144 l_msg_data VARCHAR2(2000);
4145
4146 l_object_id NUMBER;
4147 l_object_ovn NUMBER;
4148 l_orig_status VARCHAR2(30);
4149 l_error_msg VARCHAR2(2000);
4150
4151 BEGIN
4152
4153 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4154 THEN
4155 fnd_log.string
4156 (
4157 fnd_log.level_procedure,
4158 l_debug_module||'.begin',
4159 'At the start of PLSQL procedure'
4160 );
4161 END IF;
4162
4163 SAVEPOINT AHL_NR_REVERT_STATUS;
4164
4165 -- set Org context.
4166 MO_GLOBAL.INIT('AHL');
4167
4168 --
4169 -- RUN mode
4170 --
4171 IF (funcmode = 'RUN')
4172 THEN
4173
4174 l_object_id := wf_engine.getitemattrnumber
4175 (
4176 itemtype => itemtype,
4177 itemkey => itemkey,
4178 aname => 'OBJECT_ID'
4179 );
4180
4181 l_object_ovn := wf_engine.getitemattrnumber
4182 (
4183 itemtype => itemtype,
4184 itemkey => itemkey,
4185 aname => 'OBJECT_VER'
4186 );
4187
4188
4189 l_orig_status := wf_engine.getitemattrnumber
4190 (
4191 itemtype => itemtype,
4192 itemkey => itemkey,
4193 aname => 'ORG_STATUS_ID'
4194 );
4195
4196
4197 OPEN get_ue_mel_cdl_details(l_object_id);
4198 FETCH get_ue_mel_cdl_details into l_ue_mel_cdl_details_rec;
4199 IF (get_ue_mel_cdl_details%NOTFOUND OR l_mel_cdl_rec.object_version_number <> l_object_ovn)
4200 THEN
4201 CLOSE get_ue_mel_cdl_details;
4202
4203 fnd_message.set_name('AHL', 'AHL_MEL_CDL_NTF_ID_INVALID');
4204 fnd_message.set_token('HDR_ID', l_object_id, false);
4205 fnd_msg_pub.add;
4206
4207 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4208 THEN
4209 fnd_log.message
4210 (
4211 fnd_log.level_exception,
4212 l_debug_module,
4213 false
4214 );
4215 END IF;
4216
4217 resultout := 'COMPLETE:ERROR';
4218 RAISE FND_API.G_EXC_ERROR;
4219
4220 END IF;
4221 CLOSE get_ue_mel_cdl_details;
4222
4223 -- revert
4227 p_new_status => l_orig_status,
4224 AHL_PRD_DF_PVT.process_approval_rejected(
4225 p_unit_deferral_id => l_object_id,
4226 p_object_version_number => l_object_ovn,
4228 x_return_status => l_return_status
4229 );
4230
4231 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4232 fnd_log.string
4233 (
4234 fnd_log.level_statement,
4235 l_debug_module,
4236 'return status after process_approval_rejected API call : ' || l_return_status
4237 );
4238 END IF;
4239
4240 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
4241 RAISE FND_API.G_EXC_ERROR;
4242 ELSE
4243 COMMIT WORK;
4244 END IF;
4245
4246 resultout := 'COMPLETE:SUCCESS';
4247
4248 END IF;
4249
4250 --
4251 -- CANCEL mode
4252 --
4253 IF (funcmode = 'CANCEL')
4254 THEN
4255 resultout := 'COMPLETE:';
4256 END IF;
4257
4258 --
4259 -- TIMEOUT mode
4260 --
4261 IF (funcmode = 'TIMEOUT')
4262 THEN
4263 resultout := 'COMPLETE:';
4264 END IF;
4265
4266 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4267 THEN
4268 fnd_log.string
4269 (
4270 fnd_log.level_procedure,
4271 l_debug_module||'.end',
4272 'At the end of PLSQL procedure'
4273 );
4274 END IF;
4275
4276 EXCEPTION
4277 WHEN FND_API.G_EXC_ERROR THEN
4278 ROLLBACK TO AHL_NR_REVERT_STATUS;
4279
4280 FND_MSG_PUB.Count_And_Get
4281 (
4282 p_encoded => FND_API.G_FALSE,
4283 p_count => l_msg_count,
4284 p_data => l_msg_data
4285 );
4286
4287 AHL_GENERIC_APRV_PVT.handle_error
4288 (
4289 p_itemtype => itemtype,
4290 p_itemkey => itemkey,
4291 p_msg_count => l_msg_count,
4295 );
4292 p_msg_data => l_msg_data,
4293 p_attr_name => 'ERROR_MSG',
4294 x_error_msg => l_error_msg
4296
4297 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4298 THEN
4299 fnd_log.string
4300 (
4301 fnd_log.level_exception,
4302 l_debug_module,
4303 l_error_msg
4304 );
4305 END IF;
4306
4307 wf_core.context
4308 (
4309 'AHL_MEL_CDL_APPROVALS_PVT',
4310 'NR_REVERT_STATUS',
4311 itemtype,
4312 itemkey,
4313 actid,
4314 funcmode,
4315 l_error_msg
4316 );
4317 resultout := 'COMPLETE:ERROR';
4318
4319 -- update validation errors.
4320 UPDATE AHL_UNIT_DEFERRALS_TL
4321 SET approver_notes = substrb(l_error_msg,1,4000),
4322 LAST_UPDATE_DATE = sysdate,
4323 LAST_UPDATED_BY = fnd_global.user_id,
4324 LAST_UPDATE_LOGIN = fnd_global.login_id,
4325 SOURCE_LANG = userenv('LANG')
4326 WHERE unit_deferral_id = l_object_id
4327 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
4328
4329 UPDATE AHL_UNIT_DEFERRALS_B
4330 SET approval_status_code = 'DEFERRAL_REJECTED',
4331 object_version_number = object_version_number + 1,
4332 last_update_date = sysdate,
4333 last_updated_by = fnd_global.user_id,
4334 last_update_login = fnd_global.login_id
4335 WHERE unit_deferral_id = l_object_id;
4336
4337 COMMIT WORK;
4338
4339 RAISE;
4340
4341 WHEN OTHERS THEN
4342 ROLLBACK TO AHL_NR_REVERT_STATUS;
4343 wf_core.context
4344 (
4345 'AHL_MEL_CDL_APPROVALS_PVT',
4346 'NR_REVERT_STATUS',
4347 itemtype,
4348 itemkey,
4349 actid,
4350 'Unexpected Error! '||SUBSTR(SQLERRM,1,240)
4351 );
4352
4353 l_error_msg := SQLCODE || ': ' || SQLERRM;
4354 -- update validation errors.
4355 UPDATE AHL_UNIT_DEFERRALS_TL
4356 SET approver_notes = substrb(l_error_msg,1,4000),
4357 LAST_UPDATE_DATE = sysdate,
4358 LAST_UPDATED_BY = fnd_global.user_id,
4359 LAST_UPDATE_LOGIN = fnd_global.login_id,
4360 SOURCE_LANG = userenv('LANG')
4361 WHERE unit_deferral_id = l_object_id
4362 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
4363
4364 UPDATE AHL_UNIT_DEFERRALS_B
4368 last_updated_by = fnd_global.user_id,
4365 SET approval_status_code = 'DEFERRAL_REJECTED',
4366 object_version_number = object_version_number + 1,
4367 last_update_date = sysdate,
4369 last_update_login = fnd_global.login_id
4370 WHERE unit_deferral_id = l_object_id;
4371 COMMIT WORK;
4372
4373 RAISE;
4374
4375 END NR_REVERT_STATUS;
4376
4377 -- procedure to revert NR status and workorder status.
4378 PROCEDURE NR_Rollback_Status (p_unit_deferral_id IN NUMBER,
4379 p_unit_deferral_ovn IN NUMBER,
4380 p_new_status IN NUMBER,
4381 p_itemtype IN VARCHAR2,
4382 p_itemkey IN VARCHAR2,
4383 p_actid IN NUMBER,
4384 p_funcmode IN VARCHAR2,
4385 x_resultout OUT NOCOPY VARCHAR2)
4386 IS
4387 -- Declare local variables
4388 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.NR_Rollback_Status';
4389
4390 l_return_status VARCHAR2(1);
4391 l_msg_count NUMBER;
4392 l_msg_data VARCHAR2(2000);
4393
4394 l_error_msg VARCHAR2(2000);
4395
4396 BEGIN
4397 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4398 fnd_log.string
4399 (
4400 fnd_log.level_statement,
4401 'NR_Rollback_Status',
4402 'Start of API call'
4403 );
4404 END IF;
4405 -- revert
4406 AHL_PRD_DF_PVT.process_approval_rejected(
4407 p_unit_deferral_id => p_unit_deferral_id,
4408 p_object_version_number => p_unit_deferral_ovn,
4409 p_new_status => p_new_status,
4410 x_return_status => l_return_status
4411 );
4412
4413 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4414 fnd_log.string
4415 (
4416 fnd_log.level_statement,
4417 l_debug_module,
4418 'return status after process_approval_rejected API call : ' || l_return_status
4419 );
4420 END IF;
4421
4422 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
4423 RAISE FND_API.G_EXC_ERROR;
4424 ELSE
4425 COMMIT WORK;
4426 END IF;
4427
4428 EXCEPTION
4429
4430 WHEN FND_API.G_EXC_ERROR THEN
4431 FND_MSG_PUB.Count_And_Get
4432 (
4433 p_encoded => FND_API.G_FALSE,
4434 p_count => l_msg_count,
4435 p_data => l_msg_data
4436 );
4437
4438 AHL_GENERIC_APRV_PVT.handle_error
4439 (
4440 p_itemtype => p_itemtype,
4444 p_attr_name => 'ERROR_MSG',
4441 p_itemkey => p_itemkey,
4442 p_msg_count => l_msg_count,
4443 p_msg_data => l_msg_data,
4445 x_error_msg => l_error_msg
4446 );
4447
4448 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4449 THEN
4450 fnd_log.string
4451 (
4452 fnd_log.level_exception,
4453 l_debug_module,
4454 l_error_msg
4455 );
4456 END IF;
4457
4458 wf_core.context
4459 (
4460 'AHL_MEL_CDL_APPROVALS_PVT',
4461 'NR_REVERT_STATUS',
4462 p_itemtype,
4463 p_itemkey,
4464 p_actid,
4465 p_funcmode,
4466 l_error_msg
4467 );
4468 x_resultout := 'COMPLETE:ERROR';
4469
4470 RAISE;
4471
4472 WHEN OTHERS THEN
4473 wf_core.context
4474 (
4475 'AHL_MEL_CDL_APPROVALS_PVT',
4476 'NR_REVERT_STATUS',
4477 p_itemtype,
4478 p_itemkey,
4479 p_actid,
4480 'Unexpected Error! '||SUBSTR(SQLERRM,1,240)
4481 );
4482 x_resultout := 'COMPLETE:ERROR';
4483
4484 RAISE;
4485
4486 END NR_Rollback_Status;
4487
4488 End AHL_MEL_CDL_APPROVALS_PVT;