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