[Home] [Help]
PACKAGE BODY: APPS.OZF_FUND_EXTENSION_PVT
Source
1 PACKAGE BODY OZF_Fund_Extension_Pvt AS
2 /* $Header: ozfvfexb.pls 115.10 2004/05/13 07:06:17 kdass noship $*/
3 g_pkg_name CONSTANT VARCHAR2(30) := 'OZF_Fund_Extension_Pvt';
4 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
5
6 ---------------------------------------------------------------------
7 -- PROCEDURE
8 --- get_object_info
9 --
10 -- PURPOSE
11 -- private api to get object owner, name
12 -- HISTORY
13 -- 02/20/02 yzhao Created.
14 --
15 -- PARAMETERS
16 -- p_fund_rec: the fund record.
17 -- p_mode: the mode for create, and delete.
18 ---------------------------------------------------------------------
19
20 PROCEDURE get_object_info(
21 p_object_id IN NUMBER
22 , p_object_type IN VARCHAR2
23 , p_actbudget_status IN VARCHAR2
24 , x_object_owner OUT NOCOPY VARCHAR2
25 , x_object_name OUT NOCOPY VARCHAR2
26 , x_deletable_flag OUT NOCOPY VARCHAR2
27 ) IS
28 l_owner_id NUMBER;
29 l_budget_offer_yn VARCHAR2(30);
30 l_object_owner VARCHAR2(240) := '-';
31 l_object_name VARCHAR2(240) := '-';
32 l_object_status VARCHAR2(30) := NULL;
33 l_deletable_flag VARCHAR2(1) := 'D';
34
35 CURSOR c_resource(p_owner_id NUMBER) IS
36 SELECT full_name
37 FROM ams_jtf_rs_emp_v
38 WHERE resource_id = p_owner_id;
39
40 CURSOR c_offer IS
41 SELECT ofr.owner_id, qp.description, ofr.status_code, ofr.budget_offer_yn
42 FROM ozf_offers ofr, qp_list_headers qp
43 WHERE ofr.qp_list_header_id = qp.list_header_id
44 AND ofr.qp_list_header_id = p_object_id;
45
46 CURSOR c_campaign IS
47 SELECT owner_user_id, campaign_name, status_code
48 FROM ams_campaigns_vl
49 WHERE campaign_id = p_object_id;
50
51 CURSOR c_campaign_schl IS
52 SELECT owner_user_id, schedule_name, status_code
53 FROM ams_campaign_schedules_vl
54 WHERE schedule_id = p_object_id;
55
56 CURSOR c_eheader IS
57 SELECT owner_user_id, event_header_name, system_status_code
58 FROM ams_event_headers_vl
59 WHERE event_header_id = p_object_id;
60
61 CURSOR c_eoffer IS
62 SELECT owner_user_id, event_offer_name, system_status_code
63 FROM ams_event_offers_vl
64 WHERE event_offer_id = p_object_id;
65
66 CURSOR c_deliverable IS
67 SELECT owner_user_id, deliverable_name, status_code
68 FROM ams_deliverables_vl
69 WHERE deliverable_id = p_object_id;
70
71 CURSOR c_fund IS
72 SELECT owner, short_name, status_code
73 FROM ozf_funds_all_vl
74 WHERE fund_id = p_object_id;
75
76 BEGIN
77 x_object_owner := '-';
78 x_object_name := '-';
79 x_deletable_flag := 'D';
80
81 -- Offer
82 IF p_object_type = 'OFFR' THEN
83 OPEN c_offer;
84 FETCH c_offer INTO l_owner_id, l_object_name, l_object_status,l_budget_offer_yn;
85 CLOSE c_offer;
86 -- do not check offer status if it fully accrual budget's offer
87 IF NVL(l_budget_offer_yn, 'N') = 'N' THEN
88 IF l_object_status IS NOT NULL AND l_object_status <> 'ARCHIVED' AND
89 p_actbudget_status IN ('PENDING', 'APPROVED') THEN
90 l_deletable_flag := 'N';
91 END IF;
92 END IF;
93 -- Campaign
94 ELSIF p_object_type = 'CAMP' THEN
95 OPEN c_campaign;
96 FETCH c_campaign INTO l_owner_id, l_object_name, l_object_status;
97 CLOSE c_campaign;
98 IF l_object_status IS NOT NULL AND l_object_status <> 'ARCHIVED' AND
99 p_actbudget_status IN ('PENDING', 'APPROVED') THEN
100 l_deletable_flag := 'N';
101 END IF;
102 -- Campaign Schdules
103 ELSIF p_object_type = 'CSCH' THEN
104 OPEN c_campaign_schl;
105 FETCH c_campaign_schl INTO l_owner_id, l_object_name, l_object_status;
106 CLOSE c_campaign_schl;
107 -- Event Header/Rollup Event
108 ELSIF p_object_type = 'EVEH' THEN
109 OPEN c_eheader;
110 FETCH c_eheader INTO l_owner_id, l_object_name, l_object_status;
111 CLOSE c_eheader;
112 IF l_object_status IS NOT NULL AND l_object_status <> 'ARCHIVED' AND
113 p_actbudget_status IN ('PENDING', 'APPROVED') THEN
114 l_deletable_flag := 'N';
115 END IF;
116 -- Event Offer/Execution Event
117 ELSIF p_object_type IN ('EONE','EVEO') THEN
118 OPEN c_eoffer;
119 FETCH c_eoffer INTO l_owner_id, l_object_name, l_object_status;
120 CLOSE c_eoffer;
121 IF l_object_status IS NOT NULL AND l_object_status <> 'ARCHIVED' AND
122 p_actbudget_status IN ('PENDING', 'APPROVED') THEN
123 l_deletable_flag := 'N';
124 END IF;
125 -- Deliverable
126 ELSIF p_object_type = 'DELV' THEN
127 OPEN c_deliverable;
128 FETCH c_deliverable INTO l_owner_id, l_object_name, l_object_status;
129 CLOSE c_deliverable;
130 IF l_object_status IS NOT NULL AND l_object_status <> 'ARCHIVED' AND
131 p_actbudget_status IN ('PENDING', 'APPROVED') THEN
132 l_deletable_flag := 'N';
133 END IF;
134 -- Fund
135 ELSIF p_object_type = 'FUND' THEN
136 OPEN c_fund;
137 FETCH c_fund INTO l_owner_id, l_object_name, l_object_status;
138 CLOSE c_fund;
139 -- the transaction is non-deletable
140 -- if pending/approved transfer/request budget is not in 'DRAFT', 'REJECTED', 'ARCHIVED' status
141 IF l_object_status IS NOT NULL AND
142 l_object_status NOT IN ('DRAFT', 'REJECTED', 'ARCHIVED') AND
143 p_actbudget_status IN ('PENDING', 'APPROVED') THEN
144 l_deletable_flag := 'N';
145 END IF;
146 ELSE
147 l_owner_id := -1;
148 l_object_owner := ' ';
149 l_object_name := ' ';
150 END IF;
151
152 IF l_owner_id IS NOT NULL AND l_owner_id <> -1 THEN
153 OPEN c_resource(l_owner_id);
154 FETCH c_resource INTO l_object_owner;
155 CLOSE c_resource;
156 END IF;
157
158 x_object_owner := l_object_owner;
159 x_object_name := l_object_name;
160 x_deletable_flag := l_deletable_flag;
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 -- ignore exceptions
165 NULL;
166 END get_object_info;
167
168
169 ---------------------------------------------------------------------
170 -- PROCEDURE
171 -- get_actbudgets
172 --
173 -- PURPOSE
174 -- private api called by validate_delete_fund() and get_child_funds()
175 -- Get an archived fund's transaction records.
176 -- only archived fund may have transactions. draft or rejected fund does not.
177 --
178 -- HISTORY
179 -- 09/03/02 yzhao Created. Fix bug 2538082: check object's status
180 --
181 -- PARAMETERS
182 --
183 ---------------------------------------------------------------------
184 PROCEDURE get_actbudgets(
185 p_object_id IN NUMBER
186 , p_parent_fund_id IN NUMBER := NULL -- set if it is called from child fund
187 , x_non_del_flag OUT NOCOPY BOOLEAN
188 , x_actbudget_tbl OUT NOCOPY ams_utility_pvt.dependent_objects_tbl_type
189 , x_return_status OUT NOCOPY VARCHAR2
190 , x_msg_count OUT NOCOPY NUMBER
191 , x_msg_data OUT NOCOPY VARCHAR2
192 ) IS
193 l_api_name CONSTANT VARCHAR2(30) := 'get_actbudgets';
194 I NUMBER;
195 l_include_rec BOOLEAN := TRUE;
196 l_child_fund_id NUMBER;
197 l_actbudget_tbl ams_utility_pvt.dependent_objects_tbl_type;
198
199 CURSOR c_get_actbudget_usedby IS
200 SELECT arc_act_budget_used_by, act_budget_used_by_id, status_code
201 FROM ozf_act_budgets
202 WHERE budget_source_type = 'FUND'
203 AND budget_source_id = p_object_id
204 AND transfer_type IN ('TRANSFER', 'REQUEST', 'UTILIZED');
205
206 CURSOR c_get_actbudget_source IS
207 SELECT budget_source_type, budget_source_id, status_code
208 FROM ozf_act_budgets
209 WHERE arc_act_budget_used_by = 'FUND'
210 AND act_budget_used_by_id = p_object_id
211 AND transfer_type IN ('TRANSFER', 'REQUEST', 'UTILIZED');
212
213 CURSOR c_get_fund_child(p_child_fund_id NUMBER) IS
214 SELECT fund_id
215 FROM ozf_funds_all_b
216 WHERE parent_fund_id = p_object_id
217 AND fund_id = p_child_fund_id;
218
219 BEGIN
220 x_return_status := fnd_api.g_ret_sts_success;
221 x_non_del_flag := false;
222 I := 1;
223
224 FOR actbudget_rec IN c_get_actbudget_usedby LOOP
225 l_include_rec := TRUE;
226 IF p_parent_fund_id IS NOT NULL AND
227 actbudget_rec.arc_act_budget_used_by = 'FUND' THEN
228 -- do not display fund transfers between parent and children, since we check child funds seperately
229 IF actbudget_rec.act_budget_used_by_id = p_parent_fund_id THEN
230 l_include_rec := FALSE;
231 ELSE
232 l_child_fund_id := NULL;
233 OPEN c_get_fund_child(actbudget_rec.act_budget_used_by_id);
234 FETCH c_get_fund_child INTO l_child_fund_id;
235 CLOSE c_get_fund_child;
236 IF l_child_fund_id IS NULL THEN
237 l_include_rec := TRUE;
238 ELSE
239 -- the transaction is for parent-child fund transfer, do not display
240 l_include_rec := FALSE;
241 END IF;
242 END IF;
243 END IF;
244
245 IF l_include_rec THEN
246 l_actbudget_tbl(I).TYPE := ozf_utility_pvt.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER', actbudget_rec.arc_act_budget_used_by);
247 l_actbudget_tbl(I).status := actbudget_rec.status_code;
248 get_object_info( actbudget_rec.act_budget_used_by_id
249 , actbudget_rec.arc_act_budget_used_by
250 , actbudget_rec.status_code
251 , l_actbudget_tbl(I).owner
252 , l_actbudget_tbl(I).name
253 , l_actbudget_tbl(I).deletable_flag
254 );
255 /* dbms_output.put_line('actbudget ' || I || ': used by' || actbudget_rec.arc_act_budget_used_by
256 || ' id=' || actbudget_rec.act_budget_used_by_id
257 || ' status=' || actbudget_rec.status_code
258 || ' owner=' || l_actbudget_tbl(I).owner
259 || ' name=' || l_actbudget_tbl(I).name
260 );
261 */
262 IF l_actbudget_tbl(I).deletable_flag = 'N' THEN
263 x_non_del_flag := true;
264 END IF;
265 I := I + 1;
266 END IF;
267 END LOOP;
268
269 FOR actbudget_rec IN c_get_actbudget_source LOOP
270 l_include_rec := TRUE;
271 IF p_parent_fund_id IS NOT NULL AND
272 actbudget_rec.budget_source_type = 'FUND' THEN
273 -- do not display fund transfers between parent and children, since we check child funds seperately
274 IF actbudget_rec.budget_source_id = p_parent_fund_id THEN
275 l_include_rec := FALSE;
276 ELSE
277 l_child_fund_id := NULL;
278 OPEN c_get_fund_child(actbudget_rec.budget_source_id);
279 FETCH c_get_fund_child INTO l_child_fund_id;
280 CLOSE c_get_fund_child;
281 IF l_child_fund_id IS NULL THEN
282 l_include_rec := TRUE;
283 ELSE
284 -- the transaction is for parent-child fund transfer, do not display
285 l_include_rec := FALSE;
286 END IF;
287 END IF;
288 END IF;
289
290 IF l_include_rec THEN
291 l_actbudget_tbl(I).TYPE := ozf_utility_pvt.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER', actbudget_rec.budget_source_type);
292 l_actbudget_tbl(I).status := actbudget_rec.status_code;
293 get_object_info( actbudget_rec.budget_source_id
294 , actbudget_rec.budget_source_type
295 , actbudget_rec.status_code
296 , l_actbudget_tbl(I).owner
297 , l_actbudget_tbl(I).name
298 , l_actbudget_tbl(I).deletable_flag
299 );
300 /*
301 dbms_output.put_line('actbudget ' || I || ': used by' || actbudget_rec.budget_source_type
302 || ' id=' || actbudget_rec.budget_source_id
303 || ' status=' || actbudget_rec.status_code
304 || ' owner=' || l_actbudget_tbl(I).owner
305 || ' name=' || l_actbudget_tbl(I).name
306 );
307 */
308 IF l_actbudget_tbl(I).deletable_flag = 'N' THEN
309 x_non_del_flag := true;
310 END IF;
311 I := I + 1;
312 END IF;
313 END LOOP;
314
315 x_actbudget_tbl := l_actbudget_tbl;
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 x_return_status := fnd_api.g_ret_sts_unexp_error;
320 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
321 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
322 END IF;
323 fnd_msg_pub.count_and_get(
324 p_encoded => fnd_api.g_false
325 ,p_count => x_msg_count
326 ,p_data => x_msg_data);
327 END get_actbudgets;
328
329
330 ---------------------------------------------------------------------
331 -- PROCEDURE
332 -- get_child_funds
333 --
334 -- PURPOSE
335 -- private api called by validate_delete_fund()
336 -- Get children and grandchildren funds information
337 --
338 -- HISTORY
339 -- 02/20/02 yzhao Created.
340 --
341 -- PARAMETERS
342 --
343 ---------------------------------------------------------------------
344 PROCEDURE get_child_funds(
345 p_object_id IN NUMBER
346 , x_non_del_flag OUT NOCOPY BOOLEAN
347 , x_child_fund_tbl OUT NOCOPY ams_utility_pvt.dependent_objects_tbl_type
348 , x_return_status OUT NOCOPY VARCHAR2
349 , x_msg_count OUT NOCOPY NUMBER
350 , x_msg_data OUT NOCOPY VARCHAR2
351 ) IS
352 l_api_name CONSTANT VARCHAR2(30) := 'get_child_funds';
353 l_object_type VARCHAR2(30);
354 l_non_del_flag BOOLEAN;
355 l_actbudget_tbl ams_utility_pvt.dependent_objects_tbl_type;
356 l_child_fund_tbl ams_utility_pvt.dependent_objects_tbl_type;
357 l_grandchild_fund_tbl ams_utility_pvt.dependent_objects_tbl_type;
358 I NUMBER;
359
360 CURSOR c_get_child_funds IS
361 SELECT fund_id, short_name, status_code, owner,
362 decode(status_code, 'DRAFT', 'Y', 'REJECTED', 'Y', 'ARCHIVED', 'Y', 'N') deletable_flag
363 FROM ozf_funds_all_vl
364 WHERE parent_fund_id = p_object_id;
365
366 CURSOR c_resource(p_owner_id NUMBER) IS
367 SELECT full_name
368 FROM ams_jtf_rs_emp_v
369 WHERE resource_id = p_owner_id;
370
371 BEGIN
372 x_return_status := fnd_api.g_ret_sts_success;
373 x_non_del_flag := false;
374 I := 1;
375
376 l_object_type := ozf_utility_pvt.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER', 'FUND');
377 FOR child_fund_rec IN c_get_child_funds LOOP
378 l_child_fund_tbl(I).name := child_fund_rec.short_name;
379 l_child_fund_tbl(I).type := l_object_type;
380 l_child_fund_tbl(I).status := child_fund_rec.status_code;
381 -- draft, rejected or archived child fund is deletable.
382 l_child_fund_tbl(I).deletable_flag := child_fund_rec.deletable_flag;
383 IF child_fund_rec.deletable_flag = 'N' THEN
384 x_non_del_flag := true;
385 END IF;
386 l_child_fund_tbl(I).owner := '-';
387 OPEN c_resource(child_fund_rec.owner);
388 FETCH c_resource INTO l_child_fund_tbl(I).owner;
389 CLOSE c_resource;
390 /*
391 dbms_output.put_line('child fund ' || I || ': ' || child_fund_rec.short_name
392 || ' status=' || child_fund_rec.status_code
393 || ' owner=' || l_child_fund_tbl(I).owner
394 );
395 */
396 I := I + 1;
397
398 -- 09/03/2002 fix bug 2538082: check archived child fund's transaction records
399 IF child_fund_rec.status_code = 'ARCHIVED' THEN
400 -- only archived fund may have transactions. draft or rejected fund does not.
401 get_actbudgets(p_object_id => child_fund_rec.fund_id
402 , p_parent_fund_id => p_object_id
403 , x_non_del_flag => l_non_del_flag
404 , x_actbudget_tbl => l_actbudget_tbl
405 , x_return_status => x_return_status
406 , x_msg_count => x_msg_count
407 , x_msg_data => x_msg_data
408 );
409 IF x_return_status <> fnd_api.g_ret_sts_success THEN
410 RAISE fnd_api.g_exc_unexpected_error;
411 END IF;
412
413 FOR J IN NVL(l_actbudget_tbl.FIRST, 1) .. NVL(l_actbudget_tbl.LAST, 0) LOOP
414 l_child_fund_tbl(I).name := l_actbudget_tbl(J).name;
415 l_child_fund_tbl(I).TYPE := l_actbudget_tbl(J).TYPE;
416 l_child_fund_tbl(I).status := l_actbudget_tbl(J).status;
417 l_child_fund_tbl(I).deletable_flag := l_actbudget_tbl(J).deletable_flag;
418 l_child_fund_tbl(I).owner := l_actbudget_tbl(J).owner;
419 I := I + 1;
420 END LOOP;
421 IF l_non_del_flag THEN
422 x_non_del_flag := TRUE;
423 END IF;
424 END IF;
425 -- 09/03/2002 checking child fund's transaction records ends
426
427 get_child_funds( p_object_id => child_fund_rec.fund_id
428 , x_non_del_flag => l_non_del_flag
429 , x_child_fund_tbl => l_grandchild_fund_tbl
430 , x_return_status => x_return_status
431 , x_msg_count => x_msg_count
432 , x_msg_data => x_msg_data
433 );
434 IF l_grandchild_fund_tbl IS NOT NULL THEN
435 FOR J IN NVL(l_grandchild_fund_tbl.FIRST, 1) .. NVL(l_grandchild_fund_tbl.LAST, 0) LOOP
436 l_child_fund_tbl(I).name := l_grandchild_fund_tbl(J).name;
437 l_child_fund_tbl(I).TYPE := l_grandchild_fund_tbl(J).TYPE;
438 l_child_fund_tbl(I).status := l_grandchild_fund_tbl(J).status;
439 l_child_fund_tbl(I).deletable_flag := l_grandchild_fund_tbl(J).deletable_flag;
440 l_child_fund_tbl(I).owner := l_grandchild_fund_tbl(J).owner;
441 I := I + 1;
442 END LOOP;
443 IF l_non_del_flag THEN
444 x_non_del_flag := TRUE;
445 END IF;
446 END IF;
447 END LOOP;
448 x_child_fund_tbl := l_child_fund_tbl;
449
450 EXCEPTION
451 WHEN OTHERS THEN
452 x_return_status := fnd_api.g_ret_sts_unexp_error;
453 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
454 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
455 END IF;
456 fnd_msg_pub.count_and_get(
457 p_encoded => fnd_api.g_false
458 ,p_count => x_msg_count
459 ,p_data => x_msg_data);
460
461 END get_child_funds;
462
463
464 ---------------------------------------------------------------------
465 -- PROCEDURE
466 --- validate_delete_fund
467 --
468 -- PURPOSE
469 -- Validate whether a fund can be deleted. Called by 'Delete Objects' framework
470 -- Only 'draft', 'rejected', 'archived' fund are allowed to be deleted
471 -- 1) identify and provide details of dependent objects that cannot be deleted.
472 -- 2) if all dependent objects can be deleted, identify and provide details of
473 -- these dependent objects and relationships that can be disassociated.
474 --
475 -- HISTORY
476 -- 02/20/02 yzhao Created.
477 --
478 -- PARAMETERS
479 --
480 ---------------------------------------------------------------------
481
482 PROCEDURE validate_delete_fund(
483 p_api_version_number IN NUMBER
484 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
485 , p_commit IN VARCHAR2 := fnd_api.g_false
486 , p_object_id IN NUMBER
487 , p_object_version_number IN NUMBER
488 , x_dependent_object_tbl OUT NOCOPY ams_utility_pvt.dependent_objects_tbl_type
489 , x_return_status OUT NOCOPY VARCHAR2
490 , x_msg_count OUT NOCOPY NUMBER
491 , x_msg_data OUT NOCOPY VARCHAR2
492 ) IS
493 l_api_name CONSTANT VARCHAR2(30) := 'validate_delete_fund';
494 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
495 l_depend_obj_tbl ams_utility_pvt.dependent_objects_tbl_type;
496 l_actbudget_tbl ams_utility_pvt.dependent_objects_tbl_type;
497 l_fund_status VARCHAR2(30);
498 l_non_del_flag BOOLEAN := FALSE;
499 I NUMBER := 1;
500
501 CURSOR c_get_fund_info IS
502 SELECT status_code
503 FROM ozf_funds_all_b
504 WHERE fund_id = p_object_id;
505
506 BEGIN
507 x_return_status := fnd_api.g_ret_sts_success;
508
509 OPEN c_get_fund_info;
510 FETCH c_get_fund_info INTO l_fund_status;
511 CLOSE c_get_fund_info;
512 -- dbms_output.put_line('Enter validate_delete_fund fund_id=' || p_object_id || ' status=' || l_fund_status);
513
514 get_child_funds( p_object_id => p_object_id
515 , x_non_del_flag => l_non_del_flag
516 , x_child_fund_tbl => l_depend_obj_tbl
517 , x_return_status => x_return_status
518 , x_msg_count => x_msg_count
519 , x_msg_data => x_msg_data
520 );
521 IF x_return_status <> fnd_api.g_ret_sts_success THEN
522 raise fnd_api.g_exc_unexpected_error;
523 END IF;
524
525 IF l_non_del_flag THEN
526 -- return. There are non-deletable objects.
527 x_dependent_object_tbl := l_depend_obj_tbl;
528 x_return_status := fnd_api.g_ret_sts_success;
529 RETURN;
530 END IF;
531
532 I := NVL(l_depend_obj_tbl.LAST, 0) + 1;
533 IF l_fund_status = 'ARCHIVED' THEN
534 -- only archived fund may have transactions. draft or rejected fund does not.
535 get_actbudgets(p_object_id => p_object_id
536 , p_parent_fund_id => NULL
537 , x_non_del_flag => l_non_del_flag
538 , x_actbudget_tbl => l_actbudget_tbl
539 , x_return_status => x_return_status
540 , x_msg_count => x_msg_count
541 , x_msg_data => x_msg_data
542 );
543 IF x_return_status <> fnd_api.g_ret_sts_success THEN
544 raise fnd_api.g_exc_unexpected_error;
545 END IF;
546
547 FOR J IN NVL(l_actbudget_tbl.FIRST, 1) .. NVL(l_actbudget_tbl.LAST, 0) LOOP
548 l_depend_obj_tbl(I).name := l_actbudget_tbl(J).name;
549 l_depend_obj_tbl(I).TYPE := l_actbudget_tbl(J).TYPE;
550 l_depend_obj_tbl(I).status := l_actbudget_tbl(J).status;
551 l_depend_obj_tbl(I).deletable_flag := l_actbudget_tbl(J).deletable_flag;
552 l_depend_obj_tbl(I).owner := l_actbudget_tbl(J).owner;
553 I := I + 1;
554 END LOOP;
555 END IF;
556
557 x_dependent_object_tbl := l_depend_obj_tbl;
558 -- dbms_output.put_line('Validate_delete_fund successfully ends');
559
560 EXCEPTION
561 WHEN fnd_api.g_exc_error THEN
562 x_return_status := fnd_api.g_ret_sts_error;
563 fnd_msg_pub.count_and_get(
564 p_encoded => fnd_api.g_false
565 ,p_count => x_msg_count
566 ,p_data => x_msg_data);
567 WHEN fnd_api.g_exc_unexpected_error THEN
568 x_return_status := fnd_api.g_ret_sts_unexp_error;
569 fnd_msg_pub.count_and_get(
570 p_encoded => fnd_api.g_false
571 ,p_count => x_msg_count
572 ,p_data => x_msg_data);
573 WHEN OTHERS THEN
574 x_return_status := fnd_api.g_ret_sts_unexp_error;
575 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
576 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
577 END IF;
578 fnd_msg_pub.count_and_get(
579 p_encoded => fnd_api.g_false
580 ,p_count => x_msg_count
581 ,p_data => x_msg_data);
582
583 END validate_delete_fund;
584
585 ---------------------------------------------------------------------
586 -- PROCEDURE
587 -- terminate_accrual_offer
588 --
589 -- PURPOSE
590 -- This API does the following transactions
591 -- 1) Terminates an offer when its associated budget is deleted.
592 -- HISTORY
593 -- 23/06/2003 Navin Kumar Create.
594 -- NOTES
595 ---------------------------------------------------------------------
596
597 PROCEDURE terminate_accrual_offer
598 (
599 p_fund_id IN NUMBER,
600 x_msg_count OUT NOCOPY NUMBER,
601 x_msg_data OUT NOCOPY VARCHAR2,
602 x_return_status OUT NOCOPY VARCHAR2
603 )
604 IS
605
606 l_fund_id VARCHAR2(30) := p_fund_id;
607 l_user_status_id VARCHAR2(30);
608 l_full_name VARCHAR2(30);
609
610 l_status_type VARCHAR2(30) := 'OZF_OFFER_STATUS';
611 l_offer_id NUMBER;
612 l_qp_list_header_id NUMBER;
613 l_offer_obj_ver_num NUMBER;
614 l_offer_type VARCHAR2(30);
615 l_error_location NUMBER;
616
617 l_offer_hdr_rec ozf_offer_pvt.modifier_list_rec_type;
618 l_offer_line_tbl ozf_offer_pvt.modifier_line_tbl_type;
619 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
620
621 CURSOR c_get_offers(p_qp_list_header_id IN NUMBER)
622 IS
623 SELECT offer_id, object_version_number, offer_type
624 FROM ozf_offers
625 WHERE qp_list_header_id = p_qp_list_header_id;
626
627 CURSOR c_qp_list_header_id
628 IS
629 SELECT plan_id
630 FROM ozf_funds_all_b
631 WHERE fund_id = l_fund_id;
632
633 BEGIN
634
635 SAVEPOINT terminate_accrual_offer;
636 IF G_DEBUG THEN
637 ozf_utility_pvt.debug_message(l_full_name ||
638 ': terminate_accrual_offer');
639 END IF;
640 x_return_status := fnd_api.g_ret_sts_success;
641
642
643 IF G_DEBUG THEN
644 ozf_utility_pvt.debug_message('l_qp_list_header_id =>' ||
645 l_qp_list_header_id);
646 END IF;
647
648 OPEN c_qp_list_header_id;
649 FETCH c_qp_list_header_id INTO l_qp_list_header_id;
650 CLOSE c_qp_list_header_id;
651
652 OPEN c_get_offers(l_qp_list_header_id);
653 FETCH c_get_offers INTO l_offer_id, l_offer_obj_ver_num,l_offer_type;
654 CLOSE c_get_offers;
655
656
657 l_offer_hdr_rec.qp_list_header_id := l_qp_list_header_id;
658 l_offer_hdr_rec.offer_id := l_offer_id;
659 l_offer_hdr_rec.object_version_number := l_offer_obj_ver_num;
660 l_offer_hdr_rec.offer_type := l_offer_type;
661 l_offer_hdr_rec.offer_operation := 'UPDATE';
662 l_offer_hdr_rec.modifier_operation := 'UPDATE';
663
664 --kdass bug 3612350 - changed offer status from TERMINATED to CANCELLED
665 l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(l_status_type,'CANCELLED');
666 l_offer_hdr_rec.status_code := 'CANCELLED';
667 --l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(l_status_type,'TERMINATED');
668 --l_offer_hdr_rec.status_code := 'TERMINATED';
669
670 ozf_offer_pvt.process_modifiers(
671 p_init_msg_list=> fnd_api.g_false,
672 p_api_version=> 1.0,
673 p_commit=> fnd_api.g_false,
674 x_return_status=> l_return_status,
675 x_msg_count=> x_msg_count,
676 x_msg_data=> x_msg_data,
677 p_modifier_list_rec=> l_offer_hdr_rec,
678 p_modifier_line_tbl=> l_offer_line_tbl,
679 p_offer_type=> l_offer_hdr_rec.offer_type,
680 x_qp_list_header_id=> l_qp_list_header_id,
681 x_error_location=> l_error_location);
682
683 IF G_DEBUG THEN
684 ozf_utility_pvt.debug_message(
685 'l_return_status' ||
686 l_return_status ||
687 '-' ||
688 l_error_location ||
689 x_msg_data);
690 END IF;
691
692 IF l_return_status <> fnd_api.g_ret_sts_success THEN
693 IF G_DEBUG THEN
694 ozf_utility_pvt.debug_message('OZF_OFFR_QP_FAILURE ' ||
695 l_error_location ||
696 x_msg_data);
697 END IF;
698
699
700 IF l_return_status = fnd_api.g_ret_sts_error THEN
701 RAISE fnd_api.g_exc_error;
702 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
703 RAISE fnd_api.g_exc_unexpected_error;
704 END IF;
705 END IF;
706
707
708 END terminate_accrual_offer;
709
710 ---------------------------------------------------------------------
711 -- PROCEDURE
712 -- delete_fund_schema
713 --
714 -- PURPOSE
715 -- private api called by delete_fund()
716 -- p_top_del_budget_id: the top budget id to be deleted.
717 --
718 -- HISTORY
719 -- 02/20/02 yzhao Created.
720 -- 06/23/03 nkumar Modified.
721 --
722 -- PARAMETERS
723 ---------------------------------------------------------------------
724
725 PROCEDURE delete_fund_schema(
726 p_api_version_number IN NUMBER
727 , p_object_id IN NUMBER
728 , p_object_version_number IN NUMBER
729 , p_top_del_budget_id IN NUMBER
730 , x_return_status OUT NOCOPY VARCHAR2
731 , x_msg_count OUT NOCOPY NUMBER
732 , x_msg_data OUT NOCOPY VARCHAR2
733 ) IS
734 l_api_name CONSTANT VARCHAR2(30) := 'delete_fund_schema';
735 l_fund_rec ozf_funds_pvt.fund_rec_type;
736 l_msg_text VARCHAR2(4000);
737 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
738 l_msg_count NUMBER;
739 l_msg_data VARCHAR2(4000);
740
741
742 CURSOR c_get_child_funds IS
743 SELECT fund_id, object_version_number
744 FROM ozf_funds_all_b
745 WHERE parent_fund_id = p_object_id;
746
747 CURSOR c_get_fund_info IS
748 SELECT fund_id
749 , owner
750 , short_name
751 , parent_fund_id
752 , -rollup_original_budget
753 , -rollup_transfered_in_amt
754 , -rollup_transfered_out_amt
755 , -rollup_holdback_amt
756 , -rollup_planned_amt
757 , -rollup_committed_amt
758 , -rollup_utilized_amt -- yzhao: 12/02/2003 11.5.10 added
759 , -rollup_earned_amt
760 , -rollup_paid_amt
761 , -rollup_recal_committed
762 , status_code
763 , fund_type
764
765 FROM ozf_funds_all_vl
766 WHERE fund_id = p_object_id
767 AND object_version_number = p_object_version_number;
768
769 CURSOR c_get_utilization IS
770 SELECT utilization_id
771 FROM ozf_funds_utilized_all_b
772 WHERE fund_id = p_object_id
773 OR (object_id = p_object_id AND object_type = 'FUND')
774 OR (component_id = p_object_id AND component_type = 'FUND');
775
776 CURSOR c_get_metrics IS
777 SELECT activity_metric_id
778 from ozf_act_metrics_all
779 where act_metric_used_by_id = p_object_id
780 and arc_act_metric_used_by = 'FUND';
781
782 CURSOR c_fund_access IS
783 SELECT activity_access_id
784 , object_version_number
785 FROM ams_act_access
786 WHERE act_access_to_object_id = p_object_id
787 AND arc_act_access_to_object = 'FUND';
788
789 BEGIN
790 x_return_status := fnd_api.g_ret_sts_success;
791
792 FOR child_rec IN c_get_child_funds LOOP
793 delete_fund_schema(p_api_version_number => p_api_version_number
794 , p_object_id => child_rec.fund_id
795 , p_object_version_number => child_rec.object_version_number
796 , p_top_del_budget_id => p_top_del_budget_id
797 , x_return_status => l_return_status
798 , x_msg_count => x_msg_count
799 , x_msg_data => x_msg_data
800 );
801 IF l_return_status <> fnd_api.g_ret_sts_success THEN
802 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
803 END IF;
804 END LOOP;
805
806 -- ??? how to delete note? no JTF_NOTEs_PUB.delete
807
808 l_fund_rec := NULL;
809 OPEN c_get_fund_info;
810 FETCH c_get_fund_info
811 INTO l_fund_rec.fund_id
812 , l_fund_rec.owner
813 , l_fund_rec.short_name
814 , l_fund_rec.parent_fund_id
815 , l_fund_rec.rollup_original_budget
816 , l_fund_rec.rollup_transfered_in_amt
817 , l_fund_rec.rollup_transfered_out_amt
818 , l_fund_rec.rollup_holdback_amt
819 , l_fund_rec.rollup_planned_amt
820 , l_fund_rec.rollup_committed_amt
821 , l_fund_rec.rollup_utilized_amt -- yzhao: 12/02/2003 11.5.10 added
822 , l_fund_rec.rollup_earned_amt
823 , l_fund_rec.rollup_paid_amt
824 , l_fund_rec.rollup_recal_committed
825 , l_fund_rec.status_code
826 , l_fund_rec.fund_type;
827
828 l_fund_rec.object_version_number := p_object_version_number;
829 CLOSE c_get_fund_info;
830 -- dbms_output.put_line(' Enter delete_fund_schema ' || p_object_id || ' name=' || l_fund_rec.short_name || ' parent_fund_id=' || l_fund_rec.parent_fund_id);
831
832 -- update ancestors rollup amount and access only if this is the top delete budget
833 IF l_fund_rec.parent_fund_id IS NOT NULL
834 AND p_top_del_budget_id = p_object_id THEN
835 -- update its ancestors' rollup amount
836 ozf_funds_pvt.update_rollup_amount(
837 p_api_version => p_api_version_number
838 ,p_init_msg_list => fnd_api.g_false
839 ,p_commit => fnd_api.g_false
840 ,p_validation_level => fnd_api.G_VALID_LEVEL_NONE
841 ,x_return_status => l_return_status
842 ,x_msg_count => x_msg_count
843 ,x_msg_data => x_msg_data
844 ,p_fund_rec => l_fund_rec
845 );
846 -- dbms_output.put_line('update_rollup_amount for budget ' || l_fund_rec.short_name || ' returns ' || x_return_status);
847 IF l_return_status <> fnd_api.g_ret_sts_success THEN
848 -- l_msg_text := 'Update ancestor''s rollup amount for budget ' || l_fund_rec.short_name;
849 fnd_message.set_name ('OZF', 'OZF_ERR_UPD_FUND_ROLLUP');
850 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
851 ozf_utility_pvt.create_log(
852 x_return_status => l_return_status
853 , p_arc_log_used_by => 'FUND'
854 , p_log_used_by_id => p_top_del_budget_id
855 , p_msg_data => fnd_message.get
856 , p_msg_type => 'ERROR'
857 );
858
859 IF l_return_status = fnd_api.g_ret_sts_error THEN
860 RAISE fnd_api.g_exc_error;
861 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
862 RAISE fnd_api.g_exc_unexpected_error;
863 END IF;
864 END IF;
865
866 -- remove its ancestors' access to the budget
867 ozf_funds_pvt.update_funds_access(
868 p_api_version => p_api_version_number
869 ,p_init_msg_list => fnd_api.g_false
870 ,p_commit => fnd_api.g_false
871 ,p_validation_level => fnd_api.G_VALID_LEVEL_NONE
872 ,x_return_status => l_return_status
873 ,x_msg_count => x_msg_count
874 ,x_msg_data => x_msg_data
875 ,p_fund_rec => l_fund_rec
876 ,p_mode => 'DELETE'
877 );
878 -- dbms_output.put_line('update_funds_access for budget ' || l_fund_rec.short_name || ' returns ' || x_return_status);
879 IF l_return_status <> fnd_api.g_ret_sts_success THEN
880 -- l_msg_text := 'Remove ancestor''s access to budget ' || l_fund_rec.short_name;
881 fnd_message.set_name ('OZF', 'OZF_ERR_UPD_FUND_ACCESS');
882 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
883 ozf_utility_pvt.create_log(
884 x_return_status => l_return_status
885 , p_arc_log_used_by => 'FUND'
886 , p_log_used_by_id => p_top_del_budget_id
887 , p_msg_data => fnd_message.get
888 , p_msg_type => 'ERROR'
889 );
890
891 IF l_return_status = fnd_api.g_ret_sts_error THEN
892 RAISE fnd_api.g_exc_error;
893 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
894 RAISE fnd_api.g_exc_unexpected_error;
895 END IF;
896 END IF;
897
898 END IF; -- update ancestor
899
900 -- Terminate the associated offer on accrual budget deletion whos status is DRAFT/REJECT
901
902 IF l_fund_rec.status_code IN ('DRAFT','REJECTED') AND l_fund_rec.fund_type IN ('FULLY_ACCRUED') THEN
903 terminate_accrual_offer(
904 p_fund_id => p_top_del_budget_id
905 ,x_return_status => l_return_status
906 ,x_msg_count => x_msg_count
907 ,x_msg_data => x_msg_data
908 );
909
910 IF l_return_status = fnd_api.g_ret_sts_error THEN
911 RAISE fnd_api.g_exc_error;
912 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
913 RAISE fnd_api.g_exc_unexpected_error;
914 END IF;
915 END IF;
916
917 -- End Terminate offer
918
919 BEGIN
920
921 -- delete allocation data
922 FOR metric_rec IN c_get_metrics
923 LOOP
924 -- dbms_output.put_line('Delete allocation alloc_id=' || metric_rec.activity_metric_id);
925
926 DELETE FROM OZF_ACT_METRIC_FACTS_ALL
927 WHERE ACTIVITY_METRIC_ID = metric_rec.activity_metric_id
928 AND ARC_ACT_METRIC_USED_BY = 'FUND';
929
930 DELETE FROM ozf_act_metric_form_ent
931 WHERE formula_id IN ( SELECT FORMULA_ID
932 FROM OZF_ACT_METRIC_FORMULAS
933 WHERE ACTIVITY_METRIC_ID = metric_rec.activity_metric_id);
934
935 DELETE FROM ozf_act_metric_formulas
936 WHERE ACTIVITY_METRIC_ID = metric_rec.activity_metric_id;
937
938 DELETE from ozf_act_metrics_all
939 WHERE ACTIVITY_METRIC_ID = metric_rec.activity_metric_id;
940 END LOOP;
941
942 -- delete transaction data
943 DELETE FROM ozf_act_budgets
944 WHERE (budget_source_type = 'FUND'
945 AND budget_source_id = p_object_id
946 OR arc_act_budget_used_by = 'FUND'
947 AND act_budget_used_by_id = p_object_id
948 )
949 AND transfer_type IN ('TRANSFER', 'REQUEST', 'UTILIZATION', 'RESERVE');
950 -- dbms_output.put_line('Remove ' || SQL%ROWCOUNT || ' transaction records with budget ' || l_fund_rec.short_name);
951 IF SQL%ROWCOUNT > 0 THEN
952 -- l_msg_text := 'Remove transactions associated with budget ' || l_fund_rec.short_name;
953 fnd_message.set_name ('OZF', 'OZF_DEL_FUND_TRANSACTION');
954 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
955 ozf_utility_pvt.create_log(
956 x_return_status => l_return_status
957 , p_arc_log_used_by => 'FUND'
958 , p_log_used_by_id => p_top_del_budget_id
959 , p_msg_data => fnd_message.get
960 , p_msg_type => 'MILESTONE'
961 );
962 END IF;
963
964 -- delete product data
965 DELETE FROM ams_act_products
966 WHERE arc_act_product_used_by = 'FUND'
967 AND act_product_used_by_id = p_object_id;
968 -- dbms_output.put_line('Remove ' || SQL%ROWCOUNT || ' product records with budget ' || l_fund_rec.short_name);
969 IF SQL%ROWCOUNT > 0 THEN
970 --l_msg_text := 'Remove products associated with budget ' || l_fund_rec.short_name;
971 fnd_message.set_name ('OZF', 'OZF_DEL_FUND_PRODUCT');
972 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
973 ozf_utility_pvt.create_log(
974 x_return_status => l_return_status
975 , p_arc_log_used_by => 'FUND'
976 , p_log_used_by_id => p_top_del_budget_id
977 , p_msg_data => fnd_message.get
978 , p_msg_type => 'MILESTONE'
979 );
980 END IF;
981
982 -- delete market segment data
983 DELETE FROM ams_act_market_segments
984 WHERE arc_act_market_segment_used_by = 'FUND'
985 AND act_market_segment_used_by_id = p_object_id;
986 -- dbms_output.put_line('Remove ' || SQL%ROWCOUNT || ' market segment records with budget ' || l_fund_rec.short_name);
987 IF SQL%ROWCOUNT > 0 THEN
988 --l_msg_text := 'Remove market eligibilities associated with budget ' || l_fund_rec.short_name;
989 fnd_message.set_name ('OZF', 'OZF_DEL_FUND_MARKET');
990 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
991 ozf_utility_pvt.create_log(
992 x_return_status => l_return_status
993 , p_arc_log_used_by => 'FUND'
994 , p_log_used_by_id => p_top_del_budget_id
995 , p_msg_data => fnd_message.get
996 , p_msg_type => 'MILESTONE'
997 );
998 END IF;
999
1000 -- delete utilization data
1001 FOR util_rec in c_get_utilization LOOP
1002 DELETE FROM ozf_funds_utilized_all_b
1003 WHERE utilization_id = util_rec.utilization_id;
1004 DELETE FROM ozf_funds_utilized_all_tl
1005 WHERE utilization_id = util_rec.utilization_id;
1006 END LOOP;
1007 -- dbms_output.put_line('Remove ' || SQL%ROWCOUNT || ' utilization records with budget ' || l_fund_rec.short_name);
1008 EXCEPTION
1009 -- ignore these trival errors
1010 WHEN OTHERS THEN
1011 -- dbms_output.put_line('exception in delete tables. IGNORE');
1012 NULL;
1013 END;
1014
1015 -- remove every one's access to this budget
1016 FOR fund_access_rec IN c_fund_access LOOP
1017 ams_access_pvt.delete_access(
1018 p_api_version => p_api_version_number
1019 ,p_init_msg_list => fnd_api.g_false
1020 ,p_validation_level => fnd_api.G_VALID_LEVEL_NONE
1021 ,x_return_status => l_return_status
1022 ,x_msg_count => x_msg_count
1023 ,x_msg_data => x_msg_data
1024 ,p_commit => fnd_api.g_false
1025 ,p_access_id => fund_access_rec.activity_access_id
1026 ,p_object_version => fund_access_rec.object_version_number);
1027 -- dbms_output.put_line('delete access to fund returns ' || l_return_status);
1028 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1029 fnd_message.set_name ('OZF', 'OZF_ERR_UPD_FUND_ACCESS');
1030 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
1031 ozf_utility_pvt.create_log(
1032 x_return_status => l_return_status
1033 , p_arc_log_used_by => 'FUND'
1034 , p_log_used_by_id => p_top_del_budget_id
1035 , p_msg_data => fnd_message.get
1036 , p_msg_type => 'MILESTONE'
1037 );
1038 IF l_return_status = fnd_api.g_ret_sts_error THEN
1039 RAISE fnd_api.g_exc_error;
1040 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1041 RAISE fnd_api.g_exc_unexpected_error;
1042 END IF;
1043 END IF;
1044 END LOOP;
1045
1046 DELETE FROM ozf_funds_all_b
1047 WHERE fund_id = p_object_id
1048 AND object_version_number = p_object_version_number;
1049 -- dbms_output.put_line('Remove ' || SQL%ROWCOUNT || ' ozf_funds_all_b records with budget ' || l_fund_rec.short_name);
1050 IF SQL%ROWCOUNT > 0 THEN
1051 -- l_msg_text := 'Remove table record of budget ' || l_fund_rec.short_name;
1052 fnd_message.set_name ('OZF', 'OZF_DEL_FUND_RECORD');
1053 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
1054 ozf_utility_pvt.create_log(
1055 x_return_status => l_return_status
1056 , p_arc_log_used_by => 'FUND'
1057 , p_log_used_by_id => p_top_del_budget_id
1058 , p_msg_data => fnd_message.get
1059 , p_msg_type => 'MILESTONE'
1060 );
1061 END IF;
1062
1063 DELETE FROM ozf_funds_all_tl
1064 WHERE fund_id = p_object_id;
1065 -- dbms_output.put_line('Remove ' || SQL%ROWCOUNT || ' ozf_funds_all_tl records with budget ' || l_fund_rec.short_name);
1066 EXCEPTION
1067 WHEN fnd_api.g_exc_error THEN
1068 -- dbms_output.put_line('delete_fund_schema exception expected error');
1069 x_return_status := fnd_api.g_ret_sts_error;
1070 fnd_msg_pub.count_and_get(
1071 p_encoded => fnd_api.g_false
1072 ,p_count => x_msg_count
1073 ,p_data => x_msg_data);
1074 WHEN fnd_api.g_exc_unexpected_error THEN
1075 -- dbms_output.put_line('delete_fund_schema exception unexpected error');
1076 x_return_status := fnd_api.g_ret_sts_unexp_error;
1077 fnd_msg_pub.count_and_get(
1078 p_encoded => fnd_api.g_false
1079 ,p_count => x_msg_count
1080 ,p_data => x_msg_data);
1081 WHEN OTHERS THEN
1082 -- dbms_output.put_line('delete_fund_schema exception other error');
1083 x_return_status := fnd_api.g_ret_sts_unexp_error;
1084 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1085 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1086 END IF;
1087 fnd_msg_pub.count_and_get(
1088 p_encoded => fnd_api.g_false
1089 ,p_count => x_msg_count
1090 ,p_data => x_msg_data);
1091 END delete_fund_schema;
1092
1093
1094 ---------------------------------------------------------------------
1095 -- PROCEDURE
1096 --- delete_fund
1097 --
1098 -- PURPOSE
1099 -- api alled by 'Delete Objects' framework to do hard table delete
1100 --
1101 -- HISTORY
1102 -- 02/20/02 yzhao Created.
1103 --
1104 -- PARAMETERS
1105 ---------------------------------------------------------------------
1106
1107 PROCEDURE delete_fund(
1108 p_api_version_number IN NUMBER
1109 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1110 , p_commit IN VARCHAR2 := fnd_api.g_false
1111 , p_object_id IN NUMBER
1112 , p_object_version_number IN NUMBER
1113 , x_return_status OUT NOCOPY VARCHAR2
1114 , x_msg_count OUT NOCOPY NUMBER
1115 , x_msg_data OUT NOCOPY VARCHAR2
1116 ) IS
1117 l_api_name CONSTANT VARCHAR2(30) := 'delete_fund';
1118 BEGIN
1119 -- Initialize message list IF p_init_msg_list is set to TRUE.
1120 IF fnd_api.to_boolean (p_init_msg_list) THEN
1121 fnd_msg_pub.initialize;
1122 END IF;
1123
1124 delete_fund_schema(
1125 p_api_version_number => p_api_version_number
1126 , p_object_id => p_object_id
1127 , p_object_version_number => p_object_version_number
1128 , p_top_del_budget_id => p_object_id
1129 , x_return_status => x_return_status
1130 , x_msg_count => x_msg_count
1131 , x_msg_data => x_msg_data
1132 );
1133 -- dbms_output.put_line('delete_fund_schema returns ' || x_return_status);
1134
1135 IF p_commit = fnd_api.g_true THEN
1136 commit;
1137 END IF;
1138
1139 EXCEPTION
1140 WHEN OTHERS THEN
1141 -- dbms_output.put_line('delete_fund exception other error');
1142 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1143 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1144 END IF;
1145 fnd_msg_pub.count_and_get(
1146 p_encoded => fnd_api.g_false
1147 ,p_count => x_msg_count
1148 ,p_data => x_msg_data);
1149 END delete_fund;
1150
1151 END OZF_Fund_Extension_Pvt;
1152