1 PACKAGE BODY PV_AME_API_W AS
2 /* $Header: pvapprlb.pls 120.21 2006/12/01 20:32:21 saarumug ship $*/
3
4 g_concurrent_update EXCEPTION;
5 PRAGMA EXCEPTION_INIT(g_concurrent_update, -00054);
6
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_AME_API_W';
8 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvapprlb.pls';
9
10 returnStatus CONSTANT VARCHAR2(10) := 'RETURN';
11
12 PROCEDURE DEL_PRIOR_REP_APPR(p_approval_entity IN VARCHAR2
13 , p_referral_id IN NUMBER
14 , p_approval_list IN JTF_NUMBER_TABLE);
15
16 PROCEDURE GET_APPROVERS(p_approval_entity IN VARCHAR2
17 ,p_referral_id IN NUMBER
18 ,p_mode IN VARCHAR2
19 ,x_approval_list OUT NOCOPY JTF_NUMBER_TABLE
20 ,x_approval_completed OUT NOCOPY VARCHAR2
21 ,x_default_approver OUT NOCOPY VARCHAR2
22 ,x_user_id_exists OUT NOCOPY VARCHAR2);
23
24 FUNCTION VALIDATE_APPROVAL (p_transaction_id IN NUMBER
25 , p_transaction_type IN VARCHAR2
26 , p_user_id IN NUMBER
27 , p_person_id IN NUMBER
28 , p_mode IN VARCHAR2
29 , p_approval_level IN NUMBER
30 , x_approver OUT NOCOPY ame_util.approverRecord2)
31 RETURN BOOLEAN;
32
33 PROCEDURE START_APPROVAL_PROCESS( p_api_version_number IN NUMBER
34 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
35 , p_commit IN VARCHAR2 := FND_API.G_FALSE
36 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
37 , p_referral_id IN NUMBER
38 , p_partner_id IN NUMBER DEFAULT NULL
39 , p_change_cntry_flag IN VARCHAR2 -- if ref country is changed set this to true
40 , p_country_code IN VARCHAR2 -- new country code if change_country_flag is true
41 , p_approval_entity IN VARCHAR2 -- PVREFFRL/PVDEALRN/PVDQMAPR
42 , x_return_status OUT NOCOPY VARCHAR2
43 , x_msg_count OUT NOCOPY NUMBER
44 , x_msg_data OUT NOCOPY VARCHAR2
45 ) IS
46
47 l_api_name CONSTANT VARCHAR2(30) := 'START_APPROVAL_PROCESS';
48 l_api_version_number CONSTANT NUMBER := 1.0;
49
50 CURSOR lc_referral_info (pc_referral_id NUMBER) IS
51 SELECT partner_id ,benefit_id,benefit_type_code
52 FROM pv_referrals_b
53 WHERE referral_id = pc_referral_id;
54
55 CURSOR lc_get_approver_name (pc_user_id NUMBER) IS
56 SELECT source_name FROM jtf_rs_resource_extns WHERE user_id = pc_user_id;
57
58 CURSOR lc_prior_approvers IS
59 SELECT APPROVER_ID
60 FROM PV_GE_TEMP_APPROVERS
61 WHERE ARC_APPR_FOR_ENTITY_CODE = p_approval_entity
62 AND APPR_FOR_ENTITY_ID = p_referral_id;
63
64 cursor lc_lock_approvals is
65 SELECT entity_approver_id
66 FROM pv_ge_temp_approvers
67 WHERE arc_appr_for_entity_code = p_approval_entity
68 AND appr_for_entity_id = p_referral_id
69 FOR UPDATE NOWAIT;
70
71 l_message_name VARCHAR2(30);
72 l_partner_id NUMBER;
73 l_log_params_tbl pvx_utility_pvt.log_params_tbl_type;
74 l_approver_name varchar2(50);
75 l_return_status VARCHAR2(30);
76 l_msg_count NUMBER;
77 l_msg_data VARCHAR2(1000);
78
79 l_appr_usr_id NUMBER;
80 l_isDefAppr BOOLEAN := false;
81 l_appr_status VARCHAR2(30);
82 l_benefit_id NUMBER;
83 l_benefit_type_code VARCHAR2(30);
84
85 l_approval_completed VARCHAR2(10);
86 l_default_approver VARCHAR2(10);
87
88 approverUserIds JTF_NUMBER_TABLE;
89 l_valid_users_flag VARCHAR2(1);
90
91 BEGIN
92
93 -- ********* Start Standard Initializations *******
94 SAVEPOINT START_APPROVAL_PROCESS;
95
96 -- Standard call to check for call compatibility.
97 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
98 p_api_version_number,
99 l_api_name,
100 'PV_AME_API_W') THEN
101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
102 END IF;
103
107 fnd_msg_pub.initialize;
104 -- Initialize message list if p_init_msg_list is set to TRUE.
105 IF FND_API.to_Boolean( p_init_msg_list )
106 THEN
108 END IF;
109
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111 -- ********* End Standard Initializations *********
112 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
113 FND_LOG.MESSAGE(FND_LOG.LEVEL_PROCEDURE
114 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
115 ,FALSE
116 );
117 END IF;
118
119
120 IF p_approval_entity not in ('PVREFFRL','PVDEALRN','PVDQMAPR') THEN
121 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
122 fnd_message.Set_Token('TEXT', 'Invalid Approval Entity: ' || p_approval_entity);
123 fnd_msg_pub.Add;
124 RAISE FND_API.g_exc_error;
125 END IF;
126
127 -- This is to make sure that no other thread of execution
128 -- can try to update the rows for this referrral in
129 -- pv_ge_temp_approvers.
130 -- Bug 4628929
131 OPEN lc_lock_approvals;
132
133 OPEN lc_referral_info(p_referral_id);
134 FETCH lc_referral_info INTO l_partner_id, l_benefit_id, l_benefit_type_code;
135 IF lc_referral_info%NOTFOUND THEN
136 l_partner_id := p_partner_id;
137 l_benefit_type_code := p_approval_entity;
138 END IF;
139 CLOSE lc_referral_info;
140
141
142 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
144 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
145 ,'After Getting partner Info..partner id:'|| l_partner_id ||
146 ' benefit type:' || l_benefit_type_code
147 );
148 END IF;
149
150
151 IF p_change_cntry_flag = 'Y' THEN
152
153 OPEN lc_get_approver_name(pc_user_id => FND_GLOBAL.USER_ID);
154 FETCH lc_get_approver_name INTO l_approver_name;
155 CLOSE lc_get_approver_name;
156
157 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
158 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
159 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
160 ,'Approver name is: ' || l_approver_name);
161
162 END IF;
163
164 IF p_approval_entity = 'PVREFFRL' THEN
165 l_message_name := 'PV_LG_REF_COUNTRY_CHANGE';
166 ELSIF p_approval_entity = 'PVDEALRN' THEN
167 l_message_name := 'PV_LG_DEAL_COUNTRY_CHANGE';
168 END IF;
169
170 l_log_params_tbl(1).param_name := 'COUNTRY';
171 l_log_params_tbl(1).param_value := p_country_code;
172 l_log_params_tbl(2).param_name := 'APPROVER';
173 l_log_params_tbl(2).param_value := l_approver_name;
174
175 update pv_referrals_b set customer_country = p_country_code where referral_id = p_referral_id;
176
177 PVX_Utility_PVT.create_history_log(
178 p_arc_history_for_entity_code => p_approval_entity,
179 p_history_for_entity_id => p_referral_id,
180 p_history_category_code => 'GENERAL',
181 p_message_code => l_message_name,
182 p_partner_id => l_partner_id,
183 p_access_level_flag => 'V',
184 p_interaction_level => pvx_utility_pvt.G_INTERACTION_LEVEL_10,
185 p_comments => NULL,
186 p_log_params_tbl => l_log_params_tbl,
187 x_return_status => l_return_status,
188 x_msg_count => l_msg_count,
189 x_msg_data => l_msg_data);
190
191 END IF;
192
193 AME_API2.clearAllApprovals(applicationIdIn => 691,
194 transactionTypeIn => p_approval_entity,
195 transactionIdIn => p_referral_id);
196
197 UPDATE pv_ge_temp_approvers
198 SET approval_status_code = 'PRIOR_APPROVER'
199 WHERE arc_appr_for_entity_code = p_approval_entity
200 AND appr_for_entity_id = p_referral_id;
201
202 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
203 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
204 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
205 ,'B4 GET_APPROVERS....'
206 );
207 END IF;
208
209 approverUserIds := JTF_NUMBER_TABLE();
210
211 GET_APPROVERS(p_approval_entity => p_approval_entity
212 ,p_referral_id => p_referral_id
213 ,p_mode => 'START'
214 ,x_approval_list => approverUserIds
215 ,x_approval_completed => l_approval_completed
216 ,x_default_approver => l_default_approver
217 ,x_user_id_exists => l_valid_users_flag
218 );
219
220 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
221
222 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
223 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
224 ,'After GET_APPROVERS:l_approval_completed ' || l_approval_completed ||
225 ' l_default_approver ' || l_default_approver
226 );
227 END IF;
228
229
230 /**
231 * This loop makes sure that if an approver from AME already exists in pv_ge_temp_approvers
232 * then that approver must be removed from pv_ge_temp_approvers so that he/she is not added
233 * again into the table.
234 */
235 FOR l_prior_appr IN lc_prior_approvers
236 LOOP
237 FOR x IN 1..approverUserIds.COUNT
238 LOOP
239 IF ( approverUserIds(x) = l_prior_appr.APPROVER_ID) THEN
240 DELETE FROM pv_ge_temp_approvers
241 WHERE arc_appr_for_entity_code = p_approval_entity
242 AND appr_for_entity_id = p_referral_id
243 AND approver_id = approverUserIds(x);
244 END IF;
245 END LOOP;
246 END LOOP;
247
248 IF l_default_approver = 'Y' THEN
249 l_appr_status := 'PENDING_DEFAULT';
250
251 IF approverUserIds(1) IS NULL THEN
252 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
253 fnd_message.Set_Token('TEXT', 'Could not find approver in either AME or profile');
254 fnd_msg_pub.Add;
255 RAISE FND_API.G_EXC_ERROR;
256 END IF;
257 ELSE
258 l_appr_status := 'PENDING_APPROVAL';
259 END IF;
260
261 BEGIN
262 FORALL i IN 1..approverUserIds.COUNT
263 INSERT INTO pv_ge_temp_approvers
264 (
265 ENTITY_APPROVER_ID
266 ,OBJECT_VERSION_NUMBER
267 ,ARC_APPR_FOR_ENTITY_CODE
268 ,APPR_FOR_ENTITY_ID
269 ,APPROVER_ID
270 ,APPROVER_TYPE_CODE
271 ,APPROVAL_STATUS_CODE
272 ,WORKFLOW_ITEM_KEY
273 ,CREATED_BY
274 ,CREATION_DATE
275 ,LAST_UPDATED_BY
276 ,LAST_UPDATE_DATE
277 ,LAST_UPDATE_LOGIN
278 )
279 VALUES
280 (
281 pv_ge_temp_approvers_s.NEXTVAL
282 ,1
283 ,p_approval_entity
284 ,p_referral_id
285 ,approverUserIds(i)
286 ,'USER'
287 ,l_appr_status
288 ,null
289 ,FND_GLOBAL.USER_ID
290 ,sysdate
291 ,FND_GLOBAL.USER_ID
292 ,sysdate
293 ,FND_GLOBAL.LOGIN_ID
294 );
295 EXCEPTION
296 WHEN others THEN
297 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
298 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
299 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
300 ,'Bad row index = ' || (1 +sql%rowcount) ||' ' || sqlerrm
301 );
302 END IF;
303 END;
304
305 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
306 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
307 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
308 ,'Sucessfully inserted users into pv_ge_temp_approvers...'
309 );
310 END IF;
311
312 IF p_change_cntry_flag = 'Y' THEN
313 -- Invoke notification API. This is to notify that the country has been changed
314 -- So even thought there is no change in status i.e. it is still in SUBMITTED_FOR_APPROVAL
315 -- there was an event of changing country which may have caused Approvers to change.
316 -- So notification is called explicitly.
317
318 PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_notification(p_api_version_number => 1.0
319 ,p_init_msg_list => FND_API.G_FALSE
320 ,p_commit => FND_API.G_FALSE
321 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
322 ,P_BENEFIT_ID => l_benefit_id
323 ,P_STATUS => 'SUBMITTED_FOR_APPROVAL'
324 ,P_ENTITY_ID => p_referral_id
325 ,P_PARTNER_ID => l_partner_id
326 ,p_user_callback_api => 'PV_BENFT_STATUS_CHANGE.REFERRAL_RETURN_USERLIST'
327 ,p_msg_callback_api => 'PV_BENFT_STATUS_CHANGE.REFERRAL_SET_MSG_ATTRS'
328 ,p_user_role => 'BENEFIT_APPROVER'
329 ,x_return_status => l_return_status
330 ,x_msg_count => l_msg_count
331 ,x_msg_data => l_msg_data);
332
333 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
334 RAISE FND_API.G_EXC_ERROR;
335 END IF;
336
337 -- Fix for Bug 5689433.
338 l_message_name := null;
339
340 IF p_approval_entity = 'PVREFFRL' then
341 l_message_name := 'PV_LG_REF_REQR_APPRVD_BY_USER';
342 ELSIF p_approval_entity = 'PVDEALRN' then
343 l_message_name := 'PV_LG_DEAL_REQR_APPRVD_BY_USER';
344 ELSIF p_approval_entity = 'PVDQMAPR' then
345 l_message_name := 'PV_LG_DQM_REQR_DEDUP_BY_USER';
346 END IF;
347
348 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
349 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
350 ,'pv.plsql.PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_LOGGING.start'
351 ,'Approvers notification Message:'||l_message_name);
352 end if;
353
354 FOR apprCnt IN 1..approverUserIds.COUNT
355 LOOP
356
357 IF l_message_name IS NOT NULL THEN
358
359 l_log_params_tbl.DELETE;
360 FOR x in (SELECT source_name FROM jtf_rs_resource_extns B WHERE user_id = approverUserIds(apprCnt) )
361 LOOP
362 l_log_params_tbl(1).param_value := x.source_name;
363 END LOOP;
364
365 l_log_params_tbl(1).param_name := 'APPROVER';
366
367 PVX_Utility_PVT.create_history_log(
368 p_arc_history_for_entity_code => l_benefit_type_code,
369 p_history_for_entity_id => p_referral_id,
373 p_access_level_flag => 'V',
370 p_history_category_code => 'GENERAL',
371 p_message_code => l_message_name,
372 p_partner_id => l_partner_id,
374 p_interaction_level => pvx_utility_pvt.G_INTERACTION_LEVEL_10,
375 p_comments => NULL,
376 p_log_params_tbl => l_log_params_tbl,
377 x_return_status => l_return_status,
378 x_msg_count => l_msg_count,
379 x_msg_data => l_msg_data);
380
381 END IF;
382
383 END LOOP;
384
385 END IF;
386
387 CLOSE lc_lock_approvals;
388 -- Standard call to get message count and if count is 1, get message info.
389 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
390 p_count => x_msg_count,
391 p_data => x_msg_data);
392
393 EXCEPTION
394 WHEN FND_API.G_EXC_ERROR THEN
395
396 ROLLBACK TO START_APPROVAL_PROCESS;
397 x_return_status := FND_API.G_RET_STS_ERROR ;
398
399 fnd_msg_pub.Count_And_Get(
400 p_encoded => FND_API.G_FALSE,
401 p_count => x_msg_count,
402 p_data => x_msg_data);
403
404 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
405
406 ROLLBACK TO START_APPROVAL_PROCESS;
407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
408
409 fnd_msg_pub.Count_And_Get(
410 p_encoded => FND_API.G_FALSE,
411 p_count => x_msg_count,
412 p_data => x_msg_data);
413
414 WHEN g_concurrent_update THEN
415 fnd_message.Set_Name('PV', 'PV_REQUERY_THE_RECORD');
416 fnd_msg_pub.Add;
417 ROLLBACK TO UPDATE_APPROVER_RESPONSE;
418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
419 fnd_msg_pub.Count_And_Get(
420 p_encoded => FND_API.G_FALSE,
421 p_count => x_msg_count,
422 p_data => x_msg_data);
423
424 WHEN OTHERS THEN
425
426 ROLLBACK TO START_APPROVAL_PROCESS;
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428
429 FND_MSG_PUB.Add_Exc_Msg(
430 'PV_AME_API_W',
431 l_api_name);
432
433 fnd_msg_pub.Count_And_Get(
434 p_encoded => FND_API.G_FALSE,
435 p_count => x_msg_count,
436 p_data => x_msg_data);
437
438 END START_APPROVAL_PROCESS;
439
440
441 /********************************************************************************
442 This procedure starts the updates Responses given by approvers to the AME system.
443 *********************************************************************************/
444 PROCEDURE UPDATE_APPROVER_RESPONSE( p_api_version_number IN NUMBER
445 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
446 , p_commit IN VARCHAR2 := FND_API.G_FALSE
447 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
448 , p_referral_id IN NUMBER
449 , p_approval_entity IN VARCHAR2 -- PVREFFRL/PVDEALRN/PVDQMAPR
450 , p_response IN VARCHAR2 -- refer to AME_UTIL.approverIn
451 , p_approver_user_id IN NUMBER -- userID of the person sending approver resp
452 , p_forwardee_user_id IN NUMBER -- if forwarding then userID of the forwardee
453 , p_note_added_flag IN VARCHAR2 DEFAULT 'N' -- If note was added as part of this response.
454 , x_approval_done OUT NOCOPY VARCHAR2 -- True if approval process is finished False if not.
455 , x_return_status OUT NOCOPY VARCHAR2
456 , x_msg_count OUT NOCOPY NUMBER
457 , x_msg_data OUT NOCOPY VARCHAR2
458 ) IS
459
460 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_APPROVER_RESPONSE';
461 l_api_version_number CONSTANT NUMBER := 1.0;
462
463 l_forwardee ame_util.approverRecord2;
464 l_approver ame_util.approverRecord2;
465 l_approval_status VARCHAR2(30);
466 l_appr_usr_id NUMBER;
467 l_forwardee_user_id NUMBER;
468 l_benefit_id NUMBER;
469 l_partner_id NUMBER;
470 l_sec_lvl_reject BOOLEAN;
471 l_temp VARCHAR2(5);
472 l_isDefAppr BOOLEAN;
473 l_appr_status VARCHAR2(20);
474 l_log_params_tbl pvx_utility_pvt.log_params_tbl_type;
475 l_message_code VARCHAR2(30);
476 l_approver_name VARCHAR2(100);
477 l_approver_category VARCHAR2(30);
478 l_approver_source_id NUMBER;
479 l_return_status VARCHAR2(30);
480 l_msg_count NUMBER;
481 l_msg_data VARCHAR2(1000);
485 l_ret_reason_code VARCHAR2(30);
482 l_approver_current_status VARCHAR2(30);
483 l_pending_status VARCHAR2(30);
484 l_approverInList BOOLEAN := false;
486 l_return_note_id NUMBER;
487 l_user_name VARCHAR2(100);
488
489 l_approval_list JTF_NUMBER_TABLE;
490 l_default_approver VARCHAR2(10);
491 l_valid_users_flag VARCHAR2(1);
492 l_response_to_ame VARCHAR2(25);
493 l_resp_count NUMBER;
494 l_curr_appr_level NUMBER;
495
496 cursor lc_is_default_approver (pc_user_id number, pc_benefit_type varchar2, pc_entity_id number) is
497 select approval_status_code from pv_ge_temp_approvers
498 where arc_appr_for_entity_code = pc_benefit_type
499 and appr_for_entity_id = pc_entity_id
500 and approver_type_code = 'USER'
501 and approver_id = pc_user_id
502 and approval_status_code IN ('PENDING_APPROVAL','PENDING_DEFAULT');
503
504 cursor lc_get_approver_details (pc_user_id number) is
505 select decode(category, 'EMPLOYEE', source_id, null), category, source_name, user_name from jtf_rs_resource_extns where user_id = pc_user_id;
506
507 cursor lc_return_reason IS
508 select return_reason_code from pv_referrals_b
509 where referral_id = p_referral_id;
510
511 /**
512 * In case of referral return the note added by the user on the Notes
513 * region has to be added to the history log. This query finds that note
514 *
515 * It is mandatory to enter a note before returning a referral/deal
516 * So the last note created when a return action is submitted will have to be
517 * the note entered before returning the referral/deal. So this query
518 * sorts all the notes for this referral in desc order of entered date
519 * and picks up the first note.
520 */
521 cursor lc_return_note IS
522 select jtf_note_id
523 from (select jtf_note_id
524 from jtf_notes_vl
525 where source_object_id = p_referral_id
526 and source_object_code = p_approval_entity
527 order by entered_date desc)
528 where rownum = 1;
529
530 cursor lc_first_level_apporver is
531 select count(entity_approver_id)
532 from pv_ge_temp_approvers
533 where arc_appr_for_entity_code = p_approval_entity
534 and appr_for_entity_id = p_referral_id
535 and approval_status_code in ('APPROVED','REJECTED');
536
537 cursor lc_lock_approvals is
538 SELECT entity_approver_id
539 FROM pv_ge_temp_approvers
540 WHERE arc_appr_for_entity_code = p_approval_entity
541 AND appr_for_entity_id = p_referral_id
542 FOR UPDATE NOWAIT;
543
544 CURSOR lc_prior_approvers IS
545 SELECT APPROVER_ID
546 FROM PV_GE_TEMP_APPROVERS
547 WHERE ARC_APPR_FOR_ENTITY_CODE = p_approval_entity
548 AND APPR_FOR_ENTITY_ID = p_referral_id;
549
550
551 BEGIN
552
553 -- ********* Start Standard Initializations *******
554 SAVEPOINT UPDATE_APPROVER_RESPONSE;
555
556 -- Standard call to check for call compatibility.
557 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
558 p_api_version_number,
559 l_api_name,
560 'PV_AME_API_W') THEN
561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
562 END IF;
563
564 -- Initialize message list if p_init_msg_list is set to TRUE.
565 IF FND_API.to_Boolean( p_init_msg_list ) THEN
566 fnd_msg_pub.initialize;
567 END IF;
568
569 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
570 FND_LOG.MESSAGE(FND_LOG.LEVEL_PROCEDURE
571 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
572 ,FALSE
573 );
574 END IF;
575
576
577 x_return_status := FND_API.G_RET_STS_SUCCESS;
578 -- ********* End Standard Initializations *********
579
580 -- This is to make sure that no other thread of execution
581 -- can try to update the rows for this referrral in
582 -- pv_ge_temp_approvers.
583 -- Bug 4628929
584 OPEN lc_lock_approvals;
585
586 x_approval_done := 'N';
587
588 OPEN lc_get_approver_details(pc_user_id => p_approver_user_id);
589 FETCH lc_get_approver_details INTO l_approver_source_id, l_approver_category, l_approver_name, l_user_name;
590 CLOSE lc_get_approver_details;
591
592 IF p_response = AME_UTIL.forwardStatus THEN -- FORWARD
593
594 FOR x IN (SELECT employee_id,user_name FROM fnd_user WHERE user_id = p_forwardee_user_id
595 AND (end_date IS NULL OR end_date > sysdate-1))
596 LOOP
597 l_forwardee.orig_system_id := x.employee_id;
598 l_forwardee.name := x.user_name;
599 END LOOP;
600
601 -- Forward case. create a forwadee record
602 l_forwardee.orig_system := 'PER';
603 l_forwardee.approver_category := ame_util.approvalApproverCategory;
604
605 IF l_forwardee.orig_system_id IS NULL THEN
606 fnd_message.Set_Name('PV', 'PV_NO_PERSON_ERROR');
607 fnd_msg_pub.Add;
608 RAISE FND_API.g_exc_error;
609 END IF;
610
611 l_approval_status := 'FORWARDED';
612 IF p_approval_entity = 'PVREFFRL' then
613 l_message_code := 'PV_LG_REF_FORWARDED_BY_USER';
614 ELSIF p_approval_entity = 'PVDEALRN' then
618 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
615 l_message_code := 'PV_LG_DEAL_FORWARDED_BY_USER';
616 end if;
617
619 FND_LOG.MESSAGE(FND_LOG.LEVEL_PROCEDURE
620 ,'Forward case :b4 updateStatus call Forwardee ID :' || l_forwardee.orig_system_id||
621 ' l_forwardee.name '||l_forwardee.name,FALSE);
622 END IF;
623
624 ELSIF p_response = AME_UTIL.rejectStatus THEN
625
626 OPEN lc_first_level_apporver;
627 FETCH lc_first_level_apporver INTO l_resp_count;
628 CLOSE lc_first_level_apporver;
629
630 l_approval_status := 'REJECTED';
631 IF p_approval_entity = 'PVREFFRL' then
632 l_message_code := 'PV_LG_REF_REJECTED_BY_USER';
633 ELSIF p_approval_entity = 'PVDEALRN' then
634 l_message_code := 'PV_LG_DEAL_REJECTED_BY_USER';
635 end if;
636
637 ELSIF p_response = AME_UTIL.approvedStatus THEN
638
639 l_approval_status := 'APPROVED';
640 IF p_approval_entity = 'PVREFFRL' then
641 l_message_code := 'PV_LG_REF_APPRVD_BY_USER';
642 ELSIF p_approval_entity = 'PVDEALRN' then
643 l_message_code := 'PV_LG_DEAL_APPRVD_BY_USER';
644 end if;
645
646 ELSIF p_response = returnStatus THEN
647
648 l_approval_status := 'RETURNED';
649 IF p_approval_entity = 'PVREFFRL' then
650 l_message_code := 'PV_LG_REF_RETURNED_BY_USER';
651 ELSIF p_approval_entity = 'PVDEALRN' then
652 l_message_code := 'PV_LG_DEAL_RETURNED_BY_USER';
653 end if;
654
655 OPEN lc_return_reason;
656 FETCH lc_return_reason INTO l_ret_reason_code;
657 CLOSE lc_return_reason;
658
659 l_log_params_tbl(2).param_name := 'RETURN_REASON';
660 l_log_params_tbl(2).param_value := l_ret_reason_code;
661 l_log_params_tbl(2).param_type := 'LOOKUP';
662 l_log_params_tbl(2).param_lookup_type := 'PV_REFERRAL_RETURN_REASON';
663
664 IF ( p_note_added_flag = 'Y' ) THEN
665 OPEN lc_return_note;
666 FETCH lc_return_note INTO l_return_note_id;
667 CLOSE lc_return_note;
668 ELSE
669 l_return_note_id := -1;
670 END IF;
671
672 l_log_params_tbl(3).param_name := 'RETURN_NOTE';
673 l_log_params_tbl(3).param_value := l_return_note_id;
674 l_log_params_tbl(3).param_type := 'NOTE';
675
676 END IF;
677
678 -- If Approval Status was null it means that something other than
679 -- AME_UTIL.forwardStatus / AME_UTIL.rejectStatus / AME_UTIL.approvedStatus
680 -- was sent to the API. Hence error!!!
681 IF l_approval_status IS NULL THEN
682 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
683 fnd_message.Set_Token('TEXT', 'Possible error. Invalid : Response ' || p_response);
684 fnd_msg_pub.Add;
685 RAISE FND_API.g_exc_error;
686 END IF;
687
688 IF p_response = AME_UTIL.forwardStatus and p_forwardee_user_id is null then
689 fnd_message.Set_Name('PV', 'PV_REFERRAL_REASSIGN_APPROVER');
690 fnd_msg_pub.Add;
691 RAISE FND_API.g_exc_error;
692 END IF;
693
694 open lc_is_default_approver(pc_user_id => p_approver_user_id,
695 pc_benefit_type => p_approval_entity,
696 pc_entity_id => p_referral_id);
697 fetch lc_is_default_approver into l_approver_current_status;
698 close lc_is_default_approver;
699
700 IF l_approver_current_status is null then
701
702 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
703 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
704 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
705 ,'This user ' || p_approver_user_id || ' is not an approver for entity id: ' || p_referral_id
706 );
707 END IF;
708
709 END IF;
710
711 FOR x IN (SELECT partner_id FROM pv_referrals_b WHERE referral_id = p_referral_id) LOOP
712 l_partner_id := x.partner_id;
713 END LOOP;
714
715 -- Bug fix for bug 3495565. If the current approver gets removed from the
716 -- list of approvers then skip the call to updateApprover. Treat it as a
717 -- valid response as far as our system is concerned. This call is made to
718 -- find out current user is still in the AME System.
719
720 -- If the status is PENDING_DEFAULT then this approver did not come from
721 -- AME so there is no point checking AME for validity of the approver.
722 IF l_approver_current_status = 'PENDING_DEFAULT' THEN
723 l_approverInList := true;
724 ELSE
725 l_approverInList := VALIDATE_APPROVAL(p_transaction_id => p_referral_id
726 , p_transaction_type => p_approval_entity
727 , p_user_id => p_approver_user_id
728 , p_person_id => l_approver_source_id
729 , p_mode => 'CHECK_CURRENT_APPROVER'
730 , p_approval_level => null
731 , x_approver => l_approver);
732 END IF;
733
734
735 if l_message_code is not null then -- not logging for DQM approval
736
737 l_log_params_tbl(1).param_name := 'APPROVER';
741 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
738 l_log_params_tbl(1).param_value := l_approver_name;
739
740 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
742 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
743 ,'Logging approver response message: ' || l_message_code
744 );
745 END IF;
746
747
748 PVX_Utility_PVT.create_history_log(
749 p_arc_history_for_entity_code => p_approval_entity,
750 p_history_for_entity_id => p_referral_id,
751 p_history_category_code => 'GENERAL',
752 p_message_code => l_message_code,
753 p_partner_id => l_partner_id,
754 p_access_level_flag => 'V',
755 p_interaction_level => pvx_utility_pvt.G_INTERACTION_LEVEL_10,
756 p_comments => NULL,
757 p_log_params_tbl => l_log_params_tbl,
758 x_return_status => l_return_status,
759 x_msg_count => l_msg_count,
760 x_msg_data => l_msg_data);
761
762 if L_return_status <> FND_API.G_RET_STS_SUCCESS then
763 raise FND_API.G_EXC_ERROR;
764 end if;
765
766 end if;
767
768 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
769 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
770 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
771 ,'p_response ' || p_response);
772 END IF;
773
774 -- In case of forward even if the approver is not in the list
775 -- an error will be thrown. In the other case i.e approve / reject
776 -- the AME update call is circumvented
777 IF (NOT l_approverInList AND (p_response = AME_UTIL.forwardStatus)) THEN
778
779 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
780 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
781 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
782 ,' The approver is NOT in the list. p_response ' || p_response);
783 END IF;
784
785 FND_MESSAGE.Set_Name('PV', 'PV_REFERRAL_REASSIGN_ERROR');
786 FND_MSG_PUB.Add;
787 RAISE FND_API.G_EXC_ERROR;
788
789 END IF;
790
791
792 IF (l_approverInList) THEN
793
794 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
795 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
796 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
797 ,'Current User is in AME system. Sending update reponse to AME...');
798 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
799 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
800 ,'User name l_user_name '|| l_user_name ||
801 ' FND_GLOBAL.USER_NAME '|| FND_GLOBAL.USER_NAME
802 );
803
804 END IF;
805 IF l_approver_current_status = 'PENDING_APPROVAL' THEN
806
807 IF p_response = AME_UTIL.forwardStatus THEN
808
809 IF (l_approver.authority = ame_util.authorityApprover AND
810 (l_approver.api_insertion = ame_util.apiAuthorityInsertion
811 OR l_approver.api_insertion = ame_util.oamGenerated) ) THEN
812
813 l_forwardee.api_insertion := ame_util.apiAuthorityInsertion;
814
815 ELSE
816
817 l_forwardee.api_insertion := ame_util.apiInsertion;
818
819 END IF;
820
821 l_forwardee.authority := l_approver.authority;
822
823 END IF;
824
825
826 /*
827 * AME does not have the concept of RETURNing a transaction during approval
828 * but PRM does. As far as AME is concerned a return is equivalent to a
829 * Rejection. So we pass AME_UTIL.rejectStatus to AME in case of a RETURN
830 */
831 IF p_response = returnStatus THEN
832 l_response_to_ame := AME_UTIL.rejectStatus;
833 ELSE
834 l_response_to_ame := p_response;
835 END IF;
836
837 ame_api2.updateApprovalStatus2
838 (applicationIdIn => 691
839 , transactionTypeIn => p_approval_entity
840 , transactionIdIn => p_referral_id
841 , approvalStatusIn => l_response_to_ame
842 , approverNameIn => l_user_name
843 , itemClassIn => null
844 , itemIdIn => null
845 , actionTypeIdIn => null
846 , groupOrChainIdIn => null
847 , occurrenceIn => null
848 , forwardeeIn => l_forwardee
849 , updateItemIn => null
850 );
851
852 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
853 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
854 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
855 ,'After AME_API.updateApprovalStatus2 call...'
856 );
857 END IF;
858
859 END IF;
860
861 ELSE
862
863 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
864 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
865 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
866 ,'Current User is no more in the AME System as an approver. AME was not updated...'
867 );
868 END IF;
869
870 END IF; -- If current user is in AME system.
871
872 -- Update pv_ge_temp_approvers to set the values of approval status to
873 -- APPROVED/REJECTED/FORWARDED depending on the case for the approver id who
874 -- took the action.
875 UPDATE pv_ge_temp_approvers
876 SET approval_status_code = l_approval_status
877 WHERE arc_appr_for_entity_code = p_approval_entity
878 AND appr_for_entity_id = p_referral_id
879 AND approver_id = p_approver_user_id
880 AND approval_status_code IN ('PENDING_APPROVAL','PENDING_DEFAULT')
881 AND rownum = 1;
882
883 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
884 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
885 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
886 ,'Set the approval status for '|| p_approval_entity || ', ' || p_referral_id ||
887 'User id: ' || p_approver_user_id || ' to '||l_approval_status
888 );
889 END IF;
890
891
892 IF p_response = AME_UTIL.approvedStatus or p_response = AME_UTIL.forwardStatus THEN
893
894 IF p_approval_entity = 'PVDQMAPR' THEN
895 x_approval_done := 'Y';
896
897 /* In case of DQM approval the first response is the only response
898 * that counts. So regardless of whether the approval is of type
899 * first responder wins / serial / consensus/order number
900 * the first person to respond closes the DQM approval process and
904
901 * all others will be marked as PEER_RESPONDED
902 */
903 IF l_approver_current_status = 'PENDING_APPROVAL' THEN
905 UPDATE pv_ge_temp_approvers
906 SET approval_status_code = 'PEER_RESPONDED'
907 WHERE arc_appr_for_entity_code = p_approval_entity
908 AND appr_for_entity_id = p_referral_id
909 AND approval_status_code IN ('PENDING_APPROVAL');
910
911 END IF;
912
913 ELSE
914
915 -- If it is PENDING_APPROVAL only then do we go to next level of
916 -- Approval. If it is PENDING_DEFAULT then that level is
917 -- considered the last level of approval
918 IF l_approver_current_status = 'PENDING_APPROVAL' THEN
919
920 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
921 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
922 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
923 ,'B4 getNextApprover....'
924 );
925 END IF;
926
927 BEGIN
928 GET_APPROVERS(p_approval_entity => p_approval_entity
929 ,p_referral_id => p_referral_id
930 ,p_mode => 'UPDATE'
931 ,x_approval_list => l_approval_list
932 ,x_approval_completed => x_approval_done
933 ,x_default_approver => l_default_approver
934 ,x_user_id_exists => l_valid_users_flag);
935 EXCEPTION
936 WHEN OTHERS THEN
937 -- log error message by AME
938 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
939 FND_MSG_PUB.Add_Exc_Msg('PV_AME_API_W',l_api_name);
940 END IF;
941 RAISE;
942 END;
943
944 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
945 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
946 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
947 ,'After getNextApprover....l_approval_list '||l_approval_list.COUNT ||
948 'After getNextApprover....x_approval_done '||x_approval_done||
949 'After getNextApprover....l_default_approver '||l_default_approver ||
950 'After getNextApprover....l_valid_users_flag '||l_valid_users_flag
951 );
952 END IF;
953
954 IF ( l_valid_users_flag = 'N' ) THEN
955 FND_MESSAGE.Set_Name('PV', 'PV_NO_USER_FOR_PERSON_ERROR');
956 FND_MSG_PUB.Add;
957 RAISE FND_API.g_exc_error;
958 END IF;
959
960
961 IF ((l_approval_list IS NULL OR l_approval_list.COUNT < 1)
962 AND p_response = AME_UTIL.forwardStatus)
963 THEN
964
965 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
966 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
967 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
968 ,'Even After forwarding l_approval_list is '||l_approval_list.COUNT);
969 END IF;
970
971 FND_MESSAGE.Set_Name('PV', 'PV_REFERRAL_NO_FORWARDEE');
972 FND_MSG_PUB.Add;
973 RAISE FND_API.g_exc_error;
974 END IF;
975
976 DEL_PRIOR_REP_APPR(p_approval_entity => p_approval_entity
977 ,p_referral_id => p_referral_id
978 ,p_approval_list => l_approval_list);
979
980 l_curr_appr_level := l_approver.approver_order_number; -- Bug 5256368 (Consensus Issue)
981
982 /*
983 * We need to set the approvers who did not respond to PEER_RESPONDED.
984 * However if there are any approvers who are yet to approve in AME we
985 * cannot set their status to PEER_RESPONDED. This case will occur in case
986 * of CONSENSUS where multiple approvers need to respond before the entity
987 * is APPROVED.
988 * So if approvers have been returned from AME check for consensus case and
989 * only then update current approvers to PEER_APPROVED
990 *
991 * Bug fix 5256368: Remove approver from FRW case. If the current approver
992 * is no longer in the list then updates to other approvers rows is not to
993 * be allowed.
994 */
995 IF (NOT VALIDATE_APPROVAL(p_transaction_id => p_referral_id
996 , p_transaction_type => p_approval_entity
997 , p_user_id => p_approver_user_id
998 , p_person_id => l_approver_source_id
999 , p_mode => 'CHECK_PENDING_APPROVERS'
1000 , p_approval_level => l_curr_appr_level
1001 , x_approver => l_approver) AND l_approverInList)
1002 THEN
1003 UPDATE pv_ge_temp_approvers
1004 SET approval_status_code = 'PEER_RESPONDED'
1005 WHERE arc_appr_for_entity_code = p_approval_entity
1006 AND appr_for_entity_id = p_referral_id
1010 l_pending_status := 'PENDING_APPROVAL';
1007 AND approval_status_code = 'PENDING_APPROVAL';
1008 END IF;
1009
1011
1012 ELSE
1013
1014 -- l_approver_current_status = 'PENDING_DEFAULT'
1015 IF p_response = AME_UTIL.forwardStatus THEN
1016 x_approval_done := 'N';
1017 l_approval_list := JTF_NUMBER_TABLE();
1018 l_approval_list.EXTEND();
1019 l_approval_list(1) := p_forwardee_user_id;
1020 l_pending_status := 'PENDING_DEFAULT';
1021
1022 DEL_PRIOR_REP_APPR(p_approval_entity => p_approval_entity
1023 ,p_referral_id => p_referral_id
1024 ,p_approval_list => l_approval_list);
1025
1026 ELSE
1027 x_approval_done := 'Y';
1028 END IF;
1029 END IF;
1030
1031
1032 IF l_approval_list IS NOT NULL AND l_approval_list.COUNT > 0
1033 THEN
1034 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1035 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1036 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
1037 ,'Inserting users into pv_ge_temp_approvers...'
1038 );
1039 END IF;
1040
1041 BEGIN
1042 FORALL i IN 1..l_approval_list.COUNT
1043
1044
1045 INSERT INTO pv_ge_temp_approvers(
1046 ENTITY_APPROVER_ID
1047 ,OBJECT_VERSION_NUMBER
1048 ,ARC_APPR_FOR_ENTITY_CODE
1049 ,APPR_FOR_ENTITY_ID
1050 ,APPROVER_ID
1051 ,APPROVER_TYPE_CODE
1052 ,APPROVAL_STATUS_CODE
1053 ,WORKFLOW_ITEM_KEY
1054 ,CREATED_BY
1055 ,CREATION_DATE
1056 ,LAST_UPDATED_BY
1057 ,LAST_UPDATE_DATE
1058 ,LAST_UPDATE_LOGIN
1059 )VALUES(
1060 pv_ge_temp_approvers_s.NEXTVAL
1061 ,1
1062 ,p_approval_entity
1063 ,p_referral_id
1064 ,l_approval_list(i)
1065 ,'USER'
1066 ,l_pending_status
1067 ,null
1068 ,FND_GLOBAL.USER_ID
1069 ,sysdate
1070 ,FND_GLOBAL.USER_ID
1071 ,sysdate
1072 ,FND_GLOBAL.LOGIN_ID
1073 );
1074
1075 EXCEPTION
1076 WHEN others THEN
1077 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1078 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1079 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
1080 ,'Bad row index = ' || (1 +sql%rowcount) ||' ' || sqlerrm
1081 );
1082 END IF;
1083
1084 END;
1085
1086 IF p_approval_entity = 'PVREFFRL' then
1087 l_message_code := 'PV_LG_REF_REQR_APPRVD_BY_USER';
1088 ELSIF p_approval_entity = 'PVDEALRN' then
1089 l_message_code := 'PV_LG_DEAL_REQR_APPRVD_BY_USER';
1090 END IF;
1091
1092 FOR apprCnt IN 1..l_approval_list.COUNT
1093 LOOP
1094
1095 FOR x in (SELECT source_name FROM jtf_rs_resource_extns B WHERE user_id = l_approval_list(apprCnt) )
1096 LOOP
1097 l_log_params_tbl(1).param_value := x.source_name;
1098 END LOOP;
1099
1100 l_log_params_tbl(1).param_name := 'APPROVER';
1101
1102 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1103 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1104 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
1105 ,'Logging who the next approver is: ' || l_approver_name
1106 );
1107 END IF;
1108
1109 PVX_Utility_PVT.create_history_log(
1110 p_arc_history_for_entity_code => p_approval_entity,
1111 p_history_for_entity_id => p_referral_id,
1112 p_history_category_code => 'GENERAL',
1113 p_message_code => l_message_code,
1114 p_partner_id => l_partner_id,
1115 p_access_level_flag => 'V',
1116 p_interaction_level => pvx_utility_pvt.G_INTERACTION_LEVEL_10,
1117 p_comments => NULL,
1118 p_log_params_tbl => l_log_params_tbl,
1119 x_return_status => l_return_status,
1120 x_msg_count => l_msg_count,
1121 x_msg_data => l_msg_data);
1122
1123 END LOOP;
1124 END IF; -- If Approval is done
1125 END IF; -- Id it is PENDING_APPROVAL
1126
1130 -- referral approval process is not to be restarted. Conversely, in all cases of
1127 ELSIF p_response = AME_UTIL.rejectStatus THEN -- never the case for DQM
1128
1129 -- In Case of DECLINE the response is rejectStatus. In this case however the
1131 -- rejectStatus response other than DECLINED the approval process is restarted.
1132 IF l_resp_count > 0 THEN
1133 -- When an approval is Reject the approval process has to be restarted
1134 -- as if it is being approved for the first time again.
1135 --
1136 -- So the referral goes back to SUBMITTED_FOR_APPROVAL and new approvers
1137 -- are notified.
1138
1139 BEGIN
1140
1141 -- restart approval process
1142 START_APPROVAL_PROCESS(p_api_version_number => 1.0
1143 ,p_init_msg_list => FND_API.G_FALSE
1144 ,p_commit => FND_API.G_FALSE
1145 ,p_validation_level => 90 --fnd_api.g_valid_level_full,
1146 ,p_referral_id => p_referral_id
1147 ,p_change_cntry_flag => 'N'
1148 ,p_country_code => NULL
1149 ,p_approval_entity => p_approval_entity
1150 ,x_return_status => l_return_status
1151 ,x_msg_count => l_msg_count
1152 ,x_msg_data => l_msg_data);
1153
1154 IF L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1155 RAISE FND_API.G_EXC_ERROR;
1156 END IF;
1157
1158 x_approval_done := 'N';
1159 EXCEPTION
1160 WHEN no_data_found THEN
1161 x_approval_done := 'Y';
1162 WHEN OTHERS THEN
1163 RAISE;
1164 END;
1165
1166 END IF;
1167
1168 END IF; -- End of REJECTED CASE
1169
1170 CLOSE lc_lock_approvals;
1171
1172 IF x_approval_done <> 'Y' THEN
1173 -- Send Notification
1174 FOR x IN (SELECT partner_id ,benefit_id FROM pv_referrals_b WHERE referral_id = p_referral_id) LOOP
1175 l_benefit_id := x.benefit_id;
1176 l_partner_id := x.partner_id;
1177 END LOOP;
1178
1179 PV_BENFT_STATUS_CHANGE.STATUS_CHANGE_notification(p_api_version_number => 1.0
1180 ,p_init_msg_list => FND_API.G_FALSE
1181 ,p_commit => FND_API.G_FALSE
1182 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1183 ,P_BENEFIT_ID => l_benefit_id
1184 ,P_STATUS => 'SUBMITTED_FOR_APPROVAL'
1185 ,P_ENTITY_ID => p_referral_id
1186 ,P_PARTNER_ID => l_partner_id
1187 ,p_user_callback_api => 'PV_BENFT_STATUS_CHANGE.REFERRAL_RETURN_USERLIST'
1188 ,p_msg_callback_api => 'PV_BENFT_STATUS_CHANGE.REFERRAL_SET_MSG_ATTRS'
1189 ,p_user_role => 'BENEFIT_APPROVER'
1190 ,x_return_status => l_return_status
1191 ,x_msg_count => l_msg_count
1192 ,x_msg_data => l_msg_data);
1193
1194 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1195 RAISE FND_API.G_EXC_ERROR;
1196 END IF;
1197 END IF;
1198
1199 -- Standard call to get message count and if count is 1, get message info.
1200 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1201 p_count => x_msg_count,
1202 p_data => x_msg_data);
1203
1204 EXCEPTION
1205
1206 WHEN FND_API.G_EXC_ERROR THEN
1207
1208 ROLLBACK TO UPDATE_APPROVER_RESPONSE;
1209 x_return_status := FND_API.G_RET_STS_ERROR ;
1210
1211 fnd_msg_pub.Count_And_Get(
1212 p_encoded => FND_API.G_FALSE,
1213 p_count => x_msg_count,
1214 p_data => x_msg_data);
1215
1216 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1217
1218 ROLLBACK TO UPDATE_APPROVER_RESPONSE;
1219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1220
1221 fnd_msg_pub.Count_And_Get(
1222 p_encoded => FND_API.G_FALSE,
1223 p_count => x_msg_count,
1224 p_data => x_msg_data);
1225
1226 WHEN g_concurrent_update THEN
1227 fnd_message.Set_Name('PV', 'PV_REQUERY_THE_RECORD');
1228 fnd_msg_pub.Add;
1229
1230 ROLLBACK TO UPDATE_APPROVER_RESPONSE;
1231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1232 fnd_msg_pub.Count_And_Get(
1233 p_encoded => FND_API.G_FALSE,
1234 p_count => x_msg_count,
1235 p_data => x_msg_data);
1236
1237 WHEN OTHERS THEN
1238
1239 ROLLBACK TO UPDATE_APPROVER_RESPONSE;
1240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1241
1242 FND_MSG_PUB.Add_Exc_Msg('PV_AME_API_W', l_api_name);
1243
1244 fnd_msg_pub.Count_And_Get(
1245 p_encoded => FND_API.G_FALSE,
1246 p_count => x_msg_count,
1247 p_data => x_msg_data);
1248
1249 END UPDATE_APPROVER_RESPONSE;
1250
1251 /*
1255 * - CHECK_CURRENT_APPROVER: Validate if the current logged in user is a
1252 * This function queries for the latest set of approvers pending approval
1253 * in AME. With the list of approvers returned by AME we can perform certain
1254 * validations.
1256 * a valid approver in AME.
1257 * - CHECK_PENDING_APPROVERS: Check if AME is waiting for response from any of
1258 * the current set of approvers in pv_ge_temp_approvers. (Consensus case)
1259 */
1260 FUNCTION VALIDATE_APPROVAL (p_transaction_id IN NUMBER
1261 , p_transaction_type IN VARCHAR2
1262 , p_user_id IN NUMBER
1263 , p_person_id IN NUMBER
1264 , p_mode IN VARCHAR2
1265 , p_approval_level IN NUMBER
1266 , x_approver OUT NOCOPY ame_util.approverRecord2)
1267 RETURN BOOLEAN IS
1268
1269 CURSOR c_user(pc_person_id NUMBER) IS
1270 SELECT 'Y'
1271 FROM fnd_user A , pv_ge_temp_approvers appr
1272 WHERE A.employee_id = pc_person_id
1273 AND ( A.end_date IS NULL OR A.end_date > sysdate-1)
1274 AND A.user_id = appr.approver_id
1275 AND appr.approval_status_code = 'PENDING_APPROVAL'
1276 AND appr.APPR_FOR_ENTITY_ID = p_transaction_id
1277 AND appr.ARC_APPR_FOR_ENTITY_CODE = p_transaction_type;
1278
1279 l_is_valid BOOLEAN := false;
1280 l_usr_resp_pending VARCHAR2(1) := 'N';
1281 l_approversOut ame_util.approversTable;
1282
1283 x_approvalProcessCompleteYNOut VARCHAR2(10);
1284 x_nextApproversOut ame_util.approversTable2; -- New API approverOut
1285 currApprRec ame_util.approverRecord2;
1286
1287 xitemIndexesOut ame_util.idList;
1288 xitemClassesOut ame_util.stringList;
1289 xitemIdsOut ame_util.stringList;
1290 xitemSourcesOut ame_util.longStringList;
1291
1292 BEGIN
1293 -- get all the approver list and loop till you find the matching
1294 -- and set the flag to true if you find any.
1295 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1296 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1297 ,'pv.plsql.' || g_pkg_name || '.VALIDATE_APPROVAL'
1298 ,'before getPendingApprovers ' || p_transaction_id ||
1299 ' '|| p_transaction_type || ' p_user_id ' || p_user_id ||
1300 ' p_person_id '|| p_person_id||' p_mode '|| p_mode ||
1301 ' p_approval_level '|| p_approval_level
1302 );
1303 END IF;
1304
1305
1306 ame_api2.getPendingApprovers(applicationIdIn => 691,
1307 transactionTypeIn => p_transaction_type,
1308 transactionIdIn => p_transaction_id,
1309 approvalProcessCompleteYNOut => x_approvalProcessCompleteYNOut,
1310 approversOut => x_nextApproversOut);
1311
1312 IF ( p_mode = 'CHECK_CURRENT_APPROVER' ) THEN
1313
1314 FOR i IN 1..x_nextApproversOut.count LOOP
1315 currApprRec := x_nextApproversOut(i);
1316
1317 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1318 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1319 ,'pv.plsql.' || g_pkg_name || '.VALIDATE_APPROVAL'
1320 ,'currApprRec.orig_system_id ' || currApprRec.orig_system_id
1321 );
1322 END IF;
1323
1324 IF (p_person_id = currApprRec.orig_system_id) THEN
1325 l_is_valid := true;
1326 x_approver := currApprRec;
1327 EXIT;
1328 END IF;
1329 END LOOP;
1330
1331 ELSIF ( p_mode = 'CHECK_PENDING_APPROVERS' ) THEN
1332
1333 FOR i IN 1..x_nextApproversOut.count LOOP
1334 currApprRec := x_nextApproversOut(i);
1335
1336 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1337 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1338 ,'pv.plsql.' || g_pkg_name || '.VALIDATE_APPROVAL'
1339 ,'currApprRec.orig_system_id ' || currApprRec.orig_system_id||
1340 'currApprRec.orig_system_id ' || currApprRec.approver_order_number
1341 );
1342 END IF;
1343
1344 /**
1345 * Consensus case is true if there are person/persons in AME
1346 * at the same approval level as the person currently approving
1347 * e.g. Say the approval process looks like this
1348 * Level 1 : A, B
1349 * Level 2 : A
1350 * A level 1 and A level 2 are distinct. If level 1 is consensus and B
1351 * approves the A returned by getPendingApprovers will have order number as 1
1352 * as opposed to FRW case where A will have an order number 2 since it will be
1353 * the A from level 2.
1354 */
1355 IF ( p_approval_level = currApprRec.approver_order_number )
1356 THEN
1357 OPEN c_user(currApprRec.orig_system_id);
1358 FETCH c_user INTO l_usr_resp_pending;
1359 CLOSE c_user;
1360
1361 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1362 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1363 ,'pv.plsql.' || g_pkg_name || '.VALIDATE_APPROVAL'
1367
1364 ,'RESPONSES PENDING ? l_usr_resp_pending ' || l_usr_resp_pending
1365 );
1366 END IF;
1368 IF ( l_usr_resp_pending = 'Y' ) THEN
1369 l_is_valid := true;
1370 EXIT;
1371 END IF;
1372 END IF;
1373 END LOOP;
1374 END IF;
1375
1376 RETURN l_is_valid;
1377
1378 EXCEPTION
1379 WHEN OTHERS THEN
1380 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1381 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1382 ,'pv.plsql.' || g_pkg_name || '.VALIDATE_APPROVAL'
1383 ,'Error in getPendingApprovers '||SQLCODE ||
1384 ': ' || SQLERRM);
1385 END IF;
1386 FND_MESSAGE.Set_Name('PV', 'PV_REFERRAL_APPROVAL_ERROR');
1387 FND_MSG_PUB.Add;
1388 RAISE FND_API.G_EXC_ERROR;
1389
1390 END VALIDATE_APPROVAL;
1391
1392 PROCEDURE GET_APPROVERS(p_approval_entity IN VARCHAR2
1393 ,p_referral_id IN NUMBER
1394 ,p_mode IN VARCHAR2
1395 ,x_approval_list OUT NOCOPY JTF_NUMBER_TABLE
1396 ,x_approval_completed OUT NOCOPY VARCHAR2
1397 ,x_default_approver OUT NOCOPY VARCHAR2
1398 ,x_user_id_exists OUT NOCOPY VARCHAR2)
1399 IS
1400 l_api_name VARCHAR2(20) := 'GET_APPROVERS';
1401
1402 x_nextApproverOut ame_util.approverRecord; -- Old API approverOut
1403
1404 x_approvalProcessCompleteYNOut VARCHAR2(100);
1405 x_nextApproversOut ame_util.approversTable2; -- New API approverOut
1406 currApprRec ame_util.approverRecord2;
1407
1408 xitemIndexesOut ame_util.idList;
1409 xitemClassesOut ame_util.stringList;
1410 xitemIdsOut ame_util.stringList;
1411 xitemSourcesOut ame_util.longStringList;
1412 xproductionIndexesOut ame_util.idList;
1413 xvariableNamesOut ame_util.stringList;
1414 xvariableValuesOut ame_util.stringList;
1415 xtransVariableNamesOut ame_util.stringList;
1416 xtransVariableValuesOut ame_util.stringList;
1417
1418 l_valid_user_for_person VARCHAR2(1) := 'Y';
1419 l_orig_system VARCHAR2(20);
1420 l_exception_flag VARCHAR2(1) := 'N';
1421
1422 BEGIN
1423 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1424 FND_LOG.MESSAGE(FND_LOG.LEVEL_PROCEDURE
1425 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
1426 ,FALSE
1427 );
1428 END IF;
1429
1430 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1431 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1432 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
1433 ,'inside GET_APPROVERS p_approval_entity '||p_approval_entity ||
1434 ' p_referral_id ' || p_referral_id
1435 );
1436 END IF;
1437
1438 -- This BEGIN ... END is to trap any errors that is thrown from AME. In that
1439 -- case the list of approvers will be empty and approval would not be
1440 -- marked as complete. In those cases the default approver is to be picked up.
1441 BEGIN
1442 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1443 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1444 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
1445 ,'GET_APPROVERS Calling new APIs '
1446 );
1447 END IF;
1448 x_approval_list := JTF_NUMBER_TABLE();
1449
1450 AME_API2.getNextApprovers3(applicationIdIn => 691
1451 ,transactionTypeIn => p_approval_entity
1452 ,transactionIdIn => p_referral_id
1453 ,flagApproversAsNotifiedIn => ame_util.booleanTrue
1454 ,approvalProcessCompleteYNOut => x_approvalProcessCompleteYNOut
1455 ,nextApproversOut => x_nextApproversOut
1456 ,itemIndexesOut => xitemIndexesOut
1457 ,itemClassesOut => xitemClassesOut
1458 ,itemIdsOut => xitemIdsOut
1459 ,itemSourcesOut => xitemSourcesOut
1460 ,productionIndexesOut => xproductionIndexesOut
1461 ,variableNamesOut => xvariableNamesOut
1462 ,variableValuesOut => xvariableValuesOut
1463 ,transVariableNamesOut => xtransVariableNamesOut
1464 ,transVariableValuesOut => xtransVariableValuesOut);
1465
1466 --x_approval_list.EXTEND(x_nextApproversOut.COUNT);
1467
1468 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1469 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1470 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
1471 ,'GET_APPROVERS x_nextApproversOut.COUNT '||x_nextApproversOut.COUNT
1472 );
1473 END IF;
1474
1475 FOR i IN 1..x_nextApproversOut.COUNT
1476 LOOP
1477
1478 currApprRec := x_nextApproversOut(i);
1479 l_orig_system := currApprRec.orig_system;
1483 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
1480
1481 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1482 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1484 ,'GET_APPROVERS l_orig_system '||l_orig_system
1485 );
1486 END IF;
1487
1488 /**
1489 * Referral/Deals support only internal users as approvers.
1490 *
1491 * AME supports the following types of approvers
1492 * Persons, Users , Workflow Roles
1493 *
1494 * Persons are always internal users so Refereals can support all PER.
1495 * However Referral/Deal module convers all person_id to user_id
1496 * before saving to PV_GE_TEMP_APPROVERS. All of the queries in this
1497 * module is centered around the assumption that approver column will always
1498 * have a USER_ID. Also in order to approve referrals and deals the user
1499 * has to login to the system and approve. It is not possible to do it
1500 * from an email or any other way without logging into the system. So
1501 * it is safe to assume that only persons with valid USER accounts can
1502 * be approvers in case of Referrals/Deals
1503 *
1504 * Users from AME are all users that are not internal users. These are not
1505 * supported since only internal users can be approvers.
1506 *
1507 * Workflow roles. PRM does not support this type.
1508 * --------------------------------------------------------------
1509 * -- IN SHORT ONLY 'PER' WITH VALID USER ACCOUNT IS SUPPORTED --
1510 * --------------------------------------------------------------
1511 **/
1512 x_user_id_exists := 'N';
1513
1514 IF (l_orig_system = 'PER') THEN
1515
1516 FOR x IN (SELECT A.user_id FROM fnd_user A, jtf_rs_resource_extns B
1517 WHERE employee_id = currApprRec.orig_system_id
1518 AND A.user_id = B.user_id
1519 AND ( A.end_date IS NULL OR A.end_date > sysdate-1) )
1520 LOOP
1521
1522 x_approval_list.EXTEND();
1523 x_approval_list(x_approval_list.COUNT) := x.user_id;
1524 x_user_id_exists := 'Y';
1525 END LOOP;
1526
1527 IF (x_user_id_exists = 'N') THEN
1528 EXIT;
1529 END IF;
1530 END IF;
1531
1532 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1533 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1534 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
1535 ,'GET_APPROVERS x_approval_completed '||x_approval_completed
1536 );
1537 END IF;
1538
1539 END LOOP;
1540
1541 x_approval_completed := x_approvalProcessCompleteYNOut;
1542 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1543 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1544 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
1545 ,'GET_APPROVERS x_approval_completed '||x_approval_completed
1546 );
1547 END IF;
1548
1549
1550 EXCEPTION
1551 WHEN OTHERS THEN
1552 -- log error message by AME
1553 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1554 FND_MSG_PUB.Add_Exc_Msg('PV_AME_API_W',l_api_name);
1555 END IF;
1556 l_exception_flag := 'Y';
1557
1558 IF p_mode = 'UPDATE' THEN
1559 RAISE;
1560 END IF;
1561 END;
1562
1563 /**
1564 * Default approver needs to be picked up under the following conditions
1565 *
1566 * IF AME IS BEING CALLED FOR THE FIRST TIME FOR THIS ENTITY
1567 * AND ANY ONE OF THE BELOW CASES
1568 * - AME DID NOT RETURN ANY ONE (MAYBE NO RULE WAS SETUP OR ANY OTHER REASON)
1569 * OR
1570 * - THERE WAS SOME EXCEPTION IN AME
1571 * OR
1572 * - AME RETURNED A PERSON WITH INVALID USER ACCOUNT
1573 *
1574 * A DEFAULT APPROVER IS NEVER CHOSEN IN CASE OF A SUBSEQUENT LEVEL APPROVAL
1575 **/
1576
1577 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1578 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1579 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
1580 ,'p_mode '||p_mode||' x_nextApproversOut.COUNT ' || x_nextApproversOut.COUNT ||
1581 ' l_exception_flag '||l_exception_flag||' x_user_id_exists ' || x_user_id_exists);
1582 END IF;
1583
1584 IF (p_mode = 'START') THEN
1585
1586 IF (x_nextApproversOut.COUNT < 1 --x_approval_completed = 'Y' AME changed
1587 OR l_exception_flag = 'Y'
1588 OR x_user_id_exists = 'N') THEN
1589
1590 x_default_approver := 'Y';
1591
1592 -- clearing the table in case there were any already existing
1593 -- valid users in list.
1594 x_approval_list := JTF_NUMBER_TABLE();
1595 x_approval_list.EXTEND(1);
1596
1597 IF p_approval_entity = 'PVREFFRL' THEN
1598 x_approval_list(1) := FND_PROFILE.Value('PV_DEFAULT_REFERRAL_APPROVER');
1599 ELSIF p_approval_entity = 'PVDEALRN' THEN
1600 x_approval_list(1) := FND_PROFILE.Value('PV_DEFAULT_DEAL_APPROVER');
1601 ELSIF p_approval_entity = 'PVDQMAPR' THEN
1602 x_approval_list(1) := FND_PROFILE.Value('PV_DEFAULT_DQM_APPROVER');
1603 END IF;
1604
1605 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1606 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1607 ,'pv.plsql.' || g_pkg_name || '.' || l_api_name
1608 ,'Approver from profile for '||p_approval_entity||' is ' || x_approval_list(1)
1609 );
1610 END IF;
1611 END IF; -- completed or exception or invalid user
1612
1613 END IF; -- p_mode is START
1614
1615
1616 EXCEPTION
1617
1618 WHEN OTHERS THEN
1619 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1620 FND_MSG_PUB.Add_Exc_Msg('PV_AME_API_W',l_api_name);
1621 END IF;
1622 END GET_APPROVERS;
1623
1624
1625 PROCEDURE DEL_PRIOR_REP_APPR(p_approval_entity IN VARCHAR2
1626 , p_referral_id IN NUMBER
1627 , p_approval_list IN JTF_NUMBER_TABLE)
1628 IS
1629 CURSOR lc_prior_approvers IS
1630 SELECT APPROVER_ID
1631 FROM PV_GE_TEMP_APPROVERS
1632 WHERE ARC_APPR_FOR_ENTITY_CODE = p_approval_entity
1633 AND APPR_FOR_ENTITY_ID = p_referral_id;
1634
1635 BEGIN
1636
1637 /** BUG 5523142
1638 * To open up the visibility of the deal/referral to approvers who had rejected before we need to
1639 * maintain all the rows that were previously created for a prior approval process. If the people
1640 * on the current approvers list from AME are in the prior approver list they need to be removed
1641 * before rows for new set of approvers are created.
1642 */
1643 FOR l_prior_appr IN lc_prior_approvers
1644 LOOP
1645 FOR x IN 1..p_approval_list.COUNT
1646 LOOP
1647 IF ( p_approval_list(x) = l_prior_appr.APPROVER_ID) THEN
1648 DELETE FROM pv_ge_temp_approvers
1649 WHERE arc_appr_for_entity_code = p_approval_entity
1650 AND appr_for_entity_id = p_referral_id
1651 AND approver_id = p_approval_list(x)
1652 AND approval_status_code IN ('PRIOR_APPROVER');
1653 END IF;
1654 END LOOP;
1655 END LOOP;
1656
1657 END DEL_PRIOR_REP_APPR;
1658
1659 END PV_AME_API_W;
[Home] [Help]