[Home] [Help]
PACKAGE BODY: APPS.ASO_SECURITY_INT
Source
1 PACKAGE BODY ASO_SECURITY_INT AS
2 /* $Header: asoisecb.pls 120.4 2006/04/04 16:38:16 skulkarn ship $ */
3
4 -- Start of Comments
5 -- Package name : ASO_SECURITY_INT
6 -- Purpose : API methods for implementing Quoting Security
7 -- End of Comments
8
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ASO_SECURITY_INT';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoisecb.pls';
12
13
14
15 FUNCTION Get_Quote_Access
16 (
17 P_RESOURCE_ID IN NUMBER,
18 P_QUOTE_NUMBER IN NUMBER
19 ) RETURN VARCHAR2
20 IS
21
22 CURSOR C_direct_access (l_quote_number NUMBER, l_resource_id NUMBER) IS
23 SELECT update_access_flag
24 FROM ASO_QUOTE_ACCESSES
25 WHERE quote_number = l_quote_number
26 AND resource_id = l_resource_id;
27
28 CURSOR C_manager_access (l_resource_id NUMBER, l_quote_number NUMBER) IS
29 SELECT ACC.ACCESS_ID,
30 ACC.UPDATE_ACCESS_FLAG
31 FROM JTF_RS_REP_MANAGERS MGR,
32 JTF_RS_GROUP_USAGES UGS,
33 ASO_QUOTE_ACCESSES ACC
34 WHERE UGS.USAGE = 'SALES'
35 AND UGS.GROUP_ID = MGR.GROUP_ID
36 AND MGR.HIERARCHY_TYPE IN ('MGR_TO_MGR', 'MGR_TO_REP')
37 AND SYSDATE BETWEEN MGR.START_DATE_ACTIVE AND NVL(MGR.END_DATE_ACTIVE, SYSDATE)
38 AND ACC.RESOURCE_ID = MGR.RESOURCE_ID
39 AND NVL(ACC.RESOURCE_GRP_ID, MGR.GROUP_ID) = MGR.GROUP_ID
40 AND MGR.PARENT_RESOURCE_ID = l_resource_id
41 AND ACC.QUOTE_NUMBER = l_quote_number;
42
43 l_access_level VARCHAR2(10);
44 l_profile_access VARCHAR2(1);
45
46 BEGIN
47
48
49 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
50
51
52 -- API body
53 IF aso_debug_pub.g_debug_flag = 'Y' THEN
54 aso_debug_pub.add('ASO_SECURITY_INT: ****** Start of Get_Quote_Access API ******', 1, 'Y');
55 END IF;
56 l_access_level := 'NONE';
57 l_profile_access := 'N';
58
59 IF aso_debug_pub.g_debug_flag = 'Y' THEN
60 aso_debug_pub.add('ASO_SECURITY_INT: Get_Quote_Access: P_RESOURCE_ID: ' || P_RESOURCE_ID, 1, 'Y');
61 aso_debug_pub.add('ASO_SECURITY_INT: Get_Quote_Access: P_QUOTE_NUMBER: ' || P_QUOTE_NUMBER, 1, 'Y');
62 END IF;
63
64 FOR c_direct_access_rec IN C_direct_access(p_quote_number, p_resource_id) LOOP
65 IF c_direct_access_rec.update_access_flag = 'Y' THEN
66
67 IF aso_debug_pub.g_debug_flag = 'Y' THEN
68 aso_debug_pub.add('ASO_SECURITY_INT: Get_Quote_Access: resource has direct UPDATE access', 1, 'Y');
69 END IF;
70
71 RETURN 'UPDATE';
72 ELSE
73 IF aso_debug_pub.g_debug_flag = 'Y' THEN
74 aso_debug_pub.add('ASO_SECURITY_INT: Get_Quote_Access: resource has direct READ access', 1, 'Y');
75 END IF;
76 l_access_level := 'READ';
77 END IF;
78 END LOOP;
79
80 IF FND_PROFILE.VALUE('ASO_API_MGR_ROLE_ACCESS') = 'UPDATE' THEN
81 l_profile_access := 'Y';
82 END IF;
83
84 FOR c_manager_access_rec IN C_manager_access(p_resource_id, p_quote_number) LOOP
85 IF aso_debug_pub.g_debug_flag = 'Y' THEN
86 aso_debug_pub.add('ASO_SECURITY_INT: Get_Quote_Access: resource is manager', 1, 'Y');
87 END IF;
88
89 l_access_level := 'READ';
90 IF l_profile_access = 'Y' OR c_manager_access_rec.UPDATE_ACCESS_FLAG = 'Y' THEN
91 IF aso_debug_pub.g_debug_flag = 'Y' THEN
92 aso_debug_pub.add('ASO_SECURITY_INT: Get_Quote_Access: resource has profile UPDATE access OR subordinate of resource has direct UPDATE access', 1, 'Y');
93 aso_debug_pub.add('ASO_SECURITY_INT: Get_Quote_Access: profile access: ' || l_profile_access, 1, 'Y');
94 aso_debug_pub.add('ASO_SECURITY_INT: Get_Quote_Access: subordinate access: ' || c_manager_access_rec.UPDATE_ACCESS_FLAG, 1, 'Y');
95 END IF;
96
97 RETURN 'UPDATE';
98 END IF;
99 END LOOP;
100
101 -- End of API body
102 IF aso_debug_pub.g_debug_flag = 'Y' THEN
103 aso_debug_pub.add('ASO_SECURITY_INT: Get_Quote_Access: End of API body', 1, 'Y');
104 END IF;
105 RETURN l_access_level;
106
107 END Get_Quote_Access;
108
109
110
111
112 PROCEDURE Add_Resource
113 (
114 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
115 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
116 P_Qte_Access_Tbl IN Qte_Access_Tbl_Type,
117 p_call_from_oafwk_flag IN VARCHAR2,
118 X_Qte_Access_Tbl OUT NOCOPY /* file.sql.39 change */ Qte_Access_Tbl_Type,
119 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
120 X_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
121 X_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
122 )
123 IS
124
125 L_API_NAME VARCHAR2(50) := 'Add_Resource';
126
127 CURSOR C_existing_resource (l_quote_number NUMBER, l_resource_id NUMBER) IS
128 SELECT access_id
129 FROM ASO_QUOTE_ACCESSES
130 WHERE quote_number = l_quote_number
131 AND resource_id = l_resource_id;
132
133 CURSOR C_resource (l_access_id NUMBER) IS
134 SELECT quote_number,
135 resource_id,
136 resource_grp_id,
137 update_access_flag
138 FROM ASO_QUOTE_ACCESSES
139 WHERE access_id = l_access_id;
140
141 CURSOR C_primary_resource (l_quote_number NUMBER) IS
142 SELECT resource_id,quote_header_id,
143 resource_grp_id
144 FROM ASO_QUOTE_HEADERS_ALL
145 WHERE quote_number = l_quote_number
146 AND max_version_flag = 'Y';
147
148 cursor c_access_id_exist( p_access_id number) is
149 select access_id
150 from aso_quote_accesses
151 where access_id = p_access_id;
152
153 CURSOR Lock_check(p_qte_number Number)
154 IS SELECT price_request_id FROM
155 ASO_QUOTE_HEADERS_ALL where quote_number = p_qte_number
156 AND max_version_flag = 'Y';
157
158 l_quote_number NUMBER;
159 l_resource_id NUMBER;
160 l_resource_grp_id NUMBER;
161 l_update_access_flag VARCHAR2(1);
162 l_primary_resource_id NUMBER;
163 l_primary_resource_grp_id NUMBER;
164 l_access_id NUMBER;
165 l_exist_access_id NUMBER;
166
167 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
168 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
169 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type:=ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec;
170 l_x_status VARCHAR2(1);
171 l_qte_access_tbl Qte_Access_Tbl_Type := p_qte_access_tbl;
172
173 BEGIN
174 -- Standard Start of API savepoint
175 SAVEPOINT Add_Resource_INT;
176
177 -- Initialize API return status to success
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179
180 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
181 IF aso_debug_pub.g_debug_flag = 'Y' THEN
182 aso_debug_pub.add('ASO_SECURITY_INT: ****** Start of Add_Resource API ******', 1, 'Y');
183 aso_debug_pub.add('Add_Resource: p_call_from_oafwk_flag: ' || p_call_from_oafwk_flag);
184 aso_debug_pub.add('Add_Resource: p_qte_access_tbl.count: ' || p_qte_access_tbl.count);
185 END IF;
186
187 -- Initialize message list if p_init_msg_list is set to TRUE.
188 IF FND_API.To_Boolean(p_init_msg_list) THEN
189 FND_Msg_Pub.initialize;
190 END IF;
191
192 -- API body
193 X_Qte_Access_Tbl := l_qte_access_tbl;
194
195 FOR i IN 1..l_qte_access_tbl.count LOOP
196
197 l_quote_number := FND_API.G_MISS_NUM;
198 l_resource_id := FND_API.G_MISS_NUM;
199 l_resource_grp_id := FND_API.G_MISS_NUM;
200 l_update_access_flag := FND_API.G_MISS_CHAR;
201 l_primary_resource_id := FND_API.G_MISS_NUM;
202 l_primary_resource_grp_id := FND_API.G_MISS_NUM;
203 l_access_id := FND_API.G_MISS_NUM;
204
205
206 If ((l_qte_access_tbl(i).batch_price_flag <> fnd_api.g_false) or
207 (l_qte_access_tbl(i).batch_price_flag = fnd_api.g_miss_char)) then
208
209 FOR l_lock_rec IN Lock_check(l_qte_access_tbl(i).quote_number) LOOP
210
211 IF l_lock_rec.price_request_id IS NOT NULL THEN
212
213 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
214 FND_MESSAGE.Set_Name('ASO', 'ASO_CONC_REQUEST_RUNNING');
215 FND_MSG_PUB.ADD;
216 end if;
217
218 raise fnd_api.g_exc_error;
219
220 END IF;
221
222 END LOOP;
223
224 end if;
225
226 IF l_qte_access_tbl(i).access_id IS NOT NULL AND l_qte_access_tbl(i).access_id <> FND_API.G_MISS_NUM THEN
227
228 IF aso_debug_pub.g_debug_flag = 'Y' THEN
229 aso_debug_pub.add('Add_Resource: LOOP for access_id: ' || l_qte_access_tbl(i).access_id);
230 END IF;
231
232 if (nvl(p_call_from_oafwk_flag, 'N') = fnd_api.g_false)
233 OR ((nvl(p_call_from_oafwk_flag, 'N') = fnd_api.g_true) and (l_qte_access_tbl(i).operation_code = 'UPDATE')) then
234
235 open c_access_id_exist(l_qte_access_tbl(i).access_id);
236 fetch c_access_id_exist into l_qte_access_tbl(i).access_id;
237
238 if c_access_id_exist%notfound then
239
240 close c_access_id_exist;
241
242 x_return_status := fnd_api.g_ret_sts_error;
243
244 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
245 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_ID');
246 FND_MESSAGE.Set_token('COLUMN', 'access_id');
247 FND_MESSAGE.Set_token('VALUE', l_qte_access_tbl(i).access_id);
248 FND_MSG_PUB.Add;
249 END IF;
250
251 RAISE FND_API.G_EXC_ERROR;
252
253 end if;
254
255 close c_access_id_exist;
256
257 end if;
258
259 ELSE
260
261 IF l_qte_access_tbl(i).quote_number IS NOT NULL AND l_qte_access_tbl(i).quote_number <> FND_API.G_MISS_NUM THEN
262
263 IF l_qte_access_tbl(i).resource_id IS NOT NULL AND l_qte_access_tbl(i).resource_id <> FND_API.G_MISS_NUM THEN
264
265 IF aso_debug_pub.g_debug_flag = 'Y' THEN
266 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: LOOP for resource_id: ' || l_qte_access_tbl(i).resource_id, 1, 'Y');
267 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: LOOP for quote_number: ' || l_qte_access_tbl(i).quote_number, 1, 'Y');
268 END IF;
269
270 FOR l_access_rec IN C_existing_resource(l_qte_access_tbl(i).quote_number, l_qte_access_tbl(i).resource_id) LOOP
271 l_qte_access_tbl(i).access_id := l_access_rec.access_id;
272 l_qte_access_tbl(i).operation_code := 'UPDATE';
273 END LOOP;
274
275 END IF;
276 END IF;
277 END IF;
278
279 IF aso_debug_pub.g_debug_flag = 'Y' THEN
280 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: l_access_id: ' || l_access_id, 1, 'Y');
281 END IF;
282
283 IF nvl(p_call_from_oafwk_flag, 'N') = fnd_api.g_false THEN
284
285 IF ((l_qte_access_tbl(i).access_id is null) OR (l_qte_access_tbl(i).access_id = fnd_api.g_miss_num)) THEN
286
287 l_qte_access_tbl(i).operation_code := 'CREATE';
288 ELSE
289 l_qte_access_tbl(i).operation_code := 'UPDATE';
290 END IF;
291
292 END IF;
293
294 IF aso_debug_pub.g_debug_flag = 'Y' THEN
295 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: l_qte_access_tbl(i).operation_code: ' || l_qte_access_tbl(i).operation_code, 1, 'Y');
296 END IF;
297
298 IF l_qte_access_tbl(i).operation_code = 'CREATE' THEN
299
300 -- 4535602
301 OPEN C_existing_resource(l_qte_access_tbl(i).quote_number, l_qte_access_tbl(i).resource_id);
302 FETCH C_existing_resource INTO l_exist_access_id;
303 IF C_existing_resource%FOUND THEN
304 x_return_status := fnd_api.g_ret_sts_error;
305
306 IF aso_debug_pub.g_debug_flag = 'Y' THEN
307 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: duplicate resource: ' || l_exist_access_id, 1, 'Y');
308 END IF;
309 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
310 FND_MESSAGE.Set_Name('ASO', 'ASO_DUPLICATE_RESOURCE_ID');
311 FND_MSG_PUB.Add;
312 END IF;
313
314 CLOSE C_existing_resource;
315
316 RAISE FND_API.G_EXC_ERROR;
317 END IF;
318 CLOSE C_existing_resource;
319 -- 4535602
320
321 l_quote_number := l_qte_access_tbl(i).QUOTE_NUMBER;
322
323 IF aso_debug_pub.g_debug_flag = 'Y' THEN
324 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: before Insert_Row: counter: ' || i, 1, 'Y');
325 END IF;
326
327 ASO_QUOTE_ACCESSES_PKG.Insert_Row(
328 px_ACCESS_ID => l_qte_access_tbl(i).access_id,
329 p_QUOTE_NUMBER => l_qte_access_tbl(i).QUOTE_NUMBER,
330 p_RESOURCE_ID => l_qte_access_tbl(i).RESOURCE_ID,
331 p_RESOURCE_GRP_ID => l_qte_access_tbl(i).RESOURCE_GRP_ID,
332 p_CREATED_BY => G_USER_ID,
333 p_CREATION_DATE => SYSDATE,
334 p_LAST_UPDATED_BY => G_USER_ID,
335 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
336 p_LAST_UPDATE_DATE => SYSDATE,
337 p_REQUEST_ID => l_qte_access_tbl(i).REQUEST_ID,
338 p_PROGRAM_APPLICATION_ID => l_qte_access_tbl(i).PROGRAM_APPLICATION_ID,
339 p_PROGRAM_ID => l_qte_access_tbl(i).PROGRAM_ID,
340 p_PROGRAM_UPDATE_DATE => l_qte_access_tbl(i).PROGRAM_UPDATE_DATE,
341 p_KEEP_FLAG => l_qte_access_tbl(i).KEEP_FLAG,
342 p_UPDATE_ACCESS_FLAG => l_qte_access_tbl(i).UPDATE_ACCESS_FLAG,
343 p_CREATED_BY_TAP_FLAG => l_qte_access_tbl(i).CREATED_BY_TAP_FLAG,
344 p_TERRITORY_ID => l_qte_access_tbl(i).TERRITORY_ID,
345 p_TERRITORY_SOURCE_FLAG => 'N',
346 p_ROLE_ID => l_qte_access_tbl(i).ROLE_ID,
347 p_ATTRIBUTE_CATEGORY => l_qte_access_tbl(i).ATTRIBUTE_CATEGORY,
348 p_ATTRIBUTE1 => l_qte_access_tbl(i).ATTRIBUTE1,
349 p_ATTRIBUTE2 => l_qte_access_tbl(i).ATTRIBUTE2,
350 p_ATTRIBUTE3 => l_qte_access_tbl(i).ATTRIBUTE3,
351 p_ATTRIBUTE4 => l_qte_access_tbl(i).ATTRIBUTE4,
352 p_ATTRIBUTE5 => l_qte_access_tbl(i).ATTRIBUTE5,
353 p_ATTRIBUTE6 => l_qte_access_tbl(i).ATTRIBUTE6,
354 p_ATTRIBUTE7 => l_qte_access_tbl(i).ATTRIBUTE7,
355 p_ATTRIBUTE8 => l_qte_access_tbl(i).ATTRIBUTE8,
356 p_ATTRIBUTE9 => l_qte_access_tbl(i).ATTRIBUTE9,
357 p_ATTRIBUTE10 => l_qte_access_tbl(i).ATTRIBUTE10,
358 p_ATTRIBUTE11 => l_qte_access_tbl(i).ATTRIBUTE11,
359 p_ATTRIBUTE12 => l_qte_access_tbl(i).ATTRIBUTE12,
360 p_ATTRIBUTE13 => l_qte_access_tbl(i).ATTRIBUTE13,
361 p_ATTRIBUTE14 => l_qte_access_tbl(i).ATTRIBUTE14,
362 p_ATTRIBUTE15 => l_qte_access_tbl(i).ATTRIBUTE15,
363 p_ATTRIBUTE16 => l_qte_access_tbl(i).ATTRIBUTE16,
364 p_ATTRIBUTE17 => l_qte_access_tbl(i).ATTRIBUTE17,
365 p_ATTRIBUTE18 => l_qte_access_tbl(i).ATTRIBUTE18,
366 p_ATTRIBUTE19 => l_qte_access_tbl(i).ATTRIBUTE19,
367 p_ATTRIBUTE20 => l_qte_access_tbl(i).ATTRIBUTE20,
368 p_OBJECT_VERSION_NUMBER => l_qte_access_tbl(i).OBJECT_VERSION_NUMBER
369 );
370
371 X_Qte_Access_Tbl(i).access_id := l_qte_access_tbl(i).access_id;
372
373 IF aso_debug_pub.g_debug_flag = 'Y' THEN
374 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: after Insert_Row: x_qte_access_tbl('||i||').access_id: ' || x_qte_access_tbl(i).access_id, 1, 'Y');
375 END IF;
376
377 ELSIF l_qte_access_tbl(i).operation_code = 'UPDATE' THEN
378
379 IF aso_debug_pub.g_debug_flag = 'Y' THEN
380 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: before Update_Row: counter: ' || i, 1, 'Y');
381 END IF;
382
383 ASO_QUOTE_ACCESSES_PKG.Update_Row(
384 p_ACCESS_ID => l_qte_access_tbl(i).access_id,
385 p_QUOTE_NUMBER => l_qte_access_tbl(i).QUOTE_NUMBER,
386 p_RESOURCE_ID => l_qte_access_tbl(i).RESOURCE_ID,
387 p_RESOURCE_GRP_ID => l_qte_access_tbl(i).RESOURCE_GRP_ID,
388 p_CREATED_BY => G_USER_ID,
389 p_CREATION_DATE => fnd_api.g_miss_date,
390 p_LAST_UPDATED_BY => G_USER_ID,
391 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
392 p_LAST_UPDATE_DATE => SYSDATE,
393 p_REQUEST_ID => l_qte_access_tbl(i).REQUEST_ID,
394 p_PROGRAM_APPLICATION_ID => l_qte_access_tbl(i).PROGRAM_APPLICATION_ID,
395 p_PROGRAM_ID => l_qte_access_tbl(i).PROGRAM_ID,
396 p_PROGRAM_UPDATE_DATE => l_qte_access_tbl(i).PROGRAM_UPDATE_DATE,
397 p_KEEP_FLAG => l_qte_access_tbl(i).KEEP_FLAG,
398 p_UPDATE_ACCESS_FLAG => l_qte_access_tbl(i).UPDATE_ACCESS_FLAG,
399 p_CREATED_BY_TAP_FLAG => l_qte_access_tbl(i).CREATED_BY_TAP_FLAG,
400 p_TERRITORY_ID => l_qte_access_tbl(i).TERRITORY_ID,
401 p_TERRITORY_SOURCE_FLAG => l_qte_access_tbl(i).TERRITORY_SOURCE_FLAG,
402 p_ROLE_ID => l_qte_access_tbl(i).ROLE_ID,
403 p_ATTRIBUTE_CATEGORY => l_qte_access_tbl(i).ATTRIBUTE_CATEGORY,
404 p_ATTRIBUTE1 => l_qte_access_tbl(i).ATTRIBUTE1,
405 p_ATTRIBUTE2 => l_qte_access_tbl(i).ATTRIBUTE2,
406 p_ATTRIBUTE3 => l_qte_access_tbl(i).ATTRIBUTE3,
407 p_ATTRIBUTE4 => l_qte_access_tbl(i).ATTRIBUTE4,
408 p_ATTRIBUTE5 => l_qte_access_tbl(i).ATTRIBUTE5,
409 p_ATTRIBUTE6 => l_qte_access_tbl(i).ATTRIBUTE6,
410 p_ATTRIBUTE7 => l_qte_access_tbl(i).ATTRIBUTE7,
411 p_ATTRIBUTE8 => l_qte_access_tbl(i).ATTRIBUTE8,
412 p_ATTRIBUTE9 => l_qte_access_tbl(i).ATTRIBUTE9,
413 p_ATTRIBUTE10 => l_qte_access_tbl(i).ATTRIBUTE10,
414 p_ATTRIBUTE11 => l_qte_access_tbl(i).ATTRIBUTE11,
415 p_ATTRIBUTE12 => l_qte_access_tbl(i).ATTRIBUTE12,
416 p_ATTRIBUTE13 => l_qte_access_tbl(i).ATTRIBUTE13,
417 p_ATTRIBUTE14 => l_qte_access_tbl(i).ATTRIBUTE14,
418 p_ATTRIBUTE15 => l_qte_access_tbl(i).ATTRIBUTE15,
419 p_ATTRIBUTE16 => l_qte_access_tbl(i).ATTRIBUTE16,
420 p_ATTRIBUTE17 => l_qte_access_tbl(i).ATTRIBUTE17,
421 p_ATTRIBUTE18 => l_qte_access_tbl(i).ATTRIBUTE18,
422 p_ATTRIBUTE19 => l_qte_access_tbl(i).ATTRIBUTE19,
423 p_ATTRIBUTE20 => l_qte_access_tbl(i).ATTRIBUTE20,
424 p_OBJECT_VERSION_NUMBER => l_qte_access_tbl(i).OBJECT_VERSION_NUMBER
425 );
426
427 X_Qte_Access_Tbl(i).access_id := l_qte_access_tbl(i).access_id;
428
429 IF aso_debug_pub.g_debug_flag = 'Y' THEN
430 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: after Update_Row: x_qte_access_tbl('||i||').access_id: ' || x_qte_access_tbl(i).access_id, 1, 'Y');
431 END IF;
432
433 FOR l_resource_rec IN C_resource(l_qte_access_tbl(i).access_id) LOOP
434 l_quote_number := l_resource_rec.quote_number;
435 l_resource_id := l_resource_rec.resource_id;
436 l_resource_grp_id := l_resource_rec.resource_grp_id;
437 l_update_access_flag := l_resource_rec.update_access_flag;
438
439 IF aso_debug_pub.g_debug_flag = 'Y' THEN
440 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: *** C_resource LOOP variables ***', 1, 'Y');
441 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: l_quote_number: ' || l_quote_number, 1, 'Y');
442 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: l_resource_id: ' || l_resource_id, 1, 'Y');
443 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: l_resource_grp_id: ' || l_resource_grp_id, 1, 'Y');
444 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: l_update_access_flag: ' || l_update_access_flag, 1, 'Y');
445 END IF;
446 END LOOP;
447
448 FOR l_primary_resource_rec IN C_primary_resource(l_quote_number) LOOP
449 l_primary_resource_id := l_primary_resource_rec.resource_id;
450 l_primary_resource_grp_id := l_primary_resource_rec.resource_grp_id;
451 l_qte_header_rec.quote_header_id := l_primary_resource_rec.quote_header_id;
452
453 IF aso_debug_pub.g_debug_flag = 'Y' THEN
454 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: *** C_primary_resource LOOP variables ***', 1, 'Y');
455 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: l_primary_resource_id: ' || l_primary_resource_id, 1, 'Y');
456 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: l_primary_resource_grp_id: ' || l_primary_resource_grp_id, 1, 'Y');
457 END IF;
458
459 END LOOP;
460
461 IF l_primary_resource_id = l_resource_id THEN
462 IF l_update_access_flag <> 'Y' THEN
463 x_return_status := FND_API.G_RET_STS_ERROR;
464
465 IF aso_debug_pub.g_debug_flag = 'Y' THEN
466 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: trying to set primary resource update_access_flag other than Y', 1, 'Y');
467 END IF;
468 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
469 FND_MESSAGE.Set_Name('ASO', 'ASO_SEC_ADD_INVALID_ACCESS');
470 FND_MSG_PUB.Add;
471 END IF;
472 RAISE FND_API.G_EXC_ERROR;
473 ELSE
474 IF l_resource_grp_id IS NOT NULL AND l_resource_grp_id <> FND_API.G_MISS_NUM THEN
475 IF l_primary_resource_grp_id <> l_resource_grp_id THEN
476 IF aso_debug_pub.g_debug_flag = 'Y' THEN
477 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: before update ASO_QUOTE_HEADERS_ALL', 1, 'Y');
478 END IF;
479
480 UPDATE ASO_QUOTE_HEADERS_ALL
481 SET resource_grp_id = l_resource_grp_id,
482 last_update_date = sysdate,
483 last_updated_by = fnd_global.user_id,
484 last_update_login = fnd_global.conc_login_id
485 WHERE quote_number = l_quote_number
486 AND max_version_flag = 'Y';
487
488 IF SQL%ROWCOUNT = 0 THEN
489 IF aso_debug_pub.g_debug_flag = 'Y' THEN
490 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: inside update ASO_QUOTE_HEADERS_ALL: SQL%ROWCOUNT = 0', 1, 'Y');
491 END IF;
492 x_return_status := FND_API.G_RET_STS_ERROR;
493 RAISE FND_API.G_EXC_ERROR;
494 END IF;
495
496 IF aso_debug_pub.g_debug_flag = 'Y' THEN
497 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: after update ASO_QUOTE_HEADERS_ALL', 1, 'Y');
498 END IF;
499
500 END IF;
501 END IF;
502 END IF;
503 END IF;
504
505 IF aso_debug_pub.g_debug_flag = 'Y' THEN
506 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: after Update_Row: x_qte_access_tbl('||i||').access_id:: ' || x_qte_access_tbl(i).access_id, 1, 'Y');
507 END IF;
508
509 END IF;
510
511 END LOOP;
512
513 IF aso_debug_pub.g_debug_flag = 'Y' THEN
514 aso_debug_pub.add('ASO_SECURITY_INT: End of Add_Resource: before update ASO_QUOTE_HEADERS_ALL', 1, 'Y');
515 aso_debug_pub.add('ASO_SECURITY_INT: End of Add_Resource: l_quote_number: '||l_quote_number, 1, 'Y');
516 END IF;
517
518 UPDATE ASO_QUOTE_HEADERS_ALL
519 SET last_update_date = sysdate,
520 last_updated_by = fnd_global.user_id,
521 last_update_login = fnd_global.conc_login_id
522 WHERE quote_number = l_quote_number AND
523 max_version_flag = 'Y';
524
525 IF SQL%ROWCOUNT = 0 THEN
526 IF aso_debug_pub.g_debug_flag = 'Y' THEN
527 aso_debug_pub.add('ASO_SECURITY_INT: End of Add_Resource: after update ASO_QUOTE_HEADERS_ALL: SQL%ROWCOUNT = 0', 1, 'Y');
528 END IF;
529 x_return_status := FND_API.G_RET_STS_ERROR;
530 RAISE FND_API.G_EXC_ERROR;
531 END IF;
532
533 -- Change START
534 -- Release 12 TAP Changes
535 -- Girish Sachdeva 8/30/2005
536 -- Adding the call to insert record in the ASO_CHANGED_QUOTES
537
538 IF aso_debug_pub.g_debug_flag = 'Y' THEN
539 aso_debug_pub.add('ASO_SECURITY_INT.Add_Resource : Calling ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES, quote number : ' || l_quote_number, 1, 'Y');
540 END IF;
541
542 -- Call to insert record in ASO_CHANGED_QUOTES
543 ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES(l_quote_number);
544
545 -- Change END
546
547 -- End of API body
548 IF aso_debug_pub.g_debug_flag = 'Y' THEN
549 aso_debug_pub.add('ASO_SECURITY_INT: Add_Resource: End of API body', 1, 'Y');
550 END IF;
551
552 -- Standard check of p_commit.
553 IF FND_API.To_Boolean(p_commit) THEN
554 COMMIT WORK;
555 END IF;
556
557 -- Standard call to get message count and if count is 1, get message info.
558 FND_Msg_Pub.Count_And_Get(
559 p_encoded => FND_API.G_FALSE,
560 p_count => x_msg_count,
561 p_data => x_msg_data
562 );
563
564 EXCEPTION
565 WHEN FND_API.G_EXC_ERROR THEN
566 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
567 P_API_NAME => L_API_NAME,
568 P_PKG_NAME => G_PKG_NAME,
569 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
570 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT,
571 P_SQLCODE => SQLCODE,
572 P_SQLERRM => SQLERRM,
573 X_MSG_COUNT => X_MSG_COUNT,
574 X_MSG_DATA => X_MSG_DATA,
575 X_RETURN_STATUS => X_RETURN_STATUS
576 );
577
578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
579 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
580 P_API_NAME => L_API_NAME,
581 P_PKG_NAME => G_PKG_NAME,
582 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
583 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT,
584 P_SQLCODE => SQLCODE,
585 P_SQLERRM => SQLERRM,
586 X_MSG_COUNT => X_MSG_COUNT,
587 X_MSG_DATA => X_MSG_DATA,
588 X_RETURN_STATUS => X_RETURN_STATUS
589 );
590
591 WHEN OTHERS THEN
592 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
593 P_API_NAME => L_API_NAME,
594 P_PKG_NAME => G_PKG_NAME,
595 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
596 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT,
597 P_SQLCODE => SQLCODE,
598 P_SQLERRM => SQLERRM,
599 X_MSG_COUNT => X_MSG_COUNT,
600 X_MSG_DATA => X_MSG_DATA,
601 X_RETURN_STATUS => X_RETURN_STATUS
602 );
603
604 END Add_Resource;
605
606 PROCEDURE Add_Resource
607 (
608 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
609 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
610 P_Qte_Access_Tbl IN Qte_Access_Tbl_Type,
611 X_Qte_Access_Tbl OUT NOCOPY /* file.sql.39 change */ Qte_Access_Tbl_Type,
612 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
613 X_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
614 X_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
615 )
616 IS
617
618 l_call_from_oafwk_flag varchar2(1) := fnd_api.g_false;
619
620 Begin
621 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
622
623 IF aso_debug_pub.g_debug_flag = 'Y' THEN
624 aso_debug_pub.add('ASO_SECURITY_INT: ****** Start of Add_Resource API not overloaded ******', 1, 'Y');
625 END IF;
626
627 IF aso_debug_pub.g_debug_flag = 'Y' THEN
628 aso_debug_pub.add('ASO_SECURITY_INT: Before call to Add_Resource overloaded procedure', 1, 'Y');
629 END IF;
630
631 ASO_SECURITY_INT.Add_Resource(
632 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
633 P_COMMIT => P_COMMIT,
634 P_Qte_Access_tbl => p_qte_access_tbl,
635 p_call_from_oafwk_flag => l_call_from_oafwk_flag,
636 X_Qte_Access_tbl => x_qte_access_tbl,
637 X_RETURN_STATUS => x_return_status,
638 X_msg_count => X_msg_count,
639 X_msg_data => X_msg_data );
640
641 IF aso_debug_pub.g_debug_flag = 'Y' THEN
642 aso_debug_pub.add('ASO_SECURITY_INT: After call to Add_Resource overloaded procedure', 1, 'Y');
643 END IF;
644
645 End Add_Resource;
646
647
648 PROCEDURE Delete_Resource
649 (
650 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
651 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
652 P_Qte_Access_Tbl IN Qte_Access_Tbl_Type,
653 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
654 X_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
655 X_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
656 )
657 IS
658
659 L_API_NAME VARCHAR2(50) := 'Delete_Resource';
660
661 CURSOR C_existing_resource (l_quote_number NUMBER, l_resource_id NUMBER) IS
662 SELECT access_id
663 FROM ASO_QUOTE_ACCESSES
664 WHERE quote_number = l_quote_number
665 AND resource_id = l_resource_id;
666
667 CURSOR C_resource (l_access_id NUMBER) IS
668 SELECT quote_number,
669 resource_id
670 FROM ASO_QUOTE_ACCESSES
671 WHERE access_id = l_access_id;
672
673 CURSOR C_primary_resource (l_quote_number NUMBER) IS
674 SELECT resource_id
675 FROM ASO_QUOTE_HEADERS_ALL
676 WHERE quote_number = l_quote_number
677 AND max_version_flag = 'Y';
678
679 CURSOR C_Lock_check(p_qte_number Number)
680 IS SELECT price_request_id FROM
681 ASO_QUOTE_HEADERS_ALL where quote_number = p_qte_number
682 AND max_version_flag = 'Y';
683
684 CURSOR C_Quote_num(p_access_id Number) IS
685 SELECT quote_number FROM
686 ASO_QUOTE_ACCESSES WHERE
687 access_id = p_access_id;
688
689 l_primary_resource_id NUMBER;
690 l_resource_id NUMBER;
691 l_quote_number NUMBER;
692 l_access_id NUMBER;
693
694 BEGIN
695 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
696 IF aso_debug_pub.g_debug_flag = 'Y' THEN
697 aso_debug_pub.add('ASO_SECURITY_INT: ****** Start of Delete_Resource API ******', 1, 'Y');
698 END IF;
699
700 -- Standard Start of API savepoint
701 SAVEPOINT Delete_Resource_INT;
702
703 -- Initialize message list if p_init_msg_list is set to TRUE.
704 IF FND_API.To_Boolean(p_init_msg_list) THEN
705 FND_Msg_Pub.initialize;
706 END IF;
707
708 -- Initialize API return status to success
709 x_return_status := FND_API.G_RET_STS_SUCCESS;
710
711 -- API body
712
713 FOR i IN 1..P_Qte_Access_Tbl.count LOOP
714
715 l_primary_resource_id := FND_API.G_MISS_NUM;
716 l_resource_id := FND_API.G_MISS_NUM;
717 l_quote_number := FND_API.G_MISS_NUM;
718 l_access_id := FND_API.G_MISS_NUM;
719
720 IF P_Qte_Access_Tbl(i).access_id IS NOT NULL AND P_Qte_Access_Tbl(i).access_id <> FND_API.G_MISS_NUM THEN
721 IF aso_debug_pub.g_debug_flag = 'Y' THEN
722 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: LOOP for access_id: ' || P_Qte_Access_Tbl(i).access_id, 1, 'Y');
723 END IF;
724
725 l_access_id := P_Qte_Access_Tbl(i).access_id;
726
727 if ((p_qte_access_tbl(i).batch_price_flag <> fnd_api.g_false) or
728 (p_qte_access_tbl(i).batch_price_flag = fnd_api.g_miss_char)) then
729
730 FOR l_quote_num_rec IN C_Quote_num(l_access_id) LOOP
731
732 FOR l_lock_rec IN C_lock_check(l_quote_num_rec.quote_number) LOOP
733
734 IF l_lock_rec.price_request_id IS NOT NULL THEN
735
736 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
737 FND_MESSAGE.Set_Name('ASO', 'ASO_CONC_REQUEST_RUNNING');
738 FND_MSG_PUB.ADD;
739 END IF;
740
741 raise FND_API.G_EXC_ERROR;
742 END IF;
743
744 END LOOP;
745
746 END LOOP;
747
748 end if;
749
750 ELSE
751
752 IF P_Qte_Access_Tbl(i).quote_number IS NOT NULL AND P_Qte_Access_Tbl(i).quote_number <> FND_API.G_MISS_NUM THEN
753 IF P_Qte_Access_Tbl(i).resource_id IS NOT NULL AND P_Qte_Access_Tbl(i).resource_id <> FND_API.G_MISS_NUM THEN
754
755 if ((p_qte_access_tbl(i).batch_price_flag <> fnd_api.g_false) or
756 (p_qte_access_tbl(i).batch_price_flag = fnd_api.g_miss_char)) then
757
758 FOR l_lock_rec IN C_lock_check(P_Qte_Access_Tbl(i).quote_number) LOOP
759
760 IF l_lock_rec.price_request_id IS NOT NULL THEN
761
762 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
763 FND_MESSAGE.Set_Name('ASO', 'ASO_CONC_REQUEST_RUNNING');
764 FND_MSG_PUB.ADD;
765 END IF;
766
767 raise FND_API.G_EXC_ERROR;
768
769 END IF;
770
771 END LOOP;
772
773 end if;
774
775 IF aso_debug_pub.g_debug_flag = 'Y' THEN
776 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: LOOP for resource_id: ' || P_Qte_Access_Tbl(i).resource_id, 1, 'Y');
777 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: LOOP for quote_number: ' || P_Qte_Access_Tbl(i).quote_number, 1, 'Y');
778 END IF;
779
780 FOR l_access_rec IN C_existing_resource(P_Qte_Access_Tbl(i).quote_number, P_Qte_Access_Tbl(i).resource_id) LOOP
781 l_access_id := l_access_rec.access_id;
782 END LOOP;
783
784 END IF;
785 END IF;
786 END IF;
787
788 IF aso_debug_pub.g_debug_flag = 'Y' THEN
789 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: count: ' || P_Qte_Access_Tbl.count);
790 END IF;
791
792 IF l_access_id IS NOT NULL AND l_access_id <> FND_API.G_MISS_NUM THEN
793
794 FOR l_resource_rec IN C_resource(l_access_id) LOOP
795 l_quote_number := l_resource_rec.quote_number;
796 l_resource_id := l_resource_rec.resource_id;
797
798 IF aso_debug_pub.g_debug_flag = 'Y' THEN
799 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: *** C_resource LOOP variables ***');
800 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: l_quote_number: ' || l_quote_number);
801 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: l_resource_id: ' || l_resource_id);
802 END IF;
803 END LOOP;
804
805 IF l_quote_number IS NOT NULL AND l_quote_number <> FND_API.G_MISS_NUM THEN
806
807 FOR l_primary_resource_rec IN C_primary_resource(l_quote_number) LOOP
808 l_primary_resource_id := l_primary_resource_rec.resource_id;
809
810 IF aso_debug_pub.g_debug_flag = 'Y' THEN
811 aso_debug_pub.add('Delete_Resource: *** C_primary_resource LOOP variables ***');
812 aso_debug_pub.add('Delete_Resource: l_primary_resource_id: ' || l_primary_resource_id);
813 END IF;
814 END LOOP;
815
816 IF l_primary_resource_id = l_resource_id THEN
817 x_return_status := FND_API.G_RET_STS_ERROR;
818
819 IF aso_debug_pub.g_debug_flag = 'Y' THEN
820 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: trying to delete primary salesrep', 1, 'Y');
821 END IF;
822
823 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
824 FND_MESSAGE.Set_Name('ASO', 'ASO_SEC_DELETE_PRIMARY_RES');
825 FND_MSG_PUB.Add;
826 END IF;
827 RAISE FND_API.G_EXC_ERROR;
828 ELSE
829
830 IF aso_debug_pub.g_debug_flag = 'Y' THEN
831 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: before Delete_Row: l_access_id: ' || l_access_id, 1, 'Y');
832
833 END IF;
834
835 ASO_QUOTE_ACCESSES_PKG.Delete_Row(
836 p_ACCESS_ID => l_access_id
837 );
838
839 IF aso_debug_pub.g_debug_flag = 'Y' THEN
840 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: after Delete_Row', 1, 'Y');
841 END IF;
842
843 END IF;
844
845 ELSE
846 x_return_status := FND_API.G_RET_STS_ERROR;
847
848 IF aso_debug_pub.g_debug_flag = 'Y' THEN
849 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: trying to delete a non-existent entry', 1, 'Y');
850
851 END IF;
852 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
853 FND_MESSAGE.Set_Name('ASO', 'ASO_SEC_DELETE_INVALID_ID');
854 FND_MSG_PUB.Add;
855 END IF;
856 RAISE FND_API.G_EXC_ERROR;
857 END IF;
858
859 ELSE
860 x_return_status := FND_API.G_RET_STS_ERROR;
861
862 IF aso_debug_pub.g_debug_flag = 'Y' THEN
863 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: trying to delete without passing enough info', 1, 'Y');
864 END IF;
865
866 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
867 FND_MESSAGE.Set_Name('ASO', 'ASO_SEC_DELETE_INSUFFICIENT');
868 FND_MSG_PUB.Add;
869 END IF;
870 RAISE FND_API.G_EXC_ERROR;
871 END IF;
872
873 END LOOP;
874
875 IF aso_debug_pub.g_debug_flag = 'Y' THEN
876 aso_debug_pub.add('ASO_SECURITY_INT: End of Delete_Resource: before update ASO_QUOTE_HEADERS_ALL', 1, 'Y');
877 END IF;
878
879 UPDATE ASO_QUOTE_HEADERS_ALL
880 SET last_update_date = sysdate,
881 last_updated_by = fnd_global.user_id,
882 last_update_login = fnd_global.conc_login_id
883 WHERE quote_number = l_quote_number AND
884 max_version_flag = 'Y';
885
886 IF SQL%ROWCOUNT = 0 THEN
887 IF aso_debug_pub.g_debug_flag = 'Y' THEN
888 aso_debug_pub.add('ASO_SECURITY_INT: End of Delete_Resource: after update ASO_QUOTE_HEADERS_ALL: SQL%ROWCOUNT = 0', 1, 'Y');
889 END IF;
890 x_return_status := FND_API.G_RET_STS_ERROR;
891 RAISE FND_API.G_EXC_ERROR;
892 END IF;
893
894 -- Change START
895 -- Release 12 TAP Changes
896 -- Girish Sachdeva 8/30/2005
897 -- Adding the call to insert record in the ASO_CHANGED_QUOTES
898
899 IF aso_debug_pub.g_debug_flag = 'Y' THEN
900 aso_debug_pub.add('ASO_SECURITY_INT.Delete_Resource : Calling ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES, quote number : ' || l_quote_number, 1, 'Y');
901 end if;
902
903 -- Call to insert record in ASO_CHANGED_QUOTES
904 ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES(l_quote_number);
905
906 -- Change END
907
908 -- End of API body
909 IF aso_debug_pub.g_debug_flag = 'Y' THEN
910 aso_debug_pub.add('ASO_SECURITY_INT: Delete_Resource: End of API body', 1, 'Y');
911 END IF;
912 -- Standard check of p_commit.
913 IF FND_API.To_Boolean(p_commit) THEN
914 COMMIT WORK;
915 END IF;
916
917 -- Standard call to get message count and if count is 1, get message info.
918 FND_Msg_Pub.Count_And_Get(
919 p_encoded => FND_API.G_FALSE,
920 p_count => x_msg_count ,
921 p_data => x_msg_data
922 );
923
924 EXCEPTION
925 WHEN FND_API.G_EXC_ERROR THEN
926 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
927 P_API_NAME => L_API_NAME,
928 P_PKG_NAME => G_PKG_NAME,
929 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
930 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT,
931 P_SQLCODE => SQLCODE,
932 P_SQLERRM => SQLERRM,
933 X_MSG_COUNT => X_MSG_COUNT,
934 X_MSG_DATA => X_MSG_DATA,
935 X_RETURN_STATUS => X_RETURN_STATUS
936 );
937
938 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
939 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
940 P_API_NAME => L_API_NAME,
941 P_PKG_NAME => G_PKG_NAME,
942 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
943 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT,
944 P_SQLCODE => SQLCODE,
945 P_SQLERRM => SQLERRM,
946 X_MSG_COUNT => X_MSG_COUNT,
947 X_MSG_DATA => X_MSG_DATA,
948 X_RETURN_STATUS => X_RETURN_STATUS
949 );
950
951 WHEN OTHERS THEN
952 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
953 P_API_NAME => L_API_NAME,
954 P_PKG_NAME => G_PKG_NAME,
955 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
956 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT,
957 P_SQLCODE => SQLCODE,
958 P_SQLERRM => SQLERRM,
959 X_MSG_COUNT => X_MSG_COUNT,
960 X_MSG_DATA => X_MSG_DATA,
961 X_RETURN_STATUS => X_RETURN_STATUS
962 );
963
964 END Delete_Resource;
965
966
967 PROCEDURE Add_SalesRep_QuoteCreator
968 (
969 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
970 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
971 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
972 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
973 X_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
974 X_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
975 )
976 IS
977
978 L_API_NAME VARCHAR2(50) := 'Add_SalesRep_QuoteCreator';
979
980 CURSOR C_user_resource_id (l_user_id NUMBER) IS
981 SELECT resource_id
982 FROM jtf_rs_resource_extns
983 WHERE user_id = l_user_id
984 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
985
986 CURSOR C_get_resource_role ( l_resource_id NUMBER) IS
987 SELECT rel.role_id
988 FROM jtf_rs_role_relations rel, jtf_rs_roles_b rolb
989 WHERE rel.role_id = rolb.role_id
990 AND rolb.role_type_code = 'SALES'
991 AND NVL(rolb.active_flag, 'Y') <> 'N'
992 AND NVL(rel.delete_flag , 'N') <> 'Y'
993 AND TRUNC(NVL(rel.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
994 AND TRUNC(NVL(rel.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
995 AND rel.role_resource_id = l_resource_id;
996
997
998
999 c_profile_value Varchar2(2000);
1000 l_qte_access_rec Qte_Access_Rec_Type := G_MISS_QTE_ACCESS_REC;
1001 l_qte_access_tbl Qte_Access_Tbl_Type := G_MISS_QTE_ACCESS_TBL;
1002 lx_qte_access_tbl Qte_Access_Tbl_Type;
1003 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
1004 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
1005 l_obsolete_status varchar2(1);
1006 BEGIN
1007
1008 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1009 aso_debug_pub.add('ASO_SECURITY_INT: ****** Start of Add_SalesRep_QuoteCreator API ******', 1, 'Y');
1010 END IF;
1011
1012 -- Standard Start of API savepoint
1013 SAVEPOINT Add_SalesRep_QuoteCreator_INT;
1014
1015 -- Initialize message list if p_init_msg_list is set to TRUE.
1016 IF FND_API.To_Boolean(p_init_msg_list) THEN
1017 FND_Msg_Pub.initialize;
1018 END IF;
1019
1020 -- Initialize API return status to success
1021 x_return_status := FND_API.G_RET_STS_SUCCESS;
1022
1023 -- API body
1024
1025 IF p_qte_header_rec.resource_id IS NOT NULL AND p_qte_header_rec.resource_id <> FND_API.G_MISS_NUM THEN
1026
1027 l_qte_access_rec.QUOTE_NUMBER := p_qte_header_rec.quote_number;
1028 l_qte_access_rec.RESOURCE_ID := p_qte_header_rec.resource_id;
1029 l_qte_access_rec.RESOURCE_GRP_ID := p_qte_header_rec.resource_grp_id;
1030 l_qte_access_rec.CREATED_BY := G_USER_ID;
1031 l_qte_access_rec.CREATION_DATE := SYSDATE;
1032 l_qte_access_rec.LAST_UPDATED_BY := G_USER_ID;
1033 l_qte_access_rec.LAST_UPDATE_LOGIN := G_LOGIN_ID;
1034 l_qte_access_rec.LAST_UPDATE_DATE := SYSDATE;
1035 l_qte_access_rec.REQUEST_ID := p_qte_header_rec.request_id;
1036 l_qte_access_rec.PROGRAM_APPLICATION_ID := p_qte_header_rec.program_application_id;
1037 l_qte_access_rec.PROGRAM_ID := p_qte_header_rec.program_id;
1038 l_qte_access_rec.PROGRAM_UPDATE_DATE := p_qte_header_rec.program_update_date;
1039 l_qte_access_rec.UPDATE_ACCESS_FLAG := 'Y';
1040 l_qte_access_rec.KEEP_FLAG := 'N';
1041 l_qte_access_rec.batch_price_flag := p_qte_header_rec.batch_price_flag;
1042
1043 --bug 5131904
1044 OPEN C_get_resource_role (p_qte_header_rec.resource_id);
1045 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1046 aso_debug_pub.add('ASO_SECURITY_INT: Add_SalesRep_QuoteCreator:trying to get role from resource ', 1, 'N');
1047 END IF;
1048
1049 FETCH C_get_resource_role INTO l_qte_access_rec.ROLE_ID;
1050
1051 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1052 aso_debug_pub.add('ASO_SECURITY_INT: Add_SalesRep_QuoteCreator: l_qte_access_rec.ROLE_ID: '|| l_qte_access_rec.ROLE_ID, 1, 'N');
1053 END IF;
1054
1055 CLOSE C_get_resource_role;
1056
1057 -- if the resource does not have a group, get it from the QOT Params
1058 IF (l_qte_access_rec.ROLE_ID IS NULL OR l_qte_access_rec.ROLE_ID = FND_API.G_MISS_NUM) THEN
1059 l_qte_access_rec.ROLE_ID := aso_utility_pvt.get_ou_attribute_value(aso_utility_pvt.G_DEFAULT_SALES_ROLE,p_qte_header_rec.org_id);
1060 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1061 aso_debug_pub.add('ASO_SECURITY_INT: Add_SalesRep_QuoteCreator: getting role from QOT PARAMS ', 1, 'N');
1062 aso_debug_pub.add('ASO_SECURITY_INT: Add_SalesRep_QuoteCreator: l_qte_access_rec.ROLE_ID: '|| l_qte_access_rec.ROLE_ID, 1, 'N');
1063 END IF;
1064 END IF;
1065
1066
1067 l_qte_access_tbl(1) := l_qte_access_rec;
1068
1069 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1070 aso_debug_pub.add('ASO_SECURITY_INT: Add_SalesRep_QuoteCreator: before Add_Resource: p_qte_header_rec.resource_id: ' || p_qte_header_rec.resource_id, 1, 'N');
1071 aso_debug_pub.add('ASO_SECURITY_INT: Add_SalesRep_QuoteCreator: before Add_resource: p_qte_header_rec.resource_grp_id: ' || p_qte_header_rec.resource_grp_id, 1, 'N');
1072
1073 END IF;
1074
1075 FOR c_user_resource_rec IN C_user_resource_id(G_USER_ID) LOOP
1076 IF c_user_resource_rec.resource_id <> p_qte_header_rec.resource_id AND c_user_resource_rec.resource_id IS NOT NULL THEN
1077
1078 l_qte_access_tbl(2) := l_qte_access_rec;
1079 l_qte_access_tbl(2).RESOURCE_ID := c_user_resource_rec.resource_id;
1080
1081 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1082 aso_debug_pub.add('ASO_SECURITY_INT: Add_SalesRep_QuoteCreator: Before calling Get_Profile_Obsolete_Status', 1, 'N');
1083 END IF;
1084
1085 l_obsolete_status := aso_utility_pvt.Get_Profile_Obsolete_Status(p_profile_name => 'AST_DEFAULT_ROLE_AND_GROUP',
1086 p_application_id => 521);
1087
1088 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1089 aso_debug_pub.add('After calling Get_Profile_Obsolete_Status: l_obsolete_status: ' || l_obsolete_status, 1, 'N');
1090 END IF;
1091
1092 if l_obsolete_status = 'T' then
1093
1094 c_profile_value := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
1095
1096 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1097 aso_debug_pub.add('c_profile_value: ' || c_profile_value, 1, 'N');
1098 END IF;
1099
1100 l_qte_access_tbl(2).RESOURCE_GRP_ID := SUBSTR(c_profile_value, 1, INSTR(c_profile_value,'(')-1);
1101
1102 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1103 aso_debug_pub.add('l_qte_access_tbl(2).RESOURCE_GRP_ID: ' || l_qte_access_tbl(2).RESOURCE_GRP_ID, 1, 'N');
1104 END IF;
1105
1106 if l_qte_access_tbl(2).RESOURCE_GRP_ID is null then
1107
1108 c_profile_value := FND_PROFILE.Value_Specific( 'AST_DEFAULT_GROUP', G_USER_ID, NULL, 521);
1109
1110 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1111 aso_debug_pub.add('c_profile_value: ' || c_profile_value, 1, 'N');
1112 END IF;
1113
1114 l_qte_access_tbl(2).RESOURCE_GRP_ID := to_number(c_profile_value);
1115
1116 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1117 aso_debug_pub.add('l_qte_access_tbl(2).RESOURCE_GRP_ID: ' || l_qte_access_tbl(2).RESOURCE_GRP_ID, 1, 'N');
1118 END IF;
1119
1120 end if;
1121
1122 else
1123
1124 c_profile_value := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
1125
1126 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1127 aso_debug_pub.add('c_profile_value: ' || c_profile_value, 1, 'N');
1128 END IF;
1129
1130 l_qte_access_tbl(2).RESOURCE_GRP_ID := SUBSTR(c_profile_value, 1, INSTR(c_profile_value,'(')-1);
1131
1132 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1133 aso_debug_pub.add('l_qte_access_tbl(2).RESOURCE_GRP_ID: ' || l_qte_access_tbl(2).RESOURCE_GRP_ID, 1, 'N');
1134 END IF;
1135
1136 if l_qte_access_tbl(2).RESOURCE_GRP_ID is null then
1137
1138 c_profile_value := FND_PROFILE.Value_Specific( 'AST_DEFAULT_ROLE_AND_GROUP', G_USER_ID, NULL, 521);
1139
1140 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1141 aso_debug_pub.add('c_profile_value: ' || c_profile_value, 1, 'N');
1142 END IF;
1143
1144 l_qte_access_tbl(2).RESOURCE_GRP_ID := substr(c_profile_value, instr(c_profile_value,':', -1) + 1, length(c_profile_value));
1145
1146 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1147 aso_debug_pub.add('l_qte_access_tbl(2).RESOURCE_GRP_ID: ' || l_qte_access_tbl(2).RESOURCE_GRP_ID, 1, 'N');
1148 END IF;
1149
1150 end if;
1151
1152 end if;
1153
1154
1155 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1156 aso_debug_pub.add('ASO_SECURITY_INT: Add_SalesRep_QuoteCreator: before Add_Resource: c_user_resource_rec.resource_id: ' || c_user_resource_rec.resource_id, 1, 'N');
1157 aso_debug_pub.add('ASO_SECURITY_INT: Add_SalesRep_QuoteCreator: before Add_Resource: l_qte_access_tbl(2).RESOURCE_GRP_ID: ' || l_qte_access_tbl(2).RESOURCE_GRP_ID, 1, 'N');
1158 END IF;
1159
1160 END IF;
1161
1162 END LOOP;
1163
1164 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1165 aso_debug_pub.add('ASO_SECURITY_INT: Add_SalesRep_QuoteCreator: before Add_Resource', 1, 'Y');
1166 END IF;
1167
1168 ASO_SECURITY_INT.Add_Resource(
1169 P_INIT_MSG_LIST => FND_API.G_FALSE,
1170 P_COMMIT => FND_API.G_FALSE,
1171 P_Qte_Access_tbl => l_qte_access_tbl,
1172 X_Qte_Access_tbl => lx_qte_access_tbl,
1173 X_RETURN_STATUS => x_return_status,
1174 X_msg_count => X_msg_count,
1175 X_msg_data => X_msg_data
1176 );
1177
1178 l_qte_access_tbl := lx_qte_access_tbl;
1179
1180 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1181 aso_debug_pub.add('ASO_SECURITY_INT: Add_SalesRep_QuoteCreator: after Add_Resource: x_return_status: ' || x_return_status, 1, 'Y');
1182 END IF;
1183
1184 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1185 RAISE FND_API.G_EXC_ERROR;
1186 END IF;
1187
1188 END IF;
1189
1190 -- End of API body
1191
1192 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1193 aso_debug_pub.add('ASO_SECURITY_INT: Add_SalesRep_QuoteCreator: End of API body', 1, 'Y');
1194 END IF;
1195 -- Standard check of p_commit.
1196 IF FND_API.To_Boolean(p_commit) THEN
1197 COMMIT WORK;
1198 END IF;
1199
1200 -- Standard call to get message count and if count is 1, get message info.
1201 FND_Msg_Pub.Count_And_Get(
1202 p_encoded => FND_API.G_FALSE,
1203 p_count => x_msg_count ,
1204 p_data => x_msg_data
1205 );
1206
1207 EXCEPTION
1208 WHEN FND_API.G_EXC_ERROR THEN
1209 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1210 P_API_NAME => L_API_NAME,
1211 P_PKG_NAME => G_PKG_NAME,
1212 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
1213 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT,
1214 P_SQLCODE => SQLCODE,
1215 P_SQLERRM => SQLERRM,
1216 X_MSG_COUNT => X_MSG_COUNT,
1217 X_MSG_DATA => X_MSG_DATA,
1218 X_RETURN_STATUS => X_RETURN_STATUS
1219 );
1220
1221 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1222 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1223 P_API_NAME => L_API_NAME,
1224 P_PKG_NAME => G_PKG_NAME,
1225 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1226 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT,
1227 P_SQLCODE => SQLCODE,
1228 P_SQLERRM => SQLERRM,
1229 X_MSG_COUNT => X_MSG_COUNT,
1230 X_MSG_DATA => X_MSG_DATA,
1231 X_RETURN_STATUS => X_RETURN_STATUS
1232 );
1233
1234 WHEN OTHERS THEN
1235 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1236 P_API_NAME => L_API_NAME,
1237 P_PKG_NAME => G_PKG_NAME,
1238 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
1239 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT,
1240 P_SQLCODE => SQLCODE,
1241 P_SQLERRM => SQLERRM,
1242 X_MSG_COUNT => X_MSG_COUNT,
1243 X_MSG_DATA => X_MSG_DATA,
1244 X_RETURN_STATUS => X_RETURN_STATUS
1245 );
1246
1247 END Add_SalesRep_QuoteCreator;
1248
1249
1250 END ASO_SECURITY_INT;