[Home] [Help]
PACKAGE BODY: APPS.GMS_AWARD_DELETE_PKG
Source
1 PACKAGE BODY GMS_AWARD_DELETE_PKG as
2 /* $Header: gmsawdlb.pls 115.14 2003/08/01 09:34:00 lveerubh ship $ */
3
4 -- Awards delete
5 ----------------------------------------------------------------------------------------
6 --Check event exist for any Project
7 ----------------------------------------------------------------------------------------
8
9 FUNCTION check_event_exist(p_award_id IN NUMBER ,
10 p_Err_Code OUT NOCOPY VARCHAR2,
11 p_Err_Stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
12
13 CURSOR event_exist IS
14 SELECT 1 --COUNT(*) bug 2355648
15 FROM gms_event_attribute gea, gms_installments gi
16 WHERE award_id = p_award_id
17 AND gea.installment_id= gi.installment_id
18 AND rownum <=1;
19 l_event_exist number:=0;
20 BEGIN
21 OPEN event_exist;
22 FETCH event_exist INTO l_event_exist;
23 CLOSE event_exist;
24 p_Err_Code := 'S';
25 IF l_event_exist >0 THEN
26 RETURN TRUE;
27 ELSE
28 RETURN FALSE;
29 END IF;
30 EXCEPTION
31 WHEN OTHERS THEN
32 BEGIN
33 p_Err_Code := 'U';
34 p_Err_Stage := 'CHECK_EVENT_EXIST';
35 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
36 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_AWARD_DELETE_PKG: CHECK_EVENT_EXIST');
37 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
38 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
39 RETURN TRUE;
40 -- RAISE FND_API.G_EXC_ERROR;
41 END;
42 END check_event_exist;
43
44
45 ----------------------------------------------------------------------------------------
46 --Following procedure check whether award has funded to any project
47 ----------------------------------------------------------------------------------------
48
49
50 FUNCTION check_funding_exists
51 ( p_award_id IN NUMBER,
52 p_msg_count OUT NOCOPY NUMBER,
53 retcode OUT NOCOPY VARCHAR2,
54 errbuff OUT NOCOPY VARCHAR2
55 ) RETURN BOOLEAN AS
56
57 CURSOR funding_exist IS
58 SELECT 1 --COUNT(*) bug 2355648
59 FROM gms_summary_project_fundings gspf,gms_installments gi
60 WHERE gi.award_id = p_award_Id
61 AND gspf.installment_id=gi.installment_id
62 AND rownum <=1;
63 l_funding_exist NUMBER:=0;
64 BEGIN
65 OPEN funding_exist;
66 FETCH funding_exist INTO l_funding_exist;
67 retcode :='S';
68 CLOSE funding_exist;
69 IF l_funding_exist >0 THEN
70 RETURN TRUE;
71 ELSE
72 RETURN FALSE;
73 END IF;
74 EXCEPTION
75 WHEN OTHERS THEN
76 BEGIN
77 retcode :='U';
78 errbuff := 'Error :'||substr(sqlerrm,1,200);
79 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
80 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_AWARD_DELETE_PKG: CHECK_FUNDING_EXISTS');
81 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
82 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
83 FND_MSG_PUB.add;
84 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
85 p_data => errbuff);
86 RETURN FALSE;
87 END;
88 END check_funding_exists;
89
90 ---------------------------------------------------------------------------------------
91 --Check Baselined budget exist for any Project
92 ---------------------------------------------------------------------------------------
93
94 FUNCTION check_baselined_budget_exist (p_award_id IN NUMBER,
95 p_Err_Code OUT NOCOPY VARCHAR2,
96 p_Err_Stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
97
98 CURSOR budget_exist IS
99 SELECT 1 --COUNT(*) bug 2355648
100 FROM gms_budget_versions
101 WHERE award_id = p_award_id
102 AND budget_status_code IN ('B','S')
103 AND rownum <=1;
104
105 l_budget_exist NUMBER:=0;
106
107 BEGIN
108 OPEN budget_exist;
109 FETCH budget_exist INTO l_budget_exist;
110 CLOSE budget_exist;
111 p_Err_Code :='S';
112 IF l_budget_exist >0 THEN
113 RETURN TRUE;
114 ELSE
115 RETURN FALSE;
116 END IF;
117 EXCEPTION
118 WHEN OTHERS THEN
119 BEGIN
120 p_Err_Code :='U';
121 p_err_stage :=' CHECK_BASELINED_BUDGET_EXIST';
122 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
123 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_AWARD_DELETE_PKG: CHECK_BASELINED_BUDGET_EXIST');
124 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
125 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
126 RETURN TRUE;
127 -- RAISE FND_API.G_EXC_ERROR;
128 END;
129 END check_baselined_budget_exist;
130 ---------------------------------------------------------------------------------------
131 --Check ADL exist for the award
132 ---------------------------------------------------------------------------------------
133
134 FUNCTION check_adl_exist (p_award_id IN NUMBER,
135 p_Err_Code OUT NOCOPY VARCHAR2,
136 p_Err_Stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
137
138 CURSOR adl_exist IS
139 SELECT 1 --COUNT(*) bug 2355648
140 FROM gms_award_distributions
141 WHERE award_id = p_award_id
142 AND rownum <=1;
143
144 l_adl_exist NUMBER:=0;
145
146 BEGIN
147 OPEN adl_exist;
148 FETCH adl_exist INTO l_adl_exist;
149 CLOSE adl_exist;
150 p_Err_Code :='S';
151 IF l_adl_exist >0 THEN
152 RETURN TRUE;
153 ELSE
154 RETURN FALSE;
155 END IF;
156 EXCEPTION
157 WHEN OTHERS THEN
158 BEGIN
159 p_Err_Code :='U';
160 p_Err_Stage :='CHECK_ADL_EXIST';
161 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
162 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_AWARD_DELETE_PKG: CHECK_ADL_EXIST');
163 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
164 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
165 RETURN TRUE;
166 END;
167 END check_adl_exist;
168 --==========================================================
169 --Added below API to fix bug 2355648
170 --==========================================================
171 PROCEDURE DELETE_AWARD_DETAIL( p_award_id IN NUMBER ,
172 p_award_project_id IN NUMBER,
173 p_agreement_id IN NUMBER,
174 p_Award_Template_flag IN VARCHAR2,
175 p_msg_count OUT NOCOPY NUMBER,
176 retcode OUT NOCOPY VARCHAR2,
177 errbuff OUT NOCOPY VARCHAR2 ) IS
178
179 CURSOR GET_PROJECT_ID IS
180 SELECT DISTINCT PROJECT_ID
181 FROM gms_summary_project_fundings gspf,gms_installments gi
182 WHERE gi.award_id = p_award_id --:gms_awards_v.Award_Id bug 2355648
183 AND gspf.installment_id=gi.installment_id;
184
185
186 CURSOR PROJECT_FUNDING IS
187 SELECT GI.INSTALLMENT_ID,
188 FUNDING_AMOUNT,
189 PROJECT_FUNDING_ID,
190 GMS_PROJECT_FUNDING_ID,
191 PROJECT_ID ,
192 TASK_ID
193 FROM GMS_PROJECT_FUNDINGS GPF ,GMS_INSTALLMENTS GI
194 WHERE GPF.INSTALLMENT_ID=GI.INSTALLMENT_ID
195 AND GI.AWARD_ID= p_award_id ; --:GMS_AWARDS_V.AWARD_ID; 2355648
196 l_err_code number;
197 l_app_name varchar2(10);
198 l_err_stage varchar2(240) :='';
199 l_err_stack varchar2(240) :='';
200 l_Funding_Exists boolean;
201 l_draft_budget_exists boolean;
202 Begin
203 FND_MSG_PUB.Initialize;
204 IF p_Award_Template_flag ='DEFERRED' THEN
205 l_Funding_Exists:= GMS_AWARD_DELETE_PKG.CHECK_FUNDING_EXISTS
206 (p_Award_Id =>p_Award_id,
207 p_msg_count =>p_msg_count,
208 RETCODE =>retcode,
209 ERRBUFF =>errbuff);
210 IF retcode <> 'S' THEN
211 RAISE FND_API.G_EXC_ERROR; --bug 2355648
212 END IF;
213
214 IF l_funding_Exists THEN
215
216 l_Draft_Budget_Exists:=GMS_AWARD_DELETE_PKG.CHECK_DRAFT_BUDGET_EXISTS
217 (p_Award_Id =>p_Award_id,
218 p_msg_count =>p_msg_count,
219 RETCODE =>retcode,
220 ERRBUFF =>errbuff);
221
222 IF retcode <> 'S' THEN
223 RAISE FND_API.G_EXC_ERROR; --bug 2355648
224 END IF;
225 IF l_Draft_Budget_Exists THEN
226 FOR PROJECT_INFO IN GET_PROJECT_ID
227 LOOP
228 gms_budget_pub.delete_draft_budget(
229 p_api_version_number => 1.0,
230 p_pm_product_code => 'GMS',
231 x_err_code => l_err_code,
232 x_err_stage => l_err_stage,
233 x_err_stack => l_err_stack,
234 p_project_id => project_info.project_id,
235 p_award_id => p_Award_id,
236 p_budget_type_code =>'AC');
237
238 IF (l_err_code <> 0) then
239 exit;
240 END IF;
241 END LOOP;
242 IF (l_err_code <> 0) then
243 FND_MESSAGE.SET_NAME('GMS','GMS_DELETE_DRAFT_FAIL');
244 FND_MSG_PUB.add;
245 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
246 p_data => errbuff);
247 RAISE FND_API.G_EXC_ERROR; --bug 2355648
248 END IF;
249 END IF; --End if for l_Draft_Budget_Exists
250
251 FOR FUNDING_INFO IN PROJECT_FUNDING
252 LOOP
253 GMS_PROJECT_FUNDINGS_PKG.DELETE_ROW
254 (X_GMS_PROJECT_FUNDING_ID =>FUNDING_INFO.GMS_PROJECT_FUNDING_ID);
255 GMS_SUMM_FUNDING_PKG.DELETE_GMS_SUMMARY_FUNDING
256 ( X_Installment_Id => FUNDING_INFO.INSTALLMENT_ID,
257 X_Project_Id => FUNDING_INFO.PROJECT_ID,
258 X_Task_Id => FUNDING_INFO.TASK_ID,
259 X_Funding_Amount => FUNDING_INFO.FUNDING_AMOUNT,
260 RETCODE => retcode,
261 ERRBUF => errbuff);
262
263
264 IF retcode <> 'S' then
265 FND_MESSAGE.SET_NAME('GMS','GMS_DELETE_FUNDING_FAIL');
266 FND_MSG_PUB.add;
267 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
268 p_data => errbuff);
269 RAISE FND_API.G_EXC_ERROR; --bug 2355648
270 END IF;
271
272 GMS_MULTI_FUNDING.DELETE_AWARD_FUNDING
273 (X_INSTALLMENT_ID => FUNDING_INFO.INSTALLMENT_ID,
274 X_ALLOCATED_AMOUNT => FUNDING_INFO.FUNDING_AMOUNT,
275 X_PROJECT_FUNDING_ID => FUNDING_INFO.PROJECT_FUNDING_ID,
276 X_APP_SHORT_NAME => l_app_name,
277 ERRBUF => errbuff,
278 X_msg_count => p_msg_count,
279 RETCODE => retcode );
280
281 IF retcode <> 'S' then
282 RAISE FND_API.G_EXC_ERROR;
283 END IF;
284 END LOOP;
285 END IF ; --End if for l_funding_Exists
286
287 GMS_MULTI_FUNDING.DELETE_AWARD_PROJECT(
288 X_Award_id => p_award_id ,
289 X_AWARD_PROJECT_ID => p_award_project_id ,
290 X_AGREEMENT_ID => p_agreement_id ,
291 X_MSG_COUNT => p_msg_count ,
292 X_APP_SHORT_NAME => l_app_name,
293 RETCODE => retcode,
294 ERRBUF => errbuff );
295
296
297 IF retcode<> 'S' THEN
298 RAISE FND_API.G_EXC_ERROR;
299 END IF;
300 END IF; -- End if for :gms_awards_v.Award_Template_flag
301 DELETE_AWARD_ALL(p_Award_Id =>p_Award_id,
302 p_msg_count =>p_msg_count,
303 RETCODE =>retcode,
304 ERRBUFF =>errbuff);
305 IF retcode <> 'S' THEN
306 RAISE FND_API.G_EXC_ERROR;
307 END IF;
308 retcode :='S';
309 EXCEPTION
310 WHEN FND_API.G_EXC_ERROR THEN
311 BEGIN
312 retcode :='E';
313 errbuff := 'Error :'||substr(sqlerrm,1,200);
314 ROLLBACK;
315 END;
316 WHEN OTHERS THEN
317 BEGIN
318 --'S' for success, 'E' form exception for , and 'U' for Undefine Exception
319 retcode :='U';
320 errbuff := 'Error :'||substr(sqlerrm,1,200);
321 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
322 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_AWARD_DELETE_PKG: DELETE_AWARD_DETAIL');
323 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
324 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
325 FND_MSG_PUB.add;
326 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
327 p_data => errbuff);
328 ROLLBACK;
329 END;
330 END DELETE_AWARD_DETAIL;
331 ------------------------------------------------------------------------------------------
332 --Following Function checks whether award deletion is possible. (MAIN FUNCTION)
333 ------------------------------------------------------------------------------------------
334
335 FUNCTION delete_award_ok
336 (
337 p_Award_Id IN NUMBER,
338 p_Billing_Rule IN VARCHAR2, --Not Using this parameter bug 2355648
339 p_Revenue_Rule IN VARCHAR2, --Not Using this parameter bug 2355648
340 RETCODE OUT NOCOPY VARCHAR2,
341 ERRBUFF OUT NOCOPY VARCHAR2
342 ) RETURN BOOLEAN AS
343
344 l_errbuff VARCHAR2(200);
345 l_retcode VARCHAR2(200);
346 l_fund_exists BOOLEAN;
347 l_event_exist BOOLEAN;
348 l_adl_exist BOOLEAN;
349 l_baselined_exist BOOLEAN;
350 BEGIN
354 RAISE FND_API.G_EXC_ERROR;
351 l_event_exist := check_event_exist(p_award_Id, l_retcode, l_errbuff);
352
353 IF l_retcode <> 'S' THEN
355 END IF;
356
357 l_adl_exist := check_adl_exist(p_award_id, l_retcode, l_errbuff);
358
359 IF l_retcode <> 'S' THEN
360 RAISE FND_API.G_EXC_ERROR;
361 END IF;
362
363 l_baselined_exist := check_baselined_budget_exist(p_award_Id,l_retcode, l_errbuff);
364
365 IF l_retcode <> 'S' THEN
366 RAISE FND_API.G_EXC_ERROR;
367 END IF;
368
369 IF ( l_event_exist OR
370 l_adl_exist OR
371 l_baselined_exist ) THEN
372
373 retcode :='S';
374 RETURN TRUE;
375 ELSE
376 retcode :='S';
377 RETURN FALSE;
378
379 END IF;
380
381 --added below logic to fix bug 2366417
382 /* IF check_baselined_budget_exist(x_award_Id) OR
383 check_event_exist(x_award_id) THEN
384 retcode :='S';
385 RETURN TRUE;
386 ELSE
387 retcode :='S';
388 RETURN FALSE;
389 END IF; */
390
391 --Commented out NOCOPY below logic to fix bug 2366417
392 /* l_fund_exists := check_funding_exists(x_award_id,l_retcode,l_errbuff);
393 IF l_retcode = 'S' THEN
394 IF check_funding_exists(x_award_Id,l_retcode,l_errbuff) THEN
395 IF x_billing_rule= 'EVENT' AND x_revenue_rule= 'EVENT' THEN
396 IF check_baselined_budget_exist(x_award_Id) -- Bug 2301943
397 OR check_event_exist(x_award_id) THEN
398 retcode :='S';
399 RETURN TRUE;
400 ELSE
401 retcode :='S';
402 RETURN FALSE;
403 END IF;
404 ELSIF x_billing_rule= 'COST' AND x_revenue_rule= 'COST' THEN
405 IF check_baselined_budget_exist(x_award_Id) THEN
406 retcode :='S';
407 RETURN TRUE;
408 ELSE
409 retcode :='S';
410 RETURN FALSE;
411 END IF;
412 ELSIF x_billing_rule= 'EVENT' AND x_revenue_rule= 'COST' THEN
413 IF check_baselined_budget_exist(x_award_Id) OR check_event_exist(x_award_Id) THEN
414 retcode :='S';
415 RETURN TRUE;
416 ELSE
417 retcode :='S';
418 RETURN FALSE;
419 END IF;
420 END IF;
421 ELSE
422 retcode :='S';
423 RETURN FALSE;
424 END IF;
425 ELSE
426 RAISE FND_API.G_EXC_ERROR ;
427 END IF; */
428 EXCEPTION
429 WHEN FND_API.G_EXC_ERROR THEN
430 BEGIN
431 retcode :='E';
432 errbuff := 'Error :'||substr(sqlerrm,1,200);
433 RETURN FALSE;
434 END;
435 WHEN OTHERS THEN
436 BEGIN
437 --'S' for success, 'E' form exception for , and 'U' for Undefine Exception
438 retcode :='U';
439 errbuff := 'Error :'||substr(sqlerrm,1,200);
440 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
441 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_AWARD_DELETE_PKG: DELETE_AWARD_OK');
442 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
443 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
444 RETURN FALSE;
445 END;
446 END delete_award_ok;
447
448 --------------------------------------------------------------------------------------------
449 --Check Draft/Submitted budget exist
450 --------------------------------------------------------------------------------------------
451 Function check_draft_budget_exists(p_award_id IN NUMBER,
452 p_msg_count OUT NOCOPY NUMBER,
453 retcode OUT NOCOPY VARCHAR2,
454 errbuff OUT NOCOPY VARCHAR2
455 ) RETURN BOOLEAN IS
456
457 CURSOR draft_budget_exist IS
458 SELECT 1 --COUNT(*) bug 2355648
459 FROM gms_budget_versions
460 WHERE award_id = p_award_id
461 AND budget_status_code = 'W';
462
463 l_budget_exist NUMBER :=0;
464
465 BEGIN
466
467 OPEN draft_budget_exist;
468 FETCH draft_budget_exist INTO l_budget_exist;
469 CLOSE draft_budget_exist;
470 IF l_budget_exist >0 THEN
471 retcode :='S';
472 RETURN TRUE;
473 ELSE
474 retcode :='S';
475 RETURN FALSE;
476 END IF;
477 EXCEPTION
478 WHEN OTHERS THEN
479 BEGIN
480 retcode := 'U';
481 errbuff := 'Error :'||substr(sqlerrm,1,200);
482 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
483 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_AWARD_DELETE_PKG: CHECK_DRAFT_BUDGET_EXISTS');
484 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
485 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
486 FND_MSG_PUB.add;
487 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
488 p_data => Errbuff);
489
490 RETURN TRUE;
491 -- RAISE FND_API.G_EXC_ERROR;
492 END;
493 END check_draft_budget_exists;
494 -------------------------------------------------------------------------------------------------------
495 --Delete the Award Details
496 -------------------------------------------------------------------------------------------------------
497 PROCEDURE delete_award_all( p_award_Id IN NUMBER ,
498 p_msg_count OUT NOCOPY NUMBER,
499 retcode OUT NOCOPY VARCHAR2 ,
500 errbuff OUT NOCOPY VARCHAR2
501 ) AS
505 FOR UPDATE NOWAIT;
502 CURSOR notification_lock IS
503 SELECT 1 FROM gms_notifications
504 WHERE award_id=p_award_Id
506
507 CURSOR lock_installments IS
508 SELECT 1 FROM gms_installments
509 WHERE award_id=p_award_Id
510 FOR UPDATE NOWAIT;
511 --Lock gms_notifiction table
512 BEGIN
513 OPEN notification_lock;
514 CLOSE notification_lock;
515 OPEN lock_installments;
516 CLOSE lock_installments;
517 --For bug 2312564 : changed the order of execution of delete to avoid dnagling records in gms_Reports
518 DELETE FROM gms_reports WHERE installment_id in (select installment_id from gms_installments WHERE award_id =p_award_id);
519 DELETE FROM gms_installments WHERE award_id=p_award_id;
520 DELETE FROM gms_default_reports WHERE award_id = p_award_id;
521 DELETE FROM gms_notifications WHERE award_id= p_award_id;
522 DELETE FROM gms_awards_terms_conditions WHERE award_id=p_award_id;
523 -- DELETE FROM gms_reports WHERE installment_id in (select installment_id
524 -- from gms_installments WHERE award_id =p_award_id);
525 DELETE FROM gms_personnel WHERE award_id =p_award_id;
526 DELETE FROM gms_reference_numbers WHERE award_id=p_award_id;
527 DELETE FROM gms_awards_contacts WHERE award_id=p_award_id;
528 DELETE FROM pa_credit_receivers WHERE project_id= p_award_Id;
529 -- Added the following delete statement for bug 2097676 :Multiple Indirect Cost schedules
530 DELETE FROM gms_override_schedules WHERE award_id=p_award_id;
531 retcode :='S';
532 EXCEPTION
533 WHEN OTHERS THEN
534 BEGIN
535 ROLLBACK;
536 --'S' for success, 'E' form exception for , and 'U' for Undefine Exception
537 retcode :='U';
538 errbuff := 'Error :'||substr(sqlerrm,1,200);
539 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
540 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_AWARD_DELETE_PKG: DELETE_AWARD_ALL');
541 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
542 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
543 FND_MSG_PUB.add;
544 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
545 p_data => errbuff);
546 END;
547 END DELETE_AWARD_ALL;
548 END GMS_AWARD_DELETE_PKG;