[Home] [Help]
PACKAGE BODY: APPS.OZF_FUND_EXTENSION_PVT
Source
1 PACKAGE BODY OZF_Fund_Extension_Pvt AS
2 /* $Header: ozfvfexb.pls 120.1 2011/10/17 06:37:12 ninarasi ship $*/
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);
8 --- get_object_info
5
6 ---------------------------------------------------------------------
7 -- PROCEDURE
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
147 l_owner_id := -1;
144 l_deletable_flag := 'N';
145 END IF;
146 ELSE
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;
281 IF l_child_fund_id IS NULL THEN
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;
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
409 IF x_return_status <> fnd_api.g_ret_sts_success THEN
406 , x_msg_count => x_msg_count
407 , x_msg_data => x_msg_data
408 );
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
502 CURSOR c_get_fund_info IS
503 SELECT status_code
504 FROM ozf_funds_all_b
505 WHERE fund_id = p_object_id;
506
507 BEGIN
508 x_return_status := fnd_api.g_ret_sts_success;
509
510 OPEN c_get_fund_info;
511 FETCH c_get_fund_info INTO l_fund_status;
512 CLOSE c_get_fund_info;
513
514 -- dbms_output.put_line('Enter validate_delete_fund fund_id=' || p_object_id || ' status=' || l_fund_status);
515
516 get_child_funds( p_object_id => p_object_id
517 , x_non_del_flag => l_non_del_flag
518 , x_child_fund_tbl => l_depend_obj_tbl
519 , x_return_status => x_return_status
520 , x_msg_count => x_msg_count
521 , x_msg_data => x_msg_data
522 );
523 IF x_return_status <> fnd_api.g_ret_sts_success THEN
524 raise fnd_api.g_exc_unexpected_error;
525 END IF;
526
527 IF l_non_del_flag THEN
528 -- return. There are non-deletable objects.
529 x_dependent_object_tbl := l_depend_obj_tbl;
530 x_return_status := fnd_api.g_ret_sts_success;
531 RETURN;
532 END IF;
533
534 I := NVL(l_depend_obj_tbl.LAST, 0) + 1;
535 IF l_fund_status = 'ARCHIVED' THEN
536 -- only archived fund may have transactions. draft or rejected fund does not.
540 , x_actbudget_tbl => l_actbudget_tbl
537 get_actbudgets(p_object_id => p_object_id
538 , p_parent_fund_id => NULL
539 , x_non_del_flag => l_non_del_flag
541 , x_return_status => x_return_status
542 , x_msg_count => x_msg_count
543 , x_msg_data => x_msg_data
544 );
545 IF x_return_status <> fnd_api.g_ret_sts_success THEN
546 raise fnd_api.g_exc_unexpected_error;
547 END IF;
548
549 FOR J IN NVL(l_actbudget_tbl.FIRST, 1) .. NVL(l_actbudget_tbl.LAST, 0) LOOP
550 l_depend_obj_tbl(I).name := l_actbudget_tbl(J).name;
551 l_depend_obj_tbl(I).TYPE := l_actbudget_tbl(J).TYPE;
552 l_depend_obj_tbl(I).status := l_actbudget_tbl(J).status;
553 l_depend_obj_tbl(I).deletable_flag := l_actbudget_tbl(J).deletable_flag;
554 l_depend_obj_tbl(I).owner := l_actbudget_tbl(J).owner;
555 I := I + 1;
556 END LOOP;
557 END IF;
558
559 x_dependent_object_tbl := l_depend_obj_tbl;
560 -- dbms_output.put_line('Validate_delete_fund successfully ends');
561
562 EXCEPTION
563 WHEN fnd_api.g_exc_error THEN
564 x_return_status := fnd_api.g_ret_sts_error;
565 fnd_msg_pub.count_and_get(
566 p_encoded => fnd_api.g_false
567 ,p_count => x_msg_count
568 ,p_data => x_msg_data);
569 WHEN fnd_api.g_exc_unexpected_error THEN
570 x_return_status := fnd_api.g_ret_sts_unexp_error;
571 fnd_msg_pub.count_and_get(
572 p_encoded => fnd_api.g_false
573 ,p_count => x_msg_count
574 ,p_data => x_msg_data);
575 WHEN OTHERS THEN
576 x_return_status := fnd_api.g_ret_sts_unexp_error;
577 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
578 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
579 END IF;
580 fnd_msg_pub.count_and_get(
581 p_encoded => fnd_api.g_false
582 ,p_count => x_msg_count
583 ,p_data => x_msg_data);
584
585 END validate_delete_fund;
586
587 ---------------------------------------------------------------------
588 -- PROCEDURE
589 -- terminate_accrual_offer
590 --
591 -- PURPOSE
592 -- This API does the following transactions
593 -- 1) Terminates an offer when its associated budget is deleted.
594 -- HISTORY
595 -- 23/06/2003 Navin Kumar Create.
596 -- NOTES
597 ---------------------------------------------------------------------
598
599 PROCEDURE terminate_accrual_offer
600 (
601 p_fund_id IN NUMBER,
602 x_msg_count OUT NOCOPY NUMBER,
603 x_msg_data OUT NOCOPY VARCHAR2,
604 x_return_status OUT NOCOPY VARCHAR2
605 )
606 IS
607
608 l_fund_id VARCHAR2(30) := p_fund_id;
609 l_user_status_id VARCHAR2(30);
610 l_full_name VARCHAR2(30);
611
612 l_status_type VARCHAR2(30) := 'OZF_OFFER_STATUS';
613 l_offer_id NUMBER;
614 l_qp_list_header_id NUMBER;
615 l_offer_obj_ver_num NUMBER;
616 l_offer_type VARCHAR2(30);
617 l_error_location NUMBER;
618
619 l_offer_hdr_rec ozf_offer_pvt.modifier_list_rec_type;
620 l_offer_line_tbl ozf_offer_pvt.modifier_line_tbl_type;
621 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
622
623 CURSOR c_get_offers(p_qp_list_header_id IN NUMBER)
624 IS
625 SELECT offer_id, object_version_number, offer_type
626 FROM ozf_offers
627 WHERE qp_list_header_id = p_qp_list_header_id;
628
629 CURSOR c_qp_list_header_id
630 IS
631 SELECT plan_id
632 FROM ozf_funds_all_b
633 WHERE fund_id = l_fund_id;
634
635 BEGIN
636
637 SAVEPOINT terminate_accrual_offer;
638 IF G_DEBUG THEN
639 ozf_utility_pvt.debug_message(l_full_name ||
640 ': terminate_accrual_offer');
641 END IF;
642 x_return_status := fnd_api.g_ret_sts_success;
643
644
645 IF G_DEBUG THEN
646 ozf_utility_pvt.debug_message('l_qp_list_header_id =>' ||
647 l_qp_list_header_id);
648 END IF;
649
650 OPEN c_qp_list_header_id;
651 FETCH c_qp_list_header_id INTO l_qp_list_header_id;
652 CLOSE c_qp_list_header_id;
653
654 OPEN c_get_offers(l_qp_list_header_id);
655 FETCH c_get_offers INTO l_offer_id, l_offer_obj_ver_num,l_offer_type;
656 CLOSE c_get_offers;
657
658
659 l_offer_hdr_rec.qp_list_header_id := l_qp_list_header_id;
660 l_offer_hdr_rec.offer_id := l_offer_id;
661 l_offer_hdr_rec.object_version_number := l_offer_obj_ver_num;
662 l_offer_hdr_rec.offer_type := l_offer_type;
663 l_offer_hdr_rec.offer_operation := 'UPDATE';
664 l_offer_hdr_rec.modifier_operation := 'UPDATE';
665
666 --kdass bug 3612350 - changed offer status from TERMINATED to CANCELLED
667 l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(l_status_type,'CANCELLED');
668 l_offer_hdr_rec.status_code := 'CANCELLED';
669 --l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(l_status_type,'TERMINATED');
670 --l_offer_hdr_rec.status_code := 'TERMINATED';
671
672 ozf_offer_pvt.process_modifiers(
673 p_init_msg_list=> fnd_api.g_false,
674 p_api_version=> 1.0,
675 p_commit=> fnd_api.g_false,
676 x_return_status=> l_return_status,
677 x_msg_count=> x_msg_count,
678 x_msg_data=> x_msg_data,
679 p_modifier_list_rec=> l_offer_hdr_rec,
680 p_modifier_line_tbl=> l_offer_line_tbl,
681 p_offer_type=> l_offer_hdr_rec.offer_type,
682 x_qp_list_header_id=> l_qp_list_header_id,
683 x_error_location=> l_error_location);
684
685 IF G_DEBUG THEN
686 ozf_utility_pvt.debug_message(
687 'l_return_status' ||
688 l_return_status ||
689 '-' ||
690 l_error_location ||
691 x_msg_data);
692 END IF;
693
694 IF l_return_status <> fnd_api.g_ret_sts_success THEN
695 IF G_DEBUG THEN
696 ozf_utility_pvt.debug_message('OZF_OFFR_QP_FAILURE ' ||
697 l_error_location ||
698 x_msg_data);
699 END IF;
700
701
702 IF l_return_status = fnd_api.g_ret_sts_error THEN
703 RAISE fnd_api.g_exc_error;
704 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
705 RAISE fnd_api.g_exc_unexpected_error;
706 END IF;
707 END IF;
708
709
710 END terminate_accrual_offer;
711
712 ---------------------------------------------------------------------
713 -- PROCEDURE
714 -- delete_fund_schema
715 --
716 -- PURPOSE
717 -- private api called by delete_fund()
718 -- p_top_del_budget_id: the top budget id to be deleted.
719 --
720 -- HISTORY
721 -- 02/20/02 yzhao Created.
722 -- 06/23/03 nkumar Modified.
723 --
724 -- PARAMETERS
725 ---------------------------------------------------------------------
726
727 PROCEDURE delete_fund_schema(
728 p_api_version_number IN NUMBER
729 , p_object_id IN NUMBER
730 , p_object_version_number IN NUMBER
731 , p_top_del_budget_id IN NUMBER
732 , x_return_status OUT NOCOPY VARCHAR2
733 , x_msg_count OUT NOCOPY NUMBER
734 , x_msg_data OUT NOCOPY VARCHAR2
735 ) IS
736 l_api_name CONSTANT VARCHAR2(30) := 'delete_fund_schema';
737 l_fund_rec ozf_funds_pvt.fund_rec_type;
738 l_msg_text VARCHAR2(4000);
739 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
740 l_msg_count NUMBER;
741 l_msg_data VARCHAR2(4000);
742 l_utiz_count NUMBER;
743
744
745 CURSOR c_get_child_funds IS
746 SELECT fund_id, object_version_number
747 FROM ozf_funds_all_b
748 WHERE parent_fund_id = p_object_id;
749
750 CURSOR c_get_fund_info IS
751 SELECT fund_id
752 , owner
753 , short_name
754 , parent_fund_id
755 , -rollup_original_budget
756 , -rollup_transfered_in_amt
757 , -rollup_transfered_out_amt
758 , -rollup_holdback_amt
759 , -rollup_planned_amt
760 , -rollup_committed_amt
761 , -rollup_utilized_amt -- yzhao: 12/02/2003 11.5.10 added
762 , -rollup_earned_amt
763 , -rollup_paid_amt
764 , -rollup_recal_committed
765 , status_code
766 , fund_type
767
768 FROM ozf_funds_all_vl
769 WHERE fund_id = p_object_id
770 AND object_version_number = p_object_version_number;
771
772 --ninarasi fix for motorola bug 13054588
773 /*CURSOR c_get_utilization IS
774 SELECT utilization_id
775 FROM ozf_funds_utilized_all_b
776 WHERE fund_id = p_object_id
777 OR (object_id = p_object_id AND object_type = 'FUND')
778 OR (component_id = p_object_id AND component_type = 'FUND');*/
779
780 CURSOR c_get_metrics IS
781 SELECT activity_metric_id
782 from ozf_act_metrics_all
783 where act_metric_used_by_id = p_object_id
784 and arc_act_metric_used_by = 'FUND';
785
786 CURSOR c_fund_access IS
787 SELECT activity_access_id
788 , object_version_number
789 FROM ams_act_access
790 WHERE act_access_to_object_id = p_object_id
791 AND arc_act_access_to_object = 'FUND';
792
793 --ninarasi fix for motorola bug 13054588
794 CURSOR c_get_fund_utiz_info IS
795 SELECT 1
796 FROM ozf_funds_utilized_all_b
797 WHERE fund_id = p_object_id and rownum = 1;
798
799 BEGIN
800 x_return_status := fnd_api.g_ret_sts_success;
801
802 l_utiz_count := 0;
803
804 OPEN c_get_fund_utiz_info;
805 FETCH c_get_fund_utiz_info INTO l_utiz_count;
806 CLOSE c_get_fund_utiz_info;
807
808 --ninarasi fix for motorola bug 13054588
809 IF l_utiz_count = 1 THEN
810 FND_MESSAGE.set_name('OZF', 'OZF_DEL_FUND_ACCRUALS');
811 FND_MSG_PUB.add;
812 RAISE fnd_api.g_exc_unexpected_error;
813 END IF;
814
815 FOR child_rec IN c_get_child_funds LOOP
816 delete_fund_schema(p_api_version_number => p_api_version_number
817 , p_object_id => child_rec.fund_id
818 , p_object_version_number => child_rec.object_version_number
819 , p_top_del_budget_id => p_top_del_budget_id
820 , x_return_status => l_return_status
821 , x_msg_count => x_msg_count
822 , x_msg_data => x_msg_data
823 );
824 IF l_return_status <> fnd_api.g_ret_sts_success THEN
825 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
826 END IF;
827 END LOOP;
828
829 -- ??? how to delete note? no JTF_NOTEs_PUB.delete
830
831 l_fund_rec := NULL;
832 OPEN c_get_fund_info;
833 FETCH c_get_fund_info
834 INTO l_fund_rec.fund_id
835 , l_fund_rec.owner
836 , l_fund_rec.short_name
837 , l_fund_rec.parent_fund_id
838 , l_fund_rec.rollup_original_budget
839 , l_fund_rec.rollup_transfered_in_amt
840 , l_fund_rec.rollup_transfered_out_amt
841 , l_fund_rec.rollup_holdback_amt
842 , l_fund_rec.rollup_planned_amt
843 , l_fund_rec.rollup_committed_amt
844 , l_fund_rec.rollup_utilized_amt -- yzhao: 12/02/2003 11.5.10 added
845 , l_fund_rec.rollup_earned_amt
846 , l_fund_rec.rollup_paid_amt
847 , l_fund_rec.rollup_recal_committed
848 , l_fund_rec.status_code
849 , l_fund_rec.fund_type;
850
851 l_fund_rec.object_version_number := p_object_version_number;
852 CLOSE c_get_fund_info;
853 -- 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);
854
855 -- update ancestors rollup amount and access only if this is the top delete budget
856 IF l_fund_rec.parent_fund_id IS NOT NULL
857 AND p_top_del_budget_id = p_object_id THEN
858 -- update its ancestors' rollup amount
859 ozf_funds_pvt.update_rollup_amount(
860 p_api_version => p_api_version_number
861 ,p_init_msg_list => fnd_api.g_false
862 ,p_commit => fnd_api.g_false
863 ,p_validation_level => fnd_api.G_VALID_LEVEL_NONE
864 ,x_return_status => l_return_status
865 ,x_msg_count => x_msg_count
866 ,x_msg_data => x_msg_data
867 ,p_fund_rec => l_fund_rec
868 );
869 -- dbms_output.put_line('update_rollup_amount for budget ' || l_fund_rec.short_name || ' returns ' || x_return_status);
870 IF l_return_status <> fnd_api.g_ret_sts_success THEN
871 -- l_msg_text := 'Update ancestor''s rollup amount for budget ' || l_fund_rec.short_name;
872 fnd_message.set_name ('OZF', 'OZF_ERR_UPD_FUND_ROLLUP');
873 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
874 ozf_utility_pvt.create_log(
875 x_return_status => l_return_status
876 , p_arc_log_used_by => 'FUND'
877 , p_log_used_by_id => p_top_del_budget_id
878 , p_msg_data => fnd_message.get
879 , p_msg_type => 'ERROR'
880 );
881
882 IF l_return_status = fnd_api.g_ret_sts_error THEN
883 RAISE fnd_api.g_exc_error;
884 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
885 RAISE fnd_api.g_exc_unexpected_error;
886 END IF;
887 END IF;
888
889 -- remove its ancestors' access to the budget
890 ozf_funds_pvt.update_funds_access(
891 p_api_version => p_api_version_number
892 ,p_init_msg_list => fnd_api.g_false
893 ,p_commit => fnd_api.g_false
894 ,p_validation_level => fnd_api.G_VALID_LEVEL_NONE
895 ,x_return_status => l_return_status
896 ,x_msg_count => x_msg_count
897 ,x_msg_data => x_msg_data
898 ,p_fund_rec => l_fund_rec
899 ,p_mode => 'DELETE'
900 );
901 -- dbms_output.put_line('update_funds_access for budget ' || l_fund_rec.short_name || ' returns ' || x_return_status);
902 IF l_return_status <> fnd_api.g_ret_sts_success THEN
903 -- l_msg_text := 'Remove ancestor''s access to budget ' || l_fund_rec.short_name;
904 fnd_message.set_name ('OZF', 'OZF_ERR_UPD_FUND_ACCESS');
905 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
906 ozf_utility_pvt.create_log(
907 x_return_status => l_return_status
908 , p_arc_log_used_by => 'FUND'
909 , p_log_used_by_id => p_top_del_budget_id
910 , p_msg_data => fnd_message.get
911 , p_msg_type => 'ERROR'
912 );
913
914 IF l_return_status = fnd_api.g_ret_sts_error THEN
915 RAISE fnd_api.g_exc_error;
916 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
917 RAISE fnd_api.g_exc_unexpected_error;
918 END IF;
919 END IF;
920
921 END IF; -- update ancestor
922
923 -- Terminate the associated offer on accrual budget deletion whos status is DRAFT/REJECT
924
925 IF l_fund_rec.status_code IN ('DRAFT','REJECTED') AND l_fund_rec.fund_type IN ('FULLY_ACCRUED') THEN
926 terminate_accrual_offer(
927 p_fund_id => p_top_del_budget_id
928 ,x_return_status => l_return_status
929 ,x_msg_count => x_msg_count
930 ,x_msg_data => x_msg_data
931 );
932
933 IF l_return_status = fnd_api.g_ret_sts_error THEN
934 RAISE fnd_api.g_exc_error;
935 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
936 RAISE fnd_api.g_exc_unexpected_error;
937 END IF;
938 END IF;
939
940 -- End Terminate offer
941
942 BEGIN
943
944 -- delete allocation data
945 FOR metric_rec IN c_get_metrics
946 LOOP
947 -- dbms_output.put_line('Delete allocation alloc_id=' || metric_rec.activity_metric_id);
948
949 DELETE FROM OZF_ACT_METRIC_FACTS_ALL
950 WHERE ACTIVITY_METRIC_ID = metric_rec.activity_metric_id
951 AND ARC_ACT_METRIC_USED_BY = 'FUND';
952
953 DELETE FROM ozf_act_metric_form_ent
954 WHERE formula_id IN ( SELECT FORMULA_ID
955 FROM OZF_ACT_METRIC_FORMULAS
956 WHERE ACTIVITY_METRIC_ID = metric_rec.activity_metric_id);
957
958 DELETE FROM ozf_act_metric_formulas
959 WHERE ACTIVITY_METRIC_ID = metric_rec.activity_metric_id;
960
961 DELETE from ozf_act_metrics_all
962 WHERE ACTIVITY_METRIC_ID = metric_rec.activity_metric_id;
963 END LOOP;
964
965 -- delete transaction data
966 DELETE FROM ozf_act_budgets
967 WHERE (budget_source_type = 'FUND'
968 AND budget_source_id = p_object_id
969 OR arc_act_budget_used_by = 'FUND'
970 AND act_budget_used_by_id = p_object_id
971 )
972 AND transfer_type IN ('TRANSFER', 'REQUEST', 'UTILIZATION', 'RESERVE');
973 -- dbms_output.put_line('Remove ' || SQL%ROWCOUNT || ' transaction records with budget ' || l_fund_rec.short_name);
974 IF SQL%ROWCOUNT > 0 THEN
975 -- l_msg_text := 'Remove transactions associated with budget ' || l_fund_rec.short_name;
976 fnd_message.set_name ('OZF', 'OZF_DEL_FUND_TRANSACTION');
977 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
978 ozf_utility_pvt.create_log(
979 x_return_status => l_return_status
980 , p_arc_log_used_by => 'FUND'
981 , p_log_used_by_id => p_top_del_budget_id
982 , p_msg_data => fnd_message.get
983 , p_msg_type => 'MILESTONE'
984 );
985 END IF;
986
987 -- delete product data
988 DELETE FROM ams_act_products
989 WHERE arc_act_product_used_by = 'FUND'
990 AND act_product_used_by_id = p_object_id;
991 -- dbms_output.put_line('Remove ' || SQL%ROWCOUNT || ' product records with budget ' || l_fund_rec.short_name);
992 IF SQL%ROWCOUNT > 0 THEN
993 --l_msg_text := 'Remove products associated with budget ' || l_fund_rec.short_name;
994 fnd_message.set_name ('OZF', 'OZF_DEL_FUND_PRODUCT');
995 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
996 ozf_utility_pvt.create_log(
997 x_return_status => l_return_status
998 , p_arc_log_used_by => 'FUND'
999 , p_log_used_by_id => p_top_del_budget_id
1000 , p_msg_data => fnd_message.get
1001 , p_msg_type => 'MILESTONE'
1002 );
1003 END IF;
1004
1005 -- delete market segment data
1006 DELETE FROM ams_act_market_segments
1007 WHERE arc_act_market_segment_used_by = 'FUND'
1008 AND act_market_segment_used_by_id = p_object_id;
1009 -- dbms_output.put_line('Remove ' || SQL%ROWCOUNT || ' market segment records with budget ' || l_fund_rec.short_name);
1010 IF SQL%ROWCOUNT > 0 THEN
1011 --l_msg_text := 'Remove market eligibilities associated with budget ' || l_fund_rec.short_name;
1012 fnd_message.set_name ('OZF', 'OZF_DEL_FUND_MARKET');
1013 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
1014 ozf_utility_pvt.create_log(
1015 x_return_status => l_return_status
1016 , p_arc_log_used_by => 'FUND'
1017 , p_log_used_by_id => p_top_del_budget_id
1018 , p_msg_data => fnd_message.get
1019 , p_msg_type => 'MILESTONE'
1020 );
1021 END IF;
1022
1023 -- delete utilization data
1024 --ninarasi fix for motorola bug 13054588
1025 /*FOR util_rec in c_get_utilization LOOP
1026 DELETE FROM ozf_funds_utilized_all_b
1027 WHERE utilization_id = util_rec.utilization_id;
1028 DELETE FROM ozf_funds_utilized_all_tl
1029 WHERE utilization_id = util_rec.utilization_id;
1030 END LOOP;*/
1031 -- dbms_output.put_line('Remove ' || SQL%ROWCOUNT || ' utilization records with budget ' || l_fund_rec.short_name);
1032 EXCEPTION
1033 -- ignore these trival errors
1034 WHEN OTHERS THEN
1035 -- dbms_output.put_line('exception in delete tables. IGNORE');
1036 NULL;
1037 END;
1038
1039 -- remove every one's access to this budget
1040 FOR fund_access_rec IN c_fund_access LOOP
1041 ams_access_pvt.delete_access(
1042 p_api_version => p_api_version_number
1043 ,p_init_msg_list => fnd_api.g_false
1044 ,p_validation_level => fnd_api.G_VALID_LEVEL_NONE
1045 ,x_return_status => l_return_status
1046 ,x_msg_count => x_msg_count
1047 ,x_msg_data => x_msg_data
1048 ,p_commit => fnd_api.g_false
1049 ,p_access_id => fund_access_rec.activity_access_id
1050 ,p_object_version => fund_access_rec.object_version_number);
1051 -- dbms_output.put_line('delete access to fund returns ' || l_return_status);
1052 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1053 fnd_message.set_name ('OZF', 'OZF_ERR_UPD_FUND_ACCESS');
1054 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
1055 ozf_utility_pvt.create_log(
1056 x_return_status => l_return_status
1057 , p_arc_log_used_by => 'FUND'
1058 , p_log_used_by_id => p_top_del_budget_id
1059 , p_msg_data => fnd_message.get
1060 , p_msg_type => 'MILESTONE'
1061 );
1062 IF l_return_status = fnd_api.g_ret_sts_error THEN
1063 RAISE fnd_api.g_exc_error;
1064 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1065 RAISE fnd_api.g_exc_unexpected_error;
1066 END IF;
1067 END IF;
1068 END LOOP;
1069
1070 DELETE FROM ozf_funds_all_b
1071 WHERE fund_id = p_object_id
1072 AND object_version_number = p_object_version_number;
1073 -- dbms_output.put_line('Remove ' || SQL%ROWCOUNT || ' ozf_funds_all_b records with budget ' || l_fund_rec.short_name);
1074 IF SQL%ROWCOUNT > 0 THEN
1075 -- l_msg_text := 'Remove table record of budget ' || l_fund_rec.short_name;
1076 fnd_message.set_name ('OZF', 'OZF_DEL_FUND_RECORD');
1077 fnd_message.set_token ('BUDGET_NAME', l_fund_rec.short_name);
1078 ozf_utility_pvt.create_log(
1079 x_return_status => l_return_status
1080 , p_arc_log_used_by => 'FUND'
1081 , p_log_used_by_id => p_top_del_budget_id
1082 , p_msg_data => fnd_message.get
1083 , p_msg_type => 'MILESTONE'
1084 );
1085 END IF;
1086
1087 DELETE FROM ozf_funds_all_tl
1088 WHERE fund_id = p_object_id;
1089 -- dbms_output.put_line('Remove ' || SQL%ROWCOUNT || ' ozf_funds_all_tl records with budget ' || l_fund_rec.short_name);
1090 EXCEPTION
1091 WHEN fnd_api.g_exc_error THEN
1092 -- dbms_output.put_line('delete_fund_schema exception expected error');
1093 x_return_status := fnd_api.g_ret_sts_error;
1094 fnd_msg_pub.count_and_get(
1095 p_encoded => fnd_api.g_false
1096 ,p_count => x_msg_count
1097 ,p_data => x_msg_data);
1098 WHEN fnd_api.g_exc_unexpected_error THEN
1099 -- dbms_output.put_line('delete_fund_schema exception unexpected error');
1100 x_return_status := fnd_api.g_ret_sts_unexp_error;
1101 fnd_msg_pub.count_and_get(
1102 p_encoded => fnd_api.g_false
1103 ,p_count => x_msg_count
1104 ,p_data => x_msg_data);
1105 WHEN OTHERS THEN
1106 -- dbms_output.put_line('delete_fund_schema exception other error');
1107 x_return_status := fnd_api.g_ret_sts_unexp_error;
1108 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1109 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1110 END IF;
1111 fnd_msg_pub.count_and_get(
1112 p_encoded => fnd_api.g_false
1113 ,p_count => x_msg_count
1114 ,p_data => x_msg_data);
1115 END delete_fund_schema;
1116
1117
1118 ---------------------------------------------------------------------
1119 -- PROCEDURE
1120 --- delete_fund
1121 --
1122 -- PURPOSE
1123 -- api alled by 'Delete Objects' framework to do hard table delete
1124 --
1125 -- HISTORY
1126 -- 02/20/02 yzhao Created.
1127 --
1128 -- PARAMETERS
1129 ---------------------------------------------------------------------
1130
1131 PROCEDURE delete_fund(
1132 p_api_version_number IN NUMBER
1133 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1134 , p_commit IN VARCHAR2 := fnd_api.g_false
1135 , p_object_id IN NUMBER
1136 , p_object_version_number IN NUMBER
1137 , x_return_status OUT NOCOPY VARCHAR2
1138 , x_msg_count OUT NOCOPY NUMBER
1139 , x_msg_data OUT NOCOPY VARCHAR2
1140 ) IS
1141 l_api_name CONSTANT VARCHAR2(30) := 'delete_fund';
1142 BEGIN
1143 -- Initialize message list IF p_init_msg_list is set to TRUE.
1144 IF fnd_api.to_boolean (p_init_msg_list) THEN
1145 fnd_msg_pub.initialize;
1146 END IF;
1147
1148 delete_fund_schema(
1149 p_api_version_number => p_api_version_number
1150 , p_object_id => p_object_id
1151 , p_object_version_number => p_object_version_number
1152 , p_top_del_budget_id => p_object_id
1153 , x_return_status => x_return_status
1154 , x_msg_count => x_msg_count
1155 , x_msg_data => x_msg_data
1156 );
1157 -- dbms_output.put_line('delete_fund_schema returns ' || x_return_status);
1158
1159 IF p_commit = fnd_api.g_true THEN
1160 commit;
1161 END IF;
1162
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 -- dbms_output.put_line('delete_fund exception other error');
1166 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1167 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1168 END IF;
1169 fnd_msg_pub.count_and_get(
1170 p_encoded => fnd_api.g_false
1171 ,p_count => x_msg_count
1172 ,p_data => x_msg_data);
1173 END delete_fund;
1174
1175 END OZF_Fund_Extension_Pvt;
1176