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