[Home] [Help]
PACKAGE BODY: APPS.AHL_PC_APPROVAL_PVT
Source
1 PACKAGE BODY AHL_PC_APPROVAL_PVT AS
2 /* $Header: AHLVPWKB.pls 115.10 2003/10/20 19:37:07 sikumar noship $ */
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 l_commit VARCHAR2(1) := FND_API.G_TRUE;
1242 l_pc_header_id NUMBER :=0;
1243 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
1244 l_validate_only VARCHAR2(1) := FND_API.G_TRUE;
1245 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
1246 l_module_type VARCHAR2(1);
1247 l_return_status VARCHAR2(1);
1248 x_return_status VARCHAR2(2000);
1249 x_msg_count NUMBER;
1250 x_msg_data VARCHAR2(2000);
1251 l_pc_header_rec AHL_PC_HEADER_PUB.PC_HEADER_REC;
1252 l_default VARCHAR2(1) := FND_API.G_FALSE;
1253
1254 CURSOR GET_PC_HEADER_DET(c_pc_header_id number)
1255 IS
1256 SELECT *
1257 FROM AHL_PC_HEADERS_VL
1258 WHERE PC_HEADER_ID = c_pc_header_id;
1259
1260 l_pc_header_det GET_PC_HEADER_DET%ROWTYPE;
1261
1262 BEGIN
1263
1264 IF G_DEBUG='Y' THEN
1265 AHL_DEBUG_PUB.ENABLE_DEBUG;
1266 END IF;
1267
1268 --
1269 -- RUN Mode
1270 --
1271 IF (funcmode = 'RUN')
1272 THEN
1273 l_approval_status := wf_engine.getitemattrtext
1274 (
1275 itemtype => itemtype
1276 ,itemkey => itemkey
1277 ,aname => 'UPDATE_GEN_STATUS'
1278 );
1279 IF G_DEBUG='Y' THEN
1280 AHL_DEBUG_PUB.debug('PCWF -- UPDATE_STATUS -- l_approval_status='||l_approval_status);
1281 END IF;
1282
1283 /*
1284 IF l_approval_status = 'APPROVED'
1285 THEN
1286 l_next_status := wf_engine.getitemattrText
1287 (
1288 itemtype => itemtype
1289 ,itemkey => itemkey
1290 ,aname => 'NEW_STATUS_ID'
1291 );
1292 ELSE
1293 l_next_status := wf_engine.getitemattrText
1294 (
1295 itemtype => itemtype
1296 ,itemkey => itemkey
1297 ,aname => 'REJECT_STATUS_ID'
1298 );
1299 END IF;
1300 IF G_DEBUG='Y' THEN
1301 AHL_DEBUG_PUB.debug('PCWF -- UPDATE_STATUS -- l_next_status='||l_next_status);
1302
1303 END IF;
1304 */
1305
1306 l_next_status := l_approval_status;
1307
1308 l_object_version_number := wf_engine.getitemattrnumber
1309 (
1310 itemtype => itemtype
1311 ,itemkey => itemkey
1312 ,aname => 'OBJECT_VER'
1313 );
1314 IF G_DEBUG='Y' THEN
1315 AHL_DEBUG_PUB.debug('PCWF -- UPDATE_STATUS -- l_object_version_number='||l_object_version_number);
1316 END IF;
1317
1318 l_object_id := wf_engine.getitemattrnumber
1319 (
1320 itemtype => itemtype
1321 ,itemkey => itemkey
1322 ,aname => 'OBJECT_ID'
1323 );
1324 IF G_DEBUG='Y' THEN
1325 AHL_DEBUG_PUB.debug('PCWF -- UPDATE_STATUS -- l_object_id='||l_object_id);
1326 END IF;
1327
1328 l_status_date := SYSDATE;
1329
1330 OPEN GET_PC_HEADER_DET(l_object_id);
1331 FETCH GET_PC_HEADER_DET INTO l_pc_header_det;
1332 CLOSE GET_PC_HEADER_DET;
1333 IF (l_pc_header_det.object_version_number <> l_object_version_number)
1334 THEN
1335 fnd_message.set_name('AHL', 'AHL_APRV_OBJ_CHANGED');
1336 fnd_msg_pub.add;
1337 l_msg_count := 1;
1338 l_return_status := FND_API.G_RET_STS_ERROR;
1339 ELSE
1340 l_pc_header_rec.PC_HEADER_ID := l_pc_header_det.PC_HEADER_ID;
1341 l_pc_header_rec.NAME := l_pc_header_det.NAME;
1342 l_pc_header_rec.DESCRIPTION := l_pc_header_det.DESCRIPTION;
1343 l_pc_header_rec.STATUS := l_next_status;
1344 l_pc_header_rec.PRODUCT_TYPE_CODE := l_pc_header_det.PRODUCT_TYPE_CODE;
1345 l_pc_header_rec.PRIMARY_FLAG := l_pc_header_det.PRIMARY_FLAG;
1346 l_pc_header_rec.ASSOCIATION_TYPE_FLAG := l_pc_header_det.ASSOCIATION_TYPE_FLAG;
1347 l_pc_header_rec.DRAFT_FLAG := l_pc_header_det.DRAFT_FLAG;
1348 l_pc_header_rec.LINK_TO_PC_ID := l_pc_header_det.LINK_TO_PC_ID;
1349 l_pc_header_rec.OBJECT_VERSION_NUMBER := l_object_version_number;
1350 l_pc_header_rec.ATTRIBUTE_CATEGORY := l_pc_header_det.ATTRIBUTE_CATEGORY;
1351 l_pc_header_rec.ATTRIBUTE1 := l_pc_header_det.ATTRIBUTE1;
1352 l_pc_header_rec.ATTRIBUTE2 := l_pc_header_det.ATTRIBUTE2;
1353 l_pc_header_rec.ATTRIBUTE3 := l_pc_header_det.ATTRIBUTE3;
1354 l_pc_header_rec.ATTRIBUTE4 := l_pc_header_det.ATTRIBUTE4;
1355 l_pc_header_rec.ATTRIBUTE5 := l_pc_header_det.ATTRIBUTE5;
1356 l_pc_header_rec.ATTRIBUTE6 := l_pc_header_det.ATTRIBUTE6;
1357 l_pc_header_rec.ATTRIBUTE7 := l_pc_header_det.ATTRIBUTE7;
1358 l_pc_header_rec.ATTRIBUTE8 := l_pc_header_det.ATTRIBUTE8;
1359 l_pc_header_rec.ATTRIBUTE9 := l_pc_header_det.ATTRIBUTE9;
1360 l_pc_header_rec.ATTRIBUTE10 := l_pc_header_det.ATTRIBUTE10;
1361 l_pc_header_rec.ATTRIBUTE11 := l_pc_header_det.ATTRIBUTE11;
1362 l_pc_header_rec.ATTRIBUTE12 := l_pc_header_det.ATTRIBUTE12;
1363 l_pc_header_rec.ATTRIBUTE13 := l_pc_header_det.ATTRIBUTE13;
1364 l_pc_header_rec.ATTRIBUTE14 := l_pc_header_det.ATTRIBUTE14;
1365 l_pc_header_rec.ATTRIBUTE15 := l_pc_header_det.ATTRIBUTE15;
1366 l_pc_header_rec.OPERATION_FLAG := 'U';
1367 l_pc_header_rec.COPY_ASSOS_FLAG := 'N';
1368 l_pc_header_rec.COPY_DOCS_FLAG := 'N';
1369
1370 AHL_PC_HEADER_PVT.UPDATE_PC_HEADER
1371 (
1372 p_api_version => 1.0,
1373 p_init_msg_list => FND_API.G_FALSE,
1374 p_commit => FND_API.G_TRUE,
1375 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1376 p_x_pc_header_rec => l_pc_header_rec,
1377 x_return_status => x_return_status,
1378 x_msg_count => x_msg_count,
1379 x_msg_data => x_msg_data
1380 );
1381 IF G_DEBUG='Y' THEN
1382 AHL_DEBUG_PUB.debug('PCWF -- UPDATE_STATUS -- Completed AHL_PC_HEADER_PVT.UPDATE_PC_HEADER');
1383 END IF;
1384
1385 l_msg_count := x_msg_count;
1386
1387 IF (l_msg_count > 0)
1388 THEN
1389 l_return_status := FND_API.G_RET_STS_ERROR;
1390 ELSE
1391 l_return_status := FND_API.G_RET_STS_SUCCESS;
1392 END IF;
1393 END IF;
1394
1395 resultout := 'COMPLETE:SUCCESS';
1396 RETURN;
1397 END IF;
1398
1399 --
1400 -- CANCEL mode
1401 --
1402 IF (funcmode = 'CANCEL')
1403 THEN
1404 resultout := 'COMPLETE:';
1405 RETURN;
1406 END IF;
1407
1408 --
1409 -- TIMEOUT mode
1410 --
1411 IF (funcmode = 'TIMEOUT')
1412 THEN
1413 resultout := 'COMPLETE:';
1414 RETURN;
1415 END IF;
1416
1417 EXCEPTION
1418 WHEN FND_API.g_exc_error THEN
1419 FND_MSG_PUB.Count_And_Get
1420 (
1421 p_encoded => FND_API.G_FALSE,
1422 p_count => l_msg_count,
1423 p_data => l_msg_data
1424 );
1425 AHL_GENERIC_APRV_PVT.Handle_Error
1426 (
1427 p_itemtype => itemtype ,
1428 p_itemkey => itemkey ,
1429 p_msg_count => l_msg_count,
1430 p_msg_data => l_msg_data ,
1431 p_attr_name => 'ERROR_MSG',
1432 x_error_msg => l_error_msg
1433 );
1434 wf_core.context
1435 (
1436 'AHL_PC_APPROVAL_PVT',
1437 'UPDATE_STATUS',
1438 itemtype,
1439 itemkey,
1440 actid,
1441 funcmode,
1442 l_error_msg
1443 );
1444 RAISE;
1445
1446 WHEN OTHERS THEN
1447 wf_core.context(
1448 'AHL_PC_APPROVAL_PVT',
1449 'UPDATE_STATUS',
1450 itemtype,
1451 itemkey,
1452 actid,
1453 funcmode,
1454 l_error_msg
1455 );
1456 RAISE;
1457
1458 END UPDATE_STATUS;
1459
1460 PROCEDURE REVERT_STATUS
1461 (
1462 itemtype IN VARCHAR2
1463 ,itemkey IN VARCHAR2
1464 ,actid IN NUMBER
1465 ,funcmode IN VARCHAR2
1466 ,resultout OUT NOCOPY VARCHAR2
1467 )
1468 IS
1469
1470 l_error_msg VARCHAR2(4000);
1471
1472 l_next_status VARCHAR2(30);
1473 l_approval_status VARCHAR2(30);
1474 l_object_version_number NUMBER;
1475 l_object_id NUMBER;
1476 l_status_date DATE;
1477 l_msg_count NUMBER;
1478 l_msg_data VARCHAR2(4000);
1479
1480 cursor GET_PC_HEADER_DET(c_pc_header_id number)
1481 is
1482 select PC_HEADER_ID,Name
1483 from AHL_PC_headers_b
1484 where PC_HEADER_ID=c_pc_header_id;
1485
1486 l_pc_header_rec GET_PC_HEADER_DET%rowtype;
1487
1488
1489 BEGIN
1490
1491 IF G_DEBUG='Y' THEN
1492 AHL_DEBUG_PUB.ENABLE_DEBUG;
1493 END IF;
1494
1495 --
1496 -- RUN mode
1497 --
1498 IF (funcmode = 'RUN')
1499 THEN
1500 l_next_status := wf_engine.getitemattrText
1501 (
1502 itemtype => itemtype
1503 ,itemkey => itemkey
1504 ,aname => 'ORG_STATUS_ID'
1505 );
1506 IF G_DEBUG='Y' THEN
1507 AHL_DEBUG_PUB.debug('PCWF -- UPDATE_STATUS -- l_next_status'||l_next_status);
1508 END IF;
1509
1510 l_object_version_number := wf_engine.getitemattrnumber
1511 (
1512 itemtype => itemtype
1513 ,itemkey => itemkey
1514 ,aname => 'OBJECT_VER'
1515 );
1516 IF G_DEBUG='Y' THEN
1517 AHL_DEBUG_PUB.debug('PCWF -- UPDATE_STATUS -- l_object_version_number'||l_object_version_number);
1518 END IF;
1519
1520 l_object_id := wf_engine.getitemattrnumber
1521 (
1522 itemtype => itemtype
1523 ,itemkey => itemkey
1524 ,aname => 'OBJECT_ID'
1525 );
1526 IF G_DEBUG='Y' THEN
1527 AHL_DEBUG_PUB.debug('PCWF -- UPDATE_STATUS -- l_object_id'||l_object_id);
1528 END IF;
1529
1530 l_status_date := SYSDATE;
1531
1532 UPDATE AHL_PC_HEADERS_B
1533 SET STATUS = 'DRAFT',
1534 OBJECT_VERSION_NUMBER = l_object_version_number + 1
1535 WHERE PC_HEADER_ID = l_object_id AND
1536 OBJECT_VERSION_NUMBER = l_object_version_number;
1537
1538 IF G_DEBUG='Y' THEN
1539 AHL_DEBUG_PUB.debug('PCWF -- UPDATE_STATUS -- Completed reset of status');
1540 END IF;
1541
1542 COMMIT;
1543
1544 resultout := 'COMPLETE:';
1545 RETURN;
1546 END IF;
1547
1548 --
1549 -- CANCEL mode
1550 --
1551 IF (funcmode = 'CANCEL')
1552 THEN
1553 resultout := 'COMPLETE:';
1554 RETURN;
1555 END IF;
1556
1557 --
1558 -- TIMEOUT mode
1559 --
1560 IF (funcmode = 'TIMEOUT')
1561 THEN
1562 resultout := 'COMPLETE:';
1563 RETURN;
1564 END IF;
1565
1566
1567 EXCEPTION
1568 WHEN FND_API.g_exc_error THEN
1569 FND_MSG_PUB.Count_And_Get
1570 (
1571 p_encoded => FND_API.G_FALSE,
1572 p_count => l_msg_count,
1573 p_data => l_msg_data
1574 );
1575 AHL_GENERIC_APRV_PVT.Handle_Error
1576 (
1577 p_itemtype => itemtype ,
1578 p_itemkey => itemkey ,
1579 p_msg_count => l_msg_count,
1580 p_msg_data => l_msg_data ,
1581 p_attr_name => 'ERROR_MSG',
1582 x_error_msg => l_error_msg
1583 ) ;
1584 wf_core.context
1585 (
1586 'AHL_PC_APPROVAL_PVT',
1587 'REVERT_STATUS',
1588 itemtype,
1589 itemkey,
1590 actid,
1591 funcmode,
1592 l_error_msg
1593 );
1594 RAISE;
1595 WHEN OTHERS THEN
1596 wf_core.context
1597 (
1598 'AHL_PC_APPROVAL_PVT'
1599 ,'REVERT_STATUS'
1600 ,itemtype
1601 ,itemkey
1602 ,actid
1603 ,funcmode
1604 ,'Unexpected Error!'
1605 );
1606 RAISE;
1607
1608 END REVERT_STATUS;
1609
1610 END AHL_PC_APPROVAL_PVT;