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