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