[Home] [Help]
PACKAGE BODY: APPS.AS_ACCESS_PVT
Source
1 PACKAGE BODY AS_ACCESS_PVT as
2 /* $Header: asxvacsb.pls 120.6.12010000.2 2009/02/19 06:09:56 sariff 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 -- below if condition modified for bug 8266750
3025 -- Account manager
3026 if ((nvl(l_salesforce_role_code,'X') <> nvl(l_sales_team_rec.salesforce_role_code,'X'))
3027 and is_account_manager(l_sales_team_rec.salesforce_id,l_sales_team_rec.customer_id))
3028
3029
3030 -- if want to update someone to be account manager
3031 -- or update 'AM' to be not 'AM'
3032 then
3033 if not is_account_manager(p_identity_salesforce_id, l_sales_team_rec.customer_id)
3034 and (nvl(fnd_profile.value('AS_CUST_ACCESS'),'F') <>'F')
3035 -- if login person is not account manager,he can't make other
3036 -- people account manager or not account manager
3037
3038 then
3039 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3040 THEN
3041 FND_MESSAGE.Set_Name('AS','API_NO_ACC_MGR_PRIVILEGE');
3042 FND_MSG_PUB.ADD;
3043 END IF;
3044 RAISE FND_API.G_EXC_ERROR;
3045 end if;
3046 end if;
3047
3048 if l_sales_team_rec.reassign_flag = 'Y'
3049 and (l_sales_team_rec.reassign_reason is null
3050 or l_sales_team_rec.reassign_reason = fnd_api.g_miss_char)
3051 then
3052 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3053 THEN
3054 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
3055 FND_MESSAGE.Set_Token('COLUMN', 'reassign_reason', FALSE);
3056 FND_MSG_PUB.ADD;
3057 END IF;
3058 raise fnd_api.g_exc_error;
3059 end if;
3060 --if l_sales_team_rec.owner_flag = 'Y' and (l_sales_team_rec.sales_lead_id is NOT NULL
3061 -- and l_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
3062
3063 --then
3064 -- unmark_owner_flag(l_sales_team_rec.sales_lead_id);
3065 --end if;
3066
3067 --if l_sales_team_rec.owner_flag = 'Y' and (l_sales_team_rec.lead_id is NOT NULL
3068 -- and l_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
3069
3070 --then
3071 -- unmark_opp_owner_flag(l_sales_team_rec.lead_id);
3072 --end if;
3073
3074
3075 -- Owner is always a team leader
3076 if l_sales_team_rec.owner_flag = 'Y'
3077 then
3078 l_sales_team_rec.team_leader_flag :='Y';
3079 end if;
3080
3081 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)
3082 then
3083 l_sales_team_rec.owner_flag := FND_API.G_MISS_CHAR;
3084 end if;
3085
3086
3087 as_accesses_pkg.update_row(
3088 X_Rowid => l_rowid,
3089 X_Access_Id => l_sales_team_rec.access_id,
3090 X_Last_Update_Date => SYSDATE,
3091 X_Last_Updated_By => FND_GLOBAL.User_Id,
3092 X_Last_Update_Login => FND_GLOBAL.Conc_Login_Id,
3093 X_Access_Type => 'X',
3094 X_Freeze_Flag => l_sales_team_rec.freeze_flag,
3095 X_Reassign_Flag => l_sales_team_rec.reassign_flag,
3096 X_Team_Leader_Flag => l_sales_team_rec.team_leader_flag,
3097 X_Person_Id => l_sales_team_rec.person_id,
3098 X_Customer_Id => l_sales_team_rec.customer_id,
3099 X_Address_Id => l_sales_team_rec.address_id,
3100 X_Salesforce_id => l_sales_team_rec.salesforce_id,
3101 X_Created_Person_Id => l_sales_team_rec.created_person_id,
3102 X_Partner_Customer_id => l_sales_team_rec.partner_customer_id,
3103 X_Partner_Address_id => l_sales_team_rec.partner_address_id,
3104 X_Lead_Id => l_sales_team_rec.lead_id,
3105 X_Freeze_Date => l_sales_team_rec.freeze_date,
3106 X_Reassign_Reason => l_sales_team_rec.reassign_reason,
3107 X_Reassign_request_date => l_sales_team_rec.reassign_request_date,
3108 X_Reassign_requested_person_id => l_sales_team_rec.reassign_requested_person_id,
3109 X_Attribute_Category => l_sales_team_rec.attribute_category,
3110 X_Attribute1 => l_sales_team_rec.attribute1,
3111 X_Attribute2 => l_sales_team_rec.attribute2,
3112 X_Attribute3 => l_sales_team_rec.attribute3,
3113 X_Attribute4 => l_sales_team_rec.attribute4,
3114 X_Attribute5 => l_sales_team_rec.attribute5,
3115 X_Attribute6 => l_sales_team_rec.attribute6,
3116 X_Attribute7 => l_sales_team_rec.attribute7,
3117 X_Attribute8 => l_sales_team_rec.attribute8,
3118 X_Attribute9 => l_sales_team_rec.attribute9,
3119 X_Attribute10 => l_sales_team_rec.attribute10,
3120 X_Attribute11 => l_sales_team_rec.attribute11,
3121 X_Attribute12 => l_sales_team_rec.attribute12,
3122 X_Attribute13 => l_sales_team_rec.attribute13,
3123 X_Attribute14 => l_sales_team_rec.attribute14,
3124 X_Attribute15 => l_sales_team_rec.attribute15,
3125 X_Sales_group_id => l_sales_team_rec.sales_group_id,
3126 X_Sales_lead_id => l_sales_team_rec.sales_lead_id,
3127 X_Internal_update_access => l_internal_update_access,
3128 X_Partner_Cont_Party_Id =>l_sales_team_rec.partner_cont_party_id,
3129 X_owner_flag => l_sales_team_rec.owner_flag,
3130 X_created_by_tap_flag =>l_sales_team_rec.created_by_tap_flag,
3131 X_prm_keep_flag => l_sales_team_rec.prm_keep_flag,
3132 X_Salesforce_Role_Code => l_sales_team_rec.salesforce_role_code,
3133 X_Salesforce_Relationship_Code => l_sales_team_rec.salesforce_relationship_code,
3134 X_contributor_flag =>l_sales_team_rec.contributor_flag); -- Added for ASNB
3135
3136 x_access_id := l_sales_team_rec.access_id;
3137
3138 x_return_status := l_return_status;
3139
3140 if is_sales_lead_owner_row(l_sales_team_rec.access_id)
3141 then
3142 update as_leads_all set object_version_number = nvl(object_version_number,0) + 1, owner_salesforce_id = l_sales_team_rec.salesforce_id,
3143 --owner_sales_group_id = l_sales_team_rec.sales_group_id
3144 owner_sales_group_id = (select sales_group_id from as_accesses_all where access_id = l_sales_team_rec.access_id)
3145 where lead_id = l_sales_team_rec.lead_id;
3146 end if;
3147
3148 if is_sales_lead_owner_row(x_access_id)
3149 and (l_sales_team_rec.sales_lead_id is NOT NULL
3150 and l_sales_team_rec.sales_lead_id <> FND_API.G_MISS_NUM)
3151
3152 then
3153 unmark_owner_flag(l_sales_team_rec.sales_lead_id, x_access_id);
3154 end if;
3155
3156 if is_sales_lead_owner_row(x_access_id)
3157 and (l_sales_team_rec.lead_id is NOT NULL
3158 and l_sales_team_rec.lead_id <> FND_API.G_MISS_NUM)
3159 then
3160 unmark_opp_owner_flag(l_sales_team_rec.lead_id, x_access_id);
3161 end if;
3162
3163 -- Standard check of p_commit.
3164 IF FND_API.To_Boolean ( p_commit )
3165 THEN
3166 COMMIT WORK;
3167 END IF;
3168
3169 -- Standard call to get message count and if count is 1, get message info.
3170 FND_MSG_PUB.Count_And_Get
3171 ( p_count => x_msg_count,
3172 p_data => x_msg_data
3173 );
3174
3175
3176 EXCEPTION
3177
3178 WHEN DUP_VAL_ON_INDEX THEN
3179 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3180 THEN
3181 FND_MESSAGE.Set_Name('AS', 'API_DUP_SALESTEAM');
3182 FND_MSG_PUB.ADD;
3183 x_return_status := FND_API.G_RET_STS_ERROR ;
3184 END IF;
3185
3186
3187 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
3188 ROLLBACK TO UPDATE_SALESTEAM_PVT;
3189 x_return_status := FND_API.G_RET_STS_ERROR ;
3190
3191 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3192 THEN
3193 FND_MESSAGE.Set_Name('AS', 'API_CANNOT_RESERVE_RECORD');
3194 FND_MESSAGE.Set_Token('INFO', 'UPDATE_SALESTEAM', FALSE);
3195 FND_MSG_PUB.Add;
3196 END IF;
3197
3198 WHEN FND_API.G_EXC_ERROR THEN
3199 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3200 P_MODULE => l_module
3201 ,P_API_NAME => L_API_NAME
3202 ,P_PKG_NAME => G_PKG_NAME
3203 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3204 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3205 ,X_MSG_COUNT => X_MSG_COUNT
3206 ,X_MSG_DATA => X_MSG_DATA
3207 ,X_RETURN_STATUS => X_RETURN_STATUS);
3208
3209 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3210 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3211 P_MODULE => l_module
3212 ,P_API_NAME => L_API_NAME
3213 ,P_PKG_NAME => G_PKG_NAME
3214 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3215 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3216 ,X_MSG_COUNT => X_MSG_COUNT
3217 ,X_MSG_DATA => X_MSG_DATA
3218 ,X_RETURN_STATUS => X_RETURN_STATUS);
3219
3220 WHEN OTHERS THEN
3221 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3222 P_MODULE => l_module
3223 ,P_API_NAME => L_API_NAME
3224 ,P_PKG_NAME => G_PKG_NAME
3225 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
3226 ,P_SQLCODE => SQLCODE
3227 ,P_SQLERRM => SQLERRM
3228 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3229 ,X_MSG_COUNT => X_MSG_COUNT
3230 ,X_MSG_DATA => X_MSG_DATA
3231 ,X_RETURN_STATUS => X_RETURN_STATUS);
3232
3233
3234 end Update_SalesTeam;
3235
3236
3237 function is_party_person (p_customer_id in number)
3238 RETURN BOOLEAN IS
3239 cursor get_party_person_csr is
3240 select 'x'
3241 from hz_parties
3242 where party_id = p_customer_id
3243 and party_type = 'PERSON';
3244 l_tmp varchar2(1);
3245 begin
3246 open get_party_person_csr;
3247 fetch get_party_person_csr into l_tmp;
3248 if get_party_person_csr%FOUND
3249 then
3250 close get_party_person_csr;
3251 return true;
3252 else
3253 close get_party_person_csr;
3254 return false;
3255 end if;
3256 end is_party_person;
3257
3258 -- private procedure which is called in has_viewCustomerAccess
3259 -- person's access will be based on the access privilege of related organization
3260 -- this procedure only handle the case of as_cust_profile = 'T'. Other cases are
3261 -- handled in has_viewCustomerAccess
3262 procedure has_viewPersonAccess
3263 ( p_admin_flag IN VARCHAR2
3264 ,p_admin_group_id IN NUMBER
3265 ,p_person_id IN NUMBER
3266 ,p_customer_id IN NUMBER
3267 ,p_identity_salesforce_id IN NUMBER
3268 ,x_view_access_flag OUT NOCOPY VARCHAR2
3269 ) is
3270
3271 l_tmp varchar2(1);
3272 l_person_id number;
3273
3274 cursor resource_access_csr is
3275
3276 select 'X'
3277 from as_accesses_all a, hz_parties p,hz_relationships rel
3278 where a.customer_id = rel.object_id
3279 and rel.object_id = p.party_id
3280 and p.party_type in ('ORGANIZATION','PERSON')
3281 and rel.subject_id = p_customer_id
3282 and a.salesforce_id = p_identity_salesforce_id
3283 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3284 AND rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3285 and rownum = 1;
3286
3287
3288 cursor manager_access_csr(p_resource_id number) is
3289
3290 select 'X'
3291 from as_accesses_all a, as_rpt_managers_v rm,
3292 hz_parties p,hz_relationships rel
3293 where a.customer_id = rel.object_id
3294 and rel.object_id = p.party_id
3295 and p.party_type in ('ORGANIZATION','PERSON')
3296 and a.salesforce_id = rm.resource_id
3297 and rel.subject_id = p_customer_id
3298 and rm.parent_resource_id = p_resource_id
3299 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3300 AND rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3301 and rownum = 1;
3302
3303 cursor admin_access_csr is
3304
3305 select 'X'
3306 from hz_parties p, hz_relationships rel
3307 where rel.object_id = p.party_id
3308 and p.party_type in ('ORGANIZATION','PERSON')
3309 and rel.subject_id = p_customer_id
3310 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3311 and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3312 and exists (select 1 from as_accesses_all a ,as_rpt_admins_v rm
3313 where a.salesforce_id = rm.salesforce_id
3314 and a.customer_id = rel.object_id
3315 and rm.parent_sales_group_id = p_admin_group_id)
3316 and rownum = 1;
3317
3318 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3319 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_viewPersonAccess';
3320
3321 begin
3322
3323
3324 -- Debug Message
3325 IF l_debug THEN
3326 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3327 'has_viewPersonAccess: start ');
3328
3329 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3330 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3331 END IF;
3332
3333 l_person_id := p_person_id;
3334 x_view_access_flag := 'N';
3335 open resource_access_csr;
3336 fetch resource_access_csr into l_tmp;
3337 if (resource_access_csr%FOUND)
3338 -- access record exists for the login user itself
3339 then
3340 x_view_access_flag := 'Y';
3341 elsif nvl(p_admin_flag,'N') <> 'Y'
3342 then
3343 open manager_access_csr(p_identity_salesforce_id);
3344 fetch manager_access_csr into l_tmp;
3345 if (manager_access_csr%FOUND)
3346 then
3347 x_view_access_flag := 'Y';
3348 end if; -- mgr
3349 close manager_access_csr;
3350 else
3351 open admin_access_csr;
3352 fetch admin_access_csr into l_tmp;
3353 if admin_access_csr%FOUND
3354 then
3355 x_view_access_flag := 'Y';
3356 end if; -- admin
3357 close admin_access_csr;
3358 end if;
3359 close resource_access_csr;
3360
3361 IF l_debug THEN
3362 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3363 'x_view_access_flag: ' || x_view_access_flag);
3364 -- Debug Message
3365
3366 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3367 'has_viewPersonAccess: end ');
3368
3369
3370 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
3371 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3372 END IF;
3373
3374 end has_viewPersonAccess;
3375
3376 -- private procedure which is called in has_updateCustomerAccess
3377 -- person's access will be based on the access privilege of related organization
3378 -- this procedure only handle the case of as_cust_profile in ('P', 'T'). Other cases are
3379 -- handled in has_updateCustomerAccess
3380
3381 procedure has_updatePersonAccess
3382 ( p_access_profile_rec IN as_access_pub.access_profile_rec_type
3383 ,p_admin_flag IN VARCHAR2
3384 ,p_admin_group_id IN NUMBER
3385 ,p_person_id IN NUMBER
3386 ,p_customer_id IN NUMBER
3387 ,p_identity_salesforce_id IN NUMBER
3388 ,x_update_access_flag OUT NOCOPY VARCHAR2
3389 )is
3390
3391 l_tmp varchar2(1);
3392 l_person_id number;
3393
3394 cursor resource_access_csr is
3395
3396 select 'X'
3397 from as_accesses_all a, hz_parties p,hz_relationships rel
3398 where a.customer_id = rel.object_id
3399 and rel.object_id = p.party_id
3400 and p.party_type in ('ORGANIZATION','PERSON')
3401 and rel.subject_id = p_customer_id
3402 and a.salesforce_id = p_identity_salesforce_id
3403 and a.lead_id is null
3404 and a.sales_lead_id is null
3405 and a.team_leader_flag = 'Y'
3406 and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3407 AND rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3408 and rownum = 1;
3409
3410 cursor manager_access_csr(p_resource_id number) is
3411 select 'X'
3412 from as_accesses_all a,
3413 hz_parties p, hz_relationships rel
3414 where a.customer_id = rel.object_id
3415 and rel.object_id = p.party_id
3416 and p.party_type in ('ORGANIZATION','PERSON')
3417 and rel.subject_id = p_customer_id
3418 and a.lead_id is null
3419 and a.sales_lead_id is null
3420 and rel.subject_table_name = 'HZ_PARTIES'
3421 and rel.object_table_name = 'HZ_PARTIES'
3422 and (EXISTS (select 'X'
3423 from as_rpt_managers_v rm
3424 where a.salesforce_id = rm.resource_id
3425 and rm.parent_resource_id = p_resource_id
3426 and ((rm.parent_resource_id = rm.resource_id
3427 and a.team_leader_flag = 'Y')
3428 or (rm.parent_resource_id <> rm.resource_id))));
3429
3430 cursor mgr_i_access_csr(p_resource_id number) is
3431 select 'X'
3432 from as_accesses_all a, hz_parties p, hz_relationships rel
3433 where a.customer_id = rel.object_id
3434 and rel.object_id = p.party_id
3435 and p.party_type in ('ORGANIZATION','PERSON')
3436 and rel.subject_id = p_customer_id
3437 and a.lead_id is null
3438 and a.sales_lead_id is null
3439 and a.team_leader_flag = 'Y'
3440 and (EXISTS (select 'x'
3441 from as_rpt_managers_v rm
3442 where a.salesforce_id = rm.resource_id
3443 and rm.parent_resource_id = p_resource_id));
3444
3445 cursor admin_access_csr is
3446 select 'X'
3447 from as_accesses_all a, hz_parties p, hz_relationships rel
3448 where a.customer_id = rel.object_id
3449 and rel.object_id = p.party_id
3450 and p.party_type in ('ORGANIZATION','PERSON')
3451 and rel.subject_id = p_customer_id
3452 and a.lead_id is null
3453 and a.sales_lead_id is null
3454 and rel.object_table_name = 'HZ_PARTIES'
3455 and rel.subject_table_name = 'HZ_PARTIES'
3456 and EXISTS (select 'x'
3457 from as_rpt_admins_v rm
3458 where a.salesforce_id = rm.salesforce_id
3459 and rm.parent_sales_group_id = p_admin_group_id
3460 and ((rm.salesforce_id = p_identity_salesforce_id
3461 and a.team_leader_flag = 'Y')
3462 or (rm.salesforce_id <> p_identity_salesforce_id)));
3463
3464 cursor admin_i_access_csr is
3465 select 'x'
3466 from as_accesses_all a, hz_parties p, hz_relationships rel
3467 where a.customer_id = rel.object_id
3468 and rel.object_id = p.party_id
3469 and p.party_type in ('ORGANIZATION', 'PERSON')
3470 and rel.subject_id = p_customer_id
3471 and a.lead_id is null
3472 and a.sales_lead_id is null
3473 and a.team_leader_flag = 'Y'
3474 and rel.object_table_name = 'HZ_PARTIES'
3475 and rel.subject_table_name = 'HZ_PARTIES'
3476 and EXISTS (select 'x'
3477 from as_rpt_admins_v rm
3478 where a.salesforce_id = rm.salesforce_id
3479 and rm.parent_sales_group_id = p_admin_group_id);
3480
3481 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3482 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_updatePersonAccess';
3483 begin
3484
3485 -- Debug Message
3486 IF l_debug THEN
3487 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updatePersonAccess start');
3488
3489 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3490 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3491 END IF;
3492
3493 l_person_id := p_person_id;
3494 -- Initialize access flag to 'N'
3495 x_update_access_flag := 'N';
3496
3497 open resource_access_csr;
3498 fetch resource_access_csr into l_tmp;
3499 if (resource_access_csr%FOUND)
3500 -- access record exists for the login user itself
3501 then
3502 x_update_access_flag := 'Y';
3503 else -- access record doesn't exist for the login user
3504
3505 if nvl(p_admin_flag,'N') <> 'Y' -- mgr
3506 then if p_access_profile_rec.mgr_update_profile_value = 'U'
3507 then
3508 open manager_access_csr(p_identity_salesforce_id);
3509 fetch manager_access_csr into l_tmp;
3510 if (manager_access_csr%FOUND)
3511 then
3512 x_update_access_flag := 'Y';
3513 end if;
3514 close manager_access_csr;
3515 elsif p_access_profile_rec.mgr_update_profile_value = 'I'
3516 then
3517 open mgr_i_access_csr(p_identity_salesforce_id);
3518 fetch mgr_i_access_csr into l_tmp;
3519 if(mgr_i_access_csr%FOUND)
3520 then
3521 x_update_access_flag := 'Y';
3522 end if;
3523 close mgr_i_access_csr;
3524 end if; -- mgr
3525 else if p_access_profile_rec.admin_update_profile_value = 'U'
3526 then
3527 open admin_access_csr;
3528 fetch admin_access_csr into l_tmp;
3529 if (admin_access_csr%FOUND)
3530 then
3531 x_update_access_flag := 'Y';
3532 end if;
3533 close admin_access_csr;
3534 elsif p_access_profile_rec.admin_update_profile_value = 'I'
3535 then
3536 open admin_i_access_csr;
3537 fetch admin_i_access_csr into l_tmp;
3538 if(admin_i_access_csr%FOUND)
3539 then
3540 x_update_access_flag := 'Y';
3541 end if;
3542 close admin_i_access_csr;
3543 end if; -- admin
3544 end if; --nvl(p_admin_flag,'N') <> 'Y' -- mgr
3545 end if;
3546 close resource_access_csr;
3547
3548 -- Debug Message
3549 IF l_debug THEN
3550 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3551 'has_updatePersonAccess end');
3552
3553 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
3554 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3555 END IF;
3556
3557 end has_updatePersonAccess;
3558
3559
3560 procedure has_viewCustomerAccess
3561 ( p_api_version_number IN NUMBER
3562 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3563 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3564 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
3565 ,p_admin_flag IN VARCHAR2
3566 ,p_admin_group_id IN NUMBER
3567 ,p_person_id IN NUMBER
3568 ,p_customer_id IN NUMBER
3569 ,p_check_access_flag IN VARCHAR2
3570 ,p_identity_salesforce_id IN NUMBER
3571 ,p_partner_cont_party_id IN NUMBER
3572 ,x_return_status OUT NOCOPY VARCHAR2
3573 ,x_msg_count OUT NOCOPY NUMBER
3574 ,x_msg_data OUT NOCOPY VARCHAR2
3575 ,x_view_access_flag OUT NOCOPY VARCHAR2
3576 ) is
3577 l_api_name CONSTANT VARCHAR2(30) := 'has_viewCustomerAccess';
3578 l_api_version_number CONSTANT NUMBER := 2.0;
3579 l_tmp varchar2(1);
3580 l_person_id number;
3581 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
3582
3583 cursor resource_access_csr is
3584
3585 select 'X'
3586 from as_accesses_all
3587 where customer_id = p_customer_id
3588 and salesforce_id = p_identity_salesforce_id;
3589
3590
3591 cursor manager_access_csr(p_resource_id number) is
3592 select 'X'
3593 from as_accesses_all a
3594 where customer_id = p_customer_id
3595 and (EXISTS (select 'X'
3596 from as_rpt_managers_v rm
3597 where a.salesforce_id = rm.resource_id
3598 and rm.parent_resource_id = p_resource_id));
3599
3600 cursor admin_access_csr is
3601 select 'X'
3602 from as_accesses_all a
3603 where customer_id = p_customer_id
3604 and EXISTS (select 'x'
3605 from as_rpt_admins_v rm
3606 where a.salesforce_id = rm.salesforce_id
3607 and rm.parent_sales_group_id = p_admin_group_id);
3608 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3609 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_viewCustomerAccess';
3610
3611 begin
3612
3613 -- Standard call to check for call compatibility.
3614 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3615 p_api_version_number,
3616 l_api_name,
3617 G_PKG_NAME)
3618 THEN
3619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3620 END IF;
3621
3622 -- Initialize message list if p_init_msg_list is set to TRUE.
3623 IF FND_API.to_Boolean( p_init_msg_list )
3624 THEN
3625 FND_MSG_PUB.initialize;
3626 END IF;
3627
3628 -- Debug Message
3629 IF l_debug THEN
3630 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3631 'Private API: ' || l_api_name || 'start');
3632
3633 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3634 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3635 END IF;
3636
3637 -- Initialize API return status to SUCCESS
3638 x_return_status := FND_API.G_RET_STS_SUCCESS;
3639
3640 IF l_debug THEN
3641 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3642 'customer_id: ' || p_customer_id);
3643 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3644 'ident salesforce_id: ' || p_identity_salesforce_id);
3645 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3646 'admin_group_id: ' || p_admin_group_id);
3647 END IF;
3648
3649 -- Initialize access flag to 'N'
3650 x_view_access_flag := 'N';
3651
3652
3653 if p_check_access_flag = 'N'
3654 then
3655 x_view_access_flag := 'Y';
3656 else -- if p_check_access_flag = 'Y'
3657 --partner security checking. Return point
3658 if p_partner_cont_party_id is not null
3659 and p_partner_cont_party_id <> FND_API.G_MISS_NUM
3660 then
3661 open resource_access_csr;
3662 fetch resource_access_csr into l_tmp;
3663 if (resource_access_csr%FOUND)
3664 then
3665 x_view_access_flag := 'Y';
3666 close resource_access_csr;
3667 return;
3668 end if;
3669 close resource_access_csr;
3670 end if;
3671 /*
3672 if p_person_id is null or p_person_id = fnd_api.g_miss_num
3673 then
3674 get_person_id(p_identity_salesforce_id, l_person_id);
3675 else
3676 l_person_id := p_person_id;
3677 end if;
3678 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3679 'person id: ' || l_person_id);
3680 */
3681 l_access_profile_rec := p_access_profile_rec;
3682 get_accessProfileValues(l_access_profile_rec);
3683
3684 if l_access_profile_rec.cust_access_profile_value in ('F', 'P')
3685 then
3686 x_view_access_flag := 'Y';
3687 -- elsif l_access_profile_rec.lead_access_profile_value = 'T'
3688 -- and l_access_profile_rec.opp_access_profile_value = 'T'
3689 -- then
3690 -- Fix bug 1623713
3691 else
3692 if nvl(p_admin_flag,'N') <> 'Y'
3693 then
3694 open manager_access_csr(p_identity_salesforce_id);
3695 fetch manager_access_csr into l_tmp;
3696 if (manager_access_csr%FOUND)
3697 then
3698 x_view_access_flag := 'Y';
3699 end if; -- mgr
3700 close manager_access_csr;
3701 else
3702 open admin_access_csr;
3703 fetch admin_access_csr into l_tmp;
3704 if admin_access_csr%FOUND
3705 then
3706 x_view_access_flag := 'Y';
3707 end if; -- admin
3708 close admin_access_csr;
3709 end if; -- profile combination is ('T', don't care, don't care)
3710 end if; -- if l_access_profile_rec.cust_access_profile_value in ('F','P')
3711
3712 if x_view_access_flag = 'N' and is_party_person(p_customer_id)
3713 then
3714 has_viewPersonAccess(
3715 p_admin_flag => p_admin_flag
3716 ,p_admin_group_id => p_admin_group_id
3717 ,p_person_id => p_person_id
3718 ,p_customer_id => p_customer_id
3719 ,p_identity_salesforce_id => p_identity_salesforce_id
3720 ,x_view_access_flag => x_view_access_flag
3721 );
3722 end if;
3723 end if; -- if p_check_access_flag = 'N'
3724
3725 IF l_debug THEN
3726 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3727 'x_view_access_flag: ' || x_view_access_flag);
3728 -- Debug Message
3729 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3730 'Private API: ' || l_api_name || 'end');
3731
3732 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
3733 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3734 END IF;
3735
3736 FND_MSG_PUB.Count_And_Get
3737 ( p_count => x_msg_count,
3738 p_data => x_msg_data
3739 );
3740
3741 EXCEPTION
3742 WHEN FND_API.G_EXC_ERROR THEN
3743 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3744 P_MODULE => l_module
3745 ,P_API_NAME => L_API_NAME
3746 ,P_PKG_NAME => G_PKG_NAME
3747 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3748 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3749 , P_ROLLBACK_FLAG => 'N'
3750 ,X_MSG_COUNT => X_MSG_COUNT
3751 ,X_MSG_DATA => X_MSG_DATA
3752 ,X_RETURN_STATUS => X_RETURN_STATUS);
3753
3754 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3755 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3756 P_MODULE => l_module
3757 ,P_API_NAME => L_API_NAME
3758 ,P_PKG_NAME => G_PKG_NAME
3759 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3760 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3761 , P_ROLLBACK_FLAG => 'N'
3762 ,X_MSG_COUNT => X_MSG_COUNT
3763 ,X_MSG_DATA => X_MSG_DATA
3764 ,X_RETURN_STATUS => X_RETURN_STATUS);
3765
3766 WHEN OTHERS THEN
3767 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3768 P_MODULE => l_module
3769 ,P_API_NAME => L_API_NAME
3770 ,P_PKG_NAME => G_PKG_NAME
3771 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
3772 ,P_SQLCODE => SQLCODE
3773 ,P_SQLERRM => SQLERRM
3774 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3775 , P_ROLLBACK_FLAG => 'N'
3776 ,X_MSG_COUNT => X_MSG_COUNT
3777 ,X_MSG_DATA => X_MSG_DATA
3778 ,X_RETURN_STATUS => X_RETURN_STATUS);
3779
3780 end has_viewCustomerAccess;
3781
3782 procedure has_updateCustomerAccess
3783 ( p_api_version_number IN NUMBER
3784 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3785 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3786 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
3787 ,p_admin_flag IN VARCHAR2
3788 ,p_admin_group_id IN NUMBER
3789 ,p_person_id IN NUMBER
3790 ,p_customer_id IN NUMBER
3791 ,p_check_access_flag IN VARCHAR2
3792 ,p_identity_salesforce_id IN NUMBER
3793 ,p_partner_cont_party_id IN NUMBER
3794 ,x_return_status OUT NOCOPY VARCHAR2
3795 ,x_msg_count OUT NOCOPY NUMBER
3796 ,x_msg_data OUT NOCOPY VARCHAR2
3797 ,x_update_access_flag OUT NOCOPY VARCHAR2
3798 )is
3799 l_api_name CONSTANT VARCHAR2(30) := 'has_updateCustomerAccess';
3800 l_api_version_number CONSTANT NUMBER := 2.0;
3801 l_tmp varchar2(1);
3802 l_person_id number;
3803 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
3804
3805 cursor resource_access_csr is
3806
3807 select 'X'
3808 from as_accesses_all
3809 where customer_id = p_customer_id
3810 and lead_id is null
3811 and sales_lead_id is null
3812 and team_leader_flag = 'Y'
3813 and salesforce_id = p_identity_salesforce_id;
3814
3815
3816 cursor manager_access_csr(p_resource_id number) is
3817 select 'X'
3818 from as_accesses_all a
3819 where customer_id = p_customer_id
3820 and lead_id is null
3821 and sales_lead_id is null
3822 and (EXISTS (select 'X'
3823 from as_rpt_managers_v rm
3824 where a.salesforce_id = rm.resource_id
3825 and rm.parent_resource_id = p_resource_id
3826 and ((rm.parent_resource_id = rm.resource_id
3827 and a.team_leader_flag = 'Y')
3828 or (rm.parent_resource_id <> rm.resource_id))));
3829
3830 cursor mgr_i_access_csr(p_resource_id number) is
3831 select 'X'
3832 from as_accesses_all a
3833 where a.customer_id = p_customer_id
3834 and lead_id is null
3835 and sales_lead_id is null
3836 and a.team_leader_flag = 'Y'
3837 and (EXISTS (select 'x'
3838 from as_rpt_managers_v rm
3839 where a.salesforce_id = rm.resource_id
3840 and rm.parent_resource_id = p_resource_id));
3841
3842 cursor admin_access_csr is
3843 select 'X'
3844 from as_accesses_all a
3845 where customer_id = p_customer_id
3846 and lead_id is null
3847 and sales_lead_id is null
3848 and EXISTS (select 'x'
3849 from as_rpt_admins_v rm
3850 where a.salesforce_id = rm.salesforce_id
3851 and rm.parent_sales_group_id = p_admin_group_id
3852 and ((rm.salesforce_id = p_identity_salesforce_id
3853 and a.team_leader_flag = 'Y')
3854 or (rm.salesforce_id <> p_identity_salesforce_id)));
3855
3856 cursor admin_i_access_csr is
3857 select 'x'
3858 from as_accesses_all a
3859 where a.customer_id = p_customer_id
3860 and lead_id is null
3861 and sales_lead_id is null
3862 and a.team_leader_flag = 'Y'
3863 and EXISTS (select 'x'
3864 from as_rpt_admins_v rm
3865 where a.salesforce_id = rm.salesforce_id
3866 and rm.parent_sales_group_id = p_admin_group_id);
3867
3868 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3869 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_updateCustomerAccess';
3870
3871 begin
3872 -- Standard call to check for call compatibility.
3873 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3874 p_api_version_number,
3875 l_api_name,
3876 G_PKG_NAME)
3877 THEN
3878 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3879 END IF;
3880
3881 -- Initialize message list if p_init_msg_list is set to TRUE.
3882 IF FND_API.to_Boolean( p_init_msg_list )
3883 THEN
3884 FND_MSG_PUB.initialize;
3885 END IF;
3886
3887 -- Debug Message
3888 IF l_debug THEN
3889 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3890 'Private API: ' || l_api_name || 'start');
3891
3892 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3893 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3894 END IF;
3895
3896 -- Initialize API return status to SUCCESS
3897 x_return_status := FND_API.G_RET_STS_SUCCESS;
3898
3899 IF l_debug THEN
3900 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3901 'customer_id: ' || p_customer_id);
3902 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3903 'ident salesforce_id: ' || p_identity_salesforce_id);
3904 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3905 'admin_group_id: ' || p_admin_group_id);
3906 END IF;
3907
3908 --
3909 -- API body
3910 --
3911 -- Initialize access flag to 'N'
3912 x_update_access_flag := 'N';
3913
3914 if p_check_access_flag = 'N'
3915 then
3916 x_update_access_flag := 'Y';
3917 else -- if p_check_access_flag = 'Y'
3918 open resource_access_csr;
3919 fetch resource_access_csr into l_tmp;
3920 if p_partner_cont_party_id is not null
3921 and p_partner_cont_party_id <> FND_API.G_MISS_NUM
3922 then
3923 if (resource_access_csr%FOUND)
3924 then
3925 x_update_access_flag := 'Y';
3926 close resource_access_csr;
3927 return;
3928 end if;
3929 end if;
3930 /*
3931 if p_person_id is null or p_person_id = fnd_api.g_miss_num
3932 then
3933 get_person_id(p_identity_salesforce_id, l_person_id);
3934 else
3935 l_person_id := p_person_id;
3936 end if;
3937 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3938 'person id: ' || l_person_id);
3939 */
3940 l_access_profile_rec := p_access_profile_rec;
3941 get_accessProfileValues(l_access_profile_rec);
3942
3943 if l_access_profile_rec.cust_access_profile_value = 'F' then
3944 x_update_access_flag := 'Y';
3945 elsif (resource_access_csr%FOUND)
3946 -- profile is 'P' or 'T' and access record exists for the login
3947 -- user itself
3948 then
3949 x_update_access_flag := 'Y';
3950 else -- profile is 'P' or 'T' and access record doesn't exist for the
3951 -- login user
3952
3953 if nvl(p_admin_flag,'N') <> 'Y' -- mgr
3954 then
3955 if l_access_profile_rec.mgr_update_profile_value = 'U'
3956 then
3957 open manager_access_csr(p_identity_salesforce_id);
3958 fetch manager_access_csr into l_tmp;
3959 if (manager_access_csr%FOUND)
3960 then
3961 x_update_access_flag := 'Y';
3962 end if;
3963 close manager_access_csr;
3964 elsif l_access_profile_rec.mgr_update_profile_value = 'I'
3965 then
3966 open mgr_i_access_csr(p_identity_salesforce_id);
3967 fetch mgr_i_access_csr into l_tmp;
3968 if(mgr_i_access_csr%FOUND)
3969 then
3970 x_update_access_flag := 'Y';
3971 end if;
3972 close mgr_i_access_csr;
3973 end if; -- mgr
3974 else
3975 if l_access_profile_rec.admin_update_profile_value = 'U'
3976 then
3977 open admin_access_csr;
3978 fetch admin_access_csr into l_tmp;
3979 if (admin_access_csr%FOUND)
3980 then
3981 x_update_access_flag := 'Y';
3982 end if;
3983 close admin_access_csr;
3984 elsif l_access_profile_rec.admin_update_profile_value = 'I'
3985 then
3986 open admin_i_access_Csr;
3987 fetch admin_i_access_csr into l_tmp;
3988 if(admin_i_access_csr%FOUND)
3989 then
3990 x_update_access_flag := 'Y';
3991 end if;
3992 close admin_i_access_csr;
3993 end if; -- admin
3994 end if; -- (resource_access_csr%FOUND)
3995 end if;
3996 close resource_access_csr;
3997
3998 if x_update_access_flag = 'N' and is_party_person(p_customer_id)
3999 then
4000 has_updatePersonAccess(
4001 p_access_profile_rec => l_access_profile_rec
4002 ,p_admin_flag => p_admin_flag
4003 ,p_admin_group_id => p_admin_group_id
4004 ,p_person_id => p_person_id
4005 ,p_customer_id => p_customer_id
4006 ,p_identity_salesforce_id => p_identity_salesforce_id
4007 ,x_update_access_flag => x_update_access_flag
4008 );
4009 end if;
4010 end if; --if p_check_access_flag = 'N'
4011
4012 IF l_debug THEN
4013 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4014 'x_update_access_flag: ' || x_update_access_flag);
4015
4016 -- Debug Message
4017 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4018 'Private API: ' || l_api_name || 'end');
4019
4020 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
4021 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4022 END IF;
4023
4024
4025 FND_MSG_PUB.Count_And_Get
4026 ( p_count => x_msg_count,
4027 p_data => x_msg_data
4028 );
4029
4030 EXCEPTION
4031 WHEN FND_API.G_EXC_ERROR THEN
4032 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4033 P_MODULE => l_module
4034 ,P_API_NAME => L_API_NAME
4035 ,P_PKG_NAME => G_PKG_NAME
4036 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4037 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4038 , P_ROLLBACK_FLAG => 'N'
4039 ,X_MSG_COUNT => X_MSG_COUNT
4040 ,X_MSG_DATA => X_MSG_DATA
4041 ,X_RETURN_STATUS => X_RETURN_STATUS);
4042
4043 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4044 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4045 P_MODULE => l_module
4046 ,P_API_NAME => L_API_NAME
4047 ,P_PKG_NAME => G_PKG_NAME
4048 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
4049 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4050 , P_ROLLBACK_FLAG => 'N'
4051 ,X_MSG_COUNT => X_MSG_COUNT
4052 ,X_MSG_DATA => X_MSG_DATA
4053 ,X_RETURN_STATUS => X_RETURN_STATUS);
4054
4055 WHEN OTHERS THEN
4056 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4057 P_MODULE => l_module
4058 ,P_API_NAME => L_API_NAME
4059 ,P_PKG_NAME => G_PKG_NAME
4060 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
4061 ,P_SQLCODE => SQLCODE
4062 ,P_SQLERRM => SQLERRM
4063 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4064 , P_ROLLBACK_FLAG => 'N'
4065 ,X_MSG_COUNT => X_MSG_COUNT
4066 ,X_MSG_DATA => X_MSG_DATA
4067 ,X_RETURN_STATUS => X_RETURN_STATUS);
4068
4069 end has_updateCustomerAccess;
4070
4071
4072 procedure has_updateLeadAccess
4073 ( p_api_version_number IN NUMBER
4074 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4075 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4076 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
4077 ,p_admin_flag IN VARCHAR2
4078 ,p_admin_group_id IN NUMBER
4079 ,p_person_id IN NUMBER
4080 ,p_sales_lead_id IN NUMBER
4081 ,p_check_access_flag IN VARCHAR2
4082 ,p_identity_salesforce_id IN NUMBER
4083 ,p_partner_cont_party_id IN NUMBER
4084 ,x_return_status OUT NOCOPY VARCHAR2
4085 ,x_msg_count OUT NOCOPY NUMBER
4086 ,x_msg_data OUT NOCOPY VARCHAR2
4087 ,x_update_access_flag OUT NOCOPY VARCHAR2
4088 ) is
4089 l_api_name CONSTANT VARCHAR2(30) := 'has_updateLeadAccess';
4090 l_api_version_number CONSTANT NUMBER := 2.0;
4091 l_tmp varchar2(1);
4092 l_person_id number;
4093 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
4094
4095 cursor resource_access_csr is
4096 select 'X'
4097 from as_accesses_all a
4098 where a.sales_lead_id = p_sales_lead_id
4099 and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
4100 and a.salesforce_id = p_identity_salesforce_id;
4101
4102 cursor manager_access_csr(p_resource_id number) is
4103
4104 select 'X'
4105 from as_accesses_all a
4106 where a.sales_lead_id = p_sales_lead_id
4107 and EXISTS (select 'x'
4108 from as_rpt_managers_v rm
4109 where a.salesforce_id = rm.resource_id
4110 and rm.parent_resource_id = p_resource_id
4111 and (rm.parent_resource_id = rm.resource_id
4112 and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
4113 or (rm.parent_resource_id <> rm.resource_id)));
4114
4115 cursor mgr_i_access_csr(p_resource_id number) is
4116 select 'X'
4117 from as_accesses_all a
4118 where a.sales_lead_id = p_sales_lead_id
4119 and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
4120 and (EXISTS (select 'x'
4121 from as_rpt_managers_v rm
4122 where a.salesforce_id = rm.resource_id
4123 and rm.parent_resource_id = p_resource_id));
4124
4125 cursor admin_access_csr is
4126 select 'x'
4127 from as_accesses_all a
4128 where a.sales_lead_id = p_sales_lead_id
4129 and EXISTS (select 'x'
4130 from as_rpt_admins_v rm
4131 where a.salesforce_id = rm.salesforce_id
4132 and rm.parent_sales_group_id = p_admin_group_id
4133 and (rm.salesforce_id = p_identity_salesforce_id
4134 and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
4135 or (rm.salesforce_id <> p_identity_salesforce_id)));
4136
4137 cursor admin_i_access_csr is
4138 select 'x'
4139 from as_accesses_all a
4140 where a.sales_lead_id = p_sales_lead_id
4141 and (a.team_leader_flag = 'Y' or a.owner_flag = 'Y')
4142 and EXISTS (select 'x'
4143 from as_rpt_admins_v rm
4144 where a.salesforce_id = rm.salesforce_id
4145 and rm.parent_sales_group_id = p_admin_group_id);
4146
4147 cursor am_mgr_access_csr(p_resource_id number) is
4148 select 'x'
4149 from as_sales_leads lead, as_accesses_all a, as_rpt_managers_v rm
4150 where lead.customer_id = a.customer_id
4151 and a.salesforce_id = rm.resource_id
4152 and a.salesforce_role_code = 'AM'
4153 and rm.parent_resource_id = p_resource_id
4154 and lead.sales_lead_id = p_sales_lead_id;
4155
4156 cursor am_admin_access_csr is
4157 select 'x'
4158 from as_sales_leads lead, as_accesses_all a, as_rpt_admins_v rm
4159 where lead.customer_id = a.customer_id
4160 and a.salesforce_id = rm.salesforce_id
4161 and a.salesforce_role_code = 'AM'
4162 and rm.parent_sales_group_id = p_admin_group_id
4163 and lead.sales_lead_id = p_sales_lead_id;
4164
4165 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
4166 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_updateLeadAccess';
4167 begin
4168 -- Standard call to check for call compatibility.
4169 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4170 p_api_version_number,
4171 l_api_name,
4172 G_PKG_NAME)
4173 THEN
4174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4175 END IF;
4176
4177 -- Initialize message list if p_init_msg_list is set to TRUE.
4178 IF FND_API.to_Boolean( p_init_msg_list )
4179 THEN
4180 FND_MSG_PUB.initialize;
4181 END IF;
4182
4183 -- Debug Message
4184 IF l_debug THEN
4185 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4186 'Private API: ' || l_api_name || 'start');
4187
4188 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4189 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4190 END IF;
4191
4192 -- Initialize API return status to SUCCESS
4193 x_return_status := FND_API.G_RET_STS_SUCCESS;
4194
4195 IF l_debug THEN
4196 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4197 'p_sales_lead_id: ' || p_sales_lead_id);
4198 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4199 'ident salesforce_id: ' || p_identity_salesforce_id);
4200 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4201 'p_admin_group_id: ' || p_admin_group_id);
4202 END IF;
4203
4204 --
4205 -- API body
4206 --
4207
4208 -- Initialize access flag to 'N'
4209 x_update_access_flag := 'N';
4210
4211 if p_check_access_flag = 'N'
4212 then
4213 x_update_access_flag := 'Y';
4214 else -- if p_check_access_flag = 'Y'
4215 -- PRM security
4216 open resource_access_csr;
4217 fetch resource_access_csr into l_tmp;
4218 /* if p_partner_cont_party_id is not null
4219 and p_partner_cont_party_id <> FND_API.G_MISS_NUM
4220 then
4221 if (resource_access_csr%FOUND)
4222 then
4223 x_update_access_flag := 'Y';
4224 close resource_access_csr;
4225 return;
4226 end if;
4227 end if; */
4228 /*
4229 if p_person_id is null or p_person_id = fnd_api.g_miss_num
4230 then
4231 get_person_id(p_identity_salesforce_id, l_person_id);
4232 else
4233 l_person_id := p_person_id;
4234 end if;
4235 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4236 'person id: ' || l_person_id);
4237 */
4238 l_access_profile_rec := p_access_profile_rec;
4239 get_accessProfileValues(l_access_profile_rec);
4240
4241 if l_access_profile_rec.lead_access_profile_value = 'F'
4242 then
4243 x_update_access_flag := 'Y';
4244 elsif resource_access_csr%FOUND
4245 then
4246 x_update_access_flag := 'Y';
4247 else
4248 if nvl(p_admin_flag,'N') <> 'Y'
4249 then
4250 if l_access_profile_rec.mgr_update_profile_value = 'U'
4251 then
4252 open manager_access_csr(p_identity_salesforce_id);
4253 fetch manager_access_csr into l_tmp;
4254 if manager_access_csr%FOUND
4255 -- First check if mgr's subordinate
4256 -- which are not 'AM'
4257 then
4258 x_update_access_flag := 'Y';
4259 else -- if mgr's subordinate which are 'AM'
4260 open am_mgr_access_csr(p_identity_salesforce_id);
4261 fetch am_mgr_access_csr into l_tmp;
4262 if am_mgr_access_csr%FOUND
4263 then
4264 x_update_access_flag := 'Y';
4265 end if;
4266 close am_mgr_access_csr;
4267 end if; -- manager_access_csr%FOUND
4268 close manager_access_csr;
4269 elsif l_access_profile_rec.mgr_update_profile_value = 'I'
4270 then
4271 open mgr_i_access_csr(p_identity_salesforce_id);
4272 fetch mgr_i_access_csr into l_tmp;
4273 if mgr_i_access_csr%FOUND
4274 then
4275 x_update_access_flag := 'Y';
4276 end if;
4277 close mgr_i_access_csr;
4278 end if; -- l_access_profile_rec.mgr_update_profile_value = 'U'
4279 elsif l_access_profile_rec.admin_update_profile_value = 'U'
4280 then
4281 open admin_access_csr;
4282 fetch admin_access_csr into l_tmp;
4283 if admin_access_csr%FOUND
4284 then
4285 x_update_access_flag := 'Y';
4286 else
4287 open am_admin_access_csr;
4288 fetch am_admin_access_csr into l_tmp;
4289 if am_admin_access_csr%FOUND
4290 then
4291 x_update_access_flag := 'Y';
4292 end if;
4293 close am_admin_access_csr;
4294 end if; -- admin_access_csr%FOUND
4295 close admin_access_csr;
4296 elsif l_access_profile_rec.admin_update_profile_value = 'I'
4297 then
4298 open admin_i_access_csr;
4299 fetch admin_i_access_csr into l_tmp;
4300 if admin_i_access_csr%FOUND
4301 then
4302 x_update_access_flag := 'Y';
4303 end if;
4304 close admin_i_access_csr;
4305 end if; -- if p_admin_flag <> 'Y'
4306 end if;
4307 close resource_access_csr;
4308 end if; -- if p_check_access_flag = 'N'
4309 --
4310 -- End of API body.
4311 --
4312
4313 IF l_debug THEN
4314 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4315 'x_update_access_flag: ' || x_update_access_flag);
4316
4317 -- Debug Message
4318 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4319 'Private API: ' || l_api_name || 'end');
4320
4321 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
4322 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4323 END IF;
4324
4325
4326 FND_MSG_PUB.Count_And_Get
4327 ( p_count => x_msg_count,
4328 p_data => x_msg_data
4329 );
4330
4331 EXCEPTION
4332 WHEN FND_API.G_EXC_ERROR THEN
4333 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4334 P_MODULE => l_module
4335 ,P_API_NAME => L_API_NAME
4336 ,P_PKG_NAME => G_PKG_NAME
4337 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4338 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4339 , P_ROLLBACK_FLAG => 'N'
4340 ,X_MSG_COUNT => X_MSG_COUNT
4341 ,X_MSG_DATA => X_MSG_DATA
4342 ,X_RETURN_STATUS => X_RETURN_STATUS);
4343
4344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4345 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4346 P_MODULE => l_module
4347 ,P_API_NAME => L_API_NAME
4348 ,P_PKG_NAME => G_PKG_NAME
4349 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
4350 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4351 , P_ROLLBACK_FLAG => 'N'
4352 ,X_MSG_COUNT => X_MSG_COUNT
4353 ,X_MSG_DATA => X_MSG_DATA
4354 ,X_RETURN_STATUS => X_RETURN_STATUS);
4355
4356 WHEN OTHERS THEN
4357 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4358 P_MODULE => l_module
4359 ,P_API_NAME => L_API_NAME
4360 ,P_PKG_NAME => G_PKG_NAME
4361 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
4362 ,P_SQLCODE => SQLCODE
4363 ,P_SQLERRM => SQLERRM
4364 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4365 , P_ROLLBACK_FLAG => 'N'
4366 ,X_MSG_COUNT => X_MSG_COUNT
4367 ,X_MSG_DATA => X_MSG_DATA
4368 ,X_RETURN_STATUS => X_RETURN_STATUS);
4369
4370 end has_updateLeadAccess;
4371
4372 procedure has_viewLeadAccess
4373 ( p_api_version_number IN NUMBER
4374 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4375 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4376 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
4377 ,p_admin_flag IN VARCHAR2
4378 ,p_admin_group_id IN NUMBER
4379 ,p_person_id IN NUMBER
4380 ,p_sales_lead_id IN NUMBER
4381 ,p_check_access_flag IN VARCHAR2
4382 ,p_identity_salesforce_id IN NUMBER
4383 ,p_partner_cont_party_id IN NUMBER
4384 ,x_return_status OUT NOCOPY VARCHAR2
4385 ,x_msg_count OUT NOCOPY NUMBER
4386 ,x_msg_data OUT NOCOPY VARCHAR2
4387 ,x_view_access_flag OUT NOCOPY VARCHAR2
4388 ) is
4389 l_api_name CONSTANT VARCHAR2(30) := 'has_viewLeadAccess';
4390 l_api_version_number CONSTANT NUMBER := 2.0;
4391 l_tmp varchar2(1);
4392 l_person_id number;
4393 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
4394
4395 cursor resource_access_csr is
4396 select 'X'
4397 from as_accesses_all a
4398 where a.sales_lead_id = p_sales_lead_id
4399 and a.salesforce_id = p_identity_salesforce_id;
4400
4401 cursor manager_access_csr(p_resource_id number) is
4402
4403 select 'X'
4404 from as_accesses_all a
4405 where a.sales_lead_id = p_sales_lead_id
4406 and EXISTS (select 'x'
4407 from as_rpt_managers_v rm
4408 where a.salesforce_id = rm.resource_id
4409 and rm.parent_resource_id = p_resource_id);
4410
4411
4412 cursor admin_access_csr is
4413 select 'x'
4414 from as_accesses_all a
4415 where a.sales_lead_id = p_sales_lead_id
4416 and EXISTS (select 'x'
4417 from as_rpt_admins_v rm
4418 where a.salesforce_id = rm.salesforce_id
4419 and rm.parent_sales_group_id = p_admin_group_id);
4420
4421 cursor am_mgr_access_csr(p_resource_id number) is
4422 select 'x'
4423 from as_sales_leads lead, as_accesses_all a, as_rpt_managers_v rm
4424 where lead.customer_id = a.customer_id
4425 and a.salesforce_id = rm.resource_id
4426 and a.salesforce_role_code = 'AM'
4427 and rm.parent_resource_id = p_resource_id
4428 and lead.sales_lead_id = p_sales_lead_id;
4429
4430 cursor am_admin_access_csr is
4431 select 'x'
4432 from as_sales_leads lead, as_accesses_all a, as_rpt_admins_v rm
4433 where lead.customer_id = a.customer_id
4434 and a.salesforce_id = rm.salesforce_id
4435 and a.salesforce_role_code = 'AM'
4436 and rm.parent_sales_group_id = p_admin_group_id
4437 and lead.sales_lead_id = p_sales_lead_id;
4438
4439 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
4440 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_viewLeadAccess';
4441
4442 begin
4443 -- Standard call to check for call compatibility.
4444 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4445 p_api_version_number,
4446 l_api_name,
4447 G_PKG_NAME)
4448 THEN
4449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4450 END IF;
4451
4452 -- Initialize message list if p_init_msg_list is set to TRUE.
4453 IF FND_API.to_Boolean( p_init_msg_list )
4454 THEN
4455 FND_MSG_PUB.initialize;
4456 END IF;
4457
4458 -- Debug Message
4459 IF l_debug THEN
4460 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4461 'Private API: ' || l_api_name || 'start');
4462
4463 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4464 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4465 END IF;
4466
4467 -- Initialize API return status to SUCCESS
4468 x_return_status := FND_API.G_RET_STS_SUCCESS;
4469
4470 IF l_debug THEN
4471 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4472 'p_sales_lead_id: ' || p_sales_lead_id);
4473 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4474 'ident salesforce_id: ' || p_identity_salesforce_id);
4475 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4476 'p_admin_group_id: ' || p_admin_group_id);
4477 END IF;
4478
4479 --
4480 -- API body
4481 --
4482
4483 -- Initialize access flag to 'N'
4484 x_view_access_flag := 'N';
4485
4486 if p_check_access_flag = 'N'
4487 then
4488 x_view_access_flag := 'Y';
4489 else -- if p_check_access_flag = 'Y'
4490 -- PRM security
4491 open resource_access_csr;
4492 fetch resource_access_csr into l_tmp;
4493
4494 l_access_profile_rec := p_access_profile_rec;
4495 get_accessProfileValues(l_access_profile_rec);
4496
4497 if l_access_profile_rec.lead_access_profile_value in ('F','P')
4498 then
4499 x_view_access_flag := 'Y';
4500 elsif resource_access_csr%FOUND
4501 then
4502 x_view_access_flag := 'Y';
4503 else
4504 if nvl(p_admin_flag,'N') <> 'Y'
4505 then
4506
4507 open manager_access_csr(p_identity_salesforce_id);
4508 fetch manager_access_csr into l_tmp;
4509 if manager_access_csr%FOUND
4510 -- First check if mgr's subordinate
4511 -- which are not 'AM'
4512 then
4513 x_view_access_flag := 'Y';
4514 else -- if mgr's subordinate which are 'AM'
4515 open am_mgr_access_csr(p_identity_salesforce_id);
4516 fetch am_mgr_access_csr into l_tmp;
4517 if am_mgr_access_csr%FOUND
4518 then
4519 x_view_access_flag := 'Y';
4520 end if;
4521 close am_mgr_access_csr;
4522 end if; -- manager_access_csr%FOUND
4523 close manager_access_csr;
4524 else
4525 open admin_access_csr;
4526 fetch admin_access_csr into l_tmp;
4527 if admin_access_csr%FOUND
4528 then
4529 x_view_access_flag := 'Y';
4530 else
4531 open am_admin_access_csr;
4532 fetch am_admin_access_csr into l_tmp;
4533 if am_admin_access_csr%FOUND
4534 then
4535 x_view_access_flag := 'Y';
4536 end if;
4537 close am_admin_access_csr;
4538 end if; -- admin_access_csr%FOUND
4539 close admin_access_csr;
4540 end if; -- if p_admin_flag <> 'Y'
4541 end if; -- if lead_access_profile_value = 'F'
4542 close resource_access_csr;
4543 end if; -- if p_check_access_flag = 'N'
4544 --
4545 -- End of API body.
4546 --
4547
4548 IF l_debug THEN
4549 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4550 'x_view_access_flag: ' || x_view_access_flag);
4551
4552 -- Debug Message
4553 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4554 'Private API: ' || l_api_name || 'end');
4555
4556 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
4557 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4558 END IF;
4559
4560 FND_MSG_PUB.Count_And_Get
4561 ( p_count => x_msg_count,
4562 p_data => x_msg_data
4563 );
4564
4565 EXCEPTION
4566 WHEN FND_API.G_EXC_ERROR THEN
4567 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4568 P_MODULE => l_module
4569 ,P_API_NAME => L_API_NAME
4570 ,P_PKG_NAME => G_PKG_NAME
4571 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4572 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4573 , P_ROLLBACK_FLAG => 'N'
4574 ,X_MSG_COUNT => X_MSG_COUNT
4575 ,X_MSG_DATA => X_MSG_DATA
4576 ,X_RETURN_STATUS => X_RETURN_STATUS);
4577
4578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4579 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4580 P_MODULE => l_module
4581 ,P_API_NAME => L_API_NAME
4582 ,P_PKG_NAME => G_PKG_NAME
4583 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
4584 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4585 , P_ROLLBACK_FLAG => 'N'
4586 ,X_MSG_COUNT => X_MSG_COUNT
4587 ,X_MSG_DATA => X_MSG_DATA
4588 ,X_RETURN_STATUS => X_RETURN_STATUS);
4589
4590 WHEN OTHERS THEN
4591 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4592 P_MODULE => l_module
4593 ,P_API_NAME => L_API_NAME
4594 ,P_PKG_NAME => G_PKG_NAME
4595 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
4596 ,P_SQLCODE => SQLCODE
4597 ,P_SQLERRM => SQLERRM
4598 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4599 , P_ROLLBACK_FLAG => 'N'
4600 ,X_MSG_COUNT => X_MSG_COUNT
4601 ,X_MSG_DATA => X_MSG_DATA
4602 ,X_RETURN_STATUS => X_RETURN_STATUS);
4603
4604 end has_viewLeadAccess;
4605
4606
4607
4608 procedure has_updateOpportunityAccess
4609 ( p_api_version_number IN NUMBER
4610 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4611 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4612 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
4613 ,p_admin_flag IN VARCHAR2
4614 ,p_admin_group_id IN NUMBER
4615 ,p_person_id IN NUMBER
4616 ,p_opportunity_id IN NUMBER
4617 ,p_check_access_flag IN VARCHAR2
4618 ,p_identity_salesforce_id IN NUMBER
4619 ,p_partner_cont_party_id IN NUMBER
4620 ,x_return_status OUT NOCOPY VARCHAR2
4621 ,x_msg_count OUT NOCOPY NUMBER
4622 ,x_msg_data OUT NOCOPY VARCHAR2
4623 ,x_update_access_flag OUT NOCOPY VARCHAR2
4624 ) is
4625 l_api_name CONSTANT VARCHAR2(30) := 'has_updateOppurtunityAccess';
4626 l_api_version_number CONSTANT NUMBER := 2.0;
4627 l_tmp varchar2(1);
4628 l_update_access_flag varchar2(1);
4629 l_org_id NUMBER;
4630 l_person_id number;
4631 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
4632
4633 cursor resource_access_csr is
4634 select 'X'
4635 from as_accesses_all a
4636 where a.lead_id = p_opportunity_id
4637 and a.team_leader_flag = 'Y'
4638 and a.salesforce_id = p_identity_salesforce_id;
4639
4640 cursor manager_access_csr(p_resource_id number) is
4641
4642 select 'X'
4643 from as_accesses_all a
4644 where a.lead_id = p_opportunity_id
4645 and (EXISTS (select 'x'
4646 from as_rpt_managers_v rm
4647 where a.salesforce_id = rm.resource_id
4648 and rm.parent_resource_id = p_resource_id
4649 and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate)
4650 and ((rm.parent_resource_id = rm.resource_id
4651 and a.team_leader_flag = 'Y')
4652 or (rm.parent_resource_id <> rm.resource_id))));
4653
4654 cursor mgr_i_access_csr(p_resource_id number) is
4655 select 'X'
4656 from as_accesses_all a
4657 where a.lead_id = p_opportunity_id
4658 and a.team_leader_flag = 'Y'
4659 and (EXISTS (select 'x'
4660 from as_rpt_managers_v rm
4661 where a.salesforce_id = rm.resource_id
4662 and rm.parent_resource_id = p_resource_id
4663 and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate)
4664 ));
4665
4666 cursor admin_access_csr is
4667 select 'x'
4668 from as_accesses_all a
4669 where a.lead_id = p_opportunity_id
4670 and EXISTS (select 'x'
4671 from as_rpt_admins_v rm
4672 where a.salesforce_id = rm.salesforce_id
4673 and rm.parent_sales_group_id = p_admin_group_id
4674 and ((rm.salesforce_id = p_identity_salesforce_id
4675 and a.team_leader_flag = 'Y')
4676 or (rm.salesforce_id <> p_identity_salesforce_id)));
4677
4678 cursor admin_i_access_csr is
4679 select 'x'
4680 from as_accesses_all a
4681 where a.lead_id = p_opportunity_id
4682 and a.team_leader_flag = 'Y'
4683 and EXISTS (select 'x'
4684 from as_rpt_admins_v rm
4685 where a.salesforce_id = rm.salesforce_id
4686 and rm.parent_sales_group_id = p_admin_group_id);
4687
4688 cursor am_mgr_access_csr(p_resource_id number) is
4689 select 'x'
4690 from as_leads opp, as_accesses_all a, as_rpt_managers_v rm
4691 where opp.customer_id = a.customer_id
4692 and a.salesforce_id = rm.resource_id
4693 and a.salesforce_role_code = 'AM'
4694 and rm.parent_resource_id = p_resource_id
4695 and opp.lead_id = p_opportunity_id
4696 and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate);
4697
4698 cursor am_admin_access_csr is
4699 select 'x'
4700 from as_leads opp, as_accesses_all a, as_rpt_admins_v rm
4701 where opp.customer_id = a.customer_id
4702 and a.salesforce_id = rm.salesforce_id
4703 and a.salesforce_role_code = 'AM'
4704 and rm.parent_sales_group_id = p_admin_group_id
4705 and opp.lead_id = p_opportunity_id;
4706
4707 cursor find_lead_org is
4708 select org_id
4709 from as_leads_all
4710 where lead_id = p_opportunity_id;
4711
4712 cursor c_org_access(p_org_id NUMBER) is
4713 select 'Y'
4714 from hr_operating_units hr
4715 where hr.organization_id = p_org_id
4716 and mo_global.check_access(hr.organization_id) = 'Y';
4717
4718 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
4719 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_updateOpportunityAccess';
4720
4721 begin
4722 -- Standard call to check for call compatibility.
4723 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4724 p_api_version_number,
4725 l_api_name,
4726 G_PKG_NAME)
4727 THEN
4728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4729 END IF;
4730
4731 -- Initialize message list if p_init_msg_list is set to TRUE.
4732 IF FND_API.to_Boolean( p_init_msg_list )
4733 THEN
4734 FND_MSG_PUB.initialize;
4735 END IF;
4736
4737 -- Debug Message
4738 IF l_debug THEN
4739 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4740 'Private API: ' || l_api_name || 'start');
4741
4742 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4743 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4744 END IF;
4745
4746 -- Initialize API return status to SUCCESS
4747 x_return_status := FND_API.G_RET_STS_SUCCESS;
4748
4749 IF l_debug THEN
4750 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4751 'p_opportunity_id: ' || p_opportunity_id);
4752 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4753 'ident salesforce_id: ' || p_identity_salesforce_id);
4754 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4755 'p_admin_group_id: ' || p_admin_group_id);
4756 END IF;
4757
4758 --
4759 -- API body
4760 --
4761
4762 -- Initialize access flag to 'N'
4763 x_update_access_flag := 'N';
4764
4765 if p_check_access_flag = 'N'
4766 then
4767 x_update_access_flag := 'Y';
4768 else -- if p_check_access_flag = 'Y'
4769 -- PRM security
4770 open resource_access_csr;
4771 fetch resource_access_csr into l_tmp;
4772 /* if p_partner_cont_party_id is not null
4773 and p_partner_cont_party_id <> FND_API.G_MISS_NUM
4774 then
4775 if (resource_access_csr%FOUND)
4776 then
4777 x_update_access_flag := 'Y';
4778 close resource_access_csr;
4779 return;
4780 end if;
4781 end if; */
4782 /* if p_person_id is null or p_person_id = fnd_api.g_miss_num
4783 then
4784 get_person_id(p_identity_salesforce_id, l_person_id);
4785 else
4786 l_person_id := p_person_id;
4787 end if;
4788 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4789 'person id: ' || l_person_id);
4790 */
4791 l_access_profile_rec := p_access_profile_rec;
4792 get_accessProfileValues(l_access_profile_rec);
4793
4794 if l_access_profile_rec.opp_access_profile_value = 'F'
4795 then
4796 x_update_access_flag := 'Y';
4797 elsif l_access_profile_rec.opp_access_profile_value = 'O'
4798 then
4799 -- l_update_access_flag will hold value for x_update_access_flag, NULL means unknown
4800 l_update_access_flag := 'N';
4801 /* check org full access */
4802 open find_lead_org;
4803 fetch find_lead_org into l_org_id;
4804 if(find_lead_org%FOUND)then
4805 if l_org_id IS NULL then
4806 l_update_access_flag := 'Y'; -- Access allowed if org_id NULL
4807 else
4808 l_update_access_flag := NULL; -- Need to check for the org id
4809 end if;
4810 end if;
4811 close find_lead_org;
4812
4813 -- Added for MOAC
4814 if l_update_access_flag IS NULL then
4815 open c_org_access(l_org_id);
4816 fetch c_org_access into l_update_access_flag;
4817 if c_org_access%NOTFOUND then
4818 l_update_access_flag := 'N';
4819 end if;
4820 close c_org_access;
4821 end if;
4822
4823 /* for bug 1613991 */
4824 if l_update_access_flag = 'N' and resource_access_csr%FOUND then
4825 l_update_access_flag := 'Y';
4826 end if;
4827
4828 x_update_access_flag := l_update_access_flag;
4829
4830 elsif resource_access_csr%FOUND
4831 then
4832 x_update_access_flag := 'Y';
4833 else
4834 if nvl(p_admin_flag,'N') <> 'Y'
4835 then
4836 if l_access_profile_rec.mgr_update_profile_value = 'U'
4837 then
4838 open manager_access_csr(p_identity_salesforce_id);
4839 fetch manager_access_csr into l_tmp;
4840 if manager_access_csr%FOUND
4841 -- First check if mgr's subordinate
4842 -- which are not 'AM'
4843 then
4844 x_update_access_flag := 'Y';
4845 else -- if mgr's subordinate which are 'AM'
4846 open am_mgr_access_csr(p_identity_salesforce_id);
4847 fetch am_mgr_access_csr into l_tmp;
4848 if am_mgr_access_csr%FOUND
4849 then
4850 x_update_access_flag := 'Y';
4851 end if;
4852 close am_mgr_access_csr;
4853 end if; -- manager_access_csr%FOUND
4854 close manager_access_csr;
4855 elsif l_access_profile_rec.mgr_update_profile_value = 'I'
4856 then
4857 open mgr_i_access_csr(p_identity_salesforce_id);
4858 fetch mgr_i_access_csr into l_tmp;
4859 if mgr_i_access_csr%FOUND
4860 then
4861 x_update_access_flag := 'Y';
4862 end if;
4863 close mgr_i_access_csr;
4864 end if; -- l_access_profile_rec.mgr_update_profile_value = 'U'
4865 elsif l_access_profile_rec.admin_update_profile_value = 'U'
4866 then
4867 open admin_access_csr;
4868 fetch admin_access_csr into l_tmp;
4869 if admin_access_csr%FOUND
4870 then
4871 x_update_access_flag := 'Y';
4872 else
4873 open am_admin_access_csr;
4874 fetch am_admin_access_csr into l_tmp;
4875 if am_admin_access_csr%FOUND
4876 then
4877 x_update_access_flag := 'Y';
4878 end if;
4879 close am_admin_access_csr;
4880 end if; -- admin_access_csr%FOUND
4881 close admin_access_csr;
4882 elsif l_access_profile_rec.admin_update_profile_value = 'I'
4883 then
4884 open admin_i_access_csr;
4885 fetch admin_i_access_csr into l_tmp;
4886 if admin_i_access_csr%FOUND
4887 then
4888 x_update_access_flag := 'Y';
4889 end if;
4890 close admin_i_access_csr;
4891 end if; -- if p_admin_flag <> 'Y'
4892 end if;
4893 close resource_access_csr;
4894 end if; -- if p_check_access_flag = 'N'
4895 --
4896 -- End of API body.
4897 --
4898
4899 IF l_debug THEN
4900 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4901 'x_update_access_flag: ' || x_update_access_flag);
4902
4903 -- Debug Message
4904 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4905 'Private API: ' || l_api_name || 'end');
4906
4907 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
4908 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
4909 END IF;
4910
4911
4912 FND_MSG_PUB.Count_And_Get
4913 ( p_count => x_msg_count,
4914 p_data => x_msg_data
4915 );
4916
4917 EXCEPTION
4918 WHEN FND_API.G_EXC_ERROR THEN
4919 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4920 P_MODULE => l_module
4921 ,P_API_NAME => L_API_NAME
4922 ,P_PKG_NAME => G_PKG_NAME
4923 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4924 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4925 , P_ROLLBACK_FLAG => 'N'
4926 ,X_MSG_COUNT => X_MSG_COUNT
4927 ,X_MSG_DATA => X_MSG_DATA
4928 ,X_RETURN_STATUS => X_RETURN_STATUS);
4929
4930 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4931 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4932 P_MODULE => l_module
4933 ,P_API_NAME => L_API_NAME
4934 ,P_PKG_NAME => G_PKG_NAME
4935 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
4936 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4937 , P_ROLLBACK_FLAG => 'N'
4938 ,X_MSG_COUNT => X_MSG_COUNT
4939 ,X_MSG_DATA => X_MSG_DATA
4940 ,X_RETURN_STATUS => X_RETURN_STATUS);
4941
4942 WHEN OTHERS THEN
4943 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
4944 P_MODULE => l_module
4945 ,P_API_NAME => L_API_NAME
4946 ,P_PKG_NAME => G_PKG_NAME
4947 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
4948 ,P_SQLCODE => SQLCODE
4949 ,P_SQLERRM => SQLERRM
4950 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
4951 , P_ROLLBACK_FLAG => 'N'
4952 ,X_MSG_COUNT => X_MSG_COUNT
4953 ,X_MSG_DATA => X_MSG_DATA
4954 ,X_RETURN_STATUS => X_RETURN_STATUS);
4955
4956 end has_updateOpportunityAccess;
4957
4958 procedure has_viewOpportunityAccess
4959 ( p_api_version_number IN NUMBER
4960 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4961 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4962 ,p_access_profile_rec IN as_access_pub.access_profile_rec_type
4963 ,p_admin_flag IN VARCHAR2
4964 ,p_admin_group_id IN NUMBER
4965 ,p_person_id IN NUMBER
4966 ,p_opportunity_id IN NUMBER
4967 ,p_check_access_flag IN VARCHAR2
4968 ,p_identity_salesforce_id IN NUMBER
4969 ,p_partner_cont_party_id IN NUMBER
4970 ,x_return_status OUT NOCOPY VARCHAR2
4971 ,x_msg_count OUT NOCOPY NUMBER
4972 ,x_msg_data OUT NOCOPY VARCHAR2
4973 ,x_view_access_flag OUT NOCOPY VARCHAR2
4974 ) is
4975 l_api_name CONSTANT VARCHAR2(30) := 'has_viewOppurtunityAccess';
4976 l_api_version_number CONSTANT NUMBER := 2.0;
4977 l_tmp varchar2(1);
4978 l_view_access_flag varchar2(1);
4979 l_org_id number;
4980 l_person_id number;
4981 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
4982
4983 cursor resource_access_csr is
4984 select 'X'
4985 from as_accesses_all a
4986 where a.lead_id = p_opportunity_id
4987 and a.salesforce_id = p_identity_salesforce_id;
4988
4989 cursor manager_access_csr(p_resource_id number) is
4990
4991 select 'X'
4992 from as_accesses_all a
4993 where a.lead_id = p_opportunity_id
4994 and EXISTS (select 'x'
4995 from as_rpt_managers_v rm
4996 where a.salesforce_id = rm.resource_id
4997 and rm.parent_resource_id = p_resource_id
4998 and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate)
4999 );
5000
5001 cursor admin_access_csr is
5002 select 'x'
5003 from as_accesses_all a
5004 where a.lead_id = p_opportunity_id
5005 and EXISTS (select 'x'
5006 from as_rpt_admins_v rm
5007 where a.salesforce_id = rm.salesforce_id
5008 and rm.parent_sales_group_id = p_admin_group_id);
5009
5010
5011 cursor am_mgr_access_csr(p_resource_id number) is
5012 select 'x'
5013 from as_leads opp, as_accesses_all a, as_rpt_managers_v rm
5014 where opp.customer_id = a.customer_id
5015 and a.salesforce_id = rm.resource_id
5016 and a.salesforce_role_code = 'AM'
5017 and rm.parent_resource_id = p_resource_id
5018 and opp.lead_id = p_opportunity_id
5019 and sysdate between rm.start_date_active and nvl(rm.end_date_active, sysdate);
5020
5021 cursor am_admin_access_csr is
5022 select 'x'
5023 from as_leads opp, as_accesses_all a, as_rpt_admins_v rm
5024 where opp.customer_id = a.customer_id
5025 and a.salesforce_id = rm.salesforce_id
5026 and a.salesforce_role_code = 'AM'
5027 and rm.parent_sales_group_id = p_admin_group_id
5028 and opp.lead_id = p_opportunity_id;
5029
5030 cursor find_lead_org is
5031 select org_id
5032 from as_leads_all
5033 where lead_id = p_opportunity_id;
5034
5035 cursor c_org_access(p_org_id NUMBER) is
5036 select 'Y'
5037 from hr_operating_units hr
5038 where hr.organization_id = p_org_id
5039 and mo_global.check_access(hr.organization_id) = 'Y';
5040
5041 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
5042 l_module CONSTANT VARCHAR2(255) := 'as.plsql.acspv.has_viewOpportunityAccess';
5043
5044 begin
5045 -- Standard call to check for call compatibility.
5046 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
5047 p_api_version_number,
5048 l_api_name,
5049 G_PKG_NAME)
5050 THEN
5051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5052 END IF;
5053
5054 -- Initialize message list if p_init_msg_list is set to TRUE.
5055 IF FND_API.to_Boolean( p_init_msg_list )
5056 THEN
5057 FND_MSG_PUB.initialize;
5058 END IF;
5059
5060 -- Debug Message
5061 IF l_debug THEN
5062 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5063 'Private API: ' || l_api_name || 'start');
5064
5065 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5066 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
5067 END IF;
5068
5069 -- Initialize API return status to SUCCESS
5070 x_return_status := FND_API.G_RET_STS_SUCCESS;
5071
5072 IF l_debug THEN
5073 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5074 'p_opportunity_id: ' || p_opportunity_id);
5075 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5076 'ident salesforce_id: ' || p_identity_salesforce_id);
5077 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5078 'p_admin_group_id: ' || p_admin_group_id);
5079 END IF;
5080
5081 --
5082 -- API body
5083 --
5084
5085 -- Initialize access flag to 'N'
5086 x_view_access_flag := 'N';
5087
5088 if p_check_access_flag = 'N'
5089 then
5090 x_view_access_flag := 'Y';
5091 else -- if p_check_access_flag = 'Y'
5092
5093 open resource_access_csr;
5094 fetch resource_access_csr into l_tmp;
5095
5096 l_access_profile_rec := p_access_profile_rec;
5097 get_accessProfileValues(l_access_profile_rec);
5098
5099 if l_access_profile_rec.opp_access_profile_value in ('F','P')
5100 then
5101 x_view_access_flag := 'Y';
5102 elsif l_access_profile_rec.opp_access_profile_value = 'O'
5103 then
5104 /* check org full access */
5105 -- l_view_access_flag will hold value for x_view_access_flag, NULL means unknown
5106 l_view_access_flag := 'N';
5107 /* check org full access */
5108 open find_lead_org;
5109 fetch find_lead_org into l_org_id;
5110 if(find_lead_org%FOUND)then
5111 if l_org_id IS NULL then
5112 l_view_access_flag := 'Y'; -- Access allowed if org_id NULL
5113 else
5114 l_view_access_flag := NULL; -- Need to check for the org id
5115 end if;
5116 end if;
5117 close find_lead_org;
5118
5119 -- Added for MOAC
5120 if l_view_access_flag IS NULL then
5121 open c_org_access(l_org_id);
5122 fetch c_org_access into l_view_access_flag;
5123 if c_org_access%NOTFOUND then
5124 l_view_access_flag := 'N';
5125 end if;
5126 close c_org_access;
5127 end if;
5128
5129 /* for bug 1613991 */
5130 if l_view_access_flag = 'N' and resource_access_csr%FOUND then
5131 l_view_access_flag := 'Y';
5132 end if;
5133
5134 x_view_access_flag := l_view_access_flag;
5135 elsif resource_access_csr%FOUND
5136 then
5137 x_view_access_flag := 'Y';
5138 else
5139 if nvl(p_admin_flag,'N') <> 'Y'
5140 then
5141 open manager_access_csr(p_identity_salesforce_id);
5142 fetch manager_access_csr into l_tmp;
5143 if manager_access_csr%FOUND
5144 -- First check if mgr's subordinate
5145 -- which are not 'AM'
5146 then
5147 x_view_access_flag := 'Y';
5148 else -- if mgr's subordinate which are 'AM'
5149 open am_mgr_access_csr(p_identity_salesforce_id);
5150 fetch am_mgr_access_csr into l_tmp;
5151 if am_mgr_access_csr%FOUND
5152 then
5153 x_view_access_flag := 'Y';
5154 end if;
5155 close am_mgr_access_csr;
5156 end if; -- manager_access_csr%FOUND
5157 close manager_access_csr;
5158
5159 else
5160 open admin_access_csr;
5161 fetch admin_access_csr into l_tmp;
5162 if admin_access_csr%FOUND
5163 then
5164 x_view_access_flag := 'Y';
5165 else
5166 open am_admin_access_csr;
5167 fetch am_admin_access_csr into l_tmp;
5168 if am_admin_access_csr%FOUND
5169 then
5170 x_view_access_flag := 'Y';
5171 end if;
5172 close am_admin_access_csr;
5173 end if; -- admin_access_csr%FOUND
5174 close admin_access_csr;
5175 end if; -- if p_admin_flag <> 'Y'
5176 end if; -- if l_access_profile_rec.opp_access_profile_value = 'F'
5177 close resource_access_csr;
5178 end if; -- if p_check_access_flag = 'N'
5179 --
5180 -- End of API body.
5181 --
5182
5183 IF l_debug THEN
5184 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5185 'x_view_access_flag: ' || x_view_access_flag);
5186
5187 -- Debug Message
5188 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
5189 'Private API: ' || l_api_name || 'end');
5190
5191 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'
5192 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
5193 END IF;
5194
5195
5196 FND_MSG_PUB.Count_And_Get
5197 ( p_count => x_msg_count,
5198 p_data => x_msg_data
5199 );
5200
5201 EXCEPTION
5202 WHEN FND_API.G_EXC_ERROR THEN
5203 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
5204 P_MODULE => l_module
5205 ,P_API_NAME => L_API_NAME
5206 ,P_PKG_NAME => G_PKG_NAME
5207 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
5208 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
5209 , P_ROLLBACK_FLAG => 'N'
5210 ,X_MSG_COUNT => X_MSG_COUNT
5211 ,X_MSG_DATA => X_MSG_DATA
5212 ,X_RETURN_STATUS => X_RETURN_STATUS);
5213
5214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5215 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
5216 P_MODULE => l_module
5217 ,P_API_NAME => L_API_NAME
5218 ,P_PKG_NAME => G_PKG_NAME
5219 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
5220 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
5221 , P_ROLLBACK_FLAG => 'N'
5222 ,X_MSG_COUNT => X_MSG_COUNT
5223 ,X_MSG_DATA => X_MSG_DATA
5224 ,X_RETURN_STATUS => X_RETURN_STATUS);
5225
5226 WHEN OTHERS THEN
5227 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
5228 P_MODULE => l_module
5229 ,P_API_NAME => L_API_NAME
5230 ,P_PKG_NAME => G_PKG_NAME
5231 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
5232 ,P_SQLCODE => SQLCODE
5233 ,P_SQLERRM => SQLERRM
5234 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
5235 , P_ROLLBACK_FLAG => 'N'
5236 ,X_MSG_COUNT => X_MSG_COUNT
5237 ,X_MSG_DATA => X_MSG_DATA
5238 ,X_RETURN_STATUS => X_RETURN_STATUS);
5239
5240 end has_viewOpportunityAccess;
5241
5242
5243
5244 END AS_ACCESS_PVT;