[Home] [Help]
PACKAGE BODY: APPS.AS_ACCESS_PVT
Source
1 PACKAGE BODY AS_ACCESS_PVT as
2 /* $Header: asxvacsb.pls 120.6 2006/01/10 00:08:25 subabu ship $ */
3 --
4 -- NAME
5 -- AS_ACCESS_PVT
6 --
7 -- HISTORY
8 -- 7/17/98 AWU CREATED
9 -- 1/25/99 HUCHEN Added Convert_Miss_SalesTeam_Rec procedure and invocation
10 -- 06/27/00 AWU More modifications based on new business logic
11 -- 07/10/00 AWU Took out Convert_Miss_SalesTeam_Rec procedure in 11i
12 -- and made more business logic changes
13 -- 07/13/00 AWU added has_xxxAccess implementations
14 -- 09/13/00 ACNG add partner_exist_csr back so that user can pass in
15 -- partner information without partner_cont_party_id
16 -- when create sales team
17 -- Check partner_customer_id is not null when there is
18 -- no partner_cont_party_id or person_id pass in
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):='AS_ACCESS_PVT';
21 G_FILE_NAME CONSTANT VARCHAR2(12):='asxvacsb.pls';
22
23 FUNCTION salesTeam_flags_are_Valid(p_sales_team_rec IN OUT NOCOPY SALES_TEAM_REC_TYPE)
24 RETURN BOOLEAN IS
25 begin
26 if (p_sales_team_rec.freeze_flag is NOT NULL
27 and p_sales_team_rec.freeze_flag <> FND_API.G_MISS_CHAR)
28 then
29 if (AS_FOUNDATION_PVT.get_lookupMeaning('YES/NO',
30 p_sales_team_rec.freeze_flag, AS_FOUNDATION_PVT.G_AR_LOOKUPS )
31 is NULL)
32 then
33 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
34 THEN
35 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
36 FND_MESSAGE.Set_Token('COLUMN', 'FREEZE_FLAG', FALSE);
37 FND_MESSAGE.Set_Token('VALUE', p_sales_team_rec.freeze_flag, FALSE);
38 FND_MSG_PUB.ADD;
39 END IF;
40 return false;
41 end if;
42 end if;
43
44 if (p_sales_team_rec.reassign_flag is NOT NULL
45 and p_sales_team_rec.reassign_flag <> FND_API.G_MISS_CHAR)
46 then
47 if (AS_FOUNDATION_PVT.get_lookupMeaning('YES/NO',
48 p_sales_team_rec.reassign_flag, AS_FOUNDATION_PVT.G_AR_LOOKUPS )
49 is NULL)
50 then
51 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
52 THEN
53 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
54 FND_MESSAGE.Set_Token('COLUMN', 'REASSIGN_FLAG', FALSE);
55 FND_MESSAGE.Set_Token('VALUE', p_sales_team_rec.reassign_flag, FALSE);
56 FND_MSG_PUB.ADD;
57 END IF;
58 return false;
59 end if;
60 end if;
61
62 if (p_sales_team_rec.team_leader_flag is NOT NULL
63 and p_sales_team_rec.team_leader_flag <> FND_API.G_MISS_CHAR)
64 then
65 if (AS_FOUNDATION_PVT.get_lookupMeaning('YES/NO',
66 p_sales_team_rec.team_leader_flag, AS_FOUNDATION_PVT.G_AR_LOOKUPS )
67 is NULL)
68 then
69 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
70 THEN
71 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
72 FND_MESSAGE.Set_Token('COLUMN', 'TEAM_LEADER_FLAG', FALSE);
73 FND_MESSAGE.Set_Token('VALUE', p_sales_team_rec.team_leader_flag, FALSE);
74 FND_MSG_PUB.ADD;
75 END IF;
76 return false;
77 end if;
78 end if;
79
80 return true;
81 end;
82
83 procedure get_person_id(p_salesforce_id in varchar2,
84 x_person_id OUT NOCOPY varchar2)
85
86 is
87 cursor get_person_id_csr is
88 select employee_person_id
89 from as_salesforce_v
90 where salesforce_id = p_salesforce_id;
91
92 begin
93 open get_person_id_csr;
94 fetch get_person_id_csr into x_person_id;
95
96 if (get_person_id_csr%NOTFOUND)
97 then
98 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
99 THEN
100 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
101 FND_MESSAGE.Set_Token('COLUMN', 'SALESFORCE_ID', FALSE);
102 fnd_message.set_token('VALUE', p_salesforce_id, FALSE);
103 FND_MSG_PUB.ADD;
104 END IF;
105 raise FND_API.G_EXC_ERROR;
106 end if;
107 close get_person_id_csr;
108 end;
109
110 procedure get_accessProfileValues(px_access_profile_rec in OUT NOCOPY
111 AS_ACCESS_PUB.Access_Profile_Rec_Type)
112
113 is
114 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
115 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.get_accessProfileValues';
116 begin
117 IF px_access_profile_rec.opp_access_profile_value IS NULL OR
118 px_access_profile_rec.opp_access_profile_value = FND_API.G_MISS_CHAR
119 THEN
120 px_access_profile_rec.opp_access_profile_value
121 := FND_PROFILE.Value('AS_OPP_ACCESS');
122 END IF;
123
124 IF px_access_profile_rec.lead_access_profile_value IS NULL OR
125 px_access_profile_rec.lead_access_profile_value = FND_API.G_MISS_CHAR
126 THEN
127 px_access_profile_rec.lead_access_profile_value
128 := FND_PROFILE.Value('AS_LEAD_ACCESS');
129 END IF;
130
131 IF px_access_profile_rec.cust_access_profile_value IS NULL OR
132 px_access_profile_rec.cust_access_profile_value = FND_API.G_MISS_CHAR
133 THEN
134 px_access_profile_rec.cust_access_profile_value
135 := FND_PROFILE.Value('AS_CUST_ACCESS');
136 END IF;
137
138 IF px_access_profile_rec.mgr_update_profile_value IS NULL OR
139 px_access_profile_rec.mgr_update_profile_value = FND_API.G_MISS_CHAR
140 THEN
141 px_access_profile_rec.mgr_update_profile_value
142 := FND_PROFILE.Value('AS_MGR_UPDATE');
143 END IF;
144
145 IF px_access_profile_rec.admin_update_profile_value IS NULL OR
146 px_access_profile_rec.admin_update_profile_value = FND_API.G_MISS_CHAR
147 THEN
148 px_access_profile_rec.admin_update_profile_value
149 := FND_PROFILE.Value('AS_ADMIN_UPDATE');
150 END IF;
151 IF l_debug THEN
152 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
153 'AS_CUST_ACCESS: ' ||
154 px_access_profile_rec.cust_access_profile_value);
155 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
156 'AS_OPP_ACCESS: ' ||
157 px_access_profile_rec.opp_access_profile_value);
158 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
159 'AS_LEAD_ACCESS: ' ||
160 px_access_profile_rec.lead_access_profile_value);
161 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
162 'AS_MGR_UPDATE: ' ||
163 px_access_profile_rec.mgr_update_profile_value);
164 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
165 'AS_ADMIN_UPDATE: ' ||
166 px_access_profile_rec.mgr_update_profile_value);
167 END IF;
168
169 end get_accessProfileValues;
170
171
172 --- has_leadOwnerAccess is a private procedure currently.
173 procedure has_leadOwnerAccess
174 ( p_api_version_number IN NUMBER
175 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
176 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
177 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
178 ,p_admin_flag IN VARCHAR2
179 ,p_admin_group_id IN NUMBER
180 ,p_person_id IN NUMBER
181 ,p_sales_lead_id IN NUMBER
182 ,p_check_access_flag IN VARCHAR2
183 ,p_identity_salesforce_id IN NUMBER
184 ,p_partner_cont_party_id IN NUMBER
185 ,x_return_status OUT NOCOPY VARCHAR2
186 ,x_msg_count OUT NOCOPY NUMBER
187 ,x_msg_data OUT NOCOPY VARCHAR2
188 ,x_update_access_flag OUT NOCOPY VARCHAR2
189 ) is
190 l_api_name CONSTANT VARCHAR2(30) := 'has_deleteLeadAccess';
191 l_api_version_number CONSTANT NUMBER := 2.0;
192 l_tmp varchar2(1);
193 l_person_id number;
194 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
195
196 cursor resource_access_csr is
197 select 'X'
198 from as_accesses_all a
199 where a.sales_lead_id = p_sales_lead_id
200 and a.owner_flag = 'Y'
201 and a.salesforce_id = p_identity_salesforce_id;
202
203 cursor manager_access_csr(p_resource_id number) is
204
205 select 'X'
206 from as_accesses_all a
207 where a.sales_lead_id = p_sales_lead_id
208 and EXISTS (select 'x'
209 from as_rpt_managers_v rm
210 where a.salesforce_id = rm.resource_id
211 and rm.parent_resource_id = p_resource_id
212 and (rm.parent_resource_id = rm.resource_id
213 and a.owner_flag ='Y'
214 or (rm.parent_resource_id <> rm.resource_id)));
215
216 cursor mgr_i_access_csr(p_resource_id number) is
217 select 'X'
218 from as_accesses_all a
219 where a.sales_lead_id = p_sales_lead_id
220 and a.owner_flag = 'Y'
221 and (EXISTS (select 'x'
222 from as_rpt_managers_v rm
223 where a.salesforce_id = rm.resource_id
224 and rm.parent_resource_id = p_resource_id));
225
226 cursor admin_access_csr is
227 select 'x'
228 from as_accesses_all a
229 where a.sales_lead_id = p_sales_lead_id
230 and EXISTS (select 'x'
231 from as_rpt_admins_v rm
232 where a.salesforce_id = rm.salesforce_id
233 and rm.parent_sales_group_id = p_admin_group_id
234 and ((rm.salesforce_id = p_identity_salesforce_id
235 and a.owner_flag ='Y')
236 or (rm.salesforce_id <> p_identity_salesforce_id)));
237
238 cursor admin_i_access_csr is
239 select 'x'
240 from as_accesses_all a
241 where a.sales_lead_id = p_sales_lead_id
242 and a.owner_flag = 'Y'
243 and EXISTS (select 'x'
244 from as_rpt_admins_v rm
245 where a.salesforce_id = rm.salesforce_id
246 and rm.parent_sales_group_id = p_admin_group_id);
247
248 cursor am_mgr_access_csr(p_resource_id number) is
249 select 'x'
250 from as_sales_leads lead, as_accesses_all a, as_rpt_managers_v rm
251 where lead.customer_id = a.customer_id
252 and a.salesforce_id = rm.resource_id
253 and a.salesforce_role_code = 'AM'
254 and rm.parent_resource_id = p_resource_id
255 and lead.sales_lead_id = p_sales_lead_id;
256
257 cursor am_admin_access_csr is
258 select 'x'
259 from as_sales_leads lead, as_accesses_all a, as_rpt_admins_v rm
260 where lead.customer_id = a.customer_id
261 and a.salesforce_id = rm.salesforce_id
262 and a.salesforce_role_code = 'AM'
263 and rm.parent_sales_group_id = p_admin_group_id
264 and lead.sales_lead_id = p_sales_lead_id;
265
266 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
267 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_leadOwnerAccess';
268 begin
269 -- Standard call to check for call compatibility.
270 /* IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
271 p_api_version_number,
272 l_api_name,
273 G_PKG_NAME)
274 THEN
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END IF;
277
278 -- Initialize message list if p_init_msg_list is set to TRUE.
279 IF FND_API.to_Boolean( p_init_msg_list )
280 THEN
281 FND_MSG_PUB.initialize;
282 END IF;
283 */
284 -- Debug Message
285 IF l_debug THEN
286 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
287 'Private API: ' || l_api_name || 'start');
288
289 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
290 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
291 END IF;
292
293 -- Initialize API return status to SUCCESS
294 x_return_status := FND_API.G_RET_STS_SUCCESS;
295
296 IF l_debug THEN
297 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
298 'p_sales_lead_id: ' || p_sales_lead_id);
299 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
300 'ident salesforce_id: ' || p_identity_salesforce_id);
301 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
302 'p_admin_group_id: ' || p_admin_group_id);
303 END IF;
304
305 --
306 -- API body
307 --
308
309 -- Initialize access flag to 'N'
310 x_update_access_flag := 'N';
311
312 if p_check_access_flag = 'N'
313 then
314 x_update_access_flag := 'Y';
315 else -- if p_check_access_flag = 'Y'
316 -- PRM security
317 open resource_access_csr;
318 fetch resource_access_csr into l_tmp;
319 /* if p_partner_cont_party_id is not null
320 and p_partner_cont_party_id <> FND_API.G_MISS_NUM
321 then
322 if (resource_access_csr%FOUND)
323 then
324 x_update_access_flag := 'Y';
325 close resource_access_csr;
326 return;
327 end if;
328 end if; */
329 /* if p_person_id is null or p_person_id = fnd_api.g_miss_num
330 then
331 get_person_id(p_identity_salesforce_id, l_person_id);
332 else
333 l_person_id := p_person_id;
334 end if;
335 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
336 'person id: ' || l_person_id);
337 */
338 l_access_profile_rec := p_access_profile_rec;
339 get_accessProfileValues(l_access_profile_rec);
340
341 /* per nilesh, even lead access profile is full, owner privilege is not
342 granted. Need to base on sales team or hierarchy */
343 /* if l_access_profile_rec.lead_access_profile_value = 'F'
344 then
345 x_update_access_flag := 'Y';
346 elsif resource_access_csr%FOUND */
347
348 if resource_access_csr%FOUND
349 then
350 x_update_access_flag := 'Y';
351 else
352 if nvl(p_admin_flag,'N') <> 'Y'
353 then
354 if l_access_profile_rec.mgr_update_profile_value = 'U'
355 then
356 open manager_access_csr(p_identity_salesforce_id);
357 fetch manager_access_csr into l_tmp;
358 if manager_access_csr%FOUND
359 -- First check if mgr's subordinate
360 -- which are not 'AM'
361 then
362 x_update_access_flag := 'Y';
363 else -- if mgr's subordinate which are 'AM'
364 open am_mgr_access_csr(p_identity_salesforce_id);
365 fetch am_mgr_access_csr into l_tmp;
366 if am_mgr_access_csr%FOUND
367 then
368 x_update_access_flag := 'Y';
369 end if;
370 close am_mgr_access_csr;
371 end if; -- manager_access_csr%FOUND
372 close manager_access_csr;
373 elsif l_access_profile_rec.mgr_update_profile_value ='I'
374 then
375 open mgr_i_access_csr(p_identity_salesforce_id);
376 fetch mgr_i_access_csr into l_tmp;
377 if mgr_i_access_csr%FOUND
378 then
379 x_update_access_flag := 'Y';
380 end if;
381 close mgr_i_access_csr;
382 end if; -- l_access_profile_rec.mgr_update_profile_value = 'U'
383 elsif l_access_profile_rec.admin_update_profile_value = 'U'
384 then
385 open admin_access_csr;
386 fetch admin_access_csr into l_tmp;
387 if admin_access_csr%FOUND
388 then
389 x_update_access_flag := 'Y';
390 else
391 open am_admin_access_csr;
392 fetch am_admin_access_csr into l_tmp;
393 if am_admin_access_csr%FOUND
394 then
395 x_update_access_flag := 'Y';
396 end if;
397 close am_admin_access_csr;
398 end if; -- admin_access_csr%FOUND
399 close admin_access_csr;
400 elsif l_access_profile_rec.admin_update_profile_value = 'I'
401 then
402 open admin_i_access_csr;
403 fetch admin_i_access_csr into l_tmp;
404 if admin_i_access_csr%FOUND
405 then
406 x_update_access_flag := 'Y';
407 end if;
408 close admin_i_access_csr;
409 end if; -- if p_admin_flag <> 'Y'
410 end if;
411 close resource_access_csr;
412 end if; -- if p_check_access_flag = 'N'
413 --
414 -- End of API body.
415 --
416
417 IF l_debug THEN
418 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
419 'x_update_access_flag: ' || x_update_access_flag);
420
421 -- Debug Message
422 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
423 'Private API: ' || l_api_name || 'end');
424
425 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
426 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
427 END IF;
428
429
430 FND_MSG_PUB.Count_And_Get
431 ( p_count => x_msg_count,
432 p_data => x_msg_data
433 );
434
435 EXCEPTION
436 WHEN FND_API.G_EXC_ERROR THEN
437 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
438 P_MODULE => l_module
439 ,P_API_NAME => L_API_NAME
440 ,P_PKG_NAME => G_PKG_NAME
441 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
442 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
443 , P_ROLLBACK_FLAG => 'N'
444 ,X_MSG_COUNT => X_MSG_COUNT
445 ,X_MSG_DATA => X_MSG_DATA
446 ,X_RETURN_STATUS => X_RETURN_STATUS);
447
448 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
449 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
450 P_MODULE => l_module
451 ,P_API_NAME => L_API_NAME
452 ,P_PKG_NAME => G_PKG_NAME
453 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
454 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
455 , P_ROLLBACK_FLAG => 'N'
456 ,X_MSG_COUNT => X_MSG_COUNT
457 ,X_MSG_DATA => X_MSG_DATA
458 ,X_RETURN_STATUS => X_RETURN_STATUS);
459
460 WHEN OTHERS THEN
461 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
462 P_MODULE => l_module
463 ,P_API_NAME => L_API_NAME
464 ,P_PKG_NAME => G_PKG_NAME
465 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
466 ,P_SQLCODE => SQLCODE
467 ,P_SQLERRM => SQLERRM
468 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
469 , P_ROLLBACK_FLAG => 'N'
470 ,X_MSG_COUNT => X_MSG_COUNT
471 ,X_MSG_DATA => X_MSG_DATA
472 ,X_RETURN_STATUS => X_RETURN_STATUS);
473
474 end has_leadOwnerAccess;
475
476
477 --- has_oppOwnerAccess is a private procedure currently.
478 procedure has_oppOwnerAccess
479 ( p_api_version_number IN NUMBER
480 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
481 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
482 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
483 ,p_admin_flag IN VARCHAR2
484 ,p_admin_group_id IN NUMBER
485 ,p_person_id IN NUMBER
486 ,p_lead_id IN NUMBER
487 ,p_check_access_flag IN VARCHAR2
488 ,p_identity_salesforce_id IN NUMBER
489 ,p_partner_cont_party_id IN NUMBER
490 ,x_return_status OUT NOCOPY VARCHAR2
491 ,x_msg_count OUT NOCOPY NUMBER
492 ,x_msg_data OUT NOCOPY VARCHAR2
493 ,x_update_access_flag OUT NOCOPY VARCHAR2
494 ) is
495 l_api_name CONSTANT VARCHAR2(30) := 'has_oppOwnerAccess';
496 l_api_version_number CONSTANT NUMBER := 2.0;
497 l_tmp varchar2(1);
498 l_person_id number;
499 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
500
501 cursor resource_access_csr is
502 select 'X'
503 from as_accesses_all a
504 where a.lead_id = p_lead_id
505 and a.owner_flag = 'Y'
506 and a.salesforce_id = p_identity_salesforce_id;
507
508 cursor manager_access_csr(p_resource_id number) is
509 select 'X'
510 from as_accesses_all a
511 where a.lead_id = p_lead_id
512 and EXISTS (select 'x'
513 from as_rpt_managers_v rm
514 where a.salesforce_id = rm.resource_id
515 and rm.parent_resource_id = p_resource_id
516 and (rm.parent_resource_id = rm.resource_id
517 and a.owner_flag ='Y'
518 or (rm.parent_resource_id <> rm.resource_id)));
519
520 cursor am_mgr_access_csr(p_resource_id number) is
521 select 'x'
522 from as_leads_all lead, as_accesses_all a, as_rpt_managers_v rm
523 where lead.customer_id = a.customer_id
524 and a.salesforce_id = rm.resource_id
525 and a.salesforce_role_code = 'AM'
526 and rm.parent_resource_id = p_resource_id
527 and lead.lead_id = p_lead_id;
528
529 cursor mgr_i_access_csr(p_resource_id number) is
530 select 'X'
531 from as_accesses_all a
532 where a.lead_id = p_lead_id
533 and a.owner_flag = 'Y'
534 and (EXISTS (select 'x'
535 from as_rpt_managers_v rm
536 where a.salesforce_id = rm.resource_id
537 and rm.parent_resource_id = p_resource_id));
538
539 cursor admin_access_csr is
540 select 'x'
541 from as_accesses_all a
542 where a.lead_id = p_lead_id
543 and EXISTS (select 'x'
544 from as_rpt_admins_v rm
545 where a.salesforce_id = rm.salesforce_id
546 and rm.parent_sales_group_id = p_admin_group_id
547 and ((rm.salesforce_id = p_identity_salesforce_id
548 and a.owner_flag ='Y')
549 or (rm.salesforce_id <> p_identity_salesforce_id)));
550
551 cursor admin_i_access_csr is
552 select 'x'
553 from as_accesses_all a
554 where a.lead_id = p_lead_id
555 and a.owner_flag = 'Y'
556 and EXISTS (select 'x'
557 from as_rpt_admins_v rm
558 where a.salesforce_id = rm.salesforce_id
559 and rm.parent_sales_group_id = p_admin_group_id);
560
561 cursor am_admin_access_csr is
562 select 'x'
563 from as_leads_all lead, as_accesses_all a, as_rpt_admins_v rm
564 where lead.customer_id = a.customer_id
565 and a.salesforce_id = rm.salesforce_id
566 and a.salesforce_role_code = 'AM'
567 and rm.parent_sales_group_id = p_admin_group_id
568 and lead.lead_id = p_lead_id;
569
570 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
571 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_oppOwnerAccess';
572
573
574 begin
575 -- Standard call to check for call compatibility.
576
577 -- Debug Message
578 IF l_debug THEN
579 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
580 'Private API: ' || l_api_name || 'start');
581
582 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
583 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
584 END IF;
585
586 -- Initialize API return status to SUCCESS
587 x_return_status := FND_API.G_RET_STS_SUCCESS;
588
589 IF l_debug THEN
590 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
591 'p_lead_id: ' || p_lead_id);
592 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
593 'ident salesforce_id: ' || p_identity_salesforce_id);
594 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
595 'allow change opp owner profile: ' || fnd_profile.value('AS_ALLOW_CHANGE_OPP_OWNER') );
596 END IF;
597
598
599 --
600 -- API body
601 --
602
603 -- Initialize access flag to 'N'
604 x_update_access_flag := 'N';
605
606 IF p_check_access_flag = 'N'
607 THEN
608 x_update_access_flag := 'Y';
609 ELSE -- if p_check_access_flag = 'Y'
610 -- PRM security
611
612 if fnd_profile.value('AS_ALLOW_CHANGE_OPP_OWNER') = 'Y'
613 then
614 x_update_access_flag := 'Y';
615 end if;
616
617 l_access_profile_rec := p_access_profile_rec;
618 get_accessProfileValues(l_access_profile_rec);
619
620 open resource_access_csr;
621 fetch resource_access_csr into l_tmp;
622
623 if (resource_access_csr%FOUND)
624 then
625 x_update_access_flag := 'Y';
626 close resource_access_csr;
627 return;
628 else
629 if nvl(p_admin_flag,'N') <> 'Y'
630 then
631 if l_access_profile_rec.mgr_update_profile_value = 'U'
632 then
633 open manager_access_csr(p_identity_salesforce_id);
634 fetch manager_access_csr into l_tmp;
635 if manager_access_csr%FOUND
636 -- First check if mgr's subordinate
637 -- which are not 'AM'
638 then
639 x_update_access_flag := 'Y';
640
641 else -- if mgr's subordinate which are 'AM'
642 open am_mgr_access_csr(p_identity_salesforce_id);
643 fetch am_mgr_access_csr into l_tmp;
644 if am_mgr_access_csr%FOUND
645 then
646 x_update_access_flag := 'Y';
647 end if;
648 close am_mgr_access_csr;
649 close manager_access_csr;
650 end if; -- manager_access_csr%FOUND
651
652 elsif l_access_profile_rec.mgr_update_profile_value ='I'
653 then
654 open mgr_i_access_csr(p_identity_salesforce_id);
655 fetch mgr_i_access_csr into l_tmp;
656 if mgr_i_access_csr%FOUND
657 then
658 x_update_access_flag := 'Y';
659
660 end if;
661 close mgr_i_access_csr;
662 end if; -- l_access_profile_rec.mgr_update_profile_value = 'U'
663
664 elsif l_access_profile_rec.admin_update_profile_value = 'U'
665 then
666 open admin_access_csr;
667 fetch admin_access_csr into l_tmp;
668 if admin_access_csr%FOUND
669 then
670 x_update_access_flag := 'Y';
671 else
672 open am_admin_access_csr;
673 fetch am_admin_access_csr into l_tmp;
674 if am_admin_access_csr%FOUND
675 then
676 x_update_access_flag := 'Y';
677 end if;
678 close am_admin_access_csr;
679 end if; -- admin_access_csr%FOUND
680 close admin_access_csr;
681 elsif l_access_profile_rec.admin_update_profile_value = 'I'
682 then
683 open admin_i_access_csr;
684 fetch admin_i_access_csr into l_tmp;
685 if admin_i_access_csr%FOUND
686 then
687 x_update_access_flag := 'Y';
688 end if;
689 close admin_i_access_csr;
690 end if;--nvl(p_admin_flag,'N') <> 'Y'
691 close resource_access_csr;
692 end if;
693 END IF;
694
695 IF l_debug THEN
696 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
697 'x_update_access_flag: ' || x_update_access_flag);
698
699 -- Debug Message
700 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
701 'Private API: ' || l_api_name || 'end');
702
703 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
704 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
705 END IF;
706
707
708 FND_MSG_PUB.Count_And_Get
709 ( p_count => x_msg_count,
710 p_data => x_msg_data
711 );
712
713 EXCEPTION
714 WHEN FND_API.G_EXC_ERROR THEN
715 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
716 P_MODULE => l_module
717 ,P_API_NAME => L_API_NAME
718 ,P_PKG_NAME => G_PKG_NAME
719 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
720 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
721 , P_ROLLBACK_FLAG => 'N'
722 ,X_MSG_COUNT => X_MSG_COUNT
723 ,X_MSG_DATA => X_MSG_DATA
724 ,X_RETURN_STATUS => X_RETURN_STATUS);
725
726 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
727 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
728 P_MODULE => l_module
729 ,P_API_NAME => L_API_NAME
730 ,P_PKG_NAME => G_PKG_NAME
731 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
732 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
733 , P_ROLLBACK_FLAG => 'N'
734 ,X_MSG_COUNT => X_MSG_COUNT
735 ,X_MSG_DATA => X_MSG_DATA
736 ,X_RETURN_STATUS => X_RETURN_STATUS);
737
738 WHEN OTHERS THEN
739 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
740 P_MODULE => l_module
741 ,P_API_NAME => L_API_NAME
742 ,P_PKG_NAME => G_PKG_NAME
743 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
744 ,P_SQLCODE => SQLCODE
745 ,P_SQLERRM => SQLERRM
746 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
747 , P_ROLLBACK_FLAG => 'N'
748 ,X_MSG_COUNT => X_MSG_COUNT
749 ,X_MSG_DATA => X_MSG_DATA
750 ,X_RETURN_STATUS => X_RETURN_STATUS);
751
752 end has_oppOwnerAccess;
753
754
755
756 function is_account_manager (p_salesforce_id in number, p_customer_id in number)
757 RETURN BOOLEAN IS
758 cursor account_mgr_csr is
759 select 'x'
760 from as_accesses_all
761 where salesforce_id = p_salesforce_id
762 and salesforce_role_code = 'AM'
763 and customer_id = p_customer_id
764 and lead_id is null
765 and sales_lead_id is null;
766 l_tmp varchar2(1);
767 begin
768 open account_mgr_csr;
769 fetch account_mgr_csr into l_tmp;
770 if account_mgr_csr%FOUND
771 then
772 close account_mgr_csr;
773 return true;
774 else
775 close account_mgr_csr;
776 return false;
777 end if;
778 end is_account_manager;
779
780 function is_sales_lead_owner_row (p_access_id in number)
781 RETURN BOOLEAN IS
782 cursor owner_csr is
783 select 'x'
784 from as_accesses_all
785 where access_id = p_access_id
786 and owner_flag = 'Y';
787
788 l_tmp varchar2(1);
789 begin
790 open owner_csr;
791 fetch owner_csr into l_tmp;
792 if owner_csr%FOUND
793 then
794 close owner_csr;
795 return true;
796 else
797 close owner_csr;
798 return false;
799 end if;
800 end is_sales_lead_owner_row;
801
802 procedure unmark_opp_owner_flag(p_lead_id in number, p_access_id in number) is
803
804 cursor owner_exist_csr is
805 select 'x'
806 from as_accesses_all
807 where lead_id = p_lead_id
808 and access_id <> p_access_id
809 and owner_flag = 'Y';
810
811 l_var varchar2(1);
812 begin
813 open owner_exist_csr;
814 fetch owner_exist_csr into l_var;
815 close owner_exist_csr;
816
817 if l_var is not null
818 then
819 update as_accesses_all
820 set object_version_number = nvl(object_version_number,0) + 1, owner_flag = 'N'
821 where lead_id = p_lead_id
822 and access_id <> p_access_id;
823 end if;
824 end unmark_opp_owner_flag;
825
826
827 procedure unmark_owner_flag(p_sales_lead_id in number, p_access_id in number) is
828
829 cursor owner_exist_csr is
830 select 'x'
831 from as_accesses_all
832 where sales_lead_id = p_sales_lead_id
833 and access_id <> p_access_id
834 and owner_flag = 'Y';
835
836 l_var varchar2(1);
837 begin
838 open owner_exist_csr;
839 fetch owner_exist_csr into l_var;
840 close owner_exist_csr;
841
842 if l_var is not null
843 then
844 update as_accesses_all
845 set object_version_number = nvl(object_version_number,0) + 1, owner_flag = 'N'
846 where sales_lead_id = p_sales_lead_id
847 and access_id <> p_access_id;
848 end if;
849 end unmark_owner_flag;
850
851 PROCEDURE Validate_PERSON_ID (
852 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
853 P_PERSON_ID IN NUMBER,
854 X_Return_Status OUT NOCOPY VARCHAR2,
855 X_Msg_Count OUT NOCOPY NUMBER,
856 X_Msg_Data OUT NOCOPY VARCHAR2
857 )
858 IS
859
860 Cursor C_Check_Assign_Person (X_person_id NUMBER) IS
861 SELECT 'X'
862 FROM per_all_people_f per,
863 jtf_rs_resource_extns res
864 WHERE per.person_id = X_person_id
865 AND TRUNC(SYSDATE) BETWEEN per.effective_start_date
866 AND per.effective_end_date
867 AND res.category = 'EMPLOYEE'
868 AND res.source_id = per.person_id;
869
870 l_val VARCHAR2(1);
871 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
872 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.Validate_PERSON_ID';
873 BEGIN
874 -- Initialize message list IF p_init_msg_list is set to TRUE.
875 IF FND_API.to_Boolean( p_init_msg_list )
876 THEN
877 FND_MSG_PUB.initialize;
878 END IF;
879
880 -- Initialize API return status to SUCCESS
881 x_return_status := FND_API.G_RET_STS_SUCCESS;
882
883 -- Debug Message
884 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
885 -- 'Validate person id');
886
887 -- Validate PERSON_ID
888 IF (p_person_id IS NOT NULL
889 AND p_person_id <> FND_API.G_MISS_NUM)
890 THEN
891 OPEN C_Check_Assign_Person (p_person_id);
892 FETCH C_Check_Assign_Person INTO l_val;
893 IF (C_Check_Assign_Person%NOTFOUND)
894 THEN
895 AS_UTILITY_PVT.Set_Message(
896 p_module => l_module,
897 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
898 p_msg_name => 'API_INVALID_ID',
899 p_token1 => 'COLUMN',
900 p_token1_value => 'PERSON_ID',
901 p_token2 => 'VALUE',
902 p_token2_value => P_PERSON_ID );
903
904 x_return_status := FND_API.G_RET_STS_ERROR;
905 END IF;
906 CLOSE C_Check_Assign_Person;
907 END IF;
908
909 FND_MSG_PUB.Count_And_Get
910 ( p_count => x_msg_count,
911 p_data => x_msg_data );
912 END;
913
914
915 PROCEDURE Validate_SALESFORCE_ID (
916 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
917 P_SALESFORCE_ID IN NUMBER,
918 X_Return_Status OUT NOCOPY VARCHAR2,
919 X_Msg_Count OUT NOCOPY NUMBER,
920 X_Msg_Data OUT NOCOPY VARCHAR2
921 )
922 IS
923
924 Cursor C_Check_Assign_Salesforce (X_Assign_Id NUMBER) IS
925
926 SELECT 'X'
927 FROM jtf_rs_resource_extns res,
928 jtf_rs_role_relations rrel,
929 jtf_rs_roles_b role
930 WHERE sysdate between res.start_date_active and nvl(res.end_date_active,sysdate)
931 AND sysdate between rrel.start_date_active and nvl(rrel.end_date_active,sysdate)
932 AND res.resource_id = rrel.role_resource_id
933 AND rrel.role_resource_type = 'RS_INDIVIDUAL'
934 AND rrel.role_id = role.role_id
935 AND role.role_type_code IN ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
936 AND role.admin_flag = 'N'
937 AND res.resource_id = X_Assign_Id
938 AND res.category in ('EMPLOYEE','PARTY');
939
940 l_val VARCHAR2(1);
941 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.Validate_SALESFORCE_ID';
942
943 BEGIN
944 -- Initialize message list IF p_init_msg_list is set to TRUE.
945 IF FND_API.to_Boolean( p_init_msg_list )
946 THEN
947 FND_MSG_PUB.initialize;
948 END IF;
949
950 -- Initialize API return status to SUCCESS
951 x_return_status := FND_API.G_RET_STS_SUCCESS;
952
953 -- Debug Message
954 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
955 -- 'Validate salesforce id');
956
957
958 IF (p_salesforce_id IS NOT NULL
959 AND p_salesforce_id <> FND_API.G_MISS_NUM)
960 THEN
961 OPEN C_Check_Assign_Salesforce (p_salesforce_id);
962 FETCH C_Check_Assign_Salesforce INTO l_val;
963 IF (C_Check_Assign_Salesforce%NOTFOUND)
964 THEN
965 AS_UTILITY_PVT.Set_Message(
966 p_module => l_module,
967 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
968 p_msg_name => 'API_INVALID_ID',
969 p_token1 => 'COLUMN',
970 p_token1_value => 'SALESFORCE_ID',
971 p_token2 => 'VALUE',
972 p_token2_value => P_SALESFORCE_ID );
973
974 x_return_status := FND_API.G_RET_STS_ERROR;
975 END IF;
976 CLOSE C_Check_Assign_Salesforce;
977 END IF;
978
979 FND_MSG_PUB.Count_And_Get
980 ( p_count => x_msg_count,
981 p_data => x_msg_data );
982 END;
983
984 procedure validate_sales_group_id(
985 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
986 p_sales_group_id IN NUMBER,
987 p_salesforce_id IN NUMBER,
988 x_return_status OUT NOCOPY VARCHAR2,
989 x_msg_count OUT NOCOPY NUMBER,
990 x_msg_data OUT NOCOPY VARCHAR2
991 ) IS
992
993 l_val VARCHAR2(1);
994 l_return_status VARCHAR2(1);
995 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
996 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldhpv.Validate_salesrep_group';
997 CURSOR VALIDATE_SALESGROUP (p_SALESGROUP_ID NUMBER)
998 IS
999 SELECT 'X'
1000 FROM jtf_rs_groups_b grp
1001 WHERE sysdate between GRP.start_date_active and nvl(GRP.end_date_active,sysdate)
1002 AND grp.group_id = p_SALESGROUP_ID ;
1003
1004 CURSOR VALIDATE_COMBINATION (p_SALESREP_ID NUMBER, p_SALESGROUP_ID NUMBER)
1005 IS
1006 SELECT 'X'
1007 FROM jtf_rs_group_members GRPMEM
1008 WHERE resource_id = p_SALESREP_ID
1009 AND group_id = p_SALESGROUP_ID
1010 AND delete_flag = 'N'
1011 AND EXISTS
1012 (SELECT 'X'
1013 FROM jtf_rs_role_relations REL
1014 WHERE role_resource_type = 'RS_GROUP_MEMBER'
1015 AND delete_flag = 'N'
1016 AND sysdate between REL.start_date_active and nvl(REL.end_date_active,sysdate)
1017 AND REL.role_resource_id = GRPMEM.group_member_id
1018 AND role_id IN (SELECT role_id FROM jtf_rs_roles_b WHERE role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM')));
1019 begin
1020 -- initialize message list if p_init_msg_list is set to TRUE;
1021
1022 IF FND_API.to_Boolean(p_init_msg_list) THEN
1023 FND_MSG_PUB.initialize;
1024 END IF;
1025 l_return_status := FND_API.G_RET_STS_SUCCESS;
1026 OPEN VALIDATE_SALESGROUP (p_sales_group_id);
1027 FETCH VALIDATE_SALESGROUP into l_val;
1028 IF VALIDATE_SALESGROUP%NOTFOUND THEN
1029 IF l_debug THEN
1030 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
1031 'Private API: SALES_GROUP_ID is not valid');
1032 END IF;
1033 IF FND_PROFILE.value('ASF_BYPASS_GRP_VALIDATION') = 'Y' THEN
1034 FND_MESSAGE.Set_Name('AS', 'AS_UNSET_BYP_GRP_VALIDN_PROF');
1035 FND_MSG_PUB.ADD;
1036 ELSE
1037 AS_UTILITY_PVT.Set_Message(
1038 p_module => l_module,
1039 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1040 p_msg_name => 'API_INVALID_ID',
1041 p_token1 => 'COLUMN',
1042 p_token1_value => 'SALESGROUP_ID',
1043 p_token2 => 'VALUE',
1044 p_token2_value => p_sales_group_id );
1045 END IF;
1046 x_return_status := FND_API.G_RET_STS_ERROR;
1047 END IF;
1048 CLOSE VALIDATE_SALESGROUP;
1049
1050 OPEN VALIDATE_COMBINATION (p_salesforce_id,p_sales_group_id);
1051 FETCH VALIDATE_COMBINATION into l_val;
1052 IF VALIDATE_COMBINATION%NOTFOUND THEN
1053 IF l_debug THEN
1054 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
1055 'Private API: SALES_GROUP_ID,SALESFORCE_ID is not valid');
1056 END IF;
1057 IF FND_PROFILE.value('ASF_BYPASS_GRP_VALIDATION') = 'Y' THEN
1058 FND_MESSAGE.Set_Name('AS', 'AS_UNSET_BYP_GRP_VALIDN_PROF');
1059 FND_MSG_PUB.ADD;
1060 ELSE
1061 AS_UTILITY_PVT.Set_Message(
1062 p_module => l_module,
1063 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1064 p_msg_name => 'API_INVALID_ID',
1065 p_token1 => 'COLUMN',
1066 p_token1_value => 'SALESFORCE/SALESGROUP COMBINATION',
1067 p_token2 => 'VALUE',
1068 p_token2_value => to_char(p_salesforce_id) || '/' || to_char(p_sales_group_id) );
1069 END IF;
1070 x_return_status := FND_API.G_RET_STS_ERROR;
1071 END IF;
1072 CLOSE VALIDATE_COMBINATION;
1073 FND_MSG_PUB.Count_And_Get
1074 ( p_count => x_msg_count,
1075 p_data => x_msg_data
1076 );
1077
1078 END Validate_sales_group_id;
1079
1080
1081 PROCEDURE Validate_SALES_LEAD_ID (
1082 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1083 ,P_Sales_Lead_Id IN NUMBER
1084 ,X_Return_Status OUT NOCOPY VARCHAR2
1085 ,X_Msg_Count OUT NOCOPY NUMBER
1086 ,X_Msg_Data OUT NOCOPY VARCHAR2
1087 )
1088 IS
1089 CURSOR C_Sales_Lead_Id_Exists (X_Sales_Lead_Id NUMBER) IS
1090 SELECT 'X'
1091 FROM as_sales_leads
1092 WHERE sales_lead_id = X_Sales_Lead_Id;
1093
1094 l_val VARCHAR2(1);
1095 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1096 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.Validate_SALES_LEAD_ID';
1097
1098 BEGIN
1099 -- Initialize message list if p_init_msg_list is set to TRUE.
1100 IF FND_API.to_Boolean( p_init_msg_list )
1101 THEN
1102 FND_MSG_PUB.initialize;
1103 END IF;
1104
1105 -- Initialize API return status to SUCCESS
1106 x_return_status := FND_API.G_RET_STS_SUCCESS;
1107
1108 -- Debug Message
1109 IF l_debug THEN
1110 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1111 'Validate Sales Lead Id');
1112 END IF;
1113
1114 -- Validate Sales Lead Id
1115 OPEN C_Sales_Lead_Id_Exists (p_Sales_Lead_Id);
1116 FETCH C_Sales_Lead_Id_Exists into l_val;
1117
1118 IF C_Sales_Lead_Id_Exists%NOTFOUND
1119 THEN
1120 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1121 THEN
1122 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
1123 FND_MESSAGE.Set_Token('COLUMN', 'SALES_LEAD_ID', FALSE);
1124 FND_MESSAGE.Set_Token('VALUE', p_Sales_Lead_Id, FALSE);
1125 FND_MSG_PUB.ADD;
1126 END IF;
1127
1128 x_return_status := FND_API.G_RET_STS_ERROR;
1129 END IF;
1130 CLOSE C_Sales_Lead_Id_Exists ;
1131
1132 -- Standard call to get message count and if count is 1, get message info.
1133 FND_MSG_PUB.Count_And_Get
1134 ( p_count => x_msg_count,
1135 p_data => x_msg_data );
1136
1137 END Validate_SALES_LEAD_ID;
1138
1139 PROCEDURE Validate_Opportunity_ID (
1140 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1141 ,P_Lead_Id IN NUMBER
1142 ,X_Return_Status OUT NOCOPY VARCHAR2
1143 ,X_Msg_Count OUT NOCOPY NUMBER
1144 ,X_Msg_Data OUT NOCOPY VARCHAR2
1145 )
1146 IS
1147 CURSOR C_Lead_Id_Exists (X_Lead_Id NUMBER) IS
1148 SELECT 'X'
1149 FROM as_leads_all
1150 WHERE lead_id = X_Lead_Id;
1151
1152 l_val VARCHAR2(1);
1153 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1154 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.Validate_Opportunity_ID';
1155
1156 BEGIN
1157 -- Initialize message list if p_init_msg_list is set to TRUE.
1158 IF FND_API.to_Boolean( p_init_msg_list )
1159 THEN
1160 FND_MSG_PUB.initialize;
1161 END IF;
1162
1163 -- Initialize API return status to SUCCESS
1164 x_return_status := FND_API.G_RET_STS_SUCCESS;
1165
1166 -- Debug Message
1167 IF l_debug THEN
1168 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1169 'Validate Sales Lead Id');
1170 END IF;
1171
1172 -- Validate Lead Id
1173 OPEN C_Lead_Id_Exists (p_Lead_Id);
1174 FETCH C_Lead_Id_Exists into l_val;
1175
1176 IF C_Lead_Id_Exists%NOTFOUND
1177 THEN
1178 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1179 THEN
1180 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
1181 FND_MESSAGE.Set_Token('COLUMN', 'LEAD_ID', FALSE);
1182 FND_MESSAGE.Set_Token('VALUE', p_Lead_Id, FALSE);
1183 FND_MSG_PUB.ADD;
1184 END IF;
1185
1186 x_return_status := FND_API.G_RET_STS_ERROR;
1187 END IF;
1188 CLOSE C_Lead_Id_Exists ;
1189
1190 -- Standard call to get message count and if count is 1, get message info.
1191 FND_MSG_PUB.Count_And_Get
1192 ( p_count => x_msg_count,
1193 p_data => x_msg_data );
1194
1195 END Validate_Opportunity_ID;
1196
1197 PROCEDURE Validate_partner_party_id (
1198 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1199 p_party_id IN NUMBER,
1200 x_return_status OUT NOCOPY VARCHAR2,
1201 x_msg_count OUT NOCOPY NUMBER,
1202 x_msg_data OUT NOCOPY VARCHAR2
1203 ) IS
1204
1205 l_val VARCHAR2(1);
1206 l_return_status VARCHAR2(1);
1207
1208 CURSOR C_Party_Exists (X_Party_Id NUMBER) IS
1209 SELECT 1
1210 FROM HZ_PARTIES
1211 WHERE party_id = X_Party_Id;
1212
1213 BEGIN
1214
1215 -- initialize message list if p_init_msg_list is set to TRUE;
1216
1217 IF FND_API.to_Boolean(p_init_msg_list) THEN
1218 FND_MSG_PUB.initialize;
1219 END IF;
1220
1221 l_return_status := FND_API.G_RET_STS_SUCCESS;
1222 open C_Party_Exists(p_party_id);
1223 fetch C_Party_Exists into l_val;
1224 IF (C_Party_Exists%NOTFOUND) THEN
1225 x_return_status := FND_API.G_RET_STS_ERROR;
1226 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1227 THEN
1228 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
1229 FND_MESSAGE.Set_Token('COLUMN', 'PARTNER_CUSTOMER_ID', FALSE);
1230 FND_MSG_PUB.ADD;
1231 END IF;
1232 END IF;
1233 close C_Party_Exists;
1234
1235 FND_MSG_PUB.Count_And_Get
1236 ( p_count => x_msg_count,
1237 p_data => x_msg_data
1238 );
1239
1240 END Validate_partner_party_id;
1241
1242 -- Procedure to validate the party__site_id
1243 --
1244 -- Validation:
1245 -- Check if this party is in the HZ_PARTY_SITES table
1246 --
1247 -- NOTES:
1248 --
1249 PROCEDURE Validate_partner_party_site_id (
1250 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1251 p_party_id IN NUMBER,
1252 p_party_site_id IN NUMBER,
1253 x_return_status OUT NOCOPY VARCHAR2,
1254 x_msg_count OUT NOCOPY NUMBER,
1255 x_msg_data OUT NOCOPY VARCHAR2
1256 ) IS
1257
1258 l_val_1 VARCHAR2(1);
1259 l_val_2 VARCHAR2(1);
1260 l_return_status VARCHAR2(1);
1261
1262 CURSOR C_Party_Site_Exists (X_Party_Id NUMBER, X_Party_Site_Id NUMBER) IS
1263 SELECT 1
1264 FROM AS_PARTY_ADDRESSES_V
1265 WHERE party_id = X_Party_Id
1266 AND party_site_id = X_Party_Site_Id;
1267
1268 -- C_Party_Site_Exists_For_Partner_Party
1269 CURSOR C_Party_Site_Exists_Partner (X_Party_Id NUMBER, X_Party_Site_Id NUMBER) IS
1270 SELECT 1
1271 FROM AS_PARTY_ADDRESSES_V
1272 WHERE party_id = (SELECT PARTNER_PARTY_ID FROM PV_PARTNER_PROFILES WHERE PARTNER_ID = X_Party_Id)
1273 AND party_site_id = X_Party_Site_Id;
1274
1275 BEGIN
1276
1277 -- initialize message list if p_init_msg_list is set to TRUE;
1278
1279 IF FND_API.to_Boolean(p_init_msg_list) THEN
1280 FND_MSG_PUB.initialize;
1281 END IF;
1282
1283 l_return_status := FND_API.G_RET_STS_SUCCESS;
1284 open C_Party_Site_Exists(p_party_id, p_party_site_id);
1285 fetch C_Party_Site_Exists into l_val_1;
1286
1287 IF (C_Party_Site_Exists%NOTFOUND) THEN
1288
1289 open C_Party_Site_Exists_Partner(p_party_id, p_party_site_id);
1290 fetch C_Party_Site_Exists_Partner into l_val_2;
1291
1292 IF (C_Party_Site_Exists_Partner%NOTFOUND) THEN
1293 x_return_status := FND_API.G_RET_STS_ERROR;
1294 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1295 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
1296 FND_MESSAGE.Set_Token('COLUMN', 'PARTNER_ADDRESS_ID', FALSE);
1297 FND_MSG_PUB.ADD;
1298 END IF;
1299 END IF;
1300
1301 close C_Party_Site_Exists_Partner;
1302 END IF;
1303 close C_Party_Site_Exists;
1304
1305
1306 FND_MSG_PUB.Count_And_Get
1307 ( p_count => x_msg_count,
1308 p_data => x_msg_data
1309 );
1310
1311 END Validate_partner_party_site_id;
1312
1313
1314
1315 -- Validate the sales team record:
1316 -- All necessary sales team information is present
1317 -- The customer_id, opportunity id and sales_lead_id are valid
1318 -- also include address_id if profile options set to 'Y'
1319 -- The sales team is not a duplicate (warn only)
1320 -- All the lookups are valid
1321 -- Sales group id is valid
1322 PROCEDURE Validate_SalesTeamItems
1323 ( p_api_version_number IN NUMBER,
1324 p_init_msg_list IN VARCHAR2
1325 := FND_API.G_FALSE,
1326 p_validation_level IN NUMBER
1327 := FND_API.G_VALID_LEVEL_FULL,
1328 p_sales_team_rec IN SALES_TEAM_REC_TYPE,
1329 x_return_status OUT NOCOPY VARCHAR2,
1330 x_msg_count OUT NOCOPY NUMBER,
1331 x_msg_data OUT NOCOPY VARCHAR2
1332 ) is
1333 cursor partner_exist_csr is
1334 select 1
1335 from as_salesforce_v
1336 where salesforce_id = p_sales_team_rec.salesforce_id
1337 and type = 'PARTNER';
1338
1339 l_val NUMBER:=0;
1340 l_sales_team_rec SALES_TEAM_REC_TYPE;
1341 l_check_address VARCHAR2(1);
1342 begin
1343 l_sales_team_rec := p_sales_team_rec;
1344 x_return_status := FND_API.G_RET_STS_SUCCESS;
1345
1346 open partner_exist_csr;
1347 fetch partner_exist_csr into l_val;
1348 close partner_exist_csr;
1349
1350 -- uncomment partner_exist_csr%NOTFOUND
1351 -- if user pass in PARTNER type information without any partner_cont_party_id
1352 -- then allow them to do so
1353
1354 if (p_sales_team_rec.person_id is NULL
1355 or p_sales_team_rec.person_id = FND_API.G_MISS_NUM)
1356 and (l_val <> 1)
1357 and (p_sales_team_rec.partner_cont_party_id is NULL
1358 or p_sales_team_rec. partner_cont_party_id = FND_API.G_MISS_NUM)
1359 then
1360 x_return_status := FND_API.G_RET_STS_ERROR;
1361 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1362 THEN
1363 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1364 FND_MESSAGE.Set_Token('COLUMN', 'PERSON_ID', FALSE);
1365 FND_MSG_PUB.ADD;
1366 END IF;
1367 raise FND_API.G_EXC_ERROR;
1368 end if;
1369 IF (p_sales_team_rec.partner_customer_id IS NULL
1370 or p_sales_team_rec.partner_customer_id = FND_API.G_MISS_NUM)
1371 THEN
1372 -- validate employee person id
1373 Validate_PERSON_ID(
1374 p_init_msg_list => FND_API.G_FALSE,
1375 p_PERSON_ID => p_sales_team_rec.PERSON_ID,
1376 x_return_status => x_return_status,
1377 x_msg_count => x_msg_count,
1378 x_msg_data => x_msg_data);
1379 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1380 x_return_status := FND_API.G_RET_STS_ERROR;
1381 -- raise FND_API.G_EXC_ERROR;
1382 END IF;
1383
1384
1385 -- validate employee salesforce id
1386 Validate_SALESFORCE_ID(
1387 p_init_msg_list => FND_API.G_FALSE,
1388 p_SALESFORCE_ID => p_sales_team_rec.SALESFORCE_ID,
1389 x_return_status => x_return_status,
1390 x_msg_count => x_msg_count,
1391 x_msg_data => x_msg_data);
1392 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1393 x_return_status := FND_API.G_RET_STS_ERROR;
1394 -- raise FND_API.G_EXC_ERROR;
1395 END IF;
1396 end if;
1397
1398 if (p_sales_team_rec.customer_id is NULL
1399 or p_sales_team_rec.customer_id = FND_API.G_MISS_NUM)
1400 or (p_sales_team_rec.salesforce_id is NULL
1401 or p_sales_team_rec.salesforce_id = FND_API.G_MISS_NUM)
1402 then
1403 x_return_status := FND_API.G_RET_STS_ERROR;
1404 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1405 THEN
1406 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1407 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_ID, SALESFORCE_ID', FALSE);
1408 FND_MSG_PUB.ADD;
1409 END IF;
1410 raise FND_API.G_EXC_ERROR;
1411 end if;
1412
1413
1414 if (l_val = 1) and (p_sales_team_rec.partner_customer_id is NULL or
1415 p_sales_team_rec.partner_customer_id = FND_API.G_MISS_NUM)
1416 and (p_sales_team_rec.partner_cont_party_id is NULL
1417 or p_sales_team_rec. partner_cont_party_id = FND_API.G_MISS_NUM)
1418 then
1419 x_return_status := FND_API.G_RET_STS_ERROR;
1420 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1421 THEN
1422 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1423 FND_MESSAGE.Set_Token('COLUMN', 'PARTNER_CUSTOMER_ID', FALSE);
1424 FND_MSG_PUB.ADD;
1425 END IF;
1426 raise FND_API.G_EXC_ERROR;
1427 end if;
1428
1429 if (p_sales_team_rec.partner_customer_id is NULL or
1430 p_sales_team_rec.partner_customer_id = FND_API.G_MISS_NUM)
1431 and (p_sales_team_rec.partner_cont_party_id is NULL
1432 or p_sales_team_rec. partner_cont_party_id = FND_API.G_MISS_NUM)
1433 and (p_sales_team_rec.sales_group_id is NULL
1434 or p_sales_team_rec.sales_group_id = FND_API.G_MISS_NUM)
1435 then
1436 x_return_status := FND_API.G_RET_STS_ERROR;
1437 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1438 THEN
1439 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1440 FND_MESSAGE.Set_Token('COLUMN', 'SALES_GROUP_ID', FALSE);
1441 FND_MSG_PUB.ADD;
1442 END IF;
1443 raise FND_API.G_EXC_ERROR;
1444 end if;
1445
1446 /*
1447 open partner_exist_csr;
1448 fetch partner_exist_csr into l_val;
1449 if partner_exist_csr%FOUND
1450 and (p_sales_team_rec.partner_customer_id is NULL or
1451 p_sales_team_rec.partner_customer_id = FND_API.G_MISS_NUM)
1452 then
1453 x_return_status := FND_API.G_RET_STS_ERROR;
1454 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1455 THEN
1456 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1457 FND_MESSAGE.Set_Token('COLUMN', 'PARTNER_CUSTOMER_ID', FALSE);
1458 FND_MSG_PUB.ADD;
1459 END IF;
1460 end if;
1461 close partner_exist_csr;
1462 */
1463 -- validate sales_group_id
1464 if (p_sales_team_rec.sales_group_id is not null)
1465 and (p_sales_team_rec.sales_group_id<> fnd_api.g_miss_num)
1466 then
1467 validate_sales_group_id(
1468 p_init_msg_list => FND_API.G_FALSE,
1469 p_sales_group_id => p_sales_team_rec.sales_group_id,
1470 p_salesforce_id => p_sales_team_rec.salesforce_id,
1471 x_return_status => x_return_status,
1472 x_msg_count => x_msg_count,
1473 x_msg_data => x_msg_data);
1474 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1475 raise FND_API.G_EXC_ERROR;
1476 END IF;
1477 end if;
1478
1479
1480 -- validate customer_id
1481 AS_TCA_PVT.validate_party_id(
1482 p_init_msg_list => FND_API.G_FALSE,
1483 p_party_id => p_sales_team_rec.customer_id,
1484 x_return_status => x_return_status,
1485 x_msg_count => x_msg_count,
1486 x_msg_data => x_msg_data);
1487 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1488 raise FND_API.G_EXC_ERROR;
1489 END IF;
1490
1491 IF(p_sales_team_rec.address_id is not null
1492 and p_sales_team_rec.address_id <> fnd_api.g_miss_num ) then
1493 -- validate address_id
1494 AS_TCA_PVT.validate_party_site_id(
1495 p_init_msg_list => FND_API.G_FALSE,
1496 p_party_id => p_sales_team_rec.customer_id,
1497 p_party_site_id => p_sales_team_rec.address_id,
1498 x_return_status => x_return_status,
1499 x_msg_count => x_msg_count,
1500 x_msg_data => x_msg_data);
1501 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1502 raise FND_API.G_EXC_ERROR;
1503 END IF;
1504 END IF;
1505
1506
1507 IF(p_sales_team_rec.partner_customer_id is not null
1508 and p_sales_team_rec.partner_customer_id <> fnd_api.g_miss_num )
1509 and
1510 (p_sales_team_rec.partner_address_id is not null
1511 and p_sales_team_rec.partner_address_id <> fnd_api.g_miss_num ) then
1512
1513 validate_partner_party_site_id(
1514 p_init_msg_list => FND_API.G_FALSE,
1515 p_party_id => p_sales_team_rec.partner_customer_id,
1516 p_party_site_id => p_sales_team_rec.partner_address_id,
1517 x_return_status => x_return_status,
1518 x_msg_count => x_msg_count,
1519 x_msg_data => x_msg_data);
1520 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1521 raise FND_API.G_EXC_ERROR;
1522 END IF;
1523 END IF;
1524
1525 if (p_sales_team_rec.partner_customer_id is not null
1526 and p_sales_team_rec.partner_customer_id <> fnd_api.g_miss_num )
1527 then
1528 validate_partner_party_id(
1529 p_init_msg_list => FND_API.G_FALSE,
1530 p_party_id => p_sales_team_rec.partner_customer_id,
1531 x_return_status => x_return_status,
1532 x_msg_count => x_msg_count,
1533 x_msg_data => x_msg_data);
1534 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1535 raise FND_API.G_EXC_ERROR;
1536 END IF;
1537 end if;
1538
1539
1540
1541 -- validate sales_lead_id
1542 if (p_sales_team_rec.sales_lead_id is NOT NULL
1543 and p_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
1544 then
1545 Validate_Sales_Lead_Id (
1546 P_Init_Msg_List => FND_API.G_FALSE
1547 ,P_Sales_Lead_Id => P_sales_team_rec.Sales_Lead_Id
1548 ,X_Return_Status => X_Return_Status
1549 ,X_Msg_Count => X_Msg_Count
1550 ,X_Msg_Data => X_Msg_Data);
1551
1552 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1553 raise FND_API.G_EXC_ERROR;
1554 END IF;
1555 end if;
1556
1557 -- validate opportunity_id
1558 if (p_sales_team_rec.lead_id is NOT NULL
1559 and p_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
1560 then
1561 Validate_Opportunity_Id (
1562 P_Init_Msg_List => FND_API.G_FALSE
1563 ,P_Lead_Id => p_sales_team_rec.lead_id
1564 ,X_Return_Status => X_Return_Status
1565 ,X_Msg_Count => X_Msg_Count
1566 ,X_Msg_Data => X_Msg_Data);
1567
1568 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1569 raise FND_API.G_EXC_ERROR;
1570 END IF;
1571 end if;
1572
1573
1574 if not salesTeam_flags_are_valid( l_sales_team_rec )
1575 then
1576 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1577 THEN
1578 x_return_status := fnd_api.g_ret_sts_error;
1579 FND_MESSAGE.Set_Name('AS', 'API_INVALID_INFO');
1580 FND_MESSAGE.Set_Token('ROW', 'AS_ACCESSES', TRUE);
1581 FND_MSG_PUB.ADD;
1582 END IF;
1583 end if;
1584
1585 end Validate_SalesTeamItems;
1586
1587 -- Perform sales team duplication check. Note that this only produces a
1588 -- warning to let the user know that a possible duplicate sales team has
1589 -- been inserted, it is not an Error
1590
1591 function duplicate_salesTeam(p_sales_team_rec in SALES_TEAM_REC_TYPE) return boolean
1592 is
1593 cursor get_dup_salesTeam_csr is
1594 select access_id
1595 from as_accesses
1596 where customer_id = p_sales_team_rec.customer_id
1597 and nvl(lead_id, -99) = nvl(p_sales_team_rec.lead_id, -99)
1598 and nvl(sales_lead_id, -99) = nvl(p_sales_team_rec.sales_lead_id, -99)
1599 and salesforce_id = p_sales_team_rec.salesforce_id
1600 and nvl(sales_group_id, -99) = nvl(p_sales_team_rec.sales_group_id, -99);
1601
1602
1603 l_val NUMBER;
1604 begin
1605
1606 open get_dup_salesTeam_csr;
1607 fetch get_dup_salesTeam_csr into l_val;
1608 if (get_dup_salesTeam_csr%FOUND)
1609 then
1610 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1611 THEN
1612 FND_MESSAGE.Set_Name('AS', 'API_DUP_SALESTEAM');
1613 FND_MSG_PUB.ADD;
1614 END IF;
1615 return TRUE;
1616 else
1617 return FALSE;
1618 end if;
1619 close get_dup_salesTeam_csr;
1620 end duplicate_salesTeam;
1621
1622 PROCEDURE Create_SalesTeam
1623 ( p_api_version_number IN NUMBER,
1624 p_init_msg_list IN VARCHAR2
1625 := FND_API.G_FALSE,
1626 p_commit IN VARCHAR2
1627 := FND_API.G_FALSE,
1628 p_validation_level IN NUMBER
1629 := FND_API.G_VALID_LEVEL_FULL,
1630 p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
1631 p_check_access_flag IN VARCHAR2,
1632 p_admin_flag IN VARCHAR2,
1633 p_admin_group_id IN NUMBER,
1634 p_identity_salesforce_id IN NUMBER,
1635 p_sales_team_rec IN SALES_TEAM_REC_TYPE,
1636 x_return_status OUT NOCOPY VARCHAR2,
1637 x_msg_count OUT NOCOPY NUMBER,
1638 x_msg_data OUT NOCOPY VARCHAR2,
1639 x_access_id OUT NOCOPY NUMBER
1640 ) is
1641
1642 l_api_name CONSTANT VARCHAR2(30) := 'Create_SalesTeam';
1643 l_api_version_number CONSTANT NUMBER := 2.0;
1644 l_rowid ROWID;
1645 l_access_id NUMBER;
1646 l_return_status VARCHAR2(1);
1647 l_member_access VARCHAR2(1);
1648 l_member_role VARCHAR2(1);
1649 l_sales_team_rec SALES_TEAM_REC_TYPE;
1650 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
1651 l_internal_update_access number;
1652 l_open_flag VARCHAR2(1);
1653 l_update_access_flag varchar2(1);
1654 l_object_creation_date date;
1655 l_lead_rank_score number;
1656
1657 cursor get_opp_open_flag_csr(pc_lead_id number) is
1658 select decode(st.opp_open_status_flag,'N',NULL,st.opp_open_status_flag)
1659 from as_leads_all ld, as_statuses_b st
1660 where ld.lead_id = pc_lead_id
1661 and ld.status = st.status_code(+);
1662
1663 cursor get_lead_open_flag_csr(pc_sales_lead_id number) is
1664 select decode(st.opp_open_status_flag,'N',NULL,st.opp_open_status_flag)
1665 from as_sales_leads ld, as_statuses_b st
1666 where ld.sales_lead_id = pc_sales_lead_id
1667 and ld.status_code = st.status_code(+);
1668
1669 cursor get_lead_rank_score_csr(pc_sales_lead_id number) is
1670 select rank.min_score
1671 from as_sales_lead_ranks_b rank, as_sales_leads sl
1672 where sl.sales_lead_id = pc_sales_lead_id
1673 and sl.lead_rank_id = rank.rank_id(+);
1674
1675 cursor get_lead_creation_date_csr(pc_sales_lead_id number) is
1676 select sl.creation_date
1677 from as_sales_leads sl
1678 where sl.sales_lead_id = pc_sales_lead_id;
1679
1680 cursor get_dup_access_id_csr(p_sales_team_rec in SALES_TEAM_REC_TYPE) is
1681 select access_id
1682 from as_accesses
1683 where customer_id = p_sales_team_rec.customer_id
1684 and nvl(address_id, -99) = nvl(p_sales_team_rec.address_id, -99)
1685 and nvl(lead_id, -99) = nvl(p_sales_team_rec.lead_id, -99)
1686 and nvl(sales_lead_id, -99) = nvl(p_sales_team_rec.sales_lead_id, -99)
1687 and salesforce_id = p_sales_team_rec.salesforce_id
1688 and nvl(sales_group_id, -99) = nvl(p_sales_team_rec.sales_group_id, -99)
1689 and nvl(salesforce_role_code, 'X') = nvl(p_sales_team_rec.salesforce_role_code, 'X');
1690
1691 cursor lc_resource_type (pc_resource_id number) is
1692 select source_id
1693 from jtf_rs_resource_extns
1694 where resource_id = pc_resource_id
1695 and category = 'PARTY';
1696
1697 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.Create_SalesTeam';
1698
1699 begin
1700 -- Standard Start of API savepoint
1701 SAVEPOINT CREATE_SALESTEAM_PVT;
1702
1703 -- Standard call to check for call compatibility.
1704 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1705 p_api_version_number,
1706 l_api_name,
1707 G_PKG_NAME)
1708 THEN
1709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1710 END IF;
1711
1712
1713 -- Initialize message list if p_init_msg_list is set to TRUE.
1714 IF FND_API.to_Boolean( p_init_msg_list )
1715 THEN
1716 FND_MSG_PUB.initialize;
1717 END IF;
1718
1719 -- Initialize API return status to success
1720 x_return_status := FND_API.G_RET_STS_SUCCESS;
1721 l_return_status := FND_API.G_RET_STS_SUCCESS;
1722 --
1723 -- API body
1724 --
1725
1726 -- ******************************************************************
1727 -- Validate Environment
1728 -- ******************************************************************
1729 IF FND_GLOBAL.User_Id IS NULL
1730 THEN
1731 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1732 THEN
1733 FND_MESSAGE.Set_Name('AS', 'UT_CANNOT_GET_PROFILE_VALUE');
1734 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1735 FND_MSG_PUB.ADD;
1736 END IF;
1737 RAISE FND_API.G_EXC_ERROR;
1738 END IF;
1739
1740 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
1741 then
1742
1743 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1744 p_api_version_number => 2.0
1745 ,p_salesforce_id => p_identity_salesforce_id
1746 , p_admin_group_id => p_admin_group_id
1747 ,x_return_status => l_return_status
1748 ,x_msg_count => x_msg_count
1749 ,x_msg_data => x_msg_data
1750 ,x_sales_member_rec => l_identity_sales_member_rec);
1751
1752 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1753 RAISE FND_API.G_EXC_ERROR;
1754 END IF;
1755 end if;
1756
1757 -- ******************************************************************
1758
1759 l_sales_team_rec := p_sales_team_rec;
1760
1761 if l_sales_team_rec.partner_cont_party_id is null
1762 or l_sales_team_rec.partner_cont_party_id = fnd_api.g_miss_num
1763 then
1764 open lc_resource_type (l_sales_team_rec.salesforce_id);
1765 fetch lc_resource_type into l_sales_team_rec.partner_cont_party_id;
1766 close lc_resource_type;
1767 end if;
1768
1769 IF ( P_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM)
1770 THEN
1771 Validate_SalesTeamItems(
1772 p_api_version_number => 2.0,
1773 p_init_msg_list => FND_API.G_FALSE,
1774 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1775 p_sales_team_rec => l_sales_team_rec,
1776 x_return_status => x_return_status,
1777 x_msg_count => x_msg_count,
1778 x_msg_data => x_msg_data);
1779 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1780 THEN
1781 RAISE FND_API.G_EXC_ERROR;
1782 END IF;
1783 end if;
1784
1785 if p_check_access_flag = 'Y'
1786 then
1787 IF (p_sales_team_rec.lead_id is NULL or p_sales_team_rec.lead_id = FND_API.G_MISS_NUM)
1788 and (p_sales_team_rec.sales_lead_id is NULL or p_sales_team_rec.sales_lead_id = FND_API.G_MISS_NUM)
1789 THEN
1790 AS_ACCESS_PUB.has_updateCustomerAccess
1791 ( p_api_version_number => 2.0
1792 ,p_init_msg_list => p_init_msg_list
1793 ,p_validation_level => p_validation_level
1794 ,p_access_profile_rec => p_access_profile_rec
1795 ,p_admin_flag => p_admin_flag
1796 ,p_admin_group_id => p_admin_group_id
1797 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1798 ,p_customer_id => p_sales_team_rec.customer_id
1799 ,p_check_access_flag => 'Y'
1800 ,p_identity_salesforce_id => p_identity_salesforce_id
1801 ,p_partner_cont_party_id => NULL
1802 ,x_return_status => l_return_status
1803 ,x_msg_count => x_msg_count
1804 ,x_msg_data => x_msg_data
1805 ,x_update_access_flag => l_update_access_flag
1806 );
1807
1808 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1809 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1810 END IF;
1811
1812 IF (l_update_access_flag <> 'Y') THEN
1813 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1814 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
1815 FND_MSG_PUB.ADD;
1816 END IF;
1817 RAISE FND_API.G_EXC_ERROR;
1818 END IF;
1819 ELSIF (p_sales_team_rec.lead_id is not NULL and p_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
1820 then
1821
1822 AS_ACCESS_PUB.has_updateOpportunityAccess
1823 ( p_api_version_number => 2.0
1824 ,p_init_msg_list => p_init_msg_list
1825 ,p_validation_level => p_validation_level
1826 ,p_access_profile_rec => p_access_profile_rec
1827 ,p_admin_flag => p_admin_flag
1828 ,p_admin_group_id => p_admin_group_id
1829 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1830 ,p_opportunity_id => p_sales_team_rec.lead_id
1831 ,p_check_access_flag => 'Y'
1832 ,p_identity_salesforce_id => p_identity_salesforce_id
1833 ,p_partner_cont_party_id => Null
1834 ,x_return_status => l_return_status
1835 ,x_msg_count => x_msg_count
1836 ,x_msg_data => x_msg_data
1837 ,x_update_access_flag => l_update_access_flag
1838 );
1839
1840 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1841 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1842 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOpportunityAccess fail');
1843 END IF;
1844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1845 END IF;
1846
1847 IF (l_update_access_flag <> 'Y') THEN
1848 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1849 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
1850 FND_MSG_PUB.ADD;
1851 END IF;
1852 RAISE FND_API.G_EXC_ERROR;
1853 END IF;
1854 ELSIF (p_sales_team_rec.sales_lead_id is not NULL and p_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
1855 then
1856 AS_ACCESS_PUB.has_updateLeadAccess
1857 ( p_api_version_number => 2.0
1858 ,p_init_msg_list => p_init_msg_list
1859 ,p_validation_level => p_validation_level
1860 ,p_access_profile_rec => p_access_profile_rec
1861 ,p_admin_flag => p_admin_flag
1862 ,p_admin_group_id => p_admin_group_id
1863 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1864 ,p_sales_lead_id => p_sales_team_rec.sales_lead_id
1865 ,p_check_access_flag => 'Y'
1866 ,p_identity_salesforce_id => p_identity_salesforce_id
1867 ,p_partner_cont_party_id => Null
1868 ,x_return_status => l_return_status
1869 ,x_msg_count => x_msg_count
1870 ,x_msg_data => x_msg_data
1871 ,x_update_access_flag => l_update_access_flag
1872 );
1873
1874 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1875 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1876 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOpportunityAccess fail');
1877 END IF;
1878 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1879 END IF;
1880
1881 IF (l_update_access_flag <> 'Y') THEN
1882 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1883 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
1884 FND_MSG_PUB.ADD;
1885 END IF;
1886 RAISE FND_API.G_EXC_ERROR;
1887 END IF;
1888 END IF;
1889 end if; -- p_check_access_flag = 'Y'
1890
1891 if l_sales_team_rec.address_id = FND_API.G_MISS_NUM then
1892 l_sales_team_rec.address_id := NULL;
1893 end if;
1894 if l_sales_team_rec.lead_id = FND_API.G_MISS_NUM then
1895 l_sales_team_rec.lead_id := NULL;
1896 end if;
1897 if l_sales_team_rec.sales_lead_id = FND_API.G_MISS_NUM then
1898 l_sales_team_rec.sales_lead_id := NULL;
1899 end if;
1900 if l_sales_team_rec.sales_group_id = FND_API.G_MISS_NUM then
1901 l_sales_team_rec.sales_group_id := NULL;
1902 end if;
1903
1904 if l_sales_team_rec.salesforce_role_code = FND_API.G_MISS_CHAR then
1905 l_sales_team_rec.salesforce_role_code := NULL;
1906 end if;
1907
1908 --Owner is always a team leader
1909 if l_sales_team_rec.owner_flag = 'Y' then
1910 l_sales_team_rec.team_leader_flag := 'Y';
1911 end if;
1912
1913
1914 if duplicate_salesTeam(l_sales_team_rec)
1915 then
1916 x_return_status := FND_API.G_RET_STS_SUCCESS;
1917 -- open get_dup_access_id_csr(l_sales_team_rec);
1918 -- fetch get_dup_access_id_csr into x_access_id;
1919 -- close get_dup_access_id_csr;
1920 return;
1921 end if;
1922
1923 if l_sales_team_rec.reassign_flag = 'Y'
1924 and l_sales_team_rec.reassign_reason is null
1925 then
1926 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1927 THEN
1928 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1929 FND_MESSAGE.Set_Token('COLUMN', 'reassign_reason', FALSE);
1930 FND_MSG_PUB.ADD;
1931 END IF;
1932 raise fnd_api.g_exc_error;
1933 end if;
1934
1935 --if (l_sales_team_rec.sales_lead_id is NOT NULL
1936 -- and l_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
1937 -- and l_sales_team_rec.owner_flag = 'Y'
1938 -- and p_check_access_flag = 'Y'
1939
1940 if (l_sales_team_rec.sales_lead_id is NOT NULL
1941 and l_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
1942 and l_sales_team_rec.owner_flag = 'Y'
1943 and fnd_profile.value('AS_ALLOW_CHANGE_LEAD_OWNER')<>'Y'
1944 and p_check_access_flag = 'Y'
1945 then
1946 has_leadOwnerAccess
1947 ( p_api_version_number => 2.0
1948 ,p_init_msg_list => p_init_msg_list
1949 ,p_validation_level => p_validation_level
1950 ,p_access_profile_rec => p_access_profile_rec
1951 ,p_admin_flag => p_admin_flag
1952 ,p_admin_group_id => p_admin_group_id
1953 ,p_person_id =>l_identity_sales_member_rec.employee_person_id
1954 ,p_sales_lead_id => p_sales_team_rec.sales_lead_id
1955 ,p_check_access_flag => 'Y'
1956 ,p_identity_salesforce_id => p_identity_salesforce_id
1957 ,p_partner_cont_party_id => Null
1958 ,x_return_status => l_return_status
1959 ,x_msg_count => x_msg_count
1960 ,x_msg_data => x_msg_data
1961 ,x_update_access_flag => l_update_access_flag
1962 );
1963
1964 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1965 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1966 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateLeadAccess fail');
1967 END IF;
1968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1969 END IF;
1970
1971 IF (l_update_access_flag <> 'Y') THEN
1972 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1973 FND_MESSAGE.Set_Name('AS', 'API_NO_OWNER_PRIVILEGE');
1974 FND_MSG_PUB.ADD;
1975 END IF;
1976 RAISE FND_API.G_EXC_ERROR;
1977 END IF;
1978 end if; -- check owner privilege
1979
1980
1981 if (l_sales_team_rec.lead_id is NOT NULL
1982 and l_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
1983 and l_sales_team_rec.owner_flag = 'Y'
1984 and fnd_profile.value('AS_ALLOW_CHANGE_OPP_OWNER')<>'Y'
1985 --and p_check_access_flag = 'Y'
1986 then
1987 has_oppOwnerAccess
1988 ( p_api_version_number => 2.0
1989 ,p_init_msg_list => p_init_msg_list
1990 ,p_validation_level => p_validation_level
1991 ,p_access_profile_rec => p_access_profile_rec
1992 ,p_admin_flag => p_admin_flag
1993 ,p_admin_group_id => p_admin_group_id
1994 ,p_person_id =>l_identity_sales_member_rec.employee_person_id
1995 ,p_lead_id => p_sales_team_rec.lead_id
1996 ,p_check_access_flag => 'Y'
1997 ,p_identity_salesforce_id => p_identity_salesforce_id
1998 ,p_partner_cont_party_id => Null
1999 ,x_return_status => l_return_status
2000 ,x_msg_count => x_msg_count
2001 ,x_msg_data => x_msg_data
2002 ,x_update_access_flag => l_update_access_flag );
2003 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2004 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2005 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOppAccess fail');
2006 END IF;
2007 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2008 END IF;
2009
2010 IF (l_update_access_flag <> 'Y') THEN
2011 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2012 FND_MESSAGE.Set_Name('AS', 'API_NO_OPP_OWNER_PRIVILEGE');
2013 FND_MSG_PUB.ADD;
2014 END IF;
2015 RAISE FND_API.G_EXC_ERROR;
2016 END IF;
2017
2018 end if;
2019
2020
2021
2022
2023
2024 -- Account manager
2025 if l_sales_team_rec.salesforce_role_code = 'AM'
2026 and (nvl(fnd_profile.value('AS_DEF_CUST_ST_ROLE'),'PS') <>'AM')
2027
2028 then
2029 if not is_account_manager(p_identity_salesforce_id, l_sales_team_rec.customer_id)
2030 and (nvl(fnd_profile.value('AS_CUST_ACCESS'),'F') <>'F')
2031 -- if login person is not account manager and not full access,he can't make other
2032 -- people account manager
2033
2034 then
2035 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2036 THEN
2037 FND_MESSAGE.Set_Name('AS','API_NO_ACC_MGR_PRIVILEGE');
2038 FND_MSG_PUB.ADD;
2039 END IF;
2040 RAISE FND_API.G_EXC_ERROR;
2041 end if;
2042 end if;
2043 -- changes made by Jean. for HP security enhancement
2044 --if (l_sales_team_rec.lead_id is NOT NULL
2045 -- and l_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
2046 -- or (l_sales_team_rec.sales_lead_id is NOT NULL
2047 -- and l_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
2048 -- then
2049 if l_sales_team_rec.team_leader_flag = 'Y' or l_sales_team_rec.owner_flag = 'Y'
2050 then
2051 l_internal_update_access := 1;
2052 else l_internal_update_access := 0;
2053 end if;
2054 -- else l_internal_update_access := 1;
2055 -- end if;
2056 if l_sales_team_rec.freeze_flag is null
2057 or l_sales_team_rec.freeze_flag = fnd_api.g_miss_char
2058 then
2059 l_sales_team_rec.freeze_flag := Nvl(FND_PROFILE.Value('AS_DEFAULT_FREEZE_FLAG'), 'Y');
2060 end if;
2061
2062 --if l_sales_team_rec.owner_flag = 'Y' and (l_sales_team_rec.sales_lead_id is NOT NULL
2063 -- and l_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
2064
2065 --then
2066 -- unmark_owner_flag(l_sales_team_rec.sales_lead_id);
2067 --end if;
2068
2069 --if l_sales_team_rec.owner_flag = 'Y' and (l_sales_team_rec.lead_id is NOT NULL
2070 -- and l_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
2071
2072 --then
2073 -- unmark_opp_owner_flag(l_sales_team_rec.lead_id);
2074 --end if;
2075
2076 if l_sales_team_rec.lead_id is not null
2077 then
2078 open get_opp_open_flag_csr(l_sales_team_rec.lead_id);
2079 fetch get_opp_open_flag_csr into l_open_flag;
2080 close get_opp_open_flag_csr;
2081 end if;
2082
2083 if l_sales_team_rec.sales_lead_id is not null
2084 then
2085 open get_lead_open_flag_csr(l_sales_team_rec.sales_lead_id);
2086 fetch get_lead_open_flag_csr into l_open_flag;
2087 close get_lead_open_flag_csr;
2088
2089 open get_lead_rank_score_csr(l_sales_team_rec.sales_lead_id);
2090 fetch get_lead_rank_score_csr into l_lead_rank_score;
2091 close get_lead_rank_score_csr;
2092
2093 if(l_lead_rank_score is null) then
2094 l_lead_rank_score := 0;
2095 end if;
2096
2097 open get_lead_creation_date_csr(l_sales_team_rec.sales_lead_id);
2098 fetch get_lead_creation_date_csr into l_object_creation_date;
2099 close get_lead_creation_date_csr;
2100
2101 end if;
2102
2103 as_accesses_pkg.insert_row(
2104 X_Rowid => l_rowid,
2105 X_Access_Id => l_access_id,
2106 X_Last_Update_Date => SYSDATE,
2107 X_Last_Updated_By => FND_GLOBAL.User_Id,
2108 X_Creation_Date => SYSDATE,
2109 X_Created_By => FND_GLOBAL.User_Id,
2110 X_Last_Update_Login => FND_GLOBAL.Conc_Login_Id,
2111 X_Access_Type => 'X',
2112 X_Freeze_Flag => l_sales_team_rec.freeze_flag,
2113 X_Reassign_Flag => l_sales_team_rec.reassign_flag,
2114 X_Team_Leader_Flag => l_sales_team_rec.team_leader_flag,
2115 X_Person_Id => l_sales_team_rec.person_id,
2116 X_Customer_Id => l_sales_team_rec.customer_id,
2117 X_Address_Id => l_sales_team_rec.address_id,
2118 X_Salesforce_id => l_sales_team_rec.salesforce_id,
2119 X_Created_Person_Id => l_sales_team_rec.created_person_id,
2120 X_Partner_Customer_id => l_sales_team_rec.partner_customer_id,
2121 X_Partner_Address_id => l_sales_team_rec.partner_address_id,
2122 X_Lead_Id => l_sales_team_rec.lead_id,
2123 X_Freeze_Date => l_sales_team_rec.freeze_date,
2124 X_Reassign_Reason => l_sales_team_rec.reassign_reason,
2125 X_Reassign_request_date => l_sales_team_rec.reassign_request_date,
2126 X_Reassign_requested_person_id => l_sales_team_rec.reassign_requested_person_id,
2127 X_Attribute_Category => l_sales_team_rec.attribute_category,
2128 X_Attribute1 => l_sales_team_rec.attribute1,
2129 X_Attribute2 => l_sales_team_rec.attribute2,
2130 X_Attribute3 => l_sales_team_rec.attribute3,
2131 X_Attribute4 => l_sales_team_rec.attribute4,
2132 X_Attribute5 => l_sales_team_rec.attribute5,
2133 X_Attribute6 => l_sales_team_rec.attribute6,
2134 X_Attribute7 => l_sales_team_rec.attribute7,
2135 X_Attribute8 => l_sales_team_rec.attribute8,
2136 X_Attribute9 => l_sales_team_rec.attribute9,
2137 X_Attribute10 => l_sales_team_rec.attribute10,
2138 X_Attribute11 => l_sales_team_rec.attribute11,
2139 X_Attribute12 => l_sales_team_rec.attribute12,
2140 X_Attribute13 => l_sales_team_rec.attribute13,
2141 X_Attribute14 => l_sales_team_rec.attribute14,
2142 X_Attribute15 => l_sales_team_rec.attribute15,
2143 X_Sales_group_id => l_sales_team_rec.sales_group_id,
2144 X_Sales_lead_id => l_sales_team_rec.sales_lead_id,
2145 X_Internal_update_access => l_internal_update_access,
2146 X_Partner_Cont_Party_Id => l_sales_team_rec.partner_cont_party_id,
2147 X_owner_flag => l_sales_team_rec.owner_flag,
2148 X_created_by_tap_flag =>l_sales_team_rec.created_by_tap_flag,
2149 X_prm_keep_flag => l_sales_team_rec.prm_keep_flag,
2150 X_Salesforce_Role_Code => l_sales_team_rec.salesforce_role_code,
2151 X_Salesforce_Relationship_Code => l_sales_team_rec.salesforce_relationship_code,
2152 X_open_flag => l_open_flag,
2153 X_lead_rank_score => l_lead_rank_score,
2154 X_object_creation_date => l_object_creation_date,
2155 X_contributor_flag =>l_sales_team_rec.contributor_flag); -- Added for ASNB
2156
2157 x_access_id := l_access_id;
2158 x_return_status := l_return_status;
2159
2160 if is_sales_lead_owner_row(x_access_id)
2161 then
2162 update as_leads_all set object_version_number = nvl(object_version_number,0) + 1, owner_salesforce_id = l_sales_team_rec.salesforce_id,
2163 owner_sales_group_id = l_sales_team_rec.sales_group_id
2164 where lead_id = l_sales_team_rec.lead_id;
2165 end if;
2166
2167 if is_sales_lead_owner_row(x_access_id)
2168 and (l_sales_team_rec.sales_lead_id is NOT NULL
2169 and l_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
2170
2171 then
2172 unmark_owner_flag(l_sales_team_rec.sales_lead_id, x_access_id);
2173 end if;
2174
2175 if is_sales_lead_owner_row(x_access_id)
2176 and (l_sales_team_rec.lead_id is NOT NULL
2177 and l_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
2178 then
2179 unmark_opp_owner_flag(l_sales_team_rec.lead_id, x_access_id);
2180 end if;
2181
2182
2183 -- Standard check of p_commit.
2184 IF FND_API.To_Boolean ( p_commit )
2185 THEN
2186 COMMIT WORK;
2187 END IF;
2188
2189 -- Standard call to get message count and if count is 1, get message info.
2190 FND_MSG_PUB.Count_And_Get
2191 ( p_count => x_msg_count,
2192 p_data => x_msg_data
2193 );
2194
2195
2196 EXCEPTION
2197
2198 WHEN DUP_VAL_ON_INDEX THEN
2199 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2200 THEN
2201 x_return_status := FND_API.G_RET_STS_SUCCESS;
2202 open get_dup_access_id_csr(l_sales_team_rec);
2203 fetch get_dup_access_id_csr into x_access_id;
2204 close get_dup_access_id_csr;
2205 FND_MESSAGE.Set_Name('AS', 'API_DUP_SALESTEAM');
2206 FND_MSG_PUB.ADD;
2207
2208 END IF;
2209
2210 WHEN FND_API.G_EXC_ERROR THEN
2211 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2212 P_MODULE => l_module
2213 ,P_API_NAME => L_API_NAME
2214 ,P_PKG_NAME => G_PKG_NAME
2215 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2216 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2217 ,X_MSG_COUNT => X_MSG_COUNT
2218 ,X_MSG_DATA => X_MSG_DATA
2219 ,X_RETURN_STATUS => X_RETURN_STATUS);
2220
2221 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2222 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2223 P_MODULE => l_module
2224 ,P_API_NAME => L_API_NAME
2225 ,P_PKG_NAME => G_PKG_NAME
2226 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2227 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2228 ,X_MSG_COUNT => X_MSG_COUNT
2229 ,X_MSG_DATA => X_MSG_DATA
2230 ,X_RETURN_STATUS => X_RETURN_STATUS);
2231
2232 WHEN OTHERS THEN
2233 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2234 P_MODULE => l_module
2235 ,P_API_NAME => L_API_NAME
2236 ,P_PKG_NAME => G_PKG_NAME
2237 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
2238 ,P_SQLCODE => SQLCODE
2239 ,P_SQLERRM => SQLERRM
2240 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2241 ,X_MSG_COUNT => X_MSG_COUNT
2242 ,X_MSG_DATA => X_MSG_DATA
2243 ,X_RETURN_STATUS => X_RETURN_STATUS);
2244
2245
2246 end Create_SalesTeam;
2247
2248
2249 PROCEDURE Delete_SalesTeam
2250 ( p_api_version_number IN NUMBER,
2251 p_init_msg_list IN VARCHAR2
2252 := FND_API.G_FALSE,
2253 p_commit IN VARCHAR2
2254 := FND_API.G_FALSE,
2255 p_validation_level IN NUMBER
2256 := FND_API.G_VALID_LEVEL_FULL,
2257 p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
2258 p_check_access_flag IN VARCHAR2,
2259 p_admin_flag IN VARCHAR2,
2260 p_admin_group_id IN NUMBER,
2261 p_identity_salesforce_id IN NUMBER,
2262 p_sales_team_rec IN SALES_TEAM_REC_TYPE,
2263 x_return_status OUT NOCOPY VARCHAR2,
2264 x_msg_count OUT NOCOPY NUMBER,
2265 x_msg_data OUT NOCOPY VARCHAR2
2266 ) is
2267
2268 cursor get_access_info_csr(p_access_id NUMBER) is
2269 select 1
2270 from as_accesses
2271 where access_id = p_access_id;
2272
2273 l_api_name CONSTANT VARCHAR2(30) := 'Delete_SalesTeam';
2274 l_api_version_number CONSTANT NUMBER := 2.0;
2275 l_return_status VARCHAR2(1);
2276 l_member_access VARCHAR2(1);
2277 l_member_role VARCHAR2(1);
2278 l_val NUMBER;
2279 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
2280 l_update_access_flag varchar2(1);
2281 l_is_owner varchar2(1);
2282 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.Delete_SalesTeam';
2283
2284 begin
2285 -- Standard Start of API savepoint
2286 SAVEPOINT DELETE_SALESTEAM_PVT;
2287
2288 -- Standard call to check for call compatibility.
2289 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2290 p_api_version_number,
2291 l_api_name,
2292 G_PKG_NAME)
2293 THEN
2294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2295 END IF;
2296
2297
2298 -- Initialize message list if p_init_msg_list is set to TRUE.
2299 IF FND_API.to_Boolean( p_init_msg_list )
2300 THEN
2301 FND_MSG_PUB.initialize;
2302 END IF;
2303
2304 -- Initialize API return status to success
2305 x_return_status := FND_API.G_RET_STS_SUCCESS;
2306 l_return_status := FND_API.G_RET_STS_SUCCESS;
2307 --
2308 -- API body
2309 --
2310
2311 -- ******************************************************************
2312 -- Validate Environment
2313 -- ******************************************************************
2314 IF FND_GLOBAL.User_Id IS NULL
2315 THEN
2316 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2317 THEN
2318 FND_MESSAGE.Set_Name('AS', 'UT_CANNOT_GET_PROFILE_VALUE');
2319 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
2320 FND_MSG_PUB.ADD;
2321 END IF;
2322 RAISE FND_API.G_EXC_ERROR;
2323 END IF;
2324
2325 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
2326 then
2327
2328 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
2329 p_api_version_number => 2.0
2330 ,p_salesforce_id => p_identity_salesforce_id
2331 , p_admin_group_id => p_admin_group_id
2332 ,x_return_status => l_return_status
2333 ,x_msg_count => x_msg_count
2334 ,x_msg_data => x_msg_data
2335 ,x_sales_member_rec => l_identity_sales_member_rec);
2336
2337 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2338 RAISE FND_API.G_EXC_ERROR;
2339 END IF;
2340 end if;
2341
2342
2343 -- ******************************************************************
2344
2345 if (p_sales_team_rec.access_id is NULL)
2346 then
2347 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2348 THEN
2349 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
2350 FND_MESSAGE.Set_Token('COLUMN', 'ACCESS_ID', FALSE);
2351 FND_MSG_PUB.ADD;
2352 END IF;
2353 end if;
2354
2355 open get_access_info_csr(p_sales_team_rec.access_id);
2356 fetch get_access_info_csr into l_val;
2357
2358 if (get_access_info_csr%NOTFOUND)
2359 then
2360 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2361 THEN
2362 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
2363 FND_MESSAGE.Set_Token('COLUMN', 'ACCESS_ID', FALSE);
2364 fnd_message.set_token('VALUE', p_sales_team_rec.access_id, FALSE);
2365 FND_MSG_PUB.ADD;
2366 END IF;
2367 close get_access_info_csr;
2368 raise FND_API.G_EXC_ERROR;
2369 End if;
2370
2371 if p_check_access_flag = 'Y'
2372 then
2373 IF (p_sales_team_rec.lead_id is NULL or p_sales_team_rec.lead_id = FND_API.G_MISS_NUM)
2374 and (p_sales_team_rec.sales_lead_id is NULL or p_sales_team_rec.sales_lead_id = FND_API.G_MISS_NUM)
2375 THEN
2376 AS_ACCESS_PUB.has_updateCustomerAccess
2377 ( p_api_version_number => 2.0
2378 ,p_init_msg_list => p_init_msg_list
2379 ,p_validation_level => p_validation_level
2380 ,p_access_profile_rec => p_access_profile_rec
2381 ,p_admin_flag => p_admin_flag
2382 ,p_admin_group_id => p_admin_group_id
2383 ,p_person_id => l_identity_sales_member_rec.employee_person_id
2384 ,p_customer_id => p_sales_team_rec.customer_id
2385 ,p_check_access_flag => 'Y'
2386 ,p_identity_salesforce_id => p_identity_salesforce_id
2387 ,p_partner_cont_party_id => NULL
2388 ,x_return_status => l_return_status
2389 ,x_msg_count => x_msg_count
2390 ,x_msg_data => x_msg_data
2391 ,x_update_access_flag => l_update_access_flag
2392 );
2393
2394 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2396 END IF;
2397
2398 IF (l_update_access_flag <> 'Y') THEN
2399 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2400 FND_MESSAGE.Set_Name('AS', 'API_NO_DELETE_PRIVILEGE');
2401 FND_MSG_PUB.ADD;
2402 END IF;
2403 RAISE FND_API.G_EXC_ERROR;
2404 END IF;
2405 ELSIF (p_sales_team_rec.lead_id is not NULL and p_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
2406 then
2407
2408 AS_ACCESS_PUB.has_updateOpportunityAccess
2409 ( p_api_version_number => 2.0
2410 ,p_init_msg_list => p_init_msg_list
2411 ,p_validation_level => p_validation_level
2412 ,p_access_profile_rec => p_access_profile_rec
2413 ,p_admin_flag => p_admin_flag
2414 ,p_admin_group_id => p_admin_group_id
2415 ,p_person_id => l_identity_sales_member_rec.employee_person_id
2416 ,p_opportunity_id => p_sales_team_rec.lead_id
2417 ,p_check_access_flag => 'Y'
2418 ,p_identity_salesforce_id => p_identity_salesforce_id
2419 ,p_partner_cont_party_id => Null
2420 ,x_return_status => l_return_status
2421 ,x_msg_count => x_msg_count
2422 ,x_msg_data => x_msg_data
2423 ,x_update_access_flag => l_update_access_flag
2424 );
2425
2426 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2427 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2428 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOpportunityAccess fail');
2429 END IF;
2430 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2431 END IF;
2432
2433 IF (l_update_access_flag <> 'Y') THEN
2434 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2435 FND_MESSAGE.Set_Name('AS', 'API_NO_DELETE_PRIVILEGE');
2436 FND_MSG_PUB.ADD;
2437 END IF;
2438 RAISE FND_API.G_EXC_ERROR;
2439 END IF;
2440
2441 ELSIF (p_sales_team_rec.sales_lead_id is not NULL and p_sales_team_rec.sales_lead_id <>FND_API.G_MISS_NUM)
2442 then
2443 has_updateLeadAccess
2444 ( p_api_version_number => 2.0
2445 ,p_init_msg_list => p_init_msg_list
2446 ,p_validation_level => p_validation_level
2447 ,p_access_profile_rec => p_access_profile_rec
2448 ,p_admin_flag => p_admin_flag
2449 ,p_admin_group_id => p_admin_group_id
2450 ,p_person_id =>l_identity_sales_member_rec.employee_person_id
2451 ,p_sales_lead_id => p_sales_team_rec.sales_lead_id
2452 ,p_check_access_flag => 'Y'
2453 ,p_identity_salesforce_id => p_identity_salesforce_id
2454 ,p_partner_cont_party_id => Null
2455 ,x_return_status => l_return_status
2456 ,x_msg_count => x_msg_count
2457 ,x_msg_data => x_msg_data
2458 ,x_update_access_flag => l_update_access_flag
2459 );
2460
2461 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2462 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2463 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOpportunityAccess fail');
2464 END IF;
2465 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2466 END IF;
2467
2468 IF (l_update_access_flag <> 'Y') THEN
2469 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2470 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
2471 FND_MSG_PUB.ADD;
2472 END IF;
2473 RAISE FND_API.G_EXC_ERROR;
2474 END IF;
2475 --end if; -- owner check
2476 END IF;
2477 end if; -- p_check_access_flag = 'Y'
2478
2479 IF (p_sales_team_rec.lead_id is not NULL and p_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
2480 then
2481 if is_sales_lead_owner_row(p_sales_team_rec.access_id)
2482 then -- only owner can delete owner row
2483 has_oppOwnerAccess
2484 ( p_api_version_number => 2.0
2485 ,p_init_msg_list => p_init_msg_list
2486 ,p_validation_level => p_validation_level
2487 ,p_access_profile_rec => p_access_profile_rec
2488 ,p_admin_flag => p_admin_flag
2489 ,p_admin_group_id => p_admin_group_id
2490 ,p_person_id =>l_identity_sales_member_rec.employee_person_id
2491 ,p_lead_id => p_sales_team_rec.lead_id
2492 ,p_check_access_flag => 'Y'
2493 ,p_identity_salesforce_id => p_identity_salesforce_id
2494 ,p_partner_cont_party_id => Null
2495 ,x_return_status => l_return_status
2496 ,x_msg_count => x_msg_count
2497 ,x_msg_data => x_msg_data
2498 ,x_update_access_flag => l_update_access_flag
2499 );
2500
2501 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2502 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2503 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOppAccess fail');
2504 END IF;
2505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2506 END IF;
2507
2508 IF (l_update_access_flag <> 'Y') THEN
2509 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2510 FND_MESSAGE.Set_Name('AS', 'API_NO_OPP_OWNER_PRIVILEGE');
2511 FND_MSG_PUB.ADD;
2512 END IF;
2513 RAISE FND_API.G_EXC_ERROR;
2514 END IF;
2515 end if; -- is_sales_lead_owner_row(p_sales_team_rec.access_id)
2516 END IF;
2517
2518 IF (p_sales_team_rec.sales_lead_id is not NULL and p_sales_team_rec.sales_lead_id <>FND_API.G_MISS_NUM)
2519 then
2520 if is_sales_lead_owner_row(p_sales_team_rec.access_id)
2521 then -- only owner can delete owner row
2522 has_leadOwnerAccess
2523 ( p_api_version_number => 2.0
2524 ,p_init_msg_list => p_init_msg_list
2525 ,p_validation_level => p_validation_level
2526 ,p_access_profile_rec => p_access_profile_rec
2527 ,p_admin_flag => p_admin_flag
2528 ,p_admin_group_id => p_admin_group_id
2529 ,p_person_id =>l_identity_sales_member_rec.employee_person_id
2530 ,p_sales_lead_id => p_sales_team_rec.sales_lead_id
2531 ,p_check_access_flag => 'Y'
2532 ,p_identity_salesforce_id => p_identity_salesforce_id
2533 ,p_partner_cont_party_id => Null
2534 ,x_return_status => l_return_status
2535 ,x_msg_count => x_msg_count
2536 ,x_msg_data => x_msg_data
2537 ,x_update_access_flag => l_update_access_flag
2538 );
2539
2540 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2541 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2542 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateLeadAccess fail');
2543 END IF;
2544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2545 END IF;
2546
2547 IF (l_update_access_flag <> 'Y') THEN
2548 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2549 FND_MESSAGE.Set_Name('AS', 'API_NO_OWNER_PRIVILEGE');
2550 FND_MSG_PUB.ADD;
2551 END IF;
2552 RAISE FND_API.G_EXC_ERROR;
2553 END IF;
2554 end if;
2555 END IF;
2556
2557 l_is_owner := 'N';
2558
2559 delete from as_accesses
2560 where access_id = p_sales_team_rec.access_id;
2561 -- the call of AS_OPP_OWNER_PVT.ASSIGN_OPPOWNER removed since OTS UI is validating before deleting
2562 -- somebody has to be an owner of the opportunity
2563
2564
2565 x_return_status := l_return_status;
2566
2567 --
2568 -- End of API body.
2569 --
2570
2571 -- Standard check of p_commit.
2572 IF FND_API.To_Boolean ( p_commit )
2573 THEN
2574 COMMIT WORK;
2575 END IF;
2576
2577 -- Standard call to get message count and if count is 1, get message info.
2578 FND_MSG_PUB.Count_And_Get
2579 ( p_count => x_msg_count,
2580 p_data => x_msg_data
2581 );
2582
2583 EXCEPTION
2584 WHEN FND_API.G_EXC_ERROR THEN
2585 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2586 P_MODULE => l_module
2587 ,P_API_NAME => L_API_NAME
2588 ,P_PKG_NAME => G_PKG_NAME
2589 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2590 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2591 ,X_MSG_COUNT => X_MSG_COUNT
2592 ,X_MSG_DATA => X_MSG_DATA
2593 ,X_RETURN_STATUS => X_RETURN_STATUS);
2594
2595 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2596 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2597 P_MODULE => l_module
2598 ,P_API_NAME => L_API_NAME
2599 ,P_PKG_NAME => G_PKG_NAME
2600 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2601 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2602 ,X_MSG_COUNT => X_MSG_COUNT
2603 ,X_MSG_DATA => X_MSG_DATA
2604 ,X_RETURN_STATUS => X_RETURN_STATUS);
2605
2606 WHEN OTHERS THEN
2607 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2608 P_MODULE => l_module
2609 ,P_API_NAME => L_API_NAME
2610 ,P_PKG_NAME => G_PKG_NAME
2611 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
2612 ,P_SQLCODE => SQLCODE
2613 ,P_SQLERRM => SQLERRM
2614 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2615 ,X_MSG_COUNT => X_MSG_COUNT
2616 ,X_MSG_DATA => X_MSG_DATA
2617 ,X_RETURN_STATUS => X_RETURN_STATUS);
2618
2619
2620 end Delete_SalesTeam;
2621
2622 PROCEDURE Update_SalesTeam
2623 ( p_api_version_number IN NUMBER,
2624 p_init_msg_list IN VARCHAR2
2625 := FND_API.G_FALSE,
2626 p_commit IN VARCHAR2
2627 := FND_API.G_FALSE,
2628 p_validation_level IN NUMBER
2629 := FND_API.G_VALID_LEVEL_FULL,
2630 p_access_profile_rec IN AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
2631 p_check_access_flag IN VARCHAR2,
2632 p_admin_flag IN VARCHAR2,
2633 p_admin_group_id IN NUMBER,
2634 p_identity_salesforce_id IN NUMBER,
2635 p_sales_team_rec IN SALES_TEAM_REC_TYPE,
2636 x_return_status OUT NOCOPY VARCHAR2,
2637 x_msg_count OUT NOCOPY NUMBER,
2638 x_msg_data OUT NOCOPY VARCHAR2,
2639 x_access_id OUT NOCOPY NUMBER
2640 ) is
2641
2642 l_api_name CONSTANT VARCHAR2(30) := 'Update_SalesTeam';
2643 l_api_version_number CONSTANT NUMBER := 2.0;
2644 l_rowid ROWID;
2645 l_member_access VARCHAR2(1);
2646 l_member_role VARCHAR2(1);
2647 l_return_status VARCHAR2(1);
2648 l_last_update_date DATE;
2649 l_sales_team_rec SALES_TEAM_REC_TYPE;
2650 l_internal_update_access number;
2651 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
2652 l_update_access_flag varchar2(1);
2653 l_owner_flag varchar2(1);
2654 l_salesforce_role_code varchar2(30);
2655
2656 cursor get_salesTeam_info_csr is
2657 select rowid, last_update_date
2658 from as_accesses
2659 where access_id = p_sales_team_rec.access_id
2660 for update of access_id nowait;
2661
2662 cursor get_owner_flag (p_access_id number) is
2663 select owner_flag
2664 from as_accesses_all
2665 where access_id = p_access_id;
2666
2667 cursor get_salesforce_role_code (p_access_id number) is
2668 select salesforce_role_code
2669 from as_accesses_all
2670 where access_id = p_access_id;
2671
2672 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2673 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.Update_SalesTeam';
2674
2675 begin
2676 -- Standard Start of API savepoint
2677 SAVEPOINT UPDATE_SALESTEAM_PVT;
2678
2679 -- Standard call to check for call compatibility.
2680 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2681 p_api_version_number,
2682 l_api_name,
2683 G_PKG_NAME)
2684 THEN
2685 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2686 END IF;
2687
2688
2689 -- Initialize message list if p_init_msg_list is set to TRUE.
2690 IF FND_API.to_Boolean( p_init_msg_list )
2691 THEN
2692 FND_MSG_PUB.initialize;
2693 END IF;
2694
2695 -- Initialize API return status to success
2696 x_return_status := FND_API.G_RET_STS_SUCCESS;
2697 l_return_status := FND_API.G_RET_STS_SUCCESS;
2698 --
2699 -- API body
2700 --
2701
2702 -- ******************************************************************
2703 -- Validate Environment
2704 -- ******************************************************************
2705 IF FND_GLOBAL.User_Id IS NULL
2706 THEN
2707 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2708 THEN
2709 FND_MESSAGE.Set_Name('AS', 'UT_CANNOT_GET_PROFILE_VALUE');
2710 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
2711 FND_MSG_PUB.ADD;
2712 END IF;
2713 RAISE FND_API.G_EXC_ERROR;
2714 END IF;
2715
2716 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
2717 then
2718
2719 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
2720 p_api_version_number => 2.0
2721 ,p_salesforce_id => p_identity_salesforce_id
2722 , p_admin_group_id => p_admin_group_id
2723 ,x_return_status => l_return_status
2724 ,x_msg_count => x_msg_count
2725 ,x_msg_data => x_msg_data
2726 ,x_sales_member_rec => l_identity_sales_member_rec);
2727
2728 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2729 RAISE FND_API.G_EXC_ERROR;
2730 END IF;
2731 end if;
2732
2733
2734 -- ******************************************************************
2735
2736 l_sales_team_rec := p_sales_team_rec;
2737
2738 if (l_sales_team_rec.access_id is NULL)
2739 then
2740 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2741 THEN
2742 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
2743 FND_MESSAGE.Set_Token('COLUMN', 'ACCESS_ID', FALSE);
2744 FND_MSG_PUB.ADD;
2745 END IF;
2746 end if;
2747
2748
2749 IF ( P_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM)
2750 THEN
2751 Validate_SalesTeamItems(
2752 p_api_version_number => 2.0,
2753 p_init_msg_list => FND_API.G_FALSE,
2754 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2755 p_sales_team_rec => l_sales_team_rec,
2756 x_return_status => x_return_status,
2757 x_msg_count => x_msg_count,
2758 x_msg_data => x_msg_data);
2759
2760 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2761 THEN
2762 RAISE FND_API.G_EXC_ERROR;
2763 END IF;
2764 end if;
2765
2766 if p_check_access_flag = 'Y'
2767 then
2768 IF (p_sales_team_rec.lead_id is NULL or p_sales_team_rec.lead_id = FND_API.G_MISS_NUM)
2769 and (p_sales_team_rec.sales_lead_id is NULL or p_sales_team_rec.sales_lead_id = FND_API.G_MISS_NUM)
2770 THEN
2771 AS_ACCESS_PUB.has_updateCustomerAccess
2772 ( p_api_version_number => 2.0
2773 ,p_init_msg_list => p_init_msg_list
2774 ,p_validation_level => p_validation_level
2775 ,p_access_profile_rec => p_access_profile_rec
2776 ,p_admin_flag => p_admin_flag
2777 ,p_admin_group_id => p_admin_group_id
2778 ,p_person_id => l_identity_sales_member_rec.employee_person_id
2779 ,p_customer_id => p_sales_team_rec.customer_id
2780 ,p_check_access_flag => 'Y'
2781 ,p_identity_salesforce_id => p_identity_salesforce_id
2782 ,p_partner_cont_party_id => NULL
2783 ,x_return_status => l_return_status
2784 ,x_msg_count => x_msg_count
2785 ,x_msg_data => x_msg_data
2786 ,x_update_access_flag => l_update_access_flag
2787 );
2788
2789 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2790 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2791 END IF;
2792
2793 IF (l_update_access_flag <> 'Y') THEN
2794 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2795 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
2796 FND_MSG_PUB.ADD;
2797 END IF;
2798 RAISE FND_API.G_EXC_ERROR;
2799 END IF;
2800 ELSIF (p_sales_team_rec.lead_id is not NULL and p_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
2801 then
2802 AS_ACCESS_PUB.has_updateOpportunityAccess
2803 ( p_api_version_number => 2.0
2804 ,p_init_msg_list => p_init_msg_list
2805 ,p_validation_level => p_validation_level
2806 ,p_access_profile_rec => p_access_profile_rec
2807 ,p_admin_flag => p_admin_flag
2808 ,p_admin_group_id => p_admin_group_id
2809 ,p_person_id => l_identity_sales_member_rec.employee_person_id
2810 ,p_opportunity_id => p_sales_team_rec.lead_id
2811 ,p_check_access_flag => 'Y'
2812 ,p_identity_salesforce_id => p_identity_salesforce_id
2813 ,p_partner_cont_party_id => Null
2814 ,x_return_status => l_return_status
2815 ,x_msg_count => x_msg_count
2816 ,x_msg_data => x_msg_data
2817 ,x_update_access_flag => l_update_access_flag
2818 );
2819
2820 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2821 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2822 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOpportunityAccess fail');
2823 END IF;
2824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2825 END IF;
2826
2827 IF (l_update_access_flag <> 'Y') THEN
2828 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2829 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
2830 FND_MSG_PUB.ADD;
2831 END IF;
2832 RAISE FND_API.G_EXC_ERROR;
2833 END IF;
2834 ELSIF (p_sales_team_rec.sales_lead_id is not NULL and p_sales_team_rec.sales_lead_id <>FND_API.G_MISS_NUM)
2835 then
2836 AS_ACCESS_PUB.has_updateLeadAccess
2837 ( p_api_version_number => 2.0
2838 ,p_init_msg_list => p_init_msg_list
2839 ,p_validation_level => p_validation_level
2840 ,p_access_profile_rec => p_access_profile_rec
2841 ,p_admin_flag => p_admin_flag
2842 ,p_admin_group_id => p_admin_group_id
2843 ,p_person_id =>l_identity_sales_member_rec.employee_person_id
2844 ,p_sales_lead_id => p_sales_team_rec.sales_lead_id
2845 ,p_check_access_flag => 'Y'
2846 ,p_identity_salesforce_id => p_identity_salesforce_id
2847 ,p_partner_cont_party_id => Null
2848 ,x_return_status => l_return_status
2849 ,x_msg_count => x_msg_count
2850 ,x_msg_data => x_msg_data
2851 ,x_update_access_flag => l_update_access_flag
2852 );
2853
2854 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2855 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2856 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateLeadAccess fail');
2857 END IF;
2858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2859 END IF;
2860
2861 IF (l_update_access_flag <> 'Y') THEN
2862 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2863 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
2864 FND_MSG_PUB.ADD;
2865 END IF;
2866 RAISE FND_API.G_EXC_ERROR;
2867 END IF;
2868 END IF;
2869 end if; -- p_check_access_flag = 'Y'
2870
2871 IF (p_sales_team_rec.lead_id is not NULL and p_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
2872 then
2873 open get_owner_flag (l_sales_team_rec.access_id);
2874 fetch get_owner_flag into l_owner_flag;
2875 close get_owner_flag;
2876
2877 IF l_debug THEN
2878 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2879 'l_owner_flag: ' || nvl(l_owner_flag, 'N') || '');
2880
2881 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2882 'l_sales_team_rec.owner_flag: ' || nvl(l_sales_team_rec.owner_flag, 'N') || '');
2883 END IF;
2884
2885
2886 if( l_sales_team_rec.owner_flag <>FND_API.G_MISS_CHAR and (nvl(l_owner_flag, 'N') <> nvl(l_sales_team_rec.owner_flag, 'N')))
2887 then
2888 has_oppOwnerAccess
2889 ( p_api_version_number => 2.0
2890 ,p_init_msg_list => p_init_msg_list
2891 ,p_validation_level => p_validation_level
2892 ,p_access_profile_rec => p_access_profile_rec
2893 ,p_admin_flag => p_admin_flag
2894 ,p_admin_group_id => p_admin_group_id
2895 ,p_person_id =>l_identity_sales_member_rec.employee_person_id
2896 ,p_lead_id => p_sales_team_rec.lead_id
2897 ,p_check_access_flag => 'Y'
2898 ,p_identity_salesforce_id => p_identity_salesforce_id
2899 ,p_partner_cont_party_id => Null
2900 ,x_return_status => l_return_status
2901 ,x_msg_count => x_msg_count
2902 ,x_msg_data => x_msg_data
2903 ,x_update_access_flag => l_update_access_flag );
2904 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2905 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2906 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOppAccess fail');
2907 END IF;
2908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2909 END IF;
2910
2911 IF (l_update_access_flag <> 'Y') THEN
2912 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2913 FND_MESSAGE.Set_Name('AS', 'API_NO_OPP_OWNER_PRIVILEGE');
2914 FND_MSG_PUB.ADD;
2915 END IF;
2916 RAISE FND_API.G_EXC_ERROR;
2917 END IF;
2918
2919 end if; --(nvl(l_owner_flag, 'N') <> nvl(l_sales_team_rec.owner_flag, 'N'))
2920
2921 END IF;
2922
2923
2924 open get_salesTeam_info_csr;
2925 fetch get_salesTeam_info_csr into l_rowid, l_last_update_date;
2926 close get_salesTeam_info_csr;
2927
2928 if (l_sales_team_rec.last_update_date is NULL
2929 or l_sales_team_rec.last_update_date = FND_API.G_MISS_DATE)
2930 then
2931 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2932 THEN
2933 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
2934 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
2935 FND_MSG_PUB.ADD;
2936 END IF;
2937 RAISE FND_API.G_EXC_ERROR;
2938 end if;
2939 if (l_last_update_date <> l_sales_team_rec.last_update_date)
2940 then
2941 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2942 THEN
2943 FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
2944 FND_MESSAGE.Set_Token('INFO', 'AS_ACCESSES', FALSE);
2945 FND_MSG_PUB.ADD;
2946 END IF;
2947 RAISE FND_API.G_EXC_ERROR;
2948 end if;
2949
2950 --if (l_sales_team_rec.lead_id is NOT NULL)
2951 -- and (l_sales_team_rec.lead_id <> FND_API.G_MISS_NUM) then
2952 if l_sales_team_rec.team_leader_flag = 'Y'
2953 then
2954 l_internal_update_access := 1;
2955 else l_internal_update_access := 0;
2956 end if;
2957 --else l_internal_update_access := 1;
2958 --end if;
2959
2960 --if (l_sales_team_rec.sales_lead_id is NOT NULL
2961 -- and l_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
2962 -- and l_sales_team_rec.owner_flag = 'Y'
2963 -- and p_check_access_flag = 'Y'
2964 -- and fnd_profile.value('AS_ALLOW_CHANGE_LEAD_OWNER')<>'Y'
2965
2966 if (l_sales_team_rec.sales_lead_id is NOT NULL
2967 and l_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
2968 and l_sales_team_rec.owner_flag = 'Y'
2969 and fnd_profile.value('AS_ALLOW_CHANGE_LEAD_OWNER')<>'Y'
2970
2971 then
2972 open get_owner_flag (l_sales_team_rec.access_id);
2973 fetch get_owner_flag into l_owner_flag;
2974 close get_owner_flag;
2975
2976 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2977 'l_owner_flag: ' || nvl(l_owner_flag, 'N') || '');
2978
2979 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2980 'l_sales_team_rec.owner_flag: ' || nvl(l_sales_team_rec.owner_flag, 'N') || '');
2981
2982 if(nvl(l_owner_flag, 'N') <> nvl(l_sales_team_rec.owner_flag, 'N'))
2983 then
2984
2985 has_leadOwnerAccess
2986 ( p_api_version_number => 2.0
2987 ,p_init_msg_list => p_init_msg_list
2988 ,p_validation_level => p_validation_level
2989 ,p_access_profile_rec => p_access_profile_rec
2990 ,p_admin_flag => p_admin_flag
2991 ,p_admin_group_id => p_admin_group_id
2992 ,p_person_id =>l_identity_sales_member_rec.employee_person_id
2993 ,p_sales_lead_id => p_sales_team_rec.sales_lead_id
2994 ,p_check_access_flag => 'Y'
2995 ,p_identity_salesforce_id => p_identity_salesforce_id
2996 ,p_partner_cont_party_id => Null
2997 ,x_return_status => l_return_status
2998 ,x_msg_count => x_msg_count
2999 ,x_msg_data => x_msg_data
3000 ,x_update_access_flag => l_update_access_flag
3001 );
3002
3003 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3004 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3005 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateLeadAccess fail');
3006 END IF;
3007 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3008 END IF;
3009
3010 IF (l_update_access_flag <> 'Y') THEN
3011 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3012 FND_MESSAGE.Set_Name('AS', 'API_NO_OWNER_PRIVILEGE');
3013 FND_MSG_PUB.ADD;
3014 END IF;
3015 RAISE FND_API.G_EXC_ERROR;
3016 END IF;
3017 end if;
3018 end if;
3019
3020 open get_salesforce_role_code (l_sales_team_rec.access_id);
3021 fetch get_salesforce_role_code into l_salesforce_role_code;
3022 close get_salesforce_role_code;
3023
3024 -- Account manager
3025 if (l_sales_team_rec.salesforce_role_code = 'AM' AND l_salesforce_role_code <> l_sales_team_rec.salesforce_role_code)
3026 or ((l_sales_team_rec.salesforce_role_code <> 'AM'
3027 OR l_sales_team_rec.salesforce_role_code = '' OR l_sales_team_rec.salesforce_role_code is null )
3028 and is_account_manager(l_sales_team_rec.salesforce_id,l_sales_team_rec.customer_id))
3029
3030
3031 -- if want to update someone to be account manager
3032 -- or update 'AM' to be not 'AM'
3033 then
3034 if not is_account_manager(p_identity_salesforce_id, l_sales_team_rec.customer_id)
3035 and (nvl(fnd_profile.value('AS_CUST_ACCESS'),'F') <>'F')
3036 -- if login person is not account manager,he can't make other
3037 -- people account manager or not account manager
3038
3039 then
3040 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3041 THEN
3042 FND_MESSAGE.Set_Name('AS','API_NO_ACC_MGR_PRIVILEGE');
3043 FND_MSG_PUB.ADD;
3044 END IF;
3045 RAISE FND_API.G_EXC_ERROR;
3046 end if;
3047 end if;
3048
3049 if l_sales_team_rec.reassign_flag = 'Y'
3050 and (l_sales_team_rec.reassign_reason is null
3051 or l_sales_team_rec.reassign_reason = fnd_api.g_miss_char)
3052 then
3053 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3054 THEN
3055 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
3056 FND_MESSAGE.Set_Token('COLUMN', 'reassign_reason', FALSE);
3057 FND_MSG_PUB.ADD;
3058 END IF;
3059 raise fnd_api.g_exc_error;
3060 end if;
3061 --if l_sales_team_rec.owner_flag = 'Y' and (l_sales_team_rec.sales_lead_id is NOT NULL
3062 -- and l_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
3063
3064 --then
3065 -- unmark_owner_flag(l_sales_team_rec.sales_lead_id);
3066 --end if;
3067
3068 --if l_sales_team_rec.owner_flag = 'Y' and (l_sales_team_rec.lead_id is NOT NULL
3069 -- and l_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
3070
3071 --then
3072 -- unmark_opp_owner_flag(l_sales_team_rec.lead_id);
3073 --end if;
3074
3075
3076 -- Owner is always a team leader
3077 if l_sales_team_rec.owner_flag = 'Y'
3078 then
3079 l_sales_team_rec.team_leader_flag :='Y';
3080 end if;
3081
3082 if l_sales_team_rec.owner_flag <> 'Y' and (l_sales_team_rec.lead_id is NOT NULL and l_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
3083 then
3084 l_sales_team_rec.owner_flag := FND_API.G_MISS_CHAR;
3085 end if;
3086
3087
3088 as_accesses_pkg.update_row(
3089 X_Rowid => l_rowid,
3090 X_Access_Id => l_sales_team_rec.access_id,
3091 X_Last_Update_Date => SYSDATE,
3092 X_Last_Updated_By => FND_GLOBAL.User_Id,
3093 X_Last_Update_Login => FND_GLOBAL.Conc_Login_Id,
3094 X_Access_Type => 'X',
3095 X_Freeze_Flag => l_sales_team_rec.freeze_flag,
3096 X_Reassign_Flag => l_sales_team_rec.reassign_flag,
3097 X_Team_Leader_Flag => l_sales_team_rec.team_leader_flag,
3098 X_Person_Id => l_sales_team_rec.person_id,
3099 X_Customer_Id => l_sales_team_rec.customer_id,
3100 X_Address_Id => l_sales_team_rec.address_id,
3101 X_Salesforce_id => l_sales_team_rec.salesforce_id,
3102 X_Created_Person_Id => l_sales_team_rec.created_person_id,
3103 X_Partner_Customer_id => l_sales_team_rec.partner_customer_id,
3104 X_Partner_Address_id => l_sales_team_rec.partner_address_id,
3105 X_Lead_Id => l_sales_team_rec.lead_id,
3106 X_Freeze_Date => l_sales_team_rec.freeze_date,
3107 X_Reassign_Reason => l_sales_team_rec.reassign_reason,
3108 X_Reassign_request_date => l_sales_team_rec.reassign_request_date,
3109 X_Reassign_requested_person_id => l_sales_team_rec.reassign_requested_person_id,
3110 X_Attribute_Category => l_sales_team_rec.attribute_category,
3111 X_Attribute1 => l_sales_team_rec.attribute1,
3112 X_Attribute2 => l_sales_team_rec.attribute2,
3113 X_Attribute3 => l_sales_team_rec.attribute3,
3114 X_Attribute4 => l_sales_team_rec.attribute4,
3115 X_Attribute5 => l_sales_team_rec.attribute5,
3116 X_Attribute6 => l_sales_team_rec.attribute6,
3117 X_Attribute7 => l_sales_team_rec.attribute7,
3118 X_Attribute8 => l_sales_team_rec.attribute8,
3119 X_Attribute9 => l_sales_team_rec.attribute9,
3120 X_Attribute10 => l_sales_team_rec.attribute10,
3121 X_Attribute11 => l_sales_team_rec.attribute11,
3122 X_Attribute12 => l_sales_team_rec.attribute12,
3123 X_Attribute13 => l_sales_team_rec.attribute13,
3124 X_Attribute14 => l_sales_team_rec.attribute14,
3125 X_Attribute15 => l_sales_team_rec.attribute15,
3126 X_Sales_group_id => l_sales_team_rec.sales_group_id,
3127 X_Sales_lead_id => l_sales_team_rec.sales_lead_id,
3128 X_Internal_update_access => l_internal_update_access,
3129 X_Partner_Cont_Party_Id =>l_sales_team_rec.partner_cont_party_id,
3130 X_owner_flag => l_sales_team_rec.owner_flag,
3131 X_created_by_tap_flag =>l_sales_team_rec.created_by_tap_flag,
3132 X_prm_keep_flag => l_sales_team_rec.prm_keep_flag,
3133 X_Salesforce_Role_Code => l_sales_team_rec.salesforce_role_code,
3134 X_Salesforce_Relationship_Code => l_sales_team_rec.salesforce_relationship_code,
3135 X_contributor_flag =>l_sales_team_rec.contributor_flag); -- Added for ASNB
3136
3137 x_access_id := l_sales_team_rec.access_id;
3138
3139 x_return_status := l_return_status;
3140
3141 if is_sales_lead_owner_row(l_sales_team_rec.access_id)
3142 then
3143 update as_leads_all set object_version_number = nvl(object_version_number,0) + 1, owner_salesforce_id = l_sales_team_rec.salesforce_id,
3144 --owner_sales_group_id = l_sales_team_rec.sales_group_id
3145 owner_sales_group_id = (select sales_group_id from as_accesses_all where access_id = l_sales_team_rec.access_id)
3146 where lead_id = l_sales_team_rec.lead_id;
3147 end if;
3148
3149 if is_sales_lead_owner_row(x_access_id)
3150 and (l_sales_team_rec.sales_lead_id is NOT NULL
3151 and l_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
3152
3153 then
3154 unmark_owner_flag(l_sales_team_rec.sales_lead_id, x_access_id);
3155 end if;
3156
3157 if is_sales_lead_owner_row(x_access_id)
3158 and (l_sales_team_rec.lead_id is NOT NULL
3159 and l_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
3160 then
3161 unmark_opp_owner_flag(l_sales_team_rec.lead_id, x_access_id);
3162 end if;
3163
3164 -- Standard check of p_commit.
3165 IF FND_API.To_Boolean ( p_commit )
3166 THEN
3167 COMMIT WORK;
3168 END IF;
3169
3170 -- Standard call to get message count and if count is 1, get message info.
3171 FND_MSG_PUB.Count_And_Get
3172 ( p_count => x_msg_count,
3173 p_data => x_msg_data
3174 );
3175
3176
3177 EXCEPTION
3178
3179 WHEN DUP_VAL_ON_INDEX THEN
3180 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3181 THEN
3182 FND_MESSAGE.Set_Name('AS', 'API_DUP_SALESTEAM');
3183 FND_MSG_PUB.ADD;
3184 x_return_status := FND_API.G_RET_STS_ERROR ;
3185 END IF;
3186
3187
3188 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
3189 ROLLBACK TO UPDATE_SALESTEAM_PVT;
3190 x_return_status := FND_API.G_RET_STS_ERROR ;
3191
3192 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3193 THEN
3194 FND_MESSAGE.Set_Name('AS', 'API_CANNOT_RESERVE_RECORD');
3195 FND_MESSAGE.Set_Token('INFO', 'UPDATE_SALESTEAM', FALSE);
3196 FND_MSG_PUB.Add;
3197 END IF;
3198
3199 WHEN FND_API.G_EXC_ERROR THEN
3200 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3201 P_MODULE => l_module
3202 ,P_API_NAME => L_API_NAME
3203 ,P_PKG_NAME => G_PKG_NAME
3204 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3205 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3206 ,X_MSG_COUNT => X_MSG_COUNT
3207 ,X_MSG_DATA => X_MSG_DATA
3208 ,X_RETURN_STATUS => X_RETURN_STATUS);
3209
3210 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3211 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3212 P_MODULE => l_module
3213 ,P_API_NAME => L_API_NAME
3214 ,P_PKG_NAME => G_PKG_NAME
3215 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3216 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3217 ,X_MSG_COUNT => X_MSG_COUNT
3218 ,X_MSG_DATA => X_MSG_DATA
3219 ,X_RETURN_STATUS => X_RETURN_STATUS);
3220
3221 WHEN OTHERS THEN
3222 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3223 P_MODULE => l_module
3224 ,P_API_NAME => L_API_NAME
3225 ,P_PKG_NAME => G_PKG_NAME
3226 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
3227 ,P_SQLCODE => SQLCODE
3228 ,P_SQLERRM => SQLERRM
3229 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3230 ,X_MSG_COUNT => X_MSG_COUNT
3231 ,X_MSG_DATA => X_MSG_DATA
3232 ,X_RETURN_STATUS => X_RETURN_STATUS);
3233
3234
3235 end Update_SalesTeam;
3236
3237
3238 function is_party_person (p_customer_id in number)
3239 RETURN BOOLEAN IS
3240 cursor get_party_person_csr is
3241 select 'x'
3242 from hz_parties
3243 where party_id = p_customer_id
3244 and party_type = 'PERSON';
3245 l_tmp varchar2(1);
3246 begin
3247 open get_party_person_csr;
3248 fetch get_party_person_csr into l_tmp;
3249 if get_party_person_csr%FOUND
3250 then
3251 close get_party_person_csr;
3252 return true;
3253 else
3254 close get_party_person_csr;
3255 return false;
3256 end if;
3257 end is_party_person;
3258
3259 -- private procedure which is called in has_viewCustomerAccess
3260 -- person's access will be based on the access privilege of related organization
3261 -- this procedure only handle the case of as_cust_profile = 'T'. Other cases are
3262 -- handled in has_viewCustomerAccess
3263 procedure has_viewPersonAccess
3264 ( p_admin_flag IN VARCHAR2
3265 ,p_admin_group_id IN NUMBER
3266 ,p_person_id IN NUMBER
3267 ,p_customer_id IN NUMBER
3268 ,p_identity_salesforce_id IN NUMBER
3269 ,x_view_access_flag OUT NOCOPY VARCHAR2
3270 ) is
3271
3272 l_tmp varchar2(1);
3273 l_person_id number;
3274
3275 cursor resource_access_csr is
3276
3277 select 'X'
3278 from as_accesses_all a, hz_parties p,hz_relationships rel
3279 where a.customer_id = rel.object_id
3280 and rel.object_id = p.party_id
3281 and p.party_type in ('ORGANIZATION','PERSON')
3282 and rel.subject_id = p_customer_id
3283 and a.salesforce_id = p_identity_salesforce_id
3284 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3285 AND rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3286 and rownum = 1;
3287
3288
3289 cursor manager_access_csr(p_resource_id number) is
3290
3291 select 'X'
3292 from as_accesses_all a, as_rpt_managers_v rm,
3293 hz_parties p,hz_relationships rel
3294 where a.customer_id = rel.object_id
3295 and rel.object_id = p.party_id
3296 and p.party_type in ('ORGANIZATION','PERSON')
3297 and a.salesforce_id = rm.resource_id
3298 and rel.subject_id = p_customer_id
3299 and rm.parent_resource_id = p_resource_id
3300 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3301 AND rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3302 and rownum = 1;
3303
3304 cursor admin_access_csr is
3305
3306 select 'X'
3307 from hz_parties p, hz_relationships rel
3308 where rel.object_id = p.party_id
3309 and p.party_type in ('ORGANIZATION','PERSON')
3310 and rel.subject_id = p_customer_id
3311 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3312 and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3313 and exists (select 1 from as_accesses_all a ,as_rpt_admins_v rm
3314 where a.salesforce_id = rm.salesforce_id
3315 and a.customer_id = rel.object_id
3316 and rm.parent_sales_group_id = p_admin_group_id)
3317 and rownum = 1;
3318
3319 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3320 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_viewPersonAccess';
3321
3322 begin
3323
3324
3325 -- Debug Message
3326 IF l_debug THEN
3327 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3328 'has_viewPersonAccess: start ');
3329
3330 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3331 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3332 END IF;
3333
3334 l_person_id := p_person_id;
3335 x_view_access_flag := 'N';
3336 open resource_access_csr;
3337 fetch resource_access_csr into l_tmp;
3338 if (resource_access_csr%FOUND)
3339 -- access record exists for the login user itself
3340 then
3341 x_view_access_flag := 'Y';
3342 elsif nvl(p_admin_flag,'N') <> 'Y'
3343 then
3344 open manager_access_csr(p_identity_salesforce_id);
3345 fetch manager_access_csr into l_tmp;
3346 if (manager_access_csr%FOUND)
3347 then
3348 x_view_access_flag := 'Y';
3349 end if; -- mgr
3350 close manager_access_csr;
3351 else
3352 open admin_access_csr;
3353 fetch admin_access_csr into l_tmp;
3354 if admin_access_csr%FOUND
3355 then
3356 x_view_access_flag := 'Y';
3357 end if; -- admin
3358 close admin_access_csr;
3359 end if;
3360 close resource_access_csr;
3361
3362 IF l_debug THEN
3363 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3364 'x_view_access_flag: ' || x_view_access_flag);
3365 -- Debug Message
3366
3367 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3368 'has_viewPersonAccess: end ');
3369
3370
3371 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
3372 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3373 END IF;
3374
3375 end has_viewPersonAccess;
3376
3377 -- private procedure which is called in has_updateCustomerAccess
3378 -- person's access will be based on the access privilege of related organization
3379 -- this procedure only handle the case of as_cust_profile in ('P', 'T'). Other cases are
3380 -- handled in has_updateCustomerAccess
3381
3382 procedure has_updatePersonAccess
3383 ( p_access_profile_rec IN as_access_pub.access_profile_rec_type
3384 ,p_admin_flag IN VARCHAR2
3385 ,p_admin_group_id IN NUMBER
3386 ,p_person_id IN NUMBER
3387 ,p_customer_id IN NUMBER
3388 ,p_identity_salesforce_id IN NUMBER
3389 ,x_update_access_flag OUT NOCOPY VARCHAR2
3390 )is
3391
3392 l_tmp varchar2(1);
3393 l_person_id number;
3394
3395 cursor resource_access_csr is
3396
3397 select 'X'
3398 from as_accesses_all a, hz_parties p,hz_relationships rel
3399 where a.customer_id = rel.object_id
3400 and rel.object_id = p.party_id
3401 and p.party_type in ('ORGANIZATION','PERSON')
3402 and rel.subject_id = p_customer_id
3403 and a.salesforce_id = p_identity_salesforce_id
3404 and a.lead_id is null
3405 and a.sales_lead_id is null
3406 and a.team_leader_flag = 'Y'
3407 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3408 AND rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3409 and rownum = 1;
3410
3411 cursor manager_access_csr(p_resource_id number) is
3412 select 'X'
3413 from as_accesses_all a,
3414 hz_parties p, hz_relationships rel
3415 where a.customer_id = rel.object_id
3416 and rel.object_id = p.party_id
3417 and p.party_type in ('ORGANIZATION','PERSON')
3418 and rel.subject_id = p_customer_id
3419 and a.lead_id is null
3420 and a.sales_lead_id is null
3421 and rel.subject_table_name = 'HZ_PARTIES'
3422 and rel.object_table_name = 'HZ_PARTIES'
3423 and (EXISTS (select 'X'
3424 from as_rpt_managers_v rm
3425 where a.salesforce_id = rm.resource_id
3426 and rm.parent_resource_id = p_resource_id
3427 and ((rm.parent_resource_id = rm.resource_id
3428 and a.team_leader_flag = 'Y')
3429 or (rm.parent_resource_id <> rm.resource_id))));
3430
3431 cursor mgr_i_access_csr(p_resource_id number) is
3432 select 'X'
3433 from as_accesses_all a, hz_parties p, hz_relationships rel
3434 where a.customer_id = rel.object_id
3435 and rel.object_id = p.party_id
3436 and p.party_type in ('ORGANIZATION','PERSON')
3437 and rel.subject_id = p_customer_id
3438 and a.lead_id is null
3439 and a.sales_lead_id is null
3440 and a.team_leader_flag = 'Y'
3441 and (EXISTS (select 'x'
3442 from as_rpt_managers_v rm
3443 where a.salesforce_id = rm.resource_id
3444 and rm.parent_resource_id = p_resource_id));
3445
3446 cursor admin_access_csr is
3447 select 'X'
3448 from as_accesses_all a, hz_parties p, hz_relationships rel
3449 where a.customer_id = rel.object_id
3450 and rel.object_id = p.party_id
3451 and p.party_type in ('ORGANIZATION','PERSON')
3452 and rel.subject_id = p_customer_id
3453 and a.lead_id is null
3454 and a.sales_lead_id is null
3455 and rel.object_table_name = 'HZ_PARTIES'
3456 and rel.subject_table_name = 'HZ_PARTIES'
3457 and EXISTS (select 'x'
3458 from as_rpt_admins_v rm
3459 where a.salesforce_id = rm.salesforce_id
3460 and rm.parent_sales_group_id = p_admin_group_id
3461 and ((rm.salesforce_id = p_identity_salesforce_id
3462 and a.team_leader_flag = 'Y')
3463 or (rm.salesforce_id <> p_identity_salesforce_id)));
3464
3465 cursor admin_i_access_csr is
3466 select 'x'
3467 from as_accesses_all a, hz_parties p, hz_relationships rel
3468 where a.customer_id = rel.object_id
3469 and rel.object_id = p.party_id
3470 and p.party_type in ('ORGANIZATION', 'PERSON')
3471 and rel.subject_id = p_customer_id
3472 and a.lead_id is null
3473 and a.sales_lead_id is null
3474 and a.team_leader_flag = 'Y'
3475 and rel.object_table_name = 'HZ_PARTIES'
3476 and rel.subject_table_name = 'HZ_PARTIES'
3477 and EXISTS (select 'x'
3478 from as_rpt_admins_v rm
3479 where a.salesforce_id = rm.salesforce_id
3480 and rm.parent_sales_group_id = p_admin_group_id);
3481
3482 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3483 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_updatePersonAccess';
3484 begin
3485
3486 -- Debug Message
3487 IF l_debug THEN
3488 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updatePersonAccess start');
3489
3490 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3491 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3492 END IF;
3493
3494 l_person_id := p_person_id;
3495 -- Initialize access flag to 'N'
3496 x_update_access_flag := 'N';
3497
3498 open resource_access_csr;
3499 fetch resource_access_csr into l_tmp;
3500 if (resource_access_csr%FOUND)
3501 -- access record exists for the login user itself
3502 then
3503 x_update_access_flag := 'Y';
3504 else -- access record doesn't exist for the login user
3505
3506 if nvl(p_admin_flag,'N') <> 'Y' -- mgr
3507 then if p_access_profile_rec.mgr_update_profile_value = 'U'
3508 then
3509 open manager_access_csr(p_identity_salesforce_id);
3510 fetch manager_access_csr into l_tmp;
3511 if (manager_access_csr%FOUND)
3512 then
3513 x_update_access_flag := 'Y';
3514 end if;
3515 close manager_access_csr;
3516 elsif p_access_profile_rec.mgr_update_profile_value = 'I'
3517 then
3518 open mgr_i_access_csr(p_identity_salesforce_id);
3519 fetch mgr_i_access_csr into l_tmp;
3520 if(mgr_i_access_csr%FOUND)
3521 then
3522 x_update_access_flag := 'Y';
3523 end if;
3524 close mgr_i_access_csr;
3525 end if; -- mgr
3526 else if p_access_profile_rec.admin_update_profile_value = 'U'
3527 then
3528 open admin_access_csr;
3529 fetch admin_access_csr into l_tmp;
3530 if (admin_access_csr%FOUND)
3531 then
3532 x_update_access_flag := 'Y';
3533 end if;
3534 close admin_access_csr;
3535 elsif p_access_profile_rec.admin_update_profile_value = 'I'
3536 then
3537 open admin_i_access_csr;
3538 fetch admin_i_access_csr into l_tmp;
3539 if(admin_i_access_csr%FOUND)
3540 then
3541 x_update_access_flag := 'Y';
3542 end if;
3543 close admin_i_access_csr;
3544 end if; -- admin
3545 end if; --nvl(p_admin_flag,'N') <> 'Y' -- mgr
3546 end if;
3547 close resource_access_csr;
3548
3549 -- Debug Message
3550 IF l_debug THEN
3551 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3552 'has_updatePersonAccess end');
3553
3554 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
3555 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3556 END IF;
3557
3558 end has_updatePersonAccess;
3559
3560
3561 procedure has_viewCustomerAccess
3562 ( p_api_version_number IN NUMBER
3563 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3564 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3565 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
3566 ,p_admin_flag IN VARCHAR2
3567 ,p_admin_group_id IN NUMBER
3568 ,p_person_id IN NUMBER
3569 ,p_customer_id IN NUMBER
3570 ,p_check_access_flag IN VARCHAR2
3571 ,p_identity_salesforce_id IN NUMBER
3572 ,p_partner_cont_party_id IN NUMBER
3573 ,x_return_status OUT NOCOPY VARCHAR2
3574 ,x_msg_count OUT NOCOPY NUMBER
3575 ,x_msg_data OUT NOCOPY VARCHAR2
3576 ,x_view_access_flag OUT NOCOPY VARCHAR2
3577 ) is
3578 l_api_name CONSTANT VARCHAR2(30) := 'has_viewCustomerAccess';
3579 l_api_version_number CONSTANT NUMBER := 2.0;
3580 l_tmp varchar2(1);
3581 l_person_id number;
3582 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
3583
3584 cursor resource_access_csr is
3585
3586 select 'X'
3587 from as_accesses_all
3588 where customer_id = p_customer_id
3589 and salesforce_id = p_identity_salesforce_id;
3590
3591
3592 cursor manager_access_csr(p_resource_id number) is
3593 select 'X'
3594 from as_accesses_all a
3595 where customer_id = p_customer_id
3596 and (EXISTS (select 'X'
3597 from as_rpt_managers_v rm
3598 where a.salesforce_id = rm.resource_id
3599 and rm.parent_resource_id = p_resource_id));
3600
3601 cursor admin_access_csr is
3602 select 'X'
3603 from as_accesses_all a
3604 where customer_id = p_customer_id
3605 and EXISTS (select 'x'
3606 from as_rpt_admins_v rm
3607 where a.salesforce_id = rm.salesforce_id
3608 and rm.parent_sales_group_id = p_admin_group_id);
3609 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3610 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_viewCustomerAccess';
3611
3612 begin
3613
3614 -- Standard call to check for call compatibility.
3615 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3616 p_api_version_number,
3617 l_api_name,
3618 G_PKG_NAME)
3619 THEN
3620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3621 END IF;
3622
3623 -- Initialize message list if p_init_msg_list is set to TRUE.
3624 IF FND_API.to_Boolean( p_init_msg_list )
3625 THEN
3626 FND_MSG_PUB.initialize;
3627 END IF;
3628
3629 -- Debug Message
3630 IF l_debug THEN
3631 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3632 'Private API: ' || l_api_name || 'start');
3633
3634 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3635 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3636 END IF;
3637
3638 -- Initialize API return status to SUCCESS
3639 x_return_status := FND_API.G_RET_STS_SUCCESS;
3640
3641 IF l_debug THEN
3642 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3643 'customer_id: ' || p_customer_id);
3644 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3645 'ident salesforce_id: ' || p_identity_salesforce_id);
3646 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3647 'admin_group_id: ' || p_admin_group_id);
3648 END IF;
3649
3650 -- Initialize access flag to 'N'
3651 x_view_access_flag := 'N';
3652
3653
3654 if p_check_access_flag = 'N'
3655 then
3656 x_view_access_flag := 'Y';
3657 else -- if p_check_access_flag = 'Y'
3658 --partner security checking. Return point
3659 if p_partner_cont_party_id is not null
3660 and p_partner_cont_party_id <> FND_API.G_MISS_NUM
3661 then
3662 open resource_access_csr;
3663 fetch resource_access_csr into l_tmp;
3664 if (resource_access_csr%FOUND)
3665 then
3666 x_view_access_flag := 'Y';
3667 close resource_access_csr;
3668 return;
3669 end if;
3670 close resource_access_csr;
3671 end if;
3672 /*
3673 if p_person_id is null or p_person_id = fnd_api.g_miss_num
3674 then
3675 get_person_id(p_identity_salesforce_id, l_person_id);
3676 else
3677 l_person_id := p_person_id;
3678 end if;
3679 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3680 'person id: ' || l_person_id);
3681 */
3682 l_access_profile_rec := p_access_profile_rec;
3683 get_accessProfileValues(l_access_profile_rec);
3684
3685 if l_access_profile_rec.cust_access_profile_value in ('F', 'P')
3686 then
3687 x_view_access_flag := 'Y';
3688 -- elsif l_access_profile_rec.lead_access_profile_value = 'T'
3689 -- and l_access_profile_rec.opp_access_profile_value = 'T'
3690 -- then
3691 -- Fix bug 1623713
3692 else
3693 if nvl(p_admin_flag,'N') <> 'Y'
3694 then
3695 open manager_access_csr(p_identity_salesforce_id);
3696 fetch manager_access_csr into l_tmp;
3697 if (manager_access_csr%FOUND)
3698 then
3699 x_view_access_flag := 'Y';
3700 end if; -- mgr
3701 close manager_access_csr;
3702 else
3703 open admin_access_csr;
3704 fetch admin_access_csr into l_tmp;
3705 if admin_access_csr%FOUND
3706 then
3707 x_view_access_flag := 'Y';
3708 end if; -- admin
3709 close admin_access_csr;
3710 end if; -- profile combination is ('T', don't care, don't care)
3711 end if; -- if l_access_profile_rec.cust_access_profile_value in ('F','P')
3712
3713 if x_view_access_flag = 'N' and is_party_person(p_customer_id)
3714 then
3715 has_viewPersonAccess(
3716 p_admin_flag => p_admin_flag
3717 ,p_admin_group_id => p_admin_group_id
3718 ,p_person_id => p_person_id
3719 ,p_customer_id => p_customer_id
3720 ,p_identity_salesforce_id => p_identity_salesforce_id
3721 ,x_view_access_flag => x_view_access_flag
3722 );
3723 end if;
3724 end if; -- if p_check_access_flag = 'N'
3725
3726 IF l_debug THEN
3727 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3728 'x_view_access_flag: ' || x_view_access_flag);
3729 -- Debug Message
3730 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3731 'Private API: ' || l_api_name || 'end');
3732
3733 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
3734 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3735 END IF;
3736
3737 FND_MSG_PUB.Count_And_Get
3738 ( p_count => x_msg_count,
3739 p_data => x_msg_data
3740 );
3741
3742 EXCEPTION
3743 WHEN FND_API.G_EXC_ERROR THEN
3744 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3745 P_MODULE => l_module
3746 ,P_API_NAME => L_API_NAME
3747 ,P_PKG_NAME => G_PKG_NAME
3748 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3749 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3750 , P_ROLLBACK_FLAG => 'N'
3751 ,X_MSG_COUNT => X_MSG_COUNT
3752 ,X_MSG_DATA => X_MSG_DATA
3753 ,X_RETURN_STATUS => X_RETURN_STATUS);
3754
3755 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3756 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3757 P_MODULE => l_module
3758 ,P_API_NAME => L_API_NAME
3759 ,P_PKG_NAME => G_PKG_NAME
3760 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3761 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3762 , P_ROLLBACK_FLAG => 'N'
3763 ,X_MSG_COUNT => X_MSG_COUNT
3764 ,X_MSG_DATA => X_MSG_DATA
3765 ,X_RETURN_STATUS => X_RETURN_STATUS);
3766
3767 WHEN OTHERS THEN
3768 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3769 P_MODULE => l_module
3770 ,P_API_NAME => L_API_NAME
3771 ,P_PKG_NAME => G_PKG_NAME
3772 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
3773 ,P_SQLCODE => SQLCODE
3774 ,P_SQLERRM => SQLERRM
3775 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3776 , P_ROLLBACK_FLAG => 'N'
3777 ,X_MSG_COUNT => X_MSG_COUNT
3778 ,X_MSG_DATA => X_MSG_DATA
3779 ,X_RETURN_STATUS => X_RETURN_STATUS);
3780
3781 end has_viewCustomerAccess;
3782
3783 procedure has_updateCustomerAccess
3784 ( p_api_version_number IN NUMBER
3785 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3786 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3787 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
3788 ,p_admin_flag IN VARCHAR2
3789 ,p_admin_group_id IN NUMBER
3790 ,p_person_id IN NUMBER
3791 ,p_customer_id IN NUMBER
3792 ,p_check_access_flag IN VARCHAR2
3793 ,p_identity_salesforce_id IN NUMBER
3794 ,p_partner_cont_party_id IN NUMBER
3795 ,x_return_status OUT NOCOPY VARCHAR2
3796 ,x_msg_count OUT NOCOPY NUMBER
3797 ,x_msg_data OUT NOCOPY VARCHAR2
3798 ,x_update_access_flag OUT NOCOPY VARCHAR2
3799 )is
3800 l_api_name CONSTANT VARCHAR2(30) := 'has_updateCustomerAccess';
3801 l_api_version_number CONSTANT NUMBER := 2.0;
3802 l_tmp varchar2(1);
3803 l_person_id number;
3804 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
3805
3806 cursor resource_access_csr is
3807
3808 select 'X'
3809 from as_accesses_all
3810 where customer_id = p_customer_id
3811 and lead_id is null
3812 and sales_lead_id is null
3813 and team_leader_flag = 'Y'
3814 and salesforce_id = p_identity_salesforce_id;
3815
3816
3817 cursor manager_access_csr(p_resource_id number) is
3818 select 'X'
3819 from as_accesses_all a
3820 where customer_id = p_customer_id
3821 and lead_id is null
3822 and sales_lead_id is null
3823 and (EXISTS (select 'X'
3824 from as_rpt_managers_v rm
3825 where a.salesforce_id = rm.resource_id
3826 and rm.parent_resource_id = p_resource_id
3827 and ((rm.parent_resource_id = rm.resource_id
3828 and a.team_leader_flag = 'Y')
3829 or (rm.parent_resource_id <> rm.resource_id))));
3830
3831 cursor mgr_i_access_csr(p_resource_id number) is
3832 select 'X'
3833 from as_accesses_all a
3834 where a.customer_id = p_customer_id
3835 and lead_id is null
3836 and sales_lead_id is null
3837 and a.team_leader_flag = 'Y'
3838 and (EXISTS (select 'x'
3839 from as_rpt_managers_v rm
3840 where a.salesforce_id = rm.resource_id
3841 and rm.parent_resource_id = p_resource_id));
3842
3843 cursor admin_access_csr is
3844 select 'X'
3845 from as_accesses_all a
3846 where customer_id = p_customer_id
3847 and lead_id is null
3848 and sales_lead_id is null
3849 and EXISTS (select 'x'
3850 from as_rpt_admins_v rm
3851 where a.salesforce_id = rm.salesforce_id
3852 and rm.parent_sales_group_id = p_admin_group_id
3853 and ((rm.salesforce_id = p_identity_salesforce_id
3854 and a.team_leader_flag = 'Y')
3855 or (rm.salesforce_id <> p_identity_salesforce_id)));
3856
3857 cursor admin_i_access_csr is
3858 select 'x'
3859 from as_accesses_all a
3860 where a.customer_id = p_customer_id
3861 and lead_id is null
3862 and sales_lead_id is null
3863 and a.team_leader_flag = 'Y'
3864 and EXISTS (select 'x'
3865 from as_rpt_admins_v rm
3866 where a.salesforce_id = rm.salesforce_id
3867 and rm.parent_sales_group_id = p_admin_group_id);
3868
3869 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3870 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_updateCustomerAccess';
3871
3872 begin
3873 -- Standard call to check for call compatibility.
3874 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3875 p_api_version_number,
3876 l_api_name,
3877 G_PKG_NAME)
3878 THEN
3879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3880 END IF;
3881
3882 -- Initialize message list if p_init_msg_list is set to TRUE.
3883 IF FND_API.to_Boolean( p_init_msg_list )
3884 THEN
3885 FND_MSG_PUB.initialize;
3886 END IF;
3887
3888 -- Debug Message
3889 IF l_debug THEN
3890 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3891 'Private API: ' || l_api_name || 'start');
3892
3893 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3894 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3895 END IF;
3896
3897 -- Initialize API return status to SUCCESS
3898 x_return_status := FND_API.G_RET_STS_SUCCESS;
3899
3900 IF l_debug THEN
3901 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3902 'customer_id: ' || p_customer_id);
3903 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3904 'ident salesforce_id: ' || p_identity_salesforce_id);
3905 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3906 'admin_group_id: ' || p_admin_group_id);
3907 END IF;
3908
3909 --
3910 -- API body
3911 --
3912 -- Initialize access flag to 'N'
3913 x_update_access_flag := 'N';
3914
3915 if p_check_access_flag = 'N'
3916 then
3917 x_update_access_flag := 'Y';
3918 else -- if p_check_access_flag = 'Y'
3919 open resource_access_csr;
3920 fetch resource_access_csr into l_tmp;
3921 if p_partner_cont_party_id is not null
3922 and p_partner_cont_party_id <> FND_API.G_MISS_NUM
3923 then
3924 if (resource_access_csr%FOUND)
3925 then
3926 x_update_access_flag := 'Y';
3927 close resource_access_csr;
3928 return;
3929 end if;
3930 end if;
3931 /*
3932 if p_person_id is null or p_person_id = fnd_api.g_miss_num
3933 then
3934 get_person_id(p_identity_salesforce_id, l_person_id);
3935 else
3936 l_person_id := p_person_id;
3937 end if;
3938 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3939 'person id: ' || l_person_id);
3940 */
3941 l_access_profile_rec := p_access_profile_rec;
3942 get_accessProfileValues(l_access_profile_rec);
3943
3944 if l_access_profile_rec.cust_access_profile_value = 'F' then
3945 x_update_access_flag := 'Y';
3946 elsif (resource_access_csr%FOUND)
3947 -- profile is 'P' or 'T' and access record exists for the login
3948 -- user itself
3949 then
3950 x_update_access_flag := 'Y';
3951 else -- profile is 'P' or 'T' and access record doesn't exist for the
3952 -- login user
3953
3954 if nvl(p_admin_flag,'N') <> 'Y' -- mgr
3955 then
3956 if l_access_profile_rec.mgr_update_profile_value = 'U'
3957 then
3958 open manager_access_csr(p_identity_salesforce_id);
3959 fetch manager_access_csr into l_tmp;
3960 if (manager_access_csr%FOUND)
3961 then
3962 x_update_access_flag := 'Y';
3963 end if;
3964 close manager_access_csr;
3965 elsif l_access_profile_rec.mgr_update_profile_value = 'I'
3966 then
3967 open mgr_i_access_csr(p_identity_salesforce_id);
3968 fetch mgr_i_access_csr into l_tmp;
3969 if(mgr_i_access_csr%FOUND)
3970 then
3971 x_update_access_flag := 'Y';
3972 end if;
3973 close mgr_i_access_csr;
3974 end if; -- mgr
3975 else
3976 if l_access_profile_rec.admin_update_profile_value = 'U'
3977 then
3978 open admin_access_csr;
3979 fetch admin_access_csr into l_tmp;
3980 if (admin_access_csr%FOUND)
3981 then
3982 x_update_access_flag := 'Y';
3983 end if;
3984 close admin_access_csr;
3985 elsif l_access_profile_rec.admin_update_profile_value = 'I'
3986 then
3987 open admin_i_access_Csr;
3988 fetch admin_i_access_csr into l_tmp;
3989 if(admin_i_access_csr%FOUND)
3990 then
3991 x_update_access_flag := 'Y';
3992 end if;
3993 close admin_i_access_csr;
3994 end if; -- admin
3995 end if; -- (resource_access_csr%FOUND)
3996 end if;
3997 close resource_access_csr;
3998
3999 if x_update_access_flag = 'N' and is_party_person(p_customer_id)
4000 then
4001 has_updatePersonAccess(
4002 p_access_profile_rec => l_access_profile_rec
4003 ,p_admin_flag => p_admin_flag
4004 ,p_admin_group_id => p_admin_group_id
4005 ,p_person_id => p_person_id
4006 ,p_customer_id => p_customer_id
4007 ,p_identity_salesforce_id => p_identity_salesforce_id
4008 ,x_update_access_flag => x_update_access_flag
4009 );
4010 end if;
4011 end if; --if p_check_access_flag = 'N'
4012
4013 IF l_debug THEN
4014 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4015 'x_update_access_flag: ' || x_update_access_flag);
4016
4017 -- Debug Message
4018 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4019 'Private API: ' || l_api_name || 'end');
4020
4021 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
4022 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4023 END IF;
4024
4025
4026 FND_MSG_PUB.Count_And_Get
4027 ( p_count => x_msg_count,
4028 p_data => x_msg_data
4029 );
4030
4031 EXCEPTION
4032 WHEN FND_API.G_EXC_ERROR THEN
4033 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4034 P_MODULE => l_module
4035 ,P_API_NAME => L_API_NAME
4036 ,P_PKG_NAME => G_PKG_NAME
4037 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4038 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4039 , P_ROLLBACK_FLAG => 'N'
4040 ,X_MSG_COUNT => X_MSG_COUNT
4041 ,X_MSG_DATA => X_MSG_DATA
4042 ,X_RETURN_STATUS => X_RETURN_STATUS);
4043
4044 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4045 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4046 P_MODULE => l_module
4047 ,P_API_NAME => L_API_NAME
4048 ,P_PKG_NAME => G_PKG_NAME
4049 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
4050 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4051 , P_ROLLBACK_FLAG => 'N'
4052 ,X_MSG_COUNT => X_MSG_COUNT
4053 ,X_MSG_DATA => X_MSG_DATA
4054 ,X_RETURN_STATUS => X_RETURN_STATUS);
4055
4056 WHEN OTHERS THEN
4057 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4058 P_MODULE => l_module
4059 ,P_API_NAME => L_API_NAME
4060 ,P_PKG_NAME => G_PKG_NAME
4061 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
4062 ,P_SQLCODE => SQLCODE
4063 ,P_SQLERRM => SQLERRM
4064 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4065 , P_ROLLBACK_FLAG => 'N'
4066 ,X_MSG_COUNT => X_MSG_COUNT
4067 ,X_MSG_DATA => X_MSG_DATA
4068 ,X_RETURN_STATUS => X_RETURN_STATUS);
4069
4070 end has_updateCustomerAccess;
4071
4072
4073 procedure has_updateLeadAccess
4074 ( p_api_version_number IN NUMBER
4075 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4076 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4077 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
4078 ,p_admin_flag IN VARCHAR2
4079 ,p_admin_group_id IN NUMBER
4080 ,p_person_id IN NUMBER
4081 ,p_sales_lead_id IN NUMBER
4082 ,p_check_access_flag IN VARCHAR2
4083 ,p_identity_salesforce_id IN NUMBER
4084 ,p_partner_cont_party_id IN NUMBER
4085 ,x_return_status OUT NOCOPY VARCHAR2
4086 ,x_msg_count OUT NOCOPY NUMBER
4087 ,x_msg_data OUT NOCOPY VARCHAR2
4088 ,x_update_access_flag OUT NOCOPY VARCHAR2
4089 ) is
4090 l_api_name CONSTANT VARCHAR2(30) := 'has_updateLeadAccess';
4091 l_api_version_number CONSTANT NUMBER := 2.0;
4092 l_tmp varchar2(1);
4093 l_person_id number;
4094 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
4095
4096 cursor resource_access_csr is
4097 select 'X'
4098 from as_accesses_all a
4099 where a.sales_lead_id = p_sales_lead_id
4100 and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
4101 and a.salesforce_id = p_identity_salesforce_id;
4102
4103 cursor manager_access_csr(p_resource_id number) is
4104
4105 select 'X'
4106 from as_accesses_all a
4107 where a.sales_lead_id = p_sales_lead_id
4108 and EXISTS (select 'x'
4109 from as_rpt_managers_v rm
4110 where a.salesforce_id = rm.resource_id
4111 and rm.parent_resource_id = p_resource_id
4112 and (rm.parent_resource_id = rm.resource_id
4113 and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
4114 or (rm.parent_resource_id <> rm.resource_id)));
4115
4116 cursor mgr_i_access_csr(p_resource_id number) is
4117 select 'X'
4118 from as_accesses_all a
4119 where a.sales_lead_id = p_sales_lead_id
4120 and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
4121 and (EXISTS (select 'x'
4122 from as_rpt_managers_v rm
4123 where a.salesforce_id = rm.resource_id
4124 and rm.parent_resource_id = p_resource_id));
4125
4126 cursor admin_access_csr is
4127 select 'x'
4128 from as_accesses_all a
4129 where a.sales_lead_id = p_sales_lead_id
4130 and EXISTS (select 'x'
4131 from as_rpt_admins_v rm
4132 where a.salesforce_id = rm.salesforce_id
4133 and rm.parent_sales_group_id = p_admin_group_id
4134 and (rm.salesforce_id = p_identity_salesforce_id
4135 and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
4136 or (rm.salesforce_id <> p_identity_salesforce_id)));
4137
4138 cursor admin_i_access_csr is
4139 select 'x'
4140 from as_accesses_all a
4141 where a.sales_lead_id = p_sales_lead_id
4142 and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
4143 and EXISTS (select 'x'
4144 from as_rpt_admins_v rm
4145 where a.salesforce_id = rm.salesforce_id
4146 and rm.parent_sales_group_id = p_admin_group_id);
4147
4148 cursor am_mgr_access_csr(p_resource_id number) is
4149 select 'x'
4150 from as_sales_leads lead, as_accesses_all a, as_rpt_managers_v rm
4151 where lead.customer_id = a.customer_id
4152 and a.salesforce_id = rm.resource_id
4153 and a.salesforce_role_code = 'AM'
4154 and rm.parent_resource_id = p_resource_id
4155 and lead.sales_lead_id = p_sales_lead_id;
4156
4157 cursor am_admin_access_csr is
4158 select 'x'
4159 from as_sales_leads lead, as_accesses_all a, as_rpt_admins_v rm
4160 where lead.customer_id = a.customer_id
4161 and a.salesforce_id = rm.salesforce_id
4162 and a.salesforce_role_code = 'AM'
4163 and rm.parent_sales_group_id = p_admin_group_id
4164 and lead.sales_lead_id = p_sales_lead_id;
4165
4166 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
4167 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_updateLeadAccess';
4168 begin
4169 -- Standard call to check for call compatibility.
4170 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4171 p_api_version_number,
4172 l_api_name,
4173 G_PKG_NAME)
4174 THEN
4175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4176 END IF;
4177
4178 -- Initialize message list if p_init_msg_list is set to TRUE.
4179 IF FND_API.to_Boolean( p_init_msg_list )
4180 THEN
4181 FND_MSG_PUB.initialize;
4182 END IF;
4183
4184 -- Debug Message
4185 IF l_debug THEN
4186 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4187 'Private API: ' || l_api_name || 'start');
4188
4189 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4190 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4191 END IF;
4192
4193 -- Initialize API return status to SUCCESS
4194 x_return_status := FND_API.G_RET_STS_SUCCESS;
4195
4196 IF l_debug THEN
4197 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4198 'p_sales_lead_id: ' || p_sales_lead_id);
4199 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4200 'ident salesforce_id: ' || p_identity_salesforce_id);
4201 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4202 'p_admin_group_id: ' || p_admin_group_id);
4203 END IF;
4204
4205 --
4206 -- API body
4207 --
4208
4209 -- Initialize access flag to 'N'
4210 x_update_access_flag := 'N';
4211
4212 if p_check_access_flag = 'N'
4213 then
4214 x_update_access_flag := 'Y';
4215 else -- if p_check_access_flag = 'Y'
4216 -- PRM security
4217 open resource_access_csr;
4218 fetch resource_access_csr into l_tmp;
4219 /* if p_partner_cont_party_id is not null
4220 and p_partner_cont_party_id <> FND_API.G_MISS_NUM
4221 then
4222 if (resource_access_csr%FOUND)
4223 then
4224 x_update_access_flag := 'Y';
4225 close resource_access_csr;
4226 return;
4227 end if;
4228 end if; */
4229 /*
4230 if p_person_id is null or p_person_id = fnd_api.g_miss_num
4231 then
4232 get_person_id(p_identity_salesforce_id, l_person_id);
4233 else
4234 l_person_id := p_person_id;
4235 end if;
4236 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4237 'person id: ' || l_person_id);
4238 */
4239 l_access_profile_rec := p_access_profile_rec;
4240 get_accessProfileValues(l_access_profile_rec);
4241
4242 if l_access_profile_rec.lead_access_profile_value = 'F'
4243 then
4244 x_update_access_flag := 'Y';
4245 elsif resource_access_csr%FOUND
4246 then
4247 x_update_access_flag := 'Y';
4248 else
4249 if nvl(p_admin_flag,'N') <> 'Y'
4250 then
4251 if l_access_profile_rec.mgr_update_profile_value = 'U'
4252 then
4253 open manager_access_csr(p_identity_salesforce_id);
4254 fetch manager_access_csr into l_tmp;
4255 if manager_access_csr%FOUND
4256 -- First check if mgr's subordinate
4257 -- which are not 'AM'
4258 then
4259 x_update_access_flag := 'Y';
4260 else -- if mgr's subordinate which are 'AM'
4261 open am_mgr_access_csr(p_identity_salesforce_id);
4262 fetch am_mgr_access_csr into l_tmp;
4263 if am_mgr_access_csr%FOUND
4264 then
4265 x_update_access_flag := 'Y';
4266 end if;
4267 close am_mgr_access_csr;
4268 end if; -- manager_access_csr%FOUND
4269 close manager_access_csr;
4270 elsif l_access_profile_rec.mgr_update_profile_value = 'I'
4271 then
4272 open mgr_i_access_csr(p_identity_salesforce_id);
4273 fetch mgr_i_access_csr into l_tmp;
4274 if mgr_i_access_csr%FOUND
4275 then
4276 x_update_access_flag := 'Y';
4277 end if;
4278 close mgr_i_access_csr;
4279 end if; -- l_access_profile_rec.mgr_update_profile_value = 'U'
4280 elsif l_access_profile_rec.admin_update_profile_value = 'U'
4281 then
4282 open admin_access_csr;
4283 fetch admin_access_csr into l_tmp;
4284 if admin_access_csr%FOUND
4285 then
4286 x_update_access_flag := 'Y';
4287 else
4288 open am_admin_access_csr;
4289 fetch am_admin_access_csr into l_tmp;
4290 if am_admin_access_csr%FOUND
4291 then
4292 x_update_access_flag := 'Y';
4293 end if;
4294 close am_admin_access_csr;
4295 end if; -- admin_access_csr%FOUND
4296 close admin_access_csr;
4297 elsif l_access_profile_rec.admin_update_profile_value = 'I'
4298 then
4299 open admin_i_access_csr;
4300 fetch admin_i_access_csr into l_tmp;
4301 if admin_i_access_csr%FOUND
4302 then
4303 x_update_access_flag := 'Y';
4304 end if;
4305 close admin_i_access_csr;
4306 end if; -- if p_admin_flag <> 'Y'
4307 end if;
4308 close resource_access_csr;
4309 end if; -- if p_check_access_flag = 'N'
4310 --
4311 -- End of API body.
4312 --
4313
4314 IF l_debug THEN
4315 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4316 'x_update_access_flag: ' || x_update_access_flag);
4317
4318 -- Debug Message
4319 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4320 'Private API: ' || l_api_name || 'end');
4321
4322 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
4323 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4324 END IF;
4325
4326
4327 FND_MSG_PUB.Count_And_Get
4328 ( p_count => x_msg_count,
4329 p_data => x_msg_data
4330 );
4331
4332 EXCEPTION
4333 WHEN FND_API.G_EXC_ERROR THEN
4334 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4335 P_MODULE => l_module
4336 ,P_API_NAME => L_API_NAME
4337 ,P_PKG_NAME => G_PKG_NAME
4338 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4339 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4340 , P_ROLLBACK_FLAG => 'N'
4341 ,X_MSG_COUNT => X_MSG_COUNT
4342 ,X_MSG_DATA => X_MSG_DATA
4343 ,X_RETURN_STATUS => X_RETURN_STATUS);
4344
4345 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4346 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4347 P_MODULE => l_module
4348 ,P_API_NAME => L_API_NAME
4349 ,P_PKG_NAME => G_PKG_NAME
4350 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
4351 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4352 , P_ROLLBACK_FLAG => 'N'
4353 ,X_MSG_COUNT => X_MSG_COUNT
4354 ,X_MSG_DATA => X_MSG_DATA
4355 ,X_RETURN_STATUS => X_RETURN_STATUS);
4356
4357 WHEN OTHERS THEN
4358 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4359 P_MODULE => l_module
4360 ,P_API_NAME => L_API_NAME
4361 ,P_PKG_NAME => G_PKG_NAME
4362 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
4363 ,P_SQLCODE => SQLCODE
4364 ,P_SQLERRM => SQLERRM
4365 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4366 , P_ROLLBACK_FLAG => 'N'
4367 ,X_MSG_COUNT => X_MSG_COUNT
4368 ,X_MSG_DATA => X_MSG_DATA
4369 ,X_RETURN_STATUS => X_RETURN_STATUS);
4370
4371 end has_updateLeadAccess;
4372
4373 procedure has_viewLeadAccess
4374 ( p_api_version_number IN NUMBER
4375 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4376 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4377 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
4378 ,p_admin_flag IN VARCHAR2
4379 ,p_admin_group_id IN NUMBER
4380 ,p_person_id IN NUMBER
4381 ,p_sales_lead_id IN NUMBER
4382 ,p_check_access_flag IN VARCHAR2
4383 ,p_identity_salesforce_id IN NUMBER
4384 ,p_partner_cont_party_id IN NUMBER
4385 ,x_return_status OUT NOCOPY VARCHAR2
4386 ,x_msg_count OUT NOCOPY NUMBER
4387 ,x_msg_data OUT NOCOPY VARCHAR2
4388 ,x_view_access_flag OUT NOCOPY VARCHAR2
4389 ) is
4390 l_api_name CONSTANT VARCHAR2(30) := 'has_viewLeadAccess';
4391 l_api_version_number CONSTANT NUMBER := 2.0;
4392 l_tmp varchar2(1);
4393 l_person_id number;
4394 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
4395
4396 cursor resource_access_csr is
4397 select 'X'
4398 from as_accesses_all a
4399 where a.sales_lead_id = p_sales_lead_id
4400 and a.salesforce_id = p_identity_salesforce_id;
4401
4402 cursor manager_access_csr(p_resource_id number) is
4403
4404 select 'X'
4405 from as_accesses_all a
4406 where a.sales_lead_id = p_sales_lead_id
4407 and EXISTS (select 'x'
4408 from as_rpt_managers_v rm
4409 where a.salesforce_id = rm.resource_id
4410 and rm.parent_resource_id = p_resource_id);
4411
4412
4413 cursor admin_access_csr is
4414 select 'x'
4415 from as_accesses_all a
4416 where a.sales_lead_id = p_sales_lead_id
4417 and EXISTS (select 'x'
4418 from as_rpt_admins_v rm
4419 where a.salesforce_id = rm.salesforce_id
4420 and rm.parent_sales_group_id = p_admin_group_id);
4421
4422 cursor am_mgr_access_csr(p_resource_id number) is
4423 select 'x'
4424 from as_sales_leads lead, as_accesses_all a, as_rpt_managers_v rm
4425 where lead.customer_id = a.customer_id
4426 and a.salesforce_id = rm.resource_id
4427 and a.salesforce_role_code = 'AM'
4428 and rm.parent_resource_id = p_resource_id
4429 and lead.sales_lead_id = p_sales_lead_id;
4430
4431 cursor am_admin_access_csr is
4432 select 'x'
4433 from as_sales_leads lead, as_accesses_all a, as_rpt_admins_v rm
4434 where lead.customer_id = a.customer_id
4435 and a.salesforce_id = rm.salesforce_id
4436 and a.salesforce_role_code = 'AM'
4437 and rm.parent_sales_group_id = p_admin_group_id
4438 and lead.sales_lead_id = p_sales_lead_id;
4439
4440 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
4441 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_viewLeadAccess';
4442
4443 begin
4444 -- Standard call to check for call compatibility.
4445 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4446 p_api_version_number,
4447 l_api_name,
4448 G_PKG_NAME)
4449 THEN
4450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4451 END IF;
4452
4453 -- Initialize message list if p_init_msg_list is set to TRUE.
4454 IF FND_API.to_Boolean( p_init_msg_list )
4455 THEN
4456 FND_MSG_PUB.initialize;
4457 END IF;
4458
4459 -- Debug Message
4460 IF l_debug THEN
4461 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4462 'Private API: ' || l_api_name || 'start');
4463
4464 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4465 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4466 END IF;
4467
4468 -- Initialize API return status to SUCCESS
4469 x_return_status := FND_API.G_RET_STS_SUCCESS;
4470
4471 IF l_debug THEN
4472 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4473 'p_sales_lead_id: ' || p_sales_lead_id);
4474 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4475 'ident salesforce_id: ' || p_identity_salesforce_id);
4476 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4477 'p_admin_group_id: ' || p_admin_group_id);
4478 END IF;
4479
4480 --
4481 -- API body
4482 --
4483
4484 -- Initialize access flag to 'N'
4485 x_view_access_flag := 'N';
4486
4487 if p_check_access_flag = 'N'
4488 then
4489 x_view_access_flag := 'Y';
4490 else -- if p_check_access_flag = 'Y'
4491 -- PRM security
4492 open resource_access_csr;
4493 fetch resource_access_csr into l_tmp;
4494
4495 l_access_profile_rec := p_access_profile_rec;
4496 get_accessProfileValues(l_access_profile_rec);
4497
4498 if l_access_profile_rec.lead_access_profile_value in ('F','P')
4499 then
4500 x_view_access_flag := 'Y';
4501 elsif resource_access_csr%FOUND
4502 then
4503 x_view_access_flag := 'Y';
4504 else
4505 if nvl(p_admin_flag,'N') <> 'Y'
4506 then
4507
4508 open manager_access_csr(p_identity_salesforce_id);
4509 fetch manager_access_csr into l_tmp;
4510 if manager_access_csr%FOUND
4511 -- First check if mgr's subordinate
4512 -- which are not 'AM'
4513 then
4514 x_view_access_flag := 'Y';
4515 else -- if mgr's subordinate which are 'AM'
4516 open am_mgr_access_csr(p_identity_salesforce_id);
4517 fetch am_mgr_access_csr into l_tmp;
4518 if am_mgr_access_csr%FOUND
4519 then
4520 x_view_access_flag := 'Y';
4521 end if;
4522 close am_mgr_access_csr;
4523 end if; -- manager_access_csr%FOUND
4524 close manager_access_csr;
4525 else
4526 open admin_access_csr;
4527 fetch admin_access_csr into l_tmp;
4528 if admin_access_csr%FOUND
4529 then
4530 x_view_access_flag := 'Y';
4531 else
4532 open am_admin_access_csr;
4533 fetch am_admin_access_csr into l_tmp;
4534 if am_admin_access_csr%FOUND
4535 then
4536 x_view_access_flag := 'Y';
4537 end if;
4538 close am_admin_access_csr;
4539 end if; -- admin_access_csr%FOUND
4540 close admin_access_csr;
4541 end if; -- if p_admin_flag <> 'Y'
4542 end if; -- if lead_access_profile_value = 'F'
4543 close resource_access_csr;
4544 end if; -- if p_check_access_flag = 'N'
4545 --
4546 -- End of API body.
4547 --
4548
4549 IF l_debug THEN
4550 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4551 'x_view_access_flag: ' || x_view_access_flag);
4552
4553 -- Debug Message
4554 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4555 'Private API: ' || l_api_name || 'end');
4556
4557 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
4558 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4559 END IF;
4560
4561 FND_MSG_PUB.Count_And_Get
4562 ( p_count => x_msg_count,
4563 p_data => x_msg_data
4564 );
4565
4566 EXCEPTION
4567 WHEN FND_API.G_EXC_ERROR THEN
4568 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4569 P_MODULE => l_module
4570 ,P_API_NAME => L_API_NAME
4571 ,P_PKG_NAME => G_PKG_NAME
4572 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4573 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4574 , P_ROLLBACK_FLAG => 'N'
4575 ,X_MSG_COUNT => X_MSG_COUNT
4576 ,X_MSG_DATA => X_MSG_DATA
4577 ,X_RETURN_STATUS => X_RETURN_STATUS);
4578
4579 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4580 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4581 P_MODULE => l_module
4582 ,P_API_NAME => L_API_NAME
4583 ,P_PKG_NAME => G_PKG_NAME
4584 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
4585 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4586 , P_ROLLBACK_FLAG => 'N'
4587 ,X_MSG_COUNT => X_MSG_COUNT
4588 ,X_MSG_DATA => X_MSG_DATA
4589 ,X_RETURN_STATUS => X_RETURN_STATUS);
4590
4591 WHEN OTHERS THEN
4592 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4593 P_MODULE => l_module
4594 ,P_API_NAME => L_API_NAME
4595 ,P_PKG_NAME => G_PKG_NAME
4596 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
4597 ,P_SQLCODE => SQLCODE
4598 ,P_SQLERRM => SQLERRM
4599 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4600 , P_ROLLBACK_FLAG => 'N'
4601 ,X_MSG_COUNT => X_MSG_COUNT
4602 ,X_MSG_DATA => X_MSG_DATA
4603 ,X_RETURN_STATUS => X_RETURN_STATUS);
4604
4605 end has_viewLeadAccess;
4606
4607
4608
4609 procedure has_updateOpportunityAccess
4610 ( p_api_version_number IN NUMBER
4611 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4612 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4613 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
4614 ,p_admin_flag IN VARCHAR2
4615 ,p_admin_group_id IN NUMBER
4616 ,p_person_id IN NUMBER
4617 ,p_opportunity_id IN NUMBER
4618 ,p_check_access_flag IN VARCHAR2
4619 ,p_identity_salesforce_id IN NUMBER
4620 ,p_partner_cont_party_id IN NUMBER
4621 ,x_return_status OUT NOCOPY VARCHAR2
4622 ,x_msg_count OUT NOCOPY NUMBER
4623 ,x_msg_data OUT NOCOPY VARCHAR2
4624 ,x_update_access_flag OUT NOCOPY VARCHAR2
4625 ) is
4626 l_api_name CONSTANT VARCHAR2(30) := 'has_updateOppurtunityAccess';
4627 l_api_version_number CONSTANT NUMBER := 2.0;
4628 l_tmp varchar2(1);
4629 l_update_access_flag varchar2(1);
4630 l_org_id NUMBER;
4631 l_person_id number;
4632 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
4633
4634 cursor resource_access_csr is
4635 select 'X'
4636 from as_accesses_all a
4637 where a.lead_id = p_opportunity_id
4638 and a.team_leader_flag = 'Y'
4639 and a.salesforce_id = p_identity_salesforce_id;
4640
4641 cursor manager_access_csr(p_resource_id number) is
4642
4643 select 'X'
4644 from as_accesses_all a
4645 where a.lead_id = p_opportunity_id
4646 and (EXISTS (select 'x'
4647 from as_rpt_managers_v rm
4648 where a.salesforce_id = rm.resource_id
4649 and rm.parent_resource_id = p_resource_id
4650 and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate)
4651 and ((rm.parent_resource_id = rm.resource_id
4652 and a.team_leader_flag = 'Y')
4653 or (rm.parent_resource_id <> rm.resource_id))));
4654
4655 cursor mgr_i_access_csr(p_resource_id number) is
4656 select 'X'
4657 from as_accesses_all a
4658 where a.lead_id = p_opportunity_id
4659 and a.team_leader_flag = 'Y'
4660 and (EXISTS (select 'x'
4661 from as_rpt_managers_v rm
4662 where a.salesforce_id = rm.resource_id
4663 and rm.parent_resource_id = p_resource_id
4664 and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate)
4665 ));
4666
4667 cursor admin_access_csr is
4668 select 'x'
4669 from as_accesses_all a
4670 where a.lead_id = p_opportunity_id
4671 and EXISTS (select 'x'
4672 from as_rpt_admins_v rm
4673 where a.salesforce_id = rm.salesforce_id
4674 and rm.parent_sales_group_id = p_admin_group_id
4675 and ((rm.salesforce_id = p_identity_salesforce_id
4676 and a.team_leader_flag = 'Y')
4677 or (rm.salesforce_id <> p_identity_salesforce_id)));
4678
4679 cursor admin_i_access_csr is
4680 select 'x'
4681 from as_accesses_all a
4682 where a.lead_id = p_opportunity_id
4683 and a.team_leader_flag = 'Y'
4684 and EXISTS (select 'x'
4685 from as_rpt_admins_v rm
4686 where a.salesforce_id = rm.salesforce_id
4687 and rm.parent_sales_group_id = p_admin_group_id);
4688
4689 cursor am_mgr_access_csr(p_resource_id number) is
4690 select 'x'
4691 from as_leads opp, as_accesses_all a, as_rpt_managers_v rm
4692 where opp.customer_id = a.customer_id
4693 and a.salesforce_id = rm.resource_id
4694 and a.salesforce_role_code = 'AM'
4695 and rm.parent_resource_id = p_resource_id
4696 and opp.lead_id = p_opportunity_id
4697 and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate);
4698
4699 cursor am_admin_access_csr is
4700 select 'x'
4701 from as_leads opp, as_accesses_all a, as_rpt_admins_v rm
4702 where opp.customer_id = a.customer_id
4703 and a.salesforce_id = rm.salesforce_id
4704 and a.salesforce_role_code = 'AM'
4705 and rm.parent_sales_group_id = p_admin_group_id
4706 and opp.lead_id = p_opportunity_id;
4707
4708 cursor find_lead_org is
4709 select org_id
4710 from as_leads_all
4711 where lead_id = p_opportunity_id;
4712
4713 cursor c_org_access(p_org_id NUMBER) is
4714 select 'Y'
4715 from hr_operating_units hr
4716 where hr.organization_id = p_org_id
4717 and mo_global.check_access(hr.organization_id) = 'Y';
4718
4719 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
4720 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_updateOpportunityAccess';
4721
4722 begin
4723 -- Standard call to check for call compatibility.
4724 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4725 p_api_version_number,
4726 l_api_name,
4727 G_PKG_NAME)
4728 THEN
4729 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4730 END IF;
4731
4732 -- Initialize message list if p_init_msg_list is set to TRUE.
4733 IF FND_API.to_Boolean( p_init_msg_list )
4734 THEN
4735 FND_MSG_PUB.initialize;
4736 END IF;
4737
4738 -- Debug Message
4739 IF l_debug THEN
4740 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4741 'Private API: ' || l_api_name || 'start');
4742
4743 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4744 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4745 END IF;
4746
4747 -- Initialize API return status to SUCCESS
4748 x_return_status := FND_API.G_RET_STS_SUCCESS;
4749
4750 IF l_debug THEN
4751 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4752 'p_opportunity_id: ' || p_opportunity_id);
4753 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4754 'ident salesforce_id: ' || p_identity_salesforce_id);
4755 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4756 'p_admin_group_id: ' || p_admin_group_id);
4757 END IF;
4758
4759 --
4760 -- API body
4761 --
4762
4763 -- Initialize access flag to 'N'
4764 x_update_access_flag := 'N';
4765
4766 if p_check_access_flag = 'N'
4767 then
4768 x_update_access_flag := 'Y';
4769 else -- if p_check_access_flag = 'Y'
4770 -- PRM security
4771 open resource_access_csr;
4772 fetch resource_access_csr into l_tmp;
4773 /* if p_partner_cont_party_id is not null
4774 and p_partner_cont_party_id <> FND_API.G_MISS_NUM
4775 then
4776 if (resource_access_csr%FOUND)
4777 then
4778 x_update_access_flag := 'Y';
4779 close resource_access_csr;
4780 return;
4781 end if;
4782 end if; */
4783 /* if p_person_id is null or p_person_id = fnd_api.g_miss_num
4784 then
4785 get_person_id(p_identity_salesforce_id, l_person_id);
4786 else
4787 l_person_id := p_person_id;
4788 end if;
4789 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4790 'person id: ' || l_person_id);
4791 */
4792 l_access_profile_rec := p_access_profile_rec;
4793 get_accessProfileValues(l_access_profile_rec);
4794
4795 if l_access_profile_rec.opp_access_profile_value = 'F'
4796 then
4797 x_update_access_flag := 'Y';
4798 elsif l_access_profile_rec.opp_access_profile_value = 'O'
4799 then
4800 -- l_update_access_flag will hold value for x_update_access_flag, NULL means unknown
4801 l_update_access_flag := 'N';
4802 /* check org full access */
4803 open find_lead_org;
4804 fetch find_lead_org into l_org_id;
4805 if(find_lead_org%FOUND)then
4806 if l_org_id IS NULL then
4807 l_update_access_flag := 'Y'; -- Access allowed if org_id NULL
4808 else
4809 l_update_access_flag := NULL; -- Need to check for the org id
4810 end if;
4811 end if;
4812 close find_lead_org;
4813
4814 -- Added for MOAC
4815 if l_update_access_flag IS NULL then
4816 open c_org_access(l_org_id);
4817 fetch c_org_access into l_update_access_flag;
4818 if c_org_access%NOTFOUND then
4819 l_update_access_flag := 'N';
4820 end if;
4821 close c_org_access;
4822 end if;
4823
4824 /* for bug 1613991 */
4825 if l_update_access_flag = 'N' and resource_access_csr%FOUND then
4826 l_update_access_flag := 'Y';
4827 end if;
4828
4829 x_update_access_flag := l_update_access_flag;
4830
4831 elsif resource_access_csr%FOUND
4832 then
4833 x_update_access_flag := 'Y';
4834 else
4835 if nvl(p_admin_flag,'N') <> 'Y'
4836 then
4837 if l_access_profile_rec.mgr_update_profile_value = 'U'
4838 then
4839 open manager_access_csr(p_identity_salesforce_id);
4840 fetch manager_access_csr into l_tmp;
4841 if manager_access_csr%FOUND
4842 -- First check if mgr's subordinate
4843 -- which are not 'AM'
4844 then
4845 x_update_access_flag := 'Y';
4846 else -- if mgr's subordinate which are 'AM'
4847 open am_mgr_access_csr(p_identity_salesforce_id);
4848 fetch am_mgr_access_csr into l_tmp;
4849 if am_mgr_access_csr%FOUND
4850 then
4851 x_update_access_flag := 'Y';
4852 end if;
4853 close am_mgr_access_csr;
4854 end if; -- manager_access_csr%FOUND
4855 close manager_access_csr;
4856 elsif l_access_profile_rec.mgr_update_profile_value = 'I'
4857 then
4858 open mgr_i_access_csr(p_identity_salesforce_id);
4859 fetch mgr_i_access_csr into l_tmp;
4860 if mgr_i_access_csr%FOUND
4861 then
4862 x_update_access_flag := 'Y';
4863 end if;
4864 close mgr_i_access_csr;
4865 end if; -- l_access_profile_rec.mgr_update_profile_value = 'U'
4866 elsif l_access_profile_rec.admin_update_profile_value = 'U'
4867 then
4868 open admin_access_csr;
4869 fetch admin_access_csr into l_tmp;
4870 if admin_access_csr%FOUND
4871 then
4872 x_update_access_flag := 'Y';
4873 else
4874 open am_admin_access_csr;
4875 fetch am_admin_access_csr into l_tmp;
4876 if am_admin_access_csr%FOUND
4877 then
4878 x_update_access_flag := 'Y';
4879 end if;
4880 close am_admin_access_csr;
4881 end if; -- admin_access_csr%FOUND
4882 close admin_access_csr;
4883 elsif l_access_profile_rec.admin_update_profile_value = 'I'
4884 then
4885 open admin_i_access_csr;
4886 fetch admin_i_access_csr into l_tmp;
4887 if admin_i_access_csr%FOUND
4888 then
4889 x_update_access_flag := 'Y';
4890 end if;
4891 close admin_i_access_csr;
4892 end if; -- if p_admin_flag <> 'Y'
4893 end if;
4894 close resource_access_csr;
4895 end if; -- if p_check_access_flag = 'N'
4896 --
4897 -- End of API body.
4898 --
4899
4900 IF l_debug THEN
4901 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4902 'x_update_access_flag: ' || x_update_access_flag);
4903
4904 -- Debug Message
4905 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4906 'Private API: ' || l_api_name || 'end');
4907
4908 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
4909 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4910 END IF;
4911
4912
4913 FND_MSG_PUB.Count_And_Get
4914 ( p_count => x_msg_count,
4915 p_data => x_msg_data
4916 );
4917
4918 EXCEPTION
4919 WHEN FND_API.G_EXC_ERROR THEN
4920 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4921 P_MODULE => l_module
4922 ,P_API_NAME => L_API_NAME
4923 ,P_PKG_NAME => G_PKG_NAME
4924 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4925 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4926 , P_ROLLBACK_FLAG => 'N'
4927 ,X_MSG_COUNT => X_MSG_COUNT
4928 ,X_MSG_DATA => X_MSG_DATA
4929 ,X_RETURN_STATUS => X_RETURN_STATUS);
4930
4931 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4932 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4933 P_MODULE => l_module
4934 ,P_API_NAME => L_API_NAME
4935 ,P_PKG_NAME => G_PKG_NAME
4936 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
4937 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4938 , P_ROLLBACK_FLAG => 'N'
4939 ,X_MSG_COUNT => X_MSG_COUNT
4940 ,X_MSG_DATA => X_MSG_DATA
4941 ,X_RETURN_STATUS => X_RETURN_STATUS);
4942
4943 WHEN OTHERS THEN
4944 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4945 P_MODULE => l_module
4946 ,P_API_NAME => L_API_NAME
4947 ,P_PKG_NAME => G_PKG_NAME
4948 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
4949 ,P_SQLCODE => SQLCODE
4950 ,P_SQLERRM => SQLERRM
4951 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4952 , P_ROLLBACK_FLAG => 'N'
4953 ,X_MSG_COUNT => X_MSG_COUNT
4954 ,X_MSG_DATA => X_MSG_DATA
4955 ,X_RETURN_STATUS => X_RETURN_STATUS);
4956
4957 end has_updateOpportunityAccess;
4958
4959 procedure has_viewOpportunityAccess
4960 ( p_api_version_number IN NUMBER
4961 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4962 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4963 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
4964 ,p_admin_flag IN VARCHAR2
4965 ,p_admin_group_id IN NUMBER
4966 ,p_person_id IN NUMBER
4967 ,p_opportunity_id IN NUMBER
4968 ,p_check_access_flag IN VARCHAR2
4969 ,p_identity_salesforce_id IN NUMBER
4970 ,p_partner_cont_party_id IN NUMBER
4971 ,x_return_status OUT NOCOPY VARCHAR2
4972 ,x_msg_count OUT NOCOPY NUMBER
4973 ,x_msg_data OUT NOCOPY VARCHAR2
4974 ,x_view_access_flag OUT NOCOPY VARCHAR2
4975 ) is
4976 l_api_name CONSTANT VARCHAR2(30) := 'has_viewOppurtunityAccess';
4977 l_api_version_number CONSTANT NUMBER := 2.0;
4978 l_tmp varchar2(1);
4979 l_view_access_flag varchar2(1);
4980 l_org_id number;
4981 l_person_id number;
4982 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
4983
4984 cursor resource_access_csr is
4985 select 'X'
4986 from as_accesses_all a
4987 where a.lead_id = p_opportunity_id
4988 and a.salesforce_id = p_identity_salesforce_id;
4989
4990 cursor manager_access_csr(p_resource_id number) is
4991
4992 select 'X'
4993 from as_accesses_all a
4994 where a.lead_id = p_opportunity_id
4995 and EXISTS (select 'x'
4996 from as_rpt_managers_v rm
4997 where a.salesforce_id = rm.resource_id
4998 and rm.parent_resource_id = p_resource_id
4999 and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate)
5000 );
5001
5002 cursor admin_access_csr is
5003 select 'x'
5004 from as_accesses_all a
5005 where a.lead_id = p_opportunity_id
5006 and EXISTS (select 'x'
5007 from as_rpt_admins_v rm
5008 where a.salesforce_id = rm.salesforce_id
5009 and rm.parent_sales_group_id = p_admin_group_id);
5010
5011
5012 cursor am_mgr_access_csr(p_resource_id number) is
5013 select 'x'
5014 from as_leads opp, as_accesses_all a, as_rpt_managers_v rm
5015 where opp.customer_id = a.customer_id
5016 and a.salesforce_id = rm.resource_id
5017 and a.salesforce_role_code = 'AM'
5018 and rm.parent_resource_id = p_resource_id
5019 and opp.lead_id = p_opportunity_id
5020 and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate);
5021
5022 cursor am_admin_access_csr is
5023 select 'x'
5024 from as_leads opp, as_accesses_all a, as_rpt_admins_v rm
5025 where opp.customer_id = a.customer_id
5026 and a.salesforce_id = rm.salesforce_id
5027 and a.salesforce_role_code = 'AM'
5028 and rm.parent_sales_group_id = p_admin_group_id
5029 and opp.lead_id = p_opportunity_id;
5030
5031 cursor find_lead_org is
5032 select org_id
5033 from as_leads_all
5034 where lead_id = p_opportunity_id;
5035
5036 cursor c_org_access(p_org_id NUMBER) is
5037 select 'Y'
5038 from hr_operating_units hr
5039 where hr.organization_id = p_org_id
5040 and mo_global.check_access(hr.organization_id) = 'Y';
5041
5042 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
5043 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_viewOpportunityAccess';
5044
5045 begin
5046 -- Standard call to check for call compatibility.
5047 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
5048 p_api_version_number,
5049 l_api_name,
5050 G_PKG_NAME)
5051 THEN
5052 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5053 END IF;
5054
5055 -- Initialize message list if p_init_msg_list is set to TRUE.
5056 IF FND_API.to_Boolean( p_init_msg_list )
5057 THEN
5058 FND_MSG_PUB.initialize;
5059 END IF;
5060
5061 -- Debug Message
5062 IF l_debug THEN
5063 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5064 'Private API: ' || l_api_name || 'start');
5065
5066 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5067 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
5068 END IF;
5069
5070 -- Initialize API return status to SUCCESS
5071 x_return_status := FND_API.G_RET_STS_SUCCESS;
5072
5073 IF l_debug THEN
5074 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5075 'p_opportunity_id: ' || p_opportunity_id);
5076 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5077 'ident salesforce_id: ' || p_identity_salesforce_id);
5078 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5079 'p_admin_group_id: ' || p_admin_group_id);
5080 END IF;
5081
5082 --
5083 -- API body
5084 --
5085
5086 -- Initialize access flag to 'N'
5087 x_view_access_flag := 'N';
5088
5089 if p_check_access_flag = 'N'
5090 then
5091 x_view_access_flag := 'Y';
5092 else -- if p_check_access_flag = 'Y'
5093
5094 open resource_access_csr;
5095 fetch resource_access_csr into l_tmp;
5096
5097 l_access_profile_rec := p_access_profile_rec;
5098 get_accessProfileValues(l_access_profile_rec);
5099
5100 if l_access_profile_rec.opp_access_profile_value in ('F','P')
5101 then
5102 x_view_access_flag := 'Y';
5103 elsif l_access_profile_rec.opp_access_profile_value = 'O'
5104 then
5105 /* check org full access */
5106 -- l_view_access_flag will hold value for x_view_access_flag, NULL means unknown
5107 l_view_access_flag := 'N';
5108 /* check org full access */
5109 open find_lead_org;
5110 fetch find_lead_org into l_org_id;
5111 if(find_lead_org%FOUND)then
5112 if l_org_id IS NULL then
5113 l_view_access_flag := 'Y'; -- Access allowed if org_id NULL
5114 else
5115 l_view_access_flag := NULL; -- Need to check for the org id
5116 end if;
5117 end if;
5118 close find_lead_org;
5119
5120 -- Added for MOAC
5121 if l_view_access_flag IS NULL then
5122 open c_org_access(l_org_id);
5123 fetch c_org_access into l_view_access_flag;
5124 if c_org_access%NOTFOUND then
5125 l_view_access_flag := 'N';
5126 end if;
5127 close c_org_access;
5128 end if;
5129
5130 /* for bug 1613991 */
5131 if l_view_access_flag = 'N' and resource_access_csr%FOUND then
5132 l_view_access_flag := 'Y';
5133 end if;
5134
5135 x_view_access_flag := l_view_access_flag;
5136 elsif resource_access_csr%FOUND
5137 then
5138 x_view_access_flag := 'Y';
5139 else
5140 if nvl(p_admin_flag,'N') <> 'Y'
5141 then
5142 open manager_access_csr(p_identity_salesforce_id);
5143 fetch manager_access_csr into l_tmp;
5144 if manager_access_csr%FOUND
5145 -- First check if mgr's subordinate
5146 -- which are not 'AM'
5147 then
5148 x_view_access_flag := 'Y';
5149 else -- if mgr's subordinate which are 'AM'
5150 open am_mgr_access_csr(p_identity_salesforce_id);
5151 fetch am_mgr_access_csr into l_tmp;
5152 if am_mgr_access_csr%FOUND
5153 then
5154 x_view_access_flag := 'Y';
5155 end if;
5156 close am_mgr_access_csr;
5157 end if; -- manager_access_csr%FOUND
5158 close manager_access_csr;
5159
5160 else
5161 open admin_access_csr;
5162 fetch admin_access_csr into l_tmp;
5163 if admin_access_csr%FOUND
5164 then
5165 x_view_access_flag := 'Y';
5166 else
5167 open am_admin_access_csr;
5168 fetch am_admin_access_csr into l_tmp;
5169 if am_admin_access_csr%FOUND
5170 then
5171 x_view_access_flag := 'Y';
5172 end if;
5173 close am_admin_access_csr;
5174 end if; -- admin_access_csr%FOUND
5175 close admin_access_csr;
5176 end if; -- if p_admin_flag <> 'Y'
5177 end if; -- if l_access_profile_rec.opp_access_profile_value = 'F'
5178 close resource_access_csr;
5179 end if; -- if p_check_access_flag = 'N'
5180 --
5181 -- End of API body.
5182 --
5183
5184 IF l_debug THEN
5185 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5186 'x_view_access_flag: ' || x_view_access_flag);
5187
5188 -- Debug Message
5189 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5190 'Private API: ' || l_api_name || 'end');
5191
5192 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
5193 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
5194 END IF;
5195
5196
5197 FND_MSG_PUB.Count_And_Get
5198 ( p_count => x_msg_count,
5199 p_data => x_msg_data
5200 );
5201
5202 EXCEPTION
5203 WHEN FND_API.G_EXC_ERROR THEN
5204 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
5205 P_MODULE => l_module
5206 ,P_API_NAME => L_API_NAME
5207 ,P_PKG_NAME => G_PKG_NAME
5208 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
5209 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
5210 , P_ROLLBACK_FLAG => 'N'
5211 ,X_MSG_COUNT => X_MSG_COUNT
5212 ,X_MSG_DATA => X_MSG_DATA
5213 ,X_RETURN_STATUS => X_RETURN_STATUS);
5214
5215 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5216 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
5217 P_MODULE => l_module
5218 ,P_API_NAME => L_API_NAME
5219 ,P_PKG_NAME => G_PKG_NAME
5220 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
5221 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
5222 , P_ROLLBACK_FLAG => 'N'
5223 ,X_MSG_COUNT => X_MSG_COUNT
5224 ,X_MSG_DATA => X_MSG_DATA
5225 ,X_RETURN_STATUS => X_RETURN_STATUS);
5226
5227 WHEN OTHERS THEN
5228 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
5229 P_MODULE => l_module
5230 ,P_API_NAME => L_API_NAME
5231 ,P_PKG_NAME => G_PKG_NAME
5232 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
5233 ,P_SQLCODE => SQLCODE
5234 ,P_SQLERRM => SQLERRM
5235 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
5236 , P_ROLLBACK_FLAG => 'N'
5237 ,X_MSG_COUNT => X_MSG_COUNT
5238 ,X_MSG_DATA => X_MSG_DATA
5239 ,X_RETURN_STATUS => X_RETURN_STATUS);
5240
5241 end has_viewOpportunityAccess;
5242
5243
5244
5245 END AS_ACCESS_PVT;