[Home] [Help]
PACKAGE BODY: APPS.ASO_SALES_TEAM_PVT
Source
1 PACKAGE BODY ASO_SALES_TEAM_PVT as
2 /* $Header: asovastb.pls 120.18 2011/09/19 09:30:20 vidsrini ship $ */
3 -- Start of Comments
4 -- Package name : ASO_SALES_TEAM_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ASO_SALES_TEAM_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovstmb.pls';
13 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
14 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
15
16 PROCEDURE INSERT_ACCESSES_ACCOUNTS(
17 x_errbuf OUT NOCOPY VARCHAR2,
18 x_retcode OUT NOCOPY VARCHAR2,
19 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
20 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
21 x_return_status OUT NOCOPY VARCHAR2) IS
22 BEGIN
23
24 aso_debug_pub.add('Begin INSERT_ACCESSES_ACCOUNTS',1,'Y');
25 x_return_status := FND_API.G_RET_STS_SUCCESS;
26 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
27
28 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
29
30
31 --added inline view in the select clause of Insert statement to fetch the salesforce role code for Employee resource --fix for bug 5869095
32
33 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_EMPLOYEE' AND p_WinningTerrMember_tbl.group_id(l_index) IS NOT NULL THEN
34 aso_debug_pub.add('Begin INSERT_ACCESSES_ACCOUNTS'|| p_WinningTerrMember_tbl.resource_type(l_index),1,'Y');
35
36 Insert into ASO_QUOTE_ACCESSES
37 (ACCESS_ID,
38 QUOTE_NUMBER,
39 RESOURCE_ID,
40 RESOURCE_GRP_ID,
41 CREATED_BY,
42 CREATION_DATE,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_LOGIN,
45 LAST_UPDATE_DATE,
46 ROLE_ID,
47 OBJECT_VERSION_NUMBER )
48 select
49 ASO_QUOTE_ACCESSES_S.nextval,
50 P_Qte_Header_Rec.quote_number,
51 p_WinningTerrMember_tbl.resource_id(l_index),
52 p_WinningTerrMember_tbl.group_ID(l_index),
53 FND_GLOBAL.USER_ID ,
54 SYSDATE ,
55 FND_GLOBAL.USER_ID ,
56 FND_GLOBAL.USER_ID ,
57 SYSDATE,
58 null,
59 null
60 from dual where not exists (select 1 from
61 ASO_QUOTE_ACCESSES where resource_id = p_WinningTerrMember_tbl.resource_id(l_index)
62 and quote_number = P_Qte_Header_Rec.quote_number);
63
64
65 END IF;
66 END LOOP;
67 END IF;
68
69 EXCEPTION
70 WHEN OTHERS THEN
71
72 aso_debug_pub.add('Proc INSERT_ACCESSES_ACCOUNTS exception part',1,'Y');
73
74 x_errbuf := SQLERRM;
75 x_retcode := SQLCODE;
76 x_return_status := FND_API.G_RET_STS_ERROR;
77 END INSERT_ACCESSES_ACCOUNTS;
78
79 /************************** Start Explode Teams ******************/
80 PROCEDURE EXPLODE_TEAMS(
81 x_errbuf OUT NOCOPY VARCHAR2,
82 x_retcode OUT NOCOPY VARCHAR2,
83 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
84 x_return_status OUT NOCOPY VARCHAR2)
85 IS
86
87 /*-------------------------------------------------------------------------+
88 | LOGIC
89 |
90 | A RESOURCE team can be comprised OF resources who belong TO one OR more
91 | GROUPS OF resources.
92 | So get a LIST OF team members (OF TYPE employee OR partner OR parter contact
93 | AND play a ROLE OF salesrep ) AND get atleast one GROUP id that they belong TO
94 | WHERE they play a similar ROLE.
95 | UNION THE above WITH a LIST OF ALL members OF ALL GROUPS which BY themselves
96 | are a RESOURCE within a team.
97 | INSERT these members INTO winners IF they are NOT already IN winners.
98 +-------------------------------------------------------------------------*/
99
100 l_errbuf VARCHAR2(4000);
101 l_retcode VARCHAR2(255);
102 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
103
104 l_resource_id num_list;
105 l_group_id num_list;
106 l_person_id num_list;
107
108
109 BEGIN
110
111 aso_debug_pub.add('Explode Team',1,'Y');
112
113 x_return_status := FND_API.G_RET_STS_SUCCESS;
114 -- l_resource_type := 'RS_TEAM';
115 /* Get resources within a resource team */
116 /** Note
117 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
118 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
119 **/
120 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
121
122 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
123 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_TEAM' THEN
124 aso_debug_pub.add('Explode Team ' ||p_WinningTerrMember_tbl.resource_type(l_index),1,'Y');
125 SELECT resource_id, group_id , person_id
126 BULK COLLECT INTO l_resource_id, l_group_id,l_person_id
127 FROM
128 (
129 SELECT TM.team_resource_id resource_id,
130 TM.person_id person_id2,
131 MIN(G.group_id)group_id,
132 MIN(T.team_id) team_id,
133 TRES.CATEGORY resource_category,
134 MIN(TRES.source_id) person_id
135 FROM jtf_rs_team_members TM, jtf_rs_teams_b T,
136 jtf_rs_team_usages TU, jtf_rs_role_relations TRR,
137 jtf_rs_roles_b TR, jtf_rs_resource_extns TRES,
138 (
139 SELECT m.group_id group_id, m.resource_id resource_id
140 FROM jtf_rs_group_members m,
141 jtf_rs_groups_b g,
142 jtf_rs_group_usages u,
143 jtf_rs_role_relations rr,
144 jtf_rs_roles_b r,
145 jtf_rs_resource_extns res
146 WHERE m.group_id = g.group_id
147 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
148 AND NVL(g.end_date_active,SYSDATE)
149 AND u.group_id = g.group_id
150 AND u.usage IN ('SALES','PRM')
151 AND m.group_member_id = rr.role_resource_id
152 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
153 AND rr.delete_flag <> 'Y'
154 AND SYSDATE BETWEEN rr.start_date_active
155 AND NVL(rr.end_date_active,SYSDATE)
156 AND rr.role_id = r.role_id
157 AND r.role_type_code
158 IN ('SALES', 'TELESALES', 'FIELDSALES','PRM')
159 AND r.active_flag = 'Y'
160 AND res.resource_id = m.resource_id
161 AND res.CATEGORY IN ('EMPLOYEE')--,'PARTY','PARTNER')
162 ) G
163 WHERE tm.team_id = t.team_id
164 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
165 AND NVL(t.end_date_active,SYSDATE)
166 AND tu.team_id = t.team_id
167 AND tu.usage IN ('SALES','PRM')
168 AND tm.team_member_id = trr.role_resource_id
169 AND tm.delete_flag <> 'Y'
170 AND tm.resource_type = 'INDIVIDUAL'
171 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
172 AND trr.delete_flag <> 'Y'
173 AND SYSDATE BETWEEN trr.start_date_active
174 AND NVL(trr.end_date_active,SYSDATE)
175 AND trr.role_id = tr.role_id
176 AND tr.role_type_code IN
177 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
178 AND tr.active_flag = 'Y'
179 AND tres.resource_id = tm.team_resource_id
180 AND tres.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
181 AND tm.team_resource_id = g.resource_id
182 GROUP BY tm.team_resource_id,
183 tm.person_id,
184 tres.CATEGORY,
185 tres.source_id
186 UNION ALL
187 SELECT MIN(m.resource_id) resource_id,
188 MIN(m.person_id) person_id2, MIN(m.group_id) group_id,
189 MIN(jtm.team_id) team_id, res.CATEGORY resource_category,
190 MIN(res.source_id) person_id
191 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
192 jtf_rs_group_usages u, jtf_rs_role_relations rr,
193 jtf_rs_roles_b r, jtf_rs_resource_extns res,
194 (
195 SELECT tm.team_resource_id group_id,
196 t.team_id team_id
197 FROM jtf_rs_team_members tm, jtf_rs_teams_b t,
198 jtf_rs_team_usages tu,jtf_rs_role_relations trr,
199 jtf_rs_roles_b tr, jtf_rs_resource_extns tres
200 WHERE tm.team_id = t.team_id
201 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
202 AND NVL(t.end_date_active,SYSDATE)
203 AND tu.team_id = t.team_id
204 AND tu.usage IN ('SALES','PRM')
205 AND tm.team_member_id = trr.role_resource_id
206 AND tm.delete_flag <> 'Y'
207 AND tm.resource_type = 'GROUP'
208 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
209 AND trr.delete_flag <> 'Y'
210 AND SYSDATE BETWEEN trr.start_date_active
211 AND NVL(trr.end_date_active,SYSDATE)
212 AND trr.role_id = tr.role_id
213 AND tr.role_type_code IN
214 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
215 AND tr.active_flag = 'Y'
216 AND tres.resource_id = tm.team_resource_id
217 AND tres.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
218 ) jtm
219 WHERE m.group_id = g.group_id
220 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
221 AND NVL(g.end_date_active,SYSDATE)
222 AND u.group_id = g.group_id
223 AND u.usage IN ('SALES','PRM')
224 AND m.group_member_id = rr.role_resource_id
225 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
226 AND rr.delete_flag <> 'Y'
227 AND SYSDATE BETWEEN rr.start_date_active
228 AND NVL(rr.end_date_active,SYSDATE)
229 AND rr.role_id = r.role_id
230 AND r.role_type_code IN
231 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
232 AND r.active_flag = 'Y'
233 AND res.resource_id = m.resource_id
234 AND res.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
235 AND jtm.group_id = g.group_id
236 GROUP BY m.resource_id, m.person_id, jtm.team_id, res.CATEGORY) J
237
238 WHERE j.team_id = p_WinningTerrMember_tbl.resource_id(l_index);
239 aso_debug_pub.add('Explode Team ' || p_WinningTerrMember_tbl.resource_id(l_index),1,'Y');
240
241
242 IF l_resource_id.COUNT > 0 THEN
243 FOR i IN l_resource_id.FIRST .. l_resource_id.LAST LOOP
244 /* No need to Check to see if it is already part of
245 p_WinningTerrMember_tbl because this will be slow,
246 So we insert into p_WinningTerrMember_tbl directly*/
247 IF l_group_id(i) IS NOT NULL THEN --- Resources without groups should NOT be added to the sales team
248 p_WinningTerrMember_tbl.resource_id.EXTEND;
249 p_WinningTerrMember_tbl.group_id.EXTEND;
250 p_WinningTerrMember_tbl.person_id.EXTEND;
251 p_WinningTerrMember_tbl.resource_type.EXTEND;
252 p_WinningTerrMember_tbl.full_access_flag.EXTEND;
253 p_WinningTerrMember_tbl.terr_id.EXTEND;
254 p_WinningTerrMember_tbl.trans_object_id.EXTEND;
255 p_WinningTerrMember_tbl.org_id.EXTEND;
256 p_WinningTerrMember_tbl.ROLE.EXTEND;
257 p_WinningTerrMember_tbl.resource_id(p_WinningTerrMember_tbl.resource_id.COUNT) := l_resource_id(i);
258 p_WinningTerrMember_tbl.group_id(p_WinningTerrMember_tbl.resource_id.COUNT) := l_group_id(i);
259
260 p_WinningTerrMember_tbl.person_id(p_WinningTerrMember_tbl.person_id.COUNT ) := l_person_id(i);
261
262 p_WinningTerrMember_tbl.resource_type(p_WinningTerrMember_tbl.resource_id.COUNT) := 'RS_EMPLOYEE';
263
264 p_WinningTerrMember_tbl.full_access_flag(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.full_access_flag(l_index);
265
266 p_WinningTerrMember_tbl.terr_id(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.terr_id(l_index);
267
268 p_WinningTerrMember_tbl.trans_object_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := 1;
269
270 p_WinningTerrMember_tbl.org_id(p_WinningTerrMember_tbl.org_id.COUNT ) :=p_WinningTerrMember_tbl.org_id(l_index);
271
272 END IF;
273 END LOOP;
274 END IF;
275 END IF;
276 END LOOP;
277 /* FOR i IN l_resource_id.FIRST .. l_resource_id.LAST LOOP
278 aso_debug_pub.add('Explode Team praks1212 ' || p_WinningTerrMember_tbl.resource_id(i),1,'Y');
279 end loop;
280 */
281
282 END IF; /* if p_WinningTerrMember_tbl.resource_id.COUNT > 0 */
283 EXCEPTION
284 WHEN others THEN
285 -- AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS, SQLERRM, TO_CHAR(SQLCODE));
286 x_errbuf := SQLERRM;
287 x_retcode := SQLCODE;
288 x_return_status := FND_API.G_RET_STS_ERROR;
289 END EXPLODE_TEAMS;
290 /************************** End Explode Teams ******************/
291
292
293 PROCEDURE Assign_Sales_Team(
294 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
295 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
296 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
297 P_Qte_Line_Tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type
298 := ASO_QUOTE_PUB.G_MISS_qte_line_TBL,
299 P_Operation IN VARCHAR2 := FND_API.G_MISS_CHAR,
300 X_Qte_Header_Rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
301 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
302 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
303 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
304 )
305 IS
306
307 -- Change START
308 -- Release 12 TAP Changes
309 -- Changes Done by : Girish
310 -- Comments : Call from release 12 has changed and also the record type
311
312 --lx_gen_return_Rec JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type;
313 lx_gen_return_Rec JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
314
315
316 TYPE Keep_Res_Id_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
317
318 Keep_Res_Id Keep_Res_Id_Type;
319
320 -- Change START
321 -- Release 12 MOAC Changes : Bug 4500739
322 -- Changes Done by : Girish
323 -- Comments : Using HR EIT in place of org striped profile
324
325 --l_default_salesrep_prof VARCHAR2(50) := FND_PROFILE.Value('ASO_DEFAULT_PERSON_ID');
326 l_default_salesrep_prof VARCHAR2(50) := ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(ASO_UTILITY_PVT.G_DEFAULT_SALESREP);
327
328 --l_default_role_prof VARCHAR2(50) := FND_PROFILE.Value('ASO_DEFAULT_SALES_ROLE');
329 l_default_role_prof VARCHAR2(50) := ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(ASO_UTILITY_PVT.G_DEFAULT_SALES_ROLE);
330
331 -- Change End
332
333
334 l_ots_role_prof VARCHAR2(50);
335 -- := FND_PROFILE.Value('AST_DEFAULT_ROLE_AND_GROUP');
336
337 l_ots_grp_prof VARCHAR2(50);
338
339 /* := NVL(FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE',
340 G_USER_ID, NULL, 522),
341 FND_PROFILE.Value_Specific(
342 'AST_DEFAULT_ROLE_AND_GROUP',
343 G_USER_ID, NULL, 521));
344 */
345
346 l_role_prof VARCHAR2(50);
347
348 l_sales_team_prof VARCHAR2(30) := NVL(FND_PROFILE.value('ASO_AUTO_TEAM_ASSIGN'),'NONE');
349
350 i NUMBER;
351 j NUMBER;
352 l_api_name CONSTANT VARCHAR2 ( 30 ) := 'ASSIGN_SALES_TEAM';
353 l_api_version_number CONSTANT NUMBER := 1.0;
354 l_exists_flag VARCHAR2(1);
355 l_last_upd_date DATE;
356 l_Qte_Header_Rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
357 l_reassign_flag VARCHAR2(1) := 'N';
358 l_creator_res NUMBER := NULL;
359 l_creator_found VARCHAR2(1);
360 l_creator_role NUMBER;
361 l_creator_grp NUMBER;
362 l_valid VARCHAR2(1);
363 l_sequence NUMBER := null;
364 lx_return_status VARCHAR2(1);
365 l_dynamic VARCHAR2(1000);
366 l_primary_salesagent NUMBER;
367 l_primary_role NUMBER;
368 l_primary_res_grp NUMBER;
369
370 Leave_Proc EXCEPTION;
371
372 l_obsolete_status varchar2(1);
373
374
375 CURSOR C_Get_Header_Info (l_qte_hdr NUMBER) IS
376 SELECT Quote_Header_Id, Quote_Number, Party_Id, Sold_To_Party_Site_Id, Cust_Party_Id,
377 cust_account_id -- Code change done for Bug 11076978
378 FROM ASO_QUOTE_HEADERS_ALL
379 WHERE Quote_Header_Id = l_qte_hdr;
380
381 CURSOR C_Get_Out_Hdr_Info (l_qte_hdr NUMBER) IS
382 SELECT Quote_Header_Id, Last_Update_Date
383 FROM ASO_QUOTE_HEADERS_ALL
384 WHERE Quote_header_Id = l_qte_hdr;
385
386 CURSOR C_Quote_Exists (l_qte_hdr NUMBER) IS
387 SELECT 'Y'
388 FROM ASO_QUOTE_HEADERS_ALL
389 WHERE Quote_Header_Id = l_qte_hdr;
390
391 CURSOR C_Get_Update_Date(qte_hdr_id NUMBER) IS
392 SELECT Last_Update_Date
393 FROM ASO_QUOTE_HEADERS_ALL
394 WHERE Quote_Header_Id = qte_hdr_id;
395
396 CURSOR C_Team_Exists (l_qte_number NUMBER) IS
397 SELECT 'Y'
398 FROM ASO_QUOTE_ACCESSES
399 WHERE Quote_Number = l_qte_number;
400
401 CURSOR C_Get_All_Resource (l_qte_number NUMBER) IS
402 SELECT Resource_Id
403 FROM ASO_QUOTE_ACCESSES
404 WHERE Quote_Number = l_qte_number;
405
406 CURSOR C_Get_Creator_Res (l_user_id NUMBER) IS
407 SELECT resource_id
408 FROM JTF_RS_RESOURCE_EXTNS
409 WHERE user_id = l_user_id
410 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
411
412 CURSOR C_Check_Creator_Res (l_qte_num NUMBER, l_res NUMBER) IS
413 SELECT 'Y', Resource_Grp_Id
414 FROM ASO_QUOTE_ACCESSES
415 WHERE Quote_Number = l_qte_num
416 AND Resource_Id = l_res;
417
418 CURSOR C_Valid_SalesRep (l_res_id NUMBER) IS
419 SELECT 'Y'
420 /* FROM JTF_RS_SRP_VL */ --Commented Code Yogeshwar (MOAC)
421 FROM JTF_RS_SALESREPS_MO_V -- New Code Yogeshwar (MOAC)
422 WHERE resource_id = l_res_id
423 AND NVL(status,'A') = 'A'
424 AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
425 AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate);
426 --Commented Code start Yogeshwar (MOAC)
427 /*
428 AND NVL(org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
429 */
430 --Commented Code End Yogeshwar (MOAC)
431 CURSOR C_Get_Role_From_Code (l_code VARCHAR2) IS
432 SELECT Role_Id
433 FROM JTF_RS_ROLES_B
434 WHERE Role_Code = l_code
435 AND Role_Type_Code IN ('TELESALES', 'SALES', 'FIELDSALES', 'PRM');
436
437 CURSOR C_Get_Resource_Role (l_res NUMBER) IS
438 SELECT Role_Id
439 FROM JTF_RS_ROLE_RELATIONS
440 WHERE Role_Resource_Id = l_res
441 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
442
443 CURSOR C_Get_Res_From_Srep (l_Srep VARCHAR2) IS
444 SELECT Resource_Id
445 /* FROM JTF_RS_SRP_VL */ --Commented Code Yogeshwar (MOAC)
446 FROM JTF_RS_SALESREPS_MO_V --New Code Yogeshwar (MOAC)
447 WHERE Salesrep_Number = l_Srep
448 AND NVL(status,'A') = 'A'
449 AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
450 AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate) ;
451 --Commented Code Start Yogeshwar (MOAC)
452 /*
453 AND NVL(org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
454 */
455 --Commented Code End Yogeshwar (MOAC)
456 BEGIN
457
458 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
459
460 -- Standard Start of API savepoint
461 SAVEPOINT ASSIGN_SALES_TEAM_PVT;
462
463 -- Standard call to check for call compatibility.
464 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
465 1.0,
466 l_api_name,
467 G_PKG_NAME)
468 THEN
469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
471 END IF;
472
473 -- Initialize message list if p_init_msg_list is set to TRUE.
474 IF FND_API.to_Boolean( p_init_msg_list )
475 THEN
476 FND_MSG_PUB.initialize;
477 END IF;
478
479 -- Initialize API return status to SUCCESS
480 x_return_status := FND_API.G_RET_STS_SUCCESS;
481
482 --
483 -- API body
484 --
485 IF aso_debug_pub.g_debug_flag = 'Y' THEN
486 aso_debug_pub.add('Begin Sales_Team_Assign',1,'Y');
487 END IF;
488 -- BASIC VALIDATIONS
489
490 -- Check if profiles are set
491 IF (NVL(FND_PROFILE.Value('ASO_API_ENABLE_SECURITY'),'N') = 'N') OR (l_sales_team_prof = 'NONE') THEN
492
493 IF aso_debug_pub.g_debug_flag = 'Y' THEN
494 aso_debug_pub.add('API_Enable_Sec is N or sales_team_prof is None: ',1,'Y');
495 END IF;
496
497 RAISE Leave_Proc;
498
499 END IF;
500
501 -- Check if quote header id exists
502 OPEN C_Quote_Exists(P_Qte_Header_Rec.Quote_Header_Id);
503 FETCH C_Quote_Exists INTO l_Exists_Flag;
504 IF (C_Quote_Exists%NOTFOUND) OR l_Exists_Flag <> 'Y' THEN
505 CLOSE C_Quote_Exists;
506 x_return_status := FND_API.G_RET_STS_ERROR;
507 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
508 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_ID');
509 FND_MESSAGE.Set_Token('COLUMN', 'ORIGINAL_QUOTE_ID', FALSE);
510 FND_MESSAGE.Set_Token('VALUE', TO_CHAR(p_qte_header_rec.quote_header_id), FALSE);
511 FND_MSG_PUB.ADD;
512 END IF;
513 RAISE FND_API.G_EXC_ERROR;
514 END IF;
515 CLOSE C_Quote_Exists;
516 -- End: Check if quote header id exists
517
518 -- Check Whether record has been changed
519 OPEN C_Get_Update_Date(P_Qte_Header_Rec.Quote_Header_Id);
520 FETCH C_Get_Update_Date INTO l_last_upd_date;
521
522 IF (C_Get_Update_Date%NOTFOUND) OR
523 (l_last_upd_date IS NULL OR l_last_upd_date = FND_API.G_MISS_DATE) THEN
524 x_return_status := FND_API.G_RET_STS_ERROR;
525 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
526 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_COLUMN');
527 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
528 FND_MSG_PUB.ADD;
529 END IF;
530 CLOSE C_Get_Update_Date;
531 RAISE FND_API.G_EXC_ERROR;
532 END IF;
533
534 CLOSE C_Get_Update_Date;
535
536 IF (p_qte_header_rec.last_update_date IS NOT NULL AND
537 p_qte_header_rec.last_update_date <> FND_API.G_MISS_DATE) AND
538 (l_last_upd_date <> p_qte_header_rec.last_update_date) THEN
539 x_return_status := FND_API.G_RET_STS_ERROR;
540 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
541 FND_MESSAGE.Set_Name('ASO', 'ASO_API_RECORD_CHANGED');
542 FND_MESSAGE.Set_Token('INFO', 'quote', FALSE);
543 FND_MSG_PUB.ADD;
544 END IF;
545 RAISE FND_API.G_EXC_ERROR;
546 END IF;
547 -- End: Check Whether record has been changed
548
549 -- Check if a concurrent lock exists
550 ASO_CONC_REQ_INT.Lock_Exists(
551 p_quote_header_id => p_qte_header_rec.quote_header_id,
552 x_status => lx_return_status);
553
554 IF (lx_return_status = FND_API.G_TRUE) THEN
555 x_return_status := FND_API.G_RET_STS_ERROR;
556 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
557 FND_MESSAGE.Set_Name('ASO', 'ASO_CONC_REQUEST_RUNNING');
558 FND_MSG_PUB.ADD;
559 END IF;
560 RAISE FND_API.G_EXC_ERROR;
561 END IF;
562 -- End: Check if a concurrent lock exists
563 -- END BASIC VALIDATIONS
564
565 IF aso_debug_pub.g_debug_flag = 'Y' THEN
566 aso_debug_pub.add('Operation_Code: '||P_Operation,1,'Y');
567 aso_debug_pub.add('Before Truncating Temp Table',1,'Y');
568 END IF;
569
570 DELETE FROM ASO_STEAM_TEMP;
571
572 IF P_Operation <> 'CREATE' THEN
573
574 OPEN C_Get_Header_Info (p_qte_header_rec.Quote_header_id);
575 FETCH C_Get_Header_Info INTO l_qte_header_rec.Quote_header_id,
576 l_qte_header_rec.Quote_Number, l_qte_header_rec.party_id,
577 l_qte_header_rec.sold_to_party_site_id, l_qte_header_rec.cust_party_id,
578 l_qte_header_rec.cust_account_id; -- Code change done for Bug 11076978
579 CLOSE C_Get_Header_Info;
580
581 /*** Start : Code change done for Bug 11076978 ***/
582 If (NVL(FND_PROFILE.Value('ASO_API_ENABLE_SECURITY'),'N') = 'Y') Then
583 If (l_sales_team_prof = 'FULL' OR l_sales_team_prof = 'PARTIAL') Then
584 If (p_qte_header_rec.cust_account_id IS NOT NULL AND
585 p_qte_header_rec.cust_account_id <> FND_API.G_MISS_NUM) Then
586 If (l_qte_header_rec.cust_account_id <> P_Qte_Header_Rec.cust_account_id) Then
587 l_qte_header_rec := P_Qte_Header_Rec;
588 End If;
589 End If;
590 End If;
591 End If;
592 /*** End : Code change done for Bug 11076978 ***/
593
594 OPEN C_Team_Exists(l_Qte_Header_Rec.Quote_Number);
595 FETCH C_Team_Exists INTO l_Reassign_Flag;
596 CLOSE C_Team_Exists;
597
598 ELSE
599
600 l_qte_header_rec := P_Qte_Header_Rec;
601
602 END IF;
603 IF aso_debug_pub.g_debug_flag = 'Y' THEN
604 aso_debug_pub.add('Quote_Number: '||l_qte_header_rec.Quote_Number,1,'Y');
605 aso_debug_pub.add('Reassign_Flag: '||l_Reassign_Flag,1,'Y');
606 END IF;
607
608 ASO_SALES_TEAM_PVT.Get_Sales_Team (
609 P_Init_Msg_List => FND_API.G_FALSE,
610 P_Qte_Header_Rec => l_Qte_Header_Rec,
611 X_Winners_Rec => lx_gen_return_rec,
612 x_return_status => x_return_status,
613 x_msg_count => x_msg_count,
614 x_msg_data => x_msg_data
615 );
616
617 IF aso_debug_pub.g_debug_flag = 'Y' THEN
618 aso_debug_pub.add('After ASO_SALES_TEAM_PVT.get_sales_team: '||x_return_status,1,'Y');
619 END IF;
620
621 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
622 RAISE FND_API.G_EXC_ERROR;
623 END IF;
624 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
626 END IF;
627
628 IF nvl(lx_gen_return_Rec.resource_id.count,0) = 0 THEN
629 IF P_Operation <> 'CREATE' AND P_Operation <> 'UPDATE' AND P_Operation <> 'SUBMIT' THEN
630 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
631 FND_MESSAGE.Set_Name('ASO', 'ASO_NO_SALES_TEAM');
632 FND_MSG_PUB.ADD;
633 END IF;
634 IF aso_debug_pub.g_debug_flag = 'Y' THEN
635 aso_debug_pub.add('After ASO_SALES_TEAM_PVT Added MSG: ',1,'Y');
636 END IF;
637
638 X_Qte_Header_Rec := P_Qte_Header_Rec;
639 RAISE Leave_Proc;
640 ELSE
641 IF P_Operation = 'UPDATE' THEN -- istore case
642
643 IF aso_debug_pub.g_debug_flag = 'Y' THEN
644 aso_debug_pub.add('Before Update_Primary_SalesAgent(oprn=update): ',1,'Y');
645 END IF;
646 ASO_SALES_TEAM_PVT.Update_Primary_SalesInfo (
647 P_Init_Msg_List => FND_API.G_FALSE,
648 P_Qte_Header_Rec => l_Qte_Header_Rec,
649 P_Primary_SalesAgent => NULL,
650 P_Primary_SalesGrp => NULL,
651 P_reassign_flag => l_reassign_flag,
652 X_Qte_Header_Rec => x_Qte_Header_Rec,
653 x_return_status => x_return_status,
654 x_msg_count => x_msg_count,
655 x_msg_data => x_msg_data
656 );
657
658 IF aso_debug_pub.g_debug_flag = 'Y' THEN
659 aso_debug_pub.add('After Update_Primary_SalesInfo(oprn=update): '||x_return_status,1,'Y');
660 END IF;
661
662 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
663 RAISE FND_API.G_EXC_ERROR;
664 END IF;
665 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667 END IF;
668
669 RAISE Leave_Proc;
670
671 ELSIF P_Operation = 'CREATE' THEN -- create case
672
673 OPEN C_Get_Creator_Res(G_USER_ID);
674 FETCH C_Get_Creator_Res INTO l_creator_res;
675 CLOSE C_Get_Creator_Res;
676
677 IF aso_debug_pub.g_debug_flag = 'Y' THEN
678 aso_debug_pub.add('After ASO_SALES_TEAM_PVT No res returned:P_Operation: '||P_Operation,1,'Y');
679 aso_debug_pub.add('After ASO_SALES_TEAM_PVT No res returned:l_creator_res: '||l_creator_res,1,'Y');
680 END IF;
681 IF l_creator_res IS NOT NULL THEN
682
683 -- Role Defaulting Logic
684
685 IF aso_debug_pub.g_debug_flag = 'Y' THEN
686 aso_debug_pub.add('Assign_Sales_Team: Before calling Get_Profile_Obsolete_Status', 1, 'N');
687 END IF;
688
689 l_obsolete_status := aso_utility_pvt.Get_Profile_Obsolete_Status(p_profile_name => 'AST_DEFAULT_ROLE_AND_GROUP',
690 p_application_id => 521);
691
692 IF aso_debug_pub.g_debug_flag = 'Y' THEN
693 aso_debug_pub.add('After calling Get_Profile_Obsolete_Status: l_obsolete_status: ' || l_obsolete_status, 1, 'N');
694 END IF;
695
696 if l_obsolete_status = 'T' then
697
698 l_ots_role_prof := fnd_profile.value('AST_DEFAULT_ROLE');
699
700 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
701
702 IF aso_debug_pub.g_debug_flag = 'Y' THEN
703 aso_debug_pub.add('l_ots_role_prof: ' || l_ots_role_prof, 1, 'N');
704 aso_debug_pub.add('l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
705 END IF;
706
707 l_creator_grp := SUBSTR(l_ots_grp_prof, 1, INSTR(l_ots_grp_prof,'(')-1);
708
709 IF aso_debug_pub.g_debug_flag = 'Y' THEN
710 aso_debug_pub.add('l_creator_grp: ' || l_creator_grp, 1, 'N');
711 END IF;
712
713 if l_creator_grp is null then
714
715 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'AST_DEFAULT_GROUP', G_USER_ID, NULL, 521);
716
717 IF aso_debug_pub.g_debug_flag = 'Y' THEN
718 aso_debug_pub.add('l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
719 END IF;
720
721 l_creator_grp := to_number(l_ots_grp_prof);
722
723 IF aso_debug_pub.g_debug_flag = 'Y' THEN
724 aso_debug_pub.add('l_creator_grp: ' || l_creator_grp, 1, 'N');
725 END IF;
726
727 end if;
728
729 else
730
731 l_ots_role_prof := fnd_profile.value('AST_DEFAULT_ROLE_AND_GROUP');
732
733 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
734
735 IF aso_debug_pub.g_debug_flag = 'Y' THEN
736 aso_debug_pub.add('l_ots_role_prof: ' || l_ots_role_prof, 1, 'N');
737 aso_debug_pub.add('ASF_DEFAULT_GROUP_ROLE value: l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
738 END IF;
739
740 l_creator_grp := SUBSTR(l_ots_grp_prof, 1, INSTR(l_ots_grp_prof,'(')-1);
741
742 IF aso_debug_pub.g_debug_flag = 'Y' THEN
743 aso_debug_pub.add('l_creator_grp: ' || l_creator_grp, 1, 'N');
744 END IF;
745
746 if l_creator_grp is null then
747
748 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'AST_DEFAULT_ROLE_AND_GROUP', G_USER_ID, NULL, 521);
749
750 IF aso_debug_pub.g_debug_flag = 'Y' THEN
751 aso_debug_pub.add('AST_DEFAULT_ROLE_AND_GROUP value :l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
752 END IF;
753
754 l_creator_grp := substr(l_ots_grp_prof, instr(l_ots_grp_prof,':', -1) + 1, length(l_ots_grp_prof));
755
756 IF aso_debug_pub.g_debug_flag = 'Y' THEN
757 aso_debug_pub.add('l_creator_grp: ' || l_creator_grp, 1, 'N');
758 END IF;
759
760 end if;
761
762 end if;
763
764 l_role_prof := SUBSTR(l_ots_role_prof, 1, INSTR(l_ots_role_prof, ':')-1);
765
766 IF aso_debug_pub.g_debug_flag = 'Y' THEN
767 aso_debug_pub.add(' nores:create:l_role_prof: ' || l_role_prof, 1, 'N');
768 END IF;
769
770 OPEN C_Get_Role_From_Code (l_role_prof);
771 FETCH C_Get_Role_From_Code INTO l_creator_role;
772 CLOSE C_Get_Role_From_Code;
773
774 IF l_creator_role IS NULL THEN
775 IF aso_debug_pub.g_debug_flag = 'Y' THEN
776 aso_debug_pub.add('nores:create:Creator Role From Res: '||l_creator_role,1,'N');
777 END IF;
778
779 OPEN C_Get_Resource_Role (l_creator_res);
780 FETCH C_Get_Resource_Role INTO l_creator_role;
781 CLOSE C_Get_Resource_Role;
782
783 END IF;
784
785 IF aso_debug_pub.g_debug_flag = 'Y' THEN
786 aso_debug_pub.add('nores:create:Role Profile: '||l_ots_role_prof,1,'Y');
787 aso_debug_pub.add('nores:create:Role Profile: '||l_role_prof,1,'Y');
788 aso_debug_pub.add('nores:create:Creator Role: '||l_creator_role,1,'Y');
789 aso_debug_pub.add('nores:create:Creator Group: '||l_creator_grp,1,'Y');
790 END IF;
791
792 l_sequence := NULL;
793
794 ASO_QUOTE_ACCESSES_PKG.Insert_Row(
795 px_ACCESS_ID => l_sequence,
796 p_QUOTE_NUMBER => l_Qte_Header_Rec.Quote_Number,
797 p_RESOURCE_ID => l_creator_res,
798 p_RESOURCE_GRP_ID => l_creator_grp,
799 p_CREATED_BY => G_USER_ID,
800 p_CREATION_DATE => SYSDATE,
801 p_LAST_UPDATED_BY => G_USER_ID,
802 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
803 p_LAST_UPDATE_DATE => SYSDATE,
804 p_REQUEST_ID => FND_API.G_MISS_NUM,
805 p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
806 p_PROGRAM_ID => FND_API.G_MISS_NUM,
807 p_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
808 p_KEEP_FLAG => 'N',
809 p_UPDATE_ACCESS_FLAG => 'Y',
810 p_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
811 p_TERRITORY_ID => FND_API.G_MISS_NUM,
812 p_TERRITORY_SOURCE_FLAG => 'N',
813 p_ROLE_ID => l_creator_role,
814 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
815 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
816 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
817 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
818 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
819 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
820 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
821 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
822 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
823 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
824 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
825 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
826 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
827 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
828 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
829 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
830 p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
831 p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
832 p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
833 p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
834 p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
835 p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
836 );
837
838 OPEN C_Valid_SalesRep (l_creator_res);
839 FETCH C_Valid_SalesRep INTO l_valid;
840 CLOSE C_Valid_SalesRep;
841
842 IF aso_debug_pub.g_debug_flag = 'Y' THEN
843 aso_debug_pub.add('nores:create:Creator Valid SalesRep : '||l_valid,1,'Y');
844 END IF;
845
846 IF l_valid = 'Y' THEN
847 l_primary_salesagent := l_creator_res;
848 l_primary_res_grp := l_creator_grp;
849 END IF;
850
851 END IF; -- creator not null
852
853 IF l_primary_salesagent IS NULL THEN
854
855 IF l_default_salesrep_prof IS NOT NULL THEN
856 OPEN C_Get_Res_From_Srep (l_default_salesrep_prof);
857 FETCH C_Get_Res_From_Srep INTO l_primary_salesagent;
858 CLOSE C_Get_Res_From_Srep;
859
860 IF aso_debug_pub.g_debug_flag = 'Y' THEN
861 aso_debug_pub.add('nores:create:Assign_Sales_Team: Default SalesRep: '||l_primary_salesagent,1,'N');
862 aso_debug_pub.add('nores:create:Assign_Sales_Team: Default SalesGrp: '||l_primary_res_grp,1,'N');
863 END IF;
864 ELSE
865 x_return_status := FND_API.G_RET_STS_ERROR;
866 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
867
868 -- Created new message to display the error message more appropriately - Girish Bug 4654938
869 -- FND_MESSAGE.Set_Name('ASO', 'ASO_API_NO_PROFILE_VALUE');
870 -- FND_MESSAGE.Set_Token('PROFILE', 'ASO_DEFAULT_PERSON_ID', FALSE);
871 FND_MESSAGE.Set_Name('ASO', 'ASO_NO_DEFAULT_VALUE');
872 FND_MESSAGE.Set_Token('PROFILE', 'ASO_DEFAULT_SALESREP', TRUE);
873
874 FND_MSG_PUB.Add;
875 END IF;
876 RAISE FND_API.G_EXC_ERROR;
877 END IF; -- salesrep_prof
878
879 l_primary_role := l_default_role_prof;
880
881 IF aso_debug_pub.g_debug_flag = 'Y' THEN
882 aso_debug_pub.add('Before calling Get_Profile_Obsolete_Status', 1, 'N');
883 END IF;
884
885 l_obsolete_status := aso_utility_pvt.Get_Profile_Obsolete_Status(p_profile_name => 'AST_DEFAULT_ROLE_AND_GROUP',
886 p_application_id => 521);
887
888 IF aso_debug_pub.g_debug_flag = 'Y' THEN
889 aso_debug_pub.add('After calling Get_Profile_Obsolete_Status: l_obsolete_status: ' || l_obsolete_status, 1, 'N');
890 END IF;
891
892 if l_obsolete_status = 'T' then
893
894 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
895
896 IF aso_debug_pub.g_debug_flag = 'Y' THEN
897 aso_debug_pub.add('l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
898 END IF;
899
900 l_primary_res_grp := SUBSTR(l_ots_grp_prof, 1, INSTR(l_ots_grp_prof,'(')-1);
901
902 IF aso_debug_pub.g_debug_flag = 'Y' THEN
903 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
904 END IF;
905
906 if l_primary_res_grp is null then
907
908 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'AST_DEFAULT_GROUP', G_USER_ID, NULL, 521);
909
910 IF aso_debug_pub.g_debug_flag = 'Y' THEN
911 aso_debug_pub.add('l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
912 END IF;
913
914 l_primary_res_grp := to_number(l_ots_grp_prof);
915
916 IF aso_debug_pub.g_debug_flag = 'Y' THEN
917 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
918 END IF;
919
920 end if;
921
922 else
923
924 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
925
926 IF aso_debug_pub.g_debug_flag = 'Y' THEN
927 aso_debug_pub.add('ASF_DEFAULT_GROUP_ROLE value: l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
928 END IF;
929
930 l_primary_res_grp := SUBSTR(l_ots_grp_prof, 1, INSTR(l_ots_grp_prof,'(')-1);
931
932 IF aso_debug_pub.g_debug_flag = 'Y' THEN
933 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
934 END IF;
935
936 if l_primary_res_grp is null then
937
938 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'AST_DEFAULT_ROLE_AND_GROUP', G_USER_ID, NULL, 521);
939
940 IF aso_debug_pub.g_debug_flag = 'Y' THEN
941 aso_debug_pub.add('AST_DEFAULT_ROLE_AND_GROUP value :l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
942 END IF;
943
944 l_primary_res_grp := substr(l_ots_grp_prof, instr(l_ots_grp_prof,':', -1) + 1, length(l_ots_grp_prof));
945
946 IF aso_debug_pub.g_debug_flag = 'Y' THEN
947 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
948 END IF;
949
950 end if;
951
952 end if;
953
954 IF aso_debug_pub.g_debug_flag = 'Y' THEN
955 aso_debug_pub.add('nores:create:Assign_Sales_Team: Default Role: '||l_primary_role,1,'N');
956 aso_debug_pub.add('nores:create:Assign_Sales_Team: Default Grp: '||l_primary_res_grp,1,'N');
957 END IF;
958
959 END IF; -- salesagent is NULL
960
961 IF l_primary_role IS NULL THEN
962 OPEN C_Get_Resource_Role (l_primary_salesagent);
963 FETCH C_Get_Resource_Role INTO l_primary_role;
964 CLOSE C_Get_Resource_Role;
965
966 IF aso_debug_pub.g_debug_flag = 'Y' THEN
967 aso_debug_pub.add('nores:create:Assign_Sales_Team: Role From Res: '||l_primary_role,1,'N');
968 END IF;
969 END IF;
970
971 IF l_valid IS NULL OR l_valid <> 'Y' THEN
972 IF aso_debug_pub.g_debug_flag = 'Y' THEN
973 aso_debug_pub.add('nores:create:Assign_Sales_Team: Before primary salesagent insert:l_valid '||l_valid,1,'N');
974 END IF;
975 l_sequence := NULL;
976
977 ASO_QUOTE_ACCESSES_PKG.Insert_Row(
978 px_ACCESS_ID => l_sequence,
979 p_QUOTE_NUMBER => P_Qte_Header_Rec.Quote_Number,
980 p_RESOURCE_ID => l_primary_salesagent,
981 p_RESOURCE_GRP_ID => l_primary_res_grp,
982 p_CREATED_BY => G_USER_ID,
983 p_CREATION_DATE => SYSDATE,
984 p_LAST_UPDATED_BY => G_USER_ID,
985 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
986 p_LAST_UPDATE_DATE => SYSDATE,
987 p_REQUEST_ID => FND_API.G_MISS_NUM,
988 p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
989 p_PROGRAM_ID => FND_API.G_MISS_NUM,
990 p_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
991 p_KEEP_FLAG => FND_API.G_MISS_CHAR,
992 p_UPDATE_ACCESS_FLAG => 'Y',
993 p_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
994 p_TERRITORY_ID => FND_API.G_MISS_NUM,
995 p_TERRITORY_SOURCE_FLAG => FND_API.G_MISS_CHAR,
996 p_ROLE_ID => l_primary_role,
997 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
998 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
999 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
1000 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
1001 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
1002 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
1003 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
1004 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
1005 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
1006 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
1007 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
1008 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
1009 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
1010 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
1011 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
1012 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
1013 p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
1014 p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
1015 p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
1016 p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
1017 p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
1018 p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
1019 );
1020
1021 END IF;
1022
1023 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1024 aso_debug_pub.add('nores:create:Assign_Sales_Team: Before primary salesagent insert in qte_hdr ',1,'N');
1025 END IF;
1026
1027 UPDATE ASO_QUOTE_HEADERS_ALL
1028 SET Resource_Id = l_primary_salesagent,
1029 Resource_Grp_Id = l_primary_res_grp,
1030 last_update_date = sysdate,
1031 last_updated_by = fnd_global.user_id,
1032 last_update_login = fnd_global.conc_login_id,
1033 object_version_number = object_version_number+1
1034 WHERE quote_number = P_Qte_Header_Rec.quote_number
1035 AND max_version_flag = 'Y'
1036 RETURNING quote_header_id, last_update_date, resource_id, resource_grp_id, object_version_number
1037 INTO l_qte_header_rec.Quote_Header_Id, l_qte_header_rec.Last_Update_Date,
1038 l_qte_header_rec.resource_id, l_qte_header_rec.resource_grp_id, l_qte_header_rec.object_version_number;
1039
1040 END IF; -- operation = CREATE
1041
1042 X_Qte_Header_Rec := l_Qte_Header_Rec;
1043 RAISE Leave_Proc;
1044
1045 END IF; -- p_operation ELSE
1046 END IF; -- res count = 0
1047
1048 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1049 aso_debug_pub.add('Before Temp_Insert'||NVL(lx_gen_return_Rec.resource_id.COUNT,0),1,'Y');
1050 END IF;
1051
1052 FORALL i IN lx_gen_return_Rec.resource_id.FIRST..lx_gen_return_Rec.resource_id.LAST
1053 INSERT INTO ASO_STEAM_TEMP ( Access_Id,
1054 Quote_Number,
1055 Resource_Id,
1056 Resource_Grp_Id,
1057 Created_By,
1058 Creation_Date,
1059 Last_Updated_By,
1060 Last_Update_Login,
1061 Last_Update_Date,
1062 Keep_Flag,
1063 Full_Access_Flag,
1064 Territory_Id,
1065 Territory_Source_Flag,
1066 Role_Id )
1067 SELECT null,
1068 l_Qte_Header_Rec.Quote_Number,
1069 lx_gen_return_Rec.resource_id(i),
1070 lx_gen_return_Rec.group_id(i),
1071 G_USER_ID,
1072 SYSDATE,
1073 G_USER_ID,
1074 G_LOGIN_ID,
1075 SYSDATE,
1076 NULL,
1077 lx_gen_return_Rec.full_access_flag(i),
1078 lx_gen_return_Rec.terr_id(i),
1079 'Y',
1080 DECODE(lx_gen_return_Rec.role(i),NULL,NULL,A.Role_Id)
1081 FROM JTF_RS_ROLES_B A
1082 WHERE A.Role_Code = NVL(lx_gen_return_Rec.role(i),A.Role_Code)
1083 AND rownum = 1;
1084
1085 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1086 aso_debug_pub.add('After Temp Insert',1,'Y');
1087 END IF;
1088
1089 IF l_Reassign_Flag = 'Y' THEN
1090
1091 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1092 select count(*) into i from aso_steam_temp;
1093 aso_debug_pub.add('count: '||i,1,'Y');
1094
1095 aso_debug_pub.add('Before Delete Not Kept Existing Res ',1,'Y');
1096 END IF;
1097 DELETE FROM ASO_QUOTE_ACCESSES C
1098 WHERE C.resource_id NOT IN
1099 (SELECT A.resource_id
1100 FROM ASO_QUOTE_ACCESSES A , ASO_STEAM_TEMP B
1101 WHERE ((A.resource_id = B.resource_id
1102 AND NVL(A.resource_grp_id, -999) = NVL(B.resource_grp_id, -999)
1103 AND NVL(A.role_id, -999) = NVL(B.role_id, -999)
1104 AND NVL(A.keep_flag,'N') = 'N')
1105 OR NVL(A.keep_flag,'N') = 'Y')
1106 AND A.Quote_Number = l_qte_header_rec.quote_number)
1107 AND C.Quote_Number = l_qte_header_rec.quote_number;
1108
1109 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1110 select count(*) into i from aso_steam_temp;
1111 aso_debug_pub.add('count: '||i,1,'Y');
1112
1113 aso_debug_pub.add('Before Delete Kept Res ',1,'Y');
1114 END IF;
1115 OPEN C_Get_All_Resource(l_Qte_Header_Rec.Quote_Number);
1116 FETCH C_Get_All_Resource BULK COLLECT INTO Keep_Res_Id;
1117 CLOSE C_Get_All_Resource;
1118
1119 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1120 aso_debug_pub.add('Keep_Res_Id.COUNT: '||NVL(Keep_Res_Id.COUNT,0),1,'Y');
1121 END IF;
1122 IF NVL(Keep_Res_Id.COUNT,0) > 0 THEN
1123 FORALL i IN Keep_Res_Id.FIRST..Keep_Res_Id.LAST
1124 DELETE FROM ASO_STEAM_TEMP
1125 WHERE Resource_Id = Keep_Res_Id(i);
1126 END IF;
1127 END IF;
1128
1129 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1130 select count(*) into i from aso_steam_temp;
1131 aso_debug_pub.add('count: '||i,1,'Y');
1132
1133 aso_debug_pub.add('Before Delete Invalid Roles ',1,'Y');
1134 END IF;
1135 DELETE FROM ASO_STEAM_TEMP
1136 WHERE Role_Id IS NOT NULL
1137 AND Role_Id NOT IN ( SELECT Role_Id
1138 FROM JTF_RS_ROLES_B
1139 WHERE Role_Type_Code IN ('TELESALES', 'SALES','FIELDSALES','PRM'));
1140
1141 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1142 select count(*) into i from aso_steam_temp;
1143 aso_debug_pub.add('count: '||i,1,'Y');
1144
1145 aso_debug_pub.add('Before Delete Duplicate Res/Roles/Grp combos ',1,'Y');
1146 END IF;
1147 l_dynamic := 'DELETE FROM ASO_STEAM_TEMP '||
1148 'WHERE rowid NOT IN ( SELECT rowid '||
1149 'FROM ( SELECT rowid, dense_rank() OVER '||
1150 '( PARTITION BY Resource_Id '||
1151 'ORDER BY Role_Id DESC nulls last, Resource_Grp_Id DESC nulls last) AS Rank_Val '||
1152 'FROM ASO_STEAM_TEMP '||
1153 'ORDER BY Role_Id DESC nulls last, Resource_Grp_Id DESC nulls last ) '||
1154 'WHERE Rank_Val = 1 )';
1155
1156 EXECUTE IMMEDIATE l_dynamic;
1157
1158 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1159 select count(*) into i from aso_steam_temp;
1160 aso_debug_pub.add('count: '||i,1,'Y');
1161
1162 aso_debug_pub.add('Before Delete Duplicate Resources ',1,'Y');
1163 END IF;
1164 DELETE FROM ASO_STEAM_TEMP
1165 WHERE rowid IN (
1166 SELECT rowid FROM ASO_STEAM_TEMP
1167 GROUP BY rowid, Resource_Id
1168 MINUS
1169 SELECT min(rowid) FROM ASO_STEAM_TEMP
1170 GROUP BY Resource_Id);
1171
1172 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1173 select count(*) into i from aso_steam_temp;
1174 aso_debug_pub.add('count: '||i,1,'Y');
1175
1176 aso_debug_pub.add('Before Insert into Quote_Accesses ',1,'Y');
1177 END IF;
1178 INSERT INTO ASO_QUOTE_ACCESSES ( ACCESS_ID,
1179 QUOTE_NUMBER,
1180 RESOURCE_ID,
1181 RESOURCE_GRP_ID,
1182 CREATED_BY,
1183 CREATION_DATE,
1184 LAST_UPDATED_BY,
1185 LAST_UPDATE_LOGIN,
1186 LAST_UPDATE_DATE,
1187 UPDATE_ACCESS_FLAG,
1188 TERRITORY_ID,
1189 TERRITORY_SOURCE_FLAG,
1190 ROLE_ID )
1191 SELECT ASO_QUOTE_ACCESSES_S.nextval,
1192 Quote_Number,
1193 Resource_Id,
1194 Resource_Grp_Id,
1195 Created_By,
1196 Creation_Date,
1197 Last_Updated_By,
1198 Last_Update_Login,
1199 Last_Update_Date,
1200 Full_Access_Flag,
1201 Territory_Id,
1202 Territory_Source_Flag,
1203 Role_Id
1204 FROM ASO_STEAM_TEMP
1205 WHERE Quote_Number = l_Qte_Header_Rec.Quote_Number
1206 and not exists (select 1 from
1207 ASO_QUOTE_ACCESSES where resource_id = ASO_STEAM_TEMP.resource_id
1208 and quote_number = l_Qte_Header_Rec.Quote_Number);
1209
1210 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1211 aso_debug_pub.add('After Insert into Quote_Accesses ',1,'Y');
1212 END IF;
1213
1214 IF P_Operation = 'CREATE' THEN
1215
1216 OPEN C_Get_Creator_Res(G_USER_ID);
1217 FETCH C_Get_Creator_Res INTO l_creator_res;
1218 CLOSE C_Get_Creator_Res;
1219
1220 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1221 aso_debug_pub.add('Before Creator Res:l_creator_res: '||l_creator_res,1,'Y');
1222 END IF;
1223
1224 IF l_creator_res IS NOT NULL THEN
1225
1226 OPEN C_Check_Creator_Res(l_Qte_Header_Rec.Quote_Number, l_creator_res);
1227 FETCH C_Check_Creator_Res INTO l_creator_found, l_creator_grp;
1228 CLOSE C_Check_Creator_Res;
1229 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1230 aso_debug_pub.add('Before Creator Res:G_USER_ID: '||G_USER_ID,1,'Y');
1231 aso_debug_pub.add('Fetch Creator Res: '||l_creator_res,1,'Y');
1232 aso_debug_pub.add('Fetch Creator Grp: '||l_creator_grp,1,'Y');
1233 aso_debug_pub.add('Creator Found: '||l_creator_found,1,'Y');
1234 END IF;
1235
1236 IF l_creator_found IS NULL OR l_creator_found <> 'Y' THEN
1237 -- Role Defaulting Logic
1238
1239 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1240 aso_debug_pub.add('Assign_Sales_Team: Before calling Get_Profile_Obsolete_Status', 1, 'N');
1241 END IF;
1242
1243 l_obsolete_status := aso_utility_pvt.Get_Profile_Obsolete_Status(p_profile_name => 'AST_DEFAULT_ROLE_AND_GROUP',
1244 p_application_id => 521);
1245
1246 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1247 aso_debug_pub.add('After calling Get_Profile_Obsolete_Status: l_obsolete_status: ' || l_obsolete_status, 1, 'N');
1248 END IF;
1249
1250 if l_obsolete_status = 'T' then
1251
1252 l_ots_role_prof := fnd_profile.value('AST_DEFAULT_ROLE');
1253
1254 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
1255
1256 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1257 aso_debug_pub.add('l_ots_role_prof: ' || l_ots_role_prof, 1, 'N');
1258 aso_debug_pub.add('l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
1259 END IF;
1260
1261 l_creator_grp := SUBSTR(l_ots_grp_prof, 1, INSTR(l_ots_grp_prof,'(')-1);
1262
1263 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1264 aso_debug_pub.add('l_creator_grp: ' || l_creator_grp, 1, 'N');
1265 END IF;
1266
1267 if l_creator_grp is null then
1268
1269 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'AST_DEFAULT_GROUP', G_USER_ID, NULL, 521);
1270
1271 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1272 aso_debug_pub.add('l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
1273 END IF;
1274
1275 l_creator_grp := to_number(l_ots_grp_prof);
1276
1277 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1278 aso_debug_pub.add('l_creator_grp: ' || l_creator_grp, 1, 'N');
1279 END IF;
1280
1281 end if;
1282
1283 else
1284
1285 l_ots_role_prof := fnd_profile.value('AST_DEFAULT_ROLE_AND_GROUP');
1286
1287 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
1288
1289 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1290 aso_debug_pub.add('l_ots_role_prof: ' || l_ots_role_prof, 1, 'N');
1291 aso_debug_pub.add('ASF_DEFAULT_GROUP_ROLE value: l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
1292 END IF;
1293
1294 l_creator_grp := SUBSTR(l_ots_grp_prof, 1, INSTR(l_ots_grp_prof,'(')-1);
1295
1296 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1297 aso_debug_pub.add('l_creator_grp: ' || l_creator_grp, 1, 'N');
1298 END IF;
1299
1300 if l_creator_grp is null then
1301
1302 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'AST_DEFAULT_ROLE_AND_GROUP', G_USER_ID, NULL, 521);
1303
1304 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1305 aso_debug_pub.add('AST_DEFAULT_ROLE_AND_GROUP value :l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
1306 END IF;
1307
1308 l_creator_grp := substr(l_ots_grp_prof, instr(l_ots_grp_prof,':', -1) + 1, length(l_ots_grp_prof));
1309
1310 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1311 aso_debug_pub.add('l_creator_grp: ' || l_creator_grp, 1, 'N');
1312 END IF;
1313
1314 end if;
1315
1316 end if;
1317
1318 l_role_prof := SUBSTR(l_ots_role_prof, 1, INSTR(l_ots_role_prof, ':')-1);
1319
1320 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1321 aso_debug_pub.add('l_role_prof: ' || l_role_prof, 1, 'N');
1322 END IF;
1323
1324 OPEN C_Get_Role_From_Code (l_role_prof);
1325 FETCH C_Get_Role_From_Code INTO l_creator_role;
1326 CLOSE C_Get_Role_From_Code;
1327
1328 IF l_creator_role IS NULL THEN
1329 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1330 aso_debug_pub.add('nores:create:Creator Role From Res: '||l_creator_role,1,'N');
1331 END IF;
1332
1333 OPEN C_Get_Resource_Role (l_creator_res);
1334 FETCH C_Get_Resource_Role INTO l_creator_role;
1335 CLOSE C_Get_Resource_Role;
1336
1337 END IF;
1338
1339 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1340 aso_debug_pub.add('Role Profile: '||l_ots_role_prof,1,'Y');
1341 aso_debug_pub.add('Role Profile: '||l_role_prof,1,'Y');
1342 aso_debug_pub.add('Creator Role: '||l_creator_role,1,'Y');
1343 aso_debug_pub.add('Creator Group: '||l_creator_grp,1,'Y');
1344 END IF;
1345
1346 l_sequence := NULL;
1347
1348 ASO_QUOTE_ACCESSES_PKG.Insert_Row(
1349 px_ACCESS_ID => l_sequence,
1350 p_QUOTE_NUMBER => l_Qte_Header_Rec.Quote_Number,
1351 p_RESOURCE_ID => l_creator_res,
1352 p_RESOURCE_GRP_ID => l_creator_grp,
1353 p_CREATED_BY => G_USER_ID,
1354 p_CREATION_DATE => SYSDATE,
1355 p_LAST_UPDATED_BY => G_USER_ID,
1356 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
1357 p_LAST_UPDATE_DATE => SYSDATE,
1358 p_REQUEST_ID => FND_API.G_MISS_NUM,
1359 p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
1360 p_PROGRAM_ID => FND_API.G_MISS_NUM,
1361 p_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
1362 p_KEEP_FLAG => 'N',
1363 p_UPDATE_ACCESS_FLAG => 'Y',
1364 p_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
1365 p_TERRITORY_ID => FND_API.G_MISS_NUM,
1366 p_TERRITORY_SOURCE_FLAG => 'N',
1367 p_ROLE_ID => l_creator_role,
1368 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
1369 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
1370 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
1371 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
1372 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
1373 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
1374 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
1375 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
1376 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
1377 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
1378 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
1379 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
1380 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
1381 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
1382 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
1383 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
1384 p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
1385 p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
1386 p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
1387 p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
1388 p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
1389 p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
1390 );
1391
1392 END IF;
1393
1394 OPEN C_Valid_SalesRep (l_creator_res);
1395 FETCH C_Valid_SalesRep INTO l_valid;
1396 CLOSE C_Valid_SalesRep;
1397
1398 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1399 aso_debug_pub.add('Creator Valid SalesRep : '||l_valid,1,'Y');
1400 END IF;
1401
1402 IF l_valid = 'Y' THEN
1403 l_primary_salesagent := l_creator_res;
1404 END IF;
1405
1406 END IF; -- creator_res is not null
1407
1408 END IF; -- CREATE
1409
1410 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1411 aso_debug_pub.add('Before Update_Primary_SalesAgent: ',1,'Y');
1412 END IF;
1413 ASO_SALES_TEAM_PVT.Update_Primary_SalesInfo (
1414 P_Init_Msg_List => FND_API.G_FALSE,
1415 P_Qte_Header_Rec => l_Qte_Header_Rec,
1416 P_Primary_SalesAgent => l_primary_salesagent,
1417 P_Primary_SalesGrp => l_creator_grp,
1418 P_reassign_flag => l_reassign_flag,
1419 X_Qte_Header_Rec => x_Qte_Header_Rec,
1420 x_return_status => x_return_status,
1421 x_msg_count => x_msg_count,
1422 x_msg_data => x_msg_data
1423 );
1424
1425 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1426 aso_debug_pub.add('After Update_Primary_SalesInfo: '||x_return_status,1,'Y');
1427 END IF;
1428
1429 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1430 RAISE FND_API.G_EXC_ERROR;
1431 END IF;
1432 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1433 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1434 END IF;
1435
1436 -- Change START
1437 -- Release 12 TAP Changes
1438 -- Girish Sachdeva 8/30/2005
1439 -- Adding the call to insert record in the ASO_CHANGED_QUOTES
1440
1441 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1442 aso_debug_pub.add('ASO_SALES_TEAM_PVT.Assign_Sales_Team : Calling ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES, quote number : ' || l_Qte_Header_Rec.Quote_Number, 1, 'Y');
1443 END IF;
1444
1445 -- Call to insert record in ASO_CHANGED_QUOTES
1446 ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES(l_Qte_Header_Rec.Quote_Number);
1447
1448 -- Change END
1449
1450
1451 EXCEPTION
1452 WHEN Leave_Proc THEN
1453 NULL;
1454
1455 WHEN FND_API.G_EXC_ERROR THEN
1456 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1457 P_API_NAME => L_API_NAME,
1458 P_PKG_NAME => G_PKG_NAME,
1459 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
1460 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
1461 P_SQLCODE => SQLCODE,
1462 P_SQLERRM => SQLERRM,
1463 X_MSG_COUNT => X_MSG_COUNT,
1464 X_MSG_DATA => X_MSG_DATA,
1465 X_RETURN_STATUS => X_RETURN_STATUS
1466 );
1467
1468 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1469 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1470 P_API_NAME => L_API_NAME,
1471 P_PKG_NAME => G_PKG_NAME,
1472 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1473 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
1474 P_SQLCODE => SQLCODE,
1475 P_SQLERRM => SQLERRM,
1476 X_MSG_COUNT => X_MSG_COUNT,
1477 X_MSG_DATA => X_MSG_DATA,
1478 X_RETURN_STATUS => X_RETURN_STATUS
1479 );
1480
1481 WHEN OTHERS THEN
1482 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1483 P_API_NAME => L_API_NAME,
1484 P_PKG_NAME => G_PKG_NAME,
1485 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
1486 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
1487 P_SQLCODE => SQLCODE,
1488 P_SQLERRM => SQLERRM,
1489 X_MSG_COUNT => X_MSG_COUNT,
1490 X_MSG_DATA => X_MSG_DATA,
1491 X_RETURN_STATUS => X_RETURN_STATUS
1492 );
1493
1494 END Assign_Sales_Team;
1495
1496 -- Change START
1497 -- Release 12 TAP Changes
1498 -- Changes Done By Girish
1499 -- Commenting the whole procedure as the realtime call has changed.
1500 /*
1501 PROCEDURE Get_Sales_Team(
1502 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1503 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
1504 P_Qte_Line_Tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type
1505 := ASO_QUOTE_PUB.G_MISS_qte_line_TBL,
1506 X_Winners_Rec OUT NOCOPY JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type,
1507 X_Return_Status OUT NOCOPY VARCHAR2,
1508 X_Msg_Count OUT NOCOPY NUMBER,
1509 X_Msg_Data OUT NOCOPY VARCHAR2
1510 )
1511 IS
1512
1513 CURSOR C_Get_Party_Info (l_party_id NUMBER) IS
1514 SELECT UPPER(party_name) party_name, UPPER(category_code) category_code, employees_total,
1515 UPPER(SIC_Code) SIC_Code, UPPER(SIC_Code_Type) SIC_Code_Type
1516 FROM HZ_PARTIES
1517 WHERE party_id = l_party_id;
1518
1519 CURSOR C_Get_Party_Site_Info (l_party_site_id NUMBER) IS
1520 SELECT UPPER(B.city) city, UPPER(B.county) county, UPPER(B.state) state, UPPER(B.province) province,
1521 UPPER(B.postal_code) postal_code, UPPER(B.country) country
1522 FROM HZ_PARTY_SITES A, HZ_LOCATIONS B
1523 WHERE A.Location_Id = B.Location_Id
1524 AND A.party_site_id = l_party_site_id;
1525
1526 CURSOR C_Get_Cust_Cont_Info (l_party_id NUMBER) IS
1527 SELECT UPPER(Phone_Area_Code) Phone_Area_Code
1528 FROM HZ_CONTACT_POINTS
1529 WHERE Owner_Table_Id = l_party_id
1530 AND Owner_Table_Name = 'HZ_PARTIES'
1531 AND Contact_Point_Type = 'PHONE'
1532 AND Status = 'A'
1533 AND Primary_Flag = 'Y';
1534
1535
1536 lp_gen_bulk_Rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
1537
1538 C_Party_Rec C_Get_Party_Info%ROWTYPE;
1539 C_Party_Site_Rec C_Get_Party_Site_Info%ROWTYPE;
1540 C_Cust_Cont_Rec C_Get_Cust_Cont_Info%ROWTYPE;
1541
1542 l_api_name CONSTANT VARCHAR2 ( 30 ) := 'Get_Sales_Team';
1543
1544 BEGIN
1545
1546 -- Standard Start of API savepoint
1547 SAVEPOINT GET_SALES_TEAM_PVT;
1548
1549 x_return_status := FND_API.G_RET_STS_SUCCESS;
1550
1551 -- Get input info to pass to TM
1552 OPEN C_Get_Party_Info (P_Qte_Header_Rec.cust_party_id);
1553 FETCH C_Get_Party_Info INTO C_Party_Rec;
1554 CLOSE C_Get_Party_Info;
1555
1556 OPEN C_Get_Party_Site_Info (P_Qte_Header_Rec.sold_to_party_site_id);
1557 FETCH C_Get_Party_Site_Info INTO C_Party_Site_Rec;
1558 CLOSE C_Get_Party_Site_Info;
1559
1560 OPEN C_Get_Cust_Cont_Info (P_Qte_Header_Rec.party_id);
1561 FETCH C_Get_Cust_Cont_Info INTO C_Cust_Cont_Rec;
1562 CLOSE C_Get_Cust_Cont_Info;
1563 -- End: Get input info to pass to TM
1564
1565 -- Instantiate input rec for the input bulk_trans_rec_type
1566 -- bulk_trans_rec_type instantiation
1567 -- logic control properties
1568 lp_gen_bulk_rec.trans_object_id := JTF_TERR_NUMBER_LIST(null);
1569 lp_gen_bulk_rec.trans_detail_object_id := JTF_TERR_NUMBER_LIST(null);
1570
1571 lp_gen_bulk_rec.trans_object_id(1) := P_qte_header_rec.quote_header_id;
1572
1573 -- extend qualifier elements
1574 lp_gen_bulk_rec.SQUAL_CHAR01.EXTEND;
1575 lp_gen_bulk_rec.SQUAL_CHAR02.EXTEND;
1576 lp_gen_bulk_rec.SQUAL_CHAR03.EXTEND;
1577 lp_gen_bulk_rec.SQUAL_CHAR04.EXTEND;
1578 lp_gen_bulk_rec.SQUAL_CHAR05.EXTEND;
1579 lp_gen_bulk_rec.SQUAL_CHAR06.EXTEND;
1580 lp_gen_bulk_rec.SQUAL_CHAR07.EXTEND;
1581 lp_gen_bulk_rec.SQUAL_CHAR08.EXTEND;
1582 lp_gen_bulk_rec.SQUAL_CHAR09.EXTEND;
1583 lp_gen_bulk_rec.SQUAL_CHAR10.EXTEND;
1584
1585 -- transaction qualifier values
1586 lp_gen_bulk_rec.SQUAL_CHAR01(1) := C_Party_Rec.party_name; -- Customer Name Range
1587 lp_gen_bulk_rec.SQUAL_CHAR02(1) := C_Party_Site_Rec.city; -- City
1588 lp_gen_bulk_rec.SQUAL_CHAR03(1) := C_Party_Site_Rec.county; -- County
1589 lp_gen_bulk_rec.SQUAL_CHAR04(1) := C_Party_Site_Rec.state; -- State
1590 lp_gen_bulk_rec.SQUAL_CHAR05(1) := C_Party_Site_Rec.province; -- Province
1591 lp_gen_bulk_rec.SQUAL_CHAR06(1) := C_Party_Site_Rec.postal_code; -- Postal Code
1592 lp_gen_bulk_rec.SQUAL_CHAR07(1) := C_Party_Site_Rec.country; -- Country
1593 lp_gen_bulk_rec.SQUAL_CHAR08(1) := C_Cust_Cont_Rec.Phone_Area_Code; -- Area Code
1594 lp_gen_bulk_rec.SQUAL_CHAR09(1) := C_Party_Rec.category_code; -- Customer Category
1595 lp_gen_bulk_rec.SQUAL_CHAR10(1) := C_Party_Rec.SIC_Code_Type||': '||C_Party_Rec.SIC_Code; --SIC Code
1596
1597 lp_gen_bulk_rec.SQUAL_NUM01.EXTEND;
1598 lp_gen_bulk_rec.SQUAL_NUM02.EXTEND;
1599 lp_gen_bulk_rec.SQUAL_NUM03.EXTEND;
1600 lp_gen_bulk_rec.SQUAL_NUM04.EXTEND;
1601 lp_gen_bulk_rec.SQUAL_NUM05.EXTEND;
1602 lp_gen_bulk_rec.SQUAL_NUM06.EXTEND;
1603 lp_gen_bulk_rec.SQUAL_NUM07.EXTEND;
1604 lp_gen_bulk_rec.SQUAL_NUM08.EXTEND;
1605 lp_gen_bulk_rec.SQUAL_NUM09.EXTEND;
1606 lp_gen_bulk_rec.SQUAL_NUM10.EXTEND;
1607 lp_gen_bulk_rec.SQUAL_NUM50.EXTEND;
1608
1609 -- transaction qualifier values
1610 lp_gen_bulk_rec.SQUAL_NUM01(1) := P_Qte_Header_Rec.cust_party_id; -- PARTY_ID
1611 lp_gen_bulk_rec.SQUAL_NUM02(1) := P_Qte_Header_Rec.sold_to_party_site_id; -- PARTY_SITE_ID
1612 lp_gen_bulk_rec.SQUAL_NUM03(1) := P_Qte_Header_Rec.cust_party_id; -- Sales Partner Of
1613 lp_gen_bulk_rec.SQUAL_NUM04(1) := P_Qte_Header_Rec.cust_party_id; -- Acct Hierarchy
1614 lp_gen_bulk_rec.SQUAL_NUM05(1) := C_Party_Rec.employees_total; -- Number of Employees
1615 lp_gen_bulk_rec.SQUAL_NUM06(1) := null;
1616 lp_gen_bulk_rec.SQUAL_NUM07(1) := null;
1617 lp_gen_bulk_rec.SQUAL_NUM08(1) := null;
1618 lp_gen_bulk_rec.SQUAL_NUM09(1) := null;
1619 lp_gen_bulk_rec.SQUAL_NUM10(1) := null;
1620 lp_gen_bulk_rec.SQUAL_NUM50(1) := P_qte_header_rec.quote_header_id;
1621 -- End: Instantiate input rec
1622
1623 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1624 aso_debug_pub.add('Assign_Sales_Team: P_qte_header_rec.quote_header_id: '||P_qte_header_rec.quote_header_id,1,'N');
1625 aso_debug_pub.add('Assign_Sales_Team: P_qte_header_rec.party_id: '||P_qte_header_rec.party_id,1,'N');
1626 aso_debug_pub.add('Assign_Sales_Team: P_qte_header_rec.cust_party_id: '||P_qte_header_rec.cust_party_id,1,'N');
1627 aso_debug_pub.add('Assign_Sales_Team: P_qte_header_rec.sold_to_party_site_id: '||P_qte_header_rec.sold_to_party_site_id,1,'N');
1628 aso_debug_pub.add('Assign_Sales_Team: C_Party_Rec.party_name: '||C_Party_Rec.party_name,1,'N');
1629 aso_debug_pub.add('Assign_Sales_Team: C_Party_Rec.category_code: '||C_Party_Rec.category_code,1,'N');
1630 aso_debug_pub.add('Assign_Sales_Team: C_Party_Rec.SIC: '||C_Party_Rec.SIC_Code_Type||': '||C_Party_Rec.SIC_Code,1,'N');
1631 aso_debug_pub.add('Assign_Sales_Team: C_Party_Rec.employees_total: '||C_Party_Rec.employees_total,1,'N');
1632 aso_debug_pub.add('Assign_Sales_Team: C_Party_Site_Rec.city: '||C_Party_Site_Rec.city,1,'N');
1633 aso_debug_pub.add('Assign_Sales_Team: C_Party_Site_Rec.county: '||C_Party_Site_Rec.county,1,'N');
1634 aso_debug_pub.add('Assign_Sales_Team: C_Party_Site_Rec.state: '||C_Party_Site_Rec.state,1,'N');
1635 aso_debug_pub.add('Assign_Sales_Team: C_Party_Site_Rec.country: '||C_Party_Site_Rec.country,1,'N');
1636 aso_debug_pub.add('Assign_Sales_Team: C_Party_Site_Rec.province: '||C_Party_Site_Rec.province,1,'N');
1637 aso_debug_pub.add('Assign_Sales_Team: C_Party_Site_Rec.postal_code: '||C_Party_Site_Rec.postal_code,1,'N');
1638 aso_debug_pub.add('Assign_Sales_Team: C_Cust_Cont_Rec.Phone_Area_Code: '||C_Cust_Cont_Rec.Phone_Area_Code,1,'N');
1639
1640 aso_debug_pub.add('Before Calling JTF_TERR_ASSIGN_PUB.get_winners',1,'Y');
1641 END IF;
1642
1643 -- Call the JTF Terr Assignment API
1644 JTF_TERR_ASSIGN_PUB.get_winners
1645 ( p_api_version_number => 1.0,
1646 p_init_msg_list => FND_API.G_FALSE,
1647
1648 p_use_type => 'RESOURCE',
1649 p_source_id => -1001, -- Oracle Sales and Telesales
1650 p_trans_id => -1105, -- Quoting
1651 p_trans_rec => lp_gen_bulk_rec,
1652
1653 p_resource_type => FND_API.G_MISS_CHAR,
1654 p_role => FND_API.G_MISS_CHAR,
1655 p_top_level_terr_id => FND_API.G_MISS_NUM,
1656 p_num_winners => FND_API.G_MISS_NUM,
1657
1658 x_return_status => x_return_status,
1659 x_msg_count => x_msg_count,
1660 x_msg_data => x_msg_data,
1661
1662 x_winners_rec => x_winners_rec
1663 );
1664
1665 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1666 aso_debug_pub.add('Assign_Sales_Team: X_Return_Status: '||X_Return_Status,1,'N');
1667 aso_debug_pub.add('Assign_Sales_Team: x_winners_rec.count: '||x_winners_rec.Resource_Id.count,1,'N');
1668 IF x_winners_rec.Resource_Id.count > 0 THEN
1669 FOR i IN x_winners_rec.Resource_Id.FIRST..x_winners_rec.Resource_Id.LAST LOOP
1670 aso_debug_pub.add('Assign_Sales_Team: Trans_Object_Id: '||x_winners_rec.Trans_Object_Id(i),1,'N');
1671 aso_debug_pub.add('Assign_Sales_Team: Terr_Id: '||x_winners_rec.Terr_Id(i),1,'N');
1672 aso_debug_pub.add('Assign_Sales_Team: Resource_Id: '||x_winners_rec.Resource_Id(i),1,'N');
1673 aso_debug_pub.add('Assign_Sales_Team: Full_Access_Flag: '||x_winners_rec.Full_Access_Flag(i),1,'N');
1674 aso_debug_pub.add('Assign_Sales_Team: Group_Id: '||x_winners_rec.Group_Id(i),1,'N');
1675 aso_debug_pub.add('Assign_Sales_Team: Role: '||x_winners_rec.Role(i),1,'N');
1676 END LOOP;
1677 END IF;
1678 END IF;
1679
1680 EXCEPTION
1681
1682 WHEN FND_API.G_EXC_ERROR THEN
1683 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1684 P_API_NAME => L_API_NAME,
1685 P_PKG_NAME => G_PKG_NAME,
1686 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
1687 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
1688 P_SQLCODE => SQLCODE,
1689 P_SQLERRM => SQLERRM,
1690 X_MSG_COUNT => X_MSG_COUNT,
1691 X_MSG_DATA => X_MSG_DATA,
1692 X_RETURN_STATUS => X_RETURN_STATUS
1693 );
1694
1695 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1696 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1697 P_API_NAME => L_API_NAME,
1698 P_PKG_NAME => G_PKG_NAME,
1699 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1700 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
1701 P_SQLCODE => SQLCODE,
1702 P_SQLERRM => SQLERRM,
1703 X_MSG_COUNT => X_MSG_COUNT,
1704 X_MSG_DATA => X_MSG_DATA,
1705 X_RETURN_STATUS => X_RETURN_STATUS
1706 );
1707
1708 WHEN OTHERS THEN
1709 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1710 P_API_NAME => L_API_NAME,
1711 P_PKG_NAME => G_PKG_NAME,
1712 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
1713 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
1714 P_SQLCODE => SQLCODE,
1715 P_SQLERRM => SQLERRM,
1716 X_MSG_COUNT => X_MSG_COUNT,
1717 X_MSG_DATA => X_MSG_DATA,
1718 X_RETURN_STATUS => X_RETURN_STATUS
1719 );
1720
1721 END Get_Sales_Team;
1722 */
1723
1724 -- Change Start
1725 -- Release 12 JTY / TAP Changes
1726 -- Girish Sachdeva
1727 -- This procedure is changed to call the new JTF Terr Assignment API.
1728 PROCEDURE EXPLODE_GROUPS_ACCOUNTS(
1729 x_errbuf OUT NOCOPY VARCHAR2,
1730 x_retcode OUT NOCOPY VARCHAR2,
1731 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
1732 x_return_status OUT NOCOPY VARCHAR2)
1733 IS
1734 -------------RS_GROUP---------
1735 /*-------------------------------------------------------------------------+
1736 | PROGRAM LOGIC
1737 |
1738 | FOR EACH GROUP listed AS a winner within winners, get THE members who play
1739 | a sales ROLE AND are either an employee OR partner AND INSERT back INTO
1740 | winners IF they are NOT already IN winners.
1741 +-------------------------------------------------------------------------*/
1742 l_errbuf VARCHAR2(4000);
1743 l_retcode VARCHAR2(255);
1744
1745 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
1746
1747 l_resource_id num_list;
1748 l_group_id num_list;
1749 l_person_id num_list;
1750
1751 BEGIN
1752 aso_debug_pub.add('Proc EXPLODE_GROUPS_ACCOUNTS',1,'Y');
1753
1754 x_return_status := FND_API.G_RET_STS_SUCCESS;
1755 -- l_resource_type := 'RS_TEAM';
1756 /* Get resources within a resource team */
1757 /** Note
1758 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
1759 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
1760 **/
1761 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
1762 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
1763 aso_debug_pub.add('Proc EXPLODE_GROUPS_ACCOUNTS p_WinningTerrMember_tbl.resource_id(l_index) ' || p_WinningTerrMember_tbl.resource_id(l_index),1,'Y');
1764 aso_debug_pub.add('Proc EXPLODE_GROUPS_ACCOUNTS p_WinningTerrMember_tbl.resource_type(l_index) ' || p_WinningTerrMember_tbl.resource_type(l_index),1,'Y');
1765 aso_debug_pub.add('Proc EXPLODE_GROUPS_ACCOUNTS p_WinningTerrMember_tbl.resource_name(l_index) ' || p_WinningTerrMember_tbl.resource_name(l_index),1,'Y');
1766 aso_debug_pub.add('Proc EXPLODE_GROUPS_ACCOUNTS p_WinningTerrMember_tbl.group_ide(l_index) ' || p_WinningTerrMember_tbl.group_id(l_index),1,'Y');
1767 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_GROUP' THEN
1768 SELECT resource_id, group_id,person_id
1769 BULK COLLECT INTO l_resource_id, l_group_id,l_person_id
1770 FROM
1771 (
1772 SELECT min(m.resource_id) resource_id,
1773 res.category resource_category,
1774 m.group_id group_id, min(res.source_id) person_id
1775 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
1776 jtf_rs_group_usages u, jtf_rs_role_relations rr,
1777 jtf_rs_roles_b r, jtf_rs_resource_extns res
1778 WHERE m.group_id = g.group_id
1779 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
1780 AND NVL(g.end_date_active,SYSDATE)
1781 AND u.group_id = g.group_id
1782 AND u.usage IN ('SALES','PRM')
1783 AND m.group_member_id = rr.role_resource_id
1784 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
1785 AND rr.role_id = r.role_id
1786 AND rr.delete_flag <> 'Y'
1787 AND SYSDATE BETWEEN rr.start_date_active
1788 AND NVL(rr.end_date_active,SYSDATE)
1789 AND r.role_type_code IN
1790 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
1791 AND r.active_flag = 'Y'
1792 AND res.resource_id = m.resource_id
1793 AND res.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
1794 GROUP BY m.group_member_id, m.resource_id, m.person_id,
1795 m.group_id, res.CATEGORY) j
1796 WHERE j.group_id = p_WinningTerrMember_tbl.resource_id(l_index);
1797
1798 IF l_resource_id.COUNT > 0 THEN
1799 FOR i IN l_resource_id.FIRST .. l_resource_id.LAST LOOP
1800 /* No need to Check to see if it is already part of
1801 p_WinningTerrMember_tbl because this will be slow,
1802 So we insert into p_WinningTerrMember_tbl directly*/
1803 IF l_group_id(i) IS NOT NULL THEN --- Resources without groups should NOT be added to the sales team
1804 p_WinningTerrMember_tbl.resource_id.EXTEND;
1805 --Start for bug fix 12864534
1806 p_WinningTerrMember_tbl.resource_name.EXTEND;
1807 --End for bug fix 12864534
1808 p_WinningTerrMember_tbl.group_id.EXTEND;
1809 p_WinningTerrMember_tbl.person_id.EXTEND;
1810 p_WinningTerrMember_tbl.resource_type.EXTEND;
1811 p_WinningTerrMember_tbl.full_access_flag.EXTEND;
1812 p_WinningTerrMember_tbl.trans_object_id.EXTEND;
1813 p_WinningTerrMember_tbl.terr_id.EXTEND;
1814 p_WinningTerrMember_tbl.org_id.EXTEND;
1815 p_WinningTerrMember_tbl.role.EXTEND;
1816 p_WinningTerrMember_tbl.resource_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := l_resource_id(i);
1817 p_WinningTerrMember_tbl.resource_name(p_WinningTerrMember_tbl.resource_name.COUNT ) := p_WinningTerrMember_tbl.resource_name(l_index);
1818 p_WinningTerrMember_tbl.group_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := l_group_id(i);
1819 p_WinningTerrMember_tbl.person_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := l_person_id(i);
1820 p_WinningTerrMember_tbl.resource_type(p_WinningTerrMember_tbl.resource_id.COUNT ) := 'RS_EMPLOYEE';
1821 p_WinningTerrMember_tbl.full_access_flag(p_WinningTerrMember_tbl.resource_id.COUNT ) := p_WinningTerrMember_tbl.full_access_flag(l_index);
1822 --p_WinningTerrMember_tbl.trans_object_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := G_LEAD_ID;
1823 p_WinningTerrMember_tbl.terr_id(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.terr_id(l_index);
1824 p_WinningTerrMember_tbl.org_id(p_WinningTerrMember_tbl.resource_id.COUNT ) :=p_WinningTerrMember_tbl.org_id(l_index);
1825 END IF;
1826 END LOOP;
1827 END IF;
1828 END IF;
1829 END LOOP;
1830
1831
1832 END IF; /* if p_WinningTerrMember_tbl.resource_id.COUNT > 0 */
1833 EXCEPTION
1834 WHEN OTHERS THEN
1835 aso_debug_pub.add('exception in EXPLODE_GROUPS_ACCOUNTS ',1,'Y');
1836
1837 x_errbuf := SQLERRM;
1838 x_retcode := SQLCODE;
1839 x_return_status := FND_API.G_RET_STS_ERROR;
1840 aso_debug_pub.add('exception in EXPLODE_GROUPS_ACCOUNTS '||x_errbuf,1,'Y');
1841 aso_debug_pub.add('exception in EXPLODE_GROUPS_ACCOUNTS '||x_retcode,1,'Y');
1842 aso_debug_pub.add('exception in EXPLODE_GROUPS_ACCOUNTS '||x_return_status,1,'Y');
1843 END EXPLODE_GROUPS_ACCOUNTS;
1844
1845 PROCEDURE Get_Sales_Team(
1846 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1847 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
1848 P_Qte_Line_Tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type := ASO_QUOTE_PUB.G_MISS_qte_line_TBL,
1849 X_Winners_Rec OUT NOCOPY /* file.sql.39 change */ JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
1850 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1851 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1852 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1853 )
1854 IS
1855 l_errbuf VARCHAR2(4000);
1856 l_retcode VARCHAR2(255);
1857 l_return_status VARCHAR2(30);
1858 lp_bulk_trans_id JTY_ASSIGN_REALTIME_PUB.bulk_trans_id_type;
1859 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Sales_Team';
1860
1861 BEGIN
1862
1863 -- Standard Start of API savepoint
1864 SAVEPOINT GET_SALES_TEAM_PVT;
1865
1866 x_return_status := FND_API.G_RET_STS_SUCCESS;
1867
1868 lp_bulk_trans_id.trans_object_id1 := jtf_terr_number_list(P_Qte_Header_Rec.quote_header_id);
1869 lp_bulk_trans_id.trans_object_id2 := jtf_terr_number_list(null);
1870 lp_bulk_trans_id.trans_object_id3 := jtf_terr_number_list(null);
1871 lp_bulk_trans_id.trans_object_id4 := jtf_terr_number_list(null);
1872 lp_bulk_trans_id.trans_object_id5 := jtf_terr_number_list(null);
1873 lp_bulk_trans_id.txn_date := jtf_terr_date_list(null);
1874
1875 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1876 aso_debug_pub.add('Get_Sales_Team: P_Qte_Header_Rec.quote_header_id: '||P_qte_header_rec.quote_header_id,1,'N');
1877 aso_debug_pub.add('Get_Sales_Team: P_qte_header_rec.party_id: '||P_qte_header_rec.party_id,1,'N');
1878 aso_debug_pub.add('Get_Sales_Team: P_qte_header_rec.cust_party_id: '||P_qte_header_rec.cust_party_id,1,'N');
1879 aso_debug_pub.add('Get_Sales_Team: P_qte_header_rec.sold_to_party_site_id: '||P_qte_header_rec.sold_to_party_site_id,1,'N');
1880 aso_debug_pub.add('Before Calling JTY_ASSIGN_REALTIME_PUB.get_winners',1,'Y');
1881 aso_utility_pvt.print_login_info();
1882 END IF;
1883
1884 -- Call the new JTF Terr Assignment API
1885 JTY_ASSIGN_REALTIME_PUB.get_winners (
1886 P_api_version_number => 1.0,
1887 P_init_msg_list => FND_API.G_FALSE,
1888 P_source_id => -1001 , /* Oracle Sales and Telesales */
1889 P_trans_id => -1105 , /* Quoting */
1890 P_mode => 'REAL TIME:RESOURCE', /* It will return winning territories and resources in real time */
1891 P_param_passing_mechanism => 'PBR',
1892 P_program_name => 'SALES/QUOTE PROGRAM', /* Taken from JTY TDD*/
1893 P_trans_rec => lp_bulk_trans_id,
1894 P_name_value_pair => NULL,
1895 P_resource_type => null, --changed to null from 'RS_EMPLOYEE',vidya
1896 P_role => NULL,
1897 X_return_status => x_return_status,
1898 X_msg_count => x_msg_count,
1899 X_msg_data => x_msg_data,
1900 X_winners_rec => X_Winners_Rec
1901 );
1902 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1903 aso_debug_pub.add('Get_Sales_Team: x_return_status '||x_return_status,1,'N');
1904 aso_utility_pvt.print_login_info();
1905 aso_debug_pub.add('Get_Sales_Team: lx_bulk_winners_rec.count: '||X_Winners_Rec.Resource_Id.count,1,'N');
1906 IF X_Winners_Rec.Resource_Id.count > 0 THEN
1907 FOR i IN X_Winners_Rec.Resource_Id.FIRST..X_Winners_Rec.Resource_Id.LAST LOOP
1908 aso_debug_pub.add('Get_Sales_Team: Trans_Object_Id: '||X_Winners_Rec.Trans_Object_Id(i),1,'N');
1909 aso_debug_pub.add('Get_Sales_Team: Terr_Id: '||X_Winners_Rec.Terr_Id(i),1,'N');
1910 aso_debug_pub.add('Get_Sales_Team: Resource_Id: '||X_Winners_Rec.Resource_Id(i),1,'N');
1911 aso_debug_pub.add('Get_Sales_Team: Full_Access_Flag: '||X_Winners_Rec.Full_Access_Flag(i),1,'N');
1912 aso_debug_pub.add('Get_Sales_Team: Group_Id: '||X_Winners_Rec.Group_Id(i),1,'N');
1913 aso_debug_pub.add('Get_Sales_Team: Role: '||X_Winners_Rec.Role(i),1,'N');
1914 END LOOP;
1915 EXPLODE_GROUPS_ACCOUNTS(
1916 x_errbuf => l_errbuf,
1917 x_retcode => l_retcode,
1918 p_WinningTerrMember_tbl => X_Winners_Rec,
1919 x_return_status => l_return_status);
1920
1921 EXPLODE_TEAMS(
1922 x_errbuf => l_errbuf,
1923 x_retcode => l_retcode,
1924 p_WinningTerrMember_tbl => X_Winners_Rec,
1925 x_return_status => l_return_status);
1926
1927
1928 INSERT_ACCESSES_ACCOUNTS(
1929 x_errbuf => l_errbuf,
1930 x_retcode => l_retcode,
1931 P_Qte_Header_Rec=>P_qte_header_rec ,
1932 p_WinningTerrMember_tbl => X_Winners_Rec,
1933 x_return_status => l_return_status);
1934
1935
1936
1937 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
1938
1939 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1940 End If;
1941
1942
1943 END IF;
1944 END IF;
1945
1946 EXCEPTION
1947
1948 WHEN FND_API.G_EXC_ERROR THEN
1949 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1950 P_API_NAME => L_API_NAME,
1951 P_PKG_NAME => G_PKG_NAME,
1952 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
1953 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
1954 P_SQLCODE => SQLCODE,
1955 P_SQLERRM => SQLERRM,
1956 X_MSG_COUNT => X_MSG_COUNT,
1957 X_MSG_DATA => X_MSG_DATA,
1958 X_RETURN_STATUS => X_RETURN_STATUS
1959 );
1960
1961 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1962 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1963 P_API_NAME => L_API_NAME,
1964 P_PKG_NAME => G_PKG_NAME,
1965 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1966 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
1967 P_SQLCODE => SQLCODE,
1968 P_SQLERRM => SQLERRM,
1969 X_MSG_COUNT => X_MSG_COUNT,
1970 X_MSG_DATA => X_MSG_DATA,
1971 X_RETURN_STATUS => X_RETURN_STATUS
1972 );
1973
1974 WHEN OTHERS THEN
1975 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1976 P_API_NAME => L_API_NAME,
1977 P_PKG_NAME => G_PKG_NAME,
1978 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
1979 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
1980 P_SQLCODE => SQLCODE,
1981 P_SQLERRM => SQLERRM,
1982 X_MSG_COUNT => X_MSG_COUNT,
1983 X_MSG_DATA => X_MSG_DATA,
1984 X_RETURN_STATUS => X_RETURN_STATUS
1985 );
1986
1987 END Get_Sales_Team;
1988
1989 -- Change END
1990
1991
1992 PROCEDURE Update_Primary_SalesInfo(
1993 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1994 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
1995 P_Primary_SalesAgent IN NUMBER,
1996 P_Primary_SalesGrp IN NUMBER,
1997 P_Reassign_Flag IN VARCHAR2,
1998 X_Qte_Header_Rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
1999 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2000 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2001 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2002 )
2003 IS
2004
2005 CURSOR C_Get_Primary_Resource (l_qte_number NUMBER) IS
2006 SELECT Resource_Id, Resource_Grp_Id
2007 FROM ASO_QUOTE_HEADERS_ALL
2008 WHERE Quote_Number = l_qte_number
2009 AND Max_Version_Flag = 'Y';
2010
2011 CURSOR C_Primary_Res_Kept (l_res NUMBER, l_qte_num NUMBER) IS
2012 SELECT 'Y'
2013 FROM ASO_QUOTE_ACCESSES
2014 WHERE Resource_Id = l_res
2015 AND Quote_Number = l_qte_num;
2016
2017 CURSOR C_Valid_Salesagent (l_qte_num NUMBER) IS
2018 SELECT A.Resource_Id, A.Resource_Grp_Id, A.Role_Id
2019 /* FROM ASO_QUOTE_ACCESSES A, JTF_RS_SALESREPS B, */ --Commented Code Yogeshwar (MOAC)
2020 FROM ASO_QUOTE_ACCESSES A, JTF_RS_SALESREPS_MO_V B, --New Code Yogeshwar (MOAC)
2021 OE_SALES_CREDIT_TYPES ST
2022 WHERE B.sales_credit_type_id = ST.sales_credit_type_id
2023 AND A.Resource_Id = B.Resource_Id
2024 AND A.Update_Access_Flag = 'Y'
2025 AND A.Quote_Number = l_qte_num
2026 AND NVL(B.status,'A') = 'A'
2027 AND SYSDATE BETWEEN B.start_date_active AND NVL(B.end_date_active, SYSDATE) ;
2028 --Commented code start yogeshwar (MOAC)
2029 /*
2030 AND NVL(B.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), '',
2031 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(
2032 USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
2033 */
2034 --Commented Code End Yogeshwar (MOAC)
2035
2036 CURSOR C_Get_Resource_Role (l_res NUMBER) IS
2037 SELECT Role_Id
2038 FROM JTF_RS_ROLE_RELATIONS
2039 WHERE Role_Resource_Id = l_res
2040 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
2041
2042 CURSOR C_Get_Res_From_Srep (l_Srep VARCHAR2) IS
2043 SELECT Resource_Id
2044 /* FROM JTF_RS_SRP_VL */ --Commented Code Yogeshwar (MOAC)
2045 FROM JTF_RS_SALESREPS_MO_V --New Code Yogeshwar (MOAC)
2046 WHERE Salesrep_Number = l_Srep
2047 AND NVL(status,'A') = 'A'
2048 AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
2049 AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate) ;
2050 --Commented Code Start Yogeshwar (MOAC)
2051 /*
2052 AND NVL(org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
2053 */
2054 --Commented Code End Yogeshwar (MOAC)
2055 l_primary_resource NUMBER;
2056 l_primary_resource_grp NUMBER;
2057 l_primary_salesagent NUMBER;
2058 l_primary_res_kept VARCHAR2(1) := 'N';
2059 l_primary_role NUMBER;
2060 l_primary_res_grp NUMBER;
2061 l_primary_res NUMBER;
2062 l_sequence NUMBER := null;
2063 l_api_name CONSTANT VARCHAR2 ( 50 ) := 'Update_Primary_SalesInfo';
2064
2065 -- Change START
2066 -- Release 12 MOAC Changes : Bug 4500739
2067 -- Changes Done by : Girish
2068 -- Comments : Using HR EIT in place of org striped profile
2069
2070 --l_default_salesrep_prof VARCHAR2(50) := FND_PROFILE.Value('ASO_DEFAULT_PERSON_ID');
2071 l_default_salesrep_prof VARCHAR2(50) := ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(ASO_UTILITY_PVT.G_DEFAULT_SALESREP);
2072
2073 --l_default_role_prof VARCHAR2(50) := FND_PROFILE.Value('ASO_DEFAULT_SALES_ROLE');
2074 l_default_role_prof VARCHAR2(50) := ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(ASO_UTILITY_PVT.G_DEFAULT_SALES_ROLE);
2075
2076 -- Change End
2077
2078 l_ots_grp_prof VARCHAR2(50);
2079
2080 /*
2081 := NVL(FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE',
2082 G_USER_ID, NULL, 522),
2083 FND_PROFILE.Value_Specific(
2084 'AST_DEFAULT_ROLE_AND_GROUP',
2085 G_USER_ID, NULL, 521));
2086 */
2087
2088 l_obsolete_status varchar2(1);
2089
2090 BEGIN
2091
2092 -- Standard Start of API savepoint
2093 SAVEPOINT UPDATE_PRIMARY_SALESINFO_PVT;
2094
2095 x_return_status := FND_API.G_RET_STS_SUCCESS;
2096 l_primary_salesagent := p_primary_salesagent;
2097 l_primary_res_grp := p_primary_salesgrp;
2098 x_qte_header_rec := p_qte_header_rec;
2099
2100 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2101 aso_debug_pub.add('Assign_Sales_Team: l_primary_salesagent: '||l_primary_salesagent,1,'N');
2102 END IF;
2103
2104 IF l_primary_salesagent IS NULL THEN
2105 IF p_reassign_flag = 'Y' THEN
2106
2107 OPEN C_Get_Primary_Resource (P_Qte_Header_Rec.Quote_Number);
2108 FETCH C_Get_Primary_Resource INTO l_primary_res, l_primary_res_grp;
2109 CLOSE C_Get_Primary_Resource;
2110
2111 OPEN C_Primary_Res_Kept(l_Primary_res, P_qte_header_rec.Quote_Number);
2112 FETCH C_Primary_Res_Kept INTO l_primary_res_kept;
2113 CLOSE C_Primary_Res_Kept;
2114
2115 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2116 aso_debug_pub.add('Assign_Sales_Team: l_primary_salesagent: '||l_primary_res,1,'N');
2117 aso_debug_pub.add('Assign_Sales_Team: Primary Res kept: '||l_primary_res_kept,1,'N');
2118 END IF;
2119
2120 IF l_primary_res_kept = 'Y' THEN
2121 l_primary_salesagent := l_primary_res;
2122 END IF;
2123
2124 END IF; -- Reassign_Flag
2125
2126 IF (l_primary_res_kept IS NULL OR l_primary_res_kept <> 'Y') THEN
2127 IF l_primary_salesagent IS NULL THEN
2128
2129 OPEN C_Valid_Salesagent(P_qte_header_rec.Quote_Number);
2130 FETCH C_Valid_Salesagent INTO l_primary_salesagent, l_primary_res_grp, l_primary_role;
2131 CLOSE C_Valid_Salesagent;
2132 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2133 aso_debug_pub.add('Assign_Sales_Team: Valid Salesagent: '||l_primary_salesagent,1,'N');
2134 END IF;
2135
2136 IF l_primary_salesagent IS NOT NULL THEN
2137 l_primary_res_kept := 'Y';
2138 END IF;
2139
2140 END IF; -- salesagent is NULL
2141
2142 IF l_primary_salesagent IS NULL THEN
2143 IF l_default_salesrep_prof IS NOT NULL THEN
2144
2145 OPEN C_Get_Res_From_Srep (l_default_salesrep_prof);
2146 FETCH C_Get_Res_From_Srep INTO l_primary_salesagent;
2147 CLOSE C_Get_Res_From_Srep;
2148 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2149 aso_debug_pub.add('Assign_Sales_Team: Default SalesRep: '||l_primary_salesagent,1,'N');
2150 END IF;
2151 ELSE
2152 x_return_status := FND_API.G_RET_STS_ERROR;
2153 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2154 -- Created new message to display the error message more appropriately - Girish Bug 4654938
2155 -- FND_MESSAGE.Set_Name('ASO', 'ASO_API_NO_PROFILE_VALUE');
2156 -- FND_MESSAGE.Set_Token('PROFILE', 'ASO_DEFAULT_PERSON_ID', FALSE);
2157 FND_MESSAGE.Set_Name('ASO', 'ASO_NO_DEFAULT_VALUE');
2158 FND_MESSAGE.Set_Token('PROFILE', 'ASO_DEFAULT_SALESREP', TRUE);
2159
2160 FND_MSG_PUB.Add;
2161 END IF;
2162 RAISE FND_API.G_EXC_ERROR;
2163 END IF; -- salesrep_prof
2164
2165 l_primary_role := l_default_role_prof;
2166
2167 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2168 aso_debug_pub.add('Before calling Get_Profile_Obsolete_Status', 1, 'N');
2169 END IF;
2170
2171 l_obsolete_status := aso_utility_pvt.Get_Profile_Obsolete_Status(p_profile_name => 'AST_DEFAULT_ROLE_AND_GROUP',
2172 p_application_id => 521);
2173
2174 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2175 aso_debug_pub.add('After calling Get_Profile_Obsolete_Status: l_obsolete_status: ' || l_obsolete_status, 1, 'N');
2176 END IF;
2177
2178 if l_obsolete_status = 'T' then
2179
2180 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
2181
2182 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2183 aso_debug_pub.add('l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
2184 END IF;
2185
2186 l_primary_res_grp := SUBSTR(l_ots_grp_prof, 1, INSTR(l_ots_grp_prof,'(')-1);
2187
2188 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2189 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
2190 END IF;
2191
2192 if l_primary_res_grp is null then
2193
2194 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'AST_DEFAULT_GROUP', G_USER_ID, NULL, 521);
2195
2196 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2197 aso_debug_pub.add('l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
2198 END IF;
2199
2200 l_primary_res_grp := to_number(l_ots_grp_prof);
2201
2202 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2203 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
2204 END IF;
2205
2206 end if;
2207
2208 else
2209
2210 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
2211
2212 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2213 aso_debug_pub.add('ASF_DEFAULT_GROUP_ROLE value: l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
2214 END IF;
2215
2216 l_primary_res_grp := SUBSTR(l_ots_grp_prof, 1, INSTR(l_ots_grp_prof,'(')-1);
2217
2218 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2219 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
2220 END IF;
2221
2222 if l_primary_res_grp is null then
2223
2224 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'AST_DEFAULT_ROLE_AND_GROUP', G_USER_ID, NULL, 521);
2225
2226 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2227 aso_debug_pub.add('AST_DEFAULT_ROLE_AND_GROUP value :l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
2228 END IF;
2229
2230 l_primary_res_grp := substr(l_ots_grp_prof, instr(l_ots_grp_prof,':', -1) + 1, length(l_ots_grp_prof));
2231
2232 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2233 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
2234 END IF;
2235
2236 end if;
2237
2238 end if;
2239
2240 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2241 aso_debug_pub.add('Assign_Sales_Team: Default Role: '||l_primary_role,1,'N');
2242 aso_debug_pub.add('Assign_Sales_Team: Default Grp: '||l_primary_res_grp,1,'N');
2243 END IF;
2244
2245 END IF; -- salesagent is NULL
2246
2247 IF l_primary_role IS NULL THEN
2248 OPEN C_Get_Resource_Role (l_primary_salesagent);
2249 FETCH C_Get_Resource_Role INTO l_primary_role;
2250 CLOSE C_Get_Resource_Role;
2251
2252 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2253 aso_debug_pub.add('Assign_Sales_Team: Role From Res: '||l_primary_role,1,'N');
2254 END IF;
2255 END IF;
2256
2257
2258 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2259 aso_debug_pub.add('Assign_Sales_Team: l_primary_res_kept: '||l_primary_res_kept,1,'N');
2260 END IF;
2261
2262 IF (l_primary_res_kept IS NULL OR l_primary_res_kept <> 'Y') THEN
2263
2264 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2265 aso_debug_pub.add('Assign_Sales_Team: Before primary salesagent insert: ',1,'N');
2266 END IF;
2267 l_sequence := NULL;
2268
2269 ASO_QUOTE_ACCESSES_PKG.Insert_Row(
2270 px_ACCESS_ID => l_sequence,
2271 p_QUOTE_NUMBER => P_Qte_Header_Rec.Quote_Number,
2272 p_RESOURCE_ID => l_primary_salesagent,
2273 p_RESOURCE_GRP_ID => l_primary_res_grp,
2274 p_CREATED_BY => G_USER_ID,
2275 p_CREATION_DATE => SYSDATE,
2276 p_LAST_UPDATED_BY => G_USER_ID,
2277 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
2278 p_LAST_UPDATE_DATE => SYSDATE,
2279 p_REQUEST_ID => FND_API.G_MISS_NUM,
2280 p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
2281 p_PROGRAM_ID => FND_API.G_MISS_NUM,
2282 p_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
2283 p_KEEP_FLAG => FND_API.G_MISS_CHAR,
2284 p_UPDATE_ACCESS_FLAG => 'Y',
2285 p_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
2286 p_TERRITORY_ID => FND_API.G_MISS_NUM,
2287 p_TERRITORY_SOURCE_FLAG => FND_API.G_MISS_CHAR,
2288 p_ROLE_ID => l_primary_role,
2289 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
2290 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
2291 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
2292 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
2293 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
2294 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
2295 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
2296 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
2297 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
2298 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
2299 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
2300 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
2301 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
2302 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
2303 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
2304 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
2305 p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
2306 p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
2307 p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
2308 p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
2309 p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
2310 p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
2311 );
2312 END IF; -- primary_res_kept <> Y
2313 END IF; -- primary_res_kept <> Y
2314 END IF;
2315 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2316 aso_debug_pub.add('Assign_Sales_Team: Update primary salesagent in Hdr ',1,'N');
2317 END IF;
2318 UPDATE ASO_QUOTE_HEADERS_ALL
2319 SET Resource_Id = l_primary_salesagent,
2320 Resource_Grp_Id = l_primary_res_grp,
2321 last_update_date = sysdate,
2322 last_updated_by = fnd_global.user_id,
2323 last_update_login = fnd_global.conc_login_id,
2324 object_version_number = object_version_number+1
2325 WHERE quote_number = P_Qte_Header_Rec.quote_number
2326 AND max_version_flag = 'Y'
2327 RETURNING quote_header_id, last_update_date, resource_id, resource_grp_id, object_version_number
2328 INTO x_qte_header_rec.Quote_Header_Id, x_qte_header_rec.Last_Update_Date,
2329 x_qte_header_rec.resource_id, x_qte_header_rec.resource_grp_id, x_qte_header_rec.object_version_number;
2330
2331 -- Standard call to get message count and if count is 1, get message info.
2332 FND_MSG_PUB.Count_And_Get
2333 ( p_count => x_msg_count,
2334 p_data => x_msg_data
2335 );
2336
2337 EXCEPTION
2338
2339 WHEN FND_API.G_EXC_ERROR THEN
2340 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2341 P_API_NAME => L_API_NAME,
2342 P_PKG_NAME => G_PKG_NAME,
2343 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
2344 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
2345 P_SQLCODE => SQLCODE,
2346 P_SQLERRM => SQLERRM,
2347 X_MSG_COUNT => X_MSG_COUNT,
2348 X_MSG_DATA => X_MSG_DATA,
2349 X_RETURN_STATUS => X_RETURN_STATUS
2350 );
2351
2352 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2353 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2354 P_API_NAME => L_API_NAME,
2355 P_PKG_NAME => G_PKG_NAME,
2356 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
2357 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
2358 P_SQLCODE => SQLCODE,
2359 P_SQLERRM => SQLERRM,
2360 X_MSG_COUNT => X_MSG_COUNT,
2361 X_MSG_DATA => X_MSG_DATA,
2362 X_RETURN_STATUS => X_RETURN_STATUS
2363 );
2364
2365 WHEN OTHERS THEN
2366 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2367 P_API_NAME => L_API_NAME,
2368 P_PKG_NAME => G_PKG_NAME,
2369 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
2370 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
2371 P_SQLCODE => SQLCODE,
2372 P_SQLERRM => SQLERRM,
2373 X_MSG_COUNT => X_MSG_COUNT,
2374 X_MSG_DATA => X_MSG_DATA,
2375 X_RETURN_STATUS => X_RETURN_STATUS
2376 );
2377
2378 END Update_Primary_SalesInfo;
2379
2380
2381 PROCEDURE Opp_Quote_Primary_SalesRep(
2382 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2383 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
2384 X_Qte_Header_Rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
2385 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2386 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2387 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2388 )
2389 IS
2390
2391 l_api_name CONSTANT VARCHAR2 ( 30 ) := 'Assign_Sales_Team';
2392 l_api_version_number CONSTANT NUMBER := 1.0;
2393 l_creator_res NUMBER;
2394 l_creator_found VARCHAR2(1);
2395 l_valid VARCHAR2(1);
2396 l_primary_salesagent NUMBER;
2397 l_primary_res_grp NUMBER;
2398 l_primary_role NUMBER;
2399 l_sequence NUMBER := null;
2400 l_role_prof VARCHAR2(50);
2401
2402 -- Change START
2403 -- Release 12 MOAC Changes : Bug 4500739
2404 -- Changes Done by : Girish
2405 -- Comments : Using HR EIT in place of org striped profile
2406
2407 --l_default_salesrep_prof VARCHAR2(50) := FND_PROFILE.Value('ASO_DEFAULT_PERSON_ID');
2408 l_default_salesrep_prof VARCHAR2(50) := ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(ASO_UTILITY_PVT.G_DEFAULT_SALESREP);
2409
2410 --l_default_role_prof VARCHAR2(50) := FND_PROFILE.Value('ASO_DEFAULT_SALES_ROLE');
2411 l_default_role_prof VARCHAR2(50) := ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(ASO_UTILITY_PVT.G_DEFAULT_SALES_ROLE);
2412
2413 -- Change End
2414
2415 l_ots_role_prof VARCHAR2(50) := FND_PROFILE.Value('AST_DEFAULT_ROLE_AND_GROUP');
2416 l_ots_grp_prof VARCHAR2(50);
2417
2418 /*
2419 := NVL(FND_PROFILE.Value_Specific(
2420 'ASF_DEFAULT_GROUP_ROLE',
2421 G_USER_ID, NULL, 522),
2422 FND_PROFILE.Value_Specific(
2423 'AST_DEFAULT_ROLE_AND_GROUP',
2424 G_USER_ID, NULL, 521));
2425 */
2426
2427 l_obsolete_status varchar2(1);
2428
2429 CURSOR C_Get_Creator_Res (l_user_id NUMBER) IS
2430 SELECT resource_id
2431 FROM JTF_RS_RESOURCE_EXTNS
2432 WHERE user_id = l_user_id
2433 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
2434
2435 CURSOR C_Valid_SalesRep (l_res_id NUMBER) IS
2436 SELECT 'Y'
2437 /* FROM JTF_RS_SRP_VL */ --Commented Code Yogeshwar (MOAC)
2438 FROM JTF_RS_SALESREPS_MO_V --New Code Yogeshwar (MOAC)
2439 WHERE resource_id = l_res_id
2440 AND NVL(status,'A') = 'A'
2441 AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
2442 AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate) ;
2443 --Commented Code Start Yogeshwar (MOAC)
2444 /*
2445 AND NVL(org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
2446 */
2447 --Commented Code End Yogeshwar (MOAC)
2448
2449 CURSOR C_Valid_Salesagent (l_qte_num NUMBER) IS
2450 SELECT A.Resource_Id, A.Resource_Grp_Id
2451 /* FROM ASO_QUOTE_ACCESSES A, JTF_RS_SALESREPS B, */ --Commented Code Yogeshwar ( MOAC)
2452 FROM ASO_QUOTE_ACCESSES A,JTF_RS_SALESREPS_MO_V B, --New Code Yogeshwar (MOAC)
2453 OE_SALES_CREDIT_TYPES ST
2454 WHERE B.sales_credit_type_id = ST.sales_credit_type_id
2455 AND A.Resource_Id = B.Resource_Id
2456 AND A.Update_Access_Flag = 'Y'
2457 AND A.Quote_Number = l_qte_num
2458 AND NVL(B.status,'A') = 'A'
2459 AND SYSDATE BETWEEN B.start_date_active AND NVL(B.end_date_active, SYSDATE) ;
2460 --Commented Code Start Yogeshwar (MOAC)
2461 /*
2462 AND NVL(B.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), '',
2463 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(
2464 USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
2465 */
2466 --Commented Code End Yogeshwar (MOAC)
2467
2468 CURSOR C_Check_Creator_Res (l_qte_num NUMBER, l_res NUMBER) IS
2469 SELECT 'Y', Resource_Grp_Id
2470 FROM ASO_QUOTE_ACCESSES
2471 WHERE Quote_Number = l_qte_num
2472 AND Resource_Id = l_res;
2473
2474 CURSOR C_Get_Role_From_Code (l_code VARCHAR2) IS
2475 SELECT Role_Id
2476 FROM JTF_RS_ROLES_B
2477 WHERE Role_Code = l_code
2478 AND Role_Type_Code IN ('TELESALES', 'SALES', 'FIELDSALES', 'PRM');
2479
2480 CURSOR C_Get_Resource_Role (l_res NUMBER) IS
2481 SELECT Role_Id
2482 FROM JTF_RS_ROLE_RELATIONS
2483 WHERE Role_Resource_Id = l_res
2484 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
2485
2486 CURSOR C_Get_Res_From_Srep (l_Srep VARCHAR2) IS
2487 SELECT Resource_Id
2488 /* FROM JTF_RS_SRP_VL */ --Commented Code Yogeshwar (MOAC)
2489 FROM JTF_RS_SALESREPS_MO_V --New Code Yogeshwar (MOAC)
2490 WHERE Salesrep_Number = l_Srep
2491 AND NVL(status,'A') = 'A'
2492 AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
2493 AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate) ;
2494 --Commented Code Start Yogeshwar (MOAC)
2495 /*
2496 AND NVL(org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
2497 */
2498 --Commented Code End Yogeshwar (MOAC)
2499 BEGIN
2500
2501 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
2502
2503 -- Standard Start of API savepoint
2504 SAVEPOINT OPP_QUOTE_PRIMARY_SALESREP_PVT;
2505
2506 -- Standard call to check for call compatibility.
2507 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2508 1.0,
2509 l_api_name,
2510 G_PKG_NAME)
2511 THEN
2512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2514 END IF;
2515
2516 -- Initialize message list if p_init_msg_list is set to TRUE.
2517 IF FND_API.to_Boolean( p_init_msg_list )
2518 THEN
2519 FND_MSG_PUB.initialize;
2520 END IF;
2521
2522 -- Initialize API return status to SUCCESS
2523 x_return_status := FND_API.G_RET_STS_SUCCESS;
2524
2525 --
2526 -- API body
2527 --
2528 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2529 aso_debug_pub.add('Begin Opp_Quote_Primary_SalesRep',1,'Y');
2530 END IF;
2531
2532 x_qte_header_rec := p_qte_header_rec;
2533
2534 OPEN C_Get_Creator_Res(G_USER_ID);
2535 FETCH C_Get_Creator_Res INTO l_creator_res;
2536 CLOSE C_Get_Creator_Res;
2537
2538 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2539 aso_debug_pub.add('Opp_Qte_PS: Before Creator Res:l_creator_res: '||l_creator_res,1,'Y');
2540 END IF;
2541
2542 IF l_creator_res IS NOT NULL THEN
2543
2544 OPEN C_Valid_SalesRep (l_creator_res);
2545 FETCH C_Valid_SalesRep INTO l_valid;
2546 CLOSE C_Valid_SalesRep;
2547
2548 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2549 aso_debug_pub.add('Opp_Qte_PS: Before Creator Res:G_USER_ID: '||G_USER_ID,1,'Y');
2550 aso_debug_pub.add('Opp_Qte_PS: Creator Resource : '||l_creator_res,1,'Y');
2551 aso_debug_pub.add('Opp_Qte_PS: Creator Valid SalesRep : '||l_valid,1,'Y');
2552 END IF;
2553
2554 IF l_valid = 'Y' THEN
2555
2556 l_primary_salesagent := l_creator_res;
2557
2558 OPEN C_Check_Creator_Res(P_Qte_Header_Rec.Quote_Number, l_creator_res);
2559 FETCH C_Check_Creator_Res INTO l_creator_found, l_primary_res_grp;
2560 CLOSE C_Check_Creator_Res;
2561 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2562 aso_debug_pub.add('Opp_Qte_PS: Fetch Creator Grp: '||l_primary_res_grp,1,'Y');
2563 aso_debug_pub.add('Opp_Qte_PS: Creator Found: '||l_creator_found,1,'Y');
2564 END IF;
2565
2566 IF l_creator_found IS NULL OR l_creator_found <> 'Y' THEN
2567 -- Role Defaulting Logic
2568 l_role_prof := SUBSTR(l_ots_role_prof, 1, INSTR(l_ots_role_prof, ':')-1);
2569
2570 OPEN C_Get_Role_From_Code (l_role_prof);
2571 FETCH C_Get_Role_From_Code INTO l_primary_role;
2572 CLOSE C_Get_Role_From_Code;
2573
2574 IF l_primary_role IS NULL THEN
2575 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2576 aso_debug_pub.add('nores:create:Creator Role From Res: '||l_primary_role,1,'N');
2577 END IF;
2578
2579 OPEN C_Get_Resource_Role (l_creator_res);
2580 FETCH C_Get_Resource_Role INTO l_primary_role;
2581 CLOSE C_Get_Resource_Role;
2582
2583 END IF;
2584
2585
2586 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2587 aso_debug_pub.add('Before calling Get_Profile_Obsolete_Status', 1, 'N');
2588 END IF;
2589
2590 l_obsolete_status := aso_utility_pvt.Get_Profile_Obsolete_Status(p_profile_name => 'AST_DEFAULT_ROLE_AND_GROUP',
2591 p_application_id => 521);
2592
2593 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2594 aso_debug_pub.add('After calling Get_Profile_Obsolete_Status: l_obsolete_status: ' || l_obsolete_status, 1, 'N');
2595 END IF;
2596
2597 if l_obsolete_status = 'T' then
2598
2599 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
2600
2601 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2602 aso_debug_pub.add('l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
2603 END IF;
2604
2605 l_primary_res_grp := SUBSTR(l_ots_grp_prof, 1, INSTR(l_ots_grp_prof,'(')-1);
2606
2607 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2608 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
2609 END IF;
2610
2611 if l_primary_res_grp is null then
2612
2613 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'AST_DEFAULT_GROUP', G_USER_ID, NULL, 521);
2614
2615 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2616 aso_debug_pub.add('l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
2617 END IF;
2618
2619 l_primary_res_grp := to_number(l_ots_grp_prof);
2620
2621 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2622 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
2623 END IF;
2624
2625 end if;
2626
2627 else
2628
2629 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
2630
2631 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2632 aso_debug_pub.add('ASF_DEFAULT_GROUP_ROLE value: l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
2633 END IF;
2634
2635 l_primary_res_grp := SUBSTR(l_ots_grp_prof, 1, INSTR(l_ots_grp_prof,'(')-1);
2636
2637 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2638 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
2639 END IF;
2640
2641 if l_primary_res_grp is null then
2642
2643 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'AST_DEFAULT_ROLE_AND_GROUP', G_USER_ID, NULL, 521);
2644
2645 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2646 aso_debug_pub.add('AST_DEFAULT_ROLE_AND_GROUP value :l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
2647 END IF;
2648
2649 l_primary_res_grp := substr(l_ots_grp_prof, instr(l_ots_grp_prof,':', -1) + 1, length(l_ots_grp_prof));
2650
2651 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2652 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
2653 END IF;
2654
2655 end if;
2656
2657 end if;
2658
2659 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2660 aso_debug_pub.add('Opp_Qte_PS: Role Profile: '||l_ots_role_prof,1,'Y');
2661 aso_debug_pub.add('Opp_Qte_PS: Role Profile: '||l_role_prof,1,'Y');
2662 aso_debug_pub.add('Opp_Qte_PS: Creator Role : '||l_primary_role,1,'Y');
2663 aso_debug_pub.add('Opp_Qte_PS: Creator Group : '||l_primary_res_grp,1,'Y');
2664 END IF;
2665
2666 l_sequence := NULL;
2667
2668 ASO_QUOTE_ACCESSES_PKG.Insert_Row(
2669 px_ACCESS_ID => l_sequence,
2670 p_QUOTE_NUMBER => P_Qte_Header_Rec.Quote_Number,
2671 p_RESOURCE_ID => l_creator_res,
2672 p_RESOURCE_GRP_ID => l_primary_res_grp,
2673 p_CREATED_BY => G_USER_ID,
2674 p_CREATION_DATE => SYSDATE,
2675 p_LAST_UPDATED_BY => G_USER_ID,
2676 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
2677 p_LAST_UPDATE_DATE => SYSDATE,
2678 p_REQUEST_ID => FND_API.G_MISS_NUM,
2679 p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
2680 p_PROGRAM_ID => FND_API.G_MISS_NUM,
2681 p_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
2682 p_KEEP_FLAG => 'N',
2683 p_UPDATE_ACCESS_FLAG => 'Y',
2684 p_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
2685 p_TERRITORY_ID => FND_API.G_MISS_NUM,
2686 p_TERRITORY_SOURCE_FLAG => 'N',
2687 p_ROLE_ID => l_primary_role,
2688 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
2689 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
2690 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
2691 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
2692 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
2693 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
2694 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
2695 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
2696 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
2697 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
2698 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
2699 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
2700 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
2701 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
2702 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
2703 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
2704 p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
2705 p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
2706 p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
2707 p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
2708 p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
2709 p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
2710 );
2711
2712 END IF; -- creator_found
2713
2714 END IF; -- l_valid = Y
2715
2716 END IF; -- creator_res not null
2717
2718 IF l_primary_salesagent IS NULL THEN
2719
2720 OPEN C_Valid_Salesagent(P_qte_header_rec.Quote_Number);
2721 FETCH C_Valid_Salesagent INTO l_primary_salesagent, l_primary_res_grp;
2722 CLOSE C_Valid_Salesagent;
2723 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2724 aso_debug_pub.add('Opp_Qte_PS: Valid Salesagent: '||l_primary_salesagent,1,'N');
2725 END IF;
2726 END IF;
2727
2728 IF l_primary_salesagent IS NULL THEN
2729 IF l_default_salesrep_prof IS NOT NULL THEN
2730
2731 OPEN C_Get_Res_From_Srep (l_default_salesrep_prof);
2732 FETCH C_Get_Res_From_Srep INTO l_primary_salesagent;
2733 CLOSE C_Get_Res_From_Srep;
2734
2735 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2736 aso_debug_pub.add('Opp_Qte_PS: Default SalesRep: '||l_primary_salesagent,1,'N');
2737 END IF;
2738 ELSE
2739 x_return_status := FND_API.G_RET_STS_ERROR;
2740 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2741
2742 -- Created new message to display the error message more appropriately - Girish Bug 4654938
2743 -- FND_MESSAGE.Set_Name('ASO', 'ASO_API_NO_PROFILE_VALUE');
2744 -- FND_MESSAGE.Set_Token('PROFILE', 'ASO_DEFAULT_PERSON_ID', FALSE);
2745 FND_MESSAGE.Set_Name('ASO', 'ASO_NO_DEFAULT_VALUE');
2746 FND_MESSAGE.Set_Token('PROFILE', 'ASO_DEFAULT_SALESREP', TRUE);
2747
2748 FND_MSG_PUB.Add;
2749 END IF;
2750 RAISE FND_API.G_EXC_ERROR;
2751 END IF; -- salesrep_prof
2752
2753 l_primary_role := l_default_role_prof;
2754
2755 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2756 aso_debug_pub.add('Before calling Get_Profile_Obsolete_Status', 1, 'N');
2757 END IF;
2758
2759 l_obsolete_status := aso_utility_pvt.Get_Profile_Obsolete_Status(p_profile_name => 'AST_DEFAULT_ROLE_AND_GROUP',
2760 p_application_id => 521);
2761
2762 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2763 aso_debug_pub.add('After calling Get_Profile_Obsolete_Status: l_obsolete_status: ' || l_obsolete_status, 1, 'N');
2764 END IF;
2765
2766 if l_obsolete_status = 'T' then
2767
2768 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
2769
2770 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2771 aso_debug_pub.add('l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
2772 END IF;
2773
2774 l_primary_res_grp := SUBSTR(l_ots_grp_prof, 1, INSTR(l_ots_grp_prof,'(')-1);
2775
2776 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2777 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
2778 END IF;
2779
2780 if l_primary_res_grp is null then
2781
2782 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'AST_DEFAULT_GROUP', G_USER_ID, NULL, 521);
2783
2784 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2785 aso_debug_pub.add('l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
2786 END IF;
2787
2788 l_primary_res_grp := to_number(l_ots_grp_prof);
2789
2790 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2791 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
2792 END IF;
2793
2794 end if;
2795
2796 else
2797
2798 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
2799
2800 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2801 aso_debug_pub.add('ASF_DEFAULT_GROUP_ROLE value: l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
2802 END IF;
2803
2804 l_primary_res_grp := SUBSTR(l_ots_grp_prof, 1, INSTR(l_ots_grp_prof,'(')-1);
2805
2806 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2807 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
2808 END IF;
2809
2810 if l_primary_res_grp is null then
2811
2812 l_ots_grp_prof := FND_PROFILE.Value_Specific( 'AST_DEFAULT_ROLE_AND_GROUP', G_USER_ID, NULL, 521);
2813
2814 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2815 aso_debug_pub.add('AST_DEFAULT_ROLE_AND_GROUP value :l_ots_grp_prof: ' || l_ots_grp_prof, 1, 'N');
2816 END IF;
2817
2818 l_primary_res_grp := substr(l_ots_grp_prof, instr(l_ots_grp_prof,':', -1) + 1, length(l_ots_grp_prof));
2819
2820 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2821 aso_debug_pub.add('l_primary_res_grp: ' || l_primary_res_grp, 1, 'N');
2822 END IF;
2823
2824 end if;
2825
2826 end if;
2827
2828 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2829 aso_debug_pub.add('Opp_Qte_PS: Default Role: '||l_primary_role,1,'N');
2830 aso_debug_pub.add('Opp_Qte_PS: Default Grp: '||l_primary_res_grp,1,'N');
2831 END IF;
2832
2833 IF l_primary_role IS NULL THEN
2834 OPEN C_Get_Resource_Role (l_primary_salesagent);
2835 FETCH C_Get_Resource_Role INTO l_primary_role;
2836 CLOSE C_Get_Resource_Role;
2837 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2838 aso_debug_pub.add('Opp_Qte_PS: Role From Res: '||l_primary_role,1,'N');
2839 END IF;
2840 END IF;
2841
2842 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2843 aso_debug_pub.add('Opp_Qte_PS: Before primary salesagent insert: ',1,'N');
2844 END IF;
2845 l_sequence := NULL;
2846
2847 ASO_QUOTE_ACCESSES_PKG.Insert_Row(
2848 px_ACCESS_ID => l_sequence,
2849 p_QUOTE_NUMBER => P_Qte_Header_Rec.Quote_Number,
2850 p_RESOURCE_ID => l_primary_salesagent,
2851 p_RESOURCE_GRP_ID => l_primary_res_grp,
2852 p_CREATED_BY => G_USER_ID,
2853 p_CREATION_DATE => SYSDATE,
2854 p_LAST_UPDATED_BY => G_USER_ID,
2855 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
2856 p_LAST_UPDATE_DATE => SYSDATE,
2857 p_REQUEST_ID => FND_API.G_MISS_NUM,
2858 p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
2859 p_PROGRAM_ID => FND_API.G_MISS_NUM,
2860 p_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
2861 p_KEEP_FLAG => FND_API.G_MISS_CHAR,
2862 p_UPDATE_ACCESS_FLAG => 'Y',
2863 p_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
2864 p_TERRITORY_ID => FND_API.G_MISS_NUM,
2865 p_TERRITORY_SOURCE_FLAG => FND_API.G_MISS_CHAR,
2866 p_ROLE_ID => l_primary_role,
2867 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
2868 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
2869 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
2870 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
2871 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
2872 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
2873 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
2874 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
2875 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
2876 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
2877 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
2878 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
2879 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
2880 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
2881 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
2882 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
2883 p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
2884 p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
2885 p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
2886 p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
2887 p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
2888 p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
2889 );
2890
2891 END IF;
2892
2893 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2894 aso_debug_pub.add('Opp_Qte_PS: Update primary salesagent in Hdr ',1,'N');
2895 END IF;
2896 UPDATE ASO_QUOTE_HEADERS_ALL
2897 SET Resource_Id = l_primary_salesagent,
2898 Resource_Grp_Id = l_primary_res_grp,
2899 last_update_date = sysdate,
2900 last_updated_by = fnd_global.user_id,
2901 last_update_login = fnd_global.conc_login_id,
2902 object_version_number = object_version_number+1
2903 WHERE quote_number = P_Qte_Header_Rec.quote_number
2904 AND max_version_flag = 'Y'
2905 RETURNING quote_header_id, last_update_date, resource_id, resource_grp_id, object_version_number
2906 INTO x_qte_header_rec.Quote_Header_Id, x_qte_header_rec.Last_Update_Date,
2907 x_qte_header_rec.resource_id, x_qte_header_rec.resource_grp_id, x_qte_header_rec.object_version_number;
2908
2909 -- Standard call to get message count and if count is 1, get message info.
2910 FND_MSG_PUB.Count_And_Get
2911 ( p_count => x_msg_count,
2912 p_data => x_msg_data
2913 );
2914
2915 EXCEPTION
2916
2917 WHEN FND_API.G_EXC_ERROR THEN
2918 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2919 P_API_NAME => L_API_NAME,
2920 P_PKG_NAME => G_PKG_NAME,
2921 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
2922 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
2923 P_SQLCODE => SQLCODE,
2924 P_SQLERRM => SQLERRM,
2925 X_MSG_COUNT => X_MSG_COUNT,
2926 X_MSG_DATA => X_MSG_DATA,
2927 X_RETURN_STATUS => X_RETURN_STATUS
2928 );
2929
2930 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2931 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2932 P_API_NAME => L_API_NAME,
2933 P_PKG_NAME => G_PKG_NAME,
2934 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
2935 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
2936 P_SQLCODE => SQLCODE,
2937 P_SQLERRM => SQLERRM,
2938 X_MSG_COUNT => X_MSG_COUNT,
2939 X_MSG_DATA => X_MSG_DATA,
2940 X_RETURN_STATUS => X_RETURN_STATUS
2941 );
2942
2943 WHEN OTHERS THEN
2944 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2945 P_API_NAME => L_API_NAME,
2946 P_PKG_NAME => G_PKG_NAME,
2947 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
2948 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
2949 P_SQLCODE => SQLCODE,
2950 P_SQLERRM => SQLERRM,
2951 X_MSG_COUNT => X_MSG_COUNT,
2952 X_MSG_DATA => X_MSG_DATA,
2953 X_RETURN_STATUS => X_RETURN_STATUS
2954 );
2955
2956 END Opp_Quote_Primary_SalesRep;
2957
2958
2959 END ASO_SALES_TEAM_PVT;