[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_ASSIGN_PUB
Source
1 PACKAGE BODY CN_QUOTA_ASSIGN_PUB AS
2 /*$Header: cnpqtasb.pls 120.4 2005/11/08 03:23:47 kjayapau noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_QUOTA_ASSIGN_PUB';
5 G_LAST_UPDATE_DATE DATE := Sysdate;
6 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
7 G_CREATION_DATE DATE := Sysdate;
8 G_CREATED_BY NUMBER := fnd_global.user_id;
9 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
10
11 -- validate_input_record
12 procedure validate_input_record(p_quota_assign_rec quota_assign_rec_type) IS
13 BEGIN
14 if (p_quota_assign_rec.comp_plan_name is NULL OR
15 p_quota_assign_rec.comp_plan_name = fnd_api.g_miss_char OR
16 p_quota_assign_rec.quota_name IS NULL OR
17 p_quota_assign_rec.quota_name = fnd_api.g_miss_char) OR
18 p_quota_assign_rec.ORG_ID IS NULL OR
19 p_quota_assign_rec.org_id = fnd_api.g_miss_num
20 then
21 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
22 FND_MESSAGE.SET_NAME ('CN' , 'CN_REQ_PAR_MISSING');
23 FND_MSG_PUB.Add;
24 END IF;
25 RAISE FND_API.G_EXC_ERROR ;
26 end if;
27 END;
28
29 -- validate_cp_name
30 function validate_cp_name(p_cp_name varchar2,p_org_id number) RETURN number IS
31 l_cp_id number := 0;
32 CURSOR l_cp_id_csr(p_plan_name varchar2) IS
33 SELECT comp_plan_id
34 FROM cn_comp_plans
35 WHERE name = p_plan_name
36 and org_id = p_org_id;
37 BEGIN
38 OPEN l_cp_id_csr(p_cp_name);
39 FETCH l_cp_id_csr INTO l_cp_id;
40 CLOSE l_cp_id_csr;
41 if (l_cp_id IS NULL OR l_cp_id = 0) then
42 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
43 FND_MESSAGE.SET_NAME ('CN' , 'CN_CP_NOT_EXIST');
44 FND_MESSAGE.SET_token('CP_NAME', p_cp_name);
45 FND_MSG_PUB.Add;
46 END IF;
47 RAISE FND_API.G_EXC_ERROR ;
48 end if;
49 RETURN l_cp_id;
50 END;
51
52 -- validate_cp_name
53 function validate_pe_name(p_pe_name varchar2,p_org_id number) RETURN number IS
54 l_pe_id number := 0;
55 CURSOR l_pe_id_csr(p_element_name varchar2) IS
56 SELECT quota_id
57 FROM cn_quotas_v
58 WHERE name = p_element_name and org_id = p_org_id;
59 BEGIN
60 OPEN l_pe_id_csr(p_pe_name);
61 FETCH l_pe_id_csr INTO l_pe_id;
62 CLOSE l_pe_id_csr;
63 if (l_pe_id IS NULL OR l_pe_id = 0) then
64 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
65 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_QUOTA');
66 FND_MSG_PUB.Add;
67 END IF;
68 RAISE FND_API.G_EXC_ERROR ;
69 end if;
70 RETURN l_pe_id;
71 END;
72
73 -- Start of comments
74 -- API name : Create_Quota_Assign
75 -- Type : Public
76 -- Function :
77 -- Pre-reqs : None.
78 -- Parameters :
79 -- IN : p_api_version IN NUMBER Required
80 -- p_init_msg_list IN VARCHAR2 Optional
81 -- Default = FND_API.G_FALSE
82 -- p_commit IN VARCHAR2 Optional
83 -- Default = FND_API.G_FALSE
84 -- p_validation_level IN NUMBER Optional
85 -- Default = FND_API.G_VALID_LEVEL_FULL
86 -- p_quota_assign_rec IN quota_assign_rec_type
87 -- OUT : x_return_status OUT VARCHAR2(1)
88 -- x_msg_count OUT NUMBER
89 -- x_msg_data OUT VARCHAR2(2000)
90 -- Version : Current version 1.0
91 -- Initial version 1.0
92 --
93 -- Notes : None
94 --
95 -- End of comments
96 PROCEDURE Create_Quota_Assign
97 (p_api_version IN NUMBER ,
98 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
99 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
100 p_validation_level IN NUMBER :=
101 FND_API.G_VALID_LEVEL_FULL ,
102 p_quota_assign_rec IN quota_assign_rec_type ,
103 x_return_status OUT NOCOPY VARCHAR2 ,
104 x_msg_count OUT NOCOPY NUMBER ,
105 x_msg_data OUT NOCOPY VARCHAR2 ) IS
106 l_api_name CONSTANT VARCHAR2(30)
107 := 'Create_Quota_Assign';
108 l_api_version CONSTANT NUMBER := 1.0;
109 l_quota_assign_rec quota_assign_rec_type := p_quota_assign_rec;
110 l_comp_plan_id number := 0;
111 l_quota_id number := 0;
112 l_pvt_quota_assign_rec cn_quota_assign_pvt.quota_assign_rec_type;
113 l_return_status varchar2(1);
114 l_msg_count number;
115 l_msg_data varchar2(2000);
116
117 l_org_id NUMBER;
118 l_status VARCHAR2(1);
119
120 BEGIN
121 -- Standard Start of API savepoint
122 SAVEPOINT Create_Quota_Assign_Pub;
123 -- Standard call to check for call compatibility.
124 IF NOT FND_API.Compatible_API_Call
125 (l_api_version ,
126 p_api_version ,
127 l_api_name ,
128 G_PKG_NAME )
129 THEN
130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
131 END IF;
132 -- Initialize message list if p_init_msg_list is set to TRUE.
133 IF FND_API.to_Boolean( p_init_msg_list ) THEN
134 FND_MSG_PUB.initialize;
135 END IF;
136 -- Initialize API return status to success
137 x_return_status := FND_API.G_RET_STS_SUCCESS;
138 -- API body
139 -- Validate and Default OrgId
140 l_org_id := l_quota_assign_rec.org_id;
141 mo_global.validate_orgid_pub_api(org_id => l_org_id, status => l_status);
142
143 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
144 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
145 'cn.plsql.CN_QUOTA_ASSIGN_PUB.Create_Quota_Assign.org_validate',
146 'Validated org_id = ' || l_org_id || ' status = '|| l_status);
147 end if;
148 l_quota_assign_rec.org_id := l_org_id;
149
150 -- Validate the input first
151 validate_input_record(l_quota_assign_rec);
152 l_comp_plan_id := validate_cp_name(l_quota_assign_rec.comp_plan_name,l_org_id);
153 l_quota_id := validate_pe_name(l_quota_assign_rec.quota_name,l_org_id);
154
155 -- Now call the private api
156 l_pvt_quota_assign_rec.comp_plan_id := l_comp_plan_id;
157 l_pvt_quota_assign_rec.quota_id := l_quota_id;
158 l_pvt_quota_assign_rec.quota_sequence := l_quota_assign_rec.quota_sequence;
159 l_pvt_quota_assign_rec.org_id := l_org_id;
160
161 IF (l_pvt_quota_assign_rec.quota_sequence IS NULL OR l_pvt_quota_assign_rec.quota_sequence = FND_API.G_MISS_NUM) THEN
162 l_pvt_quota_assign_rec.quota_sequence :=1 ;
163 END IF;
164
165
166 cn_quota_assign_pvt.create_quota_assign
167 ( p_api_version =>1.0,
168 p_init_msg_list => FND_API.G_FALSE,
169 p_commit => FND_API.G_FALSE,
170 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
171 p_quota_assign => l_pvt_quota_assign_rec,
172 x_return_status => l_return_status,
173 x_msg_count => l_msg_count,
174 x_msg_data => l_msg_data);
175
176 x_return_status := l_return_status;
177 x_msg_count := l_msg_count;
178 x_msg_data := l_msg_data;
179
180 -- if the private API returned anything other than S just pass on
181 -- the errors and exit
182 if (l_return_status = 'E') then
183 RAISE FND_API.G_EXC_ERROR;
184 ELSIF (l_return_status = 'U') THEN
185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
186 end if;
187
188 -- End of API body.
189 -- Standard check of p_commit.
190 IF FND_API.To_Boolean( p_commit ) THEN
191 COMMIT WORK;
192 END IF;
193 -- Standard call to get message count and if count is 1, get message info.
194 FND_MSG_PUB.Count_And_Get
195 (p_count => x_msg_count ,
196 p_data => x_msg_data ,
197 p_encoded => FND_API.G_FALSE );
198 EXCEPTION
199 WHEN FND_API.G_EXC_ERROR THEN
200 ROLLBACK TO Create_Quota_Assign_Pub;
201 x_return_status := FND_API.G_RET_STS_ERROR ;
202 FND_MSG_PUB.Count_And_Get
203 (p_count => x_msg_count ,
204 p_data => x_msg_data ,
205 p_encoded => FND_API.G_FALSE );
206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
207 ROLLBACK TO Create_Quota_Assign_Pub;
208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
209 FND_MSG_PUB.Count_And_Get
210 (p_count => x_msg_count ,
211 p_data => x_msg_data ,
212 p_encoded => FND_API.G_FALSE );
213 WHEN OTHERS THEN
214 ROLLBACK TO Create_Quota_Assign_Pub;
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
216 IF FND_MSG_PUB.Check_Msg_Level
217 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
218 THEN
219 FND_MSG_PUB.Add_Exc_Msg
220 (G_PKG_NAME ,
221 l_api_name );
222 END IF;
223 FND_MSG_PUB.Count_And_Get
224 (p_count => x_msg_count ,
225 p_data => x_msg_data ,
226 p_encoded => FND_API.G_FALSE );
227 END Create_Quota_Assign;
228
229 -- Start of comments
230 -- API name : Update_Quota_Assign
231 -- Type : Public
232 -- Function :
233 -- Pre-reqs : None.
234 -- Parameters :
235 -- IN : p_api_version IN NUMBER Required
236 -- p_init_msg_list IN VARCHAR2 Optional
237 -- Default = FND_API.G_FALSE
238 -- p_commit IN VARCHAR2 Optional
239 -- Default = FND_API.G_FALSE
240 -- p_validation_level IN NUMBER Optional
241 -- Default = FND_API.G_VALID_LEVEL_FULL
242 -- p_quota_assign_rec IN quota_assign_rec_type
243 -- OUT : x_return_status OUT VARCHAR2(1)
244 -- x_msg_count OUT NUMBER
245 -- x_msg_data OUT VARCHAR2(2000)
246 -- Version : Current version 1.0
247 -- Initial version 1.0
248 --
249 -- Notes : Note text
250 --
251 -- End of comments
252 PROCEDURE Update_Quota_Assign
253 (p_api_version IN NUMBER ,
254 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
255 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
256 p_validation_level IN NUMBER :=
257 FND_API.G_VALID_LEVEL_FULL ,
258 p_quota_assign_rec IN quota_assign_rec_type ,
259 x_return_status OUT NOCOPY VARCHAR2 ,
260 x_msg_count OUT NOCOPY NUMBER ,
261 x_msg_data OUT NOCOPY VARCHAR2 ) IS
262 l_api_name CONSTANT VARCHAR2(30)
263 := 'Update_Quota_Assign';
264 l_api_version CONSTANT NUMBER := 1.0;
265 l_quota_assign_rec quota_assign_rec_type := p_quota_assign_rec;
266 /*l_new_quota_assign_rec quota_assign_rec_type := p_new_quota_assign_rec;*/
267 l_comp_plan_id number := 0;
268 l_quota_id number := 0;
269 l_pvt_quota_assign_rec cn_quota_assign_pvt.quota_assign_rec_type;
270 l_return_status varchar2(1);
271 l_msg_count number;
272 l_msg_data varchar2(2000);
273 l_quota_assign_id number := 0;
274 l_object_version_number number := 0;
275 CURSOR l_quota_assign_id_csr(p_cp_id number, p_pe_id number)IS
276 SELECT quota_assign_id, object_version_number, org_id
277 FROM cn_quota_assigns
278 WHERE comp_plan_id = p_cp_id
279 AND quota_id = p_pe_id;
280
281 l_org_id NUMBER;
282 l_status VARCHAR2(1);
283 old_org_id NUMBER;
284 old_quota_id NUMBER;
285
286 BEGIN
287 -- Standard Start of API savepoint
288 SAVEPOINT Update_Quota_Assign_Pub;
289 -- Standard call to check for call compatibility.
290 IF NOT FND_API.Compatible_API_Call
291 (l_api_version ,
292 p_api_version ,
293 l_api_name ,
294 G_PKG_NAME )
295 THEN
296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
297 END IF;
298 -- Initialize message list if p_init_msg_list is set to TRUE.
299 IF FND_API.to_Boolean( p_init_msg_list ) THEN
300 FND_MSG_PUB.initialize;
301 END IF;
302 -- Initialize API return status to success
303 x_return_status := FND_API.G_RET_STS_SUCCESS;
304 -- API body
305
306 -- Validate and Default OrgId
307 l_org_id := l_quota_assign_rec.org_id;
308 mo_global.validate_orgid_pub_api(org_id => l_org_id, status => l_status);
309
310 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
311 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
312 'cn.plsql.CN_QUOTA_ASSIGN_PUB.Update_Quota_Assign.org_validate',
313 'Validated org_id = ' || l_org_id || ' status = '|| l_status);
314 end if;
315
316 l_quota_assign_rec.org_id := l_org_id;
317
318 -- Validate the input first
319 validate_input_record(l_quota_assign_rec);
320
321 if (l_quota_assign_rec.old_quota_name is NULL OR
322 l_quota_assign_rec.old_quota_name = fnd_api.g_miss_char) then
323
324 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
325 FND_MESSAGE.SET_NAME ('CN' , 'CN_REQ_PAR_MISSING');
326 FND_MSG_PUB.Add;
327 END IF;
328 RAISE FND_API.G_EXC_ERROR ;
329 end if;
330
331
332 l_comp_plan_id := validate_cp_name(l_quota_assign_rec.comp_plan_name, l_quota_assign_rec.org_id);
333 l_quota_id := validate_pe_name(l_quota_assign_rec.quota_name, l_quota_assign_rec.org_id);
334 old_quota_id := validate_pe_name(l_quota_assign_rec.old_quota_name, l_quota_assign_rec.org_id);
335
336
337 -- get the quota assignment id from the old rec
338 OPEN l_quota_assign_id_csr(l_comp_plan_id, old_quota_id);
339 FETCH l_quota_assign_id_csr INTO l_quota_assign_id , l_object_version_number, old_org_id;
340 CLOSE l_quota_assign_id_csr;
341
342 -- Org id validation
343
344 if (l_org_id <> old_org_id) then
345
346 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
347 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
348 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
349 'cn.plsql.CN_QUOTA_ASSIGN_PUB.Update_Quota_Assign.error',
350 true);
351 end if;
352
353 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
354 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
355 FND_MSG_PUB.Add;
356 END IF;
357
358 RAISE FND_API.G_EXC_ERROR ;
359 end if;
360
361 -- now get the quota id for the new rec if necessary
362 /* if (l_quota_assign_rec.quota_name <> fnd_api.g_miss_char OR
363 l_quota_assign_rec.quota_name IS NOT NULL) then
364 l_quota_id := validate_pe_name(l_quota_assign_rec.quota_name);
365 end if;*/
366
367 if (l_quota_assign_id is null or l_quota_assign_id = 0) then
368 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
369 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_PLAN_ASSIGNS');
370 FND_MSG_PUB.Add;
371 END IF;
372 RAISE FND_API.G_EXC_ERROR ;
373 end if;
374
375 -- Now call the private api
376 l_pvt_quota_assign_rec.comp_plan_id := l_comp_plan_id;
377 l_pvt_quota_assign_rec.quota_id := l_quota_id;
378 l_pvt_quota_assign_rec.quota_sequence := l_quota_assign_rec.quota_sequence;
379 IF (l_pvt_quota_assign_rec.quota_sequence IS NULL OR l_pvt_quota_assign_rec.quota_sequence = FND_API.G_MISS_NUM) THEN
380 l_pvt_quota_assign_rec.quota_sequence := 1;
381 END IF;
382
383 l_pvt_quota_assign_rec.object_version_number := l_object_version_number;
384 l_pvt_quota_assign_rec.quota_assign_id := l_quota_assign_id;
385 l_pvt_quota_assign_rec.org_id := l_quota_assign_rec.org_id;
386
387 cn_quota_assign_pvt.update_quota_assign
388 ( p_api_version =>1.0,
389 p_init_msg_list => FND_API.G_FALSE,
390 p_commit => FND_API.G_FALSE,
391 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
392 p_quota_assign => l_pvt_quota_assign_rec,
393 x_return_status => l_return_status,
394 x_msg_count => l_msg_count,
395 x_msg_data => l_msg_data);
396
397 x_return_status := l_return_status;
398 x_msg_count := l_msg_count;
399 x_msg_data := l_msg_data;
400
401 -- if the private API returned anything other than S just pass on
402 -- the errors and exit
403 if (l_return_status = 'E') then
404 RAISE FND_API.G_EXC_ERROR;
405 ELSIF (l_return_status = 'U') THEN
406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
407 end if;
408
409 -- End of API body.
410 -- Standard check of p_commit.
411 IF FND_API.To_Boolean( p_commit ) THEN
412 COMMIT WORK;
413 END IF;
414 -- Standard call to get message count and if count is 1, get message info.
415 FND_MSG_PUB.Count_And_Get
416 (p_count => x_msg_count ,
417 p_data => x_msg_data ,
418 p_encoded => FND_API.G_FALSE );
419 EXCEPTION
420 WHEN FND_API.G_EXC_ERROR THEN
421 ROLLBACK TO Update_Quota_Assign_Pub;
422 x_return_status := FND_API.G_RET_STS_ERROR ;
423 FND_MSG_PUB.Count_And_Get
424 (p_count => x_msg_count ,
425 p_data => x_msg_data ,
426 p_encoded => FND_API.G_FALSE );
427 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
428 ROLLBACK TO Update_Quota_Assign_Pub;
429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
430 FND_MSG_PUB.Count_And_Get
431 (p_count => x_msg_count ,
432 p_data => x_msg_data ,
433 p_encoded => FND_API.G_FALSE );
434 WHEN OTHERS THEN
435 ROLLBACK TO Update_Quota_Assign_Pub;
436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
437 IF FND_MSG_PUB.Check_Msg_Level
438 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
439 THEN
440 FND_MSG_PUB.Add_Exc_Msg
441 (G_PKG_NAME ,
442 l_api_name );
443 END IF;
444 FND_MSG_PUB.Count_And_Get
445 (p_count => x_msg_count ,
446 p_data => x_msg_data ,
447 p_encoded => FND_API.G_FALSE );
448 END Update_Quota_Assign;
449
450 -- Start of comments
451 -- API name : Delete_Quota_Assign
452 -- Type : Public
453 -- Function :
454 -- Pre-reqs : None.
455 -- Parameters :
456 -- IN : p_api_version IN NUMBER Required
457 -- p_init_msg_list IN VARCHAR2 Optional
458 -- Default = FND_API.G_FALSE
459 -- p_commit IN VARCHAR2 Optional
460 -- Default = FND_API.G_FALSE
461 -- p_validation_level IN NUMBER Optional
462 -- Default = FND_API.G_VALID_LEVEL_FULL
463 -- p_quota_assign_rec IN quota_assign_rec_type
464 -- OUT : x_return_status OUT VARCHAR2(1)
465 -- x_msg_count OUT NUMBER
466 -- x_msg_data OUT VARCHAR2(2000)
467 -- Version : Current version 1.0
468 -- Initial version 1.0
469 --
470 -- Notes : Note text
471 --
472 -- End of comments
473 PROCEDURE Delete_Quota_Assign
474 (p_api_version IN NUMBER ,
475 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
476 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
477 p_validation_level IN NUMBER :=
478 FND_API.G_VALID_LEVEL_FULL ,
479 p_quota_assign_rec IN quota_assign_rec_type ,
480 x_return_status OUT NOCOPY VARCHAR2 ,
481 x_msg_count OUT NOCOPY NUMBER ,
482 x_msg_data OUT NOCOPY VARCHAR2 ) IS
483 l_api_name CONSTANT VARCHAR2(30)
484 := 'Delete_Quota_Assign';
485 l_api_version CONSTANT NUMBER := 1.0;
486 l_quota_assign_rec quota_assign_rec_type := p_quota_assign_rec;
487 l_comp_plan_id number := 0;
488 l_quota_id number := 0;
489 l_pvt_quota_assign_rec cn_quota_assign_pvt.quota_assign_rec_type;
490 l_return_status varchar2(1);
491 l_msg_count number;
492 l_msg_data varchar2(2000);
493 l_quota_assign_id number := 0;
494 l_object_version_number number := 0;
495 CURSOR l_quota_assign_id_csr(p_cp_id number, p_pe_id number)IS
496 SELECT quota_assign_id, object_version_number, org_id
497 FROM cn_quota_assigns
498 WHERE comp_plan_id = p_cp_id
499 AND quota_id = p_pe_id;
500
501 l_org_id NUMBER;
502 l_status VARCHAR2(1);
503 old_org_id NUMBER;
504
505 BEGIN
506 -- Standard Start of API savepoint
507 SAVEPOINT Delete_Quota_Assign_Pub;
508 -- Standard call to check for call compatibility.
509 IF NOT FND_API.Compatible_API_Call
510 (l_api_version ,
511 p_api_version ,
512 l_api_name ,
513 G_PKG_NAME )
514 THEN
515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
516 END IF;
517 -- Initialize message list if p_init_msg_list is set to TRUE.
518 IF FND_API.to_Boolean( p_init_msg_list ) THEN
519 FND_MSG_PUB.initialize;
520 END IF;
521 -- Initialize API return status to success
522 x_return_status := FND_API.G_RET_STS_SUCCESS;
523 -- API body
524 -- Validate and Default OrgId
525 l_org_id := l_quota_assign_rec.org_id;
526 mo_global.validate_orgid_pub_api(org_id => l_org_id, status => l_status);
527
528 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
529 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
530 'cn.plsql.CN_QUOTA_ASSIGN_PUB.Delete_Quota_Assign.org_validate',
531 'Validated org_id = ' || l_org_id || ' status = '|| l_status);
532 end if;
533
534 l_quota_assign_rec.org_id := l_org_id;
535
536
537 -- Validate the input first
538 validate_input_record(l_quota_assign_rec);
539 l_comp_plan_id := validate_cp_name(l_quota_assign_rec.comp_plan_name,l_quota_assign_rec.org_id);
540 l_quota_id := validate_pe_name(l_quota_assign_rec.quota_name,l_quota_assign_rec.org_id);
541
542 -- get the quota assignment id
543 OPEN l_quota_assign_id_csr(l_comp_plan_id, l_quota_id);
544 FETCH l_quota_assign_id_csr INTO l_quota_assign_id , l_object_version_number, old_org_id;
545 CLOSE l_quota_assign_id_csr;
546
547 -- Org id validation
548
549 if (l_org_id <> old_org_id) then
550
551 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
552 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
553 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
554 'cn.plsql.CN_QUOTA_ASSIGN_PUB.Delete_Quota_Assign.error',
555 true);
556 end if;
557
558 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
559 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
560 FND_MSG_PUB.Add;
561 END IF;
562
563 RAISE FND_API.G_EXC_ERROR ;
564 end if;
565
566 if (l_quota_assign_id is null or l_quota_assign_id = 0) then
567 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
568 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_PLAN_ASSIGNS');
569 FND_MSG_PUB.Add;
570 END IF;
571 RAISE FND_API.G_EXC_ERROR ;
572 end if;
573
574 -- Now call the private api
575 l_pvt_quota_assign_rec.comp_plan_id := l_comp_plan_id;
576 l_pvt_quota_assign_rec.quota_id := l_quota_id;
577 l_pvt_quota_assign_rec.quota_sequence := l_quota_assign_rec.quota_sequence;
578 l_pvt_quota_assign_rec.object_version_number := l_object_version_number;
579 l_pvt_quota_assign_rec.quota_assign_id := l_quota_assign_id;
580 l_pvt_quota_assign_rec.org_id := l_quota_assign_rec.org_id;
581
582 cn_quota_assign_pvt.delete_quota_assign
583 ( p_api_version =>1.0,
584 p_init_msg_list => FND_API.G_FALSE,
585 p_commit => FND_API.G_FALSE,
586 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
587 p_quota_assign => l_pvt_quota_assign_rec,
588 x_return_status => l_return_status,
589 x_msg_count => l_msg_count,
590 x_msg_data => l_msg_data);
591
592 x_return_status := l_return_status;
593 x_msg_count := l_msg_count;
594 x_msg_data := l_msg_data;
595
596 -- if the private API returned anything other than S just pass on
597 -- the errors and exit
598 if (l_return_status = 'E') then
599 RAISE FND_API.G_EXC_ERROR;
600 ELSIF (l_return_status = 'U') THEN
601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
602 end if;
603
604 -- End of API body.
605 -- Standard check of p_commit.
606 IF FND_API.To_Boolean( p_commit ) THEN
607 COMMIT WORK;
608 END IF;
609 -- Standard call to get message count and if count is 1, get message info.
610 FND_MSG_PUB.Count_And_Get
611 (p_count => x_msg_count ,
612 p_data => x_msg_data ,
613 p_encoded => FND_API.G_FALSE );
614 EXCEPTION
615 WHEN FND_API.G_EXC_ERROR THEN
616 ROLLBACK TO Delete_Quota_Assign_Pub;
617 x_return_status := FND_API.G_RET_STS_ERROR ;
618 FND_MSG_PUB.Count_And_Get
619 (p_count => x_msg_count ,
620 p_data => x_msg_data ,
621 p_encoded => FND_API.G_FALSE );
622 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
623 ROLLBACK TO Delete_Quota_Assign_Pub;
624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
625 FND_MSG_PUB.Count_And_Get
626 (p_count => x_msg_count ,
627 p_data => x_msg_data ,
628 p_encoded => FND_API.G_FALSE );
629 WHEN OTHERS THEN
630 ROLLBACK TO Delete_Quota_Assign_Pub;
631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
632 IF FND_MSG_PUB.Check_Msg_Level
633 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
634 THEN
635 FND_MSG_PUB.Add_Exc_Msg
636 (G_PKG_NAME ,
637 l_api_name );
638 END IF;
639 FND_MSG_PUB.Count_And_Get
640 (p_count => x_msg_count ,
641 p_data => x_msg_data ,
642 p_encoded => FND_API.G_FALSE );
643 END Delete_Quota_Assign;
644
645 END CN_QUOTA_ASSIGN_PUB;