[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.2 2008/04/28 23:57:48 sikumar 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 BEGIN
1162
1163 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1164 fnd_log.string
1165 (
1166 fnd_log.level_procedure,
1167 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Update_Status.begin',
1168 'At the start of PLSQL procedure'
1169 );
1170 END IF;
1171 SAVEPOINT AHL_DEF_UPDATE_STATUS;
1172
1173 MO_GLOBAL.INIT('AHL');
1174
1175 l_return_status := FND_API.G_RET_STS_SUCCESS;
1176
1177 IF (funcmode = 'RUN') THEN
1178
1179 l_approval_status := wf_engine.getitemattrtext(
1180 itemtype => itemtype,
1181 itemkey => itemkey,
1182 aname => 'UPDATE_GEN_STATUS'
1183 );
1184
1185 l_object_id := wf_engine.getitemattrnumber(
1186 itemtype => itemtype,
1187 itemkey => itemkey,
1188 aname => 'OBJECT_ID'
1189 );
1190 l_object_version_number := wf_engine.getitemattrnumber(
1191 itemtype => itemtype,
1192 itemkey => itemkey,
1193 aname => 'OBJECT_VER'
1194 );
1195 l_approver_note := wf_engine.getitemattrtext(
1196 itemtype => itemtype,
1197 itemkey => itemkey,
1198 aname => 'APPROVER NOTE'
1199 );
1200
1201 UPDATE AHL_UNIT_DEFERRALS_TL
1202 SET approver_notes = l_approver_note,
1203 SOURCE_LANG = userenv('LANG')
1204 WHERE unit_deferral_id = l_object_id
1205 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1206
1207 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1208 fnd_log.string
1209 (
1210 fnd_log.level_statement,
1211 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Update_Status',
1212 'unit_deferral_id : ' || l_object_id
1213 );
1214 fnd_log.string
1215 (
1216 fnd_log.level_statement,
1217 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Update_Status',
1218 'object_version_number : ' || l_object_version_number
1219 );
1220 fnd_log.string
1221 (
1222 fnd_log.level_statement,
1223 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Update_Status',
1224 'approval status : ' || l_approval_status
1225 );
1226
1227 END IF;
1228
1229 IF (l_approval_status IN( 'DEFERRED','TERMINATED','CANCELLED')) THEN
1230
1231 l_new_status := wf_engine.getitemattrText(
1232 itemtype => itemtype,
1233 itemkey => itemkey,
1234 aname => 'NEW_STATUS_ID'
1235 );
1236 AHL_PRD_DF_PVT.process_approval_approved(
1237 p_unit_deferral_id => l_object_id,
1238 p_object_version_number => l_object_version_number,
1239 p_new_status => l_new_status,
1240 x_return_status => l_return_status
1241 );
1242 ELSE
1243 l_new_status := wf_engine.getitemattrText(
1244 itemtype => itemtype,
1245 itemkey => itemkey,
1246 aname => 'REJECT_STATUS_ID'
1247 );
1248 AHL_PRD_DF_PVT.process_approval_rejected(
1249 p_unit_deferral_id => l_object_id,
1250 p_object_version_number => l_object_version_number,
1251 p_new_status => l_new_status,
1252 x_return_status => l_return_status
1253 );
1254 END IF;
1255
1256 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1257 fnd_log.string
1258 (
1259 fnd_log.level_statement,
1260 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Update_Status',
1261 'new status : ' || l_new_status
1262 );
1263 fnd_log.string
1264 (
1265 fnd_log.level_statement,
1266 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Update_Status',
1267 'return status after process_approval_rejected API call : ' || l_return_status
1268 );
1269 END IF;
1270
1271 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1272 RAISE FND_API.G_EXC_ERROR;
1273 ELSE
1274 COMMIT WORK;
1275 END IF;
1276 resultout := 'COMPLETE:';
1277 ELSIF (funcmode IN ('CANCEL','TIMEOUT'))THEN
1278 resultout := 'COMPLETE:';
1279 END IF;
1280
1281 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1282 fnd_log.string
1283 (
1284 fnd_log.level_procedure,
1285 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Update_Status.end',
1286 'At the end of PLSQL procedure'
1287 );
1288 END IF;
1289
1290
1291 EXCEPTION
1292 WHEN fnd_api.G_EXC_ERROR OR FND_API.G_EXC_UNEXPECTED_ERROR THEN
1293 ROLLBACK TO AHL_DEF_UPDATE_STATUS;
1294 FND_MSG_PUB.Count_And_Get (
1295 p_encoded => FND_API.G_FALSE,
1296 p_count => l_msg_count,
1297 p_data => l_msg_data
1298 );
1299 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1300 fnd_log.string
1301 (
1302 fnd_log.level_unexpected,
1303 'ahl.plsql.AHL_PRD_DF_APPR_PVT.update_status',
1304 ' Error Message : l_msg_data : ' || l_msg_data
1305 );
1306 END IF;
1307 ahl_generic_aprv_pvt.Handle_Error
1308 (p_itemtype => itemtype,
1309 p_itemkey => itemkey ,
1310 p_msg_count => l_msg_count, -- Number of error Messages
1311 p_msg_data => l_msg_data ,
1312 p_attr_name => 'ERROR_MSG',
1313 x_error_msg => l_error_msg
1314 ) ;
1315 wf_core.context(G_PKG_NAME,'Update_Status',itemtype,itemkey,l_error_msg);
1316
1317 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1318 fnd_log.string
1319 (
1320 fnd_log.level_unexpected,
1321 'ahl.plsql.AHL_PRD_DF_APPR_PVT.update_status',
1322 ' Error Message : l_error_msg : ' || l_error_msg
1323 );
1324 END IF;
1325
1326 -- update validation errors.
1327 UPDATE AHL_UNIT_DEFERRALS_TL
1328 SET approver_notes = substrb(l_error_msg,1,4000)
1329 WHERE unit_deferral_id = l_object_id
1330 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1331
1332 UPDATE AHL_UNIT_DEFERRALS_B
1333 SET approval_status_code = 'DEFERRAL_REJECTED',
1334 object_version_number = object_version_number + 1,
1335 last_update_date = sysdate,
1336 last_updated_by = fnd_global.user_id,
1337 last_update_login = fnd_global.login_id
1338 WHERE unit_deferral_id = l_object_id;
1339 COMMIT WORK;
1340
1341 RAISE;
1342 WHEN OTHERS THEN
1343 ROLLBACK TO AHL_DEF_UPDATE_STATUS;
1344 wf_core.context( 'AHLGAPP', 'Update_Status', itemtype, itemkey );
1345
1346 l_error_msg := SQLCODE || ': ' || SQLERRM;
1347 -- update validation errors.
1348 UPDATE AHL_UNIT_DEFERRALS_TL
1349 SET approver_notes = substrb(l_error_msg,1,4000)
1350 WHERE unit_deferral_id = l_object_id
1351 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1352
1353 UPDATE AHL_UNIT_DEFERRALS_B
1354 SET approval_status_code = 'DEFERRAL_REJECTED',
1355 object_version_number = object_version_number + 1,
1356 last_update_date = sysdate,
1357 last_updated_by = fnd_global.user_id,
1358 last_update_login = fnd_global.login_id
1359 WHERE unit_deferral_id = l_object_id;
1360 COMMIT WORK;
1361
1362
1363 RAISE;
1364
1365 END Update_Status;
1366
1367 PROCEDURE Revert_Status(
1368 itemtype IN VARCHAR2,
1369 itemkey IN VARCHAR2,
1370 actid IN NUMBER,
1371 funcmode IN VARCHAR2,
1372 resultout OUT NOCOPY VARCHAR2)
1373 IS
1374
1375 l_error_msg VARCHAR2(4000);
1376 l_orig_status VARCHAR2(30);
1377 l_object_version_number NUMBER;
1378 l_object_id NUMBER;
1379 l_msg_count NUMBER;
1380 l_msg_data VARCHAR2(4000);
1381 l_return_status VARCHAR2(1);
1382 l_approver_note VARCHAR2(4000);
1383
1384 BEGIN
1385 MO_GLOBAL.INIT('AHL');
1386 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1387 fnd_log.string
1388 (
1389 fnd_log.level_procedure,
1390 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Revert_Status.begin',
1391 'At the start of PLSQL procedure'
1392 );
1393 END IF;
1394 SAVEPOINT AHL_DEF_REVT_STATUS;
1395 l_return_status := FND_API.G_RET_STS_SUCCESS;
1396
1397 IF (funcmode = 'RUN') THEN
1398 l_orig_status := wf_engine.getitemattrText(
1399 itemtype => itemtype,
1400 itemkey => itemkey,
1401 aname => 'ORG_STATUS_ID'
1402 );
1403 l_object_id := wf_engine.getitemattrnumber(
1404 itemtype => itemtype,
1405 itemkey => itemkey,
1406 aname => 'OBJECT_ID'
1407 );
1408 l_object_version_number := wf_engine.getitemattrnumber(
1409 itemtype => itemtype,
1410 itemkey => itemkey,
1411 aname => 'OBJECT_VER'
1412 );
1413 l_approver_note := wf_engine.getitemattrnumber(
1414 itemtype => itemtype,
1415 itemkey => itemkey,
1416 aname => 'APPROVER NOTE'
1417 );
1418
1419 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1420 fnd_log.string
1421 (
1422 fnd_log.level_statement,
1423 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Revert_Status',
1424 'unit_deferral_id : ' || l_object_id
1425 );
1426 fnd_log.string
1427 (
1428 fnd_log.level_statement,
1429 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Revert_Status',
1430 'object_version_number : ' || l_object_version_number
1431 );
1432 fnd_log.string
1433 (
1434 fnd_log.level_statement,
1435 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Revert_Status',
1436 'Original status : ' || l_orig_status
1437 );
1438
1439 END IF;
1440 -- go in error mode
1441 AHL_PRD_DF_PVT.process_approval_rejected(
1442 p_unit_deferral_id => l_object_id,
1443 p_object_version_number => l_object_version_number,
1444 p_new_status => l_orig_status,
1445 x_return_status => l_return_status
1446 );
1447 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1448 fnd_log.string
1449 (
1450 fnd_log.level_statement,
1451 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Revert_Status',
1452 'return status after process_approval_rejected API call : ' || l_return_status
1453 );
1454 END IF;
1455
1456 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1457 RAISE FND_API.G_EXC_ERROR;
1458 ELSE
1459 COMMIT WORK;
1460 END IF;
1461 resultout := 'COMPLETE:';
1462 ELSIF (funcmode IN ('CANCEL','TIMEOUT'))THEN
1463 resultout := 'COMPLETE:';
1464 END IF;
1465
1466 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1467 fnd_log.string
1468 (
1469 fnd_log.level_procedure,
1470 'ahl.plsql.AHL_PRD_DF_APPR_PVT.Revert_Status.end',
1471 'At the end of PLSQL procedure'
1472 );
1473 END IF;
1474 EXCEPTION
1475 WHEN fnd_api.G_EXC_ERROR OR FND_API.G_EXC_UNEXPECTED_ERROR THEN
1476 ROLLBACK TO AHL_DEF_REVT_STATUS;
1477 FND_MSG_PUB.Count_And_Get (
1478 p_encoded => FND_API.G_FALSE,
1479 p_count => l_msg_count,
1480 p_data => l_msg_data
1481 );
1482 ahl_generic_aprv_pvt.Handle_Error
1483 (p_itemtype => itemtype,
1484 p_itemkey => itemkey ,
1485 p_msg_count => l_msg_count, -- Number of error Messages
1486 p_msg_data => l_msg_data ,
1487 p_attr_name => 'ERROR_MSG',
1488 x_error_msg => l_error_msg
1489 ) ;
1490 wf_core.context(G_PKG_NAME,'Revert_Status',itemtype,itemkey,l_error_msg);
1491
1492 -- update validation errors.
1493 UPDATE AHL_UNIT_DEFERRALS_TL
1494 SET approver_notes = substrb(l_error_msg,1,4000)
1495 WHERE unit_deferral_id = l_object_id
1496 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1497
1498 UPDATE AHL_UNIT_DEFERRALS_B
1499 SET approval_status_code = 'DEFERRAL_REJECTED',
1500 object_version_number = object_version_number + 1,
1501 last_update_date = sysdate,
1502 last_updated_by = fnd_global.user_id,
1503 last_update_login = fnd_global.login_id
1504 WHERE unit_deferral_id = l_object_id;
1505 COMMIT WORK;
1506
1507 RAISE;
1508 WHEN OTHERS THEN
1509 ROLLBACK TO AHL_DEF_REVT_STATUS;
1510 wf_core.context( 'AHLGAPP', 'Revert_Status', itemtype, itemkey );
1511
1512 l_error_msg := SQLCODE || ': ' || SQLERRM;
1513 -- update validation errors.
1514 UPDATE AHL_UNIT_DEFERRALS_TL
1515 SET approver_notes = substrb(l_error_msg,1,4000)
1516 WHERE unit_deferral_id = l_object_id
1517 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1518
1519 UPDATE AHL_UNIT_DEFERRALS_B
1520 SET approval_status_code = 'DEFERRAL_REJECTED',
1521 object_version_number = object_version_number + 1,
1522 last_update_date = sysdate,
1523 last_updated_by = fnd_global.user_id,
1524 last_update_login = fnd_global.login_id
1525 WHERE unit_deferral_id = l_object_id;
1526 COMMIT WORK;
1527
1528 RAISE;
1529
1530 END Revert_Status;
1531
1532 FUNCTION getRequesterNote(
1533 p_df_header_info_rec AHL_PRD_DF_PVT.df_header_info_rec_type,
1534 p_df_schedules_tbl AHL_PRD_DF_PVT.df_schedules_tbl_type) RETURN VARCHAR2 IS
1535
1536 l_requester_note VARCHAR2(4000);
1537 l_defer_to_meaning VARCHAR2(80);
1538 l_defer_by_meaning VARCHAR2(80);
1539
1540 CURSOR ctr_value_type_meaning_csr IS
1541 SELECT lookup_code, meaning FROM fnd_lookup_values_vl fnd
1542 WHERE fnd.lookup_type = 'AHL_PRD_DF_CT_VAL_TYPES';
1543
1544
1545 BEGIN
1546 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_CTXT_TITLE');
1547 l_requester_note := FND_MESSAGE.get;
1548
1549 IF(p_df_header_info_rec.deferral_type = AHL_PRD_DF_PVT.G_DEFERRAL_TYPE_MR)THEN
1550 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_MR_CTXT');
1551 FND_MESSAGE.SET_TOKEN('MR_TITLE',p_df_header_info_rec.mr_title,false);
1552 FND_MESSAGE.SET_TOKEN('VISIT_NUMBER',p_df_header_info_rec.visit_number,false);
1553 FND_MESSAGE.SET_TOKEN('MR_DESC',p_df_header_info_rec.mr_description,false);
1554 FND_MESSAGE.SET_TOKEN('DUE_DATE',p_df_header_info_rec.due_date,false);
1555 l_requester_note := l_requester_note || FND_MESSAGE.get;
1556 ELSE
1557 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_SR_CTXT');
1558 FND_MESSAGE.SET_TOKEN('INCIDENT_NUMBER',p_df_header_info_rec.incident_number,false);
1559 FND_MESSAGE.SET_TOKEN('VISIT_NUMBER',p_df_header_info_rec.visit_number,false);
1560 FND_MESSAGE.SET_TOKEN('SUMMARY',p_df_header_info_rec.summary,false);
1561 FND_MESSAGE.SET_TOKEN('DUE_DATE',p_df_header_info_rec.due_date,false);
1562 l_requester_note := l_requester_note || FND_MESSAGE.get;
1563 END IF;
1564
1565 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_REASON');
1566 FND_MESSAGE.SET_TOKEN('REASON',getReasonCode(p_df_header_info_rec.defer_reason_code),false);
1567 l_requester_note := l_requester_note || FND_MESSAGE.get;
1568
1569 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_REMARK');
1570 FND_MESSAGE.SET_TOKEN('REMARK',p_df_header_info_rec.remarks,false);
1571 l_requester_note := l_requester_note || FND_MESSAGE.get;
1572
1573 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_SCHEDULE');
1574 l_requester_note := l_requester_note || FND_MESSAGE.get;
1575 IF(NVL(p_df_header_info_rec.skip_mr_flag,AHL_PRD_DF_PVT.G_NO_FLAG) = AHL_PRD_DF_PVT.G_YES_FLAG)THEN
1576 IF(NVL(p_df_header_info_rec.manually_planned_flag,AHL_PRD_DF_PVT.G_NO_FLAG) = AHL_PRD_DF_PVT.G_NO_FLAG)THEN
1577 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_SKIP');
1578 l_requester_note := l_requester_note || FND_MESSAGE.get;
1579 ELSE
1580 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_CANCEL');
1581 l_requester_note := l_requester_note || FND_MESSAGE.get;
1582 END IF;
1583 ELSE
1584 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
1585 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_DFR_AFFDUE');
1586 l_requester_note := l_requester_note || FND_MESSAGE.get;
1587 END IF;
1588 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_DFR_TODT');
1589 FND_MESSAGE.SET_TOKEN('SET_DUE_DATE',p_df_header_info_rec.set_due_date,false);
1590 l_requester_note := l_requester_note || FND_MESSAGE.get;
1591
1592 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_DFR_EFFDT');
1593 FND_MESSAGE.SET_TOKEN('EFFECT_DATE',p_df_header_info_rec.deferral_effective_on,false);
1594 l_requester_note := l_requester_note || FND_MESSAGE.get;
1595
1596 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_CNT_VALS');
1597 l_requester_note := l_requester_note || FND_MESSAGE.get;
1598
1599 IF(p_df_schedules_tbl IS NOT NULL AND p_df_schedules_tbl.count > 0)THEN
1600 FOR meaning_rec IN ctr_value_type_meaning_csr LOOP
1601 IF(meaning_rec.lookup_code = AHL_PRD_DF_PVT.G_DEFER_BY)THEN
1602 l_defer_by_meaning := meaning_rec.meaning;
1603 ELSIF (meaning_rec.lookup_code = AHL_PRD_DF_PVT.G_DEFER_TO)THEN
1604 l_defer_to_meaning := meaning_rec.meaning;
1605 END IF;
1606 END LOOP;
1607
1608 FOR i IN p_df_schedules_tbl.FIRST..p_df_schedules_tbl.LAST LOOP
1609 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DF_APPR_CNT_ROW');
1610 FND_MESSAGE.SET_TOKEN('COUNTER_NAME',p_df_schedules_tbl(i).counter_name,false);
1611 IF(p_df_schedules_tbl(i).CTR_VALUE_TYPE_CODE = AHL_PRD_DF_PVT.G_DEFER_BY)THEN
1612 FND_MESSAGE.SET_TOKEN('CTR_VAL_TYPE_CODE',l_defer_by_meaning,false);
1613 ELSIF (p_df_schedules_tbl(i).CTR_VALUE_TYPE_CODE = AHL_PRD_DF_PVT.G_DEFER_TO)THEN
1614 FND_MESSAGE.SET_TOKEN('CTR_VAL_TYPE_CODE',l_defer_to_meaning,false);
1615 END IF;
1616 FND_MESSAGE.SET_TOKEN('COUNTER_VALUE',p_df_schedules_tbl(i).counter_value,false);
1617 FND_MESSAGE.SET_TOKEN('UOM_CODE',p_df_schedules_tbl(i).unit_of_measure,false);
1618 l_requester_note := l_requester_note || FND_MESSAGE.get;
1619 END LOOP;
1620 END IF;
1621 END IF;
1622 RETURN l_requester_note;
1623
1624 END getRequesterNote;
1625
1626 FUNCTION getReasonCode(p_defer_reason_code IN VARCHAR2) RETURN VARCHAR2 IS
1627
1628 l_return_meaning VARCHAR2(4000);
1629
1630 l_temp1 NUMBER := 1;
1631 l_temp2 NUMBER;
1632 l_index NUMBER := 1;
1633 exit_flag boolean := false;
1634 l_string VARCHAR2(30);
1635
1636 CURSOR val_reason_meaning_csr(p_reason_code IN VARCHAR2) IS
1637 SELECT meaning FROM fnd_lookup_values_vl fnd
1638 WHERE fnd.lookup_type = 'AHL_PRD_DF_REASON_TYPES'
1639 AND fnd.lookup_code = p_reason_code;
1640
1641 l_meaning VARCHAR2(80) := '';
1642
1643 BEGIN
1644
1645 IF(p_defer_reason_code IS NULL)THEN
1646 RETURN l_return_meaning;
1647 END IF;
1648 LOOP
1649 l_temp2 := instr(p_defer_reason_code,AHL_PRD_DF_PVT.G_REASON_CODE_DELIM,1,l_index);
1650 IF(l_temp2 = 0) THEN
1651 l_string := substr(p_defer_reason_code,l_temp1);
1652 OPEN val_reason_meaning_csr(l_string);
1653 FETCH val_reason_meaning_csr INTO l_meaning;
1654 IF(val_reason_meaning_csr%FOUND) THEN
1655 l_return_meaning := l_return_meaning || ' ' || l_meaning;
1656 END IF;
1657 CLOSE val_reason_meaning_csr;
1658 exit_flag := true;
1659 ELSE
1660 l_string := substr(p_defer_reason_code,l_temp1,l_temp2 - l_temp1);
1661 OPEN val_reason_meaning_csr(l_string);
1662 FETCH val_reason_meaning_csr INTO l_meaning;
1663 IF(val_reason_meaning_csr%FOUND) THEN
1664 l_return_meaning := l_return_meaning || ' ' || l_meaning;
1665 END IF;
1666 CLOSE val_reason_meaning_csr;
1667 l_index := l_index + 1;
1668 l_temp1 := l_temp2 + 1;
1669 END IF;
1670 EXIT WHEN exit_flag;
1671 END LOOP;
1672 RETURN l_return_meaning;
1673
1674 END getReasonCode;
1675
1676 END AHL_PRD_DF_APPR_PVT;--end package body