1 PACKAGE BODY DPP_APPROVAL_PVT AS
2 /* $Header: dppvappb.pls 120.14 2008/04/02 09:28:02 sdasan noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_APPROVAL_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'dppvappb.pls';
6
7 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9
10 DPP_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
11 DPP_UNEXP_ERROR_ON BOOLEAN :=FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error);
12 DPP_ERROR_ON BOOLEAN := FND_MSG_PUB.check_msg_level(fnd_msg_pub.g_msg_lvl_error);
13 G_DEBUG BOOLEAN := true ;
14
15 --Defining Global Value as Update_user_status, parametes are already defined
16 G_FORWARD_USER NUMBER;
17
18 ---------------------------------------------------------------------
19 FUNCTION resource_valid (p_resource_id IN NUMBER )
20 RETURN BOOLEAN
21 IS
22
23 l_resource_id NUMBER;
24 l_return_status BOOLEAN := FALSE;
25
26 CURSOR csr_resource (p_resource_id IN NUMBER) IS
27 SELECT jre.resource_id
28 FROM jtf_rs_resource_extns jre
29 WHERE jre.resource_id = p_resource_id;
30
31 BEGIN
32 OPEN csr_resource(p_resource_id);
33 FETCH csr_resource INTO l_resource_id;
34 CLOSE csr_resource;
35
36 IF l_resource_id IS NOT NULL THEN
37 l_return_status := TRUE;
38 END IF;
39
40 RETURN l_return_status;
41
42 EXCEPTION
43 WHEN OTHERS THEN
44 RETURN l_return_status;
45 END resource_valid;
46 ---------------------------------------------------------------------
47 -- PROCEDURE
48 -- Update_User_Action
49 --
50 -- PURPOSE
51 -- PARAMETERS
52 -- NOTES
53 ---------------------------------------------------------------------
54 PROCEDURE Update_User_Action(
55 p_api_version IN NUMBER
56 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
57 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
58
59 ,x_return_status OUT NOCOPY VARCHAR2
60 ,x_msg_data OUT NOCOPY VARCHAR2
61 ,x_msg_count OUT NOCOPY NUMBER
62
63 ,p_approval_rec IN approval_rec_type
64 )
65 IS
66 l_api_name CONSTANT varchar2(80) := 'Update_User_Action';
67 l_api_version CONSTANT number := 1.0;
68 l_approver_id number;
69 l_approval_access_id number;
70 l_approver_found varchar2(1) := 'N';
71 l_approver_level number;
72 l_approvers_tbl approvers_tbl_type;
73 l_ame_approver_rec ame_util.approverRecord;
74 l_ame_forward_rec ame_util.approverRecord default ame_util.emptyApproverRecord;
75 l_approval_status varchar2(30);
76 l_application_id number := 9000;
77 l_approver_type varchar2(30);
78 l_act_approver_id number;
79 l_permission varchar2(40);
80 l_min_reassign_level number;
81 l_action_code varchar2(30);
82
83
84 CURSOR csr_person_user (p_source_id IN NUMBER )IS
85 select user_id
86 from jtf_rs_resource_extns
87 where source_id = p_source_id
88 and sysdate >= start_date_active
89 and sysdate <= nvl(end_date_active, sysdate)
90 and rownum < 2;
91
92 CURSOR csr_curr_approvers (p_object_type IN VARCHAR2, p_object_id IN NUMBER )IS
93 SELECT approval_access_id, approver_id, approver_type,action_code
94 FROM DPP_APPROVAL_ACCESS
95 WHERE approval_access_flag = 'Y'
96 AND object_type = p_object_type
97 AND object_id = p_object_id;
98
99 CURSOR csr_count_approvers (p_object_type IN VARCHAR2, p_object_id IN NUMBER )IS
100 SELECT count(1)
101 FROM DPP_APPROVAL_ACCESS
102 WHERE approval_access_flag = 'Y'
103 AND object_type = p_object_type
104 AND object_id = p_object_id;
105
106 CURSOR csr_check_reassign_level (p_object_type in varchar2, p_object_id in number) IS
107 SELECT nvl(min(approval_level),0)
108 FROM DPP_APPROVAL_ACCESS
109 WHERE object_type = p_object_type
110 AND approval_access_flag = 'Y'
111 AND object_id = p_object_id;
112
113 CURSOR csr_approver_level (p_object_type in varchar2, p_object_id in number) IS
114 SELECT nvl(max(approval_level),0)
115 FROM DPP_APPROVAL_ACCESS
116 WHERE object_type = p_object_type
117 AND object_id = p_object_id;
118
119 BEGIN
120
121 -- Standard begin of API savepoint Update_User_Action_PVT
122 SAVEPOINT Update_User_Action_PVT;
123 -- Standard call to check for call compatibility.
124 IF NOT FND_API.Compatible_API_Call (
125 l_api_version,
126 p_api_version,
127 l_api_name,
128 G_PKG_NAME)
129 THEN
130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
131 END IF;
132 -- Debug Message
133 IF G_DEBUG THEN
134 dpp_utility_pvt.debug_message( l_api_name||': Start');
135 END IF;
136 --Initialize message list if p_init_msg_list is TRUE.
137 IF FND_API.To_Boolean (p_init_msg_list) THEN
138 FND_MSG_PUB.initialize;
139 END IF;
140 -- Initialize API return status to sucess
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142
143 -- Get Current Approvers and update their access
144 OPEN csr_curr_approvers(p_approval_rec.object_type, p_approval_rec.object_id);
145 LOOP
146 FETCH csr_curr_approvers INTO l_approval_access_id, l_approver_id, l_approver_type,l_action_code;
147 EXIT WHEN csr_curr_approvers%NOTFOUND;
148 --If Approver Type is Person Check, Get user Id of Person
149 if l_approver_type = 'PERSON' then
150 -- getting User id for the Person
151 OPEN csr_person_user (l_approver_id);
152 FETCH csr_person_user INTO l_act_approver_id ;
153 CLOSE csr_person_user;
154 else
155 --Actual Approver is a User
156 l_act_approver_id := l_approver_id;
157 end if;
158 IF G_DEBUG THEN
159 dpp_utility_pvt.debug_message( 'l_act_approver_id : '||l_act_approver_id);
160 END IF;
161 IF l_act_approver_id = p_approval_rec.action_performed_by THEN
162 -- Update approval access table to revoke access
163 UPDATE DPP_APPROVAL_ACCESS
164 SET action_code = p_approval_rec.action_code
165 , action_date = SYSDATE
166 , action_performed_by = p_approval_rec.action_performed_by
167 WHERE approval_access_id = l_approval_access_id;
168
169 l_approver_found := 'Y';
170 END IF;
171 -- Reset value to null
172 l_approval_access_id := null;
173
174 END LOOP;
175 CLOSE csr_curr_approvers;
176 IF l_approver_found = 'N' THEN
177
178 -- get current approval level
179 OPEN csr_approver_level (p_approval_rec.object_type, p_approval_rec.object_id);
180 FETCH csr_approver_level INTO l_approver_level;
181 CLOSE csr_approver_level;
182
183 -- construct approvers table
184 l_approvers_tbl := approvers_tbl_type();
185 l_approvers_tbl.extend;
186 l_approvers_tbl(1).approver_type := 'USER';
187 l_approvers_tbl(1).approver_id := p_approval_rec.action_performed_by;
188 l_approvers_tbl(1).approver_level := l_approver_level;
189
190 -- Add_Access - List Approvers sent from Get_Approvers api
191 Add_Access(
192 p_api_version => p_api_version
193 ,p_init_msg_list => FND_API.G_FALSE
194 ,p_commit => FND_API.G_FALSE
195 ,p_validation_level => p_validation_level
196 ,x_return_status => x_return_status
197 ,x_msg_data => x_msg_data
198 ,x_msg_count => x_msg_count
199 ,p_approval_rec => p_approval_rec
200 ,p_approvers => l_approvers_tbl );
201
202 IF x_return_status = FND_API.g_ret_sts_error THEN
203 RAISE FND_API.g_exc_error;
204 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
205 RAISE FND_API.g_exc_unexpected_error;
206 END IF;
207
208 -- Update approval access table to revoke access
209 UPDATE DPP_APPROVAL_ACCESS
210 SET action_code = p_approval_rec.action_code
211 , action_date = SYSDATE
212 , action_performed_by = p_approval_rec.action_performed_by
213 WHERE object_type = p_approval_rec.object_type
214 AND object_id = p_approval_rec.object_id
215 AND approver_id = p_approval_rec.action_performed_by
216 AND approval_level = l_approver_level;
217
218 END IF;
219
220 OPEN csr_check_reassign_level (p_approval_rec.object_type, p_approval_rec.object_id);
221 FETCH csr_check_reassign_level INTO l_min_reassign_level;
222 CLOSE csr_check_reassign_level;
223
224 -- Debug Message
225 IF G_DEBUG THEN
226 dpp_utility_pvt.debug_message( 'Approval done by user in approval list? ' || l_approver_found );
227 dpp_utility_pvt.debug_message( 'Approver User Id ' || p_approval_rec.action_performed_by );
228 dpp_utility_pvt.debug_message( 'Approver Action ' || p_approval_rec.action_code );
229 dpp_utility_pvt.debug_message( 'Approver Type ' || l_approver_type );
230 dpp_utility_pvt.debug_message( 'Act Approver User Id ' || l_act_approver_id );
231 dpp_utility_pvt.debug_message( 'Act Approver Person/User Id ' || l_approver_id );
232 dpp_utility_pvt.debug_message( 'Minimum Reassign Level ' || l_min_reassign_level );
233 dpp_utility_pvt.debug_message( 'l_action_code ' || l_action_code );
234 END IF;
235
236 /*
237 Check for minimum Reassign Level is added because , if it is 0 then the case is No AME Rule was
238 found for Transaction adn Default approver was found from profile
239 */
240 if l_min_reassign_level <> 0 AND l_action_code is NULL then
241 -- Update AME with approvers action
242 if l_approver_type = 'PERSON' then
243 l_ame_approver_rec.person_id := l_approver_id;
244 else
245 l_ame_approver_rec.user_id := p_approval_rec.action_performed_by;
246 end if;
247 l_ame_approver_rec.authority := ame_util.authorityApprover;
248
249 IF p_approval_rec.action_code = 'REJECT' THEN
250 -- Rejection of Request
251 IF l_approver_found = 'N' THEN
252 l_ame_approver_rec.api_insertion := ame_util.apiAuthorityInsertion;
253 ELSE
254 l_ame_approver_rec.api_insertion := ame_util.oamGenerated;
255 END IF;
256 l_ame_approver_rec.approval_status := AME_UTIL.rejectStatus;
257
258 ELSIF p_approval_rec.action_code = 'APPROVE' THEN
259 -- Approval of Request
260 IF l_approver_found = 'N' THEN
261 l_ame_approver_rec.api_insertion := ame_util.apiAuthorityInsertion;
262 ELSE
263 l_ame_approver_rec.api_insertion := ame_util.oamGenerated;
264 END IF;
265 l_ame_approver_rec.approval_status := AME_UTIL.approvedStatus;
266 END IF;
267 -- Update AME of Approval Status
268 AME_API.updateApprovalStatus(applicationIdIn => l_application_id
269 ,transactionIdIn => p_approval_rec.object_id
270 ,approverIn => l_ame_approver_rec
271 ,transactionTypeIn => p_approval_rec.object_type
272 ,forwardeeIn => l_ame_forward_rec
273 );
274 end if; -- End if minimum reassign Level not 0
275
276 IF G_DEBUG THEN
277 dpp_utility_pvt.debug_message( 'Revoke Access ' || p_approval_rec.action_code );
278 END IF;
279 -- Revoke_Access - Revoke acces to previous appprovers in the chain
280 Revoke_Access(
281 p_api_version => p_api_version
282 ,p_init_msg_list => FND_API.G_FALSE
283 ,p_validation_level => p_validation_level
284 ,x_return_status => x_return_status
285 ,x_msg_data => x_msg_data
286 ,x_msg_count => x_msg_count
287 ,p_object_type => p_approval_rec.object_type
288 ,p_object_id => p_approval_rec.object_id);
289
290 IF x_return_status = FND_API.g_ret_sts_error THEN
291 RAISE FND_API.g_exc_error;
292 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
293 RAISE FND_API.g_exc_unexpected_error;
294 END IF;
295
296 IF l_min_reassign_level <> 0 THEN
297 IF p_approval_rec.action_code = 'REJECT' THEN
298 AME_API.clearAllApprovals(applicationIdIn => l_application_id
299 ,transactionIdIn => p_approval_rec.object_id
300 ,transactionTypeIn => p_approval_rec.object_type
301 );
302 END IF;
303 END IF;
304
305 -- Debug Message
306 IF G_DEBUG THEN
307 dpp_utility_pvt.debug_message( l_api_name||': End');
308 END IF;
309 --Standard call to get message count and if count=1, get the message
310 FND_MSG_PUB.Count_And_Get (
311 p_encoded => FND_API.G_FALSE,
312 p_count => x_msg_count,
313 p_data => x_msg_data
314 );
315 EXCEPTION
316 WHEN FND_API.G_EXC_ERROR THEN
317 ROLLBACK TO Update_User_Action_PVT;
318 x_return_status := FND_API.G_RET_STS_ERROR;
319 -- Standard call to get message count and if count=1, get the message
320 FND_MSG_PUB.Count_And_Get (
321 p_encoded => FND_API.G_FALSE,
322 p_count => x_msg_count,
323 p_data => x_msg_data
324 );
325 IF x_msg_count > 1 THEN
326 FOR I IN 1..x_msg_count LOOP
327 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
328 END LOOP;
329 END IF;
330 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
331 ROLLBACK TO Update_User_Action_PVT;
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
333 -- Standard call to get message count and if count=1, get the message
334 FND_MSG_PUB.Count_And_Get (
335 p_encoded => FND_API.G_FALSE,
336 p_count => x_msg_count,
337 p_data => x_msg_data
338 );
339 IF x_msg_count > 1 THEN
340 FOR I IN 1..x_msg_count LOOP
341 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
342 END LOOP;
343 END IF;
344 WHEN OTHERS THEN
345 ROLLBACK TO Update_User_Action_PVT;
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 IF DPP_UNEXP_ERROR_ON
348 THEN
349 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
350 END IF;
351 -- Standard call to get message count and if count=1, get the message
352 FND_MSG_PUB.Count_And_Get (
353 p_encoded => FND_API.G_FALSE,
354 p_count => x_msg_count,
355 p_data => x_msg_data
356 );
357 IF x_msg_count > 1 THEN
358 FOR I IN 1..x_msg_count LOOP
359 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
360 END LOOP;
361 END IF;
362 --
363 END Update_User_Action;
364 ---------------------------------------------------------------------
365 -- PROCEDURE
366 -- Get_Approvers
367 --
368 -- PURPOSE
369 -- PARAMETERS
370 -- NOTES
371 ---------------------------------------------------------------------
372 PROCEDURE Get_Approvers(
373 p_api_version IN NUMBER
374 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
375 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
376
377 ,x_return_status OUT NOCOPY VARCHAR2
378 ,x_msg_data OUT NOCOPY VARCHAR2
379 ,x_msg_count OUT NOCOPY NUMBER
380
381 ,p_approval_rec IN approval_rec_type
382 ,x_approvers OUT NOCOPY approvers_tbl_type
383 ,x_final_approval_flag OUT NOCOPY VARCHAR2
384 )
385 IS
386
387
388 l_api_name CONSTANT varchar2(80) := 'Get_Approvers';
389 l_api_version CONSTANT number := 1.0;
390 l_application_id number := 9000;
391 l_object_type varchar2(30) := p_approval_rec.object_type;
392 l_object_id number := p_approval_rec.object_id;
393 l_next_approver AME_UTIL.approverRecord;
394 l_approver_level number;
395 l_resource_id number;
396
397 CURSOR csr_approver_level (p_object_type in varchar2, p_object_id in number) IS
398 SELECT nvl(max(approval_level),0)
399 FROM DPP_APPROVAL_ACCESS
400 WHERE object_type = p_object_type
401 AND object_id = p_object_id;
402
403 BEGIN
404
405 -- Standard begin of API savepoint
406 SAVEPOINT Get_Approvers_PVT;
407 -- Standard call to check for call compatibility.
408 IF NOT FND_API.Compatible_API_Call (
409 l_api_version,
410 p_api_version,
411 l_api_name,
412 G_PKG_NAME)
413 THEN
414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
415 END IF;
416 -- Debug Message
417 IF G_DEBUG THEN
418 dpp_utility_pvt.debug_message( l_api_name||': Start');
419 END IF;
420 --Initialize message list if p_init_msg_list is TRUE.
421 IF FND_API.To_Boolean (p_init_msg_list) THEN
422 FND_MSG_PUB.initialize;
423 END IF;
424 -- Initialize API return status to sucess
425 x_return_status := FND_API.G_RET_STS_SUCCESS;
426
427 OPEN csr_approver_level (l_object_type, l_object_id);
428 FETCH csr_approver_level INTO l_approver_level;
429 CLOSE csr_approver_level;
430
431 -- increment approval level by 1 for next approval;
432 l_approver_level := l_approver_level + 1;
433
434 IF G_DEBUG THEN
435 dpp_utility_pvt.debug_message( 'l_application_id:'|| l_application_id);
436 dpp_utility_pvt.debug_message( 'p_approval_rec.object_id:'|| p_approval_rec.object_id);
437 dpp_utility_pvt.debug_message( 'p_approval_rec.object_type:'|| p_approval_rec.object_type);
438 END IF;
439
440 -- Get Approver list from Approvals Manager
441 AME_API.getNextApprover(applicationIdIn => l_application_id
442 ,transactionIdIn => p_approval_rec.object_id
443 ,transactionTypeIn => p_approval_rec.object_type
444 ,nextApproverOut => l_next_approver
445 );
446
447 IF l_next_approver.person_id IS NULL AND
448 l_next_approver.user_id IS NULL AND
449 l_next_approver.approval_status IS NULL
450 THEN
451 IF G_DEBUG THEN
452 dpp_utility_pvt.debug_message( 'AME did not return any approvers in AME_API.getNextApprover call');
453 END IF;
454
455 -- If first approval, get default approver from profile
456 IF p_approval_rec.action_code = 'SUBMIT' THEN
457 IF G_DEBUG THEN
458 dpp_utility_pvt.debug_message( 'Defulting to 1 as default approver');
459 END IF;
460 -- Get default approver
461 x_approvers := approvers_tbl_type ();
462 x_approvers.extend;
463 x_approvers(1).approver_type := 'USER';
464 -- get user from profile (default approver)
465 IF p_approval_rec.object_type = 'PRICE PROTECTION' THEN
466 x_approvers(1).approver_id := to_number(fnd_profile.value('DPP_TXN_DEFAULT_APPROVER'));
467 END IF;
468 x_approvers(1).approver_level := 0;
469 x_final_approval_flag := 'N';
470
471 END IF;
472 -- If final approval, convey that information
473 IF p_approval_rec.action_code = 'APPROVE' THEN
474 IF G_DEBUG THEN
475 dpp_utility_pvt.debug_message( 'Setting to final approval');
476 END IF;
477 x_final_approval_flag := 'Y';
478 END IF;
479 ELSE
480 IF G_DEBUG THEN
481 dpp_utility_pvt.debug_message( 'AME returned approvers');
482 END IF;
483 x_final_approval_flag := 'N';
484 -- Construct the out record of approvers
485 x_approvers := approvers_tbl_type ();
486 x_approvers.extend;
487 if l_next_approver.user_id is null then
488 x_approvers(1).approver_type := 'PERSON';
489 x_approvers(1).approver_id := l_next_approver.person_id;
490 else
491 x_approvers(1).approver_type := 'USER';
492 x_approvers(1).approver_id := l_next_approver.user_id;
493 end if;
494 x_approvers(1).approver_level := l_approver_level;
495 END IF;
496
497 --Standard call to get message count and if count=1, get the message
498 FND_MSG_PUB.Count_And_Get (
499 p_encoded => FND_API.G_FALSE,
500 p_count => x_msg_count,
501 p_data => x_msg_data
502 );
503 EXCEPTION
504 WHEN FND_API.G_EXC_ERROR THEN
505 ROLLBACK TO Get_Approvers_PVT;
506 x_return_status := FND_API.G_RET_STS_ERROR;
507 -- Standard call to get message count and if count=1, get the message
508 FND_MSG_PUB.Count_And_Get (
509 p_encoded => FND_API.G_FALSE,
510 p_count => x_msg_count,
511 p_data => x_msg_data
512 );
513 IF x_msg_count > 1 THEN
514 FOR I IN 1..x_msg_count LOOP
515 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
516 END LOOP;
517 END IF;
518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
519 ROLLBACK TO Get_Approvers_PVT;
520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
521 -- Standard call to get message count and if count=1, get the message
522 FND_MSG_PUB.Count_And_Get (
523 p_encoded => FND_API.G_FALSE,
524 p_count => x_msg_count,
525 p_data => x_msg_data
526 );
527 IF x_msg_count > 1 THEN
528 FOR I IN 1..x_msg_count LOOP
529 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
530 END LOOP;
531 END IF;
532 WHEN OTHERS THEN
533 ROLLBACK TO Get_Approvers_PVT;
534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
535 IF DPP_UNEXP_ERROR_ON
536 THEN
537 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
538 END IF;
539 -- Standard call to get message count and if count=1, get the message
540 FND_MSG_PUB.Count_And_Get (
541 p_encoded => FND_API.G_FALSE,
542 p_count => x_msg_count,
543 p_data => x_msg_data
544 );
545 IF x_msg_count > 1 THEN
546 FOR I IN 1..x_msg_count LOOP
547 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
548 END LOOP;
549 END IF;
550 --
551 END Get_Approvers;
552 ---------------------------------------------------------------------
553 -- PROCEDURE
554 -- Get_AllApprovers
555 --
556 -- PURPOSE
557 -- PARAMETERS
558 -- NOTES
559 ---------------------------------------------------------------------
560 PROCEDURE Get_AllApprovers(
561 p_api_version IN NUMBER
562 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
563 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
564
565 ,x_return_status OUT NOCOPY VARCHAR2
566 ,x_msg_data OUT NOCOPY VARCHAR2
567 ,x_msg_count OUT NOCOPY NUMBER
568
569 ,p_approval_rec IN approval_rec_type
570 ,p_approversOut OUT NOCOPY approversTable
571 )
572 IS
573 l_api_name CONSTANT varchar2(80) := 'Get_AllApprovers';
574 l_api_version CONSTANT number := 1.0;
575 l_application_id NUMBER := 9000;
576 l_approversOut ame_util.approversTable;
577 l_approver_id NUMBER;
578 l_first_name VARCHAR2(150);
579 l_last_name VARCHAR2(150);
580 l_approver_email VARCHAR2(240);
581 l_approver_group_name VARCHAR2(50);
582 l_approver_sequence NUMBER;
583
584 BEGIN
585 -- Standard begin of API savepoint
586 SAVEPOINT Get_AllApprovers_PVT;
587 -- Standard call to check for call compatibility.
588 IF NOT FND_API.Compatible_API_Call (
589 l_api_version,
590 p_api_version,
591 l_api_name,
592 G_PKG_NAME)
593 THEN
594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
595 END IF;
596 -- Debug Message
597 IF G_DEBUG THEN
598 dpp_utility_pvt.debug_message( l_api_name||': Start');
599 END IF;
600 --Initialize message list if p_init_msg_list is TRUE.
601 IF FND_API.To_Boolean (p_init_msg_list) THEN
602 FND_MSG_PUB.initialize;
603 END IF;
604 --Initialize API return status to sucess
605 x_return_status := FND_API.G_RET_STS_SUCCESS;
606
607 IF G_DEBUG THEN
608 dpp_utility_pvt.debug_message( 'l_application_id:'|| l_application_id);
609 dpp_utility_pvt.debug_message( 'p_approval_rec.object_id:'|| p_approval_rec.object_id);
610 dpp_utility_pvt.debug_message( 'p_approval_rec.object_type:'|| p_approval_rec.object_type);
611 END IF;
612 --Get all the approvers for this transaction from Approvals Manager
613 AME_API.getAllApprovers(applicationIdIn => l_application_id
614 ,transactionIdIn => p_approval_rec.object_id
615 ,transactionTypeIn => p_approval_rec.object_type
616 ,approversOut => l_approversOut
617 );
618 IF l_approversOut.COUNT = 0 THEN --No approver found in AME
619 IF G_DEBUG THEN
620 dpp_utility_pvt.debug_message( 'AME did not return any approvers in AME_API.getAllApprovers call');
621 dpp_utility_pvt.debug_message( 'Get the default approver from the profile value');
622 END IF;
623 -- Get default approver from profile (default approver)
624 IF p_approval_rec.object_type = 'PRICE PROTECTION' THEN
625 l_approver_id := to_number(fnd_profile.value('DPP_TXN_DEFAULT_APPROVER'));
626 --Retrieve the first name and the last name of the approver from the per_persons_f table.
627 BEGIN
628 SELECT first_name,
629 last_name
630 INTO l_first_name,
631 l_last_name
632 FROM per_people_f
633 WHERE person_id = l_approver_id
634 AND rownum <2;
635 EXCEPTION
636 WHEN NO_DATA_FOUND THEN
637 FND_MESSAGE.set_name('DPP', 'DPP_AME_NO_APP');
638 FND_MSG_PUB.add;
639 RAISE FND_API.G_EXC_ERROR;
640 WHEN OTHERS THEN
641 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
642 fnd_message.set_token('ROUTINE', 'DPP_APPROVAL_PVT');
643 fnd_message.set_token('ERRNO', sqlcode);
644 fnd_message.set_token('REASON', sqlerrm);
645 FND_MSG_PUB.add;
646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
647 END;
648 p_approversOut(1).first_name := l_first_name;
649 p_approversOut(1).last_name := l_last_name;
650 ELSE
651 FND_MESSAGE.set_name('DPP', 'DPP_AME_NO_APP');
652 FND_MSG_PUB.add;
653 IF G_DEBUG THEN
654 dpp_utility_pvt.debug_message( 'No default approver set for the object type : '||p_approval_rec.object_type);
655 END IF;
656 END IF;
657 ELSE -- Approver set up found in AME
658 IF G_DEBUG THEN
659 dpp_utility_pvt.debug_message( 'AME returned approvers');
660 END IF;
661 --Assign the value to the out table type
662 FOR i IN l_approversOut.FIRST..l_approversOut.LAST LOOP
663 --Check if the person id is returned
664 IF l_approversOut(i).person_id IS NOT NULL THEN
665 IF G_DEBUG THEN
666 dpp_utility_pvt.debug_message( 'Person ID : '||l_approversOut(i).person_id);
667 END IF;
668 BEGIN
669 SELECT email_address
670 INTO l_approver_email
671 FROM per_all_people_f
672 WHERE person_id = l_approversOut(i).person_id
673 AND sysdate BETWEEN effective_start_date AND effective_end_date;
674 EXCEPTION
675 WHEN NO_DATA_FOUND THEN
676 l_approver_email := NULL;
677 FND_MESSAGE.set_name('DPP', 'DPP_NO_APP_DETAIL');
678 FND_MSG_PUB.add;
679 RAISE FND_API.G_EXC_ERROR;
680 WHEN OTHERS THEN
681 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
682 fnd_message.set_token('ROUTINE', 'DPP_APPROVAL_PVT');
683 fnd_message.set_token('ERRNO', sqlcode);
684 fnd_message.set_token('REASON', sqlerrm);
685 FND_MSG_PUB.add;
686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
687 END;
688 ELSIF l_approversOut(i).user_id IS NOT NULL THEN -- If person id is null then retrieve the user id
689 BEGIN
690 SELECT email_address
691 INTO l_approver_email
692 FROM fnd_user
693 WHERE user_id = l_approversOut(i).user_id;
694 EXCEPTION
695 WHEN NO_DATA_FOUND THEN
696 FND_MESSAGE.set_name('DPP', 'DPP_NO_APP_DETAIL');
697 FND_MSG_PUB.add;
698 RAISE FND_API.G_EXC_ERROR;
699 WHEN OTHERS THEN
700 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
701 fnd_message.set_token('ROUTINE', 'DPP_APPROVAL_PVT');
702 fnd_message.set_token('ERRNO', sqlcode);
703 fnd_message.set_token('REASON', sqlerrm);
704 FND_MSG_PUB.add;
705 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
706 END;
707 ELSE -- Both the person id and the user id are null
708 IF G_DEBUG THEN
709 dpp_utility_pvt.debug_message( 'No details retrieved for the Approver ');
710 END IF;
711 FND_MESSAGE.set_name('DPP', 'DPP_NO_APP_DETAIL');
712 FND_MSG_PUB.add;
713 l_approver_email := NULL;
714 END IF; --l_approversOut(i).person_id IS NOT NULL
715 --Retrieve the approval group name
716 BEGIN
717 SELECT name
718 INTO l_approver_group_name
719 FROM ame_approval_groups
720 WHERE approval_group_id = l_approversOut(i).group_or_chain_id;
721 EXCEPTION
722 WHEN NO_DATA_FOUND THEN
723 FND_MESSAGE.set_name('DPP', 'DPP_NO_APP_GRP_DETAIL');
724 FND_MSG_PUB.add;
725 RAISE FND_API.G_EXC_ERROR;
726 WHEN OTHERS THEN
727 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
728 fnd_message.set_token('ROUTINE', 'DPP_APPROVAL_PVT');
729 fnd_message.set_token('ERRNO', sqlcode);
730 fnd_message.set_token('REASON', sqlerrm);
731 FND_MSG_PUB.add;
732 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
733 END;
734 --Retrieve the order number
735 BEGIN
736 SELECT order_number
737 INTO l_approver_sequence
738 FROM AME_APPROVAL_GROUP_MEMBERS
739 WHERE approval_group_id = l_approversOut(i).group_or_chain_id
740 AND orig_system_id = nvl(l_approversOut(i).person_id,l_approversOut(i).user_id);
741 EXCEPTION
742 WHEN NO_DATA_FOUND THEN
743 FND_MESSAGE.set_name('DPP', 'DPP_NO_APP_SEQ');
744 FND_MSG_PUB.add;
745 RAISE FND_API.G_EXC_ERROR;
746 WHEN OTHERS THEN
747 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
748 fnd_message.set_token('ROUTINE', 'DPP_APPROVAL_PVT');
749 fnd_message.set_token('ERRNO', sqlcode);
750 fnd_message.set_token('REASON', sqlerrm);
751 FND_MSG_PUB.add;
752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
753 END;
754 --Assign the approver details to the out variable
755 p_approversOut(i).user_id := l_approversOut(i).user_id;
756 p_approversOut(i).person_id := l_approversOut(i).person_id;
757 p_approversOut(i).first_name := l_approversOut(i).first_name;
758 p_approversOut(i).last_name := l_approversOut(i).last_name;
759 p_approversOut(i).api_insertion := l_approversOut(i).api_insertion;
760 p_approversOut(i).authority := l_approversOut(i).authority;
761 p_approversOut(i).approval_status := l_approversOut(i).approval_status;
762 p_approversOut(i).approval_type_id := l_approversOut(i).approval_type_id;
763 p_approversOut(i).group_or_chain_id := l_approversOut(i).group_or_chain_id;
764 p_approversOut(i).occurrence := l_approversOut(i).occurrence;
765 p_approversOut(i).source := l_approversOut(i).source;
766 p_approversOut(i).approver_email := l_approver_email;
767 p_approversOut(i).approver_group_name := l_approver_group_name;
768 p_approversOut(i).approver_sequence := l_approver_sequence;
769 END LOOP;
770 END IF;
771 -- Debug Message
772 IF G_DEBUG THEN
773 dpp_utility_pvt.debug_message( l_api_name||': End');
774 END IF;
775 --Standard call to get message count and if count=1, get the message
776 FND_MSG_PUB.Count_And_Get (
777 p_encoded => FND_API.G_FALSE,
778 p_count => x_msg_count,
779 p_data => x_msg_data
780 );
781 EXCEPTION
782 WHEN FND_API.G_EXC_ERROR THEN
783 ROLLBACK TO Get_AllApprovers_PVT;
784 x_return_status := FND_API.G_RET_STS_ERROR;
785 -- Standard call to get message count and if count=1, get the message
786 FND_MSG_PUB.Count_And_Get (
787 p_encoded => FND_API.G_FALSE,
788 p_count => x_msg_count,
789 p_data => x_msg_data
790 );
791 IF x_msg_count > 1 THEN
792 FOR I IN 1..x_msg_count LOOP
793 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
794 END LOOP;
795 END IF;
796 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
797 ROLLBACK TO Get_AllApprovers_PVT;
798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
799 -- Standard call to get message count and if count=1, get the message
800 FND_MSG_PUB.Count_And_Get (
801 p_encoded => FND_API.G_FALSE,
802 p_count => x_msg_count,
803 p_data => x_msg_data
804 );
805 IF x_msg_count > 1 THEN
806 FOR I IN 1..x_msg_count LOOP
807 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
808 END LOOP;
809 END IF;
810 WHEN OTHERS THEN
811 ROLLBACK TO Get_AllApprovers_PVT;
812 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
813 IF DPP_UNEXP_ERROR_ON
814 THEN
815 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
816 END IF;
817 -- Standard call to get message count and if count=1, get the message
818 FND_MSG_PUB.Count_And_Get (
819 p_encoded => FND_API.G_FALSE,
820 p_count => x_msg_count,
821 p_data => x_msg_data
822 );
823 IF x_msg_count > 1 THEN
824 FOR I IN 1..x_msg_count LOOP
825 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
826 END LOOP;
827 END IF;
828 --
829 END Get_AllApprovers;
830 ---------------------------------------------------------------------
831 -- PROCEDURE
832 -- Add_Access
833 --
834 -- PURPOSE
835 -- adds approvers access to table
836 --
837 -- PARAMETERS
838 -- NOTES
839 ---------------------------------------------------------------------
840 PROCEDURE Add_Access(
841 p_api_version IN NUMBER
842 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
843 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
844 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
845
846 ,x_msg_data OUT NOCOPY VARCHAR2
847 ,x_msg_count OUT NOCOPY NUMBER
848 ,x_return_status OUT NOCOPY VARCHAR2
849
850 ,p_approval_rec IN approval_rec_type
851 ,p_approvers IN approvers_tbl_type
852 )
853 IS
854 l_api_name CONSTANT varchar2(80) := 'Add_Access';
855 l_api_version CONSTANT number := 1.0;
856 l_approval_access_id NUMBER;
857 l_workflow_itemkey VARCHAR2(80);
858
859 CURSOR c_id IS
860 SELECT DPP_APPROVAL_ACCESS_seq.NEXTVAL
861 FROM dual;
862
863 BEGIN
864 -- Standard begin of API savepoint
865 SAVEPOINT Add_Access_PVT;
866 -- Standard call to check for call compatibility.
867 IF NOT FND_API.Compatible_API_Call (
868 l_api_version,
869 p_api_version,
870 l_api_name,
871 G_PKG_NAME)
872 THEN
873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
874 END IF;
875 -- Debug Message
876 IF G_DEBUG THEN
877 dpp_utility_pvt.debug_message( l_api_name||': Start');
878 END IF;
879 --Initialize message list if p_init_msg_list is TRUE.
880 IF FND_API.To_Boolean (p_init_msg_list) THEN
881 FND_MSG_PUB.initialize;
882 END IF;
883 -- Initialize API return status to sucess
884 x_return_status := FND_API.G_RET_STS_SUCCESS;
885
886 -- Get Primary Key
887 OPEN c_id;
888 FETCH c_id INTO l_approval_access_id;
889 CLOSE c_id;
890
891 IF p_approval_rec.object_type IS NULL THEN
892 IF DPP_ERROR_ON THEN
893 dpp_utility_pvt.error_message('OZF_OBJECT_TYPE_NOT_FOUND');
894 x_return_status := FND_API.g_ret_sts_error;
895 RAISE FND_API.G_EXC_ERROR;
896 END IF;
897 END IF;
898
899 IF p_approval_rec.object_id IS NULL THEN
900 IF DPP_ERROR_ON THEN
901 dpp_utility_pvt.error_message('OZF_OBJECT_ID_NOT_FOUND');
902 x_return_status := FND_API.g_ret_sts_error;
903 RAISE FND_API.G_EXC_ERROR;
904 END IF;
905 END IF;
906
907 -- Validate if the approvers record is valid
908 FOR i in 1..p_approvers.count LOOP
909 IF p_approvers(i).approver_type <> 'USER' and p_approvers(i).approver_type <> 'PERSON' THEN
910 IF DPP_ERROR_ON THEN
911 dpp_utility_pvt.error_message('OZF_APPROVER_NOT_USER');
912 x_return_status := FND_API.g_ret_sts_error;
913 RAISE FND_API.G_EXC_ERROR;
914 END IF;
915 END IF;
916
917 IF p_approvers(i).approver_level IS NULL THEN
918 IF DPP_ERROR_ON THEN
919 dpp_utility_pvt.error_message('OZF_APPROVAL_LEVEL_NOT_FOUND');
920 x_return_status := FND_API.g_ret_sts_error;
921 RAISE FND_API.G_EXC_ERROR;
922 END IF;
923 END IF;
924 END LOOP;
925
926 --Insert data into DPP_APPROVAL_ACCESS_all
927 FOR i in 1..p_approvers.count LOOP
928 BEGIN
929 IF G_DEBUG THEN
930 dpp_utility_pvt.debug_message( 'Inserting data into DPP_APPROVAL_ACCESS table');
931 END IF;
932 --
933 INSERT INTO DPP_APPROVAL_ACCESS(
934 approval_access_id
935 ,object_version_number
936 ,last_update_date
937 ,last_updated_by
938 ,creation_date
939 ,created_by
940 ,last_update_login
941 ,object_type
942 ,object_id
943 ,approval_level
944 ,approver_type
945 ,approver_id
946 ,approval_access_flag
947 ,workflow_itemkey
948 ) VALUES (
949 l_approval_access_id
950 ,1
951 ,SYSDATE
952 ,G_USER_ID
953 ,SYSDATE
954 ,G_USER_ID
955 ,G_LOGIN_ID
956 ,p_approval_rec.object_type
957 ,p_approval_rec.object_id
958 ,p_approvers(i).approver_level
959 ,p_approvers(i).approver_type
960 ,p_approvers(i).approver_id
961 ,'Y'
962 ,l_workflow_itemkey
963 );
964 EXCEPTION
965 WHEN OTHERS THEN
966 IF DPP_ERROR_ON THEN
967 dpp_utility_pvt.error_message('DPP_APPROVAL_ACCESS_INSERT_ERR');
968 x_return_status := FND_API.g_ret_sts_error;
969 RAISE FND_API.G_EXC_ERROR;
970 END IF;
971 END;
972 END LOOP;
973
974 --Standard check of commit
975 IF FND_API.To_Boolean ( p_commit ) THEN
976 COMMIT WORK;
977 END IF;
978 -- Debug Message
979 IF G_DEBUG THEN
980 dpp_utility_pvt.debug_message( l_api_name||': End');
981 END IF;
982 --Standard call to get message count and if count=1, get the message
983 FND_MSG_PUB.Count_And_Get (
984 p_encoded => FND_API.G_FALSE,
985 p_count => x_msg_count,
986 p_data => x_msg_data
987 );
988 EXCEPTION
989 WHEN FND_API.G_EXC_ERROR THEN
990 ROLLBACK TO Add_Access_PVT;
991 x_return_status := FND_API.G_RET_STS_ERROR;
992 -- Standard call to get message count and if count=1, get the message
993 FND_MSG_PUB.Count_And_Get (
994 p_encoded => FND_API.G_FALSE,
995 p_count => x_msg_count,
996 p_data => x_msg_data
997 );
998 IF x_msg_count > 1 THEN
999 FOR I IN 1..x_msg_count LOOP
1000 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1001 END LOOP;
1002 END IF;
1003 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1004 ROLLBACK TO Add_Access_PVT;
1005 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1006 -- Standard call to get message count and if count=1, get the message
1007 FND_MSG_PUB.Count_And_Get (
1008 p_encoded => FND_API.G_FALSE,
1009 p_count => x_msg_count,
1010 p_data => x_msg_data
1011 );
1012 IF x_msg_count > 1 THEN
1013 FOR I IN 1..x_msg_count LOOP
1014 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1015 END LOOP;
1016 END IF;
1017 WHEN OTHERS THEN
1018 ROLLBACK TO Add_Access_PVT;
1019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1020 IF DPP_UNEXP_ERROR_ON
1021 THEN
1022 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1023 END IF;
1024 -- Standard call to get message count and if count=1, get the message
1025 FND_MSG_PUB.Count_And_Get (
1026 p_encoded => FND_API.G_FALSE,
1027 p_count => x_msg_count,
1028 p_data => x_msg_data
1029 );
1030 IF x_msg_count > 1 THEN
1031 FOR I IN 1..x_msg_count LOOP
1032 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1033 END LOOP;
1034 END IF;
1035 --
1036 END Add_Access;
1037 ---------------------------------------------------------------------
1038 -- PROCEDURE
1039 -- Revoke_Access
1040 --
1041 -- PURPOSE
1042 -- Revokes access to current approvers
1043 --
1044 -- PARAMETERS
1045 --
1046 -- NOTES
1047 ---------------------------------------------------------------------
1048 PROCEDURE Revoke_Access (
1049 p_api_version IN NUMBER
1050 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1051 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1052 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1053
1054 ,x_return_status OUT NOCOPY VARCHAR2
1055 ,x_msg_data OUT NOCOPY VARCHAR2
1056 ,x_msg_count OUT NOCOPY NUMBER
1057
1058 ,p_object_type IN VARCHAR2
1059 ,p_object_id IN NUMBER
1060 )
1061 IS
1062 l_api_name CONSTANT varchar2(80) := 'Revoke_Access';
1063 l_api_version CONSTANT number := 1.0;
1064 l_approval_access_id number;
1065
1066 CURSOR csr_curr_approvers (p_object_type IN VARCHAR2, p_object_id IN NUMBER )IS
1067 SELECT approval_access_id
1068 FROM DPP_APPROVAL_ACCESS
1069 WHERE approval_access_flag = 'Y'
1070 AND object_type = p_object_type
1071 AND object_id = p_object_id;
1072
1073 BEGIN
1074 -- Standard begin of API savepoint
1075 SAVEPOINT Revoke_Access_PVT;
1076 -- Standard call to check for call compatibility.
1077 IF NOT FND_API.Compatible_API_Call (
1078 l_api_version,
1079 p_api_version,
1080 l_api_name,
1081 G_PKG_NAME)
1082 THEN
1083 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084 END IF;
1085 -- Debug Message
1086 IF G_DEBUG THEN
1087 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1088 FND_MESSAGE.Set_Token('TEXT',l_api_name||': Start');
1089 FND_MSG_PUB.Add;
1090 END IF;
1091 --Initialize message list if p_init_msg_list is TRUE.
1092 IF FND_API.To_Boolean (p_init_msg_list) THEN
1093 FND_MSG_PUB.initialize;
1094 END IF;
1095 -- Initialize API return status to sucess
1096 x_return_status := FND_API.G_RET_STS_SUCCESS;
1097
1098 -- Update records in DPP_APPROVAL_ACCESS_all to revoke access
1099 OPEN csr_curr_approvers(p_object_type, p_object_id);
1100 LOOP
1101 FETCH csr_curr_approvers INTO l_approval_access_id;
1102 EXIT WHEN csr_curr_approvers%NOTFOUND;
1103
1104 -- Update approval access table to revoke access
1105 UPDATE DPP_APPROVAL_ACCESS
1106 SET approval_access_flag = 'N'
1107 WHERE approval_access_id = l_approval_access_id;
1108
1109 -- Reset value to null
1110 l_approval_access_id := null;
1111 END LOOP;
1112 CLOSE csr_curr_approvers;
1113
1114 --Standard check of commit
1115 IF FND_API.To_Boolean ( p_commit ) THEN
1116 COMMIT WORK;
1117 END IF;
1118 -- Debug Message
1119 IF G_DEBUG THEN
1120 dpp_utility_pvt.debug_message( l_api_name||': End');
1121 END IF;
1122 --Standard call to get message count and if count=1, get the message
1123 FND_MSG_PUB.Count_And_Get (
1124 p_encoded => FND_API.G_FALSE,
1125 p_count => x_msg_count,
1126 p_data => x_msg_data
1127 );
1128 EXCEPTION
1129 WHEN FND_API.G_EXC_ERROR THEN
1130 ROLLBACK TO Revoke_Access_PVT;
1131 x_return_status := FND_API.G_RET_STS_ERROR;
1132 -- Standard call to get message count and if count=1, get the message
1133 FND_MSG_PUB.Count_And_Get (
1134 p_encoded => FND_API.G_FALSE,
1135 p_count => x_msg_count,
1136 p_data => x_msg_data
1137 );
1138 IF x_msg_count > 1 THEN
1139 FOR I IN 1..x_msg_count LOOP
1140 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1141 END LOOP;
1142 END IF;
1143 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1144 ROLLBACK TO Revoke_Access_PVT;
1145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1146 -- Standard call to get message count and if count=1, get the message
1147 FND_MSG_PUB.Count_And_Get (
1148 p_encoded => FND_API.G_FALSE,
1149 p_count => x_msg_count,
1150 p_data => x_msg_data
1151 );
1152 IF x_msg_count > 1 THEN
1153 FOR I IN 1..x_msg_count LOOP
1154 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1155 END LOOP;
1156 END IF;
1157 WHEN OTHERS THEN
1158 ROLLBACK TO Revoke_Access_PVT;
1159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1160 IF DPP_UNEXP_ERROR_ON
1161 THEN
1162 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1163 END IF;
1164 -- Standard call to get message count and if count=1, get the message
1165 FND_MSG_PUB.Count_And_Get (
1166 p_encoded => FND_API.G_FALSE,
1167 p_count => x_msg_count,
1168 p_data => x_msg_data
1169 );
1170 IF x_msg_count > 1 THEN
1171 FOR I IN 1..x_msg_count LOOP
1172 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1173 END LOOP;
1174 END IF;
1175 --
1176 END Revoke_Access;
1177 -----------------------------------------------
1178 -- Return event name if the entered event exist
1179 -- Otherwise return NOTFOUND
1180 -----------------------------------------------
1181 FUNCTION Check_Event(p_event_name IN VARCHAR2)
1182 RETURN VARCHAR2
1183 IS
1184
1185 CURSOR c_event_name IS
1186 SELECT name
1187 FROM wf_events
1188 WHERE name = p_event_name;
1189
1190 l_event_name VARCHAR2(240);
1191
1192 BEGIN
1193
1194 OPEN c_event_name;
1195 FETCH c_event_name INTO l_event_name;
1196 IF c_event_name%NOTFOUND THEN
1197 l_event_name := 'NOTFOUND';
1198 END IF;
1199 CLOSE c_event_name;
1200
1201 RETURN l_event_name;
1202
1203 END Check_Event;
1204 ------------------------------------------------------
1205 -- Add Application-Context parameter to the enter list
1206 ------------------------------------------------------
1207 PROCEDURE Construct_Param_List (
1208 x_list IN OUT NOCOPY WF_PARAMETER_LIST_T,
1209 p_user_id IN VARCHAR2 DEFAULT NULL,
1210 p_resp_id IN VARCHAR2 DEFAULT NULL,
1211 p_resp_appl_id IN VARCHAR2 DEFAULT NULL,
1212 p_security_group_id IN VARCHAR2 DEFAULT NULL,
1213 p_org_id IN VARCHAR2 DEFAULT NULL)
1214 IS
1215 l_user_id VARCHAR2(255) := p_user_id;
1216 l_resp_appl_id VARCHAR2(255) := p_resp_appl_id;
1217 l_resp_id VARCHAR2(255) := p_resp_id;
1218 l_security_group_id VARCHAR2(255) := p_security_group_id;
1219 l_org_id VARCHAR2(255) := p_org_id;
1220 l_param WF_PARAMETER_T;
1221 l_rang NUMBER;
1222 BEGIN
1223 l_rang := 0;
1224
1225 IF l_user_id IS NULL THEN
1226 l_user_id := fnd_profile.value('USER_ID');
1227 END IF;
1228
1229 l_param := WF_PARAMETER_T( NULL, NULL );
1230 -- fill the parameters list
1231 x_list.extend;
1232 l_param.SetName('USER_ID');
1233 l_param.SetValue(l_user_id);
1234 l_rang := l_rang + 1;
1235 x_list(l_rang) := l_param;
1236
1237 IF l_resp_id IS NULL THEN
1238 l_resp_id := fnd_profile.value('RESP_ID');
1239 END IF;
1240
1241 l_param := WF_PARAMETER_T( NULL, NULL );
1242 -- fill the parameters list
1243 x_list.extend;
1244 l_param.SetName('RESP_ID');
1245 l_param.SetValue(l_resp_id);
1246 l_rang := l_rang + 1;
1247 x_list(l_rang) := l_param;
1248
1249 IF l_resp_appl_id IS NULL THEN
1250 l_resp_appl_id := fnd_profile.value('RESP_APPL_ID');
1251 END IF;
1252
1253 l_param := WF_PARAMETER_T( NULL, NULL );
1254 -- fill the parameters list
1255 x_list.extend;
1256 l_param.SetName('RESP_APPL_ID');
1257 l_param.SetValue(l_resp_appl_id);
1258 l_rang := l_rang + 1;
1259 x_list(l_rang) := l_param;
1260
1261 IF l_security_group_id IS NULL THEN
1262 l_security_group_id := fnd_profile.value('SECURITY_GROUP_ID');
1263 END IF;
1264 l_param := WF_PARAMETER_T( NULL, NULL );
1265 -- fill the parameters list
1266 x_list.extend;
1267 l_param.SetName('SECURITY_GROUP_ID');
1268 l_param.SetValue(l_security_group_id);
1269 l_rang := l_rang + 1;
1270 x_list(l_rang) := l_param;
1271
1272 IF l_org_id IS NULL THEN
1273 l_org_id := fnd_profile.value('ORG_ID');
1274 END IF;
1275
1276 l_param := WF_PARAMETER_T( NULL, NULL );
1277 -- fill the parameters list
1278 x_list.extend;
1279 l_param.SetName('ORG_ID');
1280 l_param.SetValue(l_org_id );
1281 l_rang := l_rang + 1;
1282 x_list(l_rang) := l_param;
1283
1284 END Construct_Param_List;
1285 ---------------------------------------------------------------------
1286 -- PROCEDURE
1287 -- Raise_Event
1288 --
1289 -- PURPOSE
1290 -- Raise business event
1291 --
1292 -- PARAMETERS
1293 --
1294 -- NOTES
1295 ---------------------------------------------------------------------
1296 PROCEDURE Raise_Event (
1297 x_return_status OUT NOCOPY VARCHAR2
1298 ,x_msg_data OUT NOCOPY VARCHAR2
1299 ,x_msg_count OUT NOCOPY NUMBER
1300 ,p_event_name IN VARCHAR2
1301 ,p_event_key IN VARCHAR2
1302 --,p_data IN CLOB DEFAULT NULL
1303 ,p_approval_rec IN approval_rec_type)
1304 IS
1305 l_api_name CONSTANT varchar2(80) := 'Raise_Event';
1306 l_api_version CONSTANT number := 1.0;
1307
1308 l_item_key VARCHAR2(240);
1309 l_event VARCHAR2(240);
1310
1311 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1312 l_parameter_t wf_parameter_t := wf_parameter_t(null, null);
1313
1314 BEGIN
1315
1316 SAVEPOINT Raise_Event_PVT;
1317
1318 l_event := Check_Event(p_event_name);
1319 IF G_DEBUG THEN
1320 dpp_utility_pvt.debug_message( 'Event : ' || l_event);
1321 END IF;
1322 --dpp_utility_pvt.error_message('Event Name', l_event);
1323
1324 IF l_event = 'NOTFOUND' THEN
1325 IF DPP_ERROR_ON THEN
1326 dpp_utility_pvt.error_message('OZF_WF_EVENT_NAME_NULL', 'NAME', p_event_name);
1327 END IF;
1328 x_return_status := FND_API.g_ret_sts_error;
1329 RAISE FND_API.G_EXC_ERROR;
1330 END IF;
1331
1332 l_parameter_t.setName('OBJECT_TYPE');
1333 l_parameter_t.setValue(p_approval_rec.object_type);
1334 l_parameter_list.extend;
1335 l_parameter_list(1) := l_parameter_t;
1336
1337 l_parameter_t.setName('OBJECT_ID');
1338 l_parameter_t.setValue(p_approval_rec.object_id);
1339 l_parameter_list.extend;
1340 l_parameter_list(2) := l_parameter_t;
1341
1342 l_parameter_t.setName('STATUS_CODE');
1343 l_parameter_t.setValue(p_approval_rec.status_code);
1344 l_parameter_list.extend;
1345 l_parameter_list(3) := l_parameter_t;
1346 -- Debug Message
1347 IF G_DEBUG THEN
1348 dpp_utility_pvt.debug_message( 'Event Raise :' || p_event_name);
1349 dpp_utility_pvt.debug_message( 'Event Key :' || p_event_key);
1350 END IF;
1351 -- Raise business event
1352 Wf_Event.Raise
1353 ( p_event_name => p_event_name,
1354 p_event_key => p_event_key,
1355 p_parameters => l_parameter_list,
1356 p_event_data => NULL);
1357 -- Debug Message
1358 IF G_DEBUG THEN
1359 dpp_utility_pvt.debug_message( 'event raised....');
1360 END IF;
1361 EXCEPTION
1362 WHEN FND_API.G_EXC_ERROR THEN
1363 ROLLBACK TO Raise_Event_PVT;
1364 x_return_status := FND_API.G_RET_STS_ERROR;
1365 -- Standard call to get message count and if count=1, get the message
1366 FND_MSG_PUB.Count_And_Get (
1367 p_encoded => FND_API.G_FALSE,
1368 p_count => x_msg_count,
1369 p_data => x_msg_data
1370 );
1371 IF x_msg_count > 1 THEN
1372 FOR I IN 1..x_msg_count LOOP
1373 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1374 END LOOP;
1375 END IF;
1376 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1377 ROLLBACK TO Raise_Event_PVT;
1378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1379 -- Standard call to get message count and if count=1, get the message
1380 FND_MSG_PUB.Count_And_Get (
1381 p_encoded => FND_API.G_FALSE,
1382 p_count => x_msg_count,
1383 p_data => x_msg_data
1384 );
1385 IF x_msg_count > 1 THEN
1386 FOR I IN 1..x_msg_count LOOP
1387 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1388 END LOOP;
1389 END IF;
1390 WHEN OTHERS THEN
1391 ROLLBACK TO Raise_Event_PVT;
1392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1393 IF DPP_UNEXP_ERROR_ON
1394 THEN
1395 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1396 END IF;
1397 -- Standard call to get message count and if count=1, get the message
1398 FND_MSG_PUB.Count_And_Get (
1399 p_encoded => FND_API.G_FALSE,
1400 p_count => x_msg_count,
1401 p_data => x_msg_data
1402 );
1403 IF x_msg_count > 1 THEN
1404 FOR I IN 1..x_msg_count LOOP
1405 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1406 END LOOP;
1407 END IF;
1408 --
1409 END Raise_Event;
1410 ---------------------------------------------------------------------
1411 -- PROCEDURE
1412 -- Send_Notification
1413 --
1414 -- PURPOSE
1415 -- Sends notifications to approvers
1416 --
1417 -- PARAMETERS
1418 --
1419 -- NOTES
1420 ---------------------------------------------------------------------
1421 PROCEDURE Send_Notification (
1422 p_api_version IN NUMBER
1423 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1424 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1425 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1426
1427 ,x_return_status OUT NOCOPY VARCHAR2
1428 ,x_msg_data OUT NOCOPY VARCHAR2
1429 ,x_msg_count OUT NOCOPY NUMBER
1430
1431 ,p_transaction_header_id IN NUMBER
1432 ,p_msg_callback_api IN VARCHAR2
1433 ,p_approval_rec IN approval_rec_type
1434 )
1435 IS
1436 l_api_name CONSTANT varchar2(80) := 'Send_Notification';
1437 l_api_version CONSTANT number := 1.0;
1438 l_object_type varchar2(30) := p_approval_rec.object_type;
1439 l_object_id number := p_approval_rec.object_id;
1440 l_status varchar2(30) := p_approval_rec.status_code;
1441 l_msg_callback_api varchar2(240) := p_msg_callback_api;
1442 l_final_approval number;
1443
1444 CURSOR csr_final_approval (p_object_type varchar2, p_object_id number) IS
1445 SELECT count(1)
1446 FROM dpp_approval_access
1447 WHERE object_type = p_object_type
1448 AND object_id = p_object_id
1449 AND approval_access_flag = 'Y';
1450
1451 CURSOR csr_approvers (p_object_type varchar2, p_object_id number) IS
1452 SELECT fu.user_name
1453 FROM dpp_approval_access oaa
1454 , fnd_user fu
1455 WHERE oaa.object_type = p_object_type
1456 AND oaa.object_id = p_object_id
1457 AND oaa.approver_type = 'USER'
1458 AND oaa.approver_id = fu.user_id
1459 AND oaa.approval_access_flag = 'Y'
1460 UNION
1461 SELECT jre.user_name
1462 FROM dpp_approval_access oaa
1463 , jtf_rs_resource_extns jre
1464 WHERE oaa.object_type = p_object_type
1465 AND oaa.object_id = p_object_id
1466 AND oaa.approver_type = 'PERSON'
1467 AND oaa.approver_id = jre.source_id
1468 AND oaa.approval_access_flag = 'Y'
1469 group by jre.user_name;
1470
1471 l_adhoc_role varchar2(200);
1472 l_role_list varchar2(3000);
1473 l_msg_type varchar2(12) := 'DPPTXAPP';
1474 l_msg_name varchar2(30);
1475 l_item_key varchar2(200);
1476 l_item_type varchar2(30);
1477
1478 l_group_notify_id number;
1479 l_context varchar2(1000);
1480 l_user_role varchar2(240);
1481
1482 l_execute_str varchar2(3000);
1483
1484 BEGIN
1485 -- Standard begin of API savepoint
1486 SAVEPOINT Send_Notification_PVT;
1487 -- Standard call to check for call compatibility.
1488 IF NOT FND_API.Compatible_API_Call (
1489 l_api_version,
1490 p_api_version,
1491 l_api_name,
1492 G_PKG_NAME)
1493 THEN
1494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1495 END IF;
1496 -- Debug Message
1497 IF G_DEBUG THEN
1498 dpp_utility_pvt.debug_message( l_api_name||': Start');
1499 END IF;
1500 --Initialize message list if p_init_msg_list is TRUE.
1501 IF FND_API.To_Boolean (p_init_msg_list) THEN
1502 FND_MSG_PUB.initialize;
1503 END IF;
1504 -- Initialize API return status to sucess
1505 x_return_status := FND_API.G_RET_STS_SUCCESS;
1506
1507 IF l_user_role is null THEN
1508 l_user_role := '%';
1509 ELSE
1510 l_user_role := l_user_role; -- p_user_role;
1511 END IF;
1512
1513 IF G_DEBUG THEN
1514 dpp_utility_pvt.debug_message( 'Before constructing message ' || l_user_role || ' ' || l_object_id || ' ' || l_status);
1515 END IF;
1516
1517 /*
1518 If Approved Status , Chek if it if Final Approval or Not
1519 if Final Approval Then Send Approved Notification else Send Submitted Notification
1520 */
1521 if l_status = 'APPROVED' then
1522 OPEN csr_final_approval (l_object_type, l_object_id);
1523 FETCH csr_final_approval INTO l_final_approval;
1524 CLOSE csr_final_approval;
1525 if l_final_approval <> 0 then
1526 l_status := 'SUBMITTED_FOR_APPROVAL';
1527 end if;
1528 end if;
1529
1530 l_msg_name := l_status||'01';
1531 l_role_list := '';
1532
1533 FOR l_row IN csr_approvers(l_object_type, l_object_id) LOOP
1534 l_role_list := l_role_list || ',' || l_row.user_name;
1535 END LOOP;
1536 l_role_list := substr(l_role_list,2);
1537 -- END IF;
1538
1539 IF G_DEBUG THEN
1540 dpp_utility_pvt.debug_message('Users List for sending notification' || l_role_list);
1541 END IF;
1542
1543 -- users returned from the search
1544 IF length(l_role_list) <> 0 THEN
1545 l_item_key := l_msg_type||'|'||l_msg_name||'|'||l_object_id||
1546 '|'||to_char(sysdate,'YYYYMMDDHH24MISS');
1547 IF G_DEBUG THEN
1548 dpp_utility_pvt.debug_message('l_item_key ' || l_item_key );
1549 END IF;
1550 IF l_object_type = 'PRICE PROTECTION' THEN
1551 l_item_type := 'DPPTXAPP';
1552 END IF;
1553
1554 IF G_DEBUG THEN
1555 dpp_utility_pvt.debug_message('Creating process for itemtype:' || l_item_type || ' itemkey: ' || l_item_key);
1556 END IF;
1557
1558 -- Create WF process to send notification
1559 wf_engine.CreateProcess ( ItemType => l_item_type,
1560 ItemKey => l_item_key,
1561 process => 'NOOP_PROCESS',
1562 user_key => l_item_key);
1563
1564 IF G_DEBUG THEN
1565 dpp_utility_pvt.debug_message('Executing msg callback' || l_msg_callback_api );
1566 END IF;
1567
1568 -- execute callback api to set the message attributes
1569 EXECUTE IMMEDIATE 'BEGIN ' ||
1570 l_msg_callback_api || '(:itemtype, :itemkey, :transaction_header_id, :status); ' ||
1571 'END;'
1572 USING l_item_type, l_item_key, l_object_id, l_status;
1573
1574 IF G_DEBUG THEN
1575 dpp_utility_pvt.debug_message('Adding adhoc users' || l_role_list );
1576 END IF;
1577
1578 -- create an adhoc role with named after itemkey
1579 l_adhoc_role := l_item_key;
1580
1581 wf_directory.CreateAdHocRole(role_name => l_adhoc_role,
1582 role_display_name => l_adhoc_role,
1583 role_users => l_role_list);
1584
1585 l_context := l_msg_type || ':' || l_item_key || ':';
1586
1587 IF G_DEBUG THEN
1588 dpp_utility_pvt.debug_message('Sending Notification to adhoc role ' || l_msg_type || ' ' || l_msg_name);
1589 dpp_utility_pvt.debug_message('Sent notification to role : ' || l_adhoc_role);
1590 END IF;
1591
1592 -- set the message to be sent
1593 l_group_notify_id := wf_notification.sendGroup(
1594 role => l_adhoc_role,
1595 msg_type => l_msg_type,
1596 msg_name => l_msg_name,
1597 due_date => null,
1598 callback => 'wf_engine.cb',
1599 context => l_context,
1600 send_comment => NULL,
1601 priority => NULL );
1602
1603 IF G_DEBUG THEN
1604 dpp_utility_pvt.debug_message('Sending notification to role : ' || l_adhoc_role);
1605 dpp_utility_pvt.debug_message('Using message: ' || l_msg_name || '. Notify id: ' || l_group_notify_id );
1606 END IF;
1607
1608 -- start the notification process to send message
1609 wf_engine.StartProcess(itemtype => l_item_type,
1610 itemkey => l_item_key);
1611 -- no users returned from the search
1612 END IF;
1613
1614 -- Update WorkFlow Item Key in approval Access Table
1615 update DPP_APPROVAL_ACCESS
1616 set workflow_itemkey = substr(l_item_key,1,239)
1617 where object_type = l_object_type
1618 and object_id = l_object_id
1619 and approval_level = ( select max (approval_level)
1620 from DPP_APPROVAL_ACCESS
1621 where object_type = l_object_type
1622 and object_id = l_object_id);
1623 --Standard check of commit
1624 IF FND_API.To_Boolean ( p_commit ) THEN
1625 COMMIT WORK;
1626 END IF;
1627
1628 -- Debug Message
1629 IF G_DEBUG THEN
1630 dpp_utility_pvt.debug_message( l_api_name||': End');
1631 END IF;
1632 --Standard call to get message count and if count=1, get the message
1633 FND_MSG_PUB.Count_And_Get (
1634 p_encoded => FND_API.G_FALSE,
1635 p_count => x_msg_count,
1636 p_data => x_msg_data
1637 );
1638 EXCEPTION
1639 WHEN FND_API.G_EXC_ERROR THEN
1640 ROLLBACK TO Send_Notification_PVT;
1641 x_return_status := FND_API.G_RET_STS_ERROR;
1642 -- Standard call to get message count and if count=1, get the message
1643 FND_MSG_PUB.Count_And_Get (
1644 p_encoded => FND_API.G_FALSE,
1645 p_count => x_msg_count,
1646 p_data => x_msg_data
1647 );
1648 IF x_msg_count > 1 THEN
1649 FOR I IN 1..x_msg_count LOOP
1650 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1651 END LOOP;
1652 END IF;
1653 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1654 ROLLBACK TO Send_Notification_PVT;
1655 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1656 -- Standard call to get message count and if count=1, get the message
1657 FND_MSG_PUB.Count_And_Get (
1658 p_encoded => FND_API.G_FALSE,
1659 p_count => x_msg_count,
1660 p_data => x_msg_data
1661 );
1662 IF x_msg_count > 1 THEN
1663 FOR I IN 1..x_msg_count LOOP
1664 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1665 END LOOP;
1666 END IF;
1667 WHEN OTHERS THEN
1668 ROLLBACK TO Send_Notification_PVT;
1669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1670 IF DPP_UNEXP_ERROR_ON
1671 THEN
1672 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1673 END IF;
1674 -- Standard call to get message count and if count=1, get the message
1675 FND_MSG_PUB.Count_And_Get (
1676 p_encoded => FND_API.G_FALSE,
1677 p_count => x_msg_count,
1678 p_data => x_msg_data
1679 );
1680 IF x_msg_count > 1 THEN
1681 FOR I IN 1..x_msg_count LOOP
1682 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1683 END LOOP;
1684 END IF;
1685 --
1686 END Send_Notification;
1687 ---------------------------------------------------------------------
1688 -- PROCEDURE
1689 -- Process_User_Action
1690
1691 --
1692 -- PURPOSE
1693 -- Handles the approvals and rejections of objects
1694 --
1695 -- PARAMETERS
1696 --
1697 -- NOTES
1698 -- 1. object_version_number will be set to 1.
1699 ---------------------------------------------------------------------
1700 PROCEDURE Process_User_Action (
1701 p_api_version IN NUMBER
1702 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1703 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1704 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1705
1706 ,x_return_status OUT NOCOPY VARCHAR2
1707 ,x_msg_data OUT NOCOPY VARCHAR2
1708 ,x_msg_count OUT NOCOPY NUMBER
1709
1710 ,p_approval_rec IN approval_rec_type
1711 ,p_approver_id IN NUMBER
1712 ,x_final_approval_flag OUT NOCOPY VARCHAR2
1713 )
1714 IS
1715 l_api_name CONSTANT VARCHAR2(80) := 'Process_User_Action';
1716 l_api_version CONSTANT NUMBER := 1.0;
1717 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1718
1719 l_approvers_tbl approvers_tbl_type;
1720 l_event_name VARCHAR2(240) ;
1721 l_event_key VARCHAR2(240);
1722 l_msg_callback_api VARCHAR2(240);
1723 l_benefit_id NUMBER;
1724 l_final_approval_flag VARCHAR2(1) := 'N';
1725 l_txn_number VARCHAR2(240);
1726 l_effective_date DATE;
1727 l_org_id NUMBER;
1728 errbuff VARCHAR2(4000);
1729 retcode VARCHAR2(10);
1730 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
1731 l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
1732
1733 BEGIN
1734 -- Standard begin of API savepoint
1735 SAVEPOINT Process_User_Action_PVT;
1736 -- Standard call to check for call compatibility.
1737 IF NOT FND_API.Compatible_API_Call (
1738 l_api_version,
1739 p_api_version,
1740 l_api_name,
1741 G_PKG_NAME)
1742 THEN
1743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1744 END IF;
1745 -- Debug Message
1746 IF G_DEBUG THEN
1747 dpp_utility_pvt.debug_message( l_api_name||': Start');
1748 END IF;
1749 --Initialize message list if p_init_msg_list is TRUE.
1750 IF FND_API.To_Boolean (p_init_msg_list) THEN
1751 FND_MSG_PUB.initialize;
1752 END IF;
1753 x_return_status := FND_API.G_RET_STS_SUCCESS;
1754
1755 --Retrieve the details of the transaction
1756 BEGIN
1757 SELECT transaction_number,
1758 effective_start_date,
1759 to_number(org_id)
1760 INTO l_txn_number,
1761 l_effective_date,
1762 l_org_id
1763 FROM dpp_transaction_headers_all
1764 WHERE transaction_header_id = p_approval_rec.object_id;
1765 EXCEPTION
1766 WHEN NO_DATA_FOUND THEN
1767 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1768 fnd_message.set_token('ROUTINE', 'DPP_APPROVAL_PVT');
1769 fnd_message.set_token('ERRNO', sqlcode);
1770 fnd_message.set_token('REASON', 'Invalid Transaction Header ID');
1771 FND_MSG_PUB.add;
1772 RAISE FND_API.G_EXC_ERROR;
1773 WHEN OTHERS THEN
1774 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1775 fnd_message.set_token('ROUTINE', 'DPP_APPROVAL_PVT');
1776 fnd_message.set_token('ERRNO', sqlcode);
1777 fnd_message.set_token('REASON', sqlerrm);
1778 FND_MSG_PUB.add;
1779 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1780 END;
1781 -- Update AME/approval tbl of users action and revoke access to existing approvers
1782 IF p_approval_rec.action_code = 'APPROVE' OR
1783 p_approval_rec.action_code = 'REJECT' THEN
1784 IF G_DEBUG THEN
1785 dpp_utility_pvt.debug_message( 'Update User Action ' || p_approval_rec.action_code );
1786 END IF;
1787
1788 -- Update_User_Action - Update the user action in approval table
1789 Update_User_Action(
1790 p_api_version => p_api_version
1791 ,p_init_msg_list => FND_API.G_FALSE
1792 ,p_validation_level => p_validation_level
1793 ,x_return_status => l_return_status
1794 ,x_msg_data => x_msg_data
1795 ,x_msg_count => x_msg_count
1796 ,p_approval_rec => p_approval_rec);
1797
1798 IF l_return_status = FND_API.g_ret_sts_error THEN
1799 RAISE FND_API.g_exc_error;
1800 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1801 RAISE FND_API.g_exc_unexpected_error;
1802 END IF;
1803
1804 IF p_approval_rec.action_code = 'REJECT' THEN
1805 l_final_approval_flag := 'Y';
1806 END IF;
1807 END IF;
1808
1809 -- If the request is submitted/approved - get next approvers
1810 IF p_approval_rec.action_code = 'SUBMIT' OR
1811 p_approval_rec.action_code = 'APPROVE' THEN
1812
1813 IF G_DEBUG THEN
1814 dpp_utility_pvt.debug_message( 'Get Approvers ' || p_approval_rec.action_code );
1815 END IF;
1816 -- Get_Approvers - List of Approvers or Default Approver
1817 Get_Approvers(
1818 p_api_version => p_api_version
1819 ,p_init_msg_list => FND_API.G_FALSE
1820 ,p_validation_level => p_validation_level
1821 ,x_return_status => l_return_status
1822 ,x_msg_data => x_msg_data
1823 ,x_msg_count => x_msg_count
1824 ,p_approval_rec => p_approval_rec
1825 ,x_approvers => l_approvers_tbl
1826 ,x_final_approval_flag => l_final_approval_flag);
1827
1828 IF l_return_status = FND_API.g_ret_sts_error THEN
1829 RAISE FND_API.g_exc_error;
1830 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1831 RAISE FND_API.g_exc_unexpected_error;
1832 END IF;
1833 END IF;
1834
1835 -- Add access to users who have approval privileges
1836 IF p_approval_rec.action_code = 'SUBMIT' OR
1837 p_approval_rec.action_code = 'APPROVE' THEN
1838 IF G_DEBUG THEN
1839 dpp_utility_pvt.debug_message( 'Add Access ' || p_approval_rec.action_code );
1840 END IF;
1841
1842 IF l_final_approval_flag <> 'Y' THEN
1843 --If no Approver Found Do not add record in Access table
1844 if l_approvers_tbl(1).approver_id is not null then
1845 -- Add_Access - List Approvers sent from Get_Approvers api
1846 Add_Access(
1847 p_api_version => p_api_version
1848 ,p_init_msg_list => FND_API.G_FALSE
1849 ,p_commit => FND_API.G_FALSE
1850 ,p_validation_level => p_validation_level
1851 ,x_return_status => l_return_status
1852 ,x_msg_data => x_msg_data
1853 ,x_msg_count => x_msg_count
1854 ,p_approval_rec => p_approval_rec
1855 ,p_approvers => l_approvers_tbl );
1856
1857 IF l_return_status = FND_API.g_ret_sts_error THEN
1858 RAISE FND_API.g_exc_error;
1859 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1860 RAISE FND_API.g_exc_unexpected_error;
1861 END IF;
1862 END IF; --End if some Approver is found
1863
1864 END IF;
1865
1866 IF G_DEBUG THEN
1867 dpp_utility_pvt.debug_message( 'Access Added ' || x_return_status );
1868 END IF;
1869 END IF;
1870
1871 IF p_approval_rec.object_type = 'PRICE PROTECTION' THEN
1872
1873 l_event_name := 'oracle.apps.dpp.request.Transaction.approval';
1874 IF G_DEBUG THEN
1875 dpp_utility_pvt.debug_message('assigning the event name :'||l_event_name);
1876 END IF;
1877 END IF;
1878
1879 l_event_key := p_approval_rec.object_type || ':' || p_approval_rec.object_id || ':' || to_char(sysdate, 'DD:MON:YYYY HH:MI:SS');
1880
1881 IF p_approval_rec.object_type = 'PRICE PROTECTION' THEN
1882 IF l_final_approval_flag <> 'Y' or p_approval_rec.action_code <> 'APPROVE' THEN
1883 -- Raise_Event -> Event_Subscription -> Send_Notification
1884 Raise_Event (
1885 x_return_status => l_return_status
1886 ,x_msg_data => x_msg_data
1887 ,x_msg_count => x_msg_count
1888 ,p_event_name => l_event_name
1889 ,p_event_key => l_event_key
1890 ,p_approval_rec => p_approval_rec );
1891
1892 END IF;
1893 ELSE
1894 Raise_Event (
1895 x_return_status => l_return_status
1896 ,x_msg_data => x_msg_data
1897 ,x_msg_count => x_msg_count
1898 ,p_event_name => l_event_name
1899 ,p_event_key => l_event_key
1900 ,p_approval_rec => p_approval_rec );
1901
1902 END IF;
1903
1904 x_final_approval_flag := l_final_approval_flag;
1905 --Check for the Final approval flag
1906 IF x_final_approval_flag = 'Y' AND p_approval_rec.action_code = 'APPROVE' THEN
1907 IF l_effective_date <= sysdate THEN
1908 BEGIN
1909 UPDATE dpp_transaction_headers_all
1910 SET transaction_status = 'APPROVED',
1911 object_version_number = object_version_number + 1,
1912 last_updated_by = l_user_id,
1913 last_update_date = sysdate,
1914 last_update_login = l_login_id
1915 WHERE transaction_header_id = p_approval_rec.object_id;
1916
1917 IF SQL%ROWCOUNT = 0 THEN
1918 x_return_status := FND_API.G_RET_STS_ERROR;
1919 DPP_UTILITY_PVT.debug_message('Unable to Update the column transaction_status in dpp_transaction_headers_all Table');
1920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1921 END IF;
1922 EXCEPTION
1923 WHEN OTHERS THEN
1924 ROLLBACK;
1925 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1926 fnd_message.set_token('ROUTINE', 'DPP_APPROVAL_PVT');
1927 fnd_message.set_token('ERRNO', sqlcode);
1928 fnd_message.set_token('REASON', sqlerrm);
1929 FND_MSG_PUB.add;
1930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1931 END;
1932 --Call the Initiate execution process program to make the transaction Active
1933 DPP_EXECUTIONPROCESS_PUB.Initiate_ExecutionProcess(errbuff,
1934 retcode,
1935 l_org_id,
1936 l_txn_number
1937 );
1938 IF G_DEBUG THEN
1939 dpp_utility_pvt.debug_message(substr(('Return status for Initiate_ExecutionProcess : ' || errbuff),1,4000));
1940 END IF;
1941 IF retcode = 0 THEN
1942 x_final_approval_flag := 'A';
1943 IF G_DEBUG THEN
1944 dpp_utility_pvt.debug_message( 'Transaction is made Active ');
1945 END IF;
1946 ELSE
1947 IF G_DEBUG THEN
1948 dpp_utility_pvt.debug_message( 'Initiate_ExecutionProcess Errored out. Transaction not made Active ');
1949 END IF;
1950 END IF; --retcode = 0
1951 ELSE
1952 IF G_DEBUG THEN
1953 dpp_utility_pvt.debug_message( 'Transaction is Future dated and hence not made Active ');
1954 END IF;
1955 END IF; --l_effective_date <= sysdate
1956 END IF; --x_final_approval_flag := 'Y'
1957 --Standard check of commit
1958 IF FND_API.To_Boolean ( p_commit ) THEN
1959 COMMIT WORK;
1960 END IF;
1961 -- Debug Message
1962 IF G_DEBUG THEN
1963 dpp_utility_pvt.debug_message( l_api_name||': End');
1964 END IF;
1965 --Standard call to get message count and if count=1, get the message
1966 FND_MSG_PUB.Count_And_Get (
1967 p_encoded => FND_API.G_FALSE,
1968 p_count => x_msg_count,
1969 p_data => x_msg_data
1970 );
1971
1972 EXCEPTION
1973 WHEN FND_API.G_EXC_ERROR THEN
1974 ROLLBACK TO Process_User_Action_PVT;
1975 x_return_status := FND_API.G_RET_STS_ERROR;
1976 -- Standard call to get message count and if count=1, get the message
1977 FND_MSG_PUB.Count_And_Get (
1978 p_encoded => FND_API.G_FALSE,
1979 p_count => x_msg_count,
1980 p_data => x_msg_data
1981 );
1982 IF x_msg_count > 1 THEN
1983 FOR I IN 1..x_msg_count LOOP
1984 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1985 END LOOP;
1986 END IF;
1987 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1988 ROLLBACK TO Process_User_Action_PVT;
1989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1990 -- Standard call to get message count and if count=1, get the message
1991 FND_MSG_PUB.Count_And_Get (
1992 p_encoded => FND_API.G_FALSE,
1993 p_count => x_msg_count,
1994 p_data => x_msg_data
1995 );
1996 IF x_msg_count > 1 THEN
1997 FOR I IN 1..x_msg_count LOOP
1998 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1999 END LOOP;
2000 END IF;
2001 WHEN OTHERS THEN
2002 ROLLBACK TO Process_User_Action_PVT;
2003 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2004 IF DPP_UNEXP_ERROR_ON
2005 THEN
2006 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2007 END IF;
2008 -- Standard call to get message count and if count=1, get the message
2009 FND_MSG_PUB.Count_And_Get (
2010 p_encoded => FND_API.G_FALSE,
2011 p_count => x_msg_count,
2012 p_data => x_msg_data
2013 );
2014 IF x_msg_count > 1 THEN
2015 FOR I IN 1..x_msg_count LOOP
2016 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
2017 END LOOP;
2018 END IF;
2019 --
2020 END Process_User_Action;
2021
2022 END DPP_APPROVAL_PVT;
[Home] [Help]