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