[Home] [Help]
PACKAGE BODY: APPS.AHL_UC_WF_APPR_PVT
Source
1 PACKAGE BODY AHL_UC_WF_APPR_PVT AS
2 /* $Header: AHLVUWFB.pls 115.2 2003/10/20 19:37:26 sikumar noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_UC_WF_APPR_PVT';
5
6 -- To check if AHL DEBUG is turned ON
7 --G_DEBUG VARCHAR2(1) := FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
8 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
9
10
11 CURSOR get_uc_header_det(c_uc_header_id in number)
12 IS
13 SELECT unit_config_header_id, name, object_version_number, unit_config_status_code, active_uc_status_code
14 FROM ahl_unit_config_headers
15 WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
16 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
17 AND unit_config_header_id = c_uc_header_id;
18
19 ---------------------------
20 -- SET_ACTIVITY_DETAILS
21 ---------------------------
22 PROCEDURE SET_ACTIVITY_DETAILS
23 (
24 itemtype IN VARCHAR2
25 ,itemkey IN VARCHAR2
26 ,actid IN NUMBER
27 ,funcmode IN VARCHAR2
28 ,resultout OUT NOCOPY VARCHAR2)
29 IS
30 --
31 l_object_id NUMBER;
32 l_object_version_number NUMBER;
33 l_object VARCHAR2(30) ;
34 l_approval_type VARCHAR2(30) := 'CONCEPT';
35 l_object_details AHL_GENERIC_APRV_PVT.OBJRECTYP;
36 l_approval_rule_id NUMBER;
37 l_approver_seq NUMBER;
38 l_return_status VARCHAR2(1);
39 l_msg_count NUMBER;
40 l_msg_data VARCHAR2(4000);
41 l_subject VARCHAR2(500);
42 l_error_msg VARCHAR2(2000);
43 l_uc_header_rec get_uc_header_det%rowtype;
44 --
45 BEGIN
46
47 IF G_DEBUG='Y' THEN
48 AHL_DEBUG_PUB.ENABLE_DEBUG;
49 AHL_DEBUG_PUB.debug( 'UC:Start Set Actvity Details');
50 END IF;
51
52 --Initiliaze message list
53 fnd_msg_pub.initialize;
54
55 l_return_status := FND_API.g_ret_sts_success;
56
57
58 l_object_id := wf_engine.getitemattrnumber
59 (
60 itemtype => itemtype
61 ,itemkey => itemkey
62 ,aname => 'OBJECT_ID'
63 );
64 IF G_DEBUG='Y' THEN
65 AHL_DEBUG_PUB.debug('UC: SET_ACTIVITY_DETAILS --> l_object_id='||l_object_id);
66 END IF;
67
68 l_object_version_number := wf_engine.getitemattrnumber
69 (
70 itemtype => itemtype,
71 itemkey => itemkey,
72 aname => 'OBJECT_VER'
73 );
74 IF G_DEBUG='Y' THEN
75 AHL_DEBUG_PUB.debug('UC: SET_ACTIVITY_DETAILS --> l_object_version_number='||l_object_version_number);
76 END IF;
77
78
79 l_object_details.operating_unit_id := NULL;
80 l_object_details.priority := NULL;
81
82 --
83 -- RUN mode
84 --
85
86
87 IF (funcmode = 'RUN') THEN
88
89 OPEN get_uc_header_det(l_object_id);
90 FETCH get_uc_header_det into l_uc_header_rec;
91 IF get_uc_header_det%NOTFOUND THEN
92 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
93 fnd_message.set_token('UC_HEADER_ID', l_object_id, false);
94 RAISE FND_API.G_EXC_ERROR;
95 END IF;
96 CLOSE get_uc_header_det;
97
98 --check if active uc status approval
99 IF (l_uc_header_rec.active_uc_status_code = 'APPROVAL_PENDING') THEN
100 l_object := 'UC_ACTST';
101 ELSE
102 l_object := 'UC';
103 END IF;
104
105 fnd_message.set_name('AHL', 'AHL_UC_NTF_FORWARD_SUBJECT');
106 fnd_message.set_token('UC_HEADER_ID', l_uc_header_rec.unit_config_header_id, false);
107 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
108 l_subject := fnd_message.get;
109
110 wf_engine.setitemattrtext
111 (
112 itemtype => itemtype
113 ,itemkey => itemkey
114 ,aname => 'FORWARD_SUBJECT'
115 ,avalue => l_subject
116 );
117
118 fnd_message.set_name('AHL', 'AHL_UC_NTF_APPROVAL_SUBJECT');
119 fnd_message.set_token('UC_HEADER_ID', l_uc_header_rec.unit_config_header_id, false);
120 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
121 l_subject := fnd_message.get;
122
123 wf_engine.setitemattrtext
124 (
125 itemtype => itemtype
126 ,itemkey => itemkey
127 ,aname => 'APPROVAL_SUBJECT'
128 ,avalue => l_subject
129 );
130 IF G_DEBUG='Y' THEN
131 AHL_DEBUG_PUB.debug('UC: SET_ACTIVITY_DETAILS -- APPROVAL_SUBJECT='||l_subject);
132 END IF;
133
134 fnd_message.set_name('AHL', 'AHL_UC_NTF_REJECT_SUBJECT');
135 fnd_message.set_token('UC_HEADER_ID', l_uc_header_rec.unit_config_header_id, false);
136 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
137 l_subject := fnd_message.get;
138
139 wf_engine.setitemattrtext
140 (
141 itemtype => itemtype
142 ,itemkey => itemkey
143 ,aname => 'REJECT_SUBJECT'
144 ,avalue => l_subject
145 );
146 IF G_DEBUG='Y' THEN
147 AHL_DEBUG_PUB.debug('UC: SET_ACTIVITY_DETAILS -- REJECT_SUBJECT='||l_subject);
148 END IF;
149
150 fnd_message.set_name('AHL', 'AHL_UC_NTF_APPROVED_SUBJECT');
151 fnd_message.set_token('UC_HEADER_ID', l_uc_header_rec.unit_config_header_id, false);
152 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
153 l_subject := fnd_message.get;
154
155 wf_engine.setitemattrtext
156 (
157 itemtype => itemtype
158 ,itemkey => itemkey
159 ,aname => 'APPROVED_SUBJECT'
160 ,avalue => l_subject
161 );
162 IF G_DEBUG='Y' THEN
163 AHL_DEBUG_PUB.debug('UC: SET_ACTIVITY_DETAILS -- APPROVED_SUBJECT='||l_subject);
164 END IF;
165
166 fnd_message.set_name('AHL', 'AHL_UC_NTF_FINAL_SUBJECT');
167 fnd_message.set_token('UC_HEADER_ID', l_uc_header_rec.unit_config_header_id, false);
168 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
169 l_subject := fnd_message.get;
170
171 wf_engine.setitemattrtext
172 (
173 itemtype => itemtype
174 ,itemkey => itemkey
175 ,aname => 'FINAL_SUBJECT'
176 ,avalue => l_subject
177 );
178 IF G_DEBUG='Y' THEN
179 AHL_DEBUG_PUB.debug('UC: SET_ACTIVITY_DETAILS -- FINAL_SUBJECT='||l_subject);
180 END IF;
181
182 fnd_message.set_name('AHL', 'AHL_UC_NTF_REMIND_SUBJECT');
183 fnd_message.set_token('UC_HEADER_ID', l_uc_header_rec.unit_config_header_id, false);
184 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
185 l_subject := fnd_message.get;
186
187 wf_engine.setitemattrtext
188 (
189 itemtype => itemtype
190 ,itemkey => itemkey
191 ,aname => 'REMIND_SUBJECT'
192 ,avalue => l_subject
193 );
194 IF G_DEBUG='Y' THEN
195 AHL_DEBUG_PUB.debug('UC: SET_ACTIVITY_DETAILS -- REMIND_SUBJECT='||l_subject);
196 END IF;
197
198 fnd_message.set_name('AHL', 'AHL_UC_NTF_ERROR_SUBJECT');
199 fnd_message.set_token('UC_HEADER_ID', l_uc_header_rec.unit_config_header_id, false);
200 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
201 l_subject := fnd_message.get;
202
203 wf_engine.setitemattrtext
204 (
205 itemtype => itemtype
206 ,itemkey => itemkey
207 ,aname => 'ERROR_SUBJECT'
208 ,avalue => l_subject
209 );
210 IF G_DEBUG='Y' THEN
211 AHL_DEBUG_PUB.debug('UC: SET_ACTIVITY_DETAILS -- ERROR_SUBJECT='||l_subject);
212 END IF;
213
214 AHL_GENERIC_APRV_PVT.GET_APPROVAL_DETAILS
215 (
216 p_object => l_object,
217 p_approval_type => l_approval_type,
218 p_object_details => l_object_details,
219 x_approval_rule_id => l_approval_rule_id,
220 x_approver_seq => l_approver_seq,
221 x_return_status => l_return_status
222 );
223
224
225 IF l_return_status = FND_API.g_ret_sts_success THEN
226
227 wf_engine.setitemattrnumber
228 (
229 itemtype => itemtype,
230 itemkey => itemkey,
231 aname => 'RULE_ID',
232 avalue => l_approval_rule_id
233 );
234 IF G_DEBUG='Y' THEN
235 AHL_DEBUG_PUB.debug('UC: SET_ACTIVITY_DETAILS -- RULE_ID='||l_approval_rule_id);
236 END IF;
237
238 wf_engine.setitemattrnumber
239 (
240 itemtype => itemtype,
241 itemkey => itemkey,
242 aname => 'APPROVER_SEQ',
243 avalue => l_approver_seq
244 );
245 IF G_DEBUG='Y' THEN
246 AHL_DEBUG_PUB.debug('UC: SET_ACTIVITY_DETAILS -- APPROVER_SEQ='||l_approver_seq);
247 AHL_DEBUG_PUB.debug('UC:End Set Actvity Details');
248 AHL_DEBUG_PUB.disable_debug;
249 END IF;
250
251 resultout := 'COMPLETE:SUCCESS';
252 RETURN;
253
254 ELSE
255 RAISE FND_API.G_EXC_ERROR;
256 END IF;
257 END IF;
258
259 --
260 -- CANCEL mode
261 --
262
263 IF (funcmode = 'CANCEL')
264 THEN
265 resultout := 'COMPLETE:';
266 IF G_DEBUG='Y' THEN
267 AHL_DEBUG_PUB.disable_debug;
268 END IF;
269
270 RETURN;
271 END IF;
272
273 --
274 -- TIMEOUT mode
275 --
276
277 IF (funcmode = 'TIMEOUT')
278 THEN
279 resultout := 'COMPLETE:';
280
281 IF G_DEBUG='Y' THEN
282 AHL_DEBUG_PUB.disable_debug;
283 END IF;
284
285 RETURN;
286 END IF;
287
288
289 EXCEPTION
290 WHEN FND_API.G_EXC_ERROR THEN
291 FND_MSG_PUB.Count_And_Get
292 (
293 p_encoded => FND_API.G_FALSE,
294 p_count => l_msg_count,
295 p_data => l_msg_data
296 );
297 AHL_GENERIC_APRV_PVT.handle_error
298 (
299 p_itemtype => itemtype ,
300 p_itemkey => itemkey ,
301 p_msg_count => l_msg_count,
302 p_msg_data => l_msg_data ,
303 p_attr_name => 'ERROR_MSG',
304 x_error_msg => l_error_msg
305 );
306 wf_core.context
307 (
308 G_PKG_NAME,
309 'SET_ACTIVITY_DETAILS',
310 itemtype,
311 itemkey,
312 actid,
313 funcmode,
314 l_error_msg
315 );
316 resultout := 'COMPLETE:ERROR';
317
318 WHEN OTHERS THEN
319 wf_core.context
320 (
321 G_PKG_NAME,
322 'SET_ACTIVITY_DETAILS',
323 itemtype,
324 itemkey,
325 actid,
326 'Unexpected Error!'
327 );
328 RAISE;
329
330 END SET_ACTIVITY_DETAILS;
331
332 -----------------------------
333 -- NTF_FORWARD_FYI
334 -----------------------------
335 PROCEDURE NTF_FORWARD_FYI
336 (
337 document_id IN VARCHAR2
338 ,display_type IN VARCHAR2
339 ,document IN OUT NOCOPY VARCHAR2
340 ,document_type IN OUT NOCOPY VARCHAR2
341 )
342 IS
343
344 l_hyphen_pos1 NUMBER;
345 l_object VARCHAR2(30);
346 l_item_type VARCHAR2(30);
347 l_item_key VARCHAR2(30);
348 l_approver VARCHAR2(30);
349 l_body VARCHAR2(3500);
350 l_object_id NUMBER;
351
352 l_msg_count NUMBER;
353 l_msg_data VARCHAR2(4000);
354 l_error_msg VARCHAR2(2000);
355
356 l_uc_header_rec get_uc_header_det%rowtype;
357
358 BEGIN
359
360 IF G_DEBUG='Y' THEN
361 AHL_DEBUG_PUB.ENABLE_DEBUG;
362 AHL_DEBUG_PUB.debug( 'UC:Start Notify Forward');
363 END IF;
364
365 document_type := 'text/plain';
366
367 -- parse document_id for the ':' dividing item type name from item key value
368 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
369 -- release 2.5 version of this demo
370
371 l_hyphen_pos1 := INSTR(document_id, ':');
372 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
373 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
374
375 l_object := wf_engine.getitemattrtext
376 (
377 itemtype => l_item_type
378 ,itemkey => l_item_key
379 ,aname => 'OBJECT_TYPE'
380 );
381
382 l_object_id := wf_engine.getitemattrNumber
383 (
384 itemtype => l_item_type
385 ,itemkey => l_item_key
386 ,aname => 'OBJECT_ID'
387 );
388
389 l_approver := wf_engine.getitemattrtext
390 (
391 itemtype => l_item_type
392 ,itemkey => l_item_key
393 ,aname => 'APPROVER'
394 );
395 IF G_DEBUG='Y' THEN
396 AHL_DEBUG_PUB.debug('UC: NTF_FORWARD_FYI -- l_approver='||l_approver);
397 END IF;
398
399 OPEN get_uc_header_det(l_object_id);
400 FETCH get_uc_header_det into l_uc_header_rec;
401
402 IF get_uc_header_det%NOTFOUND
403 THEN
404 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
405 fnd_message.set_token('UC_HEADER_ID', l_object_id, false);
406 RAISE FND_API.G_EXC_ERROR;
407 ELSE
408 fnd_message.set_name('AHL', 'AHL_UC_NTF_FWD_FYI_FWD');
409 fnd_message.set_token('UC_HEADER_ID',l_uc_header_rec.unit_config_header_id ,false);
410 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
411 fnd_message.set_token('APPR_NAME',l_approver, false);
412 l_body := fnd_message.get;
413 END IF;
414 CLOSE get_uc_header_det;
415
416 document := document || l_body;
417 IF G_DEBUG='Y' THEN
418 AHL_DEBUG_PUB.debug('UC: NTF_FORWARD_FYI -- document='||document);
419 END IF;
420
421
422 IF G_DEBUG='Y' THEN
423 AHL_DEBUG_PUB.debug( 'UC:END Notify Forward, l_object_id =' || l_object_id );
424 AHL_DEBUG_PUB.DISABLE_DEBUG;
425 END IF;
426
427 COMMIT;
428 RETURN;
429
430 EXCEPTION
431 WHEN FND_API.G_EXC_ERROR THEN
432 FND_MSG_PUB.Count_And_Get
433 (
434 p_encoded => FND_API.G_FALSE,
435 p_count => l_msg_count,
436 p_data => l_msg_data
437 );
438 AHL_GENERIC_APRV_PVT.Handle_Error
439 (
440 p_itemtype => l_item_type ,
441 p_itemkey => l_item_key ,
442 p_msg_count => l_msg_count,
443 p_msg_data => l_msg_data ,
444 p_attr_name => 'ERROR_MSG',
445 x_error_msg => l_error_msg
446 ) ;
447 wf_core.context
448 (
449 G_PKG_NAME,
450 'NTF_FORWARD_FYI',
451 l_item_type,
452 l_item_key,
453 l_error_msg
454 );
455 RAISE;
456
457 WHEN OTHERS THEN
458 wf_core.context(
459 G_PKG_NAME,
460 'NTF_FORWARD_FYI',
461 l_item_type,
462 l_item_key
463 );
464 RAISE;
465
466 END NTF_FORWARD_FYI;
467
468 -----------------------------
469 -- NTF_APPROVED_FYI
470 -----------------------------
471 PROCEDURE NTF_APPROVED_FYI
472 (
473 document_id IN VARCHAR2
474 ,display_type IN VARCHAR2
475 ,document IN OUT NOCOPY VARCHAR2
476 ,document_type IN OUT NOCOPY VARCHAR2
477 )
478 IS
479
480 l_hyphen_pos1 NUMBER;
481 l_object VARCHAR2(30);
482 l_item_type VARCHAR2(30);
483 l_item_key VARCHAR2(30);
484 l_approver VARCHAR2(30);
485 l_body VARCHAR2(3500);
486 l_object_id NUMBER;
487
488 l_msg_count NUMBER;
489 l_msg_data VARCHAR2(4000);
490 l_error_msg VARCHAR2(2000);
491
492 l_uc_header_rec get_uc_header_det%rowtype;
493
494 BEGIN
495
496 IF G_DEBUG='Y' THEN
497 AHL_DEBUG_PUB.ENABLE_DEBUG;
498 AHL_DEBUG_PUB.debug( 'UC:Start Notify Approved FYI');
499 END IF;
500
501 document_type := 'text/plain';
502
503 -- parse document_id for the ':' dividing item type name from item key value
504 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
505 -- release 2.5 version of this demo
506
507 l_hyphen_pos1 := INSTR(document_id, ':');
508 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
509 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
510
511 l_object := wf_engine.getitemattrtext
512 (
513 itemtype => l_item_type
514 ,itemkey => l_item_key
515 ,aname => 'OBJECT_TYPE'
516 );
517
518 l_object_id := wf_engine.getitemattrNumber
519 (
520 itemtype => l_item_type
521 ,itemkey => l_item_key
522 ,aname => 'OBJECT_ID'
523 );
524
525 l_approver := wf_engine.getitemattrtext
526 (
527 itemtype => l_item_type
528 ,itemkey => l_item_key
529 ,aname => 'APPROVER'
530 );
531 IF G_DEBUG='Y' THEN
532 AHL_DEBUG_PUB.debug('UC: NTF_APPROVED_FYI -- l_approver='||l_approver);
533 END IF;
534
535 OPEN get_uc_header_det(l_object_id);
536 FETCH get_uc_header_det into l_uc_header_rec;
537
538 IF get_uc_header_det%NOTFOUND
539 THEN
540 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
541 fnd_message.set_token('UC_HEADER_ID', l_object_id, false);
542 RAISE FND_API.G_EXC_ERROR;
543 ELSE
544 fnd_message.set_name('AHL', 'AHL_UC_NTF_FWD_FYI_APPRVD');
545 fnd_message.set_token('UC_HEADER_ID',l_uc_header_rec.unit_config_header_id ,false);
546 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
547 fnd_message.set_token('APPR_NAME',l_approver, false);
548 l_body := fnd_message.get;
549 END IF;
550 CLOSE get_uc_header_det;
551
552 document := document || l_body;
553 IF G_DEBUG='Y' THEN
554 AHL_DEBUG_PUB.debug('UC: NTF_APPROVED_FYI -- document='||document);
555 END IF;
556
557 IF G_DEBUG='Y' THEN
558 AHL_DEBUG_PUB.debug( 'UC:END Notify Approved FYI, l_object_id =' || l_object_id );
559 AHL_DEBUG_PUB.DISABLE_DEBUG;
560 END IF;
561
562 RETURN;
563
564 EXCEPTION
565 WHEN FND_API.G_EXC_ERROR THEN
566 FND_MSG_PUB.Count_And_Get
567 (
568 p_encoded => FND_API.G_FALSE,
569 p_count => l_msg_count,
570 p_data => l_msg_data
571 );
572 AHL_GENERIC_APRV_PVT.Handle_Error
573 (
574 p_itemtype => l_item_type ,
575 p_itemkey => l_item_key ,
576 p_msg_count => l_msg_count,
577 p_msg_data => l_msg_data ,
578 p_attr_name => 'ERROR_MSG',
579 x_error_msg => l_error_msg
580 ) ;
581 wf_core.context
582 (
583 G_PKG_NAME,
584 'NTF_APPROVED_API',
585 l_item_type,
586 l_item_key,
587 l_error_msg
588 );
589 RAISE;
590 WHEN OTHERS THEN
591 wf_core.context
592 (
593 G_PKG_NAME,
594 'NTF_APPROVED_API',
595 l_item_type,
596 l_item_key
597 );
598 RAISE;
599
600 END NTF_APPROVED_FYI;
601
602 --------------------------------
603 -- NTF_FINAL_APPROVAL_FYI
604 --------------------------------
605 PROCEDURE NTF_FINAL_APPROVAL_FYI
606 (
607 document_id IN VARCHAR2
608 ,display_type IN VARCHAR2
609 ,document IN OUT NOCOPY VARCHAR2
610 ,document_type IN OUT NOCOPY VARCHAR2
611 )
612 IS
613
614 l_hyphen_pos1 NUMBER;
615 l_object VARCHAR2(30);
616 l_item_type VARCHAR2(30);
617 l_item_key VARCHAR2(30);
618 l_body VARCHAR2(3500);
619 l_object_id NUMBER;
620 l_approver VARCHAR2(30);
621 l_msg_count NUMBER;
622 l_msg_data VARCHAR2(4000);
623 l_error_msg VARCHAR2(2000);
624
625 l_uc_header_rec get_uc_header_det%rowtype;
626
627 BEGIN
628
629 IF G_DEBUG='Y' THEN
630 AHL_DEBUG_PUB.ENABLE_DEBUG;
631 AHL_DEBUG_PUB.debug( 'Start NTF Final approval');
632 END IF;
633
634 document_type := 'text/plain';
635
636 -- parse document_id for the ':' dividing item type name from item key value
637 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
638 -- release 2.5 version of this demo
639
640 l_hyphen_pos1 := INSTR(document_id, ':');
641 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
642 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
643
644 l_object := wf_engine.getitemattrtext
645 (
646 itemtype => l_item_type
647 ,itemkey => l_item_key
648 ,aname => 'OBJECT_TYPE'
649 );
650
651 l_object_id := wf_engine.getitemattrNumber
652 (
653 itemtype => l_item_type
654 ,itemkey => l_item_key
655 ,aname => 'OBJECT_ID'
656 );
657
658 l_approver := wf_engine.getitemattrtext
659 (
660 itemtype => l_item_type
661 ,itemkey => l_item_key
662 ,aname => 'APPROVER'
663 );
664 IF G_DEBUG='Y' THEN
665 AHL_DEBUG_PUB.debug('UC: NTF_FINAL_APPROVAL_FYI -- l_approver='||l_approver);
666 END IF;
667
668 OPEN get_uc_header_det(l_object_id);
669 FETCH get_uc_header_det into l_uc_header_rec;
670
671 IF get_uc_header_det%NOTFOUND
672 THEN
673 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
674 fnd_message.set_token('UC_HEADER_ID', l_object_id, false);
675 RAISE FND_API.G_EXC_ERROR;
676 ELSE
677 fnd_message.set_name('AHL', 'AHL_UC_NTF_FWD_FYI_FINAL');
678 fnd_message.set_token('UC_HEADER_ID',l_uc_header_rec.unit_config_header_id ,false);
679 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
680 l_body := fnd_message.get;
681 END IF;
682 CLOSE get_uc_header_det;
683
684 document := document || l_body;
685 IF G_DEBUG='Y' THEN
686 AHL_DEBUG_PUB.debug('UC: NTF_FINAL_APPROVAL_FYI -- document='||document);
687 END IF;
688
689 IF G_DEBUG='Y' THEN
690 AHL_DEBUG_PUB.debug( 'END UC NTF Final approval , l_object_id =' || l_object_id );
691 AHL_DEBUG_PUB.DISABLE_DEBUG;
692 END IF;
693
694 RETURN;
695
696
697 EXCEPTION
698 WHEN FND_API.G_EXC_ERROR THEN
699 FND_MSG_PUB.Count_And_Get
700 (
701 p_encoded => FND_API.G_FALSE,
702 p_count => l_msg_count,
703 p_data => l_msg_data
704 );
705 AHL_GENERIC_APRV_PVT.Handle_Error
706 (
707 p_itemtype => l_item_type ,
708 p_itemkey => l_item_key ,
709 p_msg_count => l_msg_count,
710 p_msg_data => l_msg_data ,
711 p_attr_name => 'ERROR_MSG',
712 x_error_msg => l_error_msg
713 ) ;
714 wf_core.context
715 (
716 G_PKG_NAME,
717 'NTF_FINAL_APPROVAL_FYI',
718 l_item_type,
719 l_item_key,
720 l_error_msg
721 );
722 RAISE;
723 WHEN OTHERS THEN
724 wf_core.context
725 (
726 G_PKG_NAME,
727 'NTF_FINAL_APPROVAL_FYI',
728 l_item_type,
729 l_item_key
730 );
731 RAISE;
732
733 END NTF_FINAL_APPROVAL_FYI;
734
735 ---------------------------
736 -- NTF_REJECTED_FYI
737 ---------------------------
738 PROCEDURE NTF_REJECTED_FYI
739 (
740 document_id IN VARCHAR2
741 ,display_type IN VARCHAR2
742 ,document IN OUT NOCOPY VARCHAR2
743 ,document_type IN OUT NOCOPY VARCHAR2
744 )
745 IS
746
747 l_hyphen_pos1 NUMBER;
748 l_object VARCHAR2(30);
749 l_item_type VARCHAR2(30);
750 l_item_key VARCHAR2(30);
751 l_approver VARCHAR2(30);
752 l_body VARCHAR2(3500);
753 l_object_id NUMBER;
754 l_msg_count NUMBER;
755 l_msg_data VARCHAR2(4000);
756 l_error_msg VARCHAR2(2000);
757
758 l_uc_header_rec get_uc_header_det%rowtype;
759
760 BEGIN
761
762 IF G_DEBUG='Y' THEN
763 AHL_DEBUG_PUB.ENABLE_DEBUG;
764 AHL_DEBUG_PUB.debug( 'UC:Start Notify Rejected');
765 END IF;
766
767 document_type := 'text/plain';
768
769 -- parse document_id for the ':' dividing item type name from item key value
770 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
771 -- release 2.5 version of this demo
772
773 l_hyphen_pos1 := INSTR(document_id, ':');
774 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
775 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
776
777 l_object := wf_engine.getitemattrtext
778 (
779 itemtype => l_item_type
780 ,itemkey => l_item_key
781 ,aname => 'OBJECT_TYPE'
782 );
783
784 l_object_id := wf_engine.getitemattrNumber
785 (
786 itemtype => l_item_type
787 ,itemkey => l_item_key
788 ,aname => 'OBJECT_ID'
789 );
790
791 l_approver := wf_engine.getitemattrtext
792 (
793 itemtype => l_item_type
794 ,itemkey => l_item_key
795 ,aname => 'APPROVER'
796 );
797 IF G_DEBUG='Y' THEN
798 AHL_DEBUG_PUB.debug('UC:NTF_REJECTED_FYI -- l_approver='||l_approver);
799 END IF;
800
801
802 OPEN get_uc_header_det(l_object_id);
803 FETCH get_uc_header_det into l_uc_header_rec;
804
805 IF get_uc_header_det%NOTFOUND THEN
806 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
807 fnd_message.set_token('UC_HEADER_ID', l_object_id, false);
808 RAISE FND_API.G_EXC_ERROR;
809 ELSE
810 fnd_message.set_name('AHL', 'AHL_UC_NTF_FWD_FYI_RJCT');
811 fnd_message.set_token('UC_HEADER_ID',l_uc_header_rec.unit_config_header_id ,false);
812 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
813 fnd_message.set_token('APPR_NAME',l_approver, false);
814 l_body := fnd_message.get;
815 END IF;
816 CLOSE get_uc_header_det;
817
818 document := document || l_body;
819 IF G_DEBUG='Y' THEN
820 AHL_DEBUG_PUB.debug('UC:NTF_REJECTED_FYI -- document='||document);
821 END IF;
822
823
824 IF G_DEBUG='Y' THEN
825 AHL_DEBUG_PUB.debug( 'UC:End Notify Rejected, l_object_id =' || l_object_id );
826 AHL_DEBUG_PUB.DISABLE_DEBUG;
827 END IF;
828
829 RETURN;
830
831 EXCEPTION
832 WHEN FND_API.G_EXC_ERROR THEN
833 FND_MSG_PUB.Count_And_Get
834 (
835 p_encoded => FND_API.G_FALSE,
836 p_count => l_msg_count,
837 p_data => l_msg_data
838 );
839 AHL_GENERIC_APRV_PVT.Handle_Error
840 (
841 p_itemtype => l_item_type ,
842 p_itemkey => l_item_key ,
843 p_msg_count => l_msg_count,
844 p_msg_data => l_msg_data ,
845 p_attr_name => 'ERROR_MSG',
846 x_error_msg => l_error_msg
847 ) ;
848 wf_core.context
849 (
850 G_PKG_NAME,
851 'NTF_REJECTED_FYI',
852 l_item_type,
853 l_item_key,
854 l_error_msg
855 );
856 RAISE;
857 WHEN OTHERS THEN
858 wf_core.context(
859 G_PKG_NAME,
860 'NTF_REJECTED_FYI',
861 l_item_type,
862 l_item_key
863 );
864 RAISE;
865
866 END NTF_REJECTED_FYI;
867
868 ------------------------
869 -- NTF_APPROVAL
870 ------------------------
871 PROCEDURE NTF_APPROVAL
872 (
873 document_id IN VARCHAR2
874 ,display_type IN VARCHAR2
875 ,document IN OUT NOCOPY VARCHAR2
876 ,document_type IN OUT NOCOPY VARCHAR2
877 )
878 IS
879
880 l_hyphen_pos1 NUMBER;
881 l_object VARCHAR2(30);
882 l_item_type VARCHAR2(30);
883 l_item_key VARCHAR2(30);
884 l_requester VARCHAR2(30);
885 l_requester_note VARCHAR2(4000);
886 l_body VARCHAR2(5000);
887 l_object_id NUMBER;
888 l_msg_count NUMBER;
889 l_msg_data VARCHAR2(4000);
890 l_error_msg VARCHAR2(2000);
891
892 l_uc_header_rec get_uc_header_det%rowtype;
893
894 BEGIN
895
896 IF G_DEBUG='Y' THEN
897 AHL_DEBUG_PUB.ENABLE_DEBUG;
898 AHL_DEBUG_PUB.debug( 'UC: Start Nty_approval');
899 END IF;
900
901 document_type := 'text/plain';
902
903 -- parse document_id for the ':' dividing item type name from item key value
904 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
905 -- release 2.5 version of this demo
906
907 l_hyphen_pos1 := INSTR(document_id, ':');
908 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
909 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
910
911 l_object := wf_engine.getitemattrtext
912 (
913 itemtype => l_item_type
914 ,itemkey => l_item_key
915 ,aname => 'OBJECT_TYPE'
916 );
917
918 l_object_id := wf_engine.getitemattrNumber
919 (
920 itemtype => l_item_type
921 ,itemkey => l_item_key
922 ,aname => 'OBJECT_ID'
923 );
924
925 l_requester := wf_engine.getitemattrtext
926 (
927 itemtype => l_item_type
928 ,itemkey => l_item_key
929 ,aname => 'REQUESTER'
930 );
931 IF G_DEBUG='Y' THEN
932 AHL_DEBUG_PUB.debug('UC: NTF_APPROVAL --> l_requester='||l_requester);
933 END IF;
934 l_requester_note := wf_engine.getitemattrtext
935 (
936 itemtype => l_item_type
937 ,itemkey => l_item_key
938 ,aname => 'REQUESTER_NOTE'
939 );
940
941
942 OPEN get_uc_header_det(l_object_id);
943 FETCH get_uc_header_det into l_uc_header_rec;
944
945 IF get_uc_header_det%NOTFOUND
946 THEN
947 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
948 fnd_message.set_token('UC_HEADER_ID', l_object_id, false);
949 RAISE FND_API.G_EXC_ERROR;
950 ELSE
951 fnd_message.set_name('AHL', 'AHL_UC_NTF_APPROVAL');
952 fnd_message.set_token('REQUESTER',l_requester, false);
953 fnd_message.set_token('UC_HEADER_ID',l_uc_header_rec.unit_config_header_id ,false);
954 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
955 fnd_message.set_token('NOTE',l_requester_note, false);
956 l_body := fnd_message.get;
957 END IF;
958 CLOSE get_uc_header_det;
959
960 document := document || l_body;
961 IF G_DEBUG='Y' THEN
962 AHL_DEBUG_PUB.debug('UC: NTF_APPROVAL -- document='||document);
963 END IF;
964
965 IF G_DEBUG='Y' THEN
966 AHL_DEBUG_PUB.debug( 'UC: END Nty_approval, l_object_id =' || l_object_id );
967 AHL_DEBUG_PUB.DISABLE_DEBUG;
968 END IF;
969
970 RETURN;
971
972 EXCEPTION
973 WHEN FND_API.G_EXC_ERROR THEN
974 FND_MSG_PUB.Count_And_Get
975 (
976 p_encoded => FND_API.G_FALSE,
977 p_count => l_msg_count,
978 p_data => l_msg_data
979 );
980 AHL_GENERIC_APRV_PVT.Handle_Error
981 (
982 p_itemtype => l_item_type ,
983 p_itemkey => l_item_key ,
984 p_msg_count => l_msg_count,
985 p_msg_data => l_msg_data ,
986 p_attr_name => 'ERROR_MSG',
987 x_error_msg => l_error_msg
988 ) ;
989 wf_core.context
990 (
991 G_PKG_NAME,
992 'NTF_APPROVAL',
993 l_item_type,
994 l_item_key,
995 l_error_msg
996 );
997 RAISE;
998 WHEN OTHERS THEN
999 wf_core.context(
1000 G_PKG_NAME,
1001 'NTF_APPROVAL',
1002 l_item_type,
1003 l_item_key
1004 );
1005 RAISE;
1006
1007 END NTF_APPROVAL;
1008
1009 -------------------------------
1010 -- NTF_APPROVAL_REMINDER
1011 -------------------------------
1012 PROCEDURE NTF_APPROVAL_REMINDER
1013 (
1014 document_id IN VARCHAR2
1015 ,display_type IN VARCHAR2
1016 ,document IN OUT NOCOPY VARCHAR2
1017 ,document_type IN OUT NOCOPY VARCHAR2
1018 )
1019 IS
1020
1021 l_hyphen_pos1 NUMBER;
1022 l_object VARCHAR2(30);
1023 l_item_type VARCHAR2(30);
1024 l_item_key VARCHAR2(30);
1025 l_requester VARCHAR2(30);
1026 l_requester_note VARCHAR2(4000);
1027 l_body VARCHAR2(5000);
1028 l_object_id NUMBER;
1029 l_msg_count NUMBER;
1030 l_msg_data VARCHAR2(4000);
1031 l_error_msg VARCHAR2(2000);
1032
1033 l_uc_header_rec get_uc_header_det%rowtype;
1034
1035 BEGIN
1036
1037 IF G_DEBUG='Y' THEN
1038 AHL_DEBUG_PUB.ENABLE_DEBUG;
1039 AHL_DEBUG_PUB.debug( 'Start ntfy Apprvl remainder');
1040 END IF;
1041
1042 document_type := 'text/plain';
1043
1044 -- parse document_id for the ':' dividing item type name from item key value
1045 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1046 -- release 2.5 version of this demo
1047
1048 l_hyphen_pos1 := INSTR(document_id, ':');
1049 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1050 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1051
1052 l_object := wf_engine.getitemattrtext
1053 (
1054 itemtype => l_item_type
1055 ,itemkey => l_item_key
1056 ,aname => 'OBJECT_TYPE'
1057 );
1058
1059 l_object_id := wf_engine.getitemattrNumber
1060 (
1061 itemtype => l_item_type
1062 ,itemkey => l_item_key
1063 ,aname => 'OBJECT_ID'
1064 );
1065
1066 l_requester := wf_engine.getitemattrtext
1067 (
1068 itemtype => l_item_type
1069 ,itemkey => l_item_key
1070 ,aname => 'REQUESTER'
1071 );
1072 IF G_DEBUG='Y' THEN
1073 AHL_DEBUG_PUB.debug('UC: NTF_APPROVAL_REMINDER -- l_requester='||l_requester);
1074 END IF;
1075
1076 l_requester_note := wf_engine.getitemattrtext
1077 (
1078 itemtype => l_item_type
1079 ,itemkey => l_item_key
1080 ,aname => 'REQUESTER_NOTE'
1081 );
1082
1083
1084 OPEN get_uc_header_det(l_object_id);
1085 FETCH get_uc_header_det into l_uc_header_rec;
1086
1087 IF get_uc_header_det%NOTFOUND
1088 THEN
1089 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
1090 fnd_message.set_token('UC_HEADER_ID', l_object_id, false);
1091 RAISE FND_API.G_EXC_ERROR;
1092 ELSE
1093 fnd_message.set_name('AHL', 'AHL_UC_NTF_REMIND');
1094 fnd_message.set_token('REQUESTER',l_requester, false);
1095 fnd_message.set_token('UC_HEADER_ID',l_uc_header_rec.unit_config_header_id ,false);
1096 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
1097 fnd_message.set_token('NOTE',l_requester_note, false);
1098 l_body := fnd_message.get;
1099 END IF;
1100 CLOSE get_uc_header_det;
1101
1102 document := document || l_body;
1103 IF G_DEBUG='Y' THEN
1104 AHL_DEBUG_PUB.debug('UC: NTF_APPROVAL_REMINDER -- document='||document);
1105 END IF;
1106
1107 IF G_DEBUG='Y' THEN
1108 AHL_DEBUG_PUB.debug( 'END ntfy Apprvl remainder, l_object_id =' || l_object_id );
1109 AHL_DEBUG_PUB.DISABLE_DEBUG;
1110 END IF;
1111
1112 RETURN;
1113
1114 EXCEPTION
1115 WHEN FND_API.G_EXC_ERROR THEN
1116 FND_MSG_PUB.Count_And_Get
1117 (
1118 p_encoded => FND_API.G_FALSE,
1119 p_count => l_msg_count,
1120 p_data => l_msg_data
1121 );
1122 AHL_GENERIC_APRV_PVT.Handle_Error
1123 (
1124 p_itemtype => l_item_type ,
1125 p_itemkey => l_item_key ,
1126 p_msg_count => l_msg_count,
1127 p_msg_data => l_msg_data ,
1128 p_attr_name => 'ERROR_MSG',
1129 x_error_msg => l_error_msg
1130 ) ;
1131 wf_core.context
1132 (
1133 G_PKG_NAME,
1134 'NTF_APPROVAL_REMINDER',
1135 l_item_type,
1136 l_item_key,
1137 l_error_msg
1138 );
1139 RAISE;
1140 WHEN OTHERS THEN
1141 wf_core.context(
1142 G_PKG_NAME,
1143 'NTF_APPROVAL_REMINDER',
1144 l_item_type,
1145 l_item_key
1146 );
1147 RAISE;
1148
1149 END NTF_APPROVAL_REMINDER;
1150
1151 -------------------------
1152 -- NTF_ERROR_ACT
1153 --------------------------
1154 PROCEDURE NTF_ERROR_ACT
1155 (
1156 document_id IN VARCHAR2
1157 ,display_type IN VARCHAR2
1158 ,document IN OUT NOCOPY VARCHAR2
1159 ,document_type IN OUT NOCOPY VARCHAR2
1160 )
1161 IS
1162
1163 l_hyphen_pos1 NUMBER;
1164 l_object VARCHAR2(30);
1165 l_item_type VARCHAR2(30);
1166 l_item_key VARCHAR2(30);
1167 l_body VARCHAR2(3500);
1168 l_object_id NUMBER;
1169 l_error_msg VARCHAR2(4000);
1170 l_msg_count NUMBER;
1171 l_msg_data VARCHAR2(4000);
1172
1173 l_uc_header_rec get_uc_header_det%rowtype;
1174
1175 BEGIN
1176
1177 IF G_DEBUG='Y' THEN
1178 AHL_DEBUG_PUB.ENABLE_DEBUG;
1179 AHL_DEBUG_PUB.debug( 'Start Ntfy error');
1180 END IF;
1181
1182 document_type := 'text/plain';
1183
1184 -- parse document_id for the ':' dividing item type name from item key value
1185 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1186 -- release 2.5 version of this demo
1187
1188 l_hyphen_pos1 := INSTR(document_id, ':');
1189 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1190 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1191
1192 l_object := wf_engine.getitemattrtext
1193 (
1194 itemtype => l_item_type
1195 ,itemkey => l_item_key
1196 ,aname => 'OBJECT_TYPE'
1197 );
1198
1199 l_object_id := wf_engine.getitemattrNumber
1200 (
1201 itemtype => l_item_type
1202 ,itemkey => l_item_key
1203 ,aname => 'OBJECT_ID'
1204 );
1205
1206 l_error_msg := wf_engine.getitemattrText
1207 (
1208 itemtype => l_item_type
1209 ,itemkey => l_item_key
1210 ,aname => 'ERROR_MSG'
1211 );
1212 IF G_DEBUG='Y' THEN
1213 AHL_DEBUG_PUB.debug('UC: NTF_ERROR_ACT -- l_error_msg='||l_error_msg);
1214 END IF;
1215
1216 OPEN get_uc_header_det(l_object_id);
1217 FETCH get_uc_header_det into l_uc_header_rec;
1218
1219 IF get_uc_header_det%NOTFOUND
1220 THEN
1221 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
1222 fnd_message.set_token('UC_HEADER_ID', l_object_id, false);
1223 RAISE FND_API.G_EXC_ERROR;
1224 ELSE
1225 fnd_message.set_name('AHL', 'AHL_UC_NTF_ERROR_ACT');
1226 fnd_message.set_token('UC_HEADER_ID',l_uc_header_rec.unit_config_header_id ,false);
1227 fnd_message.set_token('NAME',l_uc_header_rec.name, false);
1228 fnd_message.set_token('ERR_MSG',l_error_msg, false);
1229 l_body := fnd_message.get;
1230 END IF;
1231 CLOSE get_uc_header_det;
1232
1233 document := document || l_body;
1234 IF G_DEBUG='Y' THEN
1235 AHL_DEBUG_PUB.debug('UC: NTF_ERROR_ACT -- document='||document);
1236 END IF;
1237
1238 IF G_DEBUG='Y' THEN
1239 AHL_DEBUG_PUB.debug( 'END UC Ntfy error , l_object_id =' || l_object_id );
1240 AHL_DEBUG_PUB.DISABLE_DEBUG;
1241 END IF;
1242
1243 RETURN;
1244
1245 EXCEPTION
1246 WHEN FND_API.G_EXC_ERROR THEN
1247 FND_MSG_PUB.Count_And_Get
1248 (
1249 p_encoded => FND_API.G_FALSE,
1250 p_count => l_msg_count,
1251 p_data => l_msg_data
1252 );
1253 AHL_GENERIC_APRV_PVT.Handle_Error
1254 (
1255 p_itemtype => l_item_type ,
1256 p_itemkey => l_item_key ,
1257 p_msg_count => l_msg_count,
1258 p_msg_data => l_msg_data ,
1259 p_attr_name => 'ERROR_MSG',
1260 x_error_msg => l_error_msg
1261 ) ;
1262 wf_core.context
1263 (
1264 G_PKG_NAME,
1265 'NTF_ERROR_ACT',
1266 l_item_type,
1267 l_item_key,
1268 l_error_msg
1269 );
1270 RAISE;
1271 WHEN OTHERS THEN
1272 wf_core.context
1273 (
1274 G_PKG_NAME,
1275 'NTF_ERROR_ACT',
1276 l_item_type,
1277 l_item_key
1278 );
1279 RAISE;
1280
1281 END NTF_ERROR_ACT;
1282
1283 ------------------------
1284 -- UPDATE_STATUS
1285 ------------------------
1286 PROCEDURE UPDATE_STATUS
1287 (
1288 itemtype IN VARCHAR2
1289 ,itemkey IN VARCHAR2
1290 ,actid IN NUMBER
1291 ,funcmode IN VARCHAR2
1292 ,resultout OUT NOCOPY VARCHAR2
1293 )
1294 IS
1295
1296
1297 CURSOR check_uc_ovn(c_uc_header_id in number, c_object_version_number in number)
1298 IS
1299 SELECT unit_config_header_id, name, unit_config_status_code, active_uc_status_code
1300 FROM ahl_unit_config_headers
1301 WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1302 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1303 AND unit_config_header_id = c_uc_header_id
1304 AND object_version_number = c_object_version_number;
1305
1306
1307 l_uc_header_rec get_uc_header_det%ROWTYPE;
1308 l_check_uc_ovn check_uc_ovn%ROWTYPE;
1309
1310 l_error_msg VARCHAR2(4000);
1311 l_object_version_number NUMBER;
1312 l_object_id NUMBER;
1313
1314 l_approval_status VARCHAR2(30);
1315 l_original_status VARCHAR2(30);
1316
1317 l_return_status VARCHAR2(1);
1318 l_msg_count NUMBER;
1319 l_msg_data VARCHAR2(4000);
1320
1321 BEGIN
1322
1323 IF G_DEBUG='Y' THEN
1324 AHL_DEBUG_PUB.ENABLE_DEBUG;
1325 AHL_DEBUG_PUB.debug( 'UC:Start Update Status API');
1326 END IF;
1327
1328 --
1329 -- RUN Mode
1330 --
1331 IF (funcmode = 'RUN') THEN
1332
1333 l_approval_status := wf_engine.getitemattrtext
1334 (
1335 itemtype => itemtype,
1336 itemkey => itemkey,
1337 aname => 'UPDATE_GEN_STATUS'
1338 );
1339
1340
1341 IF G_DEBUG='Y' THEN
1342 AHL_DEBUG_PUB.debug('UC: UPDATE_STATUS -- l_approval_status='||l_approval_status);
1343 END IF;
1344
1345 /* l_original_status := wf_engine.getitemattrtext
1346 (
1347 itemtype => itemtype,
1348 itemkey => itemkey,
1349 aname => 'ORIG_STATUS_ID'
1350 );
1351 IF G_DEBUG='Y' THEN
1352 AHL_DEBUG_PUB.debug('UC: UPDATE_STATUS -- l_original_status='||l_original_status);
1353 END IF;
1354 */
1355 l_object_version_number := wf_engine.getitemattrnumber
1356 (
1357 itemtype => itemtype,
1358 itemkey => itemkey,
1359 aname => 'OBJECT_VER'
1360 );
1361
1362 IF G_DEBUG='Y' THEN
1363 AHL_DEBUG_PUB.debug('UC: UPDATE_STATUS --> l_object_version_number='||l_object_version_number);
1364 END IF;
1365
1366 l_object_id := wf_engine.getitemattrnumber
1367 (
1368 itemtype => itemtype,
1369 itemkey => itemkey,
1370 aname => 'OBJECT_ID'
1371 );
1372
1373 IF G_DEBUG='Y' THEN
1374 AHL_DEBUG_PUB.debug('UC: UPDATE_STATUS --> l_object_id='||l_object_id);
1375 END IF;
1376
1377 OPEN get_uc_header_det(l_object_id);
1378 FETCH get_uc_header_det into l_uc_header_rec;
1379
1380 IF get_uc_header_det%NOTFOUND
1381 THEN
1382 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
1383 fnd_message.set_token('UC_HEADER_ID', l_object_id, false);
1384 RAISE FND_API.G_EXC_ERROR;
1385 END IF;
1386 CLOSE get_uc_header_det;
1387
1388
1389 OPEN check_uc_ovn(l_object_id, l_object_version_number);
1390 FETCH check_uc_ovn into l_check_uc_ovn;
1391
1392 IF check_uc_ovn%NOTFOUND THEN
1393
1394 fnd_message.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
1395
1396 IF G_DEBUG='Y' THEN
1397 AHL_DEBUG_PUB.debug('UC: UPDATE_STATUS check_uc_ovn --> fnd_message='||fnd_message.get );
1398 END IF;
1399
1400 RAISE FND_API.G_EXC_ERROR;
1401 END IF;
1402 CLOSE check_uc_ovn;
1403
1404 AHL_GENERIC_APRV_PVT.Handle_Error
1405 (
1406 p_itemtype => itemtype ,
1407 p_itemkey => itemkey ,
1408 p_msg_count => l_msg_count,
1409 p_msg_data => l_msg_data ,
1410 p_attr_name => 'ERROR_MSG',
1411 x_error_msg => l_error_msg
1412 );
1413
1414 IF G_DEBUG='Y' THEN
1415 AHL_DEBUG_PUB.debug('UC:AHL_UC_WF_APPR_PVT.update_status -->l_msg_count...... ' || l_msg_count);
1416 AHL_DEBUG_PUB.debug('UC:AHL_UC_WF_APPR_PVT.update_status -->l_error_msg...... ' || l_error_msg);
1417 AHL_DEBUG_PUB.debug('UC:AHL_UC_WF_APPR_PVT.update_status -->l_msg_data...... ' || l_msg_data);
1418 AHL_DEBUG_PUB.debug('UC:AHL_UC_WF_APPR_PVT.update_status --> Before call to complete_uc_approval ');
1419 END IF;
1420
1421 --CALL AHL_UC_APPROVALS_PVT.complete_uc_approvals
1422 AHL_UC_APPROVALS_PVT.complete_uc_approval(
1423 p_api_version => 1.0,
1424 p_init_msg_list => FND_API.G_TRUE,
1425 p_commit => FND_API.G_TRUE,
1426 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1427 p_uc_header_id => l_object_id,
1428 p_object_version_number => l_object_version_number,
1429 p_approval_status => l_approval_status,
1430 x_return_status => l_return_status,
1431 x_msg_count => l_msg_count,
1432 x_msg_data => l_msg_data
1433 );
1434
1435
1436 IF G_DEBUG='Y' THEN
1437 AHL_DEBUG_PUB.debug('SQLERRM' || SQLERRM );
1438 AHL_DEBUG_PUB.debug('l_return_status' || l_return_status);
1439 AHL_DEBUG_PUB.debug('l_msg_count' || l_msg_count);
1440 AHL_DEBUG_PUB.debug('l_msg_data' || l_msg_data);
1441 AHL_DEBUG_PUB.debug('UC:AHL_UC_WF_APPR_PVT.update_status -- Completed call to complete_uc_approval ');
1442 END IF;
1443
1444 IF G_DEBUG='Y' THEN
1445 AHL_DEBUG_PUB.debug( 'UC:End Update Status API, l_object_id =' || l_object_id );
1446 AHL_DEBUG_PUB.DISABLE_DEBUG;
1447 END IF;
1448
1449 resultout := 'COMPLETE:SUCCESS';
1450 RETURN;
1451 END IF;
1452
1453 --
1454 -- CANCEL mode
1455 --
1456 IF (funcmode = 'CANCEL')
1457 THEN
1458 resultout := 'COMPLETE:';
1459 RETURN;
1460 END IF;
1461
1462 --
1463 -- TIMEOUT mode
1464 --
1465 IF (funcmode = 'TIMEOUT')
1466 THEN
1467 resultout := 'COMPLETE:';
1468 RETURN;
1469 END IF;
1470
1471
1472
1473 EXCEPTION
1474 WHEN FND_API.g_exc_error THEN
1475 FND_MSG_PUB.Count_And_Get
1476 (
1477 p_encoded => FND_API.G_FALSE,
1478 p_count => l_msg_count,
1479 p_data => l_msg_data
1480 );
1481 AHL_GENERIC_APRV_PVT.Handle_Error
1482 (
1483 p_itemtype => itemtype ,
1484 p_itemkey => itemkey ,
1485 p_msg_count => l_msg_count,
1486 p_msg_data => l_msg_data ,
1487 p_attr_name => 'ERROR_MSG',
1488 x_error_msg => l_error_msg
1489 );
1490 wf_core.context
1491 (
1492 G_PKG_NAME,
1493 'UPDATE_STATUS',
1494 itemtype,
1495 itemkey,
1496 actid,
1497 funcmode,
1498 l_error_msg
1499 );
1500 RAISE;
1501
1502 WHEN OTHERS THEN
1503 wf_core.context(
1504 G_PKG_NAME,
1505 'UPDATE_STATUS',
1506 itemtype,
1507 itemkey,
1508 actid,
1509 funcmode,
1510 l_error_msg
1511 );
1512 RAISE;
1513
1514 END UPDATE_STATUS;
1515
1516
1517 -------------------------
1518 -- REVERT_STATUS
1519 -------------------------
1520 PROCEDURE REVERT_STATUS
1521 (
1522 itemtype IN VARCHAR2
1523 ,itemkey IN VARCHAR2
1524 ,actid IN NUMBER
1525 ,funcmode IN VARCHAR2
1526 ,resultout OUT NOCOPY VARCHAR2
1527 )
1528 IS
1529
1530 l_error_msg VARCHAR2(4000);
1531
1532 l_next_status VARCHAR2(30);
1533 l_object_version_number NUMBER;
1534 l_object_id NUMBER;
1535 l_status_date DATE;
1536 l_msg_count NUMBER;
1537 l_msg_data VARCHAR2(4000);
1538
1539 CURSOR check_uc_ovn(c_uc_header_id in number, c_object_version_number in number)
1540 IS
1541 SELECT unit_config_header_id, name, unit_config_status_code, active_uc_status_code
1542 FROM ahl_unit_config_headers
1543 WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1544 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1545 AND unit_config_header_id = c_uc_header_id
1546 AND object_version_number = c_object_version_number;
1547
1548
1549 l_uc_header_rec get_uc_header_det%ROWTYPE;
1550 l_check_uc_ovn check_uc_ovn%ROWTYPE;
1551
1552 BEGIN
1553
1554 IF G_DEBUG='Y' THEN
1555 AHL_DEBUG_PUB.ENABLE_DEBUG;
1556 AHL_DEBUG_PUB.debug( 'UC:Start Revert Status');
1557 END IF;
1558
1559 --
1560 -- RUN mode
1561 --
1562 IF (funcmode = 'RUN')
1563 THEN
1564 l_next_status := wf_engine.getitemattrText
1565 (
1566 itemtype => itemtype
1567 ,itemkey => itemkey
1568 ,aname => 'ORG_STATUS_ID'
1569 );
1570 IF G_DEBUG='Y' THEN
1571 AHL_DEBUG_PUB.debug('UC: REVERT_STATUS -- l_next_status'||l_next_status);
1572 END IF;
1573
1574 l_object_version_number := wf_engine.getitemattrnumber
1575 (
1576 itemtype => itemtype
1577 ,itemkey => itemkey
1578 ,aname => 'OBJECT_VER'
1579 );
1580
1581 IF G_DEBUG='Y' THEN
1582 AHL_DEBUG_PUB.debug('UC: REVERT_STATUS -- l_object_version_number'||l_object_version_number);
1583 END IF;
1584
1585 l_object_id := wf_engine.getitemattrnumber
1586 (
1587 itemtype => itemtype
1588 ,itemkey => itemkey
1589 ,aname => 'OBJECT_ID'
1590 );
1591
1592 IF G_DEBUG='Y' THEN
1593 AHL_DEBUG_PUB.debug('UC: REVERT_STATUS -- l_object_id'||l_object_id);
1594 END IF;
1595
1596 OPEN get_uc_header_det(l_object_id);
1597 FETCH get_uc_header_det into l_uc_header_rec;
1598
1599 IF get_uc_header_det%NOTFOUND
1600 THEN
1601 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
1602 fnd_message.set_token('UC_HEADER_ID', l_object_id, false);
1603 RAISE FND_API.G_EXC_ERROR;
1604 END IF;
1605 CLOSE get_uc_header_det;
1606
1607
1608 OPEN check_uc_ovn(l_object_id, l_object_version_number);
1609 FETCH check_uc_ovn into l_check_uc_ovn;
1610
1611 IF check_uc_ovn%NOTFOUND
1612 THEN
1613 fnd_message.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
1614 RAISE FND_API.G_EXC_ERROR;
1615 END IF;
1616
1617
1618 IF (l_check_uc_ovn.unit_config_status_code = 'APPROVAL_PENDING') THEN
1619
1620 UPDATE ahl_unit_config_headers
1621 SET unit_config_status_code = l_next_status,
1622 object_version_number = l_object_version_number + 1,
1623 last_update_date = sysdate,
1624 last_updated_by = to_number(fnd_global.login_id),
1625 last_update_login = to_number(fnd_global.login_id)
1626 WHERE unit_config_header_id = l_object_id;
1627
1628 ELSIF (l_check_uc_ovn.active_uc_status_code = 'APPROVAL_PENDING') THEN
1629
1630 UPDATE ahl_unit_config_headers
1631 SET active_uc_status_code = l_next_status,
1632 object_version_number = l_object_version_number + 1,
1633 last_update_date = sysdate,
1634 last_updated_by = to_number(fnd_global.login_id),
1635 last_update_login = to_number(fnd_global.login_id)
1636 WHERE unit_config_header_id = l_object_id;
1637
1638 END IF;
1639 CLOSE check_uc_ovn;
1640
1641 IF G_DEBUG='Y' THEN
1642 AHL_DEBUG_PUB.debug('UC: REVERT_STATUS --> Completed resetting of status');
1643 AHL_DEBUG_PUB.debug('UC:END Revert Status, l_object_id =' || l_object_id );
1644 AHL_DEBUG_PUB.DISABLE_DEBUG;
1645 END IF;
1646
1647 resultout := 'COMPLETE:';
1648 RETURN;
1649 END IF;
1650
1651 --
1652 -- CANCEL mode
1653 --
1654 IF (funcmode = 'CANCEL')
1655 THEN
1656 resultout := 'COMPLETE:';
1657 RETURN;
1658 END IF;
1659
1660 --
1661 -- TIMEOUT mode
1662 --
1663 IF (funcmode = 'TIMEOUT')
1664 THEN
1665 resultout := 'COMPLETE:';
1666 RETURN;
1667 END IF;
1668
1669
1670
1671 EXCEPTION
1672 WHEN FND_API.g_exc_error THEN
1673 FND_MSG_PUB.Count_And_Get
1674 (
1675 p_encoded => FND_API.G_FALSE,
1676 p_count => l_msg_count,
1677 p_data => l_msg_data
1678 );
1679 AHL_GENERIC_APRV_PVT.Handle_Error
1680 (
1681 p_itemtype => itemtype ,
1682 p_itemkey => itemkey ,
1683 p_msg_count => l_msg_count,
1684 p_msg_data => l_msg_data ,
1685 p_attr_name => 'ERROR_MSG',
1686 x_error_msg => l_error_msg
1687 ) ;
1688 wf_core.context
1689 (
1690 G_PKG_NAME,
1691 'REVERT_STATUS',
1692 itemtype,
1693 itemkey,
1694 actid,
1695 funcmode,
1696 l_error_msg
1697 );
1698 RAISE;
1699 WHEN OTHERS THEN
1700 wf_core.context
1701 (
1702 G_PKG_NAME,
1703 'REVERT_STATUS',
1704 itemtype,
1705 itemkey,
1706 actid,
1707 funcmode,
1708 'Unexpected Error!'
1709 );
1710 RAISE;
1711
1712 END REVERT_STATUS;
1713
1714 END AHL_UC_WF_APPR_PVT;