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