[Home] [Help]
PACKAGE BODY: APPS.OZF_APPROVAL_PVT
Source
1 PACKAGE BODY OZF_APPROVAL_PVT AS
2 /* $Header: ozfvappb.pls 120.5 2008/01/11 05:11:03 ateotia ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_APPROVAL_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvappb.pls';
6
7 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9
10 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
11 --G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
12 OZF_UNEXP_ERROR_ON BOOLEAN :=FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error);
13 OZF_ERROR_ON BOOLEAN := FND_MSG_PUB.check_msg_level(fnd_msg_pub.g_msg_lvl_error);
14 G_DEBUG BOOLEAN := true ;--FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
15
16 --Defining Global Value as Update_user_status, parametes are already defined
17 G_FORWARD_USER NUMBER;
18
19 ---------------------------------------------------------------------
20 FUNCTION resource_valid (p_resource_id IN NUMBER )
21 RETURN BOOLEAN
22 IS
23
24 l_resource_id NUMBER;
25 l_return_status BOOLEAN := FALSE;
26
27 CURSOR csr_resource (p_resource_id IN NUMBER) IS
28 SELECT jre.resource_id
29 FROM jtf_rs_resource_extns jre
30 WHERE jre.resource_id = p_resource_id;
31
32 BEGIN
33 OPEN csr_resource(p_resource_id);
34 FETCH csr_resource INTO l_resource_id;
35 CLOSE csr_resource;
36
37 IF l_resource_id IS NOT NULL THEN
38 l_return_status := TRUE;
39 END IF;
40
41 RETURN l_return_status;
42
43 EXCEPTION
44 WHEN OTHERS THEN
45 RETURN l_return_status;
46 END resource_valid;
47 ---------------------------------------------------------------------
48 -- PROCEDURE
49 -- Update_User_Action
50 --
51 -- PURPOSE
52 -- PARAMETERS
53 -- NOTES
54 ---------------------------------------------------------------------
55 PROCEDURE Update_User_Action(
56 p_api_version IN NUMBER
57 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
58 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
59
60 ,x_return_status OUT NOCOPY VARCHAR2
61 ,x_msg_data OUT NOCOPY VARCHAR2
62 ,x_msg_count OUT NOCOPY NUMBER
63
64 ,p_approval_rec IN approval_rec_type
65 )
66 IS
67 l_api_name CONSTANT varchar2(80) := 'Update_User_Action';
68 l_api_version CONSTANT number := 1.0;
69 l_approver_id number;
70 l_approval_access_id number;
71 l_approver_found varchar2(1) := 'N';
72 l_approver_level number;
73 l_approvers_tbl approvers_tbl_type;
74 l_ame_approver_rec ame_util.approverRecord;
75 l_ame_forward_rec ame_util.approverRecord default ame_util.emptyApproverRecord;
76 l_approval_status varchar2(30);
77 l_application_id number := 682;
78 l_approver_type varchar2(30);
79 l_act_approver_id number;
80 l_is_super_user varchar2(1) := 'N';
81 l_super_user_count number;
82 l_approver_rec_count number;
83 l_permission varchar2(30);
84 l_min_reassign_level number;
85 l_action_code varchar2(30);
86
87
88 CURSOR csr_person_user (p_source_id IN NUMBER )IS
89 select user_id
90 from jtf_rs_resource_extns
91 where source_id = p_source_id
92 and sysdate >= start_date_active
93 and sysdate <= nvl(end_date_active, sysdate)
94 and rownum < 2;
95
96 CURSOR csr_curr_approvers (p_object_type IN VARCHAR2, p_object_id IN NUMBER )IS
97 SELECT approval_access_id, approver_id, approver_type,action_code
98 FROM ozf_approval_access
99 WHERE approval_access_flag = 'Y'
100 AND object_type = p_object_type
101 AND object_id = p_object_id;
102
103
104 CURSOR csr_count_approvers (p_object_type IN VARCHAR2, p_object_id IN NUMBER )IS
105 SELECT count(1)
106 FROM ozf_approval_access
107 WHERE approval_access_flag = 'Y'
108 AND object_type = p_object_type
109 AND object_id = p_object_id;
110
111 CURSOR csr_check_reassign_level (p_object_type in varchar2, p_object_id in number) IS
112 SELECT nvl(min(approval_level),0)
113 FROM ozf_approval_access
114 WHERE object_type = p_object_type
115 AND object_id = p_object_id;
116
117 CURSOR csr_approver_level (p_object_type in varchar2, p_object_id in number) IS
118 SELECT nvl(max(approval_level),0)
119 FROM ozf_approval_access
120 WHERE object_type = p_object_type
121 AND object_id = p_object_id;
122
123 CURSOR crs_get_int_super_users(pc_permission varchar2, pc_userid number) is
124 select count(1)
125 from jtf_auth_principal_maps jtfpm,
126 jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
127 jtf_auth_principals_b jtfp2, jtf_auth_role_perms jtfrp,
128 jtf_auth_permissions_b jtfperm, jtf_rs_resource_extns pj,
129 fnd_user usr
130 where PJ.user_name = jtfp1.principal_name
131 and pj.category = 'EMPLOYEE'
132 and usr.user_id = pj.user_id
133 and (usr.end_date > sysdate OR usr.end_date IS NULL)
134 and jtfp1.is_user_flag=1
135 and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
136 and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
137 and jtfp2.is_user_flag=0
138 and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
139 and jtfrp.positive_flag = 1
140 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
141 and jtfperm.permission_name = pc_permission
142 and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
143 and jtfd.domain_name='CRM_DOMAIN'
144 and usr.user_id = pc_userid;
145
146
147 BEGIN
148 -- Standard begin of API savepoint
149 SAVEPOINT Update_User_Action_PVT;
150 -- Standard call to check for call compatibility.
151 IF NOT FND_API.Compatible_API_Call (
152 l_api_version,
153 p_api_version,
154 l_api_name,
155 G_PKG_NAME)
156 THEN
157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
158 END IF;
159 -- Debug Message
160 IF G_DEBUG THEN
161 ozf_utility_pvt.debug_message( l_api_name||': Start');
162 END IF;
163 --Initialize message list if p_init_msg_list is TRUE.
164 IF FND_API.To_Boolean (p_init_msg_list) THEN
165 FND_MSG_PUB.initialize;
166 END IF;
167 -- Initialize API return status to sucess
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170
171 --Check if InComing User is Super User or Not
172 if p_approval_rec.object_type = 'SPECIAL_PRICE' then
173 l_permission := 'OZF_SPECIAL_PRICE_SUPERUSER';
174 else
175 l_permission := 'OZF_SOFTFUND_SUPERUSER';
176 end if;
177
178 OPEN crs_get_int_super_users (l_permission, p_approval_rec.action_performed_by);
179 FETCH crs_get_int_super_users INTO l_super_user_count ;
180 CLOSE crs_get_int_super_users;
181 if l_super_user_count = 0 then
182 l_is_super_user := 'N';
183 else
184 l_is_super_user := 'Y';
185 end if;
186
187
188
189 /* Id super user and No Approvers are Present in Approval Access Table , that means
190 No Approver was defined for Request ( inc default Approver )
191 */
192 if l_is_super_user = 'Y' then
193 OPEN csr_count_approvers(p_approval_rec.object_type, p_approval_rec.object_id);
194 FETCH csr_count_approvers INTO l_approver_rec_count ;
195 CLOSE csr_count_approvers;
196 if l_approver_rec_count = 0 then
197 FND_MSG_PUB.Count_And_Get (
198 p_encoded => FND_API.G_FALSE,
199 p_count => x_msg_count,
200 p_data => x_msg_data
201 );
202
203 RETURN;
204 end if;
205 end if;
206
207 -- Get Current Approvers and update their access
208 OPEN csr_curr_approvers(p_approval_rec.object_type, p_approval_rec.object_id);
209 LOOP
210 FETCH csr_curr_approvers INTO l_approval_access_id, l_approver_id, l_approver_type,l_action_code;
211 EXIT WHEN csr_curr_approvers%NOTFOUND;
212
213 --If Approver Type is Person Check, Get user Id of Person
214 if l_approver_type = 'PERSON' then
215 -- getting User id for the Person
216 OPEN csr_person_user (l_approver_id);
217 FETCH csr_person_user INTO l_act_approver_id ;
218 CLOSE csr_person_user;
219 else
220 --Actual Approver is a User
221 l_act_approver_id := l_approver_id;
222 end if;
223
224 --IF l_approver_id = p_approval_rec.action_performed_by THEN
225 --As Action is always Performed by Approver check with User Id only
226 -- or action is performed by Super User
227 IF l_is_super_user = 'Y' OR l_act_approver_id = p_approval_rec.action_performed_by THEN
228 -- Update approval access table to revoke access
229 UPDATE ozf_approval_access
230 SET action_code = p_approval_rec.action_code
231 , action_date = SYSDATE
232 , action_performed_by = p_approval_rec.action_performed_by
233 WHERE approval_access_id = l_approval_access_id;
234
235 l_approver_found := 'Y';
236 END IF;
237 -- Reset value to null
238 l_approval_access_id := null;
239
240 END LOOP;
241 CLOSE csr_curr_approvers;
242
243 IF l_approver_found = 'N' THEN
244
245 -- get current approval level
246 OPEN csr_approver_level (p_approval_rec.object_type, p_approval_rec.object_id);
247 FETCH csr_approver_level INTO l_approver_level;
248 CLOSE csr_approver_level;
249
250 -- construct approvers table
251 l_approvers_tbl := approvers_tbl_type();
252 l_approvers_tbl.extend;
253 l_approvers_tbl(1).approver_type := 'USER';
254 l_approvers_tbl(1).approver_id := p_approval_rec.action_performed_by;
255 l_approvers_tbl(1).approver_level := l_approver_level;
256
257 -- Add_Access - List Approvers sent from Get_Approvers api
258 Add_Access(
259 p_api_version => p_api_version
260 ,p_init_msg_list => FND_API.G_FALSE
261 ,p_commit => FND_API.G_FALSE
262 ,p_validation_level => p_validation_level
263 ,x_return_status => x_return_status
264 ,x_msg_data => x_msg_data
265 ,x_msg_count => x_msg_count
266 ,p_approval_rec => p_approval_rec
267 ,p_approvers => l_approvers_tbl );
268
269 IF x_return_status = FND_API.g_ret_sts_error THEN
270 RAISE FND_API.g_exc_error;
271 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
272 RAISE FND_API.g_exc_unexpected_error;
273 END IF;
274
275 -- Update approval access table to revoke access
276 UPDATE ozf_approval_access
277 SET action_code = p_approval_rec.action_code
278 , action_date = SYSDATE
279 , action_performed_by = p_approval_rec.action_performed_by
280 WHERE object_type = p_approval_rec.object_type
281 AND object_id = p_approval_rec.object_id
282 AND approver_id = p_approval_rec.action_performed_by
283 AND approval_level = l_approver_level;
284
285 END IF;
286
287 OPEN csr_check_reassign_level (p_approval_rec.object_type, p_approval_rec.object_id);
288 FETCH csr_check_reassign_level INTO l_min_reassign_level;
289 CLOSE csr_check_reassign_level;
290
291 -- Debug Message
292 IF G_DEBUG THEN
293 ozf_utility_pvt.debug_message( 'Approval done by user in approval list? ' || l_approver_found );
294 ozf_utility_pvt.debug_message( 'Approver User Id ' || p_approval_rec.action_performed_by );
295 ozf_utility_pvt.debug_message( 'Approver Action ' || p_approval_rec.action_code );
296 ozf_utility_pvt.debug_message( 'Approver Type ' || l_approver_type );
297 ozf_utility_pvt.debug_message( 'Act Approver User Id ' || l_act_approver_id );
298 ozf_utility_pvt.debug_message( 'Act Approver Person/User Id ' || l_approver_id );
299 ozf_utility_pvt.debug_message( 'Is Super User ' || l_is_super_user );
300 ozf_utility_pvt.debug_message( 'Minimum Reassign Level ' || l_min_reassign_level );
301 ozf_utility_pvt.debug_message( 'l_action_code ' || l_action_code );
302 END IF;
303
304 /*
305 Check for minimum Reassign Level is added because , if it is 0 then the case is No AME Rule was
306 found for Transaction adn Default approver was found from profile
307 03/20/04 by feliu: to check l_action_code. when budget line get rejected, we can not go to ame to update status.
308 */
309 if l_min_reassign_level <> 0 AND l_action_code is NULL then
310
311 -- Update AME with approvers action
312 /*
313 type approverRecord is record(
314 user_id fnd_user.user_id%type,
315 person_id per_all_people_f.person_id%type,
316 first_name per_all_people_f.first_name%type,
317 last_name per_all_people_f.last_name%type,
318 api_insertion varchar2(1),
319 authority varchar2(1),
320 approval_status varchar2(50),
321 approval_type_id integer,
322 group_or_chain_id integer,
323 occurrence integer,
324 source varchar2(500));
325 */
326 if l_approver_type = 'PERSON' then
327 l_ame_approver_rec.person_id := l_approver_id;
328 else
329 if l_is_super_user = 'Y' then
330 l_ame_approver_rec.user_id := l_act_approver_id;
331 else
332 l_ame_approver_rec.user_id := p_approval_rec.action_performed_by;
333 end if ;
334 end if;
335
336 l_ame_approver_rec.authority := ame_util.authorityApprover;
337
338 IF p_approval_rec.action_code = 'FORWARD' THEN -- FORWARD
339 l_ame_approver_rec.approval_status := AME_UTIL.forwardStatus;
340 l_ame_approver_rec.api_insertion := ame_util.apiAuthorityInsertion;
341
342 -- Reassignment of Request. create a forwadee record
343 --use the Forwarder User id
344 l_ame_forward_rec.user_id := G_FORWARD_USER;
345 IF l_approver_found = 'N' THEN
346 l_ame_forward_rec.api_insertion := ame_util.apiAuthorityInsertion;
347 ELSE
348 l_ame_forward_rec.api_insertion := ame_util.apiAuthorityInsertion;
349 END IF;
350 l_ame_forward_rec.authority := ame_util.authorityApprover;
351
352
353 ELSIF p_approval_rec.action_code = 'REJECT' THEN
354 -- Rejection of Request
355 IF l_approver_found = 'N' THEN
356 l_ame_approver_rec.api_insertion := ame_util.apiAuthorityInsertion;
357 ELSE
358 l_ame_approver_rec.api_insertion := ame_util.oamGenerated;
359 END IF;
360 l_ame_approver_rec.approval_status := AME_UTIL.rejectStatus;
361
362 ELSIF p_approval_rec.action_code = 'RETURN' THEN
363 -- Rejection of Request
364 IF l_approver_found = 'N' THEN
365 l_ame_approver_rec.api_insertion := ame_util.apiAuthorityInsertion;
366 ELSE
367 l_ame_approver_rec.api_insertion := ame_util.oamGenerated;
368 END IF;
369 l_ame_approver_rec.approval_status := AME_UTIL.rejectStatus;
370
371 ELSIF p_approval_rec.action_code = 'APPROVE' THEN
372 -- Approval of Request
373 IF l_approver_found = 'N' THEN
374 l_ame_approver_rec.api_insertion := ame_util.apiAuthorityInsertion;
375 ELSE
376 l_ame_approver_rec.api_insertion := ame_util.oamGenerated;
377 END IF;
378 l_ame_approver_rec.approval_status := AME_UTIL.approvedStatus;
379 END IF;
380
381
382 -- Update AME of Approval Status
383 AME_API.updateApprovalStatus(applicationIdIn => l_application_id
384 ,transactionIdIn => p_approval_rec.object_id
385 ,approverIn => l_ame_approver_rec
386 ,transactionTypeIn => p_approval_rec.object_type
387 ,forwardeeIn => l_ame_forward_rec
388 );
389 end if; -- End if minimum reassign Level not 0
390
391 IF G_DEBUG THEN
392 ozf_utility_pvt.debug_message( 'Revoke Access ' || p_approval_rec.action_code );
393 END IF;
394 -- Revoke_Access - Revoke acces to previous appprovers in the chain
395 Revoke_Access(
396 p_api_version => p_api_version
397 ,p_init_msg_list => FND_API.G_FALSE
398 ,p_validation_level => p_validation_level
399 ,x_return_status => x_return_status
400 ,x_msg_data => x_msg_data
401 ,x_msg_count => x_msg_count
402 ,p_object_type => p_approval_rec.object_type
403 ,p_object_id => p_approval_rec.object_id);
404
405 IF x_return_status = FND_API.g_ret_sts_error THEN
406 RAISE FND_API.g_exc_error;
407 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
408 RAISE FND_API.g_exc_unexpected_error;
409 END IF;
410
411
412 IF l_min_reassign_level <> 0 THEN
413 IF p_approval_rec.action_code = 'REJECT' OR p_approval_rec.action_code = 'RETURN' THEN
414 AME_API.clearAllApprovals(applicationIdIn => l_application_id
415 ,transactionIdIn => p_approval_rec.object_id
416 ,transactionTypeIn => p_approval_rec.object_type
417 );
418 END IF;
419
420 END IF;
421
422 -- Debug Message
423 IF G_DEBUG THEN
424 ozf_utility_pvt.debug_message( l_api_name||': End');
425 END IF;
426 --Standard call to get message count and if count=1, get the message
427 FND_MSG_PUB.Count_And_Get (
428 p_encoded => FND_API.G_FALSE,
429 p_count => x_msg_count,
430 p_data => x_msg_data
431 );
432 EXCEPTION
433 WHEN FND_API.G_EXC_ERROR THEN
434 ROLLBACK TO Update_User_Action_PVT;
435 x_return_status := FND_API.G_RET_STS_ERROR;
436 -- Standard call to get message count and if count=1, get the message
437 FND_MSG_PUB.Count_And_Get (
438 p_encoded => FND_API.G_FALSE,
439 p_count => x_msg_count,
440 p_data => x_msg_data
441 );
442 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
443 ROLLBACK TO Update_User_Action_PVT;
444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
445 -- Standard call to get message count and if count=1, get the message
446 FND_MSG_PUB.Count_And_Get (
447 p_encoded => FND_API.G_FALSE,
448 p_count => x_msg_count,
449 p_data => x_msg_data
450 );
451 WHEN OTHERS THEN
452 ROLLBACK TO Update_User_Action_PVT;
453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
454 IF OZF_UNEXP_ERROR_ON
455 THEN
456 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
457 END IF;
458 -- Standard call to get message count and if count=1, get the message
459 FND_MSG_PUB.Count_And_Get (
460 p_encoded => FND_API.G_FALSE,
461 p_count => x_msg_count,
462 p_data => x_msg_data
463 );
464 --
465 END Update_User_Action;
466 ---------------------------------------------------------------------
467 -- PROCEDURE
468 -- Get_Approvers
469 --
470 -- PURPOSE
471 -- PARAMETERS
472 -- NOTES
473 ---------------------------------------------------------------------
474 PROCEDURE Get_Approvers(
475 p_api_version IN NUMBER
476 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
477 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
478
479 ,x_return_status OUT NOCOPY VARCHAR2
480 ,x_msg_data OUT NOCOPY VARCHAR2
481 ,x_msg_count OUT NOCOPY NUMBER
482
483 ,p_approval_rec IN approval_rec_type
484 ,x_approvers OUT NOCOPY approvers_tbl_type
485 ,x_final_approval_flag OUT NOCOPY VARCHAR2
486 )
487 IS
488
489
490 l_api_name CONSTANT varchar2(80) := 'Get_Approvers';
491 l_api_version CONSTANT number := 1.0;
492 l_application_id number := 682;
493 l_object_type varchar2(30) := p_approval_rec.object_type;
494 l_object_id number := p_approval_rec.object_id;
495 l_next_approver AME_UTIL.approverRecord;
496 l_approver_level number;
497 l_resource_id number;
498 --l_ame_approver_rec ame_util.approverRecord;
499 --l_ame_forward_rec ame_util.approverRecord default ame_util.emptyApproverRecord;
500
501 CURSOR csr_approver_level (p_object_type in varchar2, p_object_id in number) IS
502 SELECT nvl(max(approval_level),0)
503 FROM ozf_approval_access
504 WHERE object_type = p_object_type
505 AND object_id = p_object_id;
506
507 BEGIN
508
509 -- Standard begin of API savepoint
510 SAVEPOINT Get_Approvers_PVT;
511 -- Standard call to check for call compatibility.
512 IF NOT FND_API.Compatible_API_Call (
513 l_api_version,
514 p_api_version,
515 l_api_name,
516 G_PKG_NAME)
517 THEN
518 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
519 END IF;
520 -- Debug Message
521 IF G_DEBUG THEN
522 ozf_utility_pvt.debug_message( l_api_name||': Start');
523 END IF;
524 --Initialize message list if p_init_msg_list is TRUE.
525 IF FND_API.To_Boolean (p_init_msg_list) THEN
526 FND_MSG_PUB.initialize;
527 END IF;
528 -- Initialize API return status to sucess
529 x_return_status := FND_API.G_RET_STS_SUCCESS;
530
531 OPEN csr_approver_level (l_object_type, l_object_id);
532 FETCH csr_approver_level INTO l_approver_level;
533 CLOSE csr_approver_level;
534
535 -- increment approval level by 1 for next approval;
536 l_approver_level := l_approver_level + 1;
537
538
539 -- Get Approver list from Approvals Manager
540 AME_API.getNextApprover(applicationIdIn => l_application_id
541 ,transactionIdIn => p_approval_rec.object_id
542 ,transactionTypeIn => p_approval_rec.object_type
543 ,nextApproverOut => l_next_approver
544 );
545
546
547 IF l_next_approver.person_id IS NULL AND
548 l_next_approver.user_id IS NULL AND
549 l_next_approver.approval_status IS NULL
550 THEN
551 IF G_DEBUG THEN
552 ozf_utility_pvt.debug_message( 'AME did not return any approvers');
553 END IF;
554
555 -- If first approval, get default approver from profile
556 IF p_approval_rec.action_code = 'SUBMIT' THEN
557 IF G_DEBUG THEN
558 ozf_utility_pvt.debug_message( 'Defulting to 1 as default approver');
559 END IF;
560 -- Get default approver
561 x_approvers := approvers_tbl_type ();
562 x_approvers.extend;
563 x_approvers(1).approver_type := 'USER';
564 -- get user from profile (defaulat approver)
565 IF p_approval_rec.object_type = 'SPECIAL_PRICE' THEN
566 x_approvers(1).approver_id := to_number(fnd_profile.value('OZF_SP_DEFAULT_APPROVER'));
567 ELSIF p_approval_rec.object_type = 'SOFT_FUND' THEN
568 x_approvers(1).approver_id := to_number(fnd_profile.value('OZF_SF_DEFAULT_APPROVER'));
569 END IF;
570
571
572 x_approvers(1).approver_level := 0;
573 x_final_approval_flag := 'N';
574
575 /*
576 --Insert Default Approver into Group
577 l_ame_approver_rec.user_id := x_approvers(1).approver_id;
578 l_ame_approver_rec.authority := ame_util.authorityApprover;
579 l_ame_approver_rec.api_insertion := ame_util.apiAuthorityInsertion;
580 l_ame_approver_rec.approval_status := AME_UTIL.noResponseStatus;
581
582 AME_API.updateApprovalStatus(applicationIdIn => l_application_id
583 ,transactionIdIn => p_approval_rec.object_id
584 ,approverIn => l_ame_approver_rec
585 ,transactionTypeIn => p_approval_rec.object_type
586 ,forwardeeIn => l_ame_forward_rec
587 );
588 */
589
590 END IF;
591
592 -- If final approval, convey that information
593 IF p_approval_rec.action_code = 'APPROVE' THEN
594 IF G_DEBUG THEN
595 ozf_utility_pvt.debug_message( 'Setting to final approval');
596 END IF;
597 x_final_approval_flag := 'Y';
598 END IF;
599 ELSE
600 /*
601 -- Get resoutce id
602 l_resource_id := ozf_utility_pvt.get_resource_id (l_next_approver.user_id);
603
604 -- raise error if resource is null
605 IF l_resource_id IS NULL THEN
606 IF OZF_ERROR_ON THEN
607 ozf_utility_pvt.error_message('OZF_APPROVER_NOT_RESOURCE');
608 x_return_status := FND_API.g_ret_sts_error;
609 RAISE FND_API.G_EXC_ERROR;
610 END IF;
611 END IF;
612 */
613
614 IF G_DEBUG THEN
615 ozf_utility_pvt.debug_message( 'AME returned approvers');
616 END IF;
617 x_final_approval_flag := 'N';
618 -- Construct the out record of approvers
619 x_approvers := approvers_tbl_type ();
620 x_approvers.extend;
621 if l_next_approver.user_id is null then
622 x_approvers(1).approver_type := 'PERSON';
623 x_approvers(1).approver_id := l_next_approver.person_id;
624 else
625 x_approvers(1).approver_type := 'USER';
626 x_approvers(1).approver_id := l_next_approver.user_id;
627 end if;
628 x_approvers(1).approver_level := l_approver_level;
629 END IF;
630
631 -- Debug Message
632 /*
633 IF G_DEBUG THEN
634 ozf_utility_pvt.debug_message( l_api_name||': End' || x_approvers(1).approver_id);
635 END IF;
636 */
637 --Standard call to get message count and if count=1, get the message
638 FND_MSG_PUB.Count_And_Get (
639 p_encoded => FND_API.G_FALSE,
640 p_count => x_msg_count,
641 p_data => x_msg_data
642 );
643 EXCEPTION
644 WHEN FND_API.G_EXC_ERROR THEN
645 ROLLBACK TO Get_Approvers_PVT;
646 x_return_status := FND_API.G_RET_STS_ERROR;
647 -- Standard call to get message count and if count=1, get the message
648 FND_MSG_PUB.Count_And_Get (
649 p_encoded => FND_API.G_FALSE,
650 p_count => x_msg_count,
651 p_data => x_msg_data
652 );
653 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
654 ROLLBACK TO Get_Approvers_PVT;
655 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
656 -- Standard call to get message count and if count=1, get the message
657 FND_MSG_PUB.Count_And_Get (
658 p_encoded => FND_API.G_FALSE,
659 p_count => x_msg_count,
660 p_data => x_msg_data
661 );
662 WHEN OTHERS THEN
663 ROLLBACK TO Get_Approvers_PVT;
664 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
665 IF OZF_UNEXP_ERROR_ON
666 THEN
667 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
668 END IF;
669 -- Standard call to get message count and if count=1, get the message
670 FND_MSG_PUB.Count_And_Get (
671 p_encoded => FND_API.G_FALSE,
672 p_count => x_msg_count,
673 p_data => x_msg_data
674 );
675 --
676 END Get_Approvers;
677 ---------------------------------------------------------------------
678 -- PROCEDURE
679 -- Add_Access
680 --
681 -- PURPOSE
682 -- adds approvers access to table
683 --
684 -- PARAMETERS
685 -- NOTES
686 ---------------------------------------------------------------------
687 PROCEDURE Add_Access(
688 p_api_version IN NUMBER
689 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
690 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
691 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
692
693 ,x_msg_data OUT NOCOPY VARCHAR2
694 ,x_msg_count OUT NOCOPY NUMBER
695 ,x_return_status OUT NOCOPY VARCHAR2
696
697 ,p_approval_rec IN approval_rec_type
698 ,p_approvers IN approvers_tbl_type
699 )
700 IS
701 l_api_name CONSTANT varchar2(80) := 'Add_Access';
702 l_api_version CONSTANT number := 1.0;
703 l_approval_access_id NUMBER;
704 l_workflow_itemkey VARCHAR2(80);
705
706 CURSOR c_id IS
707 SELECT ozf_approval_access_s.NEXTVAL
708 FROM dual;
709
710 BEGIN
711 -- Standard begin of API savepoint
712 SAVEPOINT Add_Access_PVT;
713 -- Standard call to check for call compatibility.
714 IF NOT FND_API.Compatible_API_Call (
715 l_api_version,
716 p_api_version,
717 l_api_name,
718 G_PKG_NAME)
719 THEN
720 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
721 END IF;
722 -- Debug Message
723 IF G_DEBUG THEN
724 ozf_utility_pvt.debug_message( l_api_name||': Start');
725 END IF;
726 --Initialize message list if p_init_msg_list is TRUE.
727 IF FND_API.To_Boolean (p_init_msg_list) THEN
728 FND_MSG_PUB.initialize;
729 END IF;
730 -- Initialize API return status to sucess
731 x_return_status := FND_API.G_RET_STS_SUCCESS;
732
733 -- Get Primary Key
734 OPEN c_id;
735 FETCH c_id INTO l_approval_access_id;
736 CLOSE c_id;
737
738 IF p_approval_rec.object_type IS NULL THEN
739 IF OZF_ERROR_ON THEN
740 ozf_utility_pvt.error_message('OZF_OBJECT_TYPE_NOT_FOUND');
741 x_return_status := FND_API.g_ret_sts_error;
742 RAISE FND_API.G_EXC_ERROR;
743 END IF;
744 END IF;
745
746 IF p_approval_rec.object_id IS NULL THEN
747 IF OZF_ERROR_ON THEN
748 ozf_utility_pvt.error_message('OZF_OBJECT_ID_NOT_FOUND');
749 x_return_status := FND_API.g_ret_sts_error;
750 RAISE FND_API.G_EXC_ERROR;
751 END IF;
752 END IF;
753
754 -- Validate if the approvers record is valid
755 FOR i in 1..p_approvers.count LOOP
756 IF p_approvers(i).approver_type <> 'USER' and p_approvers(i).approver_type <> 'PERSON' THEN
757 IF OZF_ERROR_ON THEN
758 ozf_utility_pvt.error_message('OZF_APPROVER_NOT_USER');
759 x_return_status := FND_API.g_ret_sts_error;
760 RAISE FND_API.G_EXC_ERROR;
761 END IF;
762 END IF;
763 --IF NOT resource_valid(p_approvers(i).approver_id) THEN
764 -- IF OZF_ERROR_ON THEN
765 -- ozf_utility_pvt.error_message('OZF_APPROVER_NOT_RESOURCE');
766 -- x_return_status := FND_API.g_ret_sts_error;
767 -- RAISE FND_API.G_EXC_ERROR;
768 -- END IF;
769 --END IF;
770 IF p_approvers(i).approver_level IS NULL THEN
771 IF OZF_ERROR_ON THEN
772 ozf_utility_pvt.error_message('OZF_APPROVAL_LEVEL_NOT_FOUND');
773 x_return_status := FND_API.g_ret_sts_error;
774 RAISE FND_API.G_EXC_ERROR;
775 END IF;
776 END IF;
777 END LOOP;
778
779 --Insert data into ozf_approval_access_all
780 FOR i in 1..p_approvers.count LOOP
781 BEGIN
782 IF G_DEBUG THEN
783 ozf_utility_pvt.debug_message( 'Inserting data into OZF_APPROVAL_ACCESS table');
784 END IF;
785 --
786 INSERT INTO OZF_APPROVAL_ACCESS(
787 approval_access_id
788 ,object_version_number
789 ,last_update_date
790 ,last_updated_by
791 ,creation_date
792 ,created_by
793 ,last_update_login
794 ,object_type
795 ,object_id
796 ,approval_level
797 ,approver_type
798 ,approver_id
799 ,approval_access_flag
800 ,workflow_itemkey
801 ) VALUES (
802 l_approval_access_id
803 ,1
804 ,SYSDATE
805 ,G_USER_ID
806 ,SYSDATE
807 ,G_USER_ID
808 ,G_LOGIN_ID
809 ,p_approval_rec.object_type
810 ,p_approval_rec.object_id
811 ,p_approvers(i).approver_level
812 ,p_approvers(i).approver_type
813 ,p_approvers(i).approver_id
814 ,'Y'
815 ,l_workflow_itemkey
816 );
817 EXCEPTION
818 WHEN OTHERS THEN
819 IF OZF_ERROR_ON THEN
820 ozf_utility_pvt.error_message('OZF_APPROVAL_ACCESS_INSERT_ERR');
821 x_return_status := FND_API.g_ret_sts_error;
822 RAISE FND_API.G_EXC_ERROR;
823 END IF;
824 END;
825 END LOOP;
826
827 --Standard check of commit
828 IF FND_API.To_Boolean ( p_commit ) THEN
829 COMMIT WORK;
830 END IF;
831 -- Debug Message
832 IF G_DEBUG THEN
833 ozf_utility_pvt.debug_message( l_api_name||': End');
834 END IF;
835 --Standard call to get message count and if count=1, get the message
836 FND_MSG_PUB.Count_And_Get (
837 p_encoded => FND_API.G_FALSE,
838 p_count => x_msg_count,
839 p_data => x_msg_data
840 );
841 EXCEPTION
842 WHEN FND_API.G_EXC_ERROR THEN
843 ROLLBACK TO Add_Access_PVT;
844 x_return_status := FND_API.G_RET_STS_ERROR;
845 -- Standard call to get message count and if count=1, get the message
846 FND_MSG_PUB.Count_And_Get (
847 p_encoded => FND_API.G_FALSE,
848 p_count => x_msg_count,
849 p_data => x_msg_data
850 );
851 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
852 ROLLBACK TO Add_Access_PVT;
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
854 -- Standard call to get message count and if count=1, get the message
855 FND_MSG_PUB.Count_And_Get (
856 p_encoded => FND_API.G_FALSE,
857 p_count => x_msg_count,
858 p_data => x_msg_data
859 );
860 WHEN OTHERS THEN
861 ROLLBACK TO Add_Access_PVT;
862 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
863 IF OZF_UNEXP_ERROR_ON
864 THEN
865 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
866 END IF;
867 -- Standard call to get message count and if count=1, get the message
868 FND_MSG_PUB.Count_And_Get (
869 p_encoded => FND_API.G_FALSE,
870 p_count => x_msg_count,
871 p_data => x_msg_data
872 );
873 --
874 END Add_Access;
875 ---------------------------------------------------------------------
876 -- PROCEDURE
877 -- Revoke_Access
878 --
879 -- PURPOSE
880 -- Revokes access to current approvers
881 --
882 -- PARAMETERS
883 --
884 -- NOTES
885 ---------------------------------------------------------------------
886 PROCEDURE Revoke_Access (
887 p_api_version IN NUMBER
888 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
889 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
890 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
891
892 ,x_return_status OUT NOCOPY VARCHAR2
893 ,x_msg_data OUT NOCOPY VARCHAR2
894 ,x_msg_count OUT NOCOPY NUMBER
895
896 ,p_object_type IN VARCHAR2
897 ,p_object_id IN NUMBER
898 )
899 IS
900 l_api_name CONSTANT varchar2(80) := 'Revoke_Access';
901 l_api_version CONSTANT number := 1.0;
902 l_approval_access_id number;
903
904 CURSOR csr_curr_approvers (p_object_type IN VARCHAR2, p_object_id IN NUMBER )IS
905 SELECT approval_access_id
906 FROM ozf_approval_access
907 WHERE approval_access_flag = 'Y'
908 AND object_type = p_object_type
909 AND object_id = p_object_id;
910
911 BEGIN
912 -- Standard begin of API savepoint
913 SAVEPOINT Revoke_Access_PVT;
914 -- Standard call to check for call compatibility.
915 IF NOT FND_API.Compatible_API_Call (
916 l_api_version,
917 p_api_version,
918 l_api_name,
919 G_PKG_NAME)
920 THEN
921 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
922 END IF;
923 -- Debug Message
924 IF G_DEBUG THEN
925 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
926 FND_MESSAGE.Set_Token('TEXT',l_api_name||': Start');
927 FND_MSG_PUB.Add;
928 END IF;
929 --Initialize message list if p_init_msg_list is TRUE.
930 IF FND_API.To_Boolean (p_init_msg_list) THEN
931 FND_MSG_PUB.initialize;
932 END IF;
933 -- Initialize API return status to sucess
934 x_return_status := FND_API.G_RET_STS_SUCCESS;
935
936 -- Update records in ozf_approval_access_all to revoke access
937 OPEN csr_curr_approvers(p_object_type, p_object_id);
938 LOOP
939 FETCH csr_curr_approvers INTO l_approval_access_id;
940 EXIT WHEN csr_curr_approvers%NOTFOUND;
941
942 -- Update approval access table to revoke access
943 UPDATE ozf_approval_access
944 SET approval_access_flag = 'N'
945 WHERE approval_access_id = l_approval_access_id;
946
947 -- Reset value to null
948 l_approval_access_id := null;
949 END LOOP;
950 CLOSE csr_curr_approvers;
951
952 --Standard check of commit
953 IF FND_API.To_Boolean ( p_commit ) THEN
954 COMMIT WORK;
955 END IF;
956 -- Debug Message
957 IF G_DEBUG THEN
958 ozf_utility_pvt.debug_message( l_api_name||': End');
959 END IF;
960 --Standard call to get message count and if count=1, get the message
961 FND_MSG_PUB.Count_And_Get (
962 p_encoded => FND_API.G_FALSE,
963 p_count => x_msg_count,
964 p_data => x_msg_data
965 );
966 EXCEPTION
967 WHEN FND_API.G_EXC_ERROR THEN
968 ROLLBACK TO Revoke_Access_PVT;
969 x_return_status := FND_API.G_RET_STS_ERROR;
970 -- Standard call to get message count and if count=1, get the message
971 FND_MSG_PUB.Count_And_Get (
972 p_encoded => FND_API.G_FALSE,
973 p_count => x_msg_count,
974 p_data => x_msg_data
975 );
976 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
977 ROLLBACK TO Revoke_Access_PVT;
978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
979 -- Standard call to get message count and if count=1, get the message
980 FND_MSG_PUB.Count_And_Get (
981 p_encoded => FND_API.G_FALSE,
982 p_count => x_msg_count,
983 p_data => x_msg_data
984 );
985 WHEN OTHERS THEN
986 ROLLBACK TO Revoke_Access_PVT;
987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
988 IF OZF_UNEXP_ERROR_ON
989 THEN
990 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
991 END IF;
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 --
999 END Revoke_Access;
1000 -----------------------------------------------
1001 -- Return event name if the entered event exist
1002 -- Otherwise return NOTFOUND
1003 -----------------------------------------------
1004 FUNCTION Check_Event(p_event_name IN VARCHAR2)
1005 RETURN VARCHAR2
1006 IS
1007
1008 CURSOR c_event_name IS
1009 SELECT name
1010 FROM wf_events
1011 WHERE name = p_event_name;
1012
1013 l_event_name VARCHAR2(240);
1014
1015 BEGIN
1016
1017 OPEN c_event_name;
1018 FETCH c_event_name INTO l_event_name;
1019 IF c_event_name%NOTFOUND THEN
1020 l_event_name := 'NOTFOUND';
1021 END IF;
1022 CLOSE c_event_name;
1023
1024 RETURN l_event_name;
1025
1026 END Check_Event;
1027 ------------------------------------------------------
1028 -- Add Application-Context parameter to the enter list
1029 ------------------------------------------------------
1030 PROCEDURE Construct_Param_List (
1031 x_list IN OUT NOCOPY WF_PARAMETER_LIST_T,
1032 p_user_id IN VARCHAR2 DEFAULT NULL,
1033 p_resp_id IN VARCHAR2 DEFAULT NULL,
1034 p_resp_appl_id IN VARCHAR2 DEFAULT NULL,
1035 p_security_group_id IN VARCHAR2 DEFAULT NULL,
1036 p_org_id IN VARCHAR2 DEFAULT NULL)
1037 IS
1038 l_user_id VARCHAR2(255) := p_user_id;
1039 l_resp_appl_id VARCHAR2(255) := p_resp_appl_id;
1040 l_resp_id VARCHAR2(255) := p_resp_id;
1041 l_security_group_id VARCHAR2(255) := p_security_group_id;
1042 l_org_id VARCHAR2(255) := p_org_id;
1043 l_param WF_PARAMETER_T;
1044 l_rang NUMBER;
1045 BEGIN
1046 l_rang := 0;
1047
1048 IF l_user_id IS NULL THEN
1049 l_user_id := fnd_profile.value('USER_ID');
1050 END IF;
1051
1052 l_param := WF_PARAMETER_T( NULL, NULL );
1053 -- fill the parameters list
1054 x_list.extend;
1055 l_param.SetName('USER_ID');
1056 l_param.SetValue(l_user_id);
1057 l_rang := l_rang + 1;
1058 x_list(l_rang) := l_param;
1059
1060 IF l_resp_id IS NULL THEN
1061 l_resp_id := fnd_profile.value('RESP_ID');
1062 END IF;
1063
1064 l_param := WF_PARAMETER_T( NULL, NULL );
1065 -- fill the parameters list
1066 x_list.extend;
1067 l_param.SetName('RESP_ID');
1068 l_param.SetValue(l_resp_id);
1069 l_rang := l_rang + 1;
1070 x_list(l_rang) := l_param;
1071
1072 IF l_resp_appl_id IS NULL THEN
1073 l_resp_appl_id := fnd_profile.value('RESP_APPL_ID');
1074 END IF;
1075
1076 l_param := WF_PARAMETER_T( NULL, NULL );
1077 -- fill the parameters list
1078 x_list.extend;
1079 l_param.SetName('RESP_APPL_ID');
1080 l_param.SetValue(l_resp_appl_id);
1081 l_rang := l_rang + 1;
1082 x_list(l_rang) := l_param;
1083
1084 IF l_security_group_id IS NULL THEN
1085 l_security_group_id := fnd_profile.value('SECURITY_GROUP_ID');
1086 END IF;
1087 l_param := WF_PARAMETER_T( NULL, NULL );
1088 -- fill the parameters list
1089 x_list.extend;
1090 l_param.SetName('SECURITY_GROUP_ID');
1091 l_param.SetValue(l_security_group_id);
1092 l_rang := l_rang + 1;
1093 x_list(l_rang) := l_param;
1094
1095 IF l_org_id IS NULL THEN
1096 l_org_id := fnd_profile.value('ORG_ID');
1097 END IF;
1098
1099 l_param := WF_PARAMETER_T( NULL, NULL );
1100 -- fill the parameters list
1101 x_list.extend;
1102 l_param.SetName('ORG_ID');
1103 l_param.SetValue(l_org_id );
1104 l_rang := l_rang + 1;
1105 x_list(l_rang) := l_param;
1106
1107 END Construct_Param_List;
1108 ---------------------------------------------------------------------
1109 -- PROCEDURE
1110 -- Raise_Event
1111 --
1112 -- PURPOSE
1113 -- Raise business event
1114 --
1115 -- PARAMETERS
1116 --
1117 -- NOTES
1118 ---------------------------------------------------------------------
1119 PROCEDURE Raise_Event (
1120 x_return_status OUT NOCOPY VARCHAR2
1121 ,x_msg_data OUT NOCOPY VARCHAR2
1122 ,x_msg_count OUT NOCOPY NUMBER
1123 ,p_event_name IN VARCHAR2
1124 ,p_event_key IN VARCHAR2
1125 --,p_data IN CLOB DEFAULT NULL
1126 ,p_approval_rec IN approval_rec_type)
1127 IS
1128 l_api_name CONSTANT varchar2(80) := 'Raise_Event';
1129 l_api_version CONSTANT number := 1.0;
1130
1131 l_item_key VARCHAR2(240);
1132 l_event VARCHAR2(240);
1133
1134 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1135 l_parameter_t wf_parameter_t := wf_parameter_t(null, null);
1136
1137 BEGIN
1138
1139 SAVEPOINT Raise_Event_PVT;
1140
1141 l_event := Check_Event(p_event_name);
1142
1143 IF l_event = 'NOTFOUND' THEN
1144 IF OZF_ERROR_ON THEN
1145 ozf_utility_pvt.error_message('OZF_WF_EVENT_NAME_NULL', 'NAME', p_event_name);
1146 END IF;
1147 x_return_status := FND_API.g_ret_sts_error;
1148 RAISE FND_API.G_EXC_ERROR;
1149 END IF;
1150
1151 l_parameter_t.setName('OBJECT_TYPE');
1152 l_parameter_t.setValue(p_approval_rec.object_type);
1153 l_parameter_list.extend;
1154 l_parameter_list(1) := l_parameter_t;
1155
1156 l_parameter_t.setName('OBJECT_ID');
1157 l_parameter_t.setValue(p_approval_rec.object_id);
1158 l_parameter_list.extend;
1159 l_parameter_list(2) := l_parameter_t;
1160
1161 l_parameter_t.setName('STATUS_CODE');
1162 l_parameter_t.setValue(p_approval_rec.status_code);
1163 l_parameter_list.extend;
1164 l_parameter_list(3) := l_parameter_t;
1165
1166 -- Raise business event
1167 Wf_Event.Raise
1168 ( p_event_name => p_event_name,
1169 p_event_key => p_event_key,
1170 p_parameters => l_parameter_list,
1171 p_event_data => NULL);
1172
1173 EXCEPTION
1174 WHEN FND_API.G_EXC_ERROR THEN
1175 ROLLBACK TO Raise_Event_PVT;
1176 x_return_status := FND_API.G_RET_STS_ERROR;
1177 -- Standard call to get message count and if count=1, get the message
1178 FND_MSG_PUB.Count_And_Get (
1179 p_encoded => FND_API.G_FALSE,
1180 p_count => x_msg_count,
1181 p_data => x_msg_data
1182 );
1183 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1184 ROLLBACK TO Raise_Event_PVT;
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186 -- Standard call to get message count and if count=1, get the message
1187 FND_MSG_PUB.Count_And_Get (
1188 p_encoded => FND_API.G_FALSE,
1189 p_count => x_msg_count,
1190 p_data => x_msg_data
1191 );
1192 WHEN OTHERS THEN
1193 ROLLBACK TO Raise_Event_PVT;
1194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1195 IF OZF_UNEXP_ERROR_ON
1196 THEN
1197 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1198 END IF;
1199 -- Standard call to get message count and if count=1, get the message
1200 FND_MSG_PUB.Count_And_Get (
1201 p_encoded => FND_API.G_FALSE,
1202 p_count => x_msg_count,
1203 p_data => x_msg_data
1204 );
1205 --
1206 END Raise_Event;
1207 ---------------------------------------------------------------------
1208 -- PROCEDURE
1209 -- Send_Notification
1210 --
1211 -- PURPOSE
1212 -- Sends notifications to approvers
1213 --
1214 -- PARAMETERS
1215 --
1216 -- NOTES
1217 ---------------------------------------------------------------------
1218 PROCEDURE Send_Notification (
1219 p_api_version IN NUMBER
1220 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1221 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1222 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1223
1224 ,x_return_status OUT NOCOPY VARCHAR2
1225 ,x_msg_data OUT NOCOPY VARCHAR2
1226 ,x_msg_count OUT NOCOPY NUMBER
1227
1228 ,p_benefit_id IN NUMBER
1229 ,p_partner_id IN NUMBER
1230 ,p_msg_callback_api IN VARCHAR2
1231 ,p_user_callback_api IN VARCHAR2
1232 ,p_approval_rec IN approval_rec_type
1233 )
1234 IS
1235 l_api_name CONSTANT varchar2(80) := 'Send_Notification';
1236 l_api_version CONSTANT number := 1.0;
1237 l_object_type varchar2(30) := p_approval_rec.object_type;
1238 l_object_id number := p_approval_rec.object_id;
1239 l_status varchar2(30) := p_approval_rec.status_code;
1240 l_msg_callback_api varchar2(240) := p_msg_callback_api;
1241 l_user_callback_api varchar2(240) := p_user_callback_api;
1242 l_partner_id number := p_partner_id;
1243 l_benefit_id number:= p_benefit_id;
1244
1245 l_final_approval number;
1246
1247 CURSOR csr_message (p_object_id number, p_status varchar2, p_user_role varchar2) IS
1248 SELECT user_role
1249 , wf_message_type
1250 , wf_message_name
1251 FROM pv_notification_setups
1252 WHERE benefit_id = p_object_id
1253 AND entity_status = p_status
1254 AND user_role like p_user_role;
1255
1256
1257 CURSOR csr_final_approval (p_object_type varchar2, p_object_id number) IS
1258 SELECT count(1)
1259 FROM ozf_approval_access
1260 WHERE object_type = p_object_type
1261 AND object_id = p_object_id
1262 AND approval_access_flag = 'Y';
1263
1264
1265
1266 CURSOR csr_cm (p_partner_id number) IS
1267 SELECT fnd_user.user_name
1268 FROM pv_partner_accesses acc
1269 , jtf_rs_resource_extns res
1270 , fnd_user
1271 WHERE acc.partner_id = p_partner_id
1272 AND acc.resource_id = res.resource_id
1273 AND res.user_id = fnd_user.user_id;
1274
1275
1276 CURSOR csr_approvers (p_object_type varchar2, p_object_id number) IS
1277 SELECT fu.user_name
1278 FROM ozf_approval_access oaa
1279 , fnd_user fu
1280 --, jtf_rs_resource_extns jre
1281 WHERE oaa.object_type = p_object_type
1282 AND oaa.object_id = p_object_id
1283 AND oaa.approver_type = 'USER'
1284 --AND fu.user_id = jre.user_id
1285 --AND oaa.approver_id = jre.resource_id
1286 AND oaa.approver_id = fu.user_id
1287 AND oaa.approval_access_flag = 'Y'
1288 UNION
1289 SELECT jre.user_name
1290 FROM ozf_approval_access oaa
1291 , jtf_rs_resource_extns jre
1292 WHERE oaa.object_type = p_object_type
1293 AND oaa.object_id = p_object_id
1294 AND oaa.approver_type = 'PERSON'
1295 AND oaa.approver_id = jre.source_id
1296 AND oaa.approval_access_flag = 'Y'
1297 group by jre.user_name;
1298
1299
1300
1301 l_adhoc_role varchar2(200);
1302 l_role_list varchar2(3000);
1303 l_user_type varchar2(30);
1304 l_msg_type varchar2(30);
1305 l_msg_name varchar2(30);
1306 l_item_key varchar2(200);
1307 l_item_type varchar2(30);
1308
1309 l_group_notify_id number;
1310 l_context varchar2(1000);
1311 l_user_role varchar2(240);
1312
1313 l_execute_str varchar2(3000); -- bug 5058027
1314
1315 BEGIN
1316 -- Standard begin of API savepoint
1317 SAVEPOINT Send_Notification_PVT;
1318 -- Standard call to check for call compatibility.
1319 IF NOT FND_API.Compatible_API_Call (
1320 l_api_version,
1321 p_api_version,
1322 l_api_name,
1323 G_PKG_NAME)
1324 THEN
1325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1326 END IF;
1327 -- Debug Message
1328 IF G_DEBUG THEN
1329 ozf_utility_pvt.debug_message( l_api_name||': Start');
1330 END IF;
1331 --Initialize message list if p_init_msg_list is TRUE.
1332 IF FND_API.To_Boolean (p_init_msg_list) THEN
1333 FND_MSG_PUB.initialize;
1334 END IF;
1335 -- Initialize API return status to sucess
1336 x_return_status := FND_API.G_RET_STS_SUCCESS;
1337
1338 IF l_user_role is null THEN
1339 l_user_role := '%';
1340 ELSE
1341 l_user_role := l_user_role; -- p_user_role;
1342 END IF;
1343
1344 IF G_DEBUG THEN
1345 ozf_utility_pvt.debug_message( 'Before constructing message ' || l_user_role || ' ' || l_object_id || ' ' || l_status);
1346 END IF;
1347
1348 /*
1349 If Approved Status , Chek if it if Final Approval or Not
1350 if Final Approval Then Send Approved Notification else Send Submitted Notification
1351 */
1352 if l_status = 'APPROVED' then
1353 OPEN csr_final_approval (l_object_type, l_object_id);
1354 FETCH csr_final_approval INTO l_final_approval;
1355 CLOSE csr_final_approval;
1356 if l_final_approval <> 0 then
1357 l_status := 'SUBMITTED_FOR_APPROVAL';
1358 end if;
1359
1360 end if;
1361
1362 OPEN csr_message(p_benefit_id, l_status, l_user_role);
1363 LOOP
1364 FETCH csr_message INTO l_user_type, l_msg_type, l_msg_name;
1365 EXIT WHEN csr_message%NOTFOUND;
1366
1367 IF G_DEBUG THEN
1368 ozf_utility_pvt.debug_message('Executing Callback API ' || l_user_callback_api);
1369 END IF;
1370
1371 l_role_list := '';
1372
1373 -- execute callback api to get users to send notification
1374 -- BUG 5058027 (+)
1375 -- EXECUTE IMMEDIATE 'SELECT ' || l_user_callback_api ||
1376 -- '(:itemtype, :entity_id, :usertype, :status) FROM dual'
1377 -- INTO l_role_list
1378 -- USING l_object_type, l_object_id, l_user_type, l_status ;
1379
1380 -- l_execute_str := 'BEGIN ' ||
1381 -- l_user_callback_api||'(:itemtype, :entity_id, :usertype, :status); '||
1382 -- 'END;';
1383 -- EXECUTE IMMEDIATE l_execute_str USING IN l_object_type, IN l_object_id, IN l_user_type, IN l_status;
1384 --
1385 -- Bug 5534346: Undone above changes - callback to a function doesnt work this way
1386
1387 EXECUTE IMMEDIATE 'SELECT ' || l_user_callback_api ||
1388 '(:itemtype, :entity_id, :usertype, :status) FROM dual'
1389 INTO l_role_list
1390 USING l_object_type, l_object_id, l_user_type, l_status ;
1391
1392 -- execute pre-defined user list criterias
1393 IF l_role_list IS NULL THEN
1394 IF G_DEBUG THEN
1395 ozf_utility_pvt.debug_message('No users found from Callback API ' || l_user_callback_api);
1396 END IF;
1397
1398 IF l_user_type = 'CHANNEL_MANAGER' then
1399 FOR l_row IN csr_cm(l_partner_id) LOOP
1400 l_role_list := l_role_list || ',' || l_row.user_name;
1401 END LOOP;
1402 l_role_list := substr(l_role_list,2);
1403 ELSIF l_user_type = 'BENEFIT_APPROVER' THEN
1404 FOR l_row IN csr_approvers(l_object_type, l_object_id) LOOP
1405 l_role_list := l_role_list || ',' || l_row.user_name;
1406 END LOOP;
1407 l_role_list := substr(l_role_list,2);
1408 ELSE
1409 IF G_DEBUG THEN
1410 ozf_utility_pvt.debug_message('No users found ');
1411 END IF;
1412 END IF;
1413 END IF;
1414
1415 IF G_DEBUG THEN
1416 ozf_utility_pvt.debug_message('Users List ' || l_user_type || 'for sending notification' || l_role_list);
1417 END IF;
1418
1419 -- users returned from the search
1420 IF length(l_role_list) <> 0 THEN
1421 l_item_key := l_msg_type||'|'||l_user_type||'|'||l_msg_name||'|'||l_object_id||
1422 '|'||to_char(sysdate,'YYYYMMDDHH24MISS');
1423
1424 IF l_object_type = 'SPECIAL_PRICE' THEN
1425 l_item_type := 'OZFSPBEN';
1426 ELSIF l_object_type = 'SOFT_FUND' THEN
1427 l_item_type := 'OZFSFBEN';
1428 END IF;
1429
1430 -- l_item_type := 'PVREFFRL';
1431
1432 IF G_DEBUG THEN
1433 ozf_utility_pvt.debug_message('Creating process for itemtype:' || l_item_type || ' itemkey: ' || l_item_key);
1434 END IF;
1435
1436 -- Create WF process to send notification
1437 wf_engine.CreateProcess ( ItemType => l_item_type,
1438 ItemKey => l_item_key,
1439 process => 'NOOP_PROCESS',
1440 user_key => l_item_key);
1441
1442 IF G_DEBUG THEN
1443 ozf_utility_pvt.debug_message('Executing msg callback' || l_msg_callback_api );
1444 END IF;
1445
1446 -- execute callback api to set the message attributes
1447 EXECUTE IMMEDIATE 'BEGIN ' ||
1448 l_msg_callback_api || '(:itemtype, :itemkey, :entity_id, :usertype, :status); ' ||
1449 'END;'
1450 USING l_item_type, l_item_key, l_object_id, l_user_type, l_status;
1451
1452 IF G_DEBUG THEN
1453 ozf_utility_pvt.debug_message('Adding adhoc users' || l_role_list );
1454 END IF;
1455
1456 -- create an adhoc role with named after itemkey
1457 l_adhoc_role := l_item_key;
1458 wf_directory.CreateAdHocRole(role_name => l_adhoc_role,
1459 role_display_name => l_adhoc_role,
1460 role_users => l_role_list);
1461
1462 l_context := l_msg_type || ':' || l_item_key || ':';
1463
1464 IF G_DEBUG THEN
1465 ozf_utility_pvt.debug_message('Sending Notification to adhoc role ' || l_msg_type || ' ' || l_msg_name);
1466 END IF;
1467
1468 -- set the message to be sent
1469 l_group_notify_id := wf_notification.sendGroup(
1470 role => l_adhoc_role,
1471 msg_type => l_msg_type,
1472 msg_name => l_msg_name,
1473 due_date => null,
1474 callback => 'wf_engine.cb',
1475 context => l_context,
1476 send_comment => NULL,
1477 priority => NULL );
1478
1479 IF G_DEBUG THEN
1480 ozf_utility_pvt.debug_message('Sent notification to role: ' || l_adhoc_role);
1481 ozf_utility_pvt.debug_message('Using message: ' || l_msg_name || '. Notify id: ' || l_group_notify_id );
1482 END IF;
1483
1484 -- start the notification process to send message
1485 wf_engine.StartProcess(itemtype => l_item_type,
1486 itemkey => l_item_key);
1487 -- no users returned from the search
1488 ELSE
1489 IF G_DEBUG THEN
1490 ozf_utility_pvt.debug_message('No users found for user type: ' || l_user_type);
1491 END IF;
1492 END IF;
1493 END LOOP;
1494 CLOSE csr_message;
1495
1496 -- Update WorkFlow Item Key in approval Access Table
1497 update ozf_approval_access
1498 set workflow_itemkey = substr(l_item_key,1,239)
1499 where object_type = l_object_type
1500 and object_id = l_object_id
1501 and approval_level = ( select max (approval_level)
1502 from ozf_approval_access
1503 where object_type = l_object_type
1504 and object_id = l_object_id);
1505
1506 --Standard check of commit
1507 IF FND_API.To_Boolean ( p_commit ) THEN
1508 COMMIT WORK;
1509 END IF;
1510
1511 -- Debug Message
1512 IF G_DEBUG THEN
1513 ozf_utility_pvt.debug_message( l_api_name||': End');
1514 END IF;
1515 --Standard call to get message count and if count=1, get the message
1516 FND_MSG_PUB.Count_And_Get (
1517 p_encoded => FND_API.G_FALSE,
1518 p_count => x_msg_count,
1519 p_data => x_msg_data
1520 );
1521 EXCEPTION
1522 WHEN FND_API.G_EXC_ERROR THEN
1523 ROLLBACK TO Send_Notification_PVT;
1524 x_return_status := FND_API.G_RET_STS_ERROR;
1525 -- Standard call to get message count and if count=1, get the message
1526 FND_MSG_PUB.Count_And_Get (
1527 p_encoded => FND_API.G_FALSE,
1528 p_count => x_msg_count,
1529 p_data => x_msg_data
1530 );
1531 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1532 ROLLBACK TO Send_Notification_PVT;
1533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1534 -- Standard call to get message count and if count=1, get the message
1535 FND_MSG_PUB.Count_And_Get (
1536 p_encoded => FND_API.G_FALSE,
1537 p_count => x_msg_count,
1538 p_data => x_msg_data
1539 );
1540 WHEN OTHERS THEN
1541 -- ozf_utility_pvt.debug_message('Error OTHERS >>>>>>>>> ' || substr(sqlerrm,1,140));
1542 ROLLBACK TO Send_Notification_PVT;
1543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1544 IF OZF_UNEXP_ERROR_ON
1545 THEN
1546 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1547 END IF;
1548 -- Standard call to get message count and if count=1, get the message
1549 FND_MSG_PUB.Count_And_Get (
1550 p_encoded => FND_API.G_FALSE,
1551 p_count => x_msg_count,
1552 p_data => x_msg_data
1553 );
1554 --
1555 END Send_Notification;
1556 ---------------------------------------------------------------------
1557 -- PROCEDURE
1558 -- Process_User_Action
1559
1560 --
1561 -- PURPOSE
1562 -- Handles the approvals and rejections of objects
1563 --
1564 -- PARAMETERS
1565 --
1566 -- NOTES
1567 -- 1. object_version_number will be set to 1.
1568 ---------------------------------------------------------------------
1569 PROCEDURE Process_User_Action (
1570 p_api_version IN NUMBER
1571 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1572 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1573 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1574
1575 ,x_return_status OUT NOCOPY VARCHAR2
1576 ,x_msg_data OUT NOCOPY VARCHAR2
1577 ,x_msg_count OUT NOCOPY NUMBER
1578
1579 ,p_approval_rec IN approval_rec_type
1580 ,p_approver_id IN NUMBER
1581 ,x_final_approval_flag OUT NOCOPY VARCHAR2
1582 )
1583 IS
1584 l_api_name CONSTANT varchar2(80) := 'Process_User_Action';
1585 l_api_version CONSTANT number := 1.0;
1586 l_approvers_tbl approvers_tbl_type;
1587
1588 --l_event_name varchar2(240);
1589 l_event_name varchar2(240) ;
1590 l_event_key varchar2(240);
1591 l_msg_callback_api varchar2(240);
1592 l_user_callback_api varchar2(240);
1593 l_benefit_id number;
1594 l_partner_id number;
1595 l_final_approval_flag varchar2(1) := 'N';
1596 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1597
1598 CURSOR csr_forward_reassign (p_request_id in number , p_object_type in varchar2) IS
1599 select nvl(approval_level,1) + 1
1600 from ozf_approval_access
1601 where object_id = p_request_id
1602 and object_type = p_object_type
1603 and approval_access_flag = 'Y'
1604 and rownum < 2;
1605
1606 CURSOR csr_request (p_request_id in number) IS
1607 select benefit_id
1608 , partner_id
1609 from ozf_request_headers_all_b
1610 where request_header_id = p_request_id;
1611
1612 BEGIN
1613 -- Standard begin of API savepoint
1614 SAVEPOINT Process_User_Action_PVT;
1615 -- Standard call to check for call compatibility.
1616 IF NOT FND_API.Compatible_API_Call (
1617 l_api_version,
1618 p_api_version,
1619 l_api_name,
1620 G_PKG_NAME)
1621 THEN
1622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1623 END IF;
1624 -- Debug Message
1625 IF G_DEBUG THEN
1626 ozf_utility_pvt.debug_message( l_api_name||': Start');
1627 END IF;
1628 --Initialize message list if p_init_msg_list is TRUE.
1629 IF FND_API.To_Boolean (p_init_msg_list) THEN
1630 FND_MSG_PUB.initialize;
1631 END IF;
1632 -- Initialize API return status to sucess
1633 x_return_status := FND_API.G_RET_STS_SUCCESS;
1634
1635
1636 /*
1637 Action Code will be Notify when , the Final Approval has Happened
1638 */
1639 IF p_approval_rec.action_code = 'NOTIFY' THEN
1640 x_final_approval_flag := 'Y';
1641
1642 IF p_approval_rec.object_type = 'SPECIAL_PRICE' THEN
1643 l_event_name := 'oracle.apps.ozf.request.SpecialPrice.approval';
1644 ELSIF p_approval_rec.object_type = 'SOFT_FUND' THEN
1645 l_event_name := 'oracle.apps.ozf.request.SoftFund.approval';
1646 END IF;
1647
1648 l_event_key := p_approval_rec.object_type || ':' || p_approval_rec.object_id || ':' || to_char(sysdate, 'DD:MON:YYYY HH:MI:SS');
1649
1650 -- Raise_Event -> Event_Subscription -> Send_Notification
1651 Raise_Event (
1652 x_return_status => l_return_status
1653 ,x_msg_data => x_msg_data
1654 ,x_msg_count => x_msg_count
1655 ,p_event_name => l_event_name
1656 ,p_event_key => l_event_key
1657 ,p_approval_rec => p_approval_rec );
1658
1659 return;
1660 END IF;
1661
1662 -- Update AME/approval tbl of users action and revoke access to existing approvers
1663 IF p_approval_rec.action_code = 'APPROVE' OR
1664 p_approval_rec.action_code = 'REJECT' OR
1665 p_approval_rec.action_code = 'RETURN' OR
1666 p_approval_rec.action_code = 'FORWARD'
1667 THEN
1668 -- Add the new approver to forwarder rec
1669 IF p_approval_rec.action_code = 'FORWARD' THEN
1670 G_FORWARD_USER := p_approver_id;
1671 -- Assign new approver for the object
1672 l_approvers_tbl := approvers_tbl_type();
1673 l_approvers_tbl.extend;
1674 l_approvers_tbl(1).APPROVER_TYPE := 'USER';
1675 l_approvers_tbl(1).APPROVER_ID := p_approver_id;
1676 OPEN csr_forward_reassign (p_approval_rec.object_id, p_approval_rec.object_type);
1677 FETCH csr_forward_reassign INTO l_approvers_tbl(1).APPROVER_LEVEL;
1678 CLOSE csr_forward_reassign;
1679
1680 --l_approvers_tbl(1).APPROVER_LEVEL := 5; -- how to decide level for reassign???
1681
1682 IF G_DEBUG THEN
1683 ozf_utility_pvt.debug_message( 'Forward Request ' || p_approval_rec.action_code );
1684 END IF;
1685
1686 -- Raise error if no approvers are sent for reassignment.
1687 IF l_approvers_tbl.count = 0 THEN
1688 IF OZF_ERROR_ON THEN
1689 ozf_utility_pvt.error_message('OZF_NO_APPR_FOUND_FOR_FORWARD');
1690 x_return_status := FND_API.g_ret_sts_error;
1691 RAISE FND_API.G_EXC_ERROR;
1692 END IF;
1693 END IF;
1694 END IF;
1695
1696 IF G_DEBUG THEN
1697 ozf_utility_pvt.debug_message( 'Update User Action ' || p_approval_rec.action_code );
1698 END IF;
1699
1700 -- Update_User_Action - Update the user action in approval table
1701 Update_User_Action(
1702 p_api_version => p_api_version
1703 ,p_init_msg_list => FND_API.G_FALSE
1704 ,p_validation_level => p_validation_level
1705 ,x_return_status => l_return_status
1706 ,x_msg_data => x_msg_data
1707 ,x_msg_count => x_msg_count
1708 ,p_approval_rec => p_approval_rec);
1709
1710 IF l_return_status = FND_API.g_ret_sts_error THEN
1711 RAISE FND_API.g_exc_error;
1712 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1713 RAISE FND_API.g_exc_unexpected_error;
1714 END IF;
1715
1716 IF p_approval_rec.action_code = 'REJECT' OR p_approval_rec.action_code = 'RETURN' THEN
1717 l_final_approval_flag := 'Y';
1718 END IF;
1719
1720 END IF;
1721
1722 -- If the request is submitted/approved - get next approvers
1723 IF p_approval_rec.action_code = 'SUBMIT' OR
1724 p_approval_rec.action_code = 'APPROVE'
1725 THEN
1726
1727 IF G_DEBUG THEN
1728 ozf_utility_pvt.debug_message( 'Get Approvers ' || p_approval_rec.action_code );
1729 END IF;
1730
1731 -- Get_Approvers - List of Approvers or Default Approver
1732 Get_Approvers(
1733 p_api_version => p_api_version
1734 ,p_init_msg_list => FND_API.G_FALSE
1735 ,p_validation_level => p_validation_level
1736 ,x_return_status => l_return_status
1737 ,x_msg_data => x_msg_data
1738 ,x_msg_count => x_msg_count
1739 ,p_approval_rec => p_approval_rec
1740 ,x_approvers => l_approvers_tbl
1741 ,x_final_approval_flag => l_final_approval_flag);
1742
1743 IF l_return_status = FND_API.g_ret_sts_error THEN
1744 RAISE FND_API.g_exc_error;
1745 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1746 RAISE FND_API.g_exc_unexpected_error;
1747 END IF;
1748 END IF;
1749
1750 -- Add access to users who have approval privileges
1751 IF p_approval_rec.action_code = 'SUBMIT' OR
1752 p_approval_rec.action_code = 'APPROVE' OR
1753 p_approval_rec.action_code = 'FORWARD'
1754 THEN
1755 IF G_DEBUG THEN
1756 ozf_utility_pvt.debug_message( 'Add Access ' || p_approval_rec.action_code );
1757 END IF;
1758
1759 IF l_final_approval_flag <> 'Y' THEN
1760 --If no Approver Found Do not add record in Access table
1761 if l_approvers_tbl(1).approver_id is not null then
1762 -- Add_Access - List Approvers sent from Get_Approvers api
1763 Add_Access(
1764 p_api_version => p_api_version
1765 ,p_init_msg_list => FND_API.G_FALSE
1766 ,p_commit => FND_API.G_FALSE
1767 ,p_validation_level => p_validation_level
1768 ,x_return_status => l_return_status
1769 ,x_msg_data => x_msg_data
1770 ,x_msg_count => x_msg_count
1771 ,p_approval_rec => p_approval_rec
1772 ,p_approvers => l_approvers_tbl );
1773
1774 IF l_return_status = FND_API.g_ret_sts_error THEN
1775 RAISE FND_API.g_exc_error;
1776 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1777 RAISE FND_API.g_exc_unexpected_error;
1778 END IF;
1779 END IF; --End if some Approver is found
1780
1781 END IF;
1782
1783 IF G_DEBUG THEN
1784 ozf_utility_pvt.debug_message( 'Access Added ' || x_return_status );
1785 END IF;
1786 END IF;
1787
1788 IF p_approval_rec.object_type = 'SPECIAL_PRICE' THEN
1789 l_event_name := 'oracle.apps.ozf.request.SpecialPrice.approval';
1790 ELSIF p_approval_rec.object_type = 'SOFT_FUND' THEN
1791 l_event_name := 'oracle.apps.ozf.request.SoftFund.approval';
1792 END IF;
1793
1794 l_event_key := p_approval_rec.object_type || ':' || p_approval_rec.object_id || ':' || to_char(sysdate, 'DD:MON:YYYY HH:MI:SS');
1795
1796 IF p_approval_rec.object_type = 'SPECIAL_PRICE' THEN
1797 IF l_final_approval_flag <> 'Y' or p_approval_rec.action_code <> 'APPROVE' THEN
1798 -- Raise_Event -> Event_Subscription -> Send_Notification
1799 Raise_Event (
1800 x_return_status => l_return_status
1801 ,x_msg_data => x_msg_data
1802 ,x_msg_count => x_msg_count
1803 ,p_event_name => l_event_name
1804 ,p_event_key => l_event_key
1805 ,p_approval_rec => p_approval_rec );
1806
1807 END IF;
1808 ELSE
1809 Raise_Event (
1810 x_return_status => l_return_status
1811 ,x_msg_data => x_msg_data
1812 ,x_msg_count => x_msg_count
1813 ,p_event_name => l_event_name
1814 ,p_event_key => l_event_key
1815 ,p_approval_rec => p_approval_rec );
1816
1817 END IF;
1818
1819 /*
1820 l_msg_callback_api := 'OZF_APPROVAL_PVT.REQUEST_SET_MSG_ATTRS';
1821 l_user_callback_api := 'OZF_APPROVAL_PVT.REQUEST_RETURN_USERLIST';
1822
1823 OPEN csr_request (p_approval_rec.object_id);
1824 FETCH csr_request INTO l_benefit_id, l_partner_id;
1825 CLOSE csr_request;
1826
1827 IF G_DEBUG THEN
1828 ozf_utility_pvt.debug_message( 'Send Notification liu feng' || l_benefit_id ||'Parner ' || l_partner_id );
1829 END IF;
1830
1831 -- Call PRM api to send notification based on setups
1832 PV_BENFT_STATUS_CHANGE.status_change_raise(
1833 p_api_version_number => p_api_version,
1834 p_init_msg_list => FND_API.g_false,
1835 p_commit => FND_API.g_false,
1836 p_validation_level => p_validation_level,
1837 p_event_name => l_event_name,
1838 p_benefit_id => l_benefit_id,
1839 p_entity_id => p_approval_rec.object_id,
1840 p_status_code => p_approval_rec.status_code,
1841 p_partner_id => l_partner_id,
1842 p_msg_callback_api => l_msg_callback_api,
1843 p_user_callback_api => l_user_callback_api,
1844 x_return_status => x_return_status,
1845 x_msg_count => x_msg_count,
1846 x_msg_data => x_msg_data);
1847
1848
1849 IF G_DEBUG THEN
1850 ozf_utility_pvt.debug_message( 'Raise Notification Event ' || x_return_status );
1851 END IF;
1852
1853 IF l_return_status = FND_API.g_ret_sts_error THEN
1854 RAISE FND_API.g_exc_error;
1855 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1856 RAISE FND_API.g_exc_unexpected_error;
1857 END IF;
1858
1859 */
1860 --Standard check of commit
1861 IF FND_API.To_Boolean ( p_commit ) THEN
1862 COMMIT WORK;
1863 END IF;
1864
1865 x_final_approval_flag := l_final_approval_flag;
1866 -- Debug Message
1867 IF G_DEBUG THEN
1868 ozf_utility_pvt.debug_message( l_api_name||': End');
1869 END IF;
1870 --Standard call to get message count and if count=1, get the message
1871 FND_MSG_PUB.Count_And_Get (
1872 p_encoded => FND_API.G_FALSE,
1873 p_count => x_msg_count,
1874 p_data => x_msg_data
1875 );
1876
1877 EXCEPTION
1878 WHEN FND_API.G_EXC_ERROR THEN
1879 ROLLBACK TO Process_User_Action_PVT;
1880 x_return_status := FND_API.G_RET_STS_ERROR;
1881 -- Standard call to get message count and if count=1, get the message
1882 FND_MSG_PUB.Count_And_Get (
1883 p_encoded => FND_API.G_FALSE,
1884 p_count => x_msg_count,
1885 p_data => x_msg_data
1886 );
1887 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1888 ROLLBACK TO Process_User_Action_PVT;
1889 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1890 -- Standard call to get message count and if count=1, get the message
1891 FND_MSG_PUB.Count_And_Get (
1892 p_encoded => FND_API.G_FALSE,
1893 p_count => x_msg_count,
1894 p_data => x_msg_data
1895 );
1896 WHEN OTHERS THEN
1897 ROLLBACK TO Process_User_Action_PVT;
1898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1899 IF OZF_UNEXP_ERROR_ON
1900 THEN
1901 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1902 END IF;
1903 -- Standard call to get message count and if count=1, get the message
1904 FND_MSG_PUB.Count_And_Get (
1905 p_encoded => FND_API.G_FALSE,
1906 p_count => x_msg_count,
1907 p_data => x_msg_data
1908 );
1909 --
1910 END Process_User_Action;
1911 ---------------------------------------------------------------------
1912 /*
1913
1914 set serveroutput on
1915
1916 declare
1917 l_return_status varchar2(100);
1918 l_msg_count number;
1919 l_msg_data varchar2(100);
1920 l_null number;
1921 l_approval_rec OZF_APPROVAL_PVT.approval_rec_type;
1922 l_approvers_tbl OZF_APPROVAL_PVT.approvers_tbl_type;
1923 l_final_approval varchar2(1);
1924 l_approver_id number;
1925
1926 begin
1927 fnd_msg_pub.initialize;
1928
1929 --l_approval_rec.object_type := 'SPECIAL_PRICE';
1930 l_approval_rec.object_type := 'SOFT_FUND';
1931 l_approval_rec.object_id := 159;
1932
1933 l_approval_rec.status_code := 'PENDING';
1934 l_approval_rec.action_code := 'SUBMIT';
1935 l_approval_rec.action_performed_by := 1000196;
1936
1937 -- l_approval_rec.status_code := 'PENDING';
1938 -- l_approval_rec.action_code := 'APPROVE';
1939 -- l_approval_rec.action_performed_by := 1001773;
1940
1941 -- l_approval_rec.status_code := 'PENDING';
1942 -- l_approval_rec.action_code := 'REJECT';
1943 -- l_approval_rec.action_performed_by := 1001637;
1944 l_approver_id := 1000196;
1945
1946 OZF_APPROVAL_PVT.Process_User_Action (
1947 p_api_version => 1.0,
1948 p_init_msg_list => FND_API.G_FALSE,
1949 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1950 x_return_status => l_return_status,
1951 x_msg_count => l_msg_count,
1952 x_msg_data => l_msg_data,
1953 p_approval_rec => l_approval_rec,
1954 p_approver_id => l_approver_id,
1955 x_final_approval_flag => l_final_approval);
1956
1957 ozf_utility_pvt.debug_message('liufeng: ' || l_return_status);
1958 ozf_utility_pvt.debug_message(l_msg_count);
1959 ozf_utility_pvt.debug_message('approval: ' ||l_final_approval);
1960
1961 for i in 1..l_msg_count loop
1962 ozf_utility_pvt.debug_message(substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F'), 1, 254));
1963 end loop;
1964
1965 -- FOR i in 1..l_approvers_tbl.count LOOP
1966 -- ozf_utility_pvt.debug_message('Approver Id ' || l_approvers_tbl(i).APPROVER_ID);
1967 -- ozf_utility_pvt.debug_message('Approver Level ' || l_approvers_tbl(i).APPROVER_LEVEL);
1968 -- END LOOP;
1969
1970 ozf_utility_pvt.debug_message('Final Approval ' || l_final_approval);
1971
1972 end;
1973
1974
1975 */
1976
1977 -- 'R12.1 Enhancement: Ship & Debit Request' by ateotia(+)
1978 ---------------------------------------------------------------------
1979 -- PROCEDURE
1980 -- Process_SD_Approval
1981 --
1982 -- PURPOSE
1983 -- This procedure has been created for Ship & Debit Request.
1984 -- This procedure Handles the approval of Ship & Debit Objects.
1985 --
1986 -- PARAMETERS
1987 --
1988 -- NOTES
1989 ---------------------------------------------------------------------
1990 PROCEDURE Process_SD_Approval (
1991 p_api_version IN NUMBER
1992 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1993 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1994 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1995 ,p_object_id IN NUMBER
1996 ,p_action_code IN VARCHAR2
1997 ,x_return_status OUT NOCOPY VARCHAR2
1998 ,x_msg_count OUT NOCOPY NUMBER
1999 ,x_msg_data OUT NOCOPY VARCHAR2
2000 )
2001 IS
2002 l_api_name CONSTANT VARCHAR2(80) := 'Process_SD_Approval';
2003 l_api_version CONSTANT NUMBER := 1.0;
2004 l_event_key VARCHAR2(240);
2005 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2006 l_approvers_tbl ame_util.approversTable2;
2007 l_approval_completeYN VARCHAR2(20) := ame_util.booleanFalse;
2008 l_orig_system_id NUMBER;
2009 l_orig_system VARCHAR2(30);
2010 l_user_id NUMBER;
2011 l_resource_id NUMBER;
2012 l_person_id NUMBER;
2013 l_owner_flag VARCHAR2(1);
2014 l_approver_flag VARCHAR2(1) := 'Y';
2015 l_insert_mode VARCHAR2(1); --:= 'Y';
2016 l_approver_count NUMBER := 0;
2017
2018 CURSOR csr_person_info (p_person_id IN NUMBER )IS
2019 SELECT user_id, resource_id
2020 FROM jtf_rs_resource_extns
2021 WHERE category = 'EMPLOYEE'
2022 AND source_id = p_person_id
2023 AND user_id IS NOT NULL
2024 AND sysdate >= start_date_active
2025 AND sysdate <= nvl(end_date_active, sysdate)
2026 AND rownum < 2;
2027
2028 CURSOR csr_user_info (p_user_id IN NUMBER )IS
2029 SELECT resource_id
2030 FROM jtf_rs_resource_extns
2031 WHERE category = 'EMPLOYEE'
2032 AND user_id = p_user_id
2033 AND sysdate >= start_date_active
2034 AND sysdate <= nvl(end_date_active, sysdate)
2035 AND rownum < 2;
2036
2037 CURSOR csr_get_approver_count(p_object_id IN NUMBER )IS
2038 SELECT count(*)
2039 FROM OZF_SD_REQUEST_ACCESS
2040 WHERE request_header_id = p_object_id
2041 AND approver_flag = 'Y'
2042 AND enabled_flag = 'Y';
2043
2044 BEGIN
2045 -- Standard begin of API savepoint
2046 SAVEPOINT Process_SD_Approval;
2047 -- Standard call to check for call compatibility.
2048 IF NOT FND_API.Compatible_API_Call (
2049 l_api_version,
2050 p_api_version,
2051 l_api_name,
2052 G_PKG_NAME)
2053 THEN
2054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2055 END IF;
2056 -- Debug Message
2057 IF G_DEBUG THEN
2058 ozf_utility_pvt.debug_message( l_api_name||': Start');
2059 END IF;
2060 --Initialize message list if p_init_msg_list is TRUE.
2061 IF FND_API.To_Boolean (p_init_msg_list) THEN
2062 FND_MSG_PUB.initialize;
2063 END IF;
2064 -- Initialize API return status to sucess
2065 x_return_status := FND_API.G_RET_STS_SUCCESS;
2066 -- If action code is SUBMIT get the approver list from AME setup
2067 -- and populate OZF_SD_REQUEST_ACCESS table
2068 IF (p_action_code = 'SUBMIT') THEN
2069 IF G_DEBUG THEN
2070 ozf_utility_pvt.debug_message( 'Get Approvers for request_header_id: ' || p_object_id );
2071 END IF;
2072 Get_All_Approvers(
2073 p_api_version => p_api_version
2074 ,p_init_msg_list => p_init_msg_list
2075 ,p_validation_level => p_validation_level
2076 ,p_transaction_id => p_object_id
2077 ,p_transaction_type_key => 'SUPPLIER_SHIP_DEBIT'
2078 ,x_return_status => l_return_status
2079 ,x_msg_data => x_msg_data
2080 ,x_msg_count => x_msg_count
2081 ,x_approvers => l_approvers_tbl
2082 ,x_approval_flag => l_approval_completeYN
2083 );
2084 IF l_approvers_tbl.count <> 0 THEN
2085 FOR i IN 1 .. l_approvers_tbl.count LOOP
2086 l_orig_system_id := l_approvers_tbl(i).orig_system_id;
2087 l_orig_system := l_approvers_tbl(i).orig_system;
2088 -- IF approver has been defined as FND USER in AME Setup
2089 -- TDD Assumption: Only a valid resource shall be considered as an approver
2090 IF (l_orig_system = 'FND_USR') THEN
2091 l_user_id := l_orig_system_id;
2092 OPEN csr_user_info(l_user_id);
2093 FETCH csr_user_info INTO l_resource_id;
2094 CLOSE csr_user_info;
2095 IF l_resource_id IS NOT NULL THEN
2096 l_insert_mode := 'Y';
2097 ELSE
2098 l_insert_mode := 'N';
2099 END IF;
2100 -- ELSIF approver has been defined as HR PERSON in AME Setup
2101 -- TDD Assumption: Only a valid resource shall be considered as an approver
2102 ELSIF (l_orig_system = 'PER') THEN
2103 l_person_id := l_orig_system_id;
2104 OPEN csr_person_info(l_person_id);
2105 FETCH csr_person_info INTO l_user_id, l_resource_id;
2106 CLOSE csr_person_info;
2107 IF l_resource_id IS NOT NULL THEN
2108 l_insert_mode := 'Y';
2109 ELSE
2110 l_insert_mode := 'N';
2111 END IF;
2112 END IF;
2113 -- This procedure will do required validation and
2114 -- insert the record in OZF_SD_REQUEST_ACCESS table
2115 IF (l_insert_mode = 'Y') THEN
2116 IF G_DEBUG THEN
2117 ozf_utility_pvt.debug_message( 'Invoke Add_SD_Access for AME approver: ' || l_orig_system_id);
2118 END IF;
2119 Add_SD_Access(
2120 p_api_version => p_api_version
2121 ,p_init_msg_list => p_init_msg_list
2122 ,p_commit => p_commit
2123 ,p_validation_level => p_validation_level
2124 ,p_request_header_id => p_object_id
2125 ,p_user_id => l_user_id
2126 ,p_resource_id => l_resource_id
2127 ,p_person_id => l_person_id
2128 ,p_owner_flag => l_owner_flag
2129 ,p_approver_flag => l_approver_flag
2130 ,x_return_status => l_return_status
2131 ,x_msg_data => x_msg_data
2132 ,x_msg_count => x_msg_count);
2133 IF l_return_status = FND_API.g_ret_sts_error THEN
2134 RAISE FND_API.g_exc_error;
2135 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2136 RAISE FND_API.g_exc_unexpected_error;
2137 END IF;
2138 END IF;
2139 -- Reset the variable value to 'Y'
2140 l_insert_mode := 'N';
2141 -- Reset the variables to null
2142 l_user_id := NULL;
2143 l_resource_id := NULL;
2144 l_person_id := NULL;
2145 END LOOP;
2146 END IF;
2147
2148 -- TDD Assumption: If there is no approver found in AME setup
2149 -- i.e. either there is no approver defined in AME setup
2150 -- or there is no valid approver defined in AME setup,
2151 -- the user defined in the profile OZF_SD_DEFAULT_APPROVER
2152 -- shall be considered as default approver.
2153 OPEN csr_get_approver_count(p_object_id);
2154 FETCH csr_get_approver_count INTO l_approver_count;
2155 CLOSE csr_get_approver_count;
2156 IF (l_approver_count = 0) THEN
2157 l_user_id := to_number(fnd_profile.value('OZF_SD_DEFAULT_APPROVER'));
2158 OPEN csr_user_info(l_user_id);
2159 FETCH csr_user_info INTO l_resource_id;
2160 CLOSE csr_user_info;
2161 IF l_resource_id IS NOT NULL THEN
2162 l_insert_mode := 'Y';
2163 ELSE
2164 l_insert_mode := 'N';
2165 END IF;
2166
2167 IF (l_insert_mode = 'Y') THEN
2168 IF G_DEBUG THEN
2169 ozf_utility_pvt.debug_message( 'Invoke Add_SD_Access for default approver: ' || l_user_id);
2170 END IF;
2171 Add_SD_Access(
2172 p_api_version => p_api_version
2173 ,p_init_msg_list => p_init_msg_list
2174 ,p_commit => p_commit
2175 ,p_validation_level => p_validation_level
2176 ,p_request_header_id => p_object_id
2177 ,p_user_id => l_user_id
2178 ,p_resource_id => l_resource_id
2179 ,p_person_id => l_person_id
2180 ,p_owner_flag => l_owner_flag
2181 ,p_approver_flag => l_approver_flag
2182 ,x_return_status => l_return_status
2183 ,x_msg_data => x_msg_data
2184 ,x_msg_count => x_msg_count);
2185 IF l_return_status = FND_API.g_ret_sts_error THEN
2186 RAISE FND_API.g_exc_error;
2187 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2188 RAISE FND_API.g_exc_unexpected_error;
2189 END IF;
2190 END IF;
2191 IF G_DEBUG THEN
2192 ozf_utility_pvt.debug_message('Add_SD_Access is ended for default approver: '||x_return_status);
2193 END IF;
2194 END IF;
2195 END IF;
2196 l_event_key := p_object_id || ':' || to_char(sysdate, 'DD:MON:YYYY HH:MI:SS');
2197 -- Raise_SD_Event -> Event_Subscription -> Send_Notification
2198 Raise_SD_Event (
2199 p_event_key => l_event_key
2200 ,p_object_id => p_object_id
2201 ,p_action_code => p_action_code
2202 ,x_return_status => l_return_status
2203 ,x_msg_data => x_msg_data
2204 ,x_msg_count => x_msg_count);
2205
2206 --Standard check of commit
2207 IF FND_API.To_Boolean ( p_commit ) THEN
2208 COMMIT WORK;
2209 END IF;
2210 -- Debug Message
2211 IF G_DEBUG THEN
2212 ozf_utility_pvt.debug_message( l_api_name||': End');
2213 END IF;
2214 --Standard call to get message count and if count=1, get the message
2215 FND_MSG_PUB.Count_And_Get (
2216 p_encoded => FND_API.G_FALSE,
2217 p_count => x_msg_count,
2218 p_data => x_msg_data
2219 );
2220
2221 EXCEPTION
2222 WHEN FND_API.G_EXC_ERROR THEN
2223 ROLLBACK TO Process_SD_Approval;
2224 x_return_status := FND_API.G_RET_STS_ERROR;
2225 -- Standard call to get message count and if count=1, get the message
2226 FND_MSG_PUB.Count_And_Get (
2227 p_encoded => FND_API.G_FALSE,
2228 p_count => x_msg_count,
2229 p_data => x_msg_data
2230 );
2231 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2232 ROLLBACK TO Process_SD_Approval;
2233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2234 -- Standard call to get message count and if count=1, get the message
2235 FND_MSG_PUB.Count_And_Get (
2236 p_encoded => FND_API.G_FALSE,
2237 p_count => x_msg_count,
2238 p_data => x_msg_data
2239 );
2240 WHEN OTHERS THEN
2241 ROLLBACK TO Process_SD_Approval;
2242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2243 IF OZF_UNEXP_ERROR_ON
2244 THEN
2245 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2246 END IF;
2247 -- Standard call to get message count and if count=1, get the message
2248 FND_MSG_PUB.Count_And_Get (
2249 p_encoded => FND_API.G_FALSE,
2250 p_count => x_msg_count,
2251 p_data => x_msg_data
2252 );
2253 --
2254 END Process_SD_Approval;
2255
2256 ---------------------------------------------------------------------
2257 -- PROCEDURE
2258 -- Get_All_Approvers
2259 --
2260 -- PURPOSE
2261 -- This procedure has been created for Ship & Debit Request.
2262 -- This procedure calls ame_api2.getAllApprovers7 to get
2263 -- Approver list from AME Setup.
2264 --
2265 -- PARAMETERS
2266 --
2267 -- NOTES
2268 ---------------------------------------------------------------------
2269 PROCEDURE Get_All_Approvers(
2270 p_api_version IN NUMBER
2271 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2272 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2273 ,p_transaction_id IN VARCHAR2
2274 ,p_transaction_type_key IN VARCHAR2
2275 ,x_approvers OUT NOCOPY ame_util.approversTable2
2276 ,x_approval_flag OUT NOCOPY VARCHAR2
2277 ,x_return_status OUT NOCOPY VARCHAR2
2278 ,x_msg_data OUT NOCOPY VARCHAR2
2279 ,x_msg_count OUT NOCOPY NUMBER
2280 )
2281 IS
2282
2283 l_api_name CONSTANT varchar2(80) := 'Get_All_Approvers';
2284 l_api_version CONSTANT number := 1.0;
2285 l_application_id number := 682;
2286
2287 BEGIN
2288
2289 -- Standard begin of API savepoint
2290 SAVEPOINT Get_All_Approvers;
2291 -- Standard call to check for call compatibility.
2292 IF NOT FND_API.Compatible_API_Call (
2293 l_api_version,
2294 p_api_version,
2295 l_api_name,
2296 G_PKG_NAME)
2297 THEN
2298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2299 END IF;
2300 -- Debug Message
2301 IF G_DEBUG THEN
2302 ozf_utility_pvt.debug_message( l_api_name||': Start');
2303 END IF;
2304 --Initialize message list if p_init_msg_list is TRUE.
2305 IF FND_API.To_Boolean (p_init_msg_list) THEN
2306 FND_MSG_PUB.initialize;
2307 END IF;
2308 -- Initialize API return status to sucess
2309 x_return_status := FND_API.G_RET_STS_SUCCESS;
2310 ame_api2.getAllApprovers7
2311 (applicationIdIn => l_application_id
2312 ,transactionTypeIn => p_transaction_type_key
2313 ,transactionIdIn => p_transaction_id
2314 ,approvalProcessCompleteYNOut => x_approval_flag
2315 ,approversOut => x_approvers);
2316
2317 -- Debug Message
2318 --Standard call to get message count and if count=1, get the message
2319 FND_MSG_PUB.Count_And_Get (
2320 p_encoded => FND_API.G_FALSE,
2321 p_count => x_msg_count,
2322 p_data => x_msg_data
2323 );
2324 EXCEPTION
2325 WHEN FND_API.G_EXC_ERROR THEN
2326 ROLLBACK TO Get_All_Approvers;
2327 x_return_status := FND_API.G_RET_STS_ERROR;
2328 -- Standard call to get message count and if count=1, get the message
2329 FND_MSG_PUB.Count_And_Get (
2330 p_encoded => FND_API.G_FALSE,
2331 p_count => x_msg_count,
2332 p_data => x_msg_data
2333 );
2334 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2335 ROLLBACK TO Get_All_Approvers;
2336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2337 -- Standard call to get message count and if count=1, get the message
2338 FND_MSG_PUB.Count_And_Get (
2339 p_encoded => FND_API.G_FALSE,
2340 p_count => x_msg_count,
2341 p_data => x_msg_data
2342 );
2343 WHEN OTHERS THEN
2344 ROLLBACK TO Get_All_Approvers;
2345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2346 IF OZF_UNEXP_ERROR_ON
2347 THEN
2348 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2349 END IF;
2350 -- Standard call to get message count and if count=1, get the message
2351 FND_MSG_PUB.Count_And_Get (
2352 p_encoded => FND_API.G_FALSE,
2353 p_count => x_msg_count,
2354 p_data => x_msg_data
2355 );
2356 --
2357 END Get_All_Approvers;
2358
2359 ---------------------------------------------------------------------
2360 -- PROCEDURE
2361 -- Add_SD_Access
2362 --
2363 -- PURPOSE
2364 -- This procedure has been created for Ship & Debit Request.
2365 -- This procedure performs the required validation and invokes the
2366 -- overloaded procedure which finally adds the record into
2367 -- OZF_SD_REQUEST_ACCESS table.
2368 --
2369 -- PARAMETERS
2370 --
2371 -- NOTES
2372 ---------------------------------------------------------------------
2373 PROCEDURE Add_SD_Access(
2374 p_api_version IN NUMBER
2375 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2376 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2377 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2378 ,p_request_header_id IN NUMBER
2379 ,p_user_id IN NUMBER
2380 ,p_resource_id IN NUMBER
2381 ,p_person_id IN NUMBER DEFAULT NULL
2382 ,p_owner_flag IN VARCHAR2
2383 ,p_approver_flag IN VARCHAR2
2384 ,p_enabled_flag IN VARCHAR2 DEFAULT 'Y'
2385 ,x_return_status OUT NOCOPY VARCHAR2
2386 ,x_msg_count OUT NOCOPY NUMBER
2387 ,x_msg_data OUT NOCOPY VARCHAR2)
2388 IS
2389
2390 l_api_name CONSTANT varchar2(80) := 'Add_SD_Access';
2391 l_api_version CONSTANT number := 1.0;
2392 l_access_rec sd_access_rec_type;
2393 l_user_id NUMBER;
2394 l_resource_id NUMBER;
2395
2396 CURSOR csr_get_user_id (p_resource_id IN NUMBER )IS
2397 select user_id
2398 from jtf_rs_resource_extns
2399 where resource_id = p_resource_id
2400 and sysdate >= start_date_active
2401 and sysdate <= nvl(end_date_active, sysdate)
2402 and rownum < 2;
2403
2404 CURSOR csr_get_resource_id (p_user_id IN NUMBER )IS
2405 select resource_id
2406 from jtf_rs_resource_extns
2407 where user_id = p_user_id
2408 and sysdate >= start_date_active
2409 and sysdate <= nvl(end_date_active, sysdate)
2410 and rownum < 2;
2411
2412 BEGIN
2413 -- Standard begin of API savepoint
2414 SAVEPOINT Add_SD_Access1;
2415 -- Standard call to check for call compatibility.
2416 IF NOT FND_API.Compatible_API_Call (
2417 l_api_version,
2418 p_api_version,
2419 l_api_name,
2420 G_PKG_NAME)
2421 THEN
2422 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2423 END IF;
2424 -- Debug Message
2425 IF G_DEBUG THEN
2426 ozf_utility_pvt.debug_message( l_api_name||': Start');
2427 END IF;
2428 --Initialize message list if p_init_msg_list is TRUE.
2429 IF FND_API.To_Boolean (p_init_msg_list) THEN
2430 FND_MSG_PUB.initialize;
2431 END IF;
2432 -- Initialize API return status to sucess
2433 x_return_status := FND_API.G_RET_STS_SUCCESS;
2434
2435 -- do required validation
2436 -- If request_header_id is null
2437 -- If both user_id and resource_id are null
2438 -- If both owner_flag and approver_flag are null
2439 -- Set the appropriate return status and raise an exception
2440 IF (p_request_header_id IS NULL) THEN
2441 IF OZF_ERROR_ON THEN
2442 ozf_utility_pvt.error_message('OZF_SD_REQUEST_HEADER_ID_NULL');
2443 x_return_status := FND_API.g_ret_sts_error;
2444 RAISE FND_API.G_EXC_ERROR;
2445 END IF;
2446 END IF;
2447
2448 IF (p_user_id IS NULL AND p_resource_id IS NULL) THEN
2449 IF OZF_ERROR_ON THEN
2450 ozf_utility_pvt.error_message('OZF_SD_USER_RESOURCE_ID_NULL');
2451 x_return_status := FND_API.g_ret_sts_error;
2452 RAISE FND_API.G_EXC_ERROR;
2453 END IF;
2454 END IF;
2455
2456 IF (p_owner_flag IS NULL AND p_approver_flag IS NULL) THEN
2457 IF OZF_ERROR_ON THEN
2458 ozf_utility_pvt.error_message('OZF_SD_OWNER_APPROVER_NULL');
2459 x_return_status := FND_API.g_ret_sts_error;
2460 RAISE FND_API.G_EXC_ERROR;
2461 END IF;
2462 END IF;
2463
2464 -- Get user_id or resource_id for Admin's action i.e. change of owner/assignee
2465 IF (p_user_id IS NULL OR p_resource_id IS NULL) THEN
2466 IF (p_user_id IS NULL AND p_resource_id IS NOT NULL) THEN
2467 OPEN csr_get_user_id (p_resource_id);
2468 FETCH csr_get_user_id INTO l_user_id;
2469 CLOSE csr_get_user_id;
2470 ELSIF (p_resource_id IS NULL AND p_user_id IS NOT NULL) THEN
2471 OPEN csr_get_resource_id (p_user_id);
2472 FETCH csr_get_resource_id INTO l_resource_id;
2473 CLOSE csr_get_resource_id;
2474 END IF;
2475 END IF;
2476
2477 l_access_rec.REQUEST_HEADER_ID := p_request_header_id;
2478
2479 IF (p_user_id IS NOT NULL) THEN
2480 l_access_rec.USER_ID := p_user_id;
2481 ELSE
2482 l_access_rec.USER_ID := l_user_id;
2483 END IF;
2484
2485 IF (p_resource_id IS NOT NULL) THEN
2486 l_access_rec.RESOURCE_ID := p_resource_id;
2487 ELSE
2488 l_access_rec.RESOURCE_ID := l_resource_id;
2489 END IF;
2490
2491 IF (l_access_rec.USER_ID IS NULL OR l_access_rec.RESOURCE_ID IS NULL) THEN
2492 IF OZF_ERROR_ON THEN
2493 ozf_utility_pvt.error_message('OZF_SD_USER_IS_NOT_RESOURCE');
2494 x_return_status := FND_API.g_ret_sts_error;
2495 RAISE FND_API.G_EXC_ERROR;
2496 END IF;
2497 END IF;
2498
2499
2500 l_access_rec.PERSON_ID := p_person_id;
2501 l_access_rec.OWNER_FLAG := p_owner_flag;
2502 l_access_rec.APPROVER_FLAG := p_approver_flag;
2503 l_access_rec.ENABLED_FLAG := p_enabled_flag;
2504
2505 IF G_DEBUG THEN
2506 ozf_utility_pvt.debug_message( 'REQUEST HEADER ID: '||l_access_rec.REQUEST_HEADER_ID);
2507 ozf_utility_pvt.debug_message( 'USER ID: '||l_access_rec.USER_ID);
2508 ozf_utility_pvt.debug_message( 'RESOURCE ID: '||l_access_rec.RESOURCE_ID);
2509 ozf_utility_pvt.debug_message( 'PERSON ID: '||l_access_rec.PERSON_ID);
2510 ozf_utility_pvt.debug_message( 'OWNER FLAG: '||l_access_rec.OWNER_FLAG);
2511 ozf_utility_pvt.debug_message( 'APPROVER FLAG: '||l_access_rec.APPROVER_FLAG);
2512 ozf_utility_pvt.debug_message( 'ENABLED FLAG: '||l_access_rec.ENABLED_FLAG);
2513 END IF;
2514
2515 Add_SD_Access(
2516 p_api_version => p_api_version
2517 ,p_init_msg_list => p_init_msg_list
2518 ,p_commit => p_commit
2519 ,p_validation_level => p_validation_level
2520 ,p_access_rec => l_access_rec
2521 ,x_return_status => x_return_status
2522 ,x_msg_data => x_msg_data
2523 ,x_msg_count => x_msg_count);
2524 IF x_return_status = FND_API.g_ret_sts_error THEN
2525 RAISE FND_API.g_exc_error;
2526 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2527 RAISE FND_API.g_exc_unexpected_error;
2528 END IF;
2529 -- Debug Message
2530 IF G_DEBUG THEN
2531 ozf_utility_pvt.debug_message( l_api_name||': End');
2532 END IF;
2533 --Standard call to get message count and if count=1, get the message
2534 FND_MSG_PUB.Count_And_Get (
2535 p_encoded => FND_API.G_FALSE,
2536 p_count => x_msg_count,
2537 p_data => x_msg_data
2538 );
2539 EXCEPTION
2540 WHEN FND_API.G_EXC_ERROR THEN
2541 ROLLBACK TO Add_SD_Access1;
2542 x_return_status := FND_API.G_RET_STS_ERROR;
2543 -- Standard call to get message count and if count=1, get the message
2544 FND_MSG_PUB.Count_And_Get (
2545 p_encoded => FND_API.G_FALSE,
2546 p_count => x_msg_count,
2547 p_data => x_msg_data
2548 );
2549 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2550 ROLLBACK TO Add_SD_Access1;
2551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2552 -- Standard call to get message count and if count=1, get the message
2553 FND_MSG_PUB.Count_And_Get (
2554 p_encoded => FND_API.G_FALSE,
2555 p_count => x_msg_count,
2556 p_data => x_msg_data
2557 );
2558 WHEN OTHERS THEN
2559 ROLLBACK TO Add_SD_Access1;
2560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2561 IF OZF_UNEXP_ERROR_ON
2562 THEN
2563 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2564 END IF;
2565 -- Standard call to get message count and if count=1, get the message
2566 FND_MSG_PUB.Count_And_Get (
2567 p_encoded => FND_API.G_FALSE,
2568 p_count => x_msg_count,
2569 p_data => x_msg_data
2570 );
2571 --
2572 END Add_SD_Access;
2573
2574
2575 ---------------------------------------------------------------------
2576 -- PROCEDURE
2577 -- Add_SD_Access
2578 --
2579 -- PURPOSE
2580 -- This procedure has been created for Ship & Debit Request.
2581 -- This procedure performs the required business logic and adds
2582 -- the record into OZF_SD_REQUEST_ACCESS table.
2583 --
2584 -- PARAMETERS
2585 --
2586 -- NOTES
2587 ---------------------------------------------------------------------
2588 PROCEDURE Add_SD_Access(
2589 p_api_version IN NUMBER
2590 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2591 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2592 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2593 ,p_access_rec IN sd_access_rec_type
2594 ,x_return_status OUT NOCOPY VARCHAR2
2595 ,x_msg_count OUT NOCOPY NUMBER
2596 ,x_msg_data OUT NOCOPY VARCHAR2
2597
2598 )
2599 IS
2600 l_api_name CONSTANT VARCHAR2(20) := 'Add_SD_Access';
2601 l_api_version CONSTANT NUMBER := 1.0;
2602 l_approval_access_id NUMBER;
2603 l_workflow_itemkey VARCHAR2(80);
2604 l_exist_resource_id NUMBER;
2605 l_exist_user_id NUMBER;
2606 l_exist_approver_flag VARCHAR2(1);
2607 l_exist_owner_flag VARCHAR2(1);
2608 l_exist_version_number NUMBER;
2609 l_insert_mode VARCHAR2(1) := 'N';
2610
2611 CURSOR c_id IS
2612 SELECT OZF_SD_REQUEST_ACCESS_S.NEXTVAL
2613 FROM dual;
2614
2615 CURSOR CSR_EXISTING_OWNER (p_object_id IN NUMBER) IS
2616 SELECT resource_id, user_id, approver_flag, object_version_number
2617 FROM OZF_SD_REQUEST_ACCESS
2618 WHERE request_header_id = p_object_id
2619 AND enabled_flag = 'Y'
2620 AND owner_flag = 'Y';
2621
2622 CURSOR CSR_EXISTING_OWNER_APPROVER(p_object_id IN NUMBER, p_resource_id IN NUMBER) IS
2623 SELECT resource_id, owner_flag, approver_flag, object_version_number
2624 FROM OZF_SD_REQUEST_ACCESS
2625 WHERE request_header_id = p_object_id
2626 AND enabled_flag = 'Y'
2627 AND resource_id = p_resource_id;
2628
2629 BEGIN
2630 -- Standard begin of API savepoint
2631 SAVEPOINT Add_SD_Access2;
2632 -- Standard call to check for call compatibility.
2633 IF NOT FND_API.Compatible_API_Call (
2634 l_api_version,
2635 p_api_version,
2636 l_api_name,
2637 G_PKG_NAME)
2638 THEN
2639 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2640 END IF;
2641 -- Debug Message
2642 IF G_DEBUG THEN
2643 ozf_utility_pvt.debug_message( l_api_name||': Start');
2644 END IF;
2645 --Initialize message list if p_init_msg_list is TRUE.
2646 IF FND_API.To_Boolean (p_init_msg_list) THEN
2647 FND_MSG_PUB.initialize;
2648 END IF;
2649
2650 -- Initialize API return status to sucess
2651 x_return_status := FND_API.G_RET_STS_SUCCESS;
2652
2653 -- check for approver record
2654 IF (p_access_rec.APPROVER_FLAG = 'Y') THEN
2655 OPEN CSR_EXISTING_OWNER_APPROVER (p_access_rec.REQUEST_HEADER_ID, p_access_rec.RESOURCE_ID);
2656 FETCH CSR_EXISTING_OWNER_APPROVER INTO
2657 l_exist_resource_id,
2658 l_exist_owner_flag,
2659 l_exist_approver_flag,
2660 l_exist_version_number;
2661 CLOSE CSR_EXISTING_OWNER_APPROVER;
2662 IF l_exist_resource_id IS NOT NULL THEN
2663 IF (l_exist_owner_flag = 'Y' AND l_exist_approver_flag IS NULL)THEN
2664 UPDATE OZF_SD_REQUEST_ACCESS
2665 SET approver_flag = 'Y',
2666 object_version_number = l_exist_version_number + 1
2667 WHERE request_header_id = p_access_rec.REQUEST_HEADER_ID
2668 AND resource_id = p_access_rec.RESOURCE_ID
2669 AND enabled_flag = 'Y';
2670 END IF;
2671 ELSE
2672 l_insert_mode :='Y';
2673 END IF;
2674
2675 --CHECK FOR OWNER RECORD
2676 ELSIF (p_access_rec.OWNER_FLAG = 'Y') THEN
2677 OPEN CSR_EXISTING_OWNER (p_access_rec.REQUEST_HEADER_ID);
2678 FETCH CSR_EXISTING_OWNER INTO
2679 l_exist_resource_id,
2680 l_exist_user_id,
2681 l_exist_approver_flag,
2682 l_exist_version_number;
2683 CLOSE CSR_EXISTING_OWNER;
2684 -- check if, there is any owner exists for this request_header_id
2685 -- if yes, update the access flags for existing owner
2686 -- if p_access_rec.RESOURCE_ID and p_access_rec.USER_ID are same as the existing record,
2687 -- dont update anything
2688 IF (l_exist_resource_id IS NOT NULL) THEN
2689 IF (l_exist_resource_id <> p_access_rec.RESOURCE_ID AND l_exist_user_id <> p_access_rec.USER_ID) THEN
2690 IF l_exist_approver_flag IS NULL THEN
2691 UPDATE OZF_SD_REQUEST_ACCESS
2692 SET enabled_flag = NULL,
2693 object_version_number = l_exist_version_number + 1
2694 WHERE request_header_id = p_access_rec.REQUEST_HEADER_ID
2695 AND resource_id = l_exist_resource_id
2696 AND enabled_flag = 'Y';
2697 ELSE
2698 UPDATE OZF_SD_REQUEST_ACCESS
2699 SET owner_flag = NULL,
2700 object_version_number = l_exist_version_number + 1
2701 WHERE request_header_id = p_access_rec.REQUEST_HEADER_ID
2702 AND resource_id = l_exist_resource_id
2703 AND enabled_flag = 'Y';
2704 END IF;
2705 -- now enter the record for new owner
2706 -- before that check if there is any approver who has same resource_id
2707 -- if yes, update the owner_flag for that approver as 'Y'
2708 -- else enter a new record for new owner
2709 -- CHECK FOR EXISTING APPROVER WHO HAS THE SAME RESOURCE ID
2710 -- Reset the variables to null
2711 l_exist_resource_id := null;
2712 l_exist_owner_flag := null;
2713 l_exist_approver_flag := null;
2714 l_exist_version_number := null;
2715 OPEN CSR_EXISTING_OWNER_APPROVER (p_access_rec.REQUEST_HEADER_ID, p_access_rec.RESOURCE_ID);
2716 FETCH CSR_EXISTING_OWNER_APPROVER INTO
2717 l_exist_resource_id,
2718 l_exist_owner_flag,
2719 l_exist_approver_flag,
2720 l_exist_version_number;
2721 CLOSE CSR_EXISTING_OWNER_APPROVER;
2722 IF (l_exist_resource_id IS NOT NULL AND l_exist_approver_flag = 'Y') THEN
2723 UPDATE OZF_SD_REQUEST_ACCESS
2724 SET owner_flag = 'Y',
2725 object_version_number = l_exist_version_number + 1
2726 WHERE request_header_id = p_access_rec.REQUEST_HEADER_ID
2727 AND resource_id = p_access_rec.RESOURCE_ID
2728 AND enabled_flag = 'Y';
2729 ELSE
2730 l_insert_mode :='Y';
2731 END IF;
2732 END IF;
2733 ELSE
2734 l_insert_mode :='Y';
2735 END IF;
2736 END IF;
2737
2738 IF (l_insert_mode = 'Y') THEN
2739 BEGIN
2740 IF G_DEBUG THEN
2741 ozf_utility_pvt.debug_message( 'Inserting data into OZF_SD_REQUEST_ACCESS table');
2742 END IF;
2743 -- GET PRIMARY KEY
2744 OPEN c_id;
2745 FETCH c_id INTO l_approval_access_id;
2746 CLOSE c_id;
2747 INSERT INTO OZF_SD_REQUEST_ACCESS(
2748 request_access_id
2749 ,request_header_id
2750 ,user_id
2751 ,resource_id
2752 ,person_id
2753 ,owner_flag
2754 ,approver_flag
2755 ,enabled_flag
2756 ,object_version_number
2757 ,last_update_date
2758 ,last_updated_by
2759 ,creation_date
2760 ,created_by
2761 ,last_update_login)
2762 VALUES(
2763 l_approval_access_id
2764 ,p_access_rec.REQUEST_HEADER_ID
2765 ,p_access_rec.USER_ID
2766 ,p_access_rec.RESOURCE_ID
2767 ,p_access_rec.PERSON_ID
2768 ,p_access_rec.OWNER_FLAG
2769 ,p_access_rec.APPROVER_FLAG
2770 ,p_access_rec.ENABLED_FLAG
2771 ,1
2772 ,SYSDATE
2773 ,G_USER_ID
2774 ,SYSDATE
2775 ,G_USER_ID
2776 ,G_LOGIN_ID
2777 );
2778 EXCEPTION
2779 WHEN OTHERS THEN
2780 IF OZF_ERROR_ON THEN
2781 ozf_utility_pvt.error_message('OZF_SD_REQ_ACCESS_INSERT_ERR');
2782 x_return_status := FND_API.g_ret_sts_error;
2783 RAISE FND_API.G_EXC_ERROR;
2784 END IF;
2785 END;
2786 END IF;
2787 --Standard check of commit
2788 IF FND_API.To_Boolean ( p_commit ) THEN
2789 COMMIT WORK;
2790 END IF;
2791 -- Debug Message
2792 IF G_DEBUG THEN
2793 ozf_utility_pvt.debug_message( l_api_name||': End');
2794 END IF;
2795 --Standard call to get message count and if count=1, get the message
2796 FND_MSG_PUB.Count_And_Get (
2797 p_encoded => FND_API.G_FALSE,
2798 p_count => x_msg_count,
2799 p_data => x_msg_data
2800 );
2801 EXCEPTION
2802 WHEN FND_API.G_EXC_ERROR THEN
2803 ROLLBACK TO Add_SD_Access2;
2804 x_return_status := FND_API.G_RET_STS_ERROR;
2805 -- Standard call to get message count and if count=1, get the message
2806 FND_MSG_PUB.Count_And_Get (
2807 p_encoded => FND_API.G_FALSE,
2808 p_count => x_msg_count,
2809 p_data => x_msg_data
2810 );
2811 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2812 ROLLBACK TO Add_SD_Access2;
2813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2814 -- Standard call to get message count and if count=1, get the message
2815 FND_MSG_PUB.Count_And_Get (
2816 p_encoded => FND_API.G_FALSE,
2817 p_count => x_msg_count,
2818 p_data => x_msg_data
2819 );
2820 WHEN OTHERS THEN
2821 ROLLBACK TO Add_SD_Access2;
2822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2823 IF OZF_UNEXP_ERROR_ON
2824 THEN
2825 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2826 END IF;
2827 -- Standard call to get message count and if count=1, get the message
2828 FND_MSG_PUB.Count_And_Get (
2829 p_encoded => FND_API.G_FALSE,
2830 p_count => x_msg_count,
2831 p_data => x_msg_data
2832 );
2833 --
2834 END Add_SD_Access;
2835
2836 ---------------------------------------------------------------------
2837 -- PROCEDURE
2838 -- Raise_SD_Event
2839 --
2840 -- PURPOSE
2841 -- This procedure has been created for Ship & Debit Request.
2842 -- This procedure raises a business event to send different
2843 -- notifications for Ship & Debit request.
2844 --
2845 -- PARAMETERS
2846 --
2847 -- NOTES
2848 ---------------------------------------------------------------------
2849 PROCEDURE Raise_SD_Event (
2850 p_event_key IN VARCHAR2
2851 ,p_object_id IN NUMBER
2852 ,p_action_code IN VARCHAR2
2853 ,x_return_status OUT NOCOPY VARCHAR2
2854 ,x_msg_data OUT NOCOPY VARCHAR2
2855 ,x_msg_count OUT NOCOPY NUMBER
2856 )
2857 IS
2858 l_api_name CONSTANT varchar2(80) := 'Raise_SD_Event';
2859 l_api_version CONSTANT number := 1.0;
2860 l_item_key VARCHAR2(240);
2861 l_event VARCHAR2(240);
2862 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
2863 l_parameter_t wf_parameter_t := wf_parameter_t(null, null);
2864 l_sd_event_name VARCHAR2(240) := 'oracle.apps.ozf.request.ShipDebit.approval';
2865
2866 BEGIN
2867 SAVEPOINT Raise_SD_Event;
2868 -- Debug Message
2869 IF G_DEBUG THEN
2870 ozf_utility_pvt.debug_message( l_api_name||': Start');
2871 END IF;
2872 -- Initialize API return status to sucess
2873 x_return_status := FND_API.G_RET_STS_SUCCESS;
2874
2875 l_event := Check_Event(l_sd_event_name);
2876 IF l_event = 'NOTFOUND' THEN
2877 IF OZF_ERROR_ON THEN
2878 ozf_utility_pvt.error_message('OZF_WF_EVENT_NAME_NULL', 'NAME', l_sd_event_name);
2879 END IF;
2880 x_return_status := FND_API.g_ret_sts_error;
2881 RAISE FND_API.G_EXC_ERROR;
2882 END IF;
2883
2884 l_parameter_t.setName('OBJECT_ID');
2885 l_parameter_t.setValue(p_object_id);
2886 l_parameter_list.extend;
2887 l_parameter_list(1) := l_parameter_t;
2888
2889 l_parameter_t.setName('ACTION_CODE');
2890 l_parameter_t.setValue(p_action_code);
2891 l_parameter_list.extend;
2892 l_parameter_list(2) := l_parameter_t;
2893
2894 -- Raise business event
2895 Wf_Event.Raise
2896 ( p_event_name => l_sd_event_name,
2897 p_event_key => p_event_key,
2898 p_parameters => l_parameter_list,
2899 p_event_data => NULL);
2900
2901 -- Debug Message
2902 IF G_DEBUG THEN
2903 ozf_utility_pvt.debug_message( l_api_name||': End');
2904 END IF;
2905 --Standard call to get message count and if count=1, get the message
2906 FND_MSG_PUB.Count_And_Get (
2907 p_encoded => FND_API.G_FALSE,
2908 p_count => x_msg_count,
2909 p_data => x_msg_data
2910 );
2911
2912 EXCEPTION
2913 WHEN FND_API.G_EXC_ERROR THEN
2914 ROLLBACK TO Raise_SD_Event;
2915 x_return_status := FND_API.G_RET_STS_ERROR;
2916 -- Standard call to get message count and if count=1, get the message
2917 FND_MSG_PUB.Count_And_Get (
2918 p_encoded => FND_API.G_FALSE,
2919 p_count => x_msg_count,
2920 p_data => x_msg_data
2921 );
2922 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2923 ROLLBACK TO Raise_SD_Event;
2924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2925 -- Standard call to get message count and if count=1, get the message
2926 FND_MSG_PUB.Count_And_Get (
2927 p_encoded => FND_API.G_FALSE,
2928 p_count => x_msg_count,
2929 p_data => x_msg_data
2930 );
2931 WHEN OTHERS THEN
2932 ROLLBACK TO Raise_SD_Event;
2933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2934 IF OZF_UNEXP_ERROR_ON
2935 THEN
2936 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2937 END IF;
2938 -- Standard call to get message count and if count=1, get the message
2939 FND_MSG_PUB.Count_And_Get (
2940 p_encoded => FND_API.G_FALSE,
2941 p_count => x_msg_count,
2942 p_data => x_msg_data
2943 );
2944 --
2945 END Raise_SD_Event;
2946
2947 ---------------------------------------------------------------------
2948 -- PROCEDURE
2949 -- Send_SD_Notification
2950 --
2951 -- PURPOSE
2952 -- This procedure has been created for Ship & Debit Request.
2953 -- This procedure sends the notifications based on p_action_code.
2954 --
2955 -- PARAMETERS
2956 --
2957 -- NOTES
2958 ---------------------------------------------------------------------
2959 PROCEDURE Send_SD_Notification (
2960 p_api_version IN NUMBER
2961 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2962 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2963 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2964 ,p_object_id IN NUMBER
2965 ,p_action_code IN VARCHAR2
2966 ,x_return_status OUT NOCOPY VARCHAR2
2967 ,x_msg_data OUT NOCOPY VARCHAR2
2968 ,x_msg_count OUT NOCOPY NUMBER
2969 )
2970 IS
2971 l_api_name CONSTANT VARCHAR2(80) := 'Send_SD_Notification';
2972 l_api_version CONSTANT NUMBER := 1.0;
2973
2974 CURSOR csr_approvers (p_object_id IN NUMBER) IS
2975 SELECT fu.user_name
2976 FROM ozf_sd_request_access ora,
2977 fnd_user fu
2978 WHERE ora.request_header_id = p_object_id
2979 AND ora.person_id IS NULL
2980 AND ora.user_id = fu.user_id
2981 AND ora.approver_flag = 'Y'
2982 AND ora.enabled_flag = 'Y'
2983 UNION
2984 SELECT jre.user_name
2985 FROM ozf_sd_request_access ora,
2986 jtf_rs_resource_extns jre
2987 WHERE ora.request_header_id = p_object_id
2988 AND ora.person_id IS NOT NULL
2989 AND ora.person_id = jre.source_id
2990 AND ora.approver_flag = 'Y'
2991 AND ora.enabled_flag = 'Y'
2992 GROUP BY jre.user_name;
2993
2994 CURSOR csr_access_members(p_object_id IN NUMBER) IS
2995 SELECT fu.user_name
2996 FROM ozf_sd_request_access ora,
2997 fnd_user fu
2998 WHERE ora.request_header_id = p_object_id
2999 AND ora.person_id IS NULL
3000 AND ora.user_id = fu.user_id
3001 AND ora.enabled_flag = 'Y'
3002 UNION
3003 SELECT jre.user_name
3004 FROM ozf_sd_request_access ora,
3005 jtf_rs_resource_extns jre
3006 WHERE ora.request_header_id = p_object_id
3007 AND ora.person_id IS NOT NULL
3008 AND ora.person_id = jre.source_id
3009 AND ora.enabled_flag = 'Y'
3010 GROUP BY jre.user_name;
3011
3012 CURSOR csr_function_id (p_func_name IN VARCHAR2) IS
3013 SELECT function_id FROM fnd_form_functions
3014 WHERE function_name = p_func_name ;
3015
3016 CURSOR csr_request_info(p_object_id IN NUMBER) IS
3017 SELECT orh.request_number,
3018 requester.source_name,
3019 aps.vendor_name,
3020 hou.name,
3021 orh.creation_date,
3022 orh.request_start_date,
3023 orh.request_end_date,
3024 orh.supplier_response_date,
3025 orh.supplier_response_by_date,
3026 orh.authorization_number
3027 FROM ozf_sd_request_headers_all_b orh
3028 , ap_suppliers aps
3029 , jtf_rs_resource_extns requester
3030 , hr_all_organization_units hou
3031 WHERE
3032 orh.request_header_id = p_object_id
3033 AND orh.requestor_id = requester.resource_id (+)
3034 AND orh.SUPPLIER_ID = aps. vendor_id (+)
3035 AND orh.org_id = hou.organization_id (+);
3036
3037 CURSOR csr_assignee_info(p_object_id IN NUMBER) IS
3038 SELECT assignee.source_name,
3039 orh.asignee_response_date,
3040 orh.asignee_response_by_date
3041 FROM ozf_sd_request_headers_all_b orh
3042 , jtf_rs_resource_extns assignee
3043 WHERE
3044 orh.request_header_id = p_object_id
3045 AND orh.asignee_resource_id = assignee.resource_id;
3046
3047 CURSOR request_type_setup_info(p_object_id IN NUMBER) IS
3048 SELECT ACSV.setup_name
3049 FROM ams_custom_setups_vl ACSV,
3050 OZF_SD_REQUEST_HEADERS_ALL_B OSRH
3051 WHERE OSRH.request_header_id = p_object_id
3052 AND ACSV.custom_setup_id= OSRH.request_type_setup_id;
3053
3054 CURSOR lc_get_function_id (pc_func_name IN VARCHAR2) IS
3055 SELECT function_id
3056 FROM fnd_form_functions
3057 WHERE function_name = pc_func_name;
3058
3059 l_adhoc_role VARCHAR2(200);
3060 l_role_list VARCHAR2(3000):='';
3061 l_user_type VARCHAR2(30);
3062 l_item_type VARCHAR2(30) := 'OZFSDBEN';
3063 l_item_name VARCHAR2(30);
3064 l_item_key VARCHAR2(200);
3065 l_group_notify_id NUMBER;
3066 l_context VARCHAR2(1000);
3067 l_user_role VARCHAR2(240);
3068 l_execute_str VARCHAR2(3000);
3069 l_request_number VARCHAR2(30);
3070 l_requester_name VARCHAR2(100);
3071 l_supplier_name VARCHAR2(100);
3072 l_assignee_name VARCHAR2(100);
3073 l_operating_unit VARCHAR2(100);
3074 l_creation_date DATE;
3075 l_start_date DATE;
3076 l_end_date DATE;
3077 l_supplier_resp_date DATE;
3078 l_supplier_resp_by_date DATE;
3079 l_assignee_resp_date DATE;
3080 l_assignee_resp_by_date DATE;
3081 l_authorization_number VARCHAR2(30);
3082 l_request_type_setup_name VARCHAR2(100);
3083 l_function_id NUMBER;
3084 l_login_url VARCHAR2(1000);
3085
3086 BEGIN
3087 -- Standard begin of API savepoint
3088 SAVEPOINT Send_SD_Notification;
3089 -- Standard call to check for call compatibility.
3090 IF NOT FND_API.Compatible_API_Call (
3091 l_api_version,
3092 p_api_version,
3093 l_api_name,
3094 G_PKG_NAME)
3095 THEN
3096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3097 END IF;
3098 -- Debug Message
3099 IF G_DEBUG THEN
3100 ozf_utility_pvt.debug_message( l_api_name||': Start');
3101 END IF;
3102 --Initialize message list if p_init_msg_list is TRUE.
3103 IF FND_API.To_Boolean (p_init_msg_list) THEN
3104 FND_MSG_PUB.initialize;
3105 END IF;
3106 -- Initialize API return status to sucess
3107 x_return_status := FND_API.G_RET_STS_SUCCESS;
3108
3109 IF G_DEBUG THEN
3110 ozf_utility_pvt.debug_message( 'Construct the message for request_header_id: '||p_object_id);
3111 END IF;
3112 -- if action code is SUBMIT, send the notification only to approvers (PMs)
3113 IF (p_action_code = 'SUBMIT') THEN
3114 FOR l_row IN csr_approvers(p_object_id) LOOP
3115 l_role_list := l_role_list || ',' || l_row.user_name;
3116 END LOOP;
3117 l_item_name := 'SUBMITTED01';
3118 -- ELSIF action code is ACCEPT/REJECT, send the notification to all access members (PMs+Owner)
3119 -- Get the assignee information from csr_assignee_info
3120 ELSIF (p_action_code = 'ACCEPT' OR p_action_code = 'REJECT') THEN
3121 FOR l_row IN csr_access_members(p_object_id) LOOP
3122 l_role_list := l_role_list || ',' || l_row.user_name;
3123 END LOOP;
3124 IF (p_action_code = 'ACCEPT') THEN
3125 l_item_name := 'ACCEPTED01';
3126 ELSIF
3127 (p_action_code = 'REJECT') THEN
3128 l_item_name := 'REJECTED01';
3129 END IF;
3130 OPEN csr_assignee_info(p_object_id);
3131 FETCH csr_assignee_info INTO
3132 l_assignee_name,
3133 l_assignee_resp_date,
3134 l_assignee_resp_by_date;
3135 CLOSE csr_assignee_info;
3136 END IF;
3137
3138 l_role_list := substr(l_role_list,2);
3139 IF length(l_role_list) <> 0 THEN
3140 l_item_key := l_item_type||'|'||l_item_name||'|'||p_object_id||
3141 '|'||to_char(sysdate,'YYYYMMDDHH24MISS');
3142 END IF;
3143
3144 OPEN csr_request_info (p_object_id);
3145 FETCH csr_request_info INTO
3146 l_request_number,
3147 l_requester_name,
3148 l_supplier_name,
3149 l_operating_unit,
3150 l_creation_date,
3151 l_start_date,
3152 l_end_date,
3153 l_supplier_resp_date,
3154 l_supplier_resp_by_date,
3155 l_authorization_number;
3156 CLOSE csr_request_info;
3157
3158 OPEN request_type_setup_info (p_object_id);
3159 FETCH request_type_setup_info INTO l_request_type_setup_name;
3160 CLOSE request_type_setup_info;
3161
3162 IF G_DEBUG THEN
3163 ozf_utility_pvt.debug_message( 'request number: '||l_request_number);
3164 ozf_utility_pvt.debug_message( 'request type setup name: '||l_request_type_setup_name);
3165 ozf_utility_pvt.debug_message( 'requester name: '||l_requester_name);
3166 ozf_utility_pvt.debug_message( 'supplier name: '||l_supplier_name);
3167 ozf_utility_pvt.debug_message( 'assignee name: '||l_assignee_name);
3168 ozf_utility_pvt.debug_message( 'operating unit: '||l_operating_unit);
3169 ozf_utility_pvt.debug_message( 'request creation date: '||TO_CHAR(l_creation_date));
3170 ozf_utility_pvt.debug_message( 'request start date: '||TO_CHAR(l_start_date));
3171 ozf_utility_pvt.debug_message( 'request end date: '||TO_CHAR(l_end_date));
3172 ozf_utility_pvt.debug_message( 'supplier response date: '||TO_CHAR(l_supplier_resp_date));
3173 ozf_utility_pvt.debug_message( 'supplier_response by date: '||TO_CHAR(l_supplier_resp_by_date));
3174 ozf_utility_pvt.debug_message( 'assignee response date: '||TO_CHAR(l_assignee_resp_date));
3175 ozf_utility_pvt.debug_message( 'assignee response by date: '||TO_CHAR(l_assignee_resp_by_date));
3176 ozf_utility_pvt.debug_message( 'authorization number: '||l_authorization_number);
3177 END IF;
3178
3179 IF G_DEBUG THEN
3180 ozf_utility_pvt.debug_message('Create process for itemtype:' || l_item_type || ' itemkey: ' || l_item_key);
3181 END IF;
3182 -- Create WF process to send notification
3183 wf_engine.CreateProcess ( ItemType => l_item_type,
3184 ItemKey => l_item_key,
3185 process => 'NOOP_PROCESS',
3186 user_key => l_item_key);
3187 IF G_DEBUG THEN
3188 ozf_utility_pvt.debug_message('Set attributes for itemtype:' || l_item_type || ' itemkey: ' || l_item_key);
3189 END IF;
3190
3191 -- Set WF attributes to send notification
3192 wf_engine.SetItemAttrText(itemtype => l_item_type,
3193 itemkey => l_item_key,
3194 aname => 'REQUEST_NUMBER',
3195 avalue => l_request_number);
3196
3197 wf_engine.SetItemAttrText(itemtype => l_item_type,
3198 itemkey => l_item_key,
3199 aname => 'REQUEST_TYPE',
3200 avalue => l_request_type_setup_name);
3201
3202 wf_engine.SetItemAttrText(itemtype => l_item_type,
3203 itemkey => l_item_key,
3204 aname => 'REQUESTER_NAME',
3205 avalue => l_requester_name);
3206
3207 wf_engine.SetItemAttrText(itemtype => l_item_type,
3208 itemkey => l_item_key,
3209 aname => 'SUPPLIER_NAME',
3210 avalue => l_supplier_name);
3211
3212 wf_engine.SetItemAttrText(itemtype => l_item_type,
3213 itemkey => l_item_key,
3214 aname => 'ASSIGNEE_NAME',
3215 avalue => l_assignee_name);
3216
3217 wf_engine.SetItemAttrText(itemtype => l_item_type,
3218 itemkey => l_item_key,
3219 aname => 'OPERATING_UNIT',
3220 avalue => l_operating_unit);
3221
3222 wf_engine.SetItemAttrText(itemtype => l_item_type,
3223 itemkey => l_item_key,
3224 aname => 'REQUEST_CREATION_DATE',
3225 avalue => l_creation_date);
3226
3227 wf_engine.SetItemAttrText(itemtype => l_item_type,
3228 itemkey => l_item_key,
3229 aname => 'REQUEST_START_DATE',
3230 avalue => l_start_date);
3231
3232 wf_engine.SetItemAttrText(itemtype => l_item_type,
3233 itemkey => l_item_key,
3234 aname => 'REQUEST_END_DATE',
3235 avalue => l_end_date);
3236
3237 wf_engine.SetItemAttrText(itemtype => l_item_type,
3238 itemkey => l_item_key,
3239 aname => 'SUPPLIER_RESPONSE_DATE',
3240 avalue => l_supplier_resp_date);
3241
3242
3243 wf_engine.SetItemAttrText(itemtype => l_item_type,
3244 itemkey => l_item_key,
3245 aname => 'SUPPLIER_RESPONSE_BY_DATE',
3246 avalue => l_supplier_resp_by_date);
3247
3248 wf_engine.SetItemAttrText(itemtype => l_item_type,
3249 itemkey => l_item_key,
3250 aname => 'ASSIGNEE_RESPONSE_DATE',
3251 avalue => l_assignee_resp_date);
3252
3253 wf_engine.SetItemAttrText(itemtype => l_item_type,
3254 itemkey => l_item_key,
3255 aname => 'ASSIGNEE_RESPONSE_BY_DATE',
3256 avalue => l_assignee_resp_by_date);
3257
3258 wf_engine.SetItemAttrText(itemtype => l_item_type,
3259 itemkey => l_item_key,
3260 aname => 'AUTHORIZATION_NUMBER',
3261 avalue => l_authorization_number);
3262
3263 open lc_get_function_id(pc_func_name => 'OZF_SD_REQ_DETAILS');
3264 fetch lc_get_function_id into l_function_id;
3265 close lc_get_function_id;
3266
3267 l_login_url := fnd_run_function.get_run_function_url
3268 (l_function_id,
3269 -1,
3270 -1,
3271 0,
3272 'SDRequestHdrId='||p_object_id||'&'||'FromPage=Dtail');
3273
3274 wf_engine.SetItemAttrText( itemtype => l_item_type,
3275 itemkey => l_item_key,
3276 aname => 'LOGIN_URL',
3277 avalue => l_login_url );
3278 IF G_DEBUG THEN
3279 ozf_utility_pvt.debug_message('Adding adhoc users' || l_role_list );
3280 END IF;
3281
3282 -- create an adhoc role with named after itemkey
3283 l_adhoc_role := l_item_key;
3284
3285 wf_directory.CreateAdHocRole(role_name => l_adhoc_role,
3286 role_display_name => l_adhoc_role,
3287 role_users => l_role_list);
3288 l_context := l_item_type || ':' || l_item_key || ':';
3289 IF G_DEBUG THEN
3290 ozf_utility_pvt.debug_message('Sending Notification to adhoc role ' || l_item_type || ' ' || l_item_name);
3291 END IF;
3292 -- set the message to be sent
3293 l_group_notify_id := wf_notification.sendGroup(
3294 role => l_adhoc_role,
3295 msg_type => l_item_type,
3296 msg_name => l_item_name,
3297 due_date => null,
3298 callback => 'wf_engine.cb',
3299 context => l_context,
3300 send_comment => NULL,
3301 priority => NULL );
3302 -- start the notification process to send message
3303 wf_engine.StartProcess(itemtype => l_item_type,
3304 itemkey => l_item_key);
3305 IF G_DEBUG THEN
3306 ozf_utility_pvt.debug_message('Sent notification to role: ' || l_adhoc_role);
3307 ozf_utility_pvt.debug_message('Using message: ' || l_item_name || '. Notify id: ' || l_group_notify_id );
3308 END IF;
3309 --Standard check of commit
3310 IF FND_API.To_Boolean ( p_commit ) THEN
3311 COMMIT WORK;
3312 END IF;
3313
3314 -- Debug Message
3315 IF G_DEBUG THEN
3316 ozf_utility_pvt.debug_message( l_api_name||': End');
3317 END IF;
3318 --Standard call to get message count and if count=1, get the message
3319 FND_MSG_PUB.Count_And_Get (
3320 p_encoded => FND_API.G_FALSE,
3321 p_count => x_msg_count,
3322 p_data => x_msg_data
3323 );
3324 EXCEPTION
3325 WHEN FND_API.G_EXC_ERROR THEN
3326 ROLLBACK TO Send_SD_Notification;
3327 x_return_status := FND_API.G_RET_STS_ERROR;
3328 -- Standard call to get message count and if count=1, get the message
3329 FND_MSG_PUB.Count_And_Get (
3330 p_encoded => FND_API.G_FALSE,
3331 p_count => x_msg_count,
3332 p_data => x_msg_data
3333 );
3334 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3335 ROLLBACK TO Send_SD_Notification;
3336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3337 -- Standard call to get message count and if count=1, get the message
3338 FND_MSG_PUB.Count_And_Get (
3339 p_encoded => FND_API.G_FALSE,
3340 p_count => x_msg_count,
3341 p_data => x_msg_data
3342 );
3343 WHEN OTHERS THEN
3344 ROLLBACK TO Send_SD_Notification;
3345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3346 IF OZF_UNEXP_ERROR_ON
3347 THEN
3348 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3349 END IF;
3350 -- Standard call to get message count and if count=1, get the message
3351 FND_MSG_PUB.Count_And_Get (
3352 p_encoded => FND_API.G_FALSE,
3353 p_count => x_msg_count,
3354 p_data => x_msg_data
3355 );
3356 --
3357 END Send_SD_Notification;
3358
3359 -- 'R12.1 Enhancement: Ship & Debit Request' by ateotia(-)
3360
3361
3362 END OZF_APPROVAL_PVT;