[Home] [Help]
PACKAGE BODY: APPS.OZF_FUNDS_PVT
Source
1 PACKAGE BODY OZF_FUNDS_PVT AS
2 /* $Header: ozfvfunb.pls 120.25.12010000.2 2008/10/08 03:46:57 nirprasa ship $ */
3 -----------------------------------------------------------
4 -- PACKAGE
5 -- OZF_Funds_PVT
6 --
7 -- PROCEDURES
8 --
9 -- Create_Fund
10 -- Delete_Fund
11 -- Lock_Fund
12 -- Update_Fund
13 -- Validate_Fund
14 --
15 -- Check_Fund_Req_Items
16 -- Check_Fund_Uk_Items
17 -- Check_Fund_Fk_Items
18 -- Check_Fund_Lookup_Items
19 -- Check_Fund_Flag_Items
20 --
21 -- Check_Fund_Items
22 -- Check_Fund_Record
23 --
24 -- Init_Fund_Rec
25 -- Complete_Fund_Rec
26 -- GET_DEFAULT_GL_INFO
27 -- HISTORY
28 -- 02/02/2000 Shitij Vatsa Create.
29 -- 06/12/2000 Mumu Pande Made all fund record validation
30 -- 06/13/2000 Mumu Pande Added all access calls
31 -- 06/15/2000 Mumu Pande Added all amount validation and status_vaidation
32 -- 07/06/2000 Mumu Pande Added accrual_type fund validations fo rR2
33 --- 07/06/2000 COMPLETE_DEFAULT_GL_INFO ADDED FOR R2 Requirements to get default GL info--- mpande
34 -- 07/28/2000 Added MC Transactions calls ,added convert_currency calls ,added 3 procedures check_fund_type_vs_child
35 -- and check_fund_dates_vs_child and check_fund_amount_vs_child
36 -- 08/03/2000 Commented out uniqueness validation on fund short_name
37 -- 08/28/2001 mpande bug#1950117
38 -- 10/11/2001 yzhao bug#2020218 publish: child budget market eligibility and product eligibility not set
39 -- 03/11/2003 feliu added copy for accrual budget.
40 -- DESCRIPTION
41 -- Amount columns in Budgets
42 -- Original budget -- When you create a budget you enter this amount and update it when fund_status is DRAFT.
43 -- You cannot update this amount once the fund status is ACTIVE
44 -- Holdback Budget -- This is like putting aside some money which wont be calculated in your avalaible budget
45 -- Available budget-- This is always a calculated amount and the formula is
46 -- (OB (Original)-HB (Holback)) +(TI(Transfer in) - TO(Transfer out))
47 -- Total Budget -- This is always a calculated amount and the formula is
48 -- (AB (Available) + HB (Holback))
49 -- 08/29/2000 mchang insert org_id value into OZF_FUNDS_ALL_TL when creating a fund.
50 -- 09/12/2000 mpande added code to facilitate team and other user update access
51 -- 09/26/2000 mpande added code to facilitate user_status
52 -- 11/30/2000 mpande BUG#1494231
53 --------------major changes for 11.5.5----------------------------------------------------------------
54 -- 1) All functionality related to statistical fund removed.
55 -- 2) Added the five columns , start_period_name, end_period_name, accrual_quantity,
56 -- accrue_to_level_id,fund_calendar
57 -- 3) Introduced new API check_fund-inter_entity
58 -- 4) Removed some of the fund rules validations to package OZFFundRulesPvt (for clarity)
59 -- 5) Removed all active fund transactions to package OZFFundRulesPvt
60 -- 6) Added approval and other fully accrued fund related transactions
61 -- 05/09/2001 MPande added l_act_budget_rec.adjusted_flag ='N' when calling create_act_budget
62 -- 06/22/2001 MPande Added code for business_ubit, threshold, country task
63 -- 07/10/2001 Mpande bug#1875760
64 -- 07/30/2001 Feliu add accrual_rate, accrual_basis,country_id as required field for copy.
65 -- 10/23/2001 Feliu add recal_committed.
66 -- 11/06/2001 mpande Updated for updating transfered in amount and not original budget for child fund
67 -- Commented security group id
68 -- Added validation for dated for fully accrued budget
69 -- Added extra parameters in copy
70 ------------------------------------------------------------ ------------------------------- /
71 -- 02/08/2002 Feliu 1)Added columns for rollup amount and procedure for updating rollup amount.
72 -- for create, first create rollup amount, if parent_fund_id is not null
73 -- call update_rollup_amount to update rollup amount for parent fund.
74 -- for update, first update rollup amount. if parent_fund_id is not null,
75 -- pass rollup diffence to update_rollup_amount for all parent fund.
76 -- if parent_id is remove, then pass fund own rollup amount in negative and
77 -- call update_rollup_amount for all_parent fund.
78 -- 2)Added update_funds_access procedure for updating parent fund access. If parent_fund_id is
79 -- not null, call this procedure to create access for all parent fund. if parent_fund_id
80 -- is removed, call this procedure to remove access for all parent fund. if parent_fund_id
81 -- or fund owner has been changed, first remove access then create access for all
82 -- parent funds.
83 -- 03/11/2002 Modify rollup amount calculation.
84 -- 6/11/2002 mpande Accrual Offer Original budget Updatoin Fixed
85 -- 07/01/2002 feliu Removed default g_universal_currency and added error message.
86 -- 07/15/2002 yzhao fix bug 2457199 UNABLE TO CREATE FULLY ACCRUED BUDGET DUE TO START DATE PROBLEM
87 -- 11/06/2002 feliu fix bug 2637445 OWNER OF CHILD BUDGET CAN REMOVE OWNER OF PARENT BUDGET FROM TEAM by setting
88 -- owner_flag to 'Y' when adding access for parent budget owner.
89 -- 11/06/2002 feliu fix bug 2654263 CHILD LINE MISSING FROM TREE OVERVIEW by adding access during create budget.
90 -- 03/13/2003 feliu fix bug copy of accrual budget and market eligibility.
91 -- 03/18/2003 yzhao handle allocation activation on territory hiearchy of different owners - bypass workflow approval
92 -- 06/03/2003 yzhao fix bug 2984497 - TST1159.14 MASTER: BUDGET APPROVAL VALIDATION FAILS UPON APPROVAL IN WORKFLOW
93 -- 11/07/2003 yzhao: fix bug 3238497 - allow fully accrual budget to go below 0
94 -- Wed Mar 10 2004:1/59 PM RSSHARMA Call raise_business_event on request approval.
95 -- This will raise a business event if the fund type is Quota
96 -- 06-APR-2004 mkothari Changed bussiness event param to oracle.apps.ozf.quota.QuotaApproval
97 -- 20-Apr-2004 rimehrot Check fund amount should not be <= 0: bug fix 3580531
98 -- 10-May-2004 feliu add business event for budget create, update, and approval.
99 -- 09/09/2004 Ribha Bug Fix 3498826. Validate for fund_number uniqueness modified. Validate for fund_name uniqueness removed.
100 -- 12/28/2004 kdass fix for 11.5.10 bug 4089720, when the fund is created from mass transfer, do not check for end date
101 -- 01/04/2005 Ribha Bug Fix 4087106 - Rollup holdback amount not updated when holdback amt updated manualy.
102 -- 10/10/2005 kdass R12 bug 4613689 - validate accrual budget's ledger and offer's org
103 -- 10/26/2005 mkothari Forward port 11.5.10 Bug 4701105
104 -- 11/09/2005 kdass fixed bug 4618523
105 -- 04-Feb-2006 asylvia fixed bug 5073532 . Duplicate Budget Number error .
106 -- 27-Mar-2006 asylvia fixed bug 5107243 . Copy Budget doesnt copy all the fields .
107 -- 06-APR-2006 kdass fixed bug 5104398
108 -- 19-Apr-2006 asylvia fixed bug 5169099 . Copy Activity of Accrual Budget to new Budget.
109 -- 25-APR-2006 kdass fixed bug 5176819 - Ledger is required field
110 -- 26-APR-2006 asylvia fixed bug 5185302 . Remove copying end date to new budget .
111 -- 08-OCT-2008 nirprasa fixed bug 7425189 - Use old conversion date for reconcile flow
112 g_pkg_name CONSTANT VARCHAR2(30) := 'OZF_Funds_PVT';
113 -- 08/14/2001 mpande updated for approval and object type
114 G_PARENT_APPROVAL_TYPE CONSTANT VARCHAR2(30) := 'BUDGET';
115 -- addded 08/14/2001 mpande
116 g_activity_type CONSTANT VARCHAR2(30) := 'RFRQ';
117 -- added 02/08/2002 by feliu
118 g_universal_currency CONSTANT VARCHAR2 (15) := fnd_profile.VALUE ('OZF_UNIV_CURR_CODE');
119 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
120
121
122 -----------------------------------------------------------------------
123 -- PROCEDURE
124 -- handle_fund_status
125 --
126 -- HISTORY
127 -- 20/09/00 mpande Created.
128 -----------------------------------------------------------------------
129
130
131 PROCEDURE handle_fund_status(
132 p_user_status_id IN NUMBER
133 ,x_status_code OUT NOCOPY VARCHAR2
134 ,x_return_status OUT NOCOPY VARCHAR2)
135 IS
136 l_status_code VARCHAR2(30);
137
138 CURSOR c_status_code
139 IS
140 SELECT system_status_code
141 FROM ams_user_statuses_vl
142 WHERE user_status_id = p_user_status_id
143 AND system_status_type = 'OZF_FUND_STATUS'
144 AND enabled_flag = 'Y';
145 BEGIN
146 x_return_status := fnd_api.g_ret_sts_success;
147 OPEN c_status_code;
148 FETCH c_status_code INTO l_status_code;
149 CLOSE c_status_code;
150
151 IF l_status_code IS NULL THEN
152 x_return_status := fnd_api.g_ret_sts_error;
153 Ozf_utility_pvt.error_message('OZF_FUND_BAD_USER_STATUS');
154 END IF;
155
156 x_status_code := l_status_code;
157 END handle_fund_status;
158
159 -----------------------------------------------------------------------
160 -- PROCEDURE
161 -- get_user_status
162 --
163 -- HISTORY
164 -- 20/09/00 mpande Created.
165 -- this packagge is created because ,if there are already records in funds table
166 -- then it would be taken care of them .
167 -----------------------------------------------------------------------
168
169 PROCEDURE get_user_status(
170 p_status_code IN VARCHAR2
171 ,x_user_status_id OUT NOCOPY NUMBER
172 ,x_return_status OUT NOCOPY VARCHAR2)
173 IS
174 l_user_status_id NUMBER;
175
176 CURSOR c_user_status_id
177 IS
178 SELECT user_status_id
179 FROM ams_user_statuses_vl
180 WHERE UPPER(system_status_code) = UPPER(p_status_code)
181 AND system_status_type = 'OZF_FUND_STATUS'
182 AND enabled_flag = 'Y';
183 BEGIN
184 x_return_status := fnd_api.g_ret_sts_success;
185 OPEN c_user_status_id;
186 FETCH c_user_status_id INTO l_user_status_id;
187 CLOSE c_user_status_id;
188
189 IF l_user_status_id IS NULL THEN
190 x_return_status := fnd_api.g_ret_sts_error;
191 Ozf_utility_pvt.error_message('OZF_FUND_BAD_USER_STATUS');
192 END IF;
193
194 x_user_status_id := l_user_status_id;
195 END get_user_status;
196 -----------------------------------------------------------------------
197 -- PROCEDURE
198 -- get_child_source_code
199 --
200 -- HISTORY
201 -- 02/20/2001 mpande Created.
202 -----------------------------------------------------------------------
203
204 PROCEDURE get_child_source_code(
205 p_parent_fund_id IN NUMBER
206 ,x_code OUT NOCOPY VARCHAR2
207 ,x_return_status OUT NOCOPY VARCHAR2)
208 IS
209 l_par_number VARCHAR2(30);
210 l_count NUMBER := 0;
211
212 CURSOR c_child_count(
213 p_fund_id IN NUMBER)
214 IS
215 SELECT COUNT(fund_id)
216 FROM ozf_funds_all_b
217 WHERE parent_fund_id = p_fund_id;
218
219 CURSOR c_parent_number(
220 p_fund_id IN NUMBER)
221 IS
222 SELECT fund_number
223 FROM ozf_funds_all_b
224 WHERE fund_id = p_fund_id;
225 BEGIN
226 x_return_status := fnd_api.g_ret_sts_success;
227 OPEN c_child_count(p_parent_fund_id);
228 FETCH c_child_count INTO l_count;
229 CLOSE c_child_count;
230 OPEN c_parent_number(p_parent_fund_id);
231 FETCH c_parent_number INTO l_par_number;
232 CLOSE c_parent_number;
233
234 IF l_par_number IS NULL THEN
235 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
236 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
237 fnd_msg_pub.add;
238 END IF;
239
240 x_return_status := fnd_api.g_ret_sts_error;
241 END IF;
242
243 x_code := SUBSTRB(l_par_number || l_count, 1, 30);
244
245 --asylvia Fixed bug 5073532
246 WHILE ozf_utility_pvt.check_uniqueness('ozf_funds_all_b'
247 ,'fund_number = ''' || x_code || '''') =
248 fnd_api.g_false LOOP
249 l_count := l_count + 1 ;
250 x_code :=SUBSTRB(l_par_number || l_count, 1, 30);
251 END LOOP;
252
253 END get_child_source_code;
254
255 -----------------------------------------------------------------------
256 -- PROCEDURE
257 -- raise_business_event
258 --
259 -- HISTORY
260 -- 05/08/2004 feliu Created.
261 -----------------------------------------------------------------------
262
263
264 PROCEDURE raise_business_event(p_object_id IN NUMBER,p_event_type IN VARCHAR2)
265 IS
266 CURSOR c_fund_type(p_fund_id NUMBER) IS
267 SELECT fund_type FROM ozf_funds_all_b
268 WHERE fund_id = p_fund_id;
269
270 l_fund_type varchar2(30);
271 l_item_key varchar2(30);
272 l_event_name varchar2(80);
273
274 l_parameter_list wf_parameter_list_t;
275 BEGIN
276 l_item_key := p_object_id ||'_'|| TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
277 l_parameter_list := WF_PARAMETER_LIST_T();
278
279 OPEN c_fund_type(p_object_id);
280 FETCH c_fund_type into l_fund_type;
281 CLOSE c_fund_type;
282
283 IF l_fund_type = 'QUOTA' THEN
284 l_event_name := 'oracle.apps.ozf.quota.QuotaApproval';
285 ELSE
286 IF p_event_type = 'CREATE' THEN
287 l_event_name := 'oracle.apps.ozf.fund.budget.creation';
288 ELSIF p_event_type = 'UPDATE' THEN
289 l_event_name := 'oracle.apps.ozf.fund.budget.update';
290 ELSE
291 l_event_name := 'oracle.apps.ozf.fund.budget.approval';
292 END IF;
293 END IF;
294
295 IF G_DEBUG THEN
296 ozf_utility_pvt.debug_message('p_event_type is :'||p_event_type || ' Fund Id is :'||p_object_id );
297 END IF;
298
299 wf_event.AddParameterToList(p_name => 'P_FUND_ID',
300 p_value => p_object_id,
301 p_parameterlist => l_parameter_list);
302
303 IF G_DEBUG THEN
304 ozf_utility_pvt.debug_message('Item Key is :'||l_item_key);
305 END IF;
306
307 wf_event.raise( p_event_name =>l_event_name,
308 p_event_key => l_item_key,
309 p_parameters => l_parameter_list);
310
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 RAISE Fnd_Api.g_exc_error;
315 ozf_utility_pvt.debug_message('Exception in raising business event');
316 END;
317
318
319 ---------------------------------------------------------------------
320 -- PROCEDURE
321 -- Create_Fund
322 --
323 -- HISTORY
324 -- 02/02/2000 Shitij Vatsa Create.
325 -- 06/13/2000 Added access calls and other validations
326 -- 07/24/2000 Added Multiple Currency Calls
327
328
329 -- 01/15/2001 Made all necessary changes for 11.5.5 .
330 -- 02/08/2002 Added create rollup amount.
331 -- NOTE
332 -- For all bug fixes for prior 11.5.5 please arcs out the
333 -- earlier versions.
334 -- The create API is called with a 'active' fund status only from allocation
335 -- where no approval is required . Create fund doesnot handle the approval process.
336 ---------------------------------------------------------------------
337
338 PROCEDURE create_fund(
339 p_api_version IN NUMBER
340 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
341 ,p_commit IN VARCHAR2 := fnd_api.g_false
342 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
343 ,x_return_status OUT NOCOPY VARCHAR2
344 ,x_msg_count OUT NOCOPY NUMBER
345 ,x_msg_data OUT NOCOPY VARCHAR2
346 ,p_fund_rec IN fund_rec_type
347 ,x_fund_id OUT NOCOPY NUMBER)
348 IS
349 l_api_version CONSTANT NUMBER := 1.0;
350 l_api_name CONSTANT VARCHAR2(30) := 'Create_Fund';
351
352 l_full_name CONSTANT VARCHAR2(60)
353 := g_pkg_name || '.' || l_api_name;
354 l_return_status VARCHAR2(1);
355 l_fund_rec fund_rec_type
356 := p_fund_rec;
357 l_fund_count NUMBER;
358 l_object_version_number NUMBER := 1;
359 --//mpande
360 l_request_id NUMBER;
361 l_approver_id NUMBER;
362 l_is_requester_owner VARCHAR2(10);
363 -- l_request_rec ozf_fund_request_pvt.request_rec_type;
364 -- variable for creating access //mpande
365 l_access_rec ams_access_pvt.access_rec_type;
366 l_access_id NUMBER;
367 l_par_fund_owner NUMBER;
368 /* R12: yzhao bug 4669269 - obsolete ozf_mc_transactions
369 -- record and table variable for creating FC record // mpande
370 l_mc_transaction_rec ozf_mc_transactions_pvt.mc_transactions_rec_type;
371 l_mc_transaction_id NUMBER;
372 */
373 l_act_budget_rec ozf_actbudgets_pvt.act_budgets_rec_type;
374 l_act_budget_id NUMBER ;
375 l_is_requestor_owner VARCHAR2(30);
376 l_rate NUMBER;
377 l_valid_flag VARCHAR2(1);
378 l_ledger_name VARCHAR2(50);
379
380 CURSOR c_fund_seq
381 IS
382 SELECT ozf_funds_s.nextval
383 FROM dual;
384
385 --changed by mpande
386 CURSOR c_fund_count(
387 cv_fund_id IN NUMBER)
388 IS
389 SELECT COUNT(fund_id)
390 FROM ozf_funds_all_b
391 WHERE fund_id = cv_fund_id;
392
393
394 CURSOR c_prog_fund_number (cl_fund_number IN VARCHAR2)
395 IS
396 SELECT count(fund_id) from ozf_funds_all_b
397 WHERE fund_number = cl_fund_number;
398
399 BEGIN
400 --------------------- initialize -----------------------
401 SAVEPOINT create_fund;
402 IF G_DEBUG THEN
403 ozf_utility_pvt.debug_message(l_full_name || ': start');
404 END IF;
405 x_return_status := fnd_api.g_ret_sts_success;
406
407 IF fnd_api.to_boolean(p_init_msg_list) THEN
408 fnd_msg_pub.initialize;
409 END IF;
410
411 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
412 RAISE fnd_api.g_exc_unexpected_error;
413 END IF;
414
415 -- check fund status and fill in system status
416 handle_fund_status(
417 p_user_status_id => l_fund_rec.user_status_id
418 ,x_status_code => l_fund_rec.status_code
419 ,x_return_status => x_return_status);
420
421 IF x_return_status = fnd_api.g_ret_sts_error THEN
422 RAISE fnd_api.g_exc_error;
423 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
424 RAISE fnd_api.g_exc_unexpected_error;
425 END IF;
426
427 -- default fund calendar
428 IF l_fund_rec.fund_calendar IS NULL
429 AND ( l_fund_rec.start_period_name IS NOT NULL
430 OR l_fund_rec.end_period_name IS NOT NULL) THEN
431 l_fund_rec.fund_calendar := fnd_profile.VALUE('AMS_CAMPAIGN_DEFAULT_CALENDER');
432 END IF;
433
434 IF p_fund_rec.fund_number IS NULL THEN
435 IF p_fund_rec.parent_fund_id IS NULL THEN
436 /*l_fund_rec.fund_number :=
437 ams_sourcecode_pvt.get_source_code(
438 p_category_id => p_fund_rec.category_id
439 ,p_arc_object_for => 'FUND');*/
440 l_valid_flag := 1;
441 WHILE l_valid_flag <> 0 LOOP
442 l_fund_rec.fund_number :=
443 ams_sourcecode_pvt.get_source_code(
444 p_category_id => p_fund_rec.category_id
445 ,p_arc_object_for => 'FUND');
446
447 OPEN c_prog_fund_number (l_fund_rec.fund_number);
448 FETCH c_prog_fund_number INTO l_valid_flag;
449 CLOSE c_prog_fund_number; -- Bug Fix 3498826
450
451 /*l_valid_flag := ams_utility_pvt.check_uniqueness(
452 'ozf_funds_all_vl'
453 ,'fund_number = ''' || l_fund_rec.fund_number || '''');*/
454
455 END LOOP;
456 -- by feliu on 11/10/03 to fix bug 3244033
457 ELSE
458 get_child_source_code(
459 p_fund_rec.parent_fund_id
460 ,l_fund_rec.fund_number
461 ,x_return_status);
462 IF x_return_status = fnd_api.g_ret_sts_error THEN
463 RAISE fnd_api.g_exc_error;
464 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
465 RAISE fnd_api.g_exc_unexpected_error;
466 END IF;
467 END IF;
468 END IF;
469
470 ----------------------- validate -----------------------
471 IF G_DEBUG THEN
472 ozf_utility_pvt.debug_message(l_full_name || ': validate');
473 END IF;
474 validate_fund(
475 p_api_version => l_api_version
476 ,p_init_msg_list => p_init_msg_list
477 ,p_validation_level => p_validation_level
478 ,x_return_status => l_return_status
479 ,x_msg_count => x_msg_count
480 ,x_msg_data => x_msg_data
481 ,p_fund_rec => l_fund_rec);
482
483
484 IF l_return_status = fnd_api.g_ret_sts_error THEN
485 RAISE fnd_api.g_exc_error;
486 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
487 RAISE fnd_api.g_exc_unexpected_error;
488 END IF;
489
490 -------------------------- insert --------------------------
491 IF G_DEBUG THEN
492 ozf_utility_pvt.debug_message(l_full_name || ': insert');
493 END IF;
494
495 IF l_fund_rec.fund_id IS NULL THEN
496 LOOP
497 OPEN c_fund_seq;
498 FETCH c_fund_seq INTO l_fund_rec.fund_id;
499 CLOSE c_fund_seq;
500 OPEN c_fund_count(l_fund_rec.fund_id);
501 FETCH c_fund_count INTO l_fund_count;
502 CLOSE c_fund_count;
503 EXIT WHEN l_fund_count = 0;
504 END LOOP;
505 END IF;
506
507
508 IF G_DEBUG THEN
509 ozf_utility_pvt.debug_message(l_full_name || ': get_category');
510 END IF;
511
512 -- kdass 09-NOV-05 Bug 4618523
513 /*
514 --added by mpande 6th JULY-2000 get default category GL Info
515 IF p_fund_rec.category_id IS NOT NULL THEN
516 complete_default_gl_info(
517 l_fund_rec.category_id
518 ,l_fund_rec.accrued_liable_account
519 ,l_fund_rec.ded_adjustment_account
520 ,l_return_status);
521
522 IF l_return_status = fnd_api.g_ret_sts_error THEN
523 RAISE fnd_api.g_exc_error;
524 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
525 RAISE fnd_api.g_exc_unexpected_error;
526 END IF;
527 END IF;
528 */
529
530 -- 11/06/2001 mpande added for updating transfered in amount
531 IF p_fund_rec.status_code = 'ON_HOLD' OR l_fund_rec.status_code = 'ACTIVE' THEN
532 IF p_fund_rec.parent_fund_id IS NOT NULL THEN
533 l_fund_rec.transfered_in_amt := l_fund_rec.original_budget;
534 l_fund_rec.original_budget := 0 ;
535 END IF;
536 END IF;
537
538 IF g_universal_currency IS NULL THEN
539
540 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
541 fnd_message.set_name('OZF', 'OZF_UNIV_CURR_NOT_FOUND');
542 fnd_msg_pub.add;
543 END IF;
544
545 RAISE fnd_api.g_exc_error;
546
547 END IF;
548
549 -- Calculate rollup amount columns, added by feliu 02/08/02
550 IF l_fund_rec.original_budget IS NOT NULL
551 AND l_fund_rec.original_budget <> fnd_api.g_miss_num THEN
552 Ozf_utility_pvt.convert_currency(
553 x_return_status => l_return_status
554 ,p_from_currency => l_fund_rec.currency_code_tc
555 ,p_to_currency => g_universal_currency
556 ,p_from_amount => l_fund_rec.original_budget
557 ,x_to_amount => l_fund_rec.rollup_original_budget
558 ,x_rate => l_rate);
559
560 IF l_return_status = fnd_api.g_ret_sts_error THEN
561 RAISE fnd_api.g_exc_error;
562 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
563 RAISE fnd_api.g_exc_unexpected_error;
564 END IF;
565
566 END IF;
567
568 IF l_fund_rec.transfered_in_amt IS NOT NULL
569 AND l_rate is NOT NULL
570 AND l_fund_rec.transfered_in_amt <> fnd_api.g_miss_num THEN
571
572 l_fund_rec.rollup_transfered_in_amt := l_fund_rec.transfered_in_amt * l_rate;
573
574 END IF;
575
576 IF l_fund_rec.transfered_out_amt IS NOT NULL
577 AND l_rate is NOT NULL
578 AND l_fund_rec.transfered_out_amt <> fnd_api.g_miss_num THEN
579
580 l_fund_rec.rollup_transfered_out_amt := l_fund_rec.transfered_out_amt * l_rate;
581
582 END IF;
583
584
585 IF l_fund_rec.holdback_amt IS NOT NULL
586 AND l_rate is NOT NULL
587 AND l_fund_rec.holdback_amt <> fnd_api.g_miss_num THEN
588 l_fund_rec.rollup_holdback_amt := l_fund_rec.holdback_amt * l_rate;
589 END IF;
590
591 -- make liability_flag to be 'N' for sales accrual.
592 IF l_fund_rec.accrual_basis ='SALES' THEN
593 l_fund_rec.liability_flag := 'N';
594 END IF;
595
596 -- kdass 09-NOV-05 Bug 4618523
597 -- if ledger_id is null, derive it from org_id which is the default OU for the user
598 IF l_fund_rec.ledger_id = fnd_api.g_miss_num OR l_fund_rec.ledger_id IS NULL THEN
599
600 IF l_fund_rec.org_id <> fnd_api.g_miss_num AND l_fund_rec.org_id IS NOT NULL THEN
601 MO_UTILS.Get_Ledger_Info (
602 p_operating_unit => l_fund_rec.org_id,
603 p_ledger_id => l_fund_rec.ledger_id,
604 p_ledger_name => l_ledger_name
605 );
606 END IF;
607
608 END IF;
609
610 INSERT INTO ozf_funds_all_b
611 (
612 fund_id,
613 last_update_date,
614 last_updated_by,
615 last_update_login,
616 creation_date,
617 created_by,
618 created_from,
619 request_id,
620 program_application_id,
621 program_id,
622 program_update_date,
623 fund_number,
624 parent_fund_id,
625 category_id,
626 fund_type,
627 fund_usage, -- obsolete
628 status_code,
629 user_status_id,
630 status_date,
631 accrued_liable_account,
632 ded_adjustment_account,
633 liability_flag,
634 set_of_books_id, -- obsolete
635 start_period_id, -- obsolete
636 end_period_id, -- obsolete
637 start_date_active,
638 end_date_active,
639 budget_amount_tc, -- obsolete
640 budget_amount_fc, -- obsolete
641 available_amount, -- obsolete
642 distributed_amount, -- obsolete
643 currency_code_tc,
644 currency_code_fc, -- obsolete
645 exchange_rate_type, -- obsolete
646 exchange_rate_date, -- obsolete
647 exchange_rate, -- obsolete
648 department_id, -- obsolete
649 costcentre_id, -- obsolete
650 owner,
651 accrual_method,
652 accrual_operand,
653 accrual_rate,
654 accrual_basis,
655 hierarchy,
656 hierarchy_level,
657 hierarchy_id,
658 parent_node_id,
659 node_id, --,level_value
660 budget_flag,
661 earned_flag,
662 apply_accrual_on, -- obsolete
663 accrual_phase,
664 accrual_cap,
665 accrual_uom,
666 object_version_number,
667 attribute_category,
668 attribute1,
669 attribute2,
670 attribute3,
671 attribute4,
672 attribute5,
673 attribute6,
674 attribute7,
675 attribute8,
676 attribute9,
677 attribute10,
678 attribute11,
679 attribute12,
680 attribute13,
681 attribute14,
682 attribute15,
683 org_id,
684 original_budget,
685 transfered_in_amt,
686 transfered_out_amt,
687 holdback_amt,
688 planned_amt,
689 committed_amt,
690 earned_amt,
691 paid_amt,
692 plan_type, -- obsolete
693 plan_id, -- obsolete
694 liable_accnt_segments, -- obsolete
695 adjustment_accnt_segments, -- obsolete
696 fund_calendar,
697 start_period_name,
698 end_period_name,
699 accrual_quantity,
700 accrue_to_level_id,
701 accrual_discount_level,
702 custom_setup_id,
703 threshold_id,
704 business_unit_id,
705 country_id,
706 task_id,
707 rollup_original_budget,
708 rollup_transfered_in_amt,
709 rollup_transfered_out_amt,
710 rollup_holdback_amt,
711 retroactive_flag,
712 qualifier_id,
713 -- niprakas added
714 prev_fund_id,
715 transfered_flag,
716 utilized_amt,
717 rollup_utilized_amt,
718 product_spread_time_id,
719 ledger_id -- kdass - R12 MOAC changes
720 )
721 VALUES(
722 l_fund_rec.fund_id
723 ,SYSDATE -- LAST_UPDATE_DATE
724 ,NVL(fnd_global.user_id, -1) -- LAST_UPDATED_BY
725 ,NVL(fnd_global.conc_login_id, -1) -- LAST_UPDATE_LOGIN
726 ,SYSDATE -- CREATION_DATE
727 ,NVL(fnd_global.user_id, -1) -- CREATED_BY
728 ,NULL -- l_fund_rec.created_from -- CREATED_FROM -- we donot use this column
729 ,fnd_global.conc_request_id -- REQUEST_ID
730 ,fnd_global.prog_appl_id -- PROGRAM_APPLICATION_ID
731 ,fnd_global.conc_program_id -- PROGRAM_ID
732 ,SYSDATE -- PROGRAM_UPDATE_DATE
733 ,l_fund_rec.fund_number
734 ,l_fund_rec.parent_fund_id
735 ,l_fund_rec.category_id
736 ,l_fund_rec.fund_type
737 ,l_fund_rec.fund_usage
738 ,l_fund_rec.status_code
739 ,l_fund_rec.user_status_id
740 ,NVL(l_fund_rec.status_date, SYSDATE)
741 ,l_fund_rec.accrued_liable_account
742 ,l_fund_rec.ded_adjustment_account
743 ,NVL(l_fund_rec.liability_flag, 'N')
744 ,l_fund_rec.set_of_books_id
745 ,l_fund_rec.start_period_id
746 ,l_fund_rec.end_period_id
747 ,NVL(l_fund_rec.start_date_active, SYSDATE)
748 ,l_fund_rec.end_date_active
749 ,l_fund_rec.budget_amount_tc
750 ,l_fund_rec.budget_amount_fc
751 ,l_fund_rec.available_amount
752 ,l_fund_rec.distributed_amount
753 ,l_fund_rec.currency_code_tc
754 ,l_fund_rec.currency_code_fc
755 ,l_fund_rec.exchange_rate_type
756 ,l_fund_rec.exchange_rate_date
757 ,l_fund_rec.exchange_rate
758 ,l_fund_rec.department_id
759 ,l_fund_rec.costcentre_id
760 ,NVL(l_fund_rec.owner, NVL(fnd_global.user_id, -1)) -- OWNER
761 ,l_fund_rec.accrual_method
762 ,l_fund_rec.accrual_operand
763 ,l_fund_rec.accrual_rate
764 ,l_fund_rec.accrual_basis
765 ,l_fund_rec.hierarchy
766 ,l_fund_rec.hierarchy_level
767 ,l_fund_rec.hierarchy_id
768 ,l_fund_rec.parent_node_id
769 ,l_fund_rec.node_id --,l_fund_rec.level_value
770 ,NVL(l_fund_rec.budget_flag, 'N')
771 ,NVL(l_fund_rec.earned_flag, 'N')
772 ,l_fund_rec.apply_accrual_on
773 ,l_fund_rec.accrual_phase
774 ,l_fund_rec.accrual_cap
775 ,l_fund_rec.accrual_uom
776 ,l_object_version_number -- OBJECT_VERSION_NUMBER
777 ,l_fund_rec.attribute_category
778 ,l_fund_rec.attribute1
779 ,l_fund_rec.attribute2
780 ,l_fund_rec.attribute3
781 ,l_fund_rec.attribute4
782 ,l_fund_rec.attribute5
783 ,l_fund_rec.attribute6
784 ,l_fund_rec.attribute7
785 ,l_fund_rec.attribute8
786 ,l_fund_rec.attribute9
787 ,l_fund_rec.attribute10
788 ,l_fund_rec.attribute11
789 ,l_fund_rec.attribute12
790 ,l_fund_rec.attribute13
791 ,l_fund_rec.attribute14
792 ,l_fund_rec.attribute15
793 --,TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)) -- org_id
794 ,l_fund_rec.org_id -- kdass - R12 MOAC changes
795 ,l_fund_rec.original_budget
796 ,l_fund_rec.transfered_in_amt
797 ,l_fund_rec.transfered_out_amt
798 ,l_fund_rec.holdback_amt
799 ,l_fund_rec.planned_amt
800 ,l_fund_rec.committed_amt
801 ,l_fund_rec.earned_amt
802 ,l_fund_rec.paid_amt
803 ,l_fund_rec.plan_type
804 ,l_fund_rec.plan_id
805 ,l_fund_rec.liable_accnt_segments
806 ,l_fund_rec.adjustment_accnt_segments
807 ,l_fund_rec.fund_calendar
808 ,l_fund_rec.start_period_name
809 ,l_fund_rec.end_period_name
810 ,l_fund_rec.accrual_quantity
811 ,l_fund_rec.accrue_to_level_id
812 ,l_fund_rec.accrual_discount_level
813 ,l_fund_rec.custom_setup_id
814 ,l_fund_rec.threshold_id
815 ,l_fund_rec.business_unit_id
816 ,l_fund_rec.country_id
817 ,l_fund_rec.task_id
818 ,l_fund_rec.rollup_original_budget
819 ,l_fund_rec.rollup_transfered_in_amt
820 ,l_fund_rec.rollup_transfered_out_amt
821 ,l_fund_rec.rollup_holdback_amt
822 ,l_fund_rec.retroactive_flag
823 ,l_fund_rec.qualifier_id
824 -- niprakas added
825 ,l_fund_rec.prev_fund_id
826 ,l_fund_rec.transfered_flag
827 ,l_fund_rec.utilized_amt
828 ,l_fund_rec.rollup_utilized_amt
829 ,l_fund_rec.product_spread_time_id
830 ,l_fund_rec.ledger_id
831 );
832
833 INSERT INTO ozf_funds_all_tl
834 (fund_id,
835 last_update_date,
836 last_updated_by,
837 last_update_login,
838 creation_date,
839 created_by,
840 created_from,
841 request_id,
842 program_application_id,
843 program_id,
844 program_update_date,
845 short_name,
846 description,
847 source_lang,
848 language,
849 org_id )
850 SELECT l_fund_rec.fund_id
851 ,SYSDATE -- LAST_UPDATE_DATE
852 ,NVL(fnd_global.user_id, -1) -- LAST_UPDATED_BY
853 ,NVL(fnd_global.conc_login_id, -1) -- LAST_UPDATE_LOGIN
854 ,SYSDATE -- CREATION_DATE
855 ,NVL(fnd_global.user_id, -1) -- CREATED_BY
856 ,NULL -- CREATED_FROM
857 ,fnd_global.conc_request_id -- REQUEST_ID
858 ,fnd_global.prog_appl_id -- PROGRAM_APPLICATION_ID
859 ,fnd_global.conc_program_id -- PROGRAM_ID
860 ,SYSDATE -- PROGRAM_UPDATE_DATE
861 ,l_fund_rec.short_name
862 ,l_fund_rec.description
863 ,USERENV('LANG')
864 ,l.language_code
865 --,TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10))
866 ,l_fund_rec.org_id -- kdass - R12 MOAC changes
867 FROM fnd_languages l
868 WHERE l.installed_flag IN('I', 'B')
869 AND NOT EXISTS(SELECT NULL
870 FROM ozf_funds_all_tl t
871 WHERE t.fund_id = l_fund_rec.fund_id
872 AND t.language = l.language_code);
873
874 ------------------------- finish -------------------------------
875 x_fund_id := l_fund_rec.fund_id;
876 IF G_DEBUG THEN
877 ozf_utility_pvt.debug_message(l_full_name || ': insert object attribute');
878 END IF;
879
880 -- If the fund_status is 'ACTIVE', we need to create a record in th ACT_BUDGET table for the holdback amount
881 -- fixed bug for validation level and p_commit
882
883 IF ((l_fund_rec.status_code = 'ACTIVE')
884 AND (NVL(l_fund_rec.holdback_amt, 0) <> 0)) THEN
885 l_act_budget_rec.status_code := 'APPROVED';
886 l_act_budget_rec.arc_act_budget_used_by := 'FUND'; -- hardcoded to fund
887 l_act_budget_rec.act_budget_used_by_id := l_fund_rec.fund_id;
888 l_act_budget_rec.requester_id := l_fund_rec.owner;
889 l_act_budget_rec.approver_id := l_fund_rec.owner;
890 l_act_budget_rec.request_amount := l_fund_rec.holdback_amt; --- in transferring to fund currency
891 l_act_budget_rec.approved_amount := l_fund_rec.holdback_amt; --- in transferring to fund currency
892 l_act_budget_rec.approved_original_amount := l_fund_rec.holdback_amt; --- in transferring to fund currency
893 l_act_budget_rec.budget_source_type := 'FUND';
894 l_act_budget_rec.budget_source_id := l_fund_rec.fund_id;
895 l_act_budget_rec.transfer_type := 'RESERVE';
896 l_act_budget_rec.transaction_type := 'CREDIT';
897 l_act_budget_rec.approved_in_currency := l_fund_rec.currency_code_tc;
898 l_act_budget_rec.adjusted_flag :='N';
899 --l_act_budget_rec.date_required_by := p_needbydate;
900 -- Create_transfer record
901 ozf_actbudgets_pvt.create_act_budgets(
902 p_api_version => l_api_version
903 ,x_return_status => l_return_status
904 ,x_msg_count => x_msg_count
905 ,x_msg_data => x_msg_data
906 ,p_act_budgets_rec => l_act_budget_rec
907 ,x_act_budget_id => l_act_budget_id);
908
909 IF l_return_status = fnd_api.g_ret_sts_error THEN
910 RAISE fnd_api.g_exc_error;
911 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
912 RAISE fnd_api.g_exc_unexpected_error;
913 END IF;
914 END IF;
915
916 /* R12: yzhao bug 4669269 - obsolete ozf_mc_transactions
917 --- Insert a record in OZF_MC_TRANSACTIONS_ALL IN functional currency
918 -- so that we have the functional currency amounts
919 -- The exchange_rate_type is picked up by the MC_TRAnSACTIONS API from proile
920 IF G_DEBUG THEN
921 ozf_utility_pvt.debug_message(l_full_name || ': insert Functional currency record');
922 END IF;
923 -- Populate the record variable
924 l_mc_transaction_rec.source_object_name := 'FUND';
925 l_mc_transaction_rec.source_object_id := l_fund_rec.fund_id;
926 l_mc_transaction_rec.currency_code := l_fund_rec.currency_code_tc;
927 l_mc_transaction_rec.amount_column1 := l_fund_rec.original_budget;
928 l_mc_transaction_rec.amount_column2 := l_fund_rec.transfered_in_amt;
929 l_mc_transaction_rec.amount_column3 := l_fund_rec.transfered_out_amt;
930 l_mc_transaction_rec.amount_column4 := l_fund_rec.holdback_amt;
931 l_mc_transaction_rec.amount_column5 := l_fund_rec.planned_amt;
932 l_mc_transaction_rec.amount_column6 := l_fund_rec.committed_amt;
933 l_mc_transaction_rec.amount_column7 := l_fund_rec.earned_amt;
934 l_mc_transaction_rec.amount_column8 := l_fund_rec.paid_amt;
935 l_mc_transaction_rec.amount_column9 := l_fund_rec.utilized_amt; -- yzhao: 11.5.10
936 -- Call mc_transaction API if the fund type is not QUOTA
937 IF l_fund_rec.fund_type <> 'QUOTA' THEN
938 ozf_mc_transactions_pvt.insert_mc_transactions(
939 p_api_version => l_api_version
940 ,p_init_msg_list => fnd_api.g_false
941 ,p_commit => fnd_api.g_false
942 ,x_return_status => l_return_status
943 ,x_msg_count => x_msg_count
944 ,x_msg_data => x_msg_data
945 ,p_mc_transactions_rec => l_mc_transaction_rec
946 ,x_mc_transaction_id => l_mc_transaction_id);
947
948 IF l_return_status = fnd_api.g_ret_sts_error THEN
949 RAISE fnd_api.g_exc_error;
950 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
951 RAISE fnd_api.g_exc_unexpected_error;
952 END IF;
953 END IF;
954 */
955
956 /************************ MPANDE JAN-15 2001 ************************************************
957 For 11.5.5 release fund status will not be 'active' but will be always 'Draft' in the create
958 mode except during allocation .During fund allocation we donot need a approval for a child
959 **********************************************************************************************/
960 -- 07/10/2001 mpande bug#1875760
961 IF p_fund_rec.status_code = 'ACTIVE' THEN
962 IF p_fund_rec.parent_fund_id IS NOT NULL AND
963 p_fund_rec.fund_type <> 'FULLY_ACCRUED' THEN
964
965 -- changing status from 'DRAFT or 'REJECTED' to 'ACTIVE or ON_HOLD is
966 -- equivalent to submitting for approval.
967 -- Approval submission child fund
968 IF G_DEBUG THEN
969 ozf_utility_pvt.debug_message(l_full_name || 'owner' || p_fund_rec.owner);
970 END IF;
971
972 ozf_fund_request_apr_pvt.create_fund_request(
973 p_commit => fnd_api.g_false
974 ,p_approval_for_id => l_fund_rec.fund_id
975 ,p_requester_id => l_fund_rec.owner
976 ,p_requested_amount => l_fund_rec.transfered_in_amt ---l_fund_rec.original_budget mpande 11/06/2001
977 ,p_approval_fm => 'FUND'
978 ,p_approval_fm_id => l_fund_rec.parent_fund_id
979 ,p_transfer_type => 'TRANSFER'
980 ,p_child_flag =>'Y'
981 ,p_justification => l_fund_rec.description
982 -- 10/22/2001 mpande Changed code different owner allocation bug
983 ,p_allocation_flag => 'Y' -- set this flag to yes to by pass workflow approval
984 ,x_return_status => l_return_status
985 ,x_msg_count => x_msg_count
986 ,x_msg_data => x_msg_data
987 ,x_request_id => l_request_id
988 ,x_approver_id => l_approver_id
989 ,x_is_requester_owner => l_is_requestor_owner);
990
991 IF l_return_status = fnd_api.g_ret_sts_error THEN
992 RAISE fnd_api.g_exc_error;
993 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
994 RAISE fnd_api.g_exc_unexpected_error;
995 END IF;
996
997 -- mpande end if end
998 END IF;
999
1000 END IF ;
1001 --added by mpande
1002 -- insert a access for the owner
1003 IF G_DEBUG THEN
1004 ozf_utility_pvt.debug_message(l_full_name || ': insert access owner');
1005 END IF;
1006
1007 IF l_fund_rec.fund_id IS NOT NULL THEN
1008
1009 l_access_rec.act_access_to_object_id := l_fund_rec.fund_id;
1010 l_access_rec.arc_act_access_to_object := 'FUND';
1011 l_access_rec.user_or_role_id := l_fund_rec.owner;
1012 l_access_rec.arc_user_or_role_type := 'USER';
1013 l_access_rec.admin_flag := 'Y';
1014 l_access_rec.owner_flag := 'Y';
1015 ams_access_pvt.create_access(
1016 p_api_version => l_api_version
1017 ,p_init_msg_list => fnd_api.g_false
1018 ,p_validation_level => p_validation_level
1019 ,x_return_status => l_return_status
1020 ,x_msg_count => x_msg_count
1021 ,x_msg_data => x_msg_data
1022 ,p_commit => fnd_api.g_false
1023 ,p_access_rec => l_access_rec
1024 ,x_access_id => l_access_id);
1025
1026 IF l_return_status = fnd_api.g_ret_sts_error THEN
1027 RAISE fnd_api.g_exc_error;
1028 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1029 RAISE fnd_api.g_exc_unexpected_error;
1030 END IF;
1031 END IF;
1032
1033 --added by mpande
1034 -- if parent id is not null during creation , a row is created in the ams_act_access to give access
1035 -- to the owner of the parent fund to this child
1036 IF G_DEBUG THEN
1037 ozf_utility_pvt.debug_message(l_full_name || ': insert access parent');
1038 END IF;
1039
1040 IF l_fund_rec.parent_fund_id IS NOT NULL THEN
1041 -- added updating rollup columns by feliu.
1042 IF l_fund_rec.status_code = 'ACTIVE' THEN
1043 update_rollup_amount(
1044 p_api_version => l_api_version
1045 ,p_init_msg_list => fnd_api.g_false
1046 ,p_commit => fnd_api.g_false
1047 ,p_validation_level => p_validation_level
1048 ,x_return_status => l_return_status
1049 ,x_msg_count => x_msg_count
1050 ,x_msg_data => x_msg_data
1051 ,p_fund_rec => l_fund_rec
1052 );
1053 /* move this part to update_rollup_amount by feliu.
1054
1055 OPEN c_par_fund_owner(l_fund_rec.parent_fund_id);
1056 FETCH c_par_fund_owner INTO l_par_fund_owner;
1057 CLOSE c_par_fund_owner;
1058 --if the owner of the parent and child fund is different then only add access
1059 IF l_fund_rec.owner <> l_par_fund_owner THEN
1060 l_access_rec.act_access_to_object_id := l_fund_rec.fund_id;
1061 l_access_rec.arc_act_access_to_object := 'FUND';
1062 l_access_rec.user_or_role_id := l_par_fund_owner;
1063 l_access_rec.arc_user_or_role_type := 'USER';
1064 l_access_rec.admin_flag := 'Y'; -- 12/03/2001 yzhao: give admin access to parent
1065 l_access_rec.owner_flag := 'N';
1066 ams_access_pvt.create_access(
1067 p_api_version => l_api_version
1068 ,p_init_msg_list => fnd_api.g_false
1069 ,p_validation_level => p_validation_level
1070 ,x_return_status => l_return_status
1071 ,x_msg_count => x_msg_count
1072 ,x_msg_data => x_msg_data
1073 ,p_commit => fnd_api.g_false
1074 ,p_access_rec => l_access_rec
1075 ,x_access_id => l_access_id);
1076 END IF;
1077 */
1078 IF l_return_status = fnd_api.g_ret_sts_error THEN
1079 RAISE fnd_api.g_exc_error;
1080 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1081 RAISE fnd_api.g_exc_unexpected_error;
1082 END IF;
1083
1084 END IF;
1085 -- added by feliu to fix bug 2654263
1086
1087 update_funds_access(
1088 p_api_version => l_api_version
1089 ,p_init_msg_list => fnd_api.g_false
1090 ,p_commit => fnd_api.g_false
1091 ,p_validation_level => p_validation_level
1092 ,x_return_status => l_return_status
1093 ,x_msg_count => x_msg_count
1094 ,x_msg_data => x_msg_data
1095 ,p_fund_rec => l_fund_rec
1096 ,p_mode => 'CREATE'
1097 );
1098
1099 IF l_return_status = fnd_api.g_ret_sts_error THEN
1100 RAISE fnd_api.g_exc_error;
1101 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1102 RAISE fnd_api.g_exc_unexpected_error;
1103 END IF;
1104
1105 END IF;
1106 -- mpadne 10/14/2002 for 11.5.9
1107 IF G_DEBUG THEN
1108 OZF_UTILITY_PVT.DEBUG_MESSAGE('ACCRUAL BASIS ='||L_FUND_REC.accrual_basis);
1109 END IF;
1110 IF p_fund_rec.fund_type = 'FULLY_ACCRUED' AND p_fund_rec.plan_id is null THEN
1111 ozf_fundrules_pvt.process_accrual (
1112 p_fund_rec => l_fund_rec
1113 ,p_api_version => l_api_version
1114 ,p_mode => 'CREATE'
1115 ,x_return_status => l_return_status
1116 ,x_msg_count => x_msg_count
1117 ,x_msg_data => x_msg_data );
1118 IF l_return_status = fnd_api.g_ret_sts_error THEN
1119 RAISE fnd_api.g_exc_error;
1120 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1121 RAISE fnd_api.g_exc_unexpected_error;
1122 END IF;
1123 END IF;
1124 -- Check for commit
1125 IF fnd_api.to_boolean(p_commit) THEN
1126 COMMIT;
1127 END IF;
1128
1129 -- raise business event.
1130 raise_business_event(p_object_id => p_fund_rec.fund_id ,p_event_type =>'CREATE');
1131
1132 fnd_msg_pub.count_and_get(
1133 p_encoded => fnd_api.g_false
1134 ,p_count => x_msg_count
1135 ,p_data => x_msg_data);
1136 IF G_DEBUG THEN
1137 ozf_utility_pvt.debug_message(l_full_name || ': end');
1138 END IF;
1139
1140 EXCEPTION
1141 WHEN fnd_api.g_exc_error THEN
1142 ROLLBACK TO create_fund;
1143 x_return_status := fnd_api.g_ret_sts_error;
1144 fnd_msg_pub.count_and_get(
1145 p_encoded => fnd_api.g_false
1146 ,p_count => x_msg_count
1147 ,p_data => x_msg_data);
1148 WHEN fnd_api.g_exc_unexpected_error THEN
1149 ROLLBACK TO create_fund;
1150 x_return_status := fnd_api.g_ret_sts_unexp_error;
1151 fnd_msg_pub.count_and_get(
1152 p_encoded => fnd_api.g_false
1153 ,p_count => x_msg_count
1154 ,p_data => x_msg_data);
1155 WHEN OTHERS THEN
1156 ROLLBACK TO create_fund;
1157 x_return_status := fnd_api.g_ret_sts_unexp_error;
1158
1159 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1160 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1161 END IF;
1162
1163 fnd_msg_pub.count_and_get(
1164 p_encoded => fnd_api.g_false
1165 ,p_count => x_msg_count
1166 ,p_data => x_msg_data);
1167 END create_fund;
1168
1169
1170
1171 ---------------------------------------------------------------
1172 -- PROCEDURE
1173 -- Delete_Fund
1174 --
1175 -- HISTORY
1176 -- 02/02/2000 Shitij Vatsa Create.
1177 ---------------------------------------------------------------
1178 PROCEDURE delete_fund(
1179 p_api_version IN NUMBER
1180 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1181 ,p_commit IN VARCHAR2 := fnd_api.g_false
1182 ,x_return_status OUT NOCOPY VARCHAR2
1183 ,x_msg_count OUT NOCOPY NUMBER
1184 ,x_msg_data OUT NOCOPY VARCHAR2
1185 ,p_fund_id IN NUMBER
1186 ,p_object_version IN NUMBER)
1187 IS
1188 l_api_version CONSTANT NUMBER := 1.0;
1189 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Fund';
1190 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1191 BEGIN
1192 --------------------- initialize -----------------------
1193 SAVEPOINT delete_fund;
1194 IF G_DEBUG THEN
1195 ozf_utility_pvt.debug_message(l_full_name || ': start');
1196 END IF;
1197
1198 IF fnd_api.to_boolean(p_init_msg_list) THEN
1199 fnd_msg_pub.initialize;
1200 END IF;
1201
1202 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1203 RAISE fnd_api.g_exc_unexpected_error;
1204 END IF;
1205
1206 x_return_status := fnd_api.g_ret_sts_success;
1207 ------------------------ delete ------------------------
1208 IF G_DEBUG THEN
1209 ozf_utility_pvt.debug_message(l_full_name || ': delete');
1210 END IF;
1211
1212 DELETE
1213 FROM ozf_funds_all_b
1214 WHERE fund_id = p_fund_id
1215 AND object_version_number = p_object_version;
1216
1217 IF (SQL%NOTFOUND) THEN
1218 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1219 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1220 fnd_msg_pub.add;
1221 END IF;
1222
1223 RAISE fnd_api.g_exc_error;
1224 END IF;
1225
1226 DELETE
1227 FROM ozf_funds_all_tl
1228 WHERE fund_id = p_fund_id;
1229
1230 IF (SQL%NOTFOUND) THEN
1231 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1232 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1233 fnd_msg_pub.add;
1234 END IF;
1235
1236 RAISE fnd_api.g_exc_error;
1237 END IF;
1238
1239 -------------------- finish --------------------------
1240 IF fnd_api.to_boolean(p_commit) THEN
1241 COMMIT;
1242 END IF;
1243
1244 fnd_msg_pub.count_and_get(
1245 p_encoded => fnd_api.g_false
1246 ,p_count => x_msg_count
1247 ,p_data => x_msg_data);
1248 IF G_DEBUG THEN
1249 ozf_utility_pvt.debug_message(l_full_name || ': end');
1250 END IF;
1251 EXCEPTION
1252 WHEN fnd_api.g_exc_error THEN
1253 ROLLBACK TO delete_fund;
1254 x_return_status := fnd_api.g_ret_sts_error;
1255 fnd_msg_pub.count_and_get(
1256 p_encoded => fnd_api.g_false
1257 ,p_count => x_msg_count
1258 ,p_data => x_msg_data);
1259 WHEN fnd_api.g_exc_unexpected_error THEN
1260 ROLLBACK TO delete_fund;
1261 x_return_status := fnd_api.g_ret_sts_unexp_error;
1262 fnd_msg_pub.count_and_get(
1263 p_encoded => fnd_api.g_false
1264 ,p_count => x_msg_count
1265 ,p_data => x_msg_data);
1266 WHEN OTHERS THEN
1267 ROLLBACK TO delete_fund;
1268 x_return_status := fnd_api.g_ret_sts_unexp_error;
1269
1270 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1271 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1272 END IF;
1273
1274 fnd_msg_pub.count_and_get(
1275 p_encoded => fnd_api.g_false
1276 ,p_count => x_msg_count
1277 ,p_data => x_msg_data);
1278 END delete_fund;
1279
1280
1281
1282 -------------------------------------------------------------------
1283 -- PROCEDURE
1284 -- Lock_Fund
1285 --
1286 -- HISTORY
1287 -- 02/02/2000 Shitij Vatsa Create.
1288 --------------------------------------------------------------------
1289 PROCEDURE lock_fund(
1290 p_api_version IN NUMBER
1291 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1292 ,x_return_status OUT NOCOPY VARCHAR2
1293 ,x_msg_count OUT NOCOPY NUMBER
1294 ,x_msg_data OUT NOCOPY VARCHAR2
1295 ,p_fund_id IN NUMBER
1296 ,p_object_version IN NUMBER)
1297 IS
1298 l_api_version CONSTANT NUMBER := 1.0;
1299 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Fund';
1300 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1301 l_fund_id NUMBER;
1302
1303 CURSOR c_fund_b
1304 IS
1305 SELECT fund_id
1306 FROM ozf_funds_all_b
1307 WHERE fund_id = p_fund_id
1308 AND object_version_number = p_object_version
1309 FOR UPDATE OF fund_id NOWAIT;
1310
1311 CURSOR c_fund_tl
1312 IS
1313 SELECT fund_id
1314 FROM ozf_funds_all_tl
1315 WHERE fund_id = p_fund_id
1316 AND USERENV('LANG') IN(language, source_lang)
1317 FOR UPDATE OF fund_id NOWAIT;
1318 BEGIN
1319 -------------------- initialize ------------------------
1320 IF G_DEBUG THEN
1321 ozf_utility_pvt.debug_message(l_full_name || ': start');
1322 END IF;
1323
1324 IF fnd_api.to_boolean(p_init_msg_list) THEN
1325 fnd_msg_pub.initialize;
1326 END IF;
1327
1328 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1329 RAISE fnd_api.g_exc_unexpected_error;
1330 END IF;
1331
1332 x_return_status := fnd_api.g_ret_sts_success;
1333 ------------------------ lock -------------------------
1334 IF G_DEBUG THEN
1335 ozf_utility_pvt.debug_message(l_full_name || ': lock');
1336 END IF;
1337 OPEN c_fund_b;
1338 FETCH c_fund_b INTO l_fund_id;
1339
1340 IF (c_fund_b%NOTFOUND) THEN
1341 CLOSE c_fund_b;
1342
1343 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1344 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1345 fnd_msg_pub.add;
1346 END IF;
1347
1348 RAISE fnd_api.g_exc_error;
1349 END IF;
1350
1351 CLOSE c_fund_b;
1352 OPEN c_fund_tl;
1353 CLOSE c_fund_tl;
1354 -------------------- finish --------------------------
1355 fnd_msg_pub.count_and_get(
1356 p_encoded => fnd_api.g_false
1357 ,p_count => x_msg_count
1358 ,p_data => x_msg_data);
1359 IF G_DEBUG THEN
1360 ozf_utility_pvt.debug_message(l_full_name || ': end');
1361 END IF;
1362 EXCEPTION
1363 WHEN ozf_utility_pvt.resource_locked THEN
1364 x_return_status := fnd_api.g_ret_sts_error;
1365
1366 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1367 fnd_message.set_name('OZF', 'OZF_API_RESOURCE_LOCKED');
1368 fnd_msg_pub.add;
1369 END IF;
1370
1371 fnd_msg_pub.count_and_get(
1372 p_encoded => fnd_api.g_false
1373 ,p_count => x_msg_count
1374 ,p_data => x_msg_data);
1375 WHEN fnd_api.g_exc_error THEN
1376 x_return_status := fnd_api.g_ret_sts_error;
1377 fnd_msg_pub.count_and_get(
1378 p_encoded => fnd_api.g_false
1379 ,p_count => x_msg_count
1380 ,p_data => x_msg_data);
1381 WHEN fnd_api.g_exc_unexpected_error THEN
1382 x_return_status := fnd_api.g_ret_sts_unexp_error;
1383 fnd_msg_pub.count_and_get(
1384 p_encoded => fnd_api.g_false
1385 ,p_count => x_msg_count
1386 ,p_data => x_msg_data);
1387 WHEN OTHERS THEN
1388 x_return_status := fnd_api.g_ret_sts_unexp_error;
1389
1390 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1391 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1392 END IF;
1393
1394 fnd_msg_pub.count_and_get(
1395 p_encoded => fnd_api.g_false
1396 ,p_count => x_msg_count
1397 ,p_data => x_msg_data);
1398 END lock_fund;
1399
1400 ---------------------------------------------------------------------
1401 -- PROCEDURE
1402 -- Update_Fund
1403 --
1404 -- HISTORY
1405 -- 02/02/2000 Shitij Vatsa Create.
1406 -- 06/13/2000 mpande Added access calls
1407 -- 06/13/2000 mpande fixed bug complete rec
1408 -- 06/13/2000 Added access calls and other validations
1409 -- 07/24/2000 Added Multiple Currency Enabling Calls
1410 -- 07/28/2000 Added parent_validation against child
1411 -- 02/08/2002 Added rollup amount update.
1412 /*************major changes for 11.5.5*********************************
1413 -- All functionality related to statistical fund removed.
1414 -- 2) Added the five columns , start_period_name, end_period_name, accrual_quantity,
1415 -- accrue_to_level_id,fund_calendar
1416 -- 3) Introduced new API check_fund-inter_entity
1417 -- 4) Removed some of the fund rules validations to package OzfFundRulesPvt (because this Api was getging bigger)
1418 -- 5) Removed all active fund transactions to package OzfFundRulesPvt
1419 -- 6) Added approval and other fully accrued fund related transactions
1420 --
1421 **********************************************************************/
1422
1423
1424
1425 ----------------------------------------------------------------------
1426 PROCEDURE update_fund(
1427 p_api_version IN NUMBER
1428 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1429 ,p_commit IN VARCHAR2 := fnd_api.g_false
1430 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1431 ,x_return_status OUT NOCOPY VARCHAR2
1432 ,x_msg_count OUT NOCOPY NUMBER
1433 ,x_msg_data OUT NOCOPY VARCHAR2
1434 ,p_fund_rec IN fund_rec_type
1435 ,p_mode IN VARCHAR2 := jtf_plsql_api.g_update)
1436 IS
1437 l_api_version CONSTANT NUMBER := 1.0;
1438 l_api_name CONSTANT VARCHAR2(30)
1439 := 'Update_Fund';
1440 l_full_name CONSTANT VARCHAR2(60)
1441 := g_pkg_name || '.' || l_api_name;
1442 l_fund_rec fund_rec_type;
1443 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
1444 l_mode VARCHAR2(30);
1445 l_request_id NUMBER;
1446 /* R12: yzhao bug 4669269 - obsolete ozf_mc_transactions
1447 l_mc_obj_number NUMBER;
1448 l_mc_record_id NUMBER;
1449 l_mc_transaction_rec ozf_mc_transactions_pvt.mc_transactions_rec_type;
1450 */
1451 --- variable for creating access //mpande
1452 l_access_rec ams_access_pvt.access_rec_type;
1453 l_access_id NUMBER;
1454 l_act_access_id NUMBER;
1455 l_acc_obj_ver_num NUMBER;
1456 l_par_fund_owner NUMBER;
1457 -- l_request_rec ozf_fund_request_pvt.request_rec_type;
1458 l_status_code VARCHAR2(30);
1459 -- 08/14/2001 mpande added
1460 l_submit_budget_approval VARCHAR2(1):= FND_API.g_false;
1461 l_submit_child_approval VARCHAR2(1):= FND_API.g_false;
1462 l_approver_id NUMBER;
1463 l_workflow_process VARCHAR2(30) := 'AMSGAPP';
1464 l_item_type VARCHAR2(30) := 'AMSGAPP';
1465 l_old_user_status_id NUMBER;
1466 l_status_type VARCHAR2(30) := 'OZF_FUND_STATUS';
1467 l_is_requestor_owner VARCHAR2(1);
1468 l_reject_status_id NUMBER;
1469 l_child_request_amt NUMBER ; -- mpande 11/06/2001 added
1470
1471 -- CURSOR for old status code //updated by mpande
1472 CURSOR c_old_status(
1473 cv_fund_id IN NUMBER)
1474 IS
1475 SELECT status_code
1476 ,parent_fund_id
1477 ,user_status_id
1478 FROM ozf_funds_all_b
1479 WHERE fund_id = cv_fund_id;
1480
1481 l_old_status VARCHAR2(30);
1482 l_old_parent_fund_id NUMBER;
1483 l_old_par_fund_owner NUMBER;
1484
1485 --added by mpande cursor to get parent fund owner
1486 CURSOR c_par_fund_owner(
1487 par_fund_id IN NUMBER)
1488 IS
1489 SELECT owner
1490 FROM ozf_funds_all_b
1491 WHERE fund_id = par_fund_id;
1492
1493 /* R12: yzhao bug 4669269 - obsolete ozf_mc_transactions
1494 CURSOR c_mc_record(
1495 source_id IN NUMBER)
1496 IS
1497 SELECT mc_record_id
1498 ,object_version_number
1499 FROM ozf_mc_transactions_all
1500 WHERE source_object_id = source_id
1501 AND source_object_name = 'FUND';
1502 */
1503
1504 CURSOR c_access(
1505 p_fund_id IN NUMBER)
1506 IS
1507 SELECT activity_access_id
1508 ,object_version_number
1509 FROM ams_act_access
1510 WHERE act_access_to_object_id = p_fund_id
1511 AND arc_act_access_to_object = 'FUND'
1512 AND owner_flag = 'Y';
1513
1514 CURSOR c_par_access(
1515 p_fund_id IN NUMBER
1516 ,p_par_owner_id IN NUMBER)
1517 IS
1518 SELECT activity_access_id
1519 ,object_version_number
1520 FROM ams_act_access
1521 WHERE act_access_to_object_id = p_fund_id
1522 AND arc_act_access_to_object = 'FUND'
1523 AND arc_user_or_role_type = 'USER'
1524 AND user_or_role_id = p_par_owner_id
1525 -- 09/05/2001 mpande
1526 AND NVL(owner_flag,'N') = 'N' ;
1527
1528 --- cursor to get old (TC) currency_code
1529 CURSOR c_old_curr(
1530 cv_fund_id IN NUMBER)
1531 IS
1532 SELECT currency_code_tc , owner
1533 FROM ozf_funds_all_b
1534 WHERE fund_id = cv_fund_id;
1535
1536 l_old_curr VARCHAR2(30);
1537 l_rate NUMBER;
1538 l_owner NUMBER;
1539
1540 -- added by feliu for rollup amount updating.
1541 CURSOR c_amt IS
1542 SELECT planned_amt,committed_amt,
1543 earned_amt,paid_amt,transfered_in_amt
1544 ,transfered_out_amt,original_budget
1545 ,recal_committed,holdback_amt
1546 ,utilized_amt -- yzhao: 11.5.10
1547 FROM ozf_funds_all_b
1548 WHERE fund_id = p_fund_rec.fund_id;
1549
1550 CURSOR c_rollup_amt IS
1551 SELECT rollup_planned_amt,rollup_committed_amt
1552 ,rollup_earned_amt,rollup_paid_amt,rollup_transfered_in_amt
1553 ,rollup_transfered_out_amt,rollup_original_budget
1554 ,rollup_recal_committed,rollup_holdback_amt
1555 ,rollup_utilized_amt -- yzhao: 11.5.10
1556 FROM ozf_funds_all_b
1557 WHERE fund_id = p_fund_rec.fund_id;
1558
1559 /* kdass - R12 MOAC changes
1560 CURSOR c_get_org_id IS
1561 SELECT org_id
1562 FROM ozf_funds_all_b
1563 WHERE fund_id = p_fund_rec.fund_id;
1564 */
1565
1566 l_original_budget NUMBER;
1567 l_old_original_budget NUMBER;
1568 l_old_transfered_in_amt NUMBER;
1569 l_old_transfered_out_amt NUMBER;
1570 l_old_holdback_amt NUMBER;
1571 l_old_planned_amt NUMBER;
1572 l_old_committed_amt NUMBER;
1573 l_old_utilized_amt NUMBER; -- yzhao: 11.5.10
1574 l_old_earned_amt NUMBER;
1575 l_old_paid_amt NUMBER;
1576 l_old_recal_committed NUMBER;
1577
1578
1579 l_or_original_budget NUMBER;
1580 l_or_transfered_in_amt NUMBER;
1581 l_or_transfered_out_amt NUMBER;
1582 l_or_holdback_amt NUMBER;
1583 l_or_planned_amt NUMBER;
1584 l_or_committed_amt NUMBER;
1585 l_or_utilized_amt NUMBER; -- yzhao: 11.5.10
1586 l_or_earned_amt NUMBER;
1587 l_or_paid_amt NUMBER;
1588 l_or_recal_committed NUMBER;
1589
1590 l_rollup_original_budget NUMBER;
1591 l_rollup_transfered_in_amt NUMBER;
1592 l_rollup_transfered_out_amt NUMBER;
1593 l_rollup_holdback_amt NUMBER;
1594 l_rollup_planned_amt NUMBER;
1595 l_rollup_committed_amt NUMBER;
1596 l_rollup_utilized_amt NUMBER; -- yzhao: 11.5.10
1597 l_rollup_earned_amt NUMBER;
1598 l_rollup_paid_amt NUMBER;
1599 l_rollup_recal_committed NUMBER;
1600 l_active_flag BOOLEAN := false;
1601 l_allocation_flag VARCHAR2(2) := 'N'; -- yzhao: 03/18/2003 added
1602
1603 l_tmp_status_code VARCHAR2(30);
1604 l_tmp_status_id NUMBER;
1605
1606 --Added for bug 7425189
1607 l_fund_reconc_msg VARCHAR2(4000);
1608 l_act_bud_cst_msg VARCHAR2(4000);
1609
1610 BEGIN
1611 -------------------- initialize -------------------------
1612 SAVEPOINT update_fund;
1613 IF G_DEBUG THEN
1614 ozf_utility_pvt.debug_message(l_full_name || ': start');
1615 END IF;
1616
1617 IF fnd_api.to_boolean(p_init_msg_list) THEN
1618 fnd_msg_pub.initialize;
1619 END IF;
1620
1621 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1622 RAISE fnd_api.g_exc_unexpected_error;
1623 END IF;
1624
1625 x_return_status := fnd_api.g_ret_sts_success;
1626
1627 -- //added on Sep20 for user status validation
1628 complete_fund_rec(p_fund_rec, l_fund_rec);
1629 -- check fund status and fill in system status
1630 IF p_fund_rec.user_status_id <> fnd_api.g_miss_num THEN
1631 handle_fund_status(
1632 p_user_status_id => p_fund_rec.user_status_id
1633 ,x_status_code => l_status_code
1634 ,x_return_status => x_return_status);
1635
1636 IF x_return_status = fnd_api.g_ret_sts_error THEN
1637 RAISE fnd_api.g_exc_error;
1638 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1639 RAISE fnd_api.g_exc_unexpected_error;
1640 END IF;
1641
1642 l_fund_rec.status_code := l_status_code;
1643 ELSIF p_fund_rec.status_code <> fnd_api.g_miss_char THEN
1644 IF G_DEBUG THEN
1645 ozf_utility_pvt.debug_message(l_full_name || 'debug' || p_fund_rec.status_code);
1646 END IF;
1647 get_user_status(
1648 p_status_code => p_fund_rec.status_code
1649 ,x_user_status_id => l_fund_rec.user_status_id
1650 ,x_return_status => x_return_status);
1651
1652 IF x_return_status = fnd_api.g_ret_sts_error THEN
1653 RAISE fnd_api.g_exc_error;
1654 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1655 RAISE fnd_api.g_exc_unexpected_error;
1656 END IF;
1657 --- this we need because there could be some records in the database with no user status
1658 ELSE
1659 IF G_DEBUG THEN
1660 ozf_utility_pvt.debug_message(l_full_name || 'in else ');
1661 END IF;
1662
1663 IF l_fund_rec.user_status_id IS NOT NULL THEN
1664 handle_fund_status(
1665 p_user_status_id => l_fund_rec.user_status_id
1666 ,x_status_code => l_status_code
1667 ,x_return_status => x_return_status);
1668
1669 IF x_return_status = fnd_api.g_ret_sts_error THEN
1670 RAISE fnd_api.g_exc_error;
1671 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1672 RAISE fnd_api.g_exc_unexpected_error;
1673 END IF;
1674 ELSE
1675 -- if user_status_id is null in database then we will populate it with the corrsponnding system status value form user_status_table
1676 get_user_status(
1677 p_status_code => l_fund_rec.status_code
1678 ,x_user_status_id => l_fund_rec.user_status_id
1679 ,x_return_status => x_return_status);
1680
1681 IF x_return_status = fnd_api.g_ret_sts_error THEN
1682 RAISE fnd_api.g_exc_error;
1683 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1684 RAISE fnd_api.g_exc_unexpected_error;
1685 END IF;
1686 END IF;
1687
1688 l_fund_rec.status_code := l_status_code;
1689 END IF;
1690
1691 --Added for bug 7425189
1692 l_fund_reconc_msg := fnd_message.get_string ('OZF', 'OZF_FUND_RECONCILE');
1693 l_act_bud_cst_msg := fnd_message.get_string ('OZF', 'OZF_ACT_BUDG_CST_UTIL');
1694
1695 --changed by mpande
1696 -- Fetch the old status code and old parentID
1697 OPEN c_old_status(p_fund_rec.fund_id);
1698 FETCH c_old_status INTO l_old_status, l_old_parent_fund_id,l_old_user_status_id;
1699 CLOSE c_old_status;
1700 ----------------------- validate ----------------------
1701 IF G_DEBUG THEN
1702 ozf_utility_pvt.debug_message(l_full_name || ': validate');
1703 END IF;
1704 -- replace g_miss_char/num/date with current column values
1705 --added by mpande 27th JULY-2000
1706 ---if the fund is active and the currency_code_tc passed is different than the fund_currency_code
1707 -- then this is either a transfer of fund or utlization record or updation of planned amount .
1708 -- The amount could only be updated in fund currency
1709 -- All the amounts passed should be converted to the fund_currency and then updated
1710
1711 OPEN c_old_curr(p_fund_rec.fund_id);
1712 FETCH c_old_curr INTO l_old_curr,l_owner;
1713 CLOSE c_old_curr;
1714 ----dbms_output.put_line ('OLD CURR = '||l_old_curr);
1715 ----dbms_output.put_line ('PASSED CURR = '||p_fund_rec.currency_code_tc);
1716 IF G_DEBUG THEN
1717 ozf_utility_pvt.debug_message(l_full_name || ': convert currrency');
1718 END IF;
1719 -- changed the status call here to check for the changed status
1720 IF l_fund_rec.status_code <> 'DRAFT' AND l_old_status <> 'DRAFT'
1721 AND l_old_curr <> l_fund_rec.currency_code_tc THEN
1722 IF p_fund_rec.original_budget IS NOT NULL
1723 AND p_fund_rec.original_budget <> fnd_api.g_miss_num THEN
1724
1725 --nirprasa, added for bug 7425189
1726 IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1727 AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1728 Ozf_utility_pvt.convert_currency(
1729 x_return_status => l_return_status
1730 ,p_from_currency => p_fund_rec.currency_code_tc
1731 ,p_to_currency => l_old_curr
1732 ,p_conv_date => p_fund_rec.exchange_rate_date
1733 ,p_from_amount => p_fund_rec.original_budget
1734 ,x_to_amount => l_fund_rec.original_budget
1735 ,x_rate => l_rate);
1736 ELSE
1737 Ozf_utility_pvt.convert_currency(
1738 x_return_status => l_return_status
1739 ,p_from_currency => p_fund_rec.currency_code_tc
1740 ,p_to_currency => l_old_curr
1741 ,p_from_amount => p_fund_rec.original_budget
1742 ,x_to_amount => l_fund_rec.original_budget
1743 ,x_rate => l_rate);
1744 END IF;
1745
1746 -- we need to pass the fund currency after calculating
1747 l_fund_rec.currency_code_tc := l_old_curr;
1748
1749 ----dbms_output.put_line ('DEBUG ');
1750 IF l_return_status = fnd_api.g_ret_sts_error THEN
1751 RAISE fnd_api.g_exc_error;
1752 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1753 RAISE fnd_api.g_exc_unexpected_error;
1754 END IF;
1755 END IF;
1756
1757 IF p_fund_rec.transfered_in_amt IS NOT NULL
1758 AND p_fund_rec.transfered_in_amt <> fnd_api.g_miss_num THEN
1759 --nirprasa, added for bug 7425189
1760 IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1761 AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1762 Ozf_utility_pvt.convert_currency(
1763 x_return_status => l_return_status
1764 ,p_from_currency => p_fund_rec.currency_code_tc
1765 ,p_to_currency => l_old_curr
1766 ,p_conv_date => p_fund_rec.exchange_rate_date
1767 ,p_from_amount => p_fund_rec.transfered_in_amt
1768 ,x_to_amount => l_fund_rec.transfered_in_amt
1769 ,x_rate => l_rate);
1770 ELSE
1771 Ozf_utility_pvt.convert_currency(
1772 x_return_status => l_return_status
1773 ,p_from_currency => p_fund_rec.currency_code_tc
1774 ,p_to_currency => l_old_curr
1775 ,p_from_amount => p_fund_rec.transfered_in_amt
1776 ,x_to_amount => l_fund_rec.transfered_in_amt
1777 ,x_rate => l_rate);
1778 END IF;
1779 -- we need to pass the fund currency after calculating
1780 l_fund_rec.currency_code_tc := l_old_curr;
1781
1782 IF l_return_status = fnd_api.g_ret_sts_error THEN
1783 RAISE fnd_api.g_exc_error;
1784 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1785 RAISE fnd_api.g_exc_unexpected_error;
1786 END IF;
1787 END IF;
1788
1789 IF p_fund_rec.transfered_out_amt IS NOT NULL
1790 AND p_fund_rec.transfered_out_amt <> fnd_api.g_miss_num THEN
1791
1792 --nirprasa, added for bug 7425189
1793 IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1794 AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1795 Ozf_utility_pvt.convert_currency(
1796 x_return_status => l_return_status
1797 ,p_from_currency => p_fund_rec.currency_code_tc
1798 ,p_to_currency => l_old_curr
1799 ,p_conv_date => p_fund_rec.exchange_rate_date
1800 ,p_from_amount => p_fund_rec.transfered_in_amt
1801 ,x_to_amount => l_fund_rec.transfered_in_amt
1802 ,x_rate => l_rate);
1803 ELSE
1804 Ozf_utility_pvt.convert_currency(
1805 x_return_status => l_return_status
1806 ,p_from_currency => p_fund_rec.currency_code_tc
1807 ,p_to_currency => l_old_curr
1808 ,p_from_amount => p_fund_rec.transfered_in_amt
1809 ,x_to_amount => l_fund_rec.transfered_in_amt
1810 ,x_rate => l_rate);
1811 END IF;
1812 -- we need to pass the fund currency after calculating
1813 l_fund_rec.currency_code_tc := l_old_curr;
1814
1815 IF l_return_status = fnd_api.g_ret_sts_error THEN
1816 RAISE fnd_api.g_exc_error;
1817 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1818 RAISE fnd_api.g_exc_unexpected_error;
1819 END IF;
1820 END IF;
1821
1822 IF p_fund_rec.planned_amt IS NOT NULL
1823 AND p_fund_rec.planned_amt <> fnd_api.g_miss_num THEN
1824
1825 --nirprasa, added for bug 7425189
1826 IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1827 AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1828 Ozf_utility_pvt.convert_currency(
1829 x_return_status => l_return_status
1830 ,p_from_currency => p_fund_rec.currency_code_tc
1831 ,p_to_currency => l_old_curr
1832 ,p_conv_date => p_fund_rec.exchange_rate_date
1833 ,p_from_amount => p_fund_rec.planned_amt
1834 ,x_to_amount => l_fund_rec.planned_amt
1835 ,x_rate => l_rate);
1836 ELSE
1837 Ozf_utility_pvt.convert_currency(
1838 x_return_status => l_return_status
1839 ,p_from_currency => p_fund_rec.currency_code_tc
1840 ,p_to_currency => l_old_curr
1841 ,p_from_amount => p_fund_rec.planned_amt
1842 ,x_to_amount => l_fund_rec.planned_amt
1843 ,x_rate => l_rate);
1844 END IF;
1845
1846 -- we need to pass the fund currency after calculating
1847 l_fund_rec.currency_code_tc := l_old_curr;
1848
1849 IF l_return_status = fnd_api.g_ret_sts_error THEN
1850 RAISE fnd_api.g_exc_error;
1851 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1852 RAISE fnd_api.g_exc_unexpected_error;
1853 END IF;
1854 END IF;
1855
1856 -- yzhao: 11.5.10
1857 IF p_fund_rec.utilized_amt IS NOT NULL
1858 AND p_fund_rec.utilized_amt <> fnd_api.g_miss_num THEN
1859
1860 --nirprasa, added for bug 7425189
1861 IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1862 AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1863 Ozf_utility_pvt.convert_currency(
1864 x_return_status => l_return_status
1865 ,p_from_currency => p_fund_rec.currency_code_tc
1866 ,p_to_currency => l_old_curr
1867 ,p_conv_date => p_fund_rec.exchange_rate_date
1868 ,p_from_amount => p_fund_rec.utilized_amt
1869 ,x_to_amount => l_fund_rec.utilized_amt
1870 ,x_rate => l_rate);
1871 ELSE
1872 Ozf_utility_pvt.convert_currency(
1873 x_return_status => l_return_status
1874 ,p_from_currency => p_fund_rec.currency_code_tc
1875 ,p_to_currency => l_old_curr
1876 ,p_from_amount => p_fund_rec.utilized_amt
1877 ,x_to_amount => l_fund_rec.utilized_amt
1878 ,x_rate => l_rate);
1879 END IF;
1880 -- we need to pass the fund currency after calculating
1881 l_fund_rec.currency_code_tc := l_old_curr;
1882
1883 IF l_return_status = fnd_api.g_ret_sts_error THEN
1884 RAISE fnd_api.g_exc_error;
1885 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1886 RAISE fnd_api.g_exc_unexpected_error;
1887 END IF;
1888 END IF;
1889
1890 IF p_fund_rec.earned_amt IS NOT NULL
1891 AND p_fund_rec.earned_amt <> fnd_api.g_miss_num THEN
1892
1893 --nirprasa, added for bug 7425189
1894 IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1895 AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1896 Ozf_utility_pvt.convert_currency(
1897 x_return_status => l_return_status
1898 ,p_from_currency => p_fund_rec.currency_code_tc
1899 ,p_to_currency => l_old_curr
1900 ,p_conv_date => p_fund_rec.exchange_rate_date
1901 ,p_from_amount => p_fund_rec.earned_amt
1902 ,x_to_amount => l_fund_rec.earned_amt
1903 ,x_rate => l_rate);
1904 ELSE
1905 Ozf_utility_pvt.convert_currency(
1906 x_return_status => l_return_status
1907 ,p_from_currency => p_fund_rec.currency_code_tc
1908 ,p_to_currency => l_old_curr
1909 ,p_from_amount => p_fund_rec.earned_amt
1910 ,x_to_amount => l_fund_rec.earned_amt
1911 ,x_rate => l_rate);
1912 END IF;
1913 -- we need to pass the fund currency after calculating
1914 l_fund_rec.currency_code_tc := l_old_curr;
1915
1916 IF l_return_status = fnd_api.g_ret_sts_error THEN
1917 RAISE fnd_api.g_exc_error;
1918 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1919 RAISE fnd_api.g_exc_unexpected_error;
1920 END IF;
1921 END IF;
1922
1923 IF p_fund_rec.committed_amt IS NOT NULL
1924 AND p_fund_rec.committed_amt <> fnd_api.g_miss_num THEN
1925
1926 --nirprasa, added for bug 7425189
1927 IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1928 AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1929 Ozf_utility_pvt.convert_currency(
1930 x_return_status => l_return_status
1931 ,p_from_currency => p_fund_rec.currency_code_tc
1932 ,p_to_currency => l_old_curr
1933 ,p_conv_date => p_fund_rec.exchange_rate_date
1934 ,p_from_amount => p_fund_rec.committed_amt
1935 ,x_to_amount => l_fund_rec.committed_amt
1936 ,x_rate => l_rate);
1937 ELSE
1938 Ozf_utility_pvt.convert_currency(
1939 x_return_status => l_return_status
1940 ,p_from_currency => p_fund_rec.currency_code_tc
1941 ,p_to_currency => l_old_curr
1942 ,p_from_amount => p_fund_rec.committed_amt
1943 ,x_to_amount => l_fund_rec.committed_amt
1944 ,x_rate => l_rate);
1945 END IF;
1946 -- we need to pass the fund currency after calculating
1947 l_fund_rec.currency_code_tc := l_old_curr;
1948
1949 IF l_return_status = fnd_api.g_ret_sts_error THEN
1950 RAISE fnd_api.g_exc_error;
1951 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1952 RAISE fnd_api.g_exc_unexpected_error;
1953 END IF;
1954 END IF;
1955
1956 IF p_fund_rec.paid_amt IS NOT NULL
1957 AND p_fund_rec.paid_amt <> fnd_api.g_miss_num THEN
1958
1959 --nirprasa, added for bug 7425189
1960 IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1961 AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1962 Ozf_utility_pvt.convert_currency(
1963 x_return_status => l_return_status
1964 ,p_from_currency => p_fund_rec.currency_code_tc
1965 ,p_to_currency => l_old_curr
1966 ,p_conv_date => p_fund_rec.exchange_rate_date
1967 ,p_from_amount => p_fund_rec.paid_amt
1968 ,x_to_amount => l_fund_rec.paid_amt
1969 ,x_rate => l_rate);
1970 ELSE
1971 Ozf_utility_pvt.convert_currency(
1972 x_return_status => l_return_status
1973 ,p_from_currency => p_fund_rec.currency_code_tc
1974 ,p_to_currency => l_old_curr
1975 ,p_from_amount => p_fund_rec.paid_amt
1976 ,x_to_amount => l_fund_rec.paid_amt
1977 ,x_rate => l_rate);
1978 END IF;
1979 -- we need to pass the fund currency after calculating
1980 l_fund_rec.currency_code_tc := l_old_curr;
1981
1982 IF l_return_status = fnd_api.g_ret_sts_error THEN
1983 RAISE fnd_api.g_exc_error;
1984 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1985 RAISE fnd_api.g_exc_unexpected_error;
1986 END IF;
1987 END IF;
1988 END IF;
1989
1990 -- default fund_calendar
1991 IF l_fund_rec.start_period_name IS NULL
1992 AND l_fund_rec.end_period_name IS NULL THEN
1993 l_fund_rec.fund_calendar := NULL;
1994 ELSE
1995 l_fund_rec.fund_calendar := fnd_profile.VALUE('AMS_CAMPAIGN_DEFAULT_CALENDER');
1996 END IF;
1997
1998 -- generate source code
1999 IF p_fund_rec.parent_fund_id IS NOT NULL
2000 AND p_fund_rec.parent_fund_id <> fnd_api.g_miss_num
2001 AND p_fund_rec.fund_number IS NULL THEN
2002 get_child_source_code(
2003 p_fund_rec.parent_fund_id
2004 ,l_fund_rec.fund_number
2005 ,x_return_status);
2006
2007 IF x_return_status = fnd_api.g_ret_sts_error THEN
2008 RAISE fnd_api.g_exc_error;
2009 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2010 RAISE fnd_api.g_exc_unexpected_error;
2011 END IF;
2012 END IF;
2013
2014 IF p_validation_level >= jtf_plsql_api.g_valid_level_item THEN
2015 ----dbms_output.put_line ('Calling Check_Fund_Items');
2016 check_fund_items(
2017 /* yzhao: 06/03/2003 fix bug 2984497 - TST1159.14 MASTER: BUDGET APPROVAL VALIDATION FAILS UPON APPROVAL IN WORKFLOW
2018 p_fund_rec => p_fund_rec
2019 */
2020 p_fund_rec => l_fund_rec
2021 ,p_validation_mode => jtf_plsql_api.g_update
2022 ,x_return_status => l_return_status);
2023
2024 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2025 RAISE fnd_api.g_exc_unexpected_error;
2026 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2027 RAISE fnd_api.g_exc_error;
2028 END IF;
2029 END IF;
2030
2031 -- record level
2032 IF p_validation_level >= jtf_plsql_api.g_valid_level_record THEN
2033 check_fund_record(
2034 p_fund_rec => p_fund_rec
2035 ,p_complete_rec => l_fund_rec
2036 ,p_mode => p_mode
2037 ,x_return_status => l_return_status);
2038
2039 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2040 RAISE fnd_api.g_exc_unexpected_error;
2041 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2042 RAISE fnd_api.g_exc_error;
2043 END IF;
2044 END IF;
2045
2046 -- inter-entity level
2047 IF G_DEBUG THEN
2048 ozf_utility_pvt.debug_message(l_full_name || ': check inter-entity');
2049 END IF;
2050
2051 IF p_validation_level >= jtf_plsql_api.g_valid_level_inter_entity THEN
2052 check_fund_inter_entity(
2053 p_fund_rec => p_fund_rec
2054 ,p_complete_rec => l_fund_rec
2055 ,p_validation_mode => jtf_plsql_api.g_update
2056 ,x_return_status => l_return_status);
2057
2058 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2059 RAISE fnd_api.g_exc_unexpected_error;
2060 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2061 RAISE fnd_api.g_exc_error;
2062 END IF;
2063 END IF;
2064
2065 -------------------------- update --------------------
2066 IF G_DEBUG THEN
2067 ozf_utility_pvt.debug_message(l_full_name || ': update');
2068
2069 ozf_utility_pvt.debug_message(l_full_name || ': get_category');
2070 END IF;
2071
2072 -- kdass 09-NOV-05 Bug 4618523
2073 /*
2074 --added by mpande 6th JULY-2000
2075 --get default category GL Info not for statistical funds
2076 IF p_fund_rec.category_id <> fnd_api.g_miss_num
2077 AND l_fund_rec.category_id IS NOT NULL THEN
2078 complete_default_gl_info(
2079 l_fund_rec.category_id
2080 ,l_fund_rec.accrued_liable_account
2081 ,l_fund_rec.ded_adjustment_account
2082 ,l_return_status);
2083
2084 IF l_return_status = fnd_api.g_ret_sts_error THEN
2085 RAISE fnd_api.g_exc_error;
2086 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2087 RAISE fnd_api.g_exc_unexpected_error;
2088 END IF;
2089 END IF;
2090 */
2091
2092 IF G_DEBUG THEN
2093 ozf_utility_pvt.debug_message(l_full_name || 'before update status' ||l_fund_rec.original_budget);
2094 END IF;
2095 -- perform all status related updation and if it is successful then only update the fund record
2096 -- Update fund could be called by other APIS in active status to update earned amt or committed amt .
2097 -- We do allow them to update the record then
2098 IF l_return_status = fnd_api.g_ret_sts_success THEN
2099 IF p_mode IN (jtf_PLSQL_API.G_UPDATE, 'WORKFLOW') THEN
2100 Ozf_fundrules_pvt.update_fund_status(
2101 p_fund_rec => l_fund_rec
2102 /* yzhao: 11/26/2002 how weird to pass IN parameter l_fund_rec, and use member as OUT parameter
2103 most importantly, it breaks with NOCOPY hint
2104 ,x_new_status_code => l_fund_rec.status_code
2105 ,x_new_status_id => l_fund_rec.user_status_id
2106 */
2107 ,x_new_status_code => l_tmp_status_code
2108 ,x_new_status_id => l_tmp_status_id
2109 ,x_submit_budget_approval => l_submit_budget_approval
2110 ,x_submit_child_approval =>l_submit_child_approval
2111 ,x_return_status => l_return_status
2112 ,x_msg_count => x_msg_count
2113 ,x_msg_data => x_msg_data
2114 ,p_api_version => 1.0);
2115
2116 IF l_return_status = fnd_api.g_ret_sts_error THEN
2117 RAISE fnd_api.g_exc_error;
2118 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2119 RAISE fnd_api.g_exc_unexpected_error;
2120 END IF;
2121
2122 l_fund_rec.status_code := l_tmp_status_code;
2123 l_fund_rec.user_status_id := l_tmp_status_id;
2124
2125 -- sangara added for R12
2126 IF l_tmp_status_code = 'ACTIVE' THEN
2127 l_fund_rec.activation_date := sysdate;
2128 END IF;
2129
2130 END IF;
2131 END IF;
2132
2133 -- 11/02/2001 mpande added for child fund we donot want to update original_budget but transfered_in_amt
2134 -- when no approval required , other the approval API will do the needful
2135 -- 11/06/2001 mpande added
2136 l_child_request_amt := l_fund_rec.original_budget;
2137 -- when setting the status directly to active
2138 IF l_fund_rec.parent_fund_id IS NOT NULL THEN
2139 IF l_fund_rec.status_code = 'ON_HOLD' OR l_fund_rec.status_code = 'ACTIVE' THEN
2140 --OR l_fund_rec.status_code = 'PENDING'
2141 IF l_old_status = 'DRAFT' THEN
2142 l_fund_rec.transfered_in_amt := l_fund_rec.original_budget;
2143 l_fund_rec.original_budget := 0 ;
2144 END IF ;
2145
2146 IF l_old_status = 'PENDING' OR l_old_status = 'DRAFT' THEN
2147 l_active_flag := true;
2148 END IF ;
2149
2150 END IF;
2151 END IF;
2152
2153 OPEN c_amt;
2154 FETCH c_amt INTO
2155 l_old_planned_amt,
2156 l_old_committed_amt,
2157 l_old_earned_amt,
2158 l_old_paid_amt,
2159 l_old_transfered_in_amt ,
2160 l_old_transfered_out_amt ,
2161 l_old_original_budget,
2162 l_old_recal_committed,
2163 l_old_holdback_amt,
2164 l_old_utilized_amt; -- yzhao: 11.5.10
2165 CLOSE c_amt;
2166
2167 OPEN c_rollup_amt;
2168 FETCH c_rollup_amt INTO
2169 l_or_planned_amt,
2170 l_or_committed_amt,
2171 l_or_earned_amt,
2172 l_or_paid_amt,
2173 l_or_transfered_in_amt ,
2174 l_or_transfered_out_amt ,
2175 l_or_original_budget,
2176 l_or_recal_committed,
2177 l_or_holdback_amt,
2178 l_or_utilized_amt;
2179 CLOSE c_rollup_amt;
2180
2181 IF l_fund_rec.original_budget IS NOT NULL
2182 THEN
2183 --nirprasa, added for bug 7425189
2184 IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
2185 AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
2186 Ozf_utility_pvt.convert_currency(
2187 x_return_status => l_return_status
2188 ,p_from_currency => l_fund_rec.currency_code_tc
2189 ,p_to_currency => g_universal_currency
2190 ,p_conv_date => p_fund_rec.exchange_rate_date
2191 ,p_from_amount => l_fund_rec.original_budget
2192 ,x_to_amount => l_original_budget
2193 ,x_rate => l_rate);
2194 ELSE
2195 Ozf_utility_pvt.convert_currency(
2196 x_return_status => l_return_status
2197 ,p_from_currency => l_fund_rec.currency_code_tc
2198 ,p_to_currency => g_universal_currency
2199 ,p_from_amount => l_fund_rec.original_budget
2200 ,x_to_amount => l_original_budget
2201 ,x_rate => l_rate);
2202 END IF;
2203
2204
2205 --l_rollup_original_budget := l_original_budget - NVL(l_or_original_budget,0);
2206 --l_fund_rec.rollup_original_budget := l_original_budget;
2207
2208 l_fund_rec.rollup_original_budget := ozf_utility_pvt.CurrRound((l_fund_rec.original_budget - NVL(l_old_original_budget,0)) * l_rate
2209 ,g_universal_currency) + NVL(l_or_original_budget,0);
2210 l_rollup_original_budget := l_fund_rec.rollup_original_budget - NVL(l_or_original_budget,0);
2211
2212
2213 IF l_return_status = fnd_api.g_ret_sts_error THEN
2214 RAISE fnd_api.g_exc_error;
2215 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2216 RAISE fnd_api.g_exc_unexpected_error;
2217 END IF;
2218
2219 END IF;
2220
2221 IF l_fund_rec.transfered_in_amt IS NOT NULL
2222 AND l_rate is NOT NULL
2223 AND l_fund_rec.transfered_in_amt <> NVL(l_old_transfered_in_amt,0) THEN
2224
2225 l_fund_rec.rollup_transfered_in_amt := ozf_utility_pvt.CurrRound((l_fund_rec.transfered_in_amt - NVL(l_old_transfered_in_amt,0)) * l_rate
2226 ,g_universal_currency) + NVL(l_or_transfered_in_amt,0);
2227 l_rollup_transfered_in_amt := l_fund_rec.rollup_transfered_in_amt - NVL(l_or_transfered_in_amt,0);
2228
2229 END IF;
2230
2231 IF l_fund_rec.transfered_out_amt IS NOT NULL
2232 AND l_rate is NOT NULL
2233 AND l_fund_rec.transfered_out_amt <> NVL(l_old_transfered_out_amt,0) THEN
2234
2235 l_fund_rec.rollup_transfered_out_amt := ozf_utility_pvt.CurrRound((l_fund_rec.transfered_out_amt-NVL(l_old_transfered_out_amt,0)) * l_rate
2236 ,g_universal_currency) + NVL(l_or_transfered_out_amt,0);
2237 l_rollup_transfered_out_amt := l_fund_rec.rollup_transfered_out_amt - NVL(l_or_transfered_out_amt,0);
2238
2239 END IF;
2240
2241
2242 --IF l_fund_rec.holdback_amt IS NOT NULL
2243 --Bug Fix 4087106, Rollup holdback amt not updated when manually updated.
2244 IF l_rate is NOT NULL
2245 AND NVL(l_fund_rec.holdback_amt,0) <> NVL(l_old_holdback_amt,0) THEN
2246
2247 l_fund_rec.rollup_holdback_amt := ozf_utility_pvt.CurrRound((NVL(l_fund_rec.holdback_amt,0)-NVL(l_old_holdback_amt,0)) * l_rate
2248 ,g_universal_currency)+ NVL(l_or_holdback_amt,0);
2249 l_rollup_holdback_amt := l_fund_rec.rollup_holdback_amt - NVL(l_or_holdback_amt,0);
2250
2251 END IF;
2252
2253
2254 IF l_fund_rec.planned_amt IS NOT NULL
2255 AND l_rate is NOT NULL
2256 AND l_fund_rec.planned_amt <> NVL(l_old_planned_amt,0) THEN
2257
2258 l_fund_rec.rollup_planned_amt := ozf_utility_pvt.CurrRound((l_fund_rec.planned_amt-NVL(l_old_planned_amt,0)) * l_rate
2259 ,g_universal_currency)+ NVL(l_or_planned_amt,0);
2260 l_rollup_planned_amt := l_fund_rec.rollup_planned_amt - NVL(l_or_planned_amt,0);
2261
2262 END IF;
2263
2264 IF l_fund_rec.committed_amt IS NOT NULL
2265 AND l_rate is NOT NULL
2266 AND l_fund_rec.committed_amt <> NVL(l_old_committed_amt,0) THEN
2267
2268 l_fund_rec.rollup_committed_amt := ozf_utility_pvt.CurrRound((l_fund_rec.committed_amt-NVL(l_old_committed_amt,0)) * l_rate
2269 ,g_universal_currency) + NVL(l_or_committed_amt,0);
2270 l_rollup_committed_amt :=l_fund_rec.rollup_committed_amt - NVL(l_or_committed_amt,0);
2271
2272 END IF;
2273
2274 IF l_fund_rec.utilized_amt IS NOT NULL
2275 AND l_rate is NOT NULL
2276 AND l_fund_rec.utilized_amt <> NVL(l_old_utilized_amt,0) THEN
2277
2278 l_fund_rec.rollup_utilized_amt := ozf_utility_pvt.CurrRound((l_fund_rec.utilized_amt-NVL(l_old_utilized_amt,0)) * l_rate
2279 ,g_universal_currency) + NVL(l_or_utilized_amt,0);
2280 l_rollup_utilized_amt := l_fund_rec.rollup_utilized_amt - NVL(l_or_utilized_amt,0);
2281
2282 END IF;
2283
2284 IF l_fund_rec.earned_amt IS NOT NULL
2285 AND l_rate is NOT NULL
2286 AND l_fund_rec.earned_amt <> NVL(l_old_earned_amt,0) THEN
2287
2288 l_fund_rec.rollup_earned_amt := ozf_utility_pvt.CurrRound((l_fund_rec.earned_amt-NVL(l_old_earned_amt,0)) * l_rate
2289 ,g_universal_currency) + NVL(l_or_earned_amt,0);
2290 l_rollup_earned_amt := l_fund_rec.rollup_earned_amt - NVL(l_or_earned_amt,0);
2291
2292 END IF;
2293
2294
2295 IF l_fund_rec.paid_amt IS NOT NULL
2296 AND l_rate is NOT NULL
2297 AND l_fund_rec.paid_amt <> NVL(l_old_paid_amt,0) THEN
2298
2299 l_fund_rec.rollup_paid_amt := ozf_utility_pvt.CurrRound((l_fund_rec.paid_amt-NVL(l_old_paid_amt,0)) * l_rate
2300 ,g_universal_currency) +NVL(l_or_paid_amt,0);
2301 l_rollup_paid_amt := l_fund_rec.rollup_paid_amt - NVL(l_or_paid_amt,0);
2302
2303 END IF;
2304
2305 IF l_fund_rec.recal_committed IS NOT NULL
2306 AND l_rate is NOT NULL
2307 AND l_fund_rec.recal_committed <> NVL(l_old_recal_committed,0) THEN
2308
2309 l_fund_rec.rollup_recal_committed := ozf_utility_pvt.CurrRound((l_fund_rec.recal_committed-NVL(l_old_recal_committed,0)) * l_rate
2310 ,g_universal_currency) +NVL(l_or_recal_committed,0);
2311 l_rollup_recal_committed :=l_fund_rec.rollup_recal_committed - NVL(l_or_recal_committed,0);
2312
2313 END IF;
2314
2315 -- added by feliu to fix bug 2654263
2316 IF l_fund_rec.parent_fund_id IS NOT NULL THEN
2317 OPEN c_par_fund_owner(l_fund_rec.parent_fund_id);
2318 FETCH c_par_fund_owner INTO l_par_fund_owner;
2319 CLOSE c_par_fund_owner;
2320 END IF;
2321
2322 -- delete access before update budget because we delete access by loop through tree.
2323 --if the parent fundowner and the child fund owner is not same.
2324 IF l_fund_rec.parent_fund_id IS NOT NULL AND l_fund_rec.owner <> l_par_fund_owner THEN
2325 --if there is no parent fund OR (09/05/2001 mpande) when no records exists in ams_act_access for the fund
2326 --then create a access for the new parent ownner
2327 --if the old parent fund and the new parent fund is diffrent then delete the access
2328 IF l_old_parent_fund_id <> l_fund_rec.parent_fund_id THEN
2329 -- remove old access.
2330 update_funds_access(
2331 p_api_version => l_api_version
2332 ,p_init_msg_list => fnd_api.g_false
2333 ,p_commit => fnd_api.g_false
2334 ,p_validation_level => p_validation_level
2335 ,x_return_status => l_return_status
2336 ,x_msg_count => x_msg_count
2337 ,x_msg_data => x_msg_data
2338 ,p_fund_rec => l_fund_rec
2339 ,p_mode => 'DELETE'
2340 );
2341
2342 IF l_return_status = fnd_api.g_ret_sts_error THEN
2343 RAISE fnd_api.g_exc_error;
2344 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2345 RAISE fnd_api.g_exc_unexpected_error;
2346 END IF;
2347 END IF; -- end of l_old_parent_fund_id <> l_fund_rec.parent_fund_id.
2348 END IF; -- end of l_fund_rec.parent_fund_id IS NOT NULL
2349
2350 -- if removing parent then remove access
2351 IF l_fund_rec.parent_fund_id IS NULL AND l_old_parent_fund_id IS NOT NULL THEN
2352 update_funds_access(
2353 p_api_version => l_api_version
2354 ,p_init_msg_list => fnd_api.g_false
2355 ,p_commit => fnd_api.g_false
2356 ,p_validation_level => p_validation_level
2357 ,x_return_status => l_return_status
2358 ,x_msg_count => x_msg_count
2359 ,x_msg_data => x_msg_data
2360 ,p_fund_rec => l_fund_rec
2361 ,p_mode => 'DELETE'
2362 );
2363
2364 IF l_return_status = fnd_api.g_ret_sts_error THEN
2365 RAISE fnd_api.g_exc_error;
2366 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2367 RAISE fnd_api.g_exc_unexpected_error;
2368 END IF;
2369
2370 END IF; -- l_fund_rec.parent_fund_id
2371 -- added by feliu to fix bug 2654263
2372
2373 -- feliu 04/08/04 to fix bug 3551038 to update status_date.
2374 IF l_fund_rec.status_code <> l_old_status THEN
2375 l_fund_rec.status_date := SYSDATE;
2376 END IF;
2377
2378
2379 UPDATE ozf_funds_all_b
2380 SET last_update_date = SYSDATE
2381 ,last_updated_by = NVL(fnd_global.user_id, -1)
2382 ,last_update_login = NVL(fnd_global.conc_login_id, -1)
2383 ,created_from = NULL
2384 ,request_id = fnd_global.conc_request_id
2385 ,program_application_id = fnd_global.prog_appl_id
2386 ,program_id = fnd_global.conc_program_id
2387 ,program_update_date = SYSDATE
2388 ,fund_number = l_fund_rec.fund_number
2389 ,parent_fund_id = l_fund_rec.parent_fund_id
2390 ,category_id = l_fund_rec.category_id
2391 ,fund_type = l_fund_rec.fund_type
2392 ,fund_usage = l_fund_rec.fund_usage
2393 ,status_code = l_fund_rec.status_code
2394 ,user_status_id = l_fund_rec.user_status_id
2395 ,status_date = NVL(l_fund_rec.status_date, SYSDATE)
2396 ,accrued_liable_account = l_fund_rec.accrued_liable_account
2397 ,ded_adjustment_account = l_fund_rec.ded_adjustment_account
2398 ,liability_flag = l_fund_rec.liability_flag
2399 ,set_of_books_id = l_fund_rec.set_of_books_id
2400 ,start_period_id = l_fund_rec.start_period_id
2401 ,end_period_id = l_fund_rec.end_period_id
2402 ,start_date_active = l_fund_rec.start_date_active
2403 ,end_date_active = l_fund_rec.end_date_active
2404 ,budget_amount_tc = l_fund_rec.budget_amount_tc
2405 ,budget_amount_fc = l_fund_rec.budget_amount_fc
2406 ,available_amount = l_fund_rec.available_amount
2407 ,distributed_amount = l_fund_rec.distributed_amount
2408 ,currency_code_tc = l_fund_rec.currency_code_tc
2409 ,currency_code_fc = l_fund_rec.currency_code_fc
2410 ,exchange_rate_type = l_fund_rec.exchange_rate_type
2411 ,exchange_rate_date = l_fund_rec.exchange_rate_date
2412 ,exchange_rate = l_fund_rec.exchange_rate
2413 ,department_id = l_fund_rec.department_id
2414 ,costcentre_id = l_fund_rec.costcentre_id
2415 ,owner = l_fund_rec.owner
2416 ,accrual_method = l_fund_rec.accrual_method
2417 ,accrual_operand = l_fund_rec.accrual_operand
2418 ,accrual_rate = l_fund_rec.accrual_rate
2419 ,accrual_basis = l_fund_rec.accrual_basis
2420 ,hierarchy = l_fund_rec.hierarchy
2421 ,hierarchy_level = l_fund_rec.hierarchy_level
2422 ,hierarchy_id = l_fund_rec.hierarchy_id
2423 ,parent_node_id = l_fund_rec.parent_node_id
2424 ,node_id = l_fund_rec.node_id --,level_value = l_fund_rec.level_value
2425 ,budget_flag = l_fund_rec.budget_flag
2426 ,earned_flag = l_fund_rec.earned_flag
2427 ,apply_accrual_on = l_fund_rec.apply_accrual_on
2428 ,accrual_phase = l_fund_rec.accrual_phase
2429 ,accrual_cap = l_fund_rec.accrual_cap
2430 ,accrual_uom = l_fund_rec.accrual_uom
2431 ,object_version_number = l_fund_rec.object_version_number + 1
2432 ,recal_committed = l_fund_rec.recal_committed
2433 ,attribute_category = l_fund_rec.attribute_category
2434 ,attribute1 = l_fund_rec.attribute1
2435 ,attribute2 = l_fund_rec.attribute2
2436 ,attribute3 = l_fund_rec.attribute3
2437 ,attribute4 = l_fund_rec.attribute4
2438 ,attribute5 = l_fund_rec.attribute5
2439 ,attribute6 = l_fund_rec.attribute6
2440 ,attribute7 = l_fund_rec.attribute7
2441 ,attribute8 = l_fund_rec.attribute8
2442 ,attribute9 = l_fund_rec.attribute9
2443 ,attribute10 = l_fund_rec.attribute10
2444 ,attribute11 = l_fund_rec.attribute11
2445 ,attribute12 = l_fund_rec.attribute12
2446 ,attribute13 = l_fund_rec.attribute13
2447 ,attribute14 = l_fund_rec.attribute14
2448 ,attribute15 = l_fund_rec.attribute15
2449 ,original_budget = l_fund_rec.original_budget
2450 ,transfered_in_amt = l_fund_rec.transfered_in_amt
2451 ,transfered_out_amt = l_fund_rec.transfered_out_amt
2452 ,holdback_amt = l_fund_rec.holdback_amt
2453 ,planned_amt = l_fund_rec.planned_amt
2454 ,committed_amt = l_fund_rec.committed_amt
2455 ,earned_amt = l_fund_rec.earned_amt
2456 ,paid_amt = l_fund_rec.paid_amt
2457 ,plan_type = l_fund_rec.plan_type
2458 ,plan_id = l_fund_rec.plan_id
2459 ,liable_accnt_segments = l_fund_rec.liable_accnt_segments
2460 ,adjustment_accnt_segments = l_fund_rec.adjustment_accnt_segments
2461 ,fund_calendar = l_fund_rec.fund_calendar
2462 ,start_period_name = l_fund_rec.start_period_name
2463 ,end_period_name = l_fund_rec.end_period_name
2464 ,accrual_quantity = l_fund_rec.accrual_quantity
2465 ,accrue_to_level_id = l_fund_rec.accrue_to_level_id
2466 ,accrual_discount_level = l_fund_rec.accrual_discount_level
2467 ,custom_setup_id = l_fund_rec.custom_setup_id
2468 ,threshold_id = l_fund_rec.threshold_id
2469 ,business_unit_id = l_fund_rec.business_unit_id
2470 ,country_id = l_fund_rec.country_id
2471 ,task_id = l_fund_rec.task_id
2472 ,rollup_original_budget = l_fund_rec.rollup_original_budget
2473 ,rollup_transfered_in_amt = l_fund_rec.rollup_transfered_in_amt
2474 ,rollup_transfered_out_amt = l_fund_rec.rollup_transfered_out_amt
2475 ,rollup_holdback_amt = l_fund_rec.rollup_holdback_amt
2476 ,rollup_planned_amt = l_fund_rec.rollup_planned_amt
2477 ,rollup_committed_amt = l_fund_rec.rollup_committed_amt
2478 ,rollup_earned_amt = l_fund_rec.rollup_earned_amt
2479 ,rollup_paid_amt = l_fund_rec.rollup_paid_amt
2480 ,rollup_recal_committed = l_fund_rec.rollup_recal_committed
2481 ,retroactive_flag = l_fund_rec.retroactive_flag
2482 ,qualifier_id = l_fund_rec.qualifier_id
2483 -- niprakas added
2484 ,prev_fund_id = l_fund_rec.prev_fund_id
2485 ,transfered_flag = l_fund_rec.transfered_flag
2486 ,utilized_amt = l_fund_rec.utilized_amt
2487 ,rollup_utilized_amt = l_fund_rec.rollup_utilized_amt
2488 ,product_spread_time_id = l_fund_rec.product_spread_time_id
2489 -- sangara added
2490 ,activation_date = l_fund_rec.activation_date
2491 -- kdass - R12 MOAC changes
2492 ,ledger_id = l_fund_rec.ledger_id
2493 WHERE fund_id = l_fund_rec.fund_id
2494 AND object_version_number = l_fund_rec.object_version_number;
2495
2496 IF (SQL%NOTFOUND) THEN
2497 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2498 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
2499 fnd_msg_pub.add;
2500 END IF;
2501
2502 RAISE fnd_api.g_exc_error;
2503 END IF;
2504
2505 UPDATE ozf_funds_all_tl
2506 SET last_update_date = SYSDATE
2507 ,last_updated_by = NVL(fnd_global.user_id, -1)
2508 ,last_update_login = NVL(fnd_global.conc_login_id, -1)
2509 ,created_from = NULL
2510 ,request_id = fnd_global.conc_request_id
2511 ,program_application_id = fnd_global.prog_appl_id
2512 ,program_id = fnd_global.conc_program_id
2513 ,program_update_date = SYSDATE
2514 ,short_name = l_fund_rec.short_name
2515 ,description = l_fund_rec.description
2516 ,source_lang = USERENV('LANG')
2517 WHERE fund_id = l_fund_rec.fund_id
2518 AND USERENV('LANG') IN(language, source_lang);
2519
2520 IF (SQL%NOTFOUND) THEN
2521 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2522 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
2523 fnd_msg_pub.add;
2524 END IF;
2525
2526 RAISE fnd_api.g_exc_error;
2527 END IF;
2528
2529 IF l_return_status = fnd_api.g_ret_sts_success THEN
2530
2531 -- added by feliu for updating rollup amount columns.
2532 -- if parent_fund_id is not null, update parent's rollup amount by using this fund's
2533 -- rollup amount change.
2534 --dbms_output.put_line('parent_fund_id '||l_fund_rec.parent_fund_id);
2535
2536 IF l_fund_rec.status_code NOT IN ( 'DRAFT' ,'REJECTED','PENDING')
2537 AND (l_rollup_original_budget IS NOT NULL OR
2538 l_rollup_transfered_in_amt IS NOT NULL OR
2539 l_rollup_transfered_out_amt IS NOT NULL OR
2540 l_rollup_holdback_amt IS NOT NULL OR
2541 l_rollup_planned_amt IS NOT NULL OR
2542 l_rollup_committed_amt IS NOT NULL OR
2543 l_rollup_utilized_amt IS NOT NULL OR
2544 l_rollup_earned_amt IS NOT NULL OR -- yzhao: 11.5.10
2545 l_rollup_paid_amt IS NOT NULL OR
2546 l_rollup_recal_committed IS NOT NULL)
2547 THEN
2548
2549 --nirprasa, no chnage needed as parent_fund_id is NULL for reconcile flow
2550 IF l_fund_rec.parent_fund_id IS NOT NULL THEN
2551 --For case from draft to active, update with own value, other case use difference to update rollup
2552 IF l_active_flag = false THEN
2553 l_fund_rec.rollup_original_budget := NVL(l_rollup_original_budget,0);
2554 l_fund_rec.rollup_transfered_in_amt := NVL(l_rollup_transfered_in_amt,0);
2555 l_fund_rec.rollup_transfered_out_amt := NVL(l_rollup_transfered_out_amt,0);
2556 l_fund_rec.rollup_holdback_amt := NVL(l_rollup_holdback_amt,0);
2557 l_fund_rec.rollup_planned_amt := NVL(l_rollup_planned_amt,0);
2558 l_fund_rec.rollup_committed_amt := NVL(l_rollup_committed_amt,0);
2559 l_fund_rec.rollup_utilized_amt := NVL(l_rollup_utilized_amt,0); -- yzhao: 11.5.10
2560 l_fund_rec.rollup_earned_amt := NVL(l_rollup_earned_amt,0);
2561 l_fund_rec.rollup_paid_amt := NVL(l_rollup_paid_amt,0);
2562 l_fund_rec.rollup_recal_committed := NVL(l_rollup_recal_committed,0);
2563 END IF;
2564
2565 update_rollup_amount(
2566 p_api_version => l_api_version
2567 ,p_init_msg_list => fnd_api.g_false
2568 ,p_commit => fnd_api.g_false
2569 ,p_validation_level => p_validation_level
2570 ,x_return_status => l_return_status
2571 ,x_msg_count => x_msg_count
2572 ,x_msg_data => x_msg_data
2573 ,p_fund_rec => l_fund_rec
2574 );
2575 IF l_return_status = fnd_api.g_ret_sts_error THEN
2576 RAISE fnd_api.g_exc_error;
2577 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2578 RAISE fnd_api.g_exc_unexpected_error;
2579 END IF;
2580
2581 -- commented by feliu to fix bug 2654263
2582 /* IF l_active_flag = true THEN
2583
2584 update_funds_access(
2585 p_api_version => l_api_version
2586 ,p_init_msg_list => fnd_api.g_false
2587 ,p_commit => fnd_api.g_false
2588 ,p_validation_level => p_validation_level
2589 ,x_return_status => l_return_status
2590 ,x_msg_count => x_msg_count
2591 ,x_msg_data => x_msg_data
2592 ,p_fund_rec => l_fund_rec
2593 ,p_mode => 'CREATE'
2594 );
2595 IF l_return_status = fnd_api.g_ret_sts_error THEN
2596 RAISE fnd_api.g_exc_error;
2597 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2598 RAISE fnd_api.g_exc_unexpected_error;
2599 END IF;
2600 END IF; */
2601 END IF; -- end if for parent fund
2602 END IF; -- end if for status code
2603
2604
2605 -- yzhao 04/02/2002 no need to call process_approval if it's from actbudget update
2606 IF p_mode <> 'ADJUST' THEN
2607 -- 10/14/2002 mode is always update in QP
2608 /*IF l_fund_rec.status_code IN ('CLOSED','CANCELLED','ACTIVE','ON_HOLD')
2609 AND l_old_status IN ('ACTIVE','ON_HOLD') THEN
2610 */
2611 l_mode := 'UPDATE' ; -- when not creating act_budgets
2612 IF l_fund_rec.status_code IN ('ACTIVE','ON_HOLD')
2613 AND l_old_status IN ('PENDING','DRAFT') THEN
2614 l_mode := 'ACTIVE' ; -- when creating act budgets for active funds
2615 END IF;
2616 -- call when it is active after submitting for approval
2617 IF l_mode IN ('ACTIVE' , 'UPDATE' ) THEN
2618 ozf_fundrules_pvt.process_approval(
2619 p_fund_rec => l_fund_rec
2620 ,p_mode => l_mode
2621 ,p_old_fund_status => l_old_status
2622 ,x_return_status => l_return_status
2623 ,x_msg_count => x_msg_count
2624 ,x_msg_data => x_msg_data
2625 ,p_api_version => 1.0);
2626 END IF;
2627
2628 IF l_return_status = fnd_api.g_ret_sts_error THEN
2629 RAISE fnd_api.g_exc_error;
2630 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2631 RAISE fnd_api.g_exc_unexpected_error;
2632 END IF;
2633 END IF; -- yzhao: end of IF p_mode <> 'ADJUST'
2634
2635 -- call when it is submitting for approval
2636 IF l_submit_child_approval = FND_API.g_true THEN
2637 -- yzhao: 03/18/2003 handle budget allocation as well
2638 IF l_fund_rec.fund_usage IS NOT NULL AND l_fund_rec.fund_usage = 'ALLOC' THEN
2639 -- yzhao: 03/18/2003 budget allocation does not need workflow approval process so pass allocation_flag='Y'
2640 l_allocation_flag := 'Y';
2641 ELSE
2642 l_allocation_flag := 'N';
2643 END IF;
2644 --nirprasa, no chnage needed as this flow is for request
2645 ozf_fund_request_apr_pvt.create_fund_request(
2646 p_commit => fnd_api.g_false
2647 ,p_approval_for_id => p_fund_rec.fund_id
2648 /* yzhao: Jan 16 2005 fix bug 4943323(4912954) pass in correct requester id
2649 ,p_requester_id => l_fund_rec.owner
2650 */
2651 ,p_requester_id => ozf_utility_pvt.get_resource_id(p_user_id => fnd_global.user_id)
2652 ,p_requested_amount => l_child_request_amt
2653 ,p_approval_fm => 'FUND'
2654 ,p_approval_fm_id => l_fund_rec.parent_fund_id
2655 ,p_transfer_type => 'REQUEST'
2656 ,p_child_flag =>'Y'
2657 -- yzhao: 03/18/2003 11.5.9 for allocation activation of territory hierarchy, always pass as 'Y'; all others 'N'
2658 ,p_allocation_flag => l_allocation_flag
2659 ,p_justification => l_fund_rec.description
2660 ,x_return_status => l_return_status
2661 ,x_msg_count => x_msg_count
2662 ,x_msg_data => x_msg_data
2663 ,x_request_id => l_request_id
2664 ,x_approver_id => l_approver_id
2665 ,x_is_requester_owner => l_is_requestor_owner);
2666
2667 IF l_return_status = fnd_api.g_ret_sts_error THEN
2668 RAISE fnd_api.g_exc_error;
2669 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2670 RAISE fnd_api.g_exc_unexpected_error;
2671 END IF;
2672 END IF;
2673 -- call when it is submitting for approval
2674 IF l_submit_budget_approval = FND_API.g_true THEN
2675 l_reject_status_id :=ozf_utility_pvt.get_default_user_status(
2676 l_status_type
2677 ,'REJECTED');
2678
2679 AMS_GEN_APPROVAL_PVT.StartProcess
2680 (p_activity_type => g_activity_type
2681 ,p_activity_id => p_fund_rec.fund_id
2682 ,p_approval_type => G_PARENT_APPROVAL_TYPE
2683 ,p_object_version_number =>p_fund_rec.object_version_number -- old object version number
2684 ,p_orig_stat_id =>l_old_user_status_id
2685 ,p_new_stat_id =>p_fund_rec.user_status_id -- active status
2686 ,p_reject_stat_id =>l_reject_status_id
2687 /* yzhao: Jan 16 2005 fix bug 4943323(4912954) pass in correct requester id
2688 ,p_requester_userid =>l_fund_rec.owner
2689 */
2690 ,p_requester_userid =>ozf_utility_pvt.get_resource_id(p_user_id => fnd_global.user_id)
2691 ,p_notes_from_requester =>l_fund_rec.description
2692 ,p_workflowprocess => l_workflow_process
2693 ,p_item_type => l_item_type);
2694
2695 raise_business_event(p_object_id => p_fund_rec.fund_id , p_event_type =>'APPROVAL');
2696 ELSE
2697 -- raise business event.
2698 raise_business_event(p_object_id => p_fund_rec.fund_id ,p_event_type =>'UPDATE');
2699 END IF;
2700
2701 /************************ MPANDE JAN-16 2001 ************************************************
2702 ..The calls that were made to the following API for child fund workflow process
2703 ozf_wf_request_apr_pvt.create_fund_request
2704 was removed from this place and put in update_fund_status APIS
2705 The code was removed and not commented because of clarity and cleanliness. Please refer to
2706 earlier versions for bug fixes etc. in releases prior to 11.5.5. (hornet)
2707 **********************************************************************************************/
2708 -- reinitialize the variables
2709 l_act_access_id := NULL;
2710 l_acc_obj_ver_num := NULL;
2711 -- if owner is changing update acesss
2712 IF p_fund_rec.owner <> fnd_api.g_miss_num
2713 AND l_owner <> p_fund_rec.owner THEN
2714 IF G_DEBUG THEN
2715 ozf_utility_pvt.debug_message(l_full_name || ': insert access owner');
2716 END IF;
2717 /* commented by feliu on 05/13/2003 to fix bug 2969498;
2718 OPEN c_access(p_fund_rec.fund_id);
2719 FETCH c_access INTO l_act_access_id, l_acc_obj_ver_num;
2720 CLOSE c_access;
2721 ams_access_pvt.init_access_rec(l_access_rec);
2722 l_access_rec.activity_access_id := l_act_access_id;
2723 l_access_rec.object_version_number := l_acc_obj_ver_num;
2724 l_access_rec.act_access_to_object_id := l_fund_rec.fund_id;
2725 l_access_rec.arc_act_access_to_object := 'FUND';
2726 l_access_rec.user_or_role_id := l_fund_rec.owner;
2727 l_access_rec.arc_user_or_role_type := 'USER';
2728 l_access_rec.admin_flag := 'Y';
2729 l_access_rec.owner_flag := 'Y';
2730 ams_access_pvt.update_access(
2731 p_api_version => l_api_version
2732 ,p_init_msg_list => fnd_api.g_false
2733 ,p_validation_level => p_validation_level
2734 ,x_return_status => l_return_status
2735 ,x_msg_count => x_msg_count
2736 ,x_msg_data => x_msg_data
2737 ,p_commit => fnd_api.g_false
2738 ,p_access_rec => l_access_rec);
2739 */
2740
2741 AMS_Access_PVT.update_object_owner
2742 ( p_api_version => 1.0
2743 ,p_init_msg_list => FND_API.G_FALSE
2744 ,p_commit => FND_API.G_FALSE
2745 ,p_validation_level => p_validation_level
2746 ,x_return_status => x_return_status
2747 ,x_msg_count => x_msg_count
2748 ,x_msg_data => x_msg_data
2749 ,p_object_type => 'FUND'
2750 ,p_object_id => l_fund_rec.fund_id
2751 ,p_resource_id => l_fund_rec.owner
2752 ,p_old_resource_id => l_owner
2753 );
2754 IF l_return_status = fnd_api.g_ret_sts_error THEN
2755 RAISE fnd_api.g_exc_error;
2756 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2757 RAISE fnd_api.g_exc_unexpected_error;
2758 END IF;
2759 END IF; -- end of owner is changing
2760
2761 -- added by feliu to fix bug 2654263
2762 -- if parent id is not null during updation and old parent id is null ,
2763 -- a row is created in the ams_act_access to give access
2764 -- to the owner of the parent fund to this child
2765 --if the parent fund owner and the child fund owner is not same then only create this
2766 IF l_fund_rec.parent_fund_id IS NOT NULL AND l_fund_rec.owner <> l_par_fund_owner THEN
2767 --if there is no parent fund OR (09/05/2001 mpande) when no records exists in ams_act_access for the fund
2768 --then create a access for the new parent ownner
2769 IF l_old_parent_fund_id IS NULL THEN
2770 update_funds_access(
2771 p_api_version => l_api_version
2772 ,p_init_msg_list => fnd_api.g_false
2773 ,p_commit => fnd_api.g_false
2774 ,p_validation_level => p_validation_level
2775 ,x_return_status => l_return_status
2776 ,x_msg_count => x_msg_count
2777 ,x_msg_data => x_msg_data
2778 ,p_fund_rec => l_fund_rec
2779 ,p_mode => 'CREATE'
2780 );
2781 IF l_return_status = fnd_api.g_ret_sts_error THEN
2782 RAISE fnd_api.g_exc_error;
2783 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2784 RAISE fnd_api.g_exc_unexpected_error;
2785 END IF;
2786
2787 --if the old parent fund and the new parent fund is diffrent.
2788 ELSIF l_old_parent_fund_id <> l_fund_rec.parent_fund_id THEN
2789 update_funds_access(
2790 p_api_version => l_api_version
2791 ,p_init_msg_list => fnd_api.g_false
2792 ,p_commit => fnd_api.g_false
2793 ,p_validation_level => p_validation_level
2794 ,x_return_status => l_return_status
2795 ,x_msg_count => x_msg_count
2796 ,x_msg_data => x_msg_data
2797 ,p_fund_rec => l_fund_rec
2798 ,p_mode => 'CREATE'
2799 );
2800 IF l_return_status = fnd_api.g_ret_sts_error THEN
2801 RAISE fnd_api.g_exc_error;
2802 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2803 RAISE fnd_api.g_exc_unexpected_error;
2804 END IF;
2805 END IF; -- end of l_old_parent_fund_id <> l_fund_rec.parent_fund_id.
2806 END IF; -- end of l_fund_rec.parent_fund_id IS NOT NULL
2807 -- added by feliu to fix bug 2654263
2808
2809 /* R12: yzhao bug 4669269 - obsolete ozf_mc_transactions
2810 IF p_fund_rec.original_budget <> FND_API.g_miss_num
2811 OR p_fund_rec.transfered_in_amt <> FND_API.g_miss_num
2812 OR p_fund_rec.transfered_out_amt <> FND_API.g_miss_num
2813 OR p_fund_rec.holdback_amt <> FND_API.g_miss_num
2814 OR p_fund_rec.planned_amt <> FND_API.g_miss_num
2815 OR p_fund_rec.committed_amt <> FND_API.g_miss_num
2816 OR p_fund_rec.utilized_amt <> FND_API.g_miss_num -- yzhao: 11.5.10
2817 OR p_fund_rec.earned_amt <> FND_API.g_miss_num
2818 OR p_fund_rec.paid_amt <> FND_API.g_miss_num
2819 OR p_fund_rec.currency_code_tc <> FND_API.g_miss_char THEN
2820
2821 OPEN c_mc_record(p_fund_rec.fund_id);
2822 FETCH c_mc_record INTO l_mc_record_id, l_mc_obj_number;
2823 CLOSE c_mc_record;
2824 --///mpande
2825 -- Insert a record in OZF_MC_TRANSACTIONS_ALL IN functional currency
2826 -- so that we have the functional currency amounts
2827 -- The exchange_rate_type is picked up by the MC_TRAnSACTIONS API
2828 -- from proile
2829 -- update the transaction table on all cases.
2830
2831 IF G_DEBUG THEN
2832 ozf_utility_pvt.debug_message(l_full_name ||': insert FC record' ||l_fund_rec.currency_code_tc);
2833 END IF;
2834 ozf_mc_transactions_pvt.init_transaction_rec(x_mc_transactions_rec => l_mc_transaction_rec);
2835 -- Populate the record variable
2836 l_mc_transaction_rec.mc_record_id := l_mc_record_id;
2837 l_mc_transaction_rec.object_version_number := l_mc_obj_number;
2838 l_mc_transaction_rec.source_object_name := 'FUND';
2839 l_mc_transaction_rec.source_object_id := l_fund_rec.fund_id;
2840 l_mc_transaction_rec.currency_code := l_fund_rec.currency_code_tc;
2841 l_mc_transaction_rec.amount_column1 := l_fund_rec.original_budget;
2842 l_mc_transaction_rec.amount_column2 := l_fund_rec.transfered_in_amt;
2843 l_mc_transaction_rec.amount_column3 := l_fund_rec.transfered_out_amt;
2844 l_mc_transaction_rec.amount_column4 := l_fund_rec.holdback_amt;
2845 l_mc_transaction_rec.amount_column5 := l_fund_rec.planned_amt;
2846 l_mc_transaction_rec.amount_column6 := l_fund_rec.committed_amt;
2847 l_mc_transaction_rec.amount_column7 := l_fund_rec.earned_amt;
2848 l_mc_transaction_rec.amount_column8 := l_fund_rec.paid_amt;
2849 l_mc_transaction_rec.amount_column9 := l_fund_rec.utilized_amt; -- yzhao: 11.5.10
2850
2851 -- kdass - R12 MOAC changes
2852 OPEN c_get_org_id;
2853 FETCH c_get_org_id INTO l_fund_rec.org_id;
2854 CLOSE c_get_org_id;
2855
2856 -- Call mc_transaction API if fund type is not QUOTA
2857 IF l_fund_rec.fund_type <> 'QUOTA' THEN
2858 ozf_mc_transactions_pvt.update_mc_transactions(
2859 p_api_version => l_api_version
2860 ,p_init_msg_list => fnd_api.g_false
2861 ,p_commit => fnd_api.g_false
2862 -- 01/13/2003 yzhao fix bug BUG 2750841(same as 2741039) pass in org_id
2863 ,p_org_id => l_fund_rec.org_id
2864 ,x_return_status => l_return_status
2865 ,x_msg_count => x_msg_count
2866 ,x_msg_data => x_msg_data
2867 ,p_mc_transactions_rec => l_mc_transaction_rec);
2868
2869 IF l_return_status = fnd_api.g_ret_sts_error THEN
2870 RAISE fnd_api.g_exc_error;
2871 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2872 RAISE fnd_api.g_exc_unexpected_error;
2873 END IF;
2874 END IF;
2875
2876 END IF ; --check for amount
2877 */
2878
2879 END IF;-- end return status
2880
2881 -- Check for commit
2882 IF fnd_api.to_boolean(p_commit) THEN
2883 COMMIT;
2884 END IF;
2885
2886 fnd_msg_pub.count_and_get(
2887 p_encoded => fnd_api.g_false
2888 ,p_count => x_msg_count
2889 ,p_data => x_msg_data);
2890 IF G_DEBUG THEN
2891 ozf_utility_pvt.debug_message(l_full_name || ': end');
2892 END IF;
2893 EXCEPTION
2894 WHEN fnd_api.g_exc_error THEN
2895 ROLLBACK TO update_fund;
2896 x_return_status := fnd_api.g_ret_sts_error;
2897 fnd_msg_pub.count_and_get(
2898 p_encoded => fnd_api.g_false
2899 ,p_count => x_msg_count
2900 ,p_data => x_msg_data);
2901 WHEN fnd_api.g_exc_unexpected_error THEN
2902 ROLLBACK TO update_fund;
2903 x_return_status := fnd_api.g_ret_sts_unexp_error;
2904 fnd_msg_pub.count_and_get(
2905 p_encoded => fnd_api.g_false
2906 ,p_count => x_msg_count
2907 ,p_data => x_msg_data);
2908 WHEN OTHERS THEN
2909 ROLLBACK TO update_fund;
2910 x_return_status := fnd_api.g_ret_sts_unexp_error;
2911
2912 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2913 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2914 END IF;
2915
2916 fnd_msg_pub.count_and_get(
2917 p_encoded => fnd_api.g_false
2918 ,p_count => x_msg_count
2919 ,p_data => x_msg_data);
2920 END update_fund;
2921
2922 --------------------------------------------------------------------
2923 -- PROCEDURE
2924 -- Validate_Fund
2925 --
2926 -- HISTORY
2927 -- 02/02/2000 Shitij Vatsa Create.
2928 -- 06/13/2000 Mumu PAnde Added validations
2929 -- 07/28/2000 Mumu Pande Added parent_validation against child
2930 -- 01/20/2001 Mumu Pande Added call for fund inter entity validations
2931 --------------------------------------------------------------------
2932 PROCEDURE validate_fund(
2933 p_api_version IN NUMBER
2934 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2935 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2936 ,x_return_status OUT NOCOPY VARCHAR2
2937 ,x_msg_count OUT NOCOPY NUMBER
2938 ,x_msg_data OUT NOCOPY VARCHAR2
2939 ,p_fund_rec IN fund_rec_type)
2940 IS
2941 l_api_version CONSTANT NUMBER := 1.0;
2942 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Fund';
2943 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2944 l_return_status VARCHAR2(1);
2945 BEGIN
2946 ----------------------- initialize --------------------
2947 IF G_DEBUG THEN
2948 ozf_utility_pvt.debug_message(l_full_name || ': start');
2949 END IF;
2950
2951 IF fnd_api.to_boolean(p_init_msg_list) THEN
2952 fnd_msg_pub.initialize;
2953 END IF;
2954
2955 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2956 RAISE fnd_api.g_exc_unexpected_error;
2957 END IF;
2958
2959 x_return_status := fnd_api.g_ret_sts_success;
2960 ---------------------- validate ------------------------
2961 IF G_DEBUG THEN
2962 ozf_utility_pvt.debug_message(l_full_name || ': check items');
2963 END IF;
2964
2965 IF p_validation_level >= jtf_plsql_api.g_valid_level_item THEN
2966 ----dbms_output.put_line ('Calling Check_Fund_Items from validate');
2967 check_fund_items(
2968 p_fund_rec => p_fund_rec
2969 ,p_validation_mode => jtf_plsql_api.g_create
2970 ,x_return_status => l_return_status);
2971
2972 ----dbms_output.put_line ('Called Check_Fund_Items from validate');
2973 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2974 RAISE fnd_api.g_exc_unexpected_error;
2975 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2976 RAISE fnd_api.g_exc_error;
2977 END IF;
2978 END IF;
2979
2980 IF G_DEBUG THEN
2981 ozf_utility_pvt.debug_message(l_full_name || ': check record');
2982 END IF;
2983
2984 IF p_validation_level >= jtf_plsql_api.g_valid_level_record THEN
2985 check_fund_record(
2986 p_fund_rec => p_fund_rec
2987 ,p_complete_rec => p_fund_rec
2988 ,p_mode => jtf_plsql_api.g_create
2989 ,x_return_status => l_return_status);
2990
2991 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2992 RAISE fnd_api.g_exc_unexpected_error;
2993 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2994 RAISE fnd_api.g_exc_error;
2995 END IF;
2996 END IF;
2997
2998 -- added on 01/20/2001 for all inter entity validations Mumu Pande
2999 IF G_DEBUG THEN
3000 ozf_utility_pvt.debug_message(l_full_name || ': check inter-entity');
3001 END IF;
3002
3003 IF p_validation_level >= jtf_plsql_api.g_valid_level_inter_entity THEN
3004 check_fund_inter_entity(
3005 p_fund_rec => p_fund_rec
3006 ,p_complete_rec => p_fund_rec
3007 ,p_validation_mode => jtf_plsql_api.g_create
3008 ,x_return_status => l_return_status);
3009
3010 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3011 RAISE fnd_api.g_exc_unexpected_error;
3012 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
3013 RAISE fnd_api.g_exc_error;
3014 END IF;
3015 END IF;
3016
3017 -------------------- finish --------------------------
3018 fnd_msg_pub.count_and_get(
3019 p_encoded => fnd_api.g_false
3020 ,p_count => x_msg_count
3021 ,p_data => x_msg_data);
3022 IF G_DEBUG THEN
3023 ozf_utility_pvt.debug_message(l_full_name || ': end');
3024 END IF;
3025 EXCEPTION
3026 WHEN fnd_api.g_exc_error THEN
3027 x_return_status := fnd_api.g_ret_sts_error;
3028 fnd_msg_pub.count_and_get(
3029 p_encoded => fnd_api.g_false
3030 ,p_count => x_msg_count
3031 ,p_data => x_msg_data);
3032 WHEN fnd_api.g_exc_unexpected_error THEN
3033 x_return_status := fnd_api.g_ret_sts_unexp_error;
3034 fnd_msg_pub.count_and_get(
3035 p_encoded => fnd_api.g_false
3036 ,p_count => x_msg_count
3037 ,p_data => x_msg_data);
3038 WHEN OTHERS THEN
3039 x_return_status := fnd_api.g_ret_sts_unexp_error;
3040
3041 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3042 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3043 END IF;
3044
3045 fnd_msg_pub.count_and_get(
3046 p_encoded => fnd_api.g_false
3047 ,p_count => x_msg_count
3048 ,p_data => x_msg_data);
3049 END validate_fund;
3050
3051 ---------------------------------------------------------------------
3052 -- PROCEDURE
3053 -- Check_Fund_Req_Items
3054 --
3055 -- HISTORY
3056 -- 02/02/2000 Shitij Vatsa Create.
3057 -- 09/20/2000 Mumu Pande for user status
3058 -- 01/20/2001 Mumu Pande for category
3059 ---------------------------------------------------------------------
3060 PROCEDURE check_fund_req_items(
3061 p_fund_rec IN fund_rec_type
3062 ,x_return_status OUT NOCOPY VARCHAR2)
3063 IS
3064
3065 BEGIN
3066 x_return_status := fnd_api.g_ret_sts_success;
3067
3068 -- Commented by mpande 02/16/2001 We are going to generate the number if it is null
3069 ------------------------ fund_number --------------------------
3070 /* IF p_fund_rec.fund_number IS NULL THEN -- check for fund number
3071 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3072 IF p_fund_rec.fund_type = 'QUOTA' THEN
3073 fnd_message.set_name('OZF', 'OZF_TP_NO_QUOTA_NUMBER');
3074 ELSE
3075 fnd_message.set_name('OZF', 'OZF_FUND_NO_FUND_NUMBER');
3076 END IF;
3077 fnd_msg_pub.add;
3078 END IF;
3079
3080 x_return_status := fnd_api.g_ret_sts_error;
3081 RETURN;
3082 END IF;
3083 */
3084 ------------------------ owner -------------------------------
3085 IF p_fund_rec.owner IS NULL THEN -- check for fund owner
3086 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3087 IF p_fund_rec.fund_type = 'QUOTA' THEN
3088 fnd_message.set_name('OZF', 'OZF_TP_NO_QUOTA_OWNER');
3089 ELSE
3090 fnd_message.set_name('OZF', 'OZF_FUND_NO_FUND_OWNER');
3091 END IF;
3092 fnd_msg_pub.add;
3093 END IF;
3094
3095 x_return_status := fnd_api.g_ret_sts_error;
3096 RETURN;
3097 END IF;
3098
3099 IF p_fund_rec.fund_type IS NULL THEN -- check for fund owner
3100 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3101 fnd_message.set_name('OZF', 'OZF_FUND_NO_FUND_TYPE');
3102 fnd_msg_pub.add;
3103 END IF;
3104
3105 x_return_status := fnd_api.g_ret_sts_error;
3106 RETURN;
3107 END IF;
3108
3109 -- 09/20/2000 Mumu Pande for user status
3110 IF p_fund_rec.user_status_id IS NULL THEN -- check for fund user status
3111 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3112 fnd_message.set_name('OZF', 'OZF_FUND_NO_USER_STATUS');
3113 fnd_msg_pub.add;
3114 END IF;
3115
3116 x_return_status := fnd_api.g_ret_sts_error;
3117 RETURN;
3118 END IF;
3119
3120 -- 01/20/2001 Mumu Pande for category
3121 IF p_fund_rec.fund_type <> 'QUOTA' AND p_fund_rec.category_id IS NULL THEN -- check for fund category
3122 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3123 fnd_message.set_name('OZF', 'OZF_FUND_NO_CATEGORY');
3124 fnd_msg_pub.add;
3125 END IF;
3126
3127 x_return_status := fnd_api.g_ret_sts_error;
3128 RETURN;
3129 END IF;
3130 -- 01/20/2001 Mumu Pande for custom_setup_id
3131 IF p_fund_rec.custom_setup_id IS NULL THEN -- check for fund category
3132 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3133 fnd_message.set_name('OZF', 'OZF_FUND_NO_CUSTOM_SETUP');
3134 fnd_msg_pub.add;
3135 END IF;
3136
3137 x_return_status := fnd_api.g_ret_sts_error;
3138 RETURN;
3139 END IF;
3140 -- 11/13/2001 mpande added budget amount cannot be euqal to 0 for root budgets
3141 -- 12/23/04 by feliu. For the budgets created from mass transfer, don't validate. fix bug 3580531.
3142
3143 IF p_fund_rec.prev_fund_id IS NULL AND p_fund_rec.parent_fund_id IS NULL AND p_fund_rec.fund_type = 'FIXED' THEN
3144
3145 -- niprakas changed <= to <
3146 -- rimehrot changed back to <= for bug fix 3580531
3147 IF NVL(p_fund_rec.original_budget,0) <= 0 THEN -- check for fund amount
3148 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3149 IF p_fund_rec.fund_type = 'QUOTA' THEN
3150 fnd_message.set_name('OZF', 'OZF_TP_NO_ORG_QUOTA');
3151 ELSE
3152 fnd_message.set_name('OZF', 'OZF_FUND_NO_ORG_BUDGET');
3153 END IF;
3154 fnd_msg_pub.add;
3155 END IF;
3156
3157 x_return_status := fnd_api.g_ret_sts_error;
3158 RETURN;
3159 END IF;
3160 END IF;
3161
3162 --kdass 25-APR-2006 bug 5176819 - Ledger is required field
3163 IF p_fund_rec.fund_type <> 'QUOTA' AND p_fund_rec.ledger_id IS NULL THEN
3164 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3165 fnd_message.set_name('OZF', 'OZF_FUND_NO_LEDGER');
3166 fnd_msg_pub.add;
3167 END IF;
3168
3169 x_return_status := fnd_api.g_ret_sts_error;
3170 RETURN;
3171 END IF;
3172
3173 /* yzhao: bug 4669461: R12 budget is org aware, but not org stripped. quota is not org aware
3174 so org_id is not required
3175 IF p_fund_rec.org_id IS NULL THEN -- check for org id
3176 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3177 fnd_message.set_name('OZF', 'OZF_NO_DEFAULT_ORG_ID');
3178 fnd_msg_pub.add;
3179 END IF;
3180
3181 x_return_status := fnd_api.g_ret_sts_error;
3182 RETURN;
3183 END IF;
3184 */
3185
3186 END check_fund_req_items;
3187
3188 ---------------------------------------------------------------------
3189 -- PROCEDURE
3190 -- Check_Fund_Uk_Items
3191 --
3192 -- HISTORY
3193 -- 02/02/2000 Shitij Vatsa Create.
3194 -- 2nd August200 MPAnde Updated
3195 ---------------------------------------------------------------------
3196 PROCEDURE check_fund_uk_items(
3197 p_fund_rec IN fund_rec_type
3198 ,p_validation_mode IN VARCHAR2 := jtf_plsql_api.g_create
3199 ,x_return_status OUT NOCOPY VARCHAR2)
3200 IS
3201 l_valid_flag VARCHAR2(1);
3202
3203 -- Added for Bug #3498826
3204 CURSOR c_prog_fund_number_create
3205 IS
3206 SELECT 1 from ozf_funds_all_b
3207 WHERE fund_number = p_fund_rec.fund_number;
3208
3209 CURSOR c_prog_fund_number_update
3210 IS
3211 SELECT 1 from ozf_funds_all_b
3212 WHERE fund_number = p_fund_rec.fund_number
3213 AND fund_id <> p_fund_rec.fund_id;
3214
3215
3216 BEGIN
3217 x_return_status := fnd_api.g_ret_sts_success;
3218
3219 -- For Create_Fund, when fund_id is passed in, we need to
3220 -- check if this fund_id is unique.
3221 IF p_validation_mode = jtf_plsql_api.g_create
3222 AND p_fund_rec.fund_id IS NOT NULL THEN
3223 IF ozf_utility_pvt.check_uniqueness(
3224 'ozf_funds_all_vl'
3225 ,'fund_id = ' || p_fund_rec.fund_id) =
3226 fnd_api.g_false THEN
3227 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3228 IF p_fund_rec.fund_type = 'QUOTA' THEN
3229 fnd_message.set_name('OZF', 'OZF_TP_DUPLICATE_ID');
3230 ELSE
3231 fnd_message.set_name('OZF', 'OZF_FUND_DUPLICATE_ID');
3232 END IF;
3233 fnd_msg_pub.add;
3234 END IF;
3235
3236 x_return_status := fnd_api.g_ret_sts_error;
3237 RETURN;
3238 END IF;
3239 END IF;
3240
3241 -- Check if fund_number is unique. Need to handle create and
3242 -- update differently.
3243 IF p_validation_mode = jtf_plsql_api.g_create THEN
3244 /*l_valid_flag :=
3245 ozf_utility_pvt.check_uniqueness(
3246 'ozf_funds_all_vl'
3247 ,'fund_number = ''' || p_fund_rec.fund_number || '''');*/
3248 OPEN c_prog_fund_number_create;
3249 FETCH c_prog_fund_number_create INTO l_valid_flag;
3250 CLOSE c_prog_fund_number_create;
3251 ELSE
3252 /* l_valid_flag :=
3253 ozf_utility_pvt.check_uniqueness(
3254 'ozf_funds_all_vl'
3255 ,'fund_number = ''' ||
3256 p_fund_rec.fund_number ||
3257 ''' AND fund_id <> ' ||
3258 p_fund_rec.fund_id);*/
3259 OPEN c_prog_fund_number_update;
3260 FETCH c_prog_fund_number_update INTO l_valid_flag;
3261 CLOSE c_prog_fund_number_update;
3262 END IF;
3263
3264 IF l_valid_flag = 1 THEN
3265 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3266 IF p_fund_rec.fund_type = 'QUOTA' THEN
3267 fnd_message.set_name('OZF', 'OZF_TP_DUPLICATE_NUMBER');
3268 ELSE
3269 fnd_message.set_name('OZF', 'OZF_FUND_DUPLICATE_NUMBER');
3270 END IF;
3271 fnd_msg_pub.add;
3272 END IF;
3273
3274 x_return_status := fnd_api.g_ret_sts_error;
3275 RETURN;
3276 END IF;
3277 -- Check if fund_name is unique if it is accrual fund/offer name . Need to handle create and
3278 -- update differently.
3279 -- Commented for Bug Fix #3498826
3280 /*
3281 IF p_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
3282 IF p_validation_mode = jtf_plsql_api.g_create THEN
3283 l_valid_flag :=
3284 ozf_utility_pvt.check_uniqueness(
3285 'ozf_funds_all_vl'
3286 ,'short_name = ''' || p_fund_rec.short_name || '''');
3287 ELSE
3288 l_valid_flag :=
3289 ozf_utility_pvt.check_uniqueness(
3290 'ozf_funds_all_vl'
3291 ,'short_name = ''' ||
3292 p_fund_rec.short_name ||
3293 ''' AND fund_id <> ' ||
3294 p_fund_rec.fund_id);
3295 END IF;
3296
3297 IF l_valid_flag = fnd_api.g_false THEN
3298 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3299 IF p_fund_rec.fund_type = 'QUOTA' THEN
3300 fnd_message.set_name('OZF', 'OZF_TP_DUPLICATE_NAME');
3301 ELSE
3302 fnd_message.set_name('OZF', 'OZF_FUND_DUPLICATE_NAME');
3303 END IF;
3304 fnd_msg_pub.add;
3305 END IF;
3306
3307 x_return_status := fnd_api.g_ret_sts_error;
3308 RETURN;
3309 END IF;
3310 END IF;*/
3311
3312 END check_fund_uk_items;
3313
3314 ---------------------------------------------------------------------
3315 -- PROCEDURE
3316 -- Check_Fund_Fk_Items
3317 --
3318 -- HISTORY
3319 -- 20/09/2000 Mumu Pande Create.
3320 -- 09/20/2000 Mumu Pande for user status
3321 -- 01/20/2001 Mumu PAnde for category validations
3322 ---------------------------------------------------------------------
3323 PROCEDURE check_fund_fk_items(
3324 p_fund_rec IN fund_rec_type
3325 ,x_return_status OUT NOCOPY VARCHAR2)
3326 IS
3327 BEGIN
3328 x_return_status := fnd_api.g_ret_sts_success;
3329
3330 ----------------------- user_status_id ------------------------
3331 IF p_fund_rec.user_status_id <> fnd_api.g_miss_num THEN
3332 IF ozf_utility_pvt.check_fk_exists(
3333 'ams_user_statuses_vl'
3334 ,'user_status_id'
3335 ,p_fund_rec.user_status_id) =
3336 fnd_api.g_false THEN
3337 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3338 fnd_message.set_name('OZF', 'OZF_FUND_BAD_USER_STATUS_ID');
3339 fnd_msg_pub.add;
3340 END IF;
3341
3342 x_return_status := fnd_api.g_ret_sts_error;
3343 RETURN;
3344 END IF;
3345 END IF;
3346
3347 -- added on 01/20/2001 MPANDE
3348 ----------------------- category_id ------------------------
3349 -- mkothari - Bug 4701105 - start ----
3350 IF p_fund_rec.fund_type <> 'QUOTA' THEN
3351 IF p_fund_rec.category_id <> fnd_api.g_miss_num THEN
3352 IF ozf_utility_pvt.check_fk_exists(
3353 'ams_categories_vl'
3354 ,'category_id'
3355 ,p_fund_rec.category_id) =
3356 fnd_api.g_false THEN
3357 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3358 fnd_message.set_name('OZF', 'OZF_FUND_BAD_CAT_ID');
3359 fnd_msg_pub.add;
3360 END IF;
3361
3362 x_return_status := fnd_api.g_ret_sts_error;
3363 RETURN;
3364 END IF;
3365 END IF;
3366 END IF;
3367 -- mkothari - Bug 4701105 - end ----
3368 -- check other fk items
3369
3370 END check_fund_fk_items;
3371
3372
3373
3374 ---------------------------------------------------------------------
3375 -- PROCEDURE
3376 -- Check_Fund_Lookup_Items
3377 --
3378 -- HISTORY
3379 -- 02/02/2000 Shitij Vatsa Create.
3380 ---------------------------------------------------------------------
3381 PROCEDURE check_fund_lookup_items(
3382 p_fund_rec IN fund_rec_type
3383 ,x_return_status OUT NOCOPY VARCHAR2)
3384 IS
3385 BEGIN
3386 x_return_status := fnd_api.g_ret_sts_success;
3387
3388 ----------------------- fund_type ------------------------
3389 IF p_fund_rec.fund_type <> fnd_api.g_miss_char THEN
3390 IF ozf_utility_pvt.check_lookup_exists(
3391 p_lookup_table_name => 'OZF_LOOKUPS'
3392 ,p_lookup_type => 'OZF_FUND_TYPE'
3393 ,p_lookup_code => p_fund_rec.fund_type) =
3394 fnd_api.g_false THEN
3395 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3396 IF p_fund_rec.fund_type = 'QUOTA' THEN
3397 fnd_message.set_name('OZF', 'OZF_TP_BAD_QUOTA_TYPE');
3398 ELSE
3399 fnd_message.set_name('OZF', 'OZF_FUND_BAD_FUND_TYPE');
3400 END IF;
3401 fnd_msg_pub.add;
3402 END IF;
3403
3404 x_return_status := fnd_api.g_ret_sts_error;
3405 RETURN;
3406 END IF;
3407 END IF;
3408
3409
3410 ----------------------- status_code ------------------------
3411 IF p_fund_rec.status_code <> fnd_api.g_miss_char THEN
3412 IF ozf_utility_pvt.check_lookup_exists(
3413 p_lookup_table_name => 'OZF_LOOKUPS'
3414 ,p_lookup_type => 'OZF_FUND_STATUS'
3415 ,p_lookup_code => p_fund_rec.status_code) =
3416 fnd_api.g_false THEN
3417 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3418 IF p_fund_rec.fund_type = 'QUOTA' THEN
3419 fnd_message.set_name('OZF', 'OZF_TP_BAD_STATUS_CODE');
3420 ELSE
3421 fnd_message.set_name('OZF', 'OZF_FUND_BAD_STATUS_CODE');
3422 END IF;
3423 fnd_msg_pub.add;
3424 END IF;
3425
3426 x_return_status := fnd_api.g_ret_sts_error;
3427 RETURN;
3428 END IF;
3429 END IF;
3430 -- check other lookup codes
3431
3432 END check_fund_lookup_items;
3433
3434
3435
3436 ---------------------------------------------------------------------
3437 -- PROCEDURE
3438 -- Check_Fund_Flag_Items
3439 --
3440 -- HISTORY
3441 -- 02/02/2000 Shitij Vatsa Create.
3442 ---------------------------------------------------------------------
3443 PROCEDURE check_fund_flag_items(
3444 p_fund_rec IN fund_rec_type
3445 ,x_return_status OUT NOCOPY VARCHAR2)
3446 IS
3447 BEGIN
3448 x_return_status := fnd_api.g_ret_sts_success;
3449
3450 ----------------------- liability_flag ------------------------
3451 IF p_fund_rec.liability_flag <> fnd_api.g_miss_char
3452 AND p_fund_rec.liability_flag IS NOT NULL THEN
3453 IF ozf_utility_pvt.is_y_or_n(p_fund_rec.liability_flag) = fnd_api.g_false THEN
3454 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3455 fnd_message.set_name('OZF', 'OZF_FUND_BAD_LIABILITY_FLAG');
3456 fnd_msg_pub.add;
3457 END IF;
3458
3459 x_return_status := fnd_api.g_ret_sts_error;
3460 RETURN;
3461 END IF;
3462 END IF;
3463
3464 ----------------------- budget_flag ------------------------
3465 IF p_fund_rec.budget_flag <> fnd_api.g_miss_char
3466 AND p_fund_rec.budget_flag IS NOT NULL THEN
3467 IF ozf_utility_pvt.is_y_or_n(p_fund_rec.budget_flag) = fnd_api.g_false THEN
3468 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3469 IF p_fund_rec.fund_type = 'QUOTA' THEN
3470 fnd_message.set_name('OZF', 'OZF_TP_BAD_QUOTA_FLAG');
3471 ELSE
3472 fnd_message.set_name('OZF', 'OZF_FUND_BAD_BUDGET_FLAG');
3473 END IF;
3474 fnd_msg_pub.add;
3475 END IF;
3476
3477 x_return_status := fnd_api.g_ret_sts_error;
3478 RETURN;
3479 END IF;
3480 END IF;
3481
3482 ----------------------- earned_flag ------------------------
3483 IF p_fund_rec.earned_flag <> fnd_api.g_miss_char
3484 AND p_fund_rec.earned_flag IS NOT NULL THEN
3485 IF ozf_utility_pvt.is_y_or_n(p_fund_rec.earned_flag) = fnd_api.g_false THEN
3486 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3487 fnd_message.set_name('OZF', 'OZF_FUND_BAD_EARNED_FLAG');
3488 fnd_msg_pub.add;
3489 END IF;
3490
3491 x_return_status := fnd_api.g_ret_sts_error;
3492 RETURN;
3493 END IF;
3494 END IF;
3495 -- check other flags
3496
3497 END check_fund_flag_items;
3498
3499 ---------------------------------------------------------------------
3500 -- PROCEDURE
3501 -- Check_Fund_Items
3502 --
3503 -- HISTORY
3504 -- 02/02/2000 Shitij Vatsa Create.
3505 ---------------------------------------------------------------------
3506 PROCEDURE check_fund_items(
3507 p_validation_mode IN VARCHAR2 := jtf_plsql_api.g_create
3508 ,x_return_status OUT NOCOPY VARCHAR2
3509 ,p_fund_rec IN fund_rec_type)
3510 IS
3511 BEGIN
3512 ----dbms_output.put_line('Calling Req_Items');
3513 check_fund_req_items(p_fund_rec => p_fund_rec, x_return_status => x_return_status);
3514
3515 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3516 RETURN;
3517 END IF;
3518
3519 ----dbms_output.put_line('Calling Uk_Items');
3520 check_fund_uk_items(
3521 p_fund_rec => p_fund_rec
3522 ,p_validation_mode => p_validation_mode
3523 ,x_return_status => x_return_status);
3524
3525 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3526 RETURN;
3527 END IF;
3528
3529 ----dbms_output.put_line('Calling Fk_Items');
3530 check_fund_fk_items(p_fund_rec => p_fund_rec, x_return_status => x_return_status);
3531
3532 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3533 RETURN;
3534 END IF;
3535
3536 ----dbms_output.put_line('Calling Lookup_Items');
3537
3538 check_fund_lookup_items(p_fund_rec => p_fund_rec, x_return_status => x_return_status);
3539
3540 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3541 RETURN;
3542 END IF;
3543
3544 ----dbms_output.put_line('Calling Flag_Items');
3545 check_fund_flag_items(p_fund_rec => p_fund_rec, x_return_status => x_return_status);
3546
3547 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3548 RETURN;
3549 END IF;
3550 END check_fund_items;
3551
3552
3553
3554 ---------------------------------------------------------------------
3555 -- PROCEDURE
3556 -- Check_Fund_Record
3557 --
3558 -- HISTORY
3559 -- 02/02/2000 Shitij Vatsa Create.
3560 -- 06/14/2000 Mumu Pande Added validation and chaged some of the earlier validations
3561 -- 01/20/2001 Mumu Pande Rempved all fund inter entity validation to procedure check_fund_inter_entity
3562 -- 01/20/2001 Mumu Pande Added all calls for fund accrual validation
3563 -- 11/05/2003 yzhao: fix bug 3238497 - allow fully accrual budget to go below 0
3564 ---------------------------------------------------------------------
3565 PROCEDURE check_fund_record(
3566 p_fund_rec IN fund_rec_type
3567 ,p_complete_rec IN fund_rec_type
3568 ,p_mode IN VARCHAR2
3569 ,x_return_status OUT NOCOPY VARCHAR2)
3570 IS
3571 l_fund_id NUMBER;
3572 l_start_date DATE;
3573 l_end_date DATE;
3574
3575 -- Check old fund status
3576 CURSOR c_old_status(
3577 cv_fund_id IN NUMBER)
3578 IS
3579 SELECT status_code, original_budget
3580 FROM ozf_funds_all_b
3581 WHERE fund_id = cv_fund_id;
3582
3583 CURSOR c_offer_org(p_list_header_id IN NUMBER)
3584 IS
3585 SELECT org_id
3586 FROM ozf_offers
3587 WHERE qp_list_header_id = p_list_header_id;
3588
3589 l_fund_old_status VARCHAR2(30);
3590 l_fund_old_amount NUMBER;
3591 l_return_status VARCHAR2(1);
3592 l_resource_id NUMBER;
3593 l_offer_org NUMBER := NULL;
3594 l_offer_ledger NUMBER;
3595 l_offer_ledgerName VARCHAR2(50);
3596
3597 BEGIN
3598 x_return_status := fnd_api.g_ret_sts_success;
3599 -- Check all modes validations
3600
3601 -- Ensure that start date is greater than the end date --
3602 l_start_date := p_complete_rec.start_date_active;
3603 l_end_date := p_complete_rec.end_date_active;
3604 IF p_complete_rec.status_code = 'DRAFT' THEN
3605 IF p_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
3606 -- yzhao 07/15/2002 fix bug 2457199 UNABLE TO CREATE FULLY ACCRUED BUDGET DUE TO START DATE PROB
3607 -- start date passed in is midnight of the selected day. So trunc sysdate to get midnight time
3608 IF NVL(l_start_date, sysdate) < trunc(sysdate) THEN
3609 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3610 fnd_message.set_name('OZF', 'OZF_FUND_STARTDATE_MISMATCH');
3611 fnd_msg_pub.add;
3612 END IF;
3613
3614 x_return_status := fnd_api.g_ret_sts_error;
3615 END IF;
3616 END IF ;
3617 END IF;
3618
3619 /* yzhao: 01/14/2003 fix BUG 2750445 - UNABLE TO CLOSE BUDGET, BUDGET 'END DATE' CANNOT BE BEFORE TODAY'S DATE
3620 IF p_fund_rec.end_date_active <> FND_API.g_miss_date
3621 AND l_end_date IS NOT NULL OR p_complete_rec.status_code = 'DRAFT'
3622 AND p_fund_rec.status_code = FND_API.g_miss_char THEN
3623 */
3624 IF p_complete_rec.status_code NOT IN ('CLOSED','CANCELLED','ARCHIVED') AND
3625 l_end_date IS NOT NULL THEN
3626 -- validate only if status changes or date changes
3627 OPEN c_old_status(p_fund_rec.fund_id);
3628 FETCH c_old_status INTO l_fund_old_status,l_fund_old_amount;
3629 CLOSE c_old_status;
3630 IF ((p_fund_rec.status_code <> FND_API.G_MISS_CHAR AND
3631 p_fund_rec.status_code <> l_fund_old_status) OR
3632 p_fund_rec.end_date_active <> FND_API.g_miss_date) THEN
3633 /* yzhao: 01/14/2003 fix bug 2750445 ends */
3634
3635 -- yzhao 09/03/2002 fix bug 2540628 TST 1158.7 FUNC MASTER : CANNOT END DATE A BUDGET ON CURRENT DATE
3636 -- end date passed in is midnight of the selected day. So trunc sysdate to get midnight time
3637 -- IF NVL(l_end_date,sysdate) < sysdate THEN
3638 /* kdass 28-Dec-2004 fix for 11.5.10 bug 4089720, when the fund is created from mass transfer,
3639 do not check for end date */
3640 --IF NVL(l_end_date,sysdate) < trunc(sysdate) THEN
3641 IF NVL(l_end_date,sysdate) < trunc(sysdate) AND p_fund_rec.prev_fund_id IS NULL THEN
3642 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3643 IF p_fund_rec.fund_type = 'QUOTA' THEN
3644 fnd_message.set_name('OZF', 'OZF_TP_ENDDATE_MISMATCH');
3645 ELSE
3646 fnd_message.set_name('OZF', 'OZF_FUND_ENDDATE_MISMATCH');
3647 END IF;
3648 fnd_msg_pub.add;
3649 END IF;
3650 x_return_status := fnd_api.g_ret_sts_error;
3651 END IF;
3652 END IF;
3653 END IF;
3654
3655 /* -- Budget Amount cannot be updated for an active budget. #3570045 -- reverted change.
3656 IF p_complete_rec.status_code = 'ACTIVE' THEN
3657 OPEN c_old_status(p_fund_rec.fund_id);
3658 FETCH c_old_status INTO l_fund_old_status, l_fund_old_amount;
3659 CLOSE c_old_status;
3660
3661 IF l_fund_old_status = 'ACTIVE' AND p_complete_rec.original_budget <> l_fund_old_amount THEN
3662 fnd_message.set_name('OZF', 'OZF_ACTIVE_FUND_AMT');
3663 fnd_msg_pub.add;
3664 x_return_status := fnd_api.g_ret_sts_error;
3665 END IF;
3666 END IF;
3667 */
3668 --original amount should be within 15 digits
3669 IF p_complete_rec.original_budget > 999999999999999 THEN
3670 IF p_fund_rec.fund_type = 'QUOTA' THEN
3671 fnd_message.set_name('OZF', 'OZF_TP_MAX_AMT_EXCEEDED');
3672 ELSE
3673 fnd_message.set_name('OZF', 'OZF_FUND_MAX_AMT_EXCEEDED');
3674 END IF;
3675 fnd_msg_pub.add;
3676 x_return_status := fnd_api.g_ret_sts_error;
3677 END IF;
3678
3679 IF l_start_date > l_end_date THEN
3680 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3681 fnd_message.set_name('OZF', 'OZF_FUND_DATE_MISMATCH');
3682 fnd_msg_pub.add;
3683 END IF;
3684
3685 x_return_status := fnd_api.g_ret_sts_error;
3686 END IF;
3687
3688 IF p_mode = jtf_plsql_api.g_update THEN
3689 IF p_fund_rec.parent_fund_id <> fnd_api.g_miss_num
3690 AND p_fund_rec.parent_fund_id IS NOT NULL THEN
3691 IF p_complete_rec.fund_id = p_complete_rec.parent_fund_id THEN
3692 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3693 IF p_fund_rec.fund_type = 'QUOTA' THEN
3694 fnd_message.set_name('OZF', 'OZF_TP_WRONG_PARENT');
3695 ELSE
3696 fnd_message.set_name('OZF', 'OZF_FUND_WRONG_PARENT');
3697 END IF;
3698 fnd_msg_pub.add;
3699 x_return_status := fnd_api.g_ret_sts_error;
3700 END IF;
3701 END IF;
3702 END IF;
3703 END IF;
3704
3705 -- added by mpande
3706 -- holdback amt cannot be greater than total budget which is equal to
3707 -- (ORG_BUDG - Holdback_amt + Trasfered_in_amt - Transfered_out_amt)
3708 -- in a active fund where as in a draft fund holdback should be more than original budget
3709 IF p_complete_rec.status_code = 'ACTIVE' THEN
3710 -- 11/05/2003 yzhao: fix bug 3238497 - allow fully accrual budget to go below 0
3711 IF p_complete_rec.fund_type <> 'FULLY_ACCRUED' OR
3712 p_complete_rec.original_budget >= 0 OR
3713 NVL(p_complete_rec.holdback_amt, 0) <> 0 THEN
3714 IF p_complete_rec.holdback_amt >
3715 ( NVL(p_complete_rec.original_budget, 0) +
3716 NVL(p_complete_rec.transfered_in_amt, 0) -
3717 NVL(p_complete_rec.transfered_out_amt, 0)) THEN
3718 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3719 fnd_message.set_name('OZF', 'OZF_EXCESS_HOLDBACK_AMT');
3720 fnd_msg_pub.add;
3721 END IF;
3722 x_return_status := fnd_api.g_ret_sts_error;
3723 END IF;
3724 END IF;
3725
3726 -- hold back amount should not be negative fix for bug#3352216
3727 IF p_complete_rec.holdback_amt < 0 THEN
3728 fnd_message.set_name('OZF', 'OZF_FUND_NO_HOLDBACK_BUDGET');
3729 fnd_msg_pub.add;
3730 x_return_status := fnd_api.g_ret_sts_error;
3731 END IF;
3732
3733 ELSE
3734 IF p_complete_rec.holdback_amt > (NVL(p_complete_rec.original_budget, 0)) THEN
3735 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3736 fnd_message.set_name('OZF', 'OZF_EXCESS_HOLDBACK_AMT');
3737 fnd_msg_pub.add;
3738 END IF;
3739
3740 x_return_status := fnd_api.g_ret_sts_error;
3741 --RAISE FND_API.g_exc_error;
3742 END IF;
3743
3744 -- holdback amount should not be negative. Fix for bug#3352216
3745 IF p_complete_rec.holdback_amt < 0 THEN
3746 fnd_message.set_name('OZF', 'OZF_FUND_NO_HOLDBACK_BUDGET');
3747 fnd_msg_pub.add;
3748 x_return_status := fnd_api.g_ret_sts_error;
3749 END IF;
3750
3751 END IF;
3752
3753 -- For a accrual type fund whenever it becomes active the original budget should be 0
3754 -- 01/20/2001 Mumu Pande Added all calls for fund accrual validation
3755 -- 6/11/2002 mpande Check for Original Budget = 0 when the status is not ACTIVE
3756 IF p_complete_rec.fund_type = 'FULLY_ACCRUED' THEN
3757
3758 /* yzhao: 02/04/2003 fix bug: can not close an accrual budget if it already accrued some fund
3759 IF NVL(p_complete_rec.original_budget, 0) <> 0 AND p_complete_rec.status_code IN ('DRAFT','CLOSED','CANCELLED','ARCHIVED') THEN
3760 */
3761 IF NVL(p_complete_rec.original_budget, 0) <> 0 AND p_complete_rec.status_code = 'DRAFT' THEN
3762 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3763 fnd_message.set_name('OZF', 'OZF_ACCRUAL_NO_ORG_BUDGET');
3764 fnd_msg_pub.add;
3765 END IF;
3766
3767 x_return_status := fnd_api.g_ret_sts_error;
3768 END IF;
3769 /* 11.5.9
3770 IF p_complete_rec.accrual_basis IS NULL THEN
3771 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3772 fnd_message.set_name('OZF', 'OZF_ACCRUAL_NO_BASIS');
3773 fnd_msg_pub.add;
3774 END IF;
3775
3776 x_return_status := fnd_api.g_ret_sts_error;
3777 END IF;*/
3778 /*
3779 IF p_complete_rec.accrual_operand IS NULL THEN
3780 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3781 fnd_message.set_name('OZF', 'OZF_ACCRUAL_NO_OPERAND');
3782 fnd_msg_pub.add;
3783 END IF;
3784
3785 x_return_status := fnd_api.g_ret_sts_error;
3786 END IF;
3787 */
3788 /* -- we donot need to give the UOM
3789 IF p_complete_rec.accrual_uom IS NULL THEN
3790 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3791 fnd_message.set_name('OZF', 'OZF_ACCRUAL_NO_UOM');
3792 fnd_msg_pub.add;
3793 END IF;
3794
3795 x_return_status := fnd_api.g_ret_sts_error;
3796 END IF;
3797 */
3798 /*
3799 -- sangara - R12 enhancement - not mandatory, as they are moved to Market Options cuecard
3800 IF p_complete_rec.accrual_phase IS NULL THEN
3801 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3802 fnd_message.set_name('OZF', 'OZF_ACCRUAL_NO_PHASE');
3803 fnd_msg_pub.add;
3804 END IF;
3805
3806 x_return_status := fnd_api.g_ret_sts_error;
3807 END IF;
3808 */
3809 /*
3810 IF NVL(p_complete_rec.accrual_rate, 0) <= 0 THEN
3811 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3812 fnd_message.set_name('OZF', 'OZF_ACCRUAL_NO_RATE');
3813 fnd_msg_pub.add;
3814 END IF;
3815
3816 x_return_status := fnd_api.g_ret_sts_error;
3817 END IF;
3818 -- default the quantity to 1
3819 /*
3820 IF NVL(p_complete_rec.accrual_quantity, 0) <= 0 THEN
3821 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3822 fnd_message.set_name('OZF', 'OZF_ACCRUAL_NO_QUANTITY');
3823 fnd_msg_pub.add;
3824 END IF;
3825
3826 x_return_status := fnd_api.g_ret_sts_error;
3827 END IF;
3828
3829 --- for a active accrual fund product eligibiilty should exist 01/20/2001 mpande
3830 -- not when a active fund is created automatically
3831 IF p_complete_rec.status_code = 'ACTIVE' AND
3832 p_mode <> jtf_plsql_api.g_create THEN
3833 Ozf_fundrules_pvt.check_product_elig_exists(
3834 p_complete_rec.fund_id
3835 ,l_return_status);
3836
3837 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3838 x_return_status := l_return_status;
3839 END IF;
3840 END IF; */
3841 END IF;
3842
3843 -- Check for update validations
3844 IF p_mode = jtf_plsql_api.g_update THEN
3845 -- mpande added on Sep 11 for giving update access to owner and persons who have access with edit metric flag = 'Y'
3846 l_resource_id := ozf_utility_pvt.get_resource_id(p_user_id => fnd_global.user_id);
3847
3848 IF G_DEBUG THEN
3849 ozf_utility_pvt.debug_message('resource'||ams_access_pvt.check_update_access(p_complete_rec.fund_id, 'FUND', l_resource_id, 'USER'));
3850 END IF;
3851
3852 IF l_resource_id <> -1 THEN
3853 IF ams_access_pvt.check_update_access(
3854 p_complete_rec.fund_id
3855 ,'FUND'
3856 ,l_resource_id
3857 ,'USER') <>
3858 'F' THEN
3859 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3860 IF p_fund_rec.fund_type = 'QUOTA' THEN
3861 fnd_message.set_name('OZF', 'OZF_TP_ILLEGAL_OWNER');
3862 ELSE
3863 fnd_message.set_name('OZF', 'OZF_FUND_ILLEGAL_OWNER');
3864 END IF;
3865 fnd_msg_pub.add;
3866 END IF;
3867
3868 x_return_status := fnd_api.g_ret_sts_error;
3869 END IF;
3870 ELSIF l_resource_id = -1 THEN
3871 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3872 fnd_message.set_name('OZF', 'OZF_NO_RESOURCE_ID');
3873 fnd_msg_pub.add;
3874 END IF;
3875
3876 x_return_status := fnd_api.g_ret_sts_error;
3877 END IF;
3878
3879 --kdass 10-OCT-05 - R12 bug 4613689 validate accrual budget's ledger and offer's org
3880 IF p_fund_rec.fund_type = 'FULLY_ACCRUED' AND p_complete_rec.ledger_id IS NOT NULL THEN
3881 OPEN c_offer_org (p_complete_rec.plan_id);
3882 FETCH c_offer_org INTO l_offer_org;
3883 CLOSE c_offer_org;
3884
3885 IF l_offer_org IS NOT NULL THEN
3886 -- Get offer's ledger
3887 MO_UTILS.Get_Ledger_Info (p_operating_unit => l_offer_org,
3888 p_ledger_id => l_offer_ledger,
3889 p_ledger_name => l_offer_ledgerName
3890 );
3891 IF p_complete_rec.ledger_id <> l_offer_ledger THEN
3892 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3893 fnd_message.set_name('OZF', 'OZF_BUDGET_OFFR_LEDG_MISMATCH');
3894 fnd_msg_pub.add;
3895 END IF;
3896 x_return_status := fnd_api.g_ret_sts_error;
3897 END IF;
3898 END IF;
3899 END IF;
3900
3901 END IF;
3902
3903 END check_fund_record;
3904
3905 ---------------------------------------------------------------------
3906 -- PROCEDURE
3907 -- check_fund_inter_entity
3908 --
3909 -- HISTORY
3910 -- 1/15/01 mpande Created.
3911 ---------------------------------------------------------------------
3912
3913 PROCEDURE check_fund_inter_entity(
3914 p_fund_rec IN fund_rec_type
3915 ,p_complete_rec IN fund_rec_type
3916 ,p_validation_mode IN VARCHAR2
3917 ,x_return_status OUT NOCOPY VARCHAR2)
3918 IS
3919 l_return_status VARCHAR2(1);
3920 BEGIN
3921 x_return_status := fnd_api.g_ret_sts_success;
3922
3923 ------------------- check calendar ----------------------
3924 IF p_fund_rec.fund_calendar <> fnd_api.g_miss_char
3925 OR p_fund_rec.start_period_name <> fnd_api.g_miss_char
3926 OR p_fund_rec.end_period_name <> fnd_api.g_miss_char
3927 OR p_fund_rec.start_date_active <> fnd_api.g_miss_date
3928 OR p_fund_rec.end_date_active <> fnd_api.g_miss_date THEN
3929 Ozf_fundrules_pvt.check_fund_calendar(
3930 p_complete_rec.fund_calendar
3931 ,p_complete_rec.start_period_name
3932 ,p_complete_rec.end_period_name
3933 ,p_complete_rec.start_date_active
3934 ,p_complete_rec.end_date_active
3935 ,p_complete_rec.fund_type
3936 ,l_return_status);
3937
3938 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3939 x_return_status := l_return_status;
3940 END IF;
3941 END IF;
3942
3943 ------------------- check dates ------------------------------
3944 IF p_fund_rec.start_date_active <> fnd_api.g_miss_date
3945 OR p_fund_rec.end_date_active <> fnd_api.g_miss_date THEN
3946 Ozf_fundrules_pvt.check_fund_dates_vs_parent(
3947 p_complete_rec.parent_fund_id
3948 ,p_complete_rec.start_date_active
3949 ,p_complete_rec.end_date_active
3950 ,l_return_status);
3951
3952 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3953 x_return_status := l_return_status;
3954 END IF;
3955
3956 IF p_validation_mode = jtf_plsql_api.g_update THEN
3957 Ozf_fundrules_pvt.check_fund_dates_vs_child(
3958 p_complete_rec.fund_id
3959 ,p_complete_rec.start_date_active
3960 ,p_complete_rec.end_date_active
3961 ,l_return_status);
3962
3963 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3964 x_return_status := l_return_status;
3965 END IF;
3966 END IF;
3967 END IF;
3968
3969 ------------------- check budget amounts ------------------------------
3970 -- only in planning stage --07/23/2001 mpande
3971 IF p_complete_rec.status_code NOT IN ('ACTIVE','ON_HOLD','CANCELLED','ARCHIVED','CLOSED') THEN
3972 IF p_fund_rec.original_budget <> fnd_api.g_miss_num
3973 OR p_fund_rec.transfered_in_amt <> fnd_api.g_miss_num
3974 OR p_fund_rec.transfered_out_amt <> fnd_api.g_miss_num THEN
3975 -- updated 09/04/2001 mpande for Multi Currency Child
3976 Ozf_fundrules_pvt.check_fund_amount_vs_parent(
3977 p_complete_rec.parent_fund_id
3978 ,p_complete_rec.currency_code_tc
3979 ,p_complete_rec.original_budget
3980 ,l_return_status);
3981
3982 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3983 x_return_status := l_return_status;
3984 END IF;
3985
3986 IF p_validation_mode = jtf_plsql_api.g_update THEN
3987 -- updated 09/04/2001 mpande for Multi Currency Child
3988 Ozf_fundrules_pvt.check_fund_amount_vs_child(
3989 p_complete_rec.fund_id
3990 ,p_complete_rec.original_budget
3991 ,p_complete_rec.transfered_in_amt
3992 ,p_complete_rec.transfered_out_amt
3993 ,p_complete_rec.currency_code_tc
3994 ,l_return_status);
3995
3996 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3997 x_return_status := l_return_status;
3998 END IF;
3999 END IF;
4000 END IF;
4001 END IF;
4002
4003 ------------------- check fund type ------------------------------
4004 IF p_fund_rec.fund_type <> fnd_api.g_miss_char
4005 OR p_fund_rec.parent_fund_id <> fnd_api.g_miss_num THEN
4006 --- the chikd parent validation is done always
4007 Ozf_fundrules_pvt.check_fund_type_vs_parent(
4008 p_complete_rec.parent_fund_id
4009 ,p_complete_rec.fund_type
4010 ,l_return_status);
4011
4012 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4013 x_return_status := l_return_status;
4014 END IF;
4015
4016 -- check for change of fund type only when the status is going active
4017 -- cause in a draft mode the user can change the fund type
4018 IF p_validation_mode = jtf_plsql_api.g_update
4019 AND p_complete_rec.status_code = 'ACTIVE' THEN
4020 Ozf_fundrules_pvt.check_fund_type_vs_child(
4021 p_complete_rec.fund_id
4022 ,p_complete_rec.fund_type
4023 ,l_return_status);
4024
4025 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4026 x_return_status := l_return_status;
4027 END IF;
4028 END IF;
4029 END IF;
4030
4031 ------------------- check fund curr ------------------------------
4032 --09/04/2001 mpande commented
4033 /*
4034 IF p_fund_rec.currency_code_tc <> fnd_api.g_miss_char
4035 OR p_fund_rec.parent_fund_id <> fnd_api.g_miss_num THEN
4036 --- the child parent validation is done always
4037 Ozf_fundrules_pvt.check_fund_curr_vs_parent(
4038 p_complete_rec.parent_fund_id
4039 ,p_complete_rec.currency_code_tc
4040 ,l_return_status);
4041
4042 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4043 x_return_status := l_return_status;
4044 END IF;
4045
4046 -- check for change of fund currency only when the status is going active
4047 -- cause in a draft mode the user can change the fund currency
4048 IF p_validation_mode = jtf_plsql_api.g_update
4049 AND p_complete_rec.status_code = 'ACTIVE' THEN
4050 Ozf_fundrules_pvt.check_fund_curr_vs_child(
4051 p_complete_rec.fund_id
4052 ,p_complete_rec.currency_code_tc
4053 ,l_return_status);
4054
4055 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4056 x_return_status := l_return_status;
4057 END IF;
4058 END IF;
4059 END IF;
4060 */
4061
4062 ------------------- check fund_status ------------------------------
4063 IF p_fund_rec.status_code <> fnd_api.g_miss_char
4064 OR p_fund_rec.parent_fund_id <> fnd_api.g_miss_num THEN
4065 Ozf_fundrules_pvt.check_fund_status_vs_parent(
4066 p_complete_rec.parent_fund_id
4067 ,p_complete_rec.status_code
4068 ,l_return_status);
4069
4070 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4071 x_return_status := l_return_status;
4072 END IF;
4073 END IF;
4074 END check_fund_inter_entity;
4075
4076
4077
4078 ---------------------------------------------------------------------
4079 -- PROCEDURE
4080 -- Init_Fund_Rec
4081 --
4082 -- HISTORY
4083 -- 02/02/2000 Shitij Vatsa Create.
4084 ---------------------------------------------------------------------
4085 PROCEDURE init_fund_rec(
4086 x_fund_rec OUT NOCOPY fund_rec_type)
4087 IS
4088 BEGIN
4089
4090 RETURN;
4091 END init_fund_rec;
4092
4093
4094
4095 ---------------------------------------------------------------------
4096 -- PROCEDURE
4097 -- Complete_Fund_Rec
4098 --
4099 -- HISTORY
4100 -- 02/02/2000 Shitij Vatsa Create.
4101 ---------------------------------------------------------------------
4102 PROCEDURE complete_fund_rec(
4103 p_fund_rec IN fund_rec_type
4104 ,x_complete_rec OUT NOCOPY fund_rec_type)
4105 IS
4106 CURSOR c_fund
4107 IS
4108 SELECT *
4109 FROM ozf_funds_all_vl
4110 WHERE fund_id = p_fund_rec.fund_id;
4111
4112 l_fund_rec c_fund%ROWTYPE;
4113 BEGIN
4114 x_complete_rec := p_fund_rec;
4115 OPEN c_fund;
4116 FETCH c_fund INTO l_fund_rec;
4117
4118 IF c_fund%NOTFOUND THEN
4119 CLOSE c_fund;
4120
4121 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
4122 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
4123 fnd_msg_pub.add;
4124 END IF;
4125
4126 RAISE fnd_api.g_exc_error;
4127 END IF;
4128
4129 CLOSE c_fund;
4130
4131 IF p_fund_rec.fund_number = fnd_api.g_miss_char THEN
4132 x_complete_rec.fund_number := NULL;
4133 END IF;
4134 IF p_fund_rec.fund_number IS NULL THEN
4135 x_complete_rec.fund_number := l_fund_rec.fund_number;
4136 END IF;
4137
4138 IF p_fund_rec.parent_fund_id = fnd_api.g_miss_num THEN
4139 x_complete_rec.parent_fund_id := NULL;
4140 END IF;
4141 IF p_fund_rec.parent_fund_id IS NULL THEN
4142 x_complete_rec.parent_fund_id := l_fund_rec.parent_fund_id;
4143 END IF;
4144
4145 IF p_fund_rec.category_id = fnd_api.g_miss_num THEN
4146 x_complete_rec.category_id := NULL;
4147 END IF;
4148 IF p_fund_rec.category_id IS NULL THEN
4149 x_complete_rec.category_id := l_fund_rec.category_id;
4150 END IF;
4151
4152 IF p_fund_rec.fund_type = fnd_api.g_miss_char THEN
4153 x_complete_rec.fund_type := NULL;
4154 END IF;
4155 IF p_fund_rec.fund_type IS NULL THEN
4156 x_complete_rec.fund_type := l_fund_rec.fund_type;
4157 END IF;
4158
4159 IF p_fund_rec.fund_usage = fnd_api.g_miss_char THEN
4160 x_complete_rec.fund_usage := NULL;
4161 END IF;
4162 IF p_fund_rec.fund_usage IS NULL THEN
4163 x_complete_rec.fund_usage := l_fund_rec.fund_usage;
4164 END IF;
4165
4166 IF p_fund_rec.status_code = fnd_api.g_miss_char THEN
4167 x_complete_rec.status_code := NULL;
4168 END IF;
4169 IF p_fund_rec.status_code IS NULL THEN
4170 x_complete_rec.status_code := l_fund_rec.status_code;
4171 END IF;
4172
4173 IF p_fund_rec.user_status_id = fnd_api.g_miss_num THEN
4174 x_complete_rec.user_status_id := NULL;
4175 END IF;
4176 IF p_fund_rec.user_status_id IS NULL THEN
4177 x_complete_rec.user_status_id := l_fund_rec.user_status_id;
4178 END IF;
4179
4180 IF p_fund_rec.status_date = fnd_api.g_miss_date THEN
4181 x_complete_rec.status_date := NULL;
4182 END IF;
4183 IF p_fund_rec.status_date IS NULL THEN
4184 x_complete_rec.status_date := l_fund_rec.status_date;
4185 END IF;
4186
4187 IF p_fund_rec.accrued_liable_account = fnd_api.g_miss_num THEN
4188 x_complete_rec.accrued_liable_account := NULL;
4189 END IF;
4190 IF p_fund_rec.accrued_liable_account IS NULL THEN
4191 x_complete_rec.accrued_liable_account := l_fund_rec.accrued_liable_account;
4192 END IF;
4193
4194 IF p_fund_rec.ded_adjustment_account = fnd_api.g_miss_num THEN
4195 x_complete_rec.ded_adjustment_account := NULL;
4196 END IF;
4197 IF p_fund_rec.ded_adjustment_account IS NULL THEN
4198 x_complete_rec.ded_adjustment_account := l_fund_rec.ded_adjustment_account;
4199 END IF;
4200
4201 IF p_fund_rec.liability_flag = fnd_api.g_miss_char THEN
4202 x_complete_rec.liability_flag := NULL;
4203 END IF;
4204 IF p_fund_rec.liability_flag IS NULL THEN
4205 x_complete_rec.liability_flag := l_fund_rec.liability_flag;
4206 END IF;
4207
4208 IF p_fund_rec.set_of_books_id = fnd_api.g_miss_num THEN
4209 x_complete_rec.set_of_books_id := NULL;
4210 END IF;
4211 IF p_fund_rec.set_of_books_id IS NULL THEN
4212 x_complete_rec.set_of_books_id := l_fund_rec.set_of_books_id;
4213 END IF;
4214
4215 IF p_fund_rec.start_period_id = fnd_api.g_miss_num THEN
4216 x_complete_rec.start_period_id := NULL;
4217 END IF;
4218 IF p_fund_rec.start_period_id IS NULL THEN
4219 x_complete_rec.start_period_id := l_fund_rec.start_period_id;
4220 END IF;
4221
4222 IF p_fund_rec.end_period_id = fnd_api.g_miss_num THEN
4223 x_complete_rec.end_period_id := NULL;
4224 END IF;
4225 IF p_fund_rec.end_period_id IS NULL THEN
4226 x_complete_rec.end_period_id := l_fund_rec.end_period_id;
4227 END IF;
4228
4229 IF p_fund_rec.start_date_active = fnd_api.g_miss_date THEN
4230 x_complete_rec.start_date_active := NULL;
4231 END IF;
4232 IF p_fund_rec.start_date_active IS NULL THEN
4233 x_complete_rec.start_date_active := l_fund_rec.start_date_active;
4234 END IF;
4235
4236 IF p_fund_rec.end_date_active = fnd_api.g_miss_date THEN
4237 x_complete_rec.end_date_active := NULL;
4238 END IF;
4239 IF p_fund_rec.end_date_active IS NULL THEN
4240 x_complete_rec.end_date_active := l_fund_rec.end_date_active;
4241 END IF;
4242
4243 IF p_fund_rec.budget_amount_tc = fnd_api.g_miss_num THEN
4244 x_complete_rec.budget_amount_tc := NULL;
4245 END IF;
4246 IF p_fund_rec.budget_amount_tc IS NULL THEN
4247 x_complete_rec.budget_amount_tc := l_fund_rec.budget_amount_tc;
4248 END IF;
4249
4250 IF p_fund_rec.budget_amount_fc = fnd_api.g_miss_num THEN
4251 x_complete_rec.budget_amount_fc := NULL;
4252 END IF;
4253 IF p_fund_rec.budget_amount_fc IS NULL THEN
4254 x_complete_rec.budget_amount_fc := l_fund_rec.budget_amount_fc;
4255 END IF;
4256
4257 IF p_fund_rec.available_amount = fnd_api.g_miss_num THEN
4258 x_complete_rec.available_amount := NULL;
4259 END IF;
4260 IF p_fund_rec.available_amount IS NULL THEN
4261 x_complete_rec.available_amount := l_fund_rec.available_amount;
4262 END IF;
4263
4264 IF p_fund_rec.distributed_amount = fnd_api.g_miss_num THEN
4265 x_complete_rec.distributed_amount := NULL;
4266 END IF;
4267 IF p_fund_rec.distributed_amount IS NULL THEN
4268 x_complete_rec.distributed_amount := l_fund_rec.distributed_amount;
4269 END IF;
4270
4271 IF p_fund_rec.currency_code_tc = fnd_api.g_miss_char THEN
4272 x_complete_rec.currency_code_tc := NULL;
4273 END IF;
4274 IF p_fund_rec.currency_code_tc IS NULL THEN
4275 x_complete_rec.currency_code_tc := l_fund_rec.currency_code_tc;
4276 END IF;
4277
4278 IF p_fund_rec.currency_code_fc = fnd_api.g_miss_char THEN
4279 x_complete_rec.currency_code_fc := NULL;
4280 END IF;
4281 IF p_fund_rec.currency_code_fc IS NULL THEN
4282 x_complete_rec.currency_code_fc := l_fund_rec.currency_code_fc;
4283 END IF;
4284
4285 IF p_fund_rec.exchange_rate_type = fnd_api.g_miss_char THEN
4286 x_complete_rec.exchange_rate_type := NULL;
4287 END IF;
4288 IF p_fund_rec.exchange_rate_type IS NULL THEN
4289 x_complete_rec.exchange_rate_type := l_fund_rec.exchange_rate_type;
4290 END IF;
4291
4292 IF p_fund_rec.exchange_rate_date = fnd_api.g_miss_date THEN
4293 x_complete_rec.exchange_rate_date := NULL;
4294 END IF;
4295 IF p_fund_rec.exchange_rate_date IS NULL THEN
4296 x_complete_rec.exchange_rate_date := l_fund_rec.exchange_rate_date;
4297 END IF;
4298
4299 IF p_fund_rec.exchange_rate = fnd_api.g_miss_num THEN
4300 x_complete_rec.exchange_rate := NULL;
4301 END IF;
4302 IF p_fund_rec.exchange_rate IS NULL THEN
4303 x_complete_rec.exchange_rate := l_fund_rec.exchange_rate;
4304 END IF;
4305
4306 IF p_fund_rec.department_id = fnd_api.g_miss_num THEN
4307 x_complete_rec.department_id := NULL;
4308 END IF;
4309 IF p_fund_rec.department_id IS NULL THEN
4310 x_complete_rec.department_id := l_fund_rec.department_id;
4311 END IF;
4312
4313 IF p_fund_rec.costcentre_id = fnd_api.g_miss_num THEN
4314 x_complete_rec.costcentre_id := NULL;
4315 END IF;
4316 IF p_fund_rec.costcentre_id IS NULL THEN
4317 x_complete_rec.costcentre_id := l_fund_rec.costcentre_id;
4318 END IF;
4319
4320 IF p_fund_rec.owner = fnd_api.g_miss_num THEN
4321 x_complete_rec.owner := NULL;
4322 END IF;
4323 IF p_fund_rec.owner IS NULL THEN
4324 x_complete_rec.owner := l_fund_rec.owner;
4325 END IF;
4326
4327 IF p_fund_rec.accrual_method = fnd_api.g_miss_char THEN
4328 x_complete_rec.accrual_method := NULL;
4329 END IF;
4330 IF p_fund_rec.accrual_method IS NULL THEN
4331 x_complete_rec.accrual_method := l_fund_rec.accrual_method;
4332 END IF;
4333
4334 IF p_fund_rec.accrual_operand = fnd_api.g_miss_char THEN
4335 x_complete_rec.accrual_operand := NULL;
4336 END IF;
4337 IF p_fund_rec.accrual_operand IS NULL THEN
4338 x_complete_rec.accrual_operand := l_fund_rec.accrual_operand;
4339 END IF;
4340
4341 IF p_fund_rec.accrual_rate = fnd_api.g_miss_num THEN
4342 x_complete_rec.accrual_rate := NULL;
4343 END IF;
4344 IF p_fund_rec.accrual_rate IS NULL THEN
4345 x_complete_rec.accrual_rate := l_fund_rec.accrual_rate;
4346 END IF;
4347
4348 IF p_fund_rec.accrual_basis = fnd_api.g_miss_char THEN
4349 x_complete_rec.accrual_basis := NULL;
4350 END IF;
4351 IF p_fund_rec.accrual_basis IS NULL THEN
4352 x_complete_rec.accrual_basis := l_fund_rec.accrual_basis;
4353 END IF;
4354
4355 IF p_fund_rec.hierarchy = fnd_api.g_miss_char THEN
4356 x_complete_rec.hierarchy := NULL;
4357 END IF;
4358 IF p_fund_rec.hierarchy IS NULL THEN
4359 x_complete_rec.hierarchy := l_fund_rec.hierarchy;
4360 END IF;
4361
4362 IF p_fund_rec.hierarchy_level = fnd_api.g_miss_char THEN
4363 x_complete_rec.hierarchy_level := NULL;
4364 END IF;
4365 IF p_fund_rec.hierarchy_level IS NULL THEN
4366 x_complete_rec.hierarchy_level := l_fund_rec.hierarchy_level;
4367 END IF;
4368
4369 IF p_fund_rec.hierarchy_id = fnd_api.g_miss_num THEN
4370 x_complete_rec.hierarchy_id := NULL;
4371 END IF;
4372 IF p_fund_rec.hierarchy_id IS NULL THEN
4373 x_complete_rec.hierarchy_id := l_fund_rec.hierarchy_id;
4374 END IF;
4375
4376 IF p_fund_rec.parent_node_id = fnd_api.g_miss_num THEN
4377 x_complete_rec.parent_node_id := NULL;
4378 END IF;
4379 IF p_fund_rec.parent_node_id IS NULL THEN
4380 x_complete_rec.parent_node_id := l_fund_rec.parent_node_id;
4381 END IF;
4382
4383 IF p_fund_rec.node_id = fnd_api.g_miss_num THEN
4384 x_complete_rec.node_id := NULL;
4385 END IF;
4386 IF p_fund_rec.node_id IS NULL THEN
4387 x_complete_rec.node_id := l_fund_rec.node_id;
4388 END IF;
4389
4390 IF p_fund_rec.budget_flag = fnd_api.g_miss_char THEN
4391 x_complete_rec.budget_flag := NULL;
4392 END IF;
4393 IF p_fund_rec.budget_flag IS NULL THEN
4394 x_complete_rec.budget_flag := l_fund_rec.budget_flag;
4395 END IF;
4396
4397 IF p_fund_rec.earned_flag = fnd_api.g_miss_char THEN
4398 x_complete_rec.earned_flag := NULL;
4399 END IF;
4400 IF p_fund_rec.earned_flag IS NULL THEN
4401 x_complete_rec.earned_flag := l_fund_rec.earned_flag;
4402 END IF;
4403
4404 IF p_fund_rec.apply_accrual_on = fnd_api.g_miss_char THEN
4405 x_complete_rec.apply_accrual_on := NULL;
4406 END IF;
4407 IF p_fund_rec.apply_accrual_on IS NULL THEN
4408 x_complete_rec.apply_accrual_on := l_fund_rec.apply_accrual_on;
4409 END IF;
4410
4411 IF p_fund_rec.accrual_phase = fnd_api.g_miss_char THEN
4412 x_complete_rec.accrual_phase := NULL;
4413 END IF;
4414 IF p_fund_rec.accrual_phase IS NULL THEN
4415 x_complete_rec.accrual_phase := l_fund_rec.accrual_phase;
4416 END IF;
4417
4418 IF p_fund_rec.accrual_cap = fnd_api.g_miss_num THEN
4419 x_complete_rec.accrual_cap := NULL;
4420 END IF;
4421 IF p_fund_rec.accrual_cap IS NULL THEN
4422 x_complete_rec.accrual_cap := l_fund_rec.accrual_cap;
4423 END IF;
4424
4425 IF p_fund_rec.accrual_uom = fnd_api.g_miss_char THEN
4426 x_complete_rec.accrual_uom := NULL;
4427 END IF;
4428 IF p_fund_rec.accrual_uom IS NULL THEN
4429 x_complete_rec.accrual_uom := l_fund_rec.accrual_uom;
4430 END IF;
4431
4432
4433 IF p_fund_rec.recal_committed = fnd_api.g_miss_num THEN
4434 x_complete_rec.recal_committed := NULL;
4435 END IF;
4436 IF p_fund_rec.recal_committed IS NULL THEN
4437 x_complete_rec.recal_committed := l_fund_rec.recal_committed;
4438 END IF;
4439
4440
4441 IF p_fund_rec.attribute_category = fnd_api.g_miss_char THEN
4442 x_complete_rec.attribute_category := NULL;
4443 END IF;
4444 IF p_fund_rec.attribute_category IS NULL THEN
4445 x_complete_rec.attribute_category := l_fund_rec.attribute_category;
4446 END IF;
4447
4448 IF p_fund_rec.attribute1 = fnd_api.g_miss_char THEN
4449 x_complete_rec.attribute1 := NULL;
4450 END IF;
4451 IF p_fund_rec.attribute1 IS NULL THEN
4452 x_complete_rec.attribute1 := l_fund_rec.attribute1;
4453 END IF;
4454
4455 IF p_fund_rec.attribute2 = fnd_api.g_miss_char THEN
4456 x_complete_rec.attribute2 := NULL;
4457 END IF;
4458 IF p_fund_rec.attribute2 IS NULL THEN
4459 x_complete_rec.attribute2 := l_fund_rec.attribute2;
4460 END IF;
4461
4462 IF p_fund_rec.attribute3 = fnd_api.g_miss_char THEN
4463 x_complete_rec.attribute3 := NULL;
4464 END IF;
4465 IF p_fund_rec.attribute3 IS NULL THEN
4466 x_complete_rec.attribute3 := l_fund_rec.attribute3;
4467 END IF;
4468
4469 IF p_fund_rec.attribute4 = fnd_api.g_miss_char THEN
4470 x_complete_rec.attribute4 := NULL;
4471 END IF;
4472 IF p_fund_rec.attribute4 IS NULL THEN
4473 x_complete_rec.attribute4 := l_fund_rec.attribute4;
4474 END IF;
4475
4476 IF p_fund_rec.attribute5 = fnd_api.g_miss_char THEN
4477 x_complete_rec.attribute5 := NULL;
4478 END IF;
4479 IF p_fund_rec.attribute5 IS NULL THEN
4480 x_complete_rec.attribute5 := l_fund_rec.attribute5;
4481 END IF;
4482
4483 IF p_fund_rec.attribute6 = fnd_api.g_miss_char THEN
4484 x_complete_rec.attribute6 := NULL;
4485 END IF;
4486 IF p_fund_rec.attribute6 IS NULL THEN
4487 x_complete_rec.attribute6 := l_fund_rec.attribute6;
4488 END IF;
4489
4490 IF p_fund_rec.attribute7 = fnd_api.g_miss_char THEN
4491 x_complete_rec.attribute7 := NULL;
4492 END IF;
4493 IF p_fund_rec.attribute7 IS NULL THEN
4494 x_complete_rec.attribute7 := l_fund_rec.attribute7;
4495 END IF;
4496
4497 IF p_fund_rec.attribute8 = fnd_api.g_miss_char THEN
4498 x_complete_rec.attribute8 := NULL;
4499 END IF;
4500 IF p_fund_rec.attribute8 IS NULL THEN
4501 x_complete_rec.attribute8 := l_fund_rec.attribute8;
4502 END IF;
4503
4504 IF p_fund_rec.attribute9 = fnd_api.g_miss_char THEN
4505 x_complete_rec.attribute9 := NULL;
4506 END IF;
4507 IF p_fund_rec.attribute9 IS NULL THEN
4508 x_complete_rec.attribute9 := l_fund_rec.attribute9;
4509 END IF;
4510
4511 IF p_fund_rec.attribute10 = fnd_api.g_miss_char THEN
4512 x_complete_rec.attribute10 := NULL;
4513 END IF;
4514 IF p_fund_rec.attribute10 IS NULL THEN
4515 x_complete_rec.attribute10 := l_fund_rec.attribute10;
4516 END IF;
4517
4518 IF p_fund_rec.attribute11 = fnd_api.g_miss_char THEN
4519 x_complete_rec.attribute11 := NULL;
4520 END IF;
4521 IF p_fund_rec.attribute11 IS NULL THEN
4522 x_complete_rec.attribute11 := l_fund_rec.attribute11;
4523 END IF;
4524
4525 IF p_fund_rec.attribute12 = fnd_api.g_miss_char THEN
4526 x_complete_rec.attribute12 := NULL;
4527 END IF;
4528 IF p_fund_rec.attribute12 IS NULL THEN
4529 x_complete_rec.attribute12 := l_fund_rec.attribute12;
4530 END IF;
4531
4532 IF p_fund_rec.attribute13 = fnd_api.g_miss_char THEN
4533 x_complete_rec.attribute13 := NULL;
4534 END IF;
4535 IF p_fund_rec.attribute13 IS NULL THEN
4536 x_complete_rec.attribute13 := l_fund_rec.attribute13;
4537 END IF;
4538
4539 IF p_fund_rec.attribute14 = fnd_api.g_miss_char THEN
4540 x_complete_rec.attribute14 := NULL;
4541 END IF;
4542 IF p_fund_rec.attribute14 IS NULL THEN
4543 x_complete_rec.attribute14 := l_fund_rec.attribute14;
4544 END IF;
4545
4546 IF p_fund_rec.attribute15 = fnd_api.g_miss_char THEN
4547 x_complete_rec.attribute15 := NULL;
4548 END IF;
4549 IF p_fund_rec.attribute15 IS NULL THEN
4550 x_complete_rec.attribute15 := l_fund_rec.attribute15;
4551 END IF;
4552
4553 IF p_fund_rec.original_budget = fnd_api.g_miss_num THEN
4554 x_complete_rec.original_budget := NULL;
4555 END IF;
4556 IF p_fund_rec.original_budget IS NULL THEN
4557 x_complete_rec.original_budget := l_fund_rec.original_budget;
4558 END IF;
4559
4560 IF p_fund_rec.transfered_in_amt = fnd_api.g_miss_num THEN
4561 x_complete_rec.transfered_in_amt := NULL;
4562 END IF;
4563 IF p_fund_rec.transfered_in_amt IS NULL THEN
4564 x_complete_rec.transfered_in_amt := l_fund_rec.transfered_in_amt;
4565 END IF;
4566
4567 IF p_fund_rec.transfered_out_amt = fnd_api.g_miss_num THEN
4568 x_complete_rec.transfered_out_amt := NULL;
4569 END IF;
4570 IF p_fund_rec.transfered_out_amt IS NULL THEN
4571 x_complete_rec.transfered_out_amt := l_fund_rec.transfered_out_amt;
4572 END IF;
4573
4574 IF p_fund_rec.holdback_amt = fnd_api.g_miss_num THEN
4575 x_complete_rec.holdback_amt := NULL;
4576 END IF;
4577 IF p_fund_rec.holdback_amt IS NULL THEN
4578 x_complete_rec.holdback_amt := l_fund_rec.holdback_amt;
4579 END IF;
4580
4581 IF p_fund_rec.planned_amt = fnd_api.g_miss_num THEN
4582 x_complete_rec.planned_amt := NULL;
4583 END IF;
4584 IF p_fund_rec.planned_amt IS NULL THEN
4585 x_complete_rec.planned_amt := l_fund_rec.planned_amt;
4586 END IF;
4587
4588 IF p_fund_rec.committed_amt = fnd_api.g_miss_num THEN
4589 x_complete_rec.committed_amt := NULL;
4590 END IF;
4591 IF p_fund_rec.committed_amt IS NULL THEN
4592 x_complete_rec.committed_amt := l_fund_rec.committed_amt;
4593 END IF;
4594
4595 -- yzhao: 11.5.10
4596 IF p_fund_rec.utilized_amt = fnd_api.g_miss_num THEN
4597 x_complete_rec.utilized_amt := NULL;
4598 END IF;
4599 IF p_fund_rec.utilized_amt IS NULL THEN
4600 x_complete_rec.utilized_amt := l_fund_rec.utilized_amt;
4601 END IF;
4602
4603 IF p_fund_rec.earned_amt = fnd_api.g_miss_num THEN
4604 x_complete_rec.earned_amt := NULL;
4605 END IF;
4606 IF p_fund_rec.earned_amt IS NULL THEN
4607 x_complete_rec.earned_amt := l_fund_rec.earned_amt;
4608 END IF;
4609
4610 IF p_fund_rec.paid_amt = fnd_api.g_miss_num THEN
4611 x_complete_rec.paid_amt := NULL;
4612 END IF;
4613 IF p_fund_rec.paid_amt IS NULL THEN
4614 x_complete_rec.paid_amt := l_fund_rec.paid_amt;
4615 END IF;
4616
4617 IF p_fund_rec.plan_type = fnd_api.g_miss_char THEN
4618 x_complete_rec.plan_type := NULL;
4619 END IF;
4620 IF p_fund_rec.plan_type IS NULL THEN
4621 x_complete_rec.plan_type := l_fund_rec.plan_type;
4622 END IF;
4623
4624 IF p_fund_rec.plan_id = fnd_api.g_miss_num THEN
4625 x_complete_rec.plan_id := NULL;
4626 END IF;
4627 IF p_fund_rec.plan_id IS NULL THEN
4628 x_complete_rec.plan_id := l_fund_rec.plan_id;
4629 END IF;
4630
4631 IF p_fund_rec.liable_accnt_segments = fnd_api.g_miss_char THEN
4632 x_complete_rec.liable_accnt_segments := NULL;
4633 END IF;
4634 IF p_fund_rec.liable_accnt_segments IS NULL THEN
4635 x_complete_rec.liable_accnt_segments := l_fund_rec.liable_accnt_segments;
4636 END IF;
4637
4638 IF p_fund_rec.adjustment_accnt_segments = fnd_api.g_miss_char THEN
4639 x_complete_rec.adjustment_accnt_segments := NULL;
4640 END IF;
4641 IF p_fund_rec.adjustment_accnt_segments IS NULL THEN
4642 x_complete_rec.adjustment_accnt_segments := l_fund_rec.adjustment_accnt_segments;
4643 END IF;
4644
4645 IF p_fund_rec.short_name = fnd_api.g_miss_char THEN
4646 x_complete_rec.short_name := NULL;
4647 END IF;
4648 IF p_fund_rec.short_name IS NULL THEN
4649 x_complete_rec.short_name := l_fund_rec.short_name;
4650 END IF;
4651
4652 IF p_fund_rec.description = fnd_api.g_miss_char THEN
4653 x_complete_rec.description := NULL;
4654 END IF;
4655 IF p_fund_rec.description IS NULL THEN
4656 x_complete_rec.description := l_fund_rec.description;
4657 END IF;
4658 --08/28/2001 mpande bug#1950117
4659 /*
4660 IF p_fund_rec.language = fnd_api.g_miss_char THEN
4661 x_complete_rec.language := NULL;
4662 END IF;
4663 IF p_fund_rec.language IS NULL THEN
4664 x_complete_rec.language := l_fund_rec.language;
4665 END IF;
4666
4667 IF p_fund_rec.source_lang = fnd_api.g_miss_char THEN
4668 x_complete_rec.source_lang := NULL;
4669 END IF;
4670 IF p_fund_rec.source_lang IS NULL THEN
4671 x_complete_rec.source_lang := l_fund_rec.source_lang;
4672 END IF;
4673 */
4674 IF p_fund_rec.fund_calendar = fnd_api.g_miss_char THEN
4675 x_complete_rec.fund_calendar := NULL;
4676 END IF;
4677 IF p_fund_rec.fund_calendar IS NULL THEN
4678 x_complete_rec.fund_calendar := l_fund_rec.fund_calendar;
4679 END IF;
4680
4681 IF p_fund_rec.start_period_name = fnd_api.g_miss_char THEN
4682 x_complete_rec.start_period_name := NULL;
4683 END IF;
4684 IF p_fund_rec.start_period_name IS NULL THEN
4685 x_complete_rec.start_period_name := l_fund_rec.start_period_name;
4686 END IF;
4687
4688 IF p_fund_rec.end_period_name = fnd_api.g_miss_char THEN
4689 x_complete_rec.end_period_name := NULL;
4690 END IF;
4691 IF p_fund_rec.end_period_name IS NULL THEN
4692 x_complete_rec.end_period_name := l_fund_rec.end_period_name;
4693 END IF;
4694
4695 IF p_fund_rec.accrual_quantity = fnd_api.g_miss_num THEN
4696 x_complete_rec.accrual_quantity := NULL;
4697 END IF;
4698 IF p_fund_rec.accrual_quantity IS NULL THEN
4699 x_complete_rec.accrual_quantity := l_fund_rec.accrual_quantity;
4700 END IF;
4701
4702 IF p_fund_rec.accrue_to_level_id = fnd_api.g_miss_num THEN
4703 x_complete_rec.accrue_to_level_id := NULL;
4704 END IF;
4705 IF p_fund_rec.accrue_to_level_id IS NULL THEN
4706 x_complete_rec.accrue_to_level_id := l_fund_rec.accrue_to_level_id;
4707 END IF;
4708
4709 IF p_fund_rec.accrual_discount_level = fnd_api.g_miss_char THEN
4710 x_complete_rec.accrual_discount_level := NULL;
4711 END IF;
4712 IF p_fund_rec.accrual_discount_level IS NULL THEN
4713 x_complete_rec.accrual_discount_level := l_fund_rec.accrual_discount_level;
4714 END IF;
4715 IF p_fund_rec.custom_setup_id = fnd_api.g_miss_num THEN
4716 x_complete_rec.custom_setup_id := NULL;
4717 END IF;
4718 IF p_fund_rec.custom_setup_id IS NULL THEN
4719 x_complete_rec.custom_setup_id := l_fund_rec.custom_setup_id;
4720 END IF;
4721 IF p_fund_rec.threshold_id = fnd_api.g_miss_num THEN
4722 x_complete_rec.threshold_id := NULL;
4723 END IF;
4724 IF p_fund_rec.threshold_id IS NULL THEN
4725 x_complete_rec.threshold_id := l_fund_rec.threshold_id;
4726 END IF;
4727 IF p_fund_rec.business_unit_id = fnd_api.g_miss_num THEN
4728 x_complete_rec.business_unit_id := NULL;
4729 END IF;
4730 IF p_fund_rec.business_unit_id IS NULL THEN
4731 x_complete_rec.business_unit_id := l_fund_rec.business_unit_id;
4732 END IF;
4733 IF p_fund_rec.task_id = fnd_api.g_miss_num THEN
4734 x_complete_rec.task_id := NULL;
4735 END IF;
4736 IF p_fund_rec.task_id IS NULL THEN
4737 x_complete_rec.task_id := l_fund_rec.task_id;
4738 END IF;
4739 IF p_fund_rec.country_id = fnd_api.g_miss_num THEN
4740 x_complete_rec.country_id := NULL;
4741 END IF;
4742 IF p_fund_rec.country_id IS NULL THEN
4743 x_complete_rec.country_id := l_fund_rec.country_id;
4744 END IF;
4745 -- added by feliu 02/08/2002 for rollup amount columns
4746 IF p_fund_rec.rollup_original_budget = fnd_api.g_miss_num THEN
4747 x_complete_rec.rollup_original_budget := NULL;
4748 END IF;
4749 IF p_fund_rec.rollup_original_budget IS NULL THEN
4750 x_complete_rec.rollup_original_budget := l_fund_rec.rollup_original_budget;
4751 END IF;
4752 IF p_fund_rec.rollup_holdback_amt = fnd_api.g_miss_num THEN
4753 x_complete_rec.rollup_holdback_amt := NULL;
4754 END IF;
4755 IF p_fund_rec.rollup_holdback_amt IS NULL THEN
4756 x_complete_rec.rollup_holdback_amt := l_fund_rec.rollup_holdback_amt;
4757 END IF;
4758 IF p_fund_rec.rollup_transfered_in_amt = fnd_api.g_miss_num THEN
4759 x_complete_rec.rollup_transfered_in_amt := NULL;
4760 END IF;
4761 IF p_fund_rec.rollup_transfered_in_amt IS NULL THEN
4762 x_complete_rec.rollup_transfered_in_amt := l_fund_rec.rollup_transfered_in_amt;
4763 END IF;
4764 IF p_fund_rec.rollup_transfered_out_amt = fnd_api.g_miss_num THEN
4765 x_complete_rec.rollup_transfered_out_amt := NULL;
4766 END IF;
4767 IF p_fund_rec.rollup_transfered_out_amt IS NULL THEN
4768 x_complete_rec.rollup_transfered_out_amt := l_fund_rec.rollup_transfered_out_amt;
4769 END IF;
4770 IF p_fund_rec.rollup_planned_amt = fnd_api.g_miss_num THEN
4771 x_complete_rec.rollup_planned_amt := NULL;
4772 END IF;
4773 IF p_fund_rec.rollup_planned_amt IS NULL THEN
4774 x_complete_rec.rollup_planned_amt := l_fund_rec.rollup_planned_amt;
4775 END IF;
4776 IF p_fund_rec.rollup_committed_amt = fnd_api.g_miss_num THEN
4777 x_complete_rec.rollup_committed_amt := NULL;
4778 END IF;
4779 IF p_fund_rec.rollup_committed_amt IS NULL THEN
4780 x_complete_rec.rollup_committed_amt := l_fund_rec.rollup_committed_amt;
4781 END IF;
4782 -- yzhao: 11.5.10
4783 IF p_fund_rec.rollup_utilized_amt = fnd_api.g_miss_num THEN
4784 x_complete_rec.rollup_utilized_amt := NULL;
4785 END IF;
4786 IF p_fund_rec.rollup_utilized_amt IS NULL THEN
4787 x_complete_rec.rollup_utilized_amt := l_fund_rec.rollup_utilized_amt;
4788 END IF;
4789 IF p_fund_rec.rollup_earned_amt = fnd_api.g_miss_num THEN
4790 x_complete_rec.rollup_earned_amt := NULL;
4791 END IF;
4792 IF p_fund_rec.rollup_earned_amt IS NULL THEN
4793 x_complete_rec.rollup_earned_amt := l_fund_rec.rollup_earned_amt;
4794 END IF;
4795 IF p_fund_rec.rollup_paid_amt = fnd_api.g_miss_num THEN
4796 x_complete_rec.rollup_paid_amt := NULL;
4797 END IF;
4798 IF p_fund_rec.rollup_paid_amt IS NULL THEN
4799 x_complete_rec.rollup_paid_amt := l_fund_rec.rollup_paid_amt;
4800 END IF;
4801 IF p_fund_rec.rollup_recal_committed = fnd_api.g_miss_num THEN
4802 x_complete_rec.rollup_recal_committed := NULL;
4803 END IF;
4804 IF p_fund_rec.rollup_recal_committed IS NULL THEN
4805 x_complete_rec.rollup_recal_committed := l_fund_rec.rollup_recal_committed ;
4806 END IF;
4807 IF p_fund_rec.retroactive_flag = fnd_api.g_miss_char THEN
4808 x_complete_rec.retroactive_flag := NULL;
4809 END IF;
4810 IF p_fund_rec.retroactive_flag IS NULL THEN
4811 x_complete_rec.retroactive_flag := l_fund_rec.retroactive_flag ;
4812 END IF;
4813 IF p_fund_rec.qualifier_id = fnd_api.g_miss_num THEN
4814 x_complete_rec.qualifier_id := NULL;
4815 END IF;
4816 IF p_fund_rec.qualifier_id IS NULL THEN
4817 x_complete_rec.qualifier_id := l_fund_rec.qualifier_id;
4818 END IF;
4819
4820 -- niprakas added
4821 IF p_fund_rec.prev_fund_id = fnd_api.g_miss_num THEN
4822 x_complete_rec.prev_fund_id := NULL;
4823 END IF;
4824 IF p_fund_rec.prev_fund_id IS NULL THEN
4825 x_complete_rec.prev_fund_id := l_fund_rec.prev_fund_id;
4826 END IF;
4827
4828 -- niprakas added
4829
4830 IF p_fund_rec.transfered_flag = fnd_api.g_miss_char THEN
4831 x_complete_rec.transfered_flag := NULL;
4832 END IF;
4833 IF p_fund_rec.transfered_flag IS NULL THEN
4834 x_complete_rec.transfered_flag := l_fund_rec.transfered_flag;
4835 END IF;
4836
4837 -- niprakas added
4838 IF p_fund_rec.utilized_amt = fnd_api.g_miss_num THEN
4839 x_complete_rec.utilized_amt := NULL;
4840 END IF;
4841 IF p_fund_rec.utilized_amt = fnd_api.g_miss_num THEN
4842 x_complete_rec.utilized_amt := l_fund_rec.utilized_amt;
4843 END IF;
4844
4845 -- niprakas added
4846 IF p_fund_rec.rollup_utilized_amt = fnd_api.g_miss_num THEN
4847 x_complete_rec.rollup_utilized_amt := NULL;
4848 END IF;
4849 IF p_fund_rec.rollup_utilized_amt = fnd_api.g_miss_num THEN
4850 x_complete_rec.rollup_utilized_amt := l_fund_rec.rollup_utilized_amt;
4851 END IF;
4852
4853 --kdass added
4854 IF p_fund_rec.product_spread_time_id = fnd_api.g_miss_num THEN
4855 x_complete_rec.product_spread_time_id := NULL;
4856 END IF;
4857 IF p_fund_rec.product_spread_time_id IS NULL THEN
4858 x_complete_rec.product_spread_time_id := l_fund_rec.product_spread_time_id;
4859 END IF;
4860
4861 --kdass - R12 MOAC changes
4862 IF p_fund_rec.org_id = fnd_api.g_miss_num THEN
4863 x_complete_rec.org_id := NULL;
4864 END IF;
4865 IF p_fund_rec.org_id IS NULL THEN
4866 x_complete_rec.org_id := l_fund_rec.org_id;
4867 END IF;
4868
4869 IF p_fund_rec.ledger_id = fnd_api.g_miss_num THEN
4870 x_complete_rec.ledger_id := NULL;
4871 END IF;
4872 IF p_fund_rec.ledger_id IS NULL THEN
4873 x_complete_rec.ledger_id := l_fund_rec.ledger_id;
4874 END IF;
4875
4876 END complete_fund_rec;
4877
4878
4879 -- ADDED FOR R2 Requirements to get default GL info--- by mpande //6th JULY-2000
4880 ---------------------------------------------------------------------
4881 -- PROCEDURE
4882 -- GET_DEFAULT_GL_INFO
4883 --
4884 -- PURPOSE : A fund should always have a category . When creating a category the user can
4885 -- give the GL info 1) ACCRUED_LIABILITY_ACCOUNT 2) DED_ADJUSTMENT_ACCOUNT
4886 -- When the user is creating a fund the funds API should pickup
4887 -- the default GL INFO from the associated category of the fund if the user has not passed anything.
4888 -- This API gets the defauls GL INFO.
4889 -- PARAMETERS
4890 -- p_category_id IN NUMBER,
4891 -- p_accrued_liability_account IN OUT NUMBER -- if null will deafult it otherwise will return whatever was passed
4892 -- p_ded_adjustment_account IN OUT NUMBER,-- if null will deafult it otherwise will return whatever was passed
4893 -- x_return_status OUT VARCHAR2
4894 -- Created by mpande 07/07/2000
4895 ---------------------------------------------------------------------
4896 PROCEDURE complete_default_gl_info(
4897 p_category_id IN NUMBER
4898 ,p_accrued_liability_account IN OUT NOCOPY NUMBER
4899 ,p_ded_adjustment_account IN OUT NOCOPY NUMBER
4900 ,x_return_status OUT NOCOPY VARCHAR2)
4901 IS
4902 CURSOR c_gl_info(
4903 p_cat_id IN NUMBER)
4904 IS
4905 SELECT accrued_liability_account
4906 ,ded_adjustment_account
4907 FROM ams_categories_vl
4908 WHERE category_id = p_cat_id;
4909
4910 l_accrued_liability_account NUMBER;
4911 l_ded_adjustment_account NUMBER;
4912 BEGIN
4913 x_return_status := fnd_api.g_ret_sts_success;
4914 OPEN c_gl_info(p_category_id);
4915 FETCH c_gl_info INTO l_accrued_liability_account, l_ded_adjustment_account;
4916 CLOSE c_gl_info;
4917
4918 --- if p_categroy_id is null then return null----
4919 IF p_category_id IS NULL THEN
4920 x_return_status := fnd_api.g_ret_sts_success;
4921 ELSIF p_category_id IS NOT NULL THEN
4922 IF p_accrued_liability_account IS NULL THEN --if present keep the value else default it
4923 p_accrued_liability_account := l_accrued_liability_account;
4924 END IF;
4925
4926 IF p_ded_adjustment_account IS NULL THEN --if present keep the value else default it
4927 p_ded_adjustment_account := l_ded_adjustment_account;
4928 END IF;
4929 END IF;
4930 EXCEPTION
4931 WHEN OTHERS THEN
4932 x_return_status := fnd_api.g_ret_sts_unexp_error;
4933 END complete_default_gl_info;
4934
4935
4936 -- 14-May-2001 feliu added for copy function.
4937 ---------------------------------------------------------------------
4938 -- PROCEDURE
4939 -- copy_fund
4940 --
4941 -- PURPOSE : -- Copy is broken into 4 sections:
4942 -- - copy all required fields of the object
4943 -- - copy all fields passed in thru the UI, but
4944 -- use the value of the base object if the field
4945 -- isn't passed through the UI
4946 -- - copy all fields passed in thru the UI, but
4947 -- leave the field as null if it isn't passed in
4948 -- - copy all attributes passed in from the UI
4949 -- PARAMETERS
4950 -- p_source_object_id: Original object id,
4951 -- p_attributes_table: AMS_CpyUtility_PVT.copy_attributes_table_type,
4952 -- p_copy_columns_table: AMS_CpyUtility_PVT.copy_columns_table_type,
4953 -- x_new_object_id: New object Id.
4954 -- x_custom_setup_id: custom_setup_id.
4955 ---------------------------------------------------------------------
4956 PROCEDURE copy_fund (
4957 p_api_version IN NUMBER,
4958 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4959 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4960 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4961 x_return_status OUT NOCOPY VARCHAR2,
4962 x_msg_count OUT NOCOPY NUMBER,
4963 x_msg_data OUT NOCOPY VARCHAR2,
4964 p_source_object_id IN NUMBER,
4965 p_attributes_table IN AMS_CpyUtility_PVT.copy_attributes_table_type,
4966 p_copy_columns_table IN AMS_CpyUtility_PVT.copy_columns_table_type,
4967 x_new_object_id OUT NOCOPY NUMBER,
4968 x_custom_setup_id OUT NOCOPY NUMBER
4969 )
4970 IS
4971 L_API_NAME CONSTANT VARCHAR2(30) := 'copy_fund';
4972 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
4973 L_OBJECT_TYPE_FUND CONSTANT VARCHAR2(30) := 'FUND';
4974 L_FUND_STATUS_TYPE CONSTANT VARCHAR2(30) := 'OZF_FUND_STATUS';
4975 L_DEFAULT_STATUS CONSTANT VARCHAR2(30) := 'DRAFT';
4976 l_return_status VARCHAR2(1);
4977
4978 l_new_fund_id NUMBER;
4979 l_fund_rec fund_rec_type;
4980
4981 -- for non-standard out params in copy_act_access
4982 l_errnum NUMBER;
4983 l_errcode VARCHAR2(30);
4984 l_errmsg VARCHAR2(4000);
4985
4986 CURSOR c_fund (p_fund_id IN NUMBER) IS
4987 SELECT *
4988 FROM ozf_funds_all_vl
4989 WHERE fund_id = p_fund_id
4990 ;
4991 CURSOR c_user_status_id (p_status_type IN VARCHAR2, p_status_code IN VARCHAR2) IS
4992 SELECT user_status_id
4993 FROM ams_user_statuses_b
4994 WHERE system_status_type = p_status_type
4995 AND system_status_code = p_status_code
4996 AND default_flag = 'Y'
4997 AND enabled_flag = 'Y'
4998 ;
4999 l_reference_rec c_fund%ROWTYPE;
5000 l_new_fund_rec c_fund%ROWTYPE;
5001 l_offer_custsetup NUMBER;
5002 l_plan_id NUMBER;
5003 l_attr_table AMS_CpyUtility_PVT.copy_attributes_table_type;
5004 l_copy_columns_table AMS_CpyUtility_PVT.copy_columns_table_type;
5005
5006 -- julou: get custom_setup_id for FAB offer. bug fix for copy offer enhancement
5007 CURSOR c_custom_setup_id(p_obj_id NUMBER) IS
5008 SELECT custom_setup_id
5009 FROM ozf_offers
5010 WHERE qp_list_header_id = p_obj_id;
5011 -- julou: end
5012
5013 BEGIN
5014 -- Standard Start of API savepoint
5015 SAVEPOINT copy_fund;
5016
5017 -- Standard call to check for call compatibility.
5018 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
5019 p_api_version,
5020 l_api_name,
5021 G_PKG_NAME)
5022 THEN
5023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5024 END IF;
5025
5026 -- Initialize message list if p_init_msg_list is set to TRUE.
5027 IF FND_API.to_Boolean( p_init_msg_list )THEN
5028 FND_MSG_PUB.initialize;
5029 END IF;
5030
5031 IF G_DEBUG THEN
5032 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
5033 END IF;
5034
5035 -- Initialize API return status to SUCCESS
5036 x_return_status := FND_API.G_RET_STS_SUCCESS;
5037
5038 --
5039 -- Start of API body.
5040 --
5041 -- Initialize the new fund record
5042 -- use ams_cpyutility_pvt.get_column_value to fetch a value
5043 -- to replace the reference column value with a new value
5044 -- passed in from the UI through p_copy_columns_table.
5045 OPEN c_fund (p_source_object_id);
5046 FETCH c_fund INTO l_reference_rec;
5047 CLOSE c_fund;
5048
5049 -- copy all required fields
5050 l_fund_rec.fund_type := l_reference_rec.fund_type;
5051 l_fund_rec.fund_number := null;
5052 l_fund_rec.status_code := L_DEFAULT_STATUS;
5053 l_fund_rec.category_id := l_reference_rec.category_id;
5054 l_fund_rec.currency_code_tc := l_reference_rec.currency_code_tc;
5055
5056 -- 08/13/2004 kdass when the budget has a parent budget, then the original budget amount is 0, so copy budget gives error.
5057 -- l_fund_rec.original_budget := l_reference_rec.original_budget;
5058 l_fund_rec.original_budget := NVL(l_reference_rec.original_budget, 0)
5059 + NVL(l_reference_rec.transfered_in_amt, 0)
5060 - NVL(l_reference_rec.transfered_out_amt, 0);
5061
5062 l_fund_rec.custom_setup_id := l_reference_rec.custom_setup_id;
5063 l_fund_rec.accrual_rate := l_reference_rec.accrual_rate;
5064 l_fund_rec.accrual_basis := l_reference_rec.accrual_basis;
5065 l_fund_rec.country_id := l_reference_rec.country_id;
5066 l_fund_rec.holdback_amt := l_reference_rec.holdback_amt;
5067
5068 -- kdass R12 Bug 4621165 - copy org_id and ledger_id to the new budget
5069 l_fund_rec.org_id := l_reference_rec.org_id;
5070 l_fund_rec.ledger_id := l_reference_rec.ledger_id;
5071
5072 -- 10/22/2001 mpande added to copy all other accrual parameters and not copy org budget for fully accrued
5073 IF l_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
5074 l_fund_rec.original_budget := 0;
5075 l_fund_rec.plan_type := 'OFFR';
5076 END IF;
5077
5078 l_fund_rec.apply_accrual_on := l_reference_rec.apply_accrual_on;
5079 l_fund_rec.accrual_quantity := l_reference_rec.accrual_quantity;
5080 l_fund_rec.retroactive_flag := l_reference_rec.retroactive_flag;
5081 l_fund_rec.qualifier_id := l_reference_rec.qualifier_id;
5082 l_fund_rec.accrue_to_level_id := l_reference_rec.accrue_to_level_id;
5083 l_fund_rec.business_unit_id := l_reference_rec.business_unit_id;
5084 l_fund_rec.accrual_method := l_reference_rec.accrual_method;
5085 l_fund_rec.liability_flag := l_reference_rec.liability_flag;
5086 l_fund_rec.accrual_operand := l_reference_rec.accrual_operand;
5087 l_fund_rec.accrual_discount_level := l_reference_rec.accrual_discount_level;
5088 l_fund_rec.liability_flag := l_reference_rec.liability_flag;
5089 l_fund_rec.accrual_cap := l_reference_rec.accrual_cap;
5090 l_fund_rec.accrual_method := l_reference_rec.accrual_method;
5091
5092 -- 02/05/2003 yzhao fix bug 2788123 MKTF1R9:1159.0204:FUNC:COPY BUDGET THROWING ERROR OZF_ACCRUAL_NO_PHASE
5093 l_fund_rec.accrual_phase := l_reference_rec.accrual_phase;
5094 l_fund_rec.accrual_uom := l_reference_rec.accrual_uom;
5095 -- 02/05/2003 yzhao fix bug 2788123 ends
5096
5097 --asylvia fixed bug 5169099 - Activity copied to new budget.
5098 l_fund_rec.task_id := l_reference_rec.task_id;
5099 --asylvia start bug 5107243
5100 l_fund_rec.parent_fund_id := l_reference_rec.parent_fund_id ;
5101 l_fund_rec.accrued_liable_account := l_reference_rec.accrued_liable_account;
5102 l_fund_rec.ded_adjustment_account := l_reference_rec.ded_adjustment_account;
5103 l_fund_rec.description := l_reference_rec.description;
5104 l_fund_rec.threshold_id := l_reference_rec.threshold_id;
5105 --l_fund_rec.start_period_id:= l_reference_rec.start_period_id;
5106 --l_fund_rec.end_period_id:= l_reference_rec.end_period_id;
5107 --l_fund_rec.end_date_active := NVL (l_fund_rec.end_date_active, l_reference_rec.end_date_active);
5108 l_fund_rec.attribute_category := l_reference_rec.attribute_category;
5109 l_fund_rec.attribute1 := l_reference_rec.attribute1;
5110 l_fund_rec.attribute2 := l_reference_rec.attribute2;
5111 l_fund_rec.attribute3 := l_reference_rec.attribute3;
5112 l_fund_rec.attribute4 := l_reference_rec.attribute4;
5113 l_fund_rec.attribute5 := l_reference_rec.attribute5;
5114 l_fund_rec.attribute6 := l_reference_rec.attribute6;
5115 l_fund_rec.attribute7 := l_reference_rec.attribute7;
5116 l_fund_rec.attribute8 := l_reference_rec.attribute8;
5117 l_fund_rec.attribute9 := l_reference_rec.attribute9;
5118 l_fund_rec.attribute10 := l_reference_rec.attribute10;
5119 l_fund_rec.attribute11 := l_reference_rec.attribute11;
5120 l_fund_rec.attribute12 := l_reference_rec.attribute12;
5121 l_fund_rec.attribute13 := l_reference_rec.attribute13;
5122 l_fund_rec.attribute14 := l_reference_rec.attribute14;
5123 l_fund_rec.attribute15 := l_reference_rec.attribute15;
5124 --asylvia end bug 5107243
5125
5126 OPEN c_user_status_id (L_FUND_STATUS_TYPE, l_fund_rec.status_code);
5127 FETCH c_user_status_id INTO l_fund_rec.user_status_id;
5128 CLOSE c_user_status_id;
5129 l_fund_rec.currency_code_tc := l_reference_rec.currency_code_tc;
5130
5131 -- if field is not passed in from copy_columns_table
5132 -- copy from the base object
5133 AMS_CpyUtility_PVT.get_column_value ('ownerId', p_copy_columns_table, l_fund_rec.owner);
5134 l_fund_rec.owner := NVL (l_fund_rec.owner, l_reference_rec.owner);
5135
5136 AMS_CpyUtility_PVT.get_column_value ('startDate', p_copy_columns_table, l_fund_rec.start_date_active);
5137 l_fund_rec.start_date_active := NVL (l_fund_rec.start_date_active, l_reference_rec.start_date_active);
5138
5139 -- if field is not passed in from copy_columns_table
5140 -- don't copy
5141 AMS_CpyUtility_PVT.get_column_value ('newObjName', p_copy_columns_table, l_fund_rec.short_name);
5142
5143 IF l_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
5144 -- to fix bug 3240787
5145 IF l_fund_rec.parent_fund_id IS NULL THEN
5146 l_fund_rec.fund_number :=
5147 ams_sourcecode_pvt.get_source_code(
5148 p_category_id => l_fund_rec.category_id
5149 ,p_arc_object_for => 'FUND');
5150 ELSE
5151 get_child_source_code(
5152 l_fund_rec.parent_fund_id
5153 ,l_fund_rec.fund_number
5154 ,x_return_status);
5155 IF x_return_status = fnd_api.g_ret_sts_error THEN
5156 RAISE fnd_api.g_exc_error;
5157 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5158 RAISE fnd_api.g_exc_unexpected_error;
5159 END IF;
5160 END IF;
5161 -- to fix bug 3240787
5162
5163 l_attr_table(1) := 'DETL';
5164 l_attr_table(2) := 'ELIG';
5165 l_copy_columns_table(1).column_name := 'offerCode';
5166 l_copy_columns_table(1).column_value := l_fund_rec.fund_number;
5167 l_copy_columns_table(2).column_name := 'startDateActive';
5168 l_copy_columns_table(2).column_value := l_fund_rec.start_date_active;
5169 l_copy_columns_table(3).column_name := 'endDateActive';
5170 l_copy_columns_table(3).column_value := '';
5171 l_copy_columns_table(4).column_name := 'ownerId';
5172 l_copy_columns_table(4).column_value := l_fund_rec.owner;
5173 l_copy_columns_table(5).column_name := 'description';
5174 l_copy_columns_table(5).column_value := '';
5175 l_copy_columns_table(6).column_name :='newObjName';
5176 l_copy_columns_table(6).column_value := l_fund_rec.short_name;
5177
5178 OPEN c_custom_setup_id(l_reference_rec.plan_id);
5179 FETCH c_custom_setup_id INTO l_offer_custsetup;
5180 CLOSE c_custom_setup_id;
5181
5182 OZF_COPY_OFFER_PVT.copy_offer_detail(
5183 p_api_version=> 1.0,
5184 p_init_msg_list=> FND_API.G_FALSE,
5185 p_commit=> FND_API.G_FALSE,
5186 p_validation_level=> p_validation_level,
5187 x_return_status=> l_return_status,
5188 x_msg_count=> x_msg_count,
5189 x_msg_data=> x_msg_data,
5190 p_source_object_id => l_reference_rec.plan_id,
5191 p_attributes_table =>l_attr_table,
5192 p_copy_columns_table =>l_copy_columns_table,
5193 x_new_object_id =>l_plan_id,
5194 p_custom_setup_id =>l_offer_custsetup);
5195
5196 l_fund_rec.plan_id := l_plan_id;
5197
5198 IF l_return_status = fnd_api.g_ret_sts_error THEN
5199 RAISE fnd_api.g_exc_error;
5200 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5201 RAISE fnd_api.g_exc_unexpected_error;
5202 END IF;
5203
5204 END IF;
5205
5206 OZF_Funds_PVT.Create_Fund (
5207 p_api_version => 1.0,
5208 p_init_msg_list => FND_API.G_FALSE,
5209 p_commit => FND_API.G_FALSE,
5210 p_validation_level => p_validation_level,
5211 x_return_status => l_return_status,
5212 x_msg_count => x_msg_count,
5213 x_msg_data => x_msg_data,
5214 p_fund_rec => l_fund_rec,
5215 x_fund_id => l_new_fund_id
5216 );
5217
5218 IF l_return_status = fnd_api.g_ret_sts_error THEN
5219 RAISE fnd_api.g_exc_error;
5220 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5221 RAISE fnd_api.g_exc_unexpected_error;
5222 END IF;
5223
5224 -- copy market_segments
5225
5226
5227 IF AMS_CpyUtility_PVT.is_copy_attribute ('ELIG', p_attributes_table) = FND_API.G_TRUE THEN
5228
5229 AMS_CopyElements_PVT.copy_act_market_segments (
5230 p_src_act_type => L_OBJECT_TYPE_FUND,
5231 p_new_act_type => L_OBJECT_TYPE_FUND,
5232 p_src_act_id => p_source_object_id,
5233 p_new_act_id => l_new_fund_id,
5234 p_errnum => l_errnum,
5235 p_errcode => l_errcode,
5236 p_errmsg => l_errmsg
5237 );
5238 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5239 RAISE FND_API.G_EXC_ERROR;
5240 END IF;
5241 END IF;
5242
5243 -- copy product
5244 IF AMS_CpyUtility_PVT.is_copy_attribute (AMS_CopyElements_PVT.G_ATTRIBUTE_PROD, p_attributes_table) = FND_API.G_TRUE THEN
5245 AMS_CopyElements_PVT.copy_act_prod(
5246 p_src_act_type => L_OBJECT_TYPE_FUND,
5247 p_new_act_type => L_OBJECT_TYPE_FUND,
5248 p_src_act_id => p_source_object_id,
5249 p_new_act_id => l_new_fund_id,
5250 p_errnum => l_errnum,
5251 p_errcode => l_errcode,
5252 p_errmsg => l_errmsg
5253 );
5254 END IF;
5255
5256 -- currently, only needed to fetch custom_setup_id
5257 -- but can be used to return other values later.
5258 OPEN c_fund (l_new_fund_id);
5259 FETCH c_fund INTO l_new_fund_rec;
5260 CLOSE c_fund;
5261
5262 x_new_object_id := l_new_fund_id;
5263 x_custom_setup_id := l_new_fund_rec.custom_setup_id;
5264 --
5265 -- End of API body.
5266 --
5267
5268 -- Standard check for p_commit
5269 IF FND_API.to_Boolean( p_commit ) THEN
5270 COMMIT WORK;
5271 END IF;
5272
5273
5274 -- Debug Message
5275 IF G_DEBUG THEN
5276 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
5277 END IF;
5278
5279 -- Standard call to get message count and if count is 1, get message info.
5280 FND_MSG_PUB.Count_And_Get (
5281 p_count => x_msg_count,
5282 p_data => x_msg_data
5283 );
5284 EXCEPTION
5285 WHEN FND_API.G_EXC_ERROR THEN
5286 ROLLBACK TO copy_fund;
5287 x_return_status := FND_API.G_RET_STS_ERROR;
5288 -- Standard call to get message count and if count=1, get the message
5289 FND_MSG_PUB.Count_And_Get (
5290 p_encoded => FND_API.G_FALSE,
5291 p_count => x_msg_count,
5292 p_data => x_msg_data
5293 );
5294 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5295 ROLLBACK TO copy_fund;
5296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5297 -- Standard call to get message count and if count=1, get the message
5298 FND_MSG_PUB.Count_And_Get (
5299 p_encoded => FND_API.G_FALSE,
5300 p_count => x_msg_count,
5301 p_data => x_msg_data
5302 );
5303 WHEN OTHERS THEN
5304 ROLLBACK TO copy_fund;
5305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5306 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5307 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5308 END IF;
5309 -- Standard call to get message count and if count=1, get the message
5310 FND_MSG_PUB.Count_And_Get (
5311 p_encoded => FND_API.G_FALSE,
5312 p_count => x_msg_count,
5313 p_data => x_msg_data
5314 );
5315 END copy_fund;
5316
5317
5318 ---------------------------------------------------------------------
5319 -- PROCEDURE
5320 --- update_rollup_amount
5321 --
5322 -- PURPOSE
5323 -- Update rollup columns. added by feliu
5324 --
5325 -- PARAMETERS
5326 -- p_fund_rec fund record.
5327 ---------------------------------------------------------------------
5328
5329 PROCEDURE update_rollup_amount(
5330 p_api_version IN NUMBER
5331 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
5332 ,p_commit IN VARCHAR2 := fnd_api.g_false
5333 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
5334 ,x_return_status OUT NOCOPY VARCHAR2
5335 ,x_msg_count OUT NOCOPY NUMBER
5336 ,x_msg_data OUT NOCOPY VARCHAR2
5337 ,p_fund_rec IN fund_rec_type
5338 ) IS
5339 l_api_version CONSTANT NUMBER := 1.0;
5340 l_api_name CONSTANT VARCHAR2(30)
5341 := 'update_rollup_amount';
5342 l_full_name CONSTANT VARCHAR2(60)
5343 := g_pkg_name || '.' || l_api_name;
5344
5345 --Get all of parent fund through bottom up tree walking.
5346 CURSOR c_parent
5347 IS
5348 SELECT fund_id
5349 ,object_version_number
5350 ,rollup_original_budget
5351 ,rollup_transfered_in_amt
5352 ,rollup_transfered_out_amt
5353 ,rollup_holdback_amt
5354 ,rollup_planned_amt
5355 ,rollup_committed_amt
5356 ,rollup_utilized_amt -- yzhao: 11.5.10
5357 ,rollup_earned_amt
5358 ,rollup_paid_amt
5359 ,rollup_recal_committed
5360 FROM ozf_funds_all_b
5361 connect by prior parent_fund_id =fund_id
5362 start with fund_id = p_fund_rec.fund_id;
5363
5364 BEGIN
5365
5366 -- Standard call to check for call compatibility.
5367 IF NOT FND_API.Compatible_API_Call ( l_api_version,
5368 p_api_version,
5369 l_api_name,
5370 G_PKG_NAME)
5371 THEN
5372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5373 END IF;
5374
5375 -- Initialize message list if p_init_msg_list is set to TRUE.
5376 IF FND_API.to_Boolean( p_init_msg_list )THEN
5377 FND_MSG_PUB.initialize;
5378 END IF;
5379
5380 IF G_DEBUG THEN
5381 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
5382 END IF;
5383
5384 -- Initialize API return status to SUCCESS
5385 x_return_status := FND_API.G_RET_STS_SUCCESS;
5386
5387 FOR fund IN c_parent
5388 LOOP
5389 IF fund.fund_id <> p_fund_rec.fund_id THEN
5390
5391 UPDATE ozf_funds_all_b
5392 SET object_version_number = fund.object_version_number + 1
5393 ,rollup_original_budget = NVL(fund.rollup_original_budget,0) + NVL(p_fund_rec.rollup_original_budget,0)
5394 ,rollup_transfered_in_amt = NVL(fund.rollup_transfered_in_amt,0) + NVL(p_fund_rec.rollup_transfered_in_amt,0)
5395 ,rollup_transfered_out_amt = NVL(fund.rollup_transfered_out_amt,0) + NVL(p_fund_rec.rollup_transfered_out_amt,0)
5396 ,rollup_holdback_amt = NVL(fund.rollup_holdback_amt,0) + NVL(p_fund_rec.rollup_holdback_amt,0)
5397 ,rollup_planned_amt = NVL(fund.rollup_planned_amt,0)+ NVL(p_fund_rec.rollup_planned_amt,0)
5398 ,rollup_committed_amt = NVL(fund.rollup_committed_amt,0) + NVL(p_fund_rec.rollup_committed_amt,0)
5399 ,rollup_utilized_amt = NVL(fund.rollup_utilized_amt,0) + NVL(p_fund_rec.rollup_utilized_amt,0) -- yzhao: 11.5.10
5400 ,rollup_earned_amt = NVL(fund.rollup_earned_amt,0) + NVL(p_fund_rec.rollup_earned_amt,0)
5401 ,rollup_paid_amt = NVL(fund.rollup_paid_amt,0) + NVL(p_fund_rec.rollup_paid_amt,0)
5402 ,rollup_recal_committed = NVL(fund.rollup_recal_committed ,0)+ NVL(p_fund_rec.rollup_recal_committed,0)
5403 WHERE fund_id = fund.fund_id
5404 AND object_version_number = fund.object_version_number;
5405
5406 IF (SQL%NOTFOUND) THEN
5407 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
5408 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
5409 fnd_msg_pub.add;
5410 END IF;
5411
5412 RAISE fnd_api.g_exc_error;
5413 END IF;
5414 END IF;
5415 END LOOP;
5416 -------------------- finish --------------------------
5417 fnd_msg_pub.count_and_get(
5418 p_encoded => fnd_api.g_false
5419 ,p_count => x_msg_count
5420 ,p_data => x_msg_data);
5421 IF G_DEBUG THEN
5422 ozf_utility_pvt.debug_message( l_api_name || ': end');
5423 END IF;
5424 EXCEPTION
5425 WHEN fnd_api.g_exc_error THEN
5426 x_return_status := fnd_api.g_ret_sts_error;
5427 fnd_msg_pub.count_and_get(
5428 p_encoded => fnd_api.g_false
5429 ,p_count => x_msg_count
5430 ,p_data => x_msg_data);
5431 WHEN fnd_api.g_exc_unexpected_error THEN
5432 x_return_status := fnd_api.g_ret_sts_unexp_error;
5433 fnd_msg_pub.count_and_get(
5434 p_encoded => fnd_api.g_false
5435 ,p_count => x_msg_count
5436 ,p_data => x_msg_data);
5437 WHEN OTHERS THEN
5438 x_return_status := fnd_api.g_ret_sts_unexp_error;
5439
5440 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
5441 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
5442 END IF;
5443
5444 fnd_msg_pub.count_and_get(
5445 p_encoded => fnd_api.g_false
5446 ,p_count => x_msg_count
5447 ,p_data => x_msg_data);
5448
5449 END update_rollup_amount;
5450
5451 ---------------------------------------------------------------------
5452 -- PROCEDURE
5453 --- update_funds_access
5454 --
5455 -- PURPOSE
5456 -- Update parent funds access. added by feliu
5457 --
5458 -- PARAMETERS
5459 -- p_fund_rec: the fund record.
5460 -- p_mode: the mode for create, and delete.
5461 ---------------------------------------------------------------------
5462
5463 PROCEDURE update_funds_access(
5464 p_api_version IN NUMBER
5465 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
5466 ,p_commit IN VARCHAR2 := fnd_api.g_false
5467 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
5468 ,x_return_status OUT NOCOPY VARCHAR2
5469 ,x_msg_count OUT NOCOPY NUMBER
5470 ,x_msg_data OUT NOCOPY VARCHAR2
5471 ,p_fund_rec IN fund_rec_type
5472 ,p_mode IN VARCHAR2 := JTF_PLSQL_API.G_CREATE
5473 ) IS
5474
5475 l_api_version CONSTANT NUMBER := 1.0;
5476 l_api_name CONSTANT VARCHAR2(30)
5477 := 'update_funds_access';
5478 l_full_name CONSTANT VARCHAR2(60)
5479 := g_pkg_name || '.' || l_api_name;
5480
5481 l_access_rec ams_access_pvt.access_rec_type;
5482 --l_fund_owner NUMBER;
5483 l_return_status VARCHAR2(1);
5484 l_access_id NUMBER;
5485 l_acc_obj_ver_num NUMBER;
5486 --Get all of parent fund through bottom up tree walking.
5487 CURSOR c_parent
5488 IS
5489 SELECT fund_id,owner
5490 FROM ozf_funds_all_b
5491 connect by prior parent_fund_id =fund_id
5492 start with fund_id = p_fund_rec.fund_id;
5493
5494 CURSOR c_fund_access(
5495 p_fund_id IN NUMBER
5496 ,p_owner_id IN NUMBER)
5497 IS
5498 SELECT activity_access_id
5499 ,object_version_number
5500 FROM ams_act_access
5501 WHERE act_access_to_object_id = p_fund_id
5502 AND arc_act_access_to_object = 'FUND'
5503 AND arc_user_or_role_type = 'USER'
5504 AND user_or_role_id = p_owner_id;
5505 --AND NVL(owner_flag,'N') = 'N' ;
5506
5507
5508 TYPE owner_table_type IS TABLE of NUMBER
5509 INDEX BY BINARY_INTEGER;
5510 l_owner_table owner_table_type;
5511 l_count NUMBER := 1 ;
5512 l_owner_exist BOOLEAN := false;
5513
5514 BEGIN
5515 -- Standard call to check for call compatibility.
5516 IF NOT FND_API.Compatible_API_Call ( l_api_version,
5517 p_api_version,
5518 l_api_name,
5519 G_PKG_NAME)
5520 THEN
5521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5522 END IF;
5523
5524 -- Initialize message list if p_init_msg_list is set to TRUE.
5525 IF FND_API.to_Boolean( p_init_msg_list )THEN
5526 FND_MSG_PUB.initialize;
5527 END IF;
5528
5529 IF G_DEBUG THEN
5530 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
5531 END IF;
5532
5533 FOR fund IN c_parent
5534 LOOP
5535 --Remove last level fund.
5536 IF fund.fund_id <> p_fund_rec.fund_id THEN
5537 IF p_mode = 'CREATE' THEN
5538 --check if access has been created for same owner at children level.
5539 FOR i IN NVL(l_owner_table.FIRST, 0)..NVL(l_owner_table.LAST, 0) LOOP
5540 EXIT WHEN l_owner_table.COUNT = 0;
5541 IF l_owner_table(i) = fund.owner THEN
5542 l_owner_exist := true;
5543 END IF;
5544 END LOOP;
5545
5546 --if the owner of the parent and child fund is different then only add access
5547 IF l_owner_exist = false AND fund.owner <> p_fund_rec.owner THEN
5548 --added owner to owner table to avoide creating another access next time.
5549 l_owner_table(l_count) := fund.owner;
5550 l_count := l_count + 1;
5551
5552 l_access_rec.act_access_to_object_id := p_fund_rec.fund_id;
5553 l_access_rec.arc_act_access_to_object := 'FUND';
5554 l_access_rec.user_or_role_id := fund.owner;
5555 l_access_rec.arc_user_or_role_type := 'USER';
5556 l_access_rec.admin_flag := 'Y';
5557 l_access_rec.owner_flag := 'Y';
5558 ams_access_pvt.create_access(
5559 p_api_version => l_api_version
5560 ,p_init_msg_list => fnd_api.g_false
5561 ,p_validation_level => p_validation_level
5562 ,x_return_status => l_return_status
5563 ,x_msg_count => x_msg_count
5564 ,x_msg_data => x_msg_data
5565 ,p_commit => fnd_api.g_false
5566 ,p_access_rec => l_access_rec
5567 ,x_access_id => l_access_id);
5568
5569 --l_return_status := fnd_api.g_ret_sts_error;
5570 IF l_return_status = fnd_api.g_ret_sts_error THEN
5571 RAISE fnd_api.g_exc_error;
5572 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5573 RAISE fnd_api.g_exc_unexpected_error;
5574 END IF;
5575 END IF;
5576 ELSE -- end create access mode.
5577
5578 OPEN c_fund_access(p_fund_rec.fund_id, fund.owner);
5579 FETCH c_fund_access INTO l_access_id, l_acc_obj_ver_num;
5580 CLOSE c_fund_access;
5581 l_access_rec.activity_access_id := l_access_id;
5582 l_access_rec.object_version_number := l_acc_obj_ver_num;
5583
5584 IF fund.owner <>p_fund_rec.owner AND l_access_rec.activity_access_id is NOT NULL THEN
5585 ams_access_pvt.delete_access(
5586 p_api_version => l_api_version
5587 ,p_init_msg_list => fnd_api.g_false
5588 ,p_validation_level => p_validation_level
5589 ,x_return_status => l_return_status
5590 ,x_msg_count => x_msg_count
5591 ,x_msg_data => x_msg_data
5592 ,p_commit => fnd_api.g_false
5593 ,p_access_id => l_access_id
5594 ,p_object_version => l_acc_obj_ver_num);
5595
5596 IF l_return_status = fnd_api.g_ret_sts_error THEN
5597 RAISE fnd_api.g_exc_error;
5598 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5599 RAISE fnd_api.g_exc_unexpected_error;
5600 END IF;
5601 END IF; -- end of l_access_rec.activity_access_id is NOT NULL
5602
5603 END IF; -- end delete access mode.
5604 END IF;
5605 END LOOP;
5606 -------------------- finish --------------------------
5607 fnd_msg_pub.count_and_get(
5608 p_encoded => fnd_api.g_false
5609 ,p_count => x_msg_count
5610 ,p_data => x_msg_data);
5611 IF G_DEBUG THEN
5612 ozf_utility_pvt.debug_message( l_api_name ||': end');
5613 END IF;
5614
5615 EXCEPTION
5616 WHEN fnd_api.g_exc_error THEN
5617 x_return_status := fnd_api.g_ret_sts_error;
5618 fnd_msg_pub.count_and_get(
5619 p_encoded => fnd_api.g_false
5620 ,p_count => x_msg_count
5621 ,p_data => x_msg_data);
5622 WHEN fnd_api.g_exc_unexpected_error THEN
5623 x_return_status := fnd_api.g_ret_sts_unexp_error;
5624 fnd_msg_pub.count_and_get(
5625 p_encoded => fnd_api.g_false
5626 ,p_count => x_msg_count
5627 ,p_data => x_msg_data);
5628 WHEN OTHERS THEN
5629 x_return_status := fnd_api.g_ret_sts_unexp_error;
5630
5631 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
5632 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
5633 END IF;
5634
5635 fnd_msg_pub.count_and_get(
5636 p_encoded => fnd_api.g_false
5637 ,p_count => x_msg_count
5638 ,p_data => x_msg_data);
5639
5640 END update_funds_access;
5641
5642 END Ozf_funds_pvt;
5643