[Home] [Help]
PACKAGE BODY: APPS.OZF_OBJFUNDSUM_PVT
Source
1 PACKAGE BODY OZF_OBJFUNDSUM_PVT AS
2 /* $Header: ozfvfsub.pls 120.7 2011/05/24 10:10:01 bkunjan ship $ */
3
4 ------------------------------------------------------------------------------
5 --
6 -- NAME
7 -- OZF_OBJFUNDSUM_PVT 12.0
8 --
9 -- HISTORY
10 -- 06/30/2005 YZHAO CREATION
11 -- 06/12/2009 kdass Bug 8532055 - ADD EXCHANGE RATE DATE PARAM TO OZF_FUND_UTILIZED_PUB.CREATE_FUND_ADJUSTMENT API
12 -- 9/1/2010 nepanda Bug 10077018 - BACKPORT:8675200:MC:RMA ORDERS ARE NOT UPDATED CORRECTLY IN THE CHECKBOOK
13
14 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_OBJFUNDSUM_PVT';
15
16 OZF_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
17 OZF_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
18 OZF_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
19
20 g_universal_currency CONSTANT VARCHAR2 (15) := fnd_profile.VALUE ('OZF_UNIV_CURR_CODE');
21
22
23
24 -- NAME
25 -- complete_amount_fields
26 --
27 -- PURPOSE
28 -- This Procedure fills in amount in fund/object/universal currency if not passed in
29 -- x_amount_1 converted amount in p_currency_1
30 -- x_amount_2 converted amount in p_currency_2
31 -- x_amount_3 converted amount in universal_currency
32 --
33 -- NOTES
34 --
35 -- HISTORY
36 -- 07/25/2005 yzhao Created.
37 PROCEDURE complete_amount_fields (
38 p_currency_1 IN VARCHAR2,
39 p_amount_1 IN NUMBER,
40 p_currency_2 IN VARCHAR2,
41 p_amount_2 IN NUMBER,
42 p_amount_3 IN NUMBER,
43 p_conv_date IN DATE DEFAULT NULL, --bug 8532055
44 x_amount_1 OUT NOCOPY NUMBER,
45 x_amount_2 OUT NOCOPY NUMBER,
46 x_amount_3 OUT NOCOPY NUMBER,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2
50 )
51 IS
52 l_return_status VARCHAR2(30);
53 BEGIN
54 x_amount_1 := p_amount_1;
55 x_amount_2 := p_amount_2;
56 x_amount_3 := p_amount_3;
57
58 IF NVL(p_amount_1, 0) <> 0 THEN
59 IF NVL(p_amount_2, 0) = 0 THEN
60 -- fill in amount 2 from amount 1
61 IF p_currency_1 = p_currency_2 THEN
62 x_amount_2 := p_amount_1;
63 ELSE
64 ozf_utility_pvt.convert_currency (
65 x_return_status=> l_return_status
66 ,p_from_currency=> p_currency_1
67 ,p_to_currency=> p_currency_2
68 ,p_conv_date=> p_conv_date --bug 8532055
69 ,p_from_amount=> p_amount_1
70 ,x_to_amount=> x_amount_2
71 );
72 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
73 RAISE fnd_api.g_exc_unexpected_error;
74 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
75 RAISE fnd_api.g_exc_error;
76 END IF;
77 END IF;
78 END IF;
79
80 IF NVL(p_amount_3, 0) = 0 THEN
81 -- fill in amount in universal currency from amount 1
82 IF g_universal_currency = p_currency_1 THEN
83 x_amount_3 := p_amount_1;
84 ELSE
85 ozf_utility_pvt.convert_currency (
86 x_return_status=> l_return_status
87 ,p_from_currency=> p_currency_1
88 ,p_to_currency=> g_universal_currency
89 ,p_conv_date=> p_conv_date --bug 8532055
90 ,p_from_amount=> p_amount_1
91 ,x_to_amount=> x_amount_3
92 );
93 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
94 RAISE fnd_api.g_exc_unexpected_error;
95 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
96 RAISE fnd_api.g_exc_error;
97 END IF;
98 END IF;
99 END IF;
100 ELSE
101 IF NVL(p_amount_2, 0) <> 0 THEN
102 -- fill in amount 1 from amount 2
103 IF p_currency_1 = p_currency_2 THEN
104 x_amount_1 := p_amount_2;
105 ELSE
106 ozf_utility_pvt.convert_currency (
107 x_return_status=> l_return_status
108 ,p_from_currency=> p_currency_2
109 ,p_to_currency=> p_currency_1
110 ,p_conv_date=> p_conv_date --bug 8532055
111 ,p_from_amount=> p_amount_2
112 ,x_to_amount=> x_amount_1
113 );
114 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
115 RAISE fnd_api.g_exc_unexpected_error;
116 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
117 RAISE fnd_api.g_exc_error;
118 END IF;
119 END IF;
120
121 IF NVL(p_amount_3, 0) = 0 THEN
122 -- fill in amount in universal currency from amount 2
123 IF g_universal_currency = p_currency_2 THEN
124 x_amount_3 := p_amount_2;
125 ELSE
126 ozf_utility_pvt.convert_currency (
127 x_return_status=> l_return_status
128 ,p_from_currency=> p_currency_2
129 ,p_to_currency=> g_universal_currency
130 ,p_conv_date=> p_conv_date --bug 8532055
131 ,p_from_amount=> p_amount_2
132 ,x_to_amount=> x_amount_3
133 );
134 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
135 RAISE fnd_api.g_exc_unexpected_error;
136 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
137 RAISE fnd_api.g_exc_error;
138 END IF;
139 END IF;
140 END IF;
141 END IF;
142 END IF;
143 END complete_amount_fields;
144
145
146 -- NAME
147 -- create_objfundsum
148 --
149 -- PURPOSE
150 -- This Procedure creates a record in object fund summary table.
151 --
152 -- NOTES
153 --
154 -- HISTORY
155 -- 06/30/2005 yzhao Created.
156 --
157 PROCEDURE Create_objfundsum (
158 p_api_version IN NUMBER,
159 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
160 p_validation_level IN NUMBER := Fnd_Api.G_Valid_Level_Full,
161 p_objfundsum_rec IN objfundsum_rec_type,
162 p_conv_date IN DATE DEFAULT NULL, --bug 8532055
163 x_return_status OUT NOCOPY VARCHAR2,
164 x_msg_count OUT NOCOPY NUMBER,
165 x_msg_data OUT NOCOPY VARCHAR2,
166 x_objfundsum_id OUT NOCOPY NUMBER
167 )
168 IS
169 L_API_VERSION CONSTANT NUMBER := 1.0;
170 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_OBJFUNDSUM';
171 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
172
173 l_return_status VARCHAR2(1);
174 l_objfundsum_rec objfundsum_rec_type := p_objfundsum_rec;
175 l_objfundsum_count NUMBER := NULL;
176 l_amount_1 NUMBER;
177 l_amount_2 NUMBER;
178 l_amount_3 NUMBER;
179
180 CURSOR c_objfundsum_seq IS
181 SELECT ozf_object_fund_summary_s.nextval
182 FROM dual;
183
184 CURSOR c_objfundsum_count(p_objfundsum_id IN NUMBER) IS
185 SELECT COUNT(objfundsum_id)
186 FROM ozf_object_fund_summary
187 WHERE objfundsum_id = p_objfundsum_id;
188
189 CURSOR c_get_fund_currency(p_fund_id IN NUMBER) IS
190 SELECT currency_code_tc
191 FROM ozf_funds_all_b
192 WHERE fund_id = p_fund_id;
193
194 CURSOR c_get_reference(p_offer_id IN NUMBER) IS
195 SELECT arc_act_offer_used_by,act_offer_used_by_id
196 FROM ozf_act_offers
197 WHERE qp_list_header_id = p_offer_id;
198
199 BEGIN
200
201 IF (OZF_DEBUG_HIGH_ON) THEN
202 ozf_utility_pvt.Debug_Message(l_full_name||': start');
203 END IF;
204
205 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
206 Fnd_Msg_Pub.Initialize;
207 END IF;
208
209 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION, p_api_version, L_API_NAME, G_PKG_NAME) THEN
210 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212
213 SAVEPOINT sp_create_objfundsum;
214
215 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
216 x_objfundsum_id := NULL;
217
218 IF l_objfundsum_rec.fund_currency IS NULL THEN
219 OPEN c_get_fund_currency(l_objfundsum_rec.fund_id);
220 FETCH c_get_fund_currency INTO l_objfundsum_rec.fund_currency;
221 CLOSE c_get_fund_currency;
222 END IF;
223
224 IF l_objfundsum_rec.object_currency IS NULL THEN
225 l_objfundsum_rec.object_currency := ozf_actbudgets_pvt.get_object_currency (
226 p_object => l_objfundsum_rec.object_type
227 , p_object_id => l_objfundsum_rec.object_id
228 , x_return_status => l_return_status
229 );
230 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
231 RAISE Fnd_Api.G_EXC_ERROR;
232 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
233 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
234 END IF;
235 END IF;
236
237 -- currency conversion for planned amount
238 complete_amount_fields (
239 p_currency_1 => l_objfundsum_rec.object_currency,
240 p_amount_1 => l_objfundsum_rec.plan_curr_planned_amt,
241 p_currency_2 => l_objfundsum_rec.fund_currency,
242 p_amount_2 => l_objfundsum_rec.planned_amt,
243 p_amount_3 => l_objfundsum_rec.univ_curr_planned_amt,
244 p_conv_date => p_conv_date, --bug 8532055
245 x_amount_1 => l_amount_1,
246 x_amount_2 => l_amount_2,
247 x_amount_3 => l_amount_3,
248 x_return_status => l_return_status,
249 x_msg_count => x_msg_count,
250 x_msg_data => x_msg_data
251 );
252 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
253 RAISE Fnd_Api.G_EXC_ERROR;
254 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
255 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
256 END IF;
257 l_objfundsum_rec.plan_curr_planned_amt := l_amount_1;
258 l_objfundsum_rec.planned_amt := l_amount_2;
259 l_objfundsum_rec.univ_curr_planned_amt := l_amount_3;
260
261 -- currency conversion for committed amount
262 complete_amount_fields (
263 p_currency_1 => l_objfundsum_rec.object_currency,
264 p_amount_1 => l_objfundsum_rec.plan_curr_committed_amt,
265 p_currency_2 => l_objfundsum_rec.fund_currency,
266 p_amount_2 => l_objfundsum_rec.committed_amt,
267 p_amount_3 => l_objfundsum_rec.univ_curr_committed_amt,
268 p_conv_date => p_conv_date, --bug 8532055
269 x_amount_1 => l_amount_1,
270 x_amount_2 => l_amount_2,
271 x_amount_3 => l_amount_3,
272 x_return_status => l_return_status,
273 x_msg_count => x_msg_count,
274 x_msg_data => x_msg_data
275 );
276 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
277 RAISE Fnd_Api.G_EXC_ERROR;
278 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
279 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
280 END IF;
281 l_objfundsum_rec.plan_curr_committed_amt := l_amount_1;
282 l_objfundsum_rec.committed_amt := l_amount_2;
283 l_objfundsum_rec.univ_curr_committed_amt := l_amount_3;
284
285 -- currency conversion for recal committed amount
286 complete_amount_fields (
287 p_currency_1 => l_objfundsum_rec.object_currency,
288 p_amount_1 => l_objfundsum_rec.plan_curr_recal_committed_amt,
289 p_currency_2 => l_objfundsum_rec.fund_currency,
290 p_amount_2 => l_objfundsum_rec.recal_committed_amt,
291 p_amount_3 => l_objfundsum_rec.univ_curr_recal_committed_amt,
292 p_conv_date => p_conv_date, --bug 8532055
293 x_amount_1 => l_amount_1,
294 x_amount_2 => l_amount_2,
295 x_amount_3 => l_amount_3,
296 x_return_status => l_return_status,
297 x_msg_count => x_msg_count,
298 x_msg_data => x_msg_data
299 );
300 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
301 RAISE Fnd_Api.G_EXC_ERROR;
302 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
303 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
304 END IF;
305 l_objfundsum_rec.plan_curr_recal_committed_amt := l_amount_1;
306 l_objfundsum_rec.recal_committed_amt := l_amount_2;
307 l_objfundsum_rec.univ_curr_recal_committed_amt := l_amount_3;
308
309 -- currency conversion for utilized amount
310 complete_amount_fields (
311 p_currency_1 => l_objfundsum_rec.object_currency,
312 p_amount_1 => l_objfundsum_rec.plan_curr_utilized_amt,
313 p_currency_2 => l_objfundsum_rec.fund_currency,
314 p_amount_2 => l_objfundsum_rec.utilized_amt,
315 p_amount_3 => l_objfundsum_rec.univ_curr_utilized_amt,
316 p_conv_date => p_conv_date, --bug 8532055
317 x_amount_1 => l_amount_1,
318 x_amount_2 => l_amount_2,
319 x_amount_3 => l_amount_3,
320 x_return_status => l_return_status,
321 x_msg_count => x_msg_count,
322 x_msg_data => x_msg_data
323 );
324 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
325 RAISE Fnd_Api.G_EXC_ERROR;
326 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
327 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
328 END IF;
329 l_objfundsum_rec.plan_curr_utilized_amt := l_amount_1;
330 l_objfundsum_rec.utilized_amt := l_amount_2;
331 l_objfundsum_rec.univ_curr_utilized_amt := l_amount_3;
332
333 -- currency conversion for earned amount
334 complete_amount_fields (
335 p_currency_1 => l_objfundsum_rec.object_currency,
336 p_amount_1 => l_objfundsum_rec.plan_curr_earned_amt,
337 p_currency_2 => l_objfundsum_rec.fund_currency,
338 p_amount_2 => l_objfundsum_rec.earned_amt,
339 p_amount_3 => l_objfundsum_rec.univ_curr_earned_amt,
340 p_conv_date => p_conv_date, --bug 8532055
341 x_amount_1 => l_amount_1,
342 x_amount_2 => l_amount_2,
343 x_amount_3 => l_amount_3,
344 x_return_status => l_return_status,
345 x_msg_count => x_msg_count,
346 x_msg_data => x_msg_data
347 );
348 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
349 RAISE Fnd_Api.G_EXC_ERROR;
350 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
351 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
352 END IF;
353 l_objfundsum_rec.plan_curr_earned_amt := l_amount_1;
354 l_objfundsum_rec.earned_amt := l_amount_2;
355 l_objfundsum_rec.univ_curr_earned_amt := l_amount_3;
356
357 -- currency conversion for paid amount
358 complete_amount_fields (
359 p_currency_1 => l_objfundsum_rec.object_currency,
360 p_amount_1 => l_objfundsum_rec.plan_curr_paid_amt,
361 p_currency_2 => l_objfundsum_rec.fund_currency,
362 p_amount_2 => l_objfundsum_rec.paid_amt,
363 p_amount_3 => l_objfundsum_rec.univ_curr_paid_amt,
364 p_conv_date => p_conv_date, --bug 8532055
365 x_amount_1 => l_amount_1,
366 x_amount_2 => l_amount_2,
367 x_amount_3 => l_amount_3,
368 x_return_status => l_return_status,
369 x_msg_count => x_msg_count,
370 x_msg_data => x_msg_data
371 );
372 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
373 RAISE Fnd_Api.G_EXC_ERROR;
374 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
375 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
376 END IF;
377 l_objfundsum_rec.plan_curr_paid_amt := l_amount_1;
378 l_objfundsum_rec.paid_amt := l_amount_2;
379 l_objfundsum_rec.univ_curr_paid_amt := l_amount_3;
380
381 validate_objfundsum (
382 p_api_version => l_api_version,
383 p_init_msg_list => p_init_msg_list,
384 p_validation_level => p_validation_level,
385 p_objfundsum_rec => l_objfundsum_rec,
386 x_msg_count => x_msg_count,
387 x_msg_data => x_msg_data,
388 x_return_status => l_return_status
389 );
390
391 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
392 RAISE Fnd_Api.G_EXC_ERROR;
393 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
394 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
395 END IF;
396
397 IF (OZF_DEBUG_HIGH_ON) THEN
398 ozf_utility_pvt.debug_message(l_full_name ||': insert');
399 END IF;
400
401 IF l_objfundsum_rec.objfundsum_id IS NULL THEN
402 LOOP
403 OPEN c_objfundsum_seq;
404 FETCH c_objfundsum_seq INTO l_objfundsum_rec.objfundsum_id;
405 CLOSE c_objfundsum_seq;
406
407 OPEN c_objfundsum_count(l_objfundsum_rec.objfundsum_id);
408 FETCH c_objfundsum_count INTO l_objfundsum_count ;
409 CLOSE c_objfundsum_count ;
410
411 EXIT WHEN l_objfundsum_count = 0 ;
412 END LOOP ;
413 END IF;
414
415 IF l_objfundsum_rec.reference_object_type IS NULL
416 AND l_objfundsum_rec.object_type = 'OFFR' THEN
417 OPEN c_get_reference(l_objfundsum_rec.object_id);
418 FETCH c_get_reference INTO l_objfundsum_rec.reference_object_type, l_objfundsum_rec.reference_object_id;
419 CLOSE c_get_reference;
420 END IF;
421
422 --dbms_output.put_line('Stat Before Insert : '||l_return_status);
423
424 INSERT INTO ozf_object_fund_summary(
425 objfundsum_id,
426 creation_date,
427 created_by,
428 last_update_date,
429 last_updated_by,
430 last_update_login,
431 object_version_number,
432 fund_id,
433 fund_currency,
434 object_type,
435 object_id,
436 object_currency,
437 reference_object_type,
438 reference_object_id,
439 source_from_parent,
440 planned_amt,
441 committed_amt,
442 recal_committed_amt,
443 utilized_amt,
444 earned_amt,
445 paid_amt,
446 plan_curr_planned_amt,
447 plan_curr_committed_amt,
448 plan_curr_recal_committed_amt,
449 plan_curr_utilized_amt,
450 plan_curr_earned_amt,
451 plan_curr_paid_amt,
452 univ_curr_planned_amt,
453 univ_curr_committed_amt,
454 univ_curr_recal_committed_amt,
455 univ_curr_utilized_amt,
456 univ_curr_earned_amt,
457 univ_curr_paid_amt,
458 attribute_category,
459 attribute1,
460 attribute2,
461 attribute3,
462 attribute4,
463 attribute5,
464 attribute6,
465 attribute7,
466 attribute8,
467 attribute9,
468 attribute10,
469 attribute11,
470 attribute12,
471 attribute13,
472 attribute14,
473 attribute15
474 )
475 VALUES (
476 l_objfundsum_rec.objfundsum_id,
477 sysdate,
478 Fnd_Global.User_ID,
479 sysdate,
480 Fnd_Global.User_ID,
481 Fnd_Global.Conc_Login_ID,
482 1, --Object Version Number
483 l_objfundsum_rec.fund_id,
484 l_objfundsum_rec.fund_currency,
485 l_objfundsum_rec.object_type,
486 l_objfundsum_rec.object_id,
487 l_objfundsum_rec.object_currency,
488 l_objfundsum_rec.reference_object_type,
489 l_objfundsum_rec.reference_object_id,
490 l_objfundsum_rec.source_from_parent,
491 l_objfundsum_rec.planned_amt,
492 l_objfundsum_rec.committed_amt,
493 l_objfundsum_rec.recal_committed_amt,
494 l_objfundsum_rec.utilized_amt,
495 l_objfundsum_rec.earned_amt,
496 l_objfundsum_rec.paid_amt,
497 l_objfundsum_rec.plan_curr_planned_amt,
498 l_objfundsum_rec.plan_curr_committed_amt,
499 l_objfundsum_rec.plan_curr_recal_committed_amt,
500 l_objfundsum_rec.plan_curr_utilized_amt,
501 l_objfundsum_rec.plan_curr_earned_amt,
502 l_objfundsum_rec.plan_curr_paid_amt,
503 l_objfundsum_rec.univ_curr_planned_amt,
504 l_objfundsum_rec.univ_curr_committed_amt,
505 l_objfundsum_rec.univ_curr_recal_committed_amt,
506 l_objfundsum_rec.univ_curr_utilized_amt,
507 l_objfundsum_rec.univ_curr_earned_amt,
508 l_objfundsum_rec.univ_curr_paid_amt,
509 l_objfundsum_rec.attribute_category,
510 l_objfundsum_rec.attribute1,
511 l_objfundsum_rec.attribute2,
512 l_objfundsum_rec.attribute3,
513 l_objfundsum_rec.attribute4,
514 l_objfundsum_rec.attribute5,
515 l_objfundsum_rec.attribute6,
516 l_objfundsum_rec.attribute7,
517 l_objfundsum_rec.attribute8,
518 l_objfundsum_rec.attribute9,
519 l_objfundsum_rec.attribute10,
520 l_objfundsum_rec.attribute11,
521 l_objfundsum_rec.attribute12,
522 l_objfundsum_rec.attribute13,
523 l_objfundsum_rec.attribute14,
524 l_objfundsum_rec.attribute15
525 );
526
527 x_objfundsum_id := l_objfundsum_rec.objfundsum_id;
528
529 Fnd_Msg_Pub.Count_And_Get (
530 p_count => x_msg_count,
531 p_data => x_msg_data,
532 p_encoded => Fnd_Api.G_FALSE
533 );
534
535 IF (OZF_DEBUG_HIGH_ON) THEN
536 ozf_utility_pvt.debug_message(l_full_name ||': end Success');
537 END IF;
538
539 EXCEPTION
540 WHEN Fnd_Api.G_EXC_ERROR THEN
541 ROLLBACK TO sp_create_objfundsum;
542 x_return_status := Fnd_Api.G_RET_STS_ERROR;
543 Fnd_Msg_Pub.Count_And_Get (
544 p_count => x_msg_count,
545 p_data => x_msg_data,
546 p_encoded => FND_API.G_FALSE
547 );
548 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
549 ROLLBACK TO sp_create_objfundsum;
550 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
551 Fnd_Msg_Pub.Count_And_Get (
552 p_count => x_msg_count,
553 p_data => x_msg_data,
554 p_encoded => FND_API.G_FALSE
555 );
556 WHEN OTHERS THEN
557 ROLLBACK TO sp_create_objfundsum;
558 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
559 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
560 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
561 END IF;
562 Fnd_Msg_Pub.Count_And_Get (
563 p_count => x_msg_count,
564 p_data => x_msg_data,
565 p_encoded => FND_API.G_FALSE
566 );
567 END Create_objfundsum;
568
569
570 -- NAME
571 -- update_objfundsum
572 --
573 -- PURPOSE
574 -- This Procedure updates record in object fund summary table.
575 --
576 -- NOTES
577 --
578 -- HISTORY
579 -- 05/26/1999 choang Created.
580 PROCEDURE Update_objfundsum (
581 p_api_version IN NUMBER,
582 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
583 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
584 p_objfundsum_rec IN objfundsum_rec_type,
585 p_conv_date IN DATE DEFAULT NULL, --bug 8532055
586 x_return_status OUT NOCOPY VARCHAR2,
587 x_msg_count OUT NOCOPY NUMBER,
588 x_msg_data OUT NOCOPY VARCHAR2
589 )
590 IS
591 L_API_VERSION CONSTANT NUMBER := 1.0;
592 L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_objfundsum';
593 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
594
595 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
596 l_objfundsum_rec objfundsum_rec_type := p_objfundsum_rec;
597 l_amount_1 NUMBER;
598 l_amount_2 NUMBER;
599 l_amount_3 NUMBER;
600
601 BEGIN
602
603 IF (OZF_DEBUG_HIGH_ON) THEN
604 ozf_utility_pvt.debug_message('Now updating objfundsum_id: '||p_objfundsum_rec.objfundsum_id);
605 END IF;
606
607 SAVEPOINT sp_Update_objfundsum;
608
609 IF (OZF_DEBUG_HIGH_ON) THEN
610 ozf_utility_pvt.debug_message(l_full_name||': start');
611 END IF;
612
613 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
614 Fnd_Msg_Pub.Initialize;
615 END IF;
616
617 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
618 p_api_version,
619 L_API_NAME,
620 G_PKG_NAME)
621 THEN
622 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
623 END IF;
624
625 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
626
627 IF (OZF_DEBUG_HIGH_ON) THEN
628 ozf_utility_pvt.debug_message(l_full_name ||': validate');
629 END IF;
630
631 -- replace g_miss_char/num/date with current column values
632 Complete_objfundsum_Rec(p_objfundsum_rec, l_objfundsum_rec);
633
634 -- currency conversion for planned amount
635 complete_amount_fields (
636 p_currency_1 => l_objfundsum_rec.object_currency,
637 p_amount_1 => l_objfundsum_rec.plan_curr_planned_amt,
638 p_currency_2 => l_objfundsum_rec.fund_currency,
639 p_amount_2 => l_objfundsum_rec.planned_amt,
640 p_amount_3 => l_objfundsum_rec.univ_curr_planned_amt,
641 p_conv_date => p_conv_date, --bug 8532055
642 x_amount_1 => l_amount_1,
643 x_amount_2 => l_amount_2,
644 x_amount_3 => l_amount_3,
645 x_return_status => l_return_status,
646 x_msg_count => x_msg_count,
647 x_msg_data => x_msg_data
648 );
649 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
650 RAISE Fnd_Api.G_EXC_ERROR;
651 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
652 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
653 END IF;
654 l_objfundsum_rec.plan_curr_planned_amt := l_amount_1;
655 l_objfundsum_rec.planned_amt := l_amount_2;
656 l_objfundsum_rec.univ_curr_planned_amt := l_amount_3;
657
658 -- currency conversion for committed amount
659 complete_amount_fields (
660 p_currency_1 => l_objfundsum_rec.object_currency,
661 p_amount_1 => l_objfundsum_rec.plan_curr_committed_amt,
662 p_currency_2 => l_objfundsum_rec.fund_currency,
663 p_amount_2 => l_objfundsum_rec.committed_amt,
664 p_amount_3 => l_objfundsum_rec.univ_curr_committed_amt,
665 p_conv_date => p_conv_date, --bug 8532055
666 x_amount_1 => l_amount_1,
667 x_amount_2 => l_amount_2,
668 x_amount_3 => l_amount_3,
669 x_return_status => l_return_status,
670 x_msg_count => x_msg_count,
671 x_msg_data => x_msg_data
672 );
673 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
674 RAISE Fnd_Api.G_EXC_ERROR;
675 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
676 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
677 END IF;
678 l_objfundsum_rec.plan_curr_committed_amt := l_amount_1;
679 l_objfundsum_rec.committed_amt := l_amount_2;
680 l_objfundsum_rec.univ_curr_committed_amt := l_amount_3;
681
682 -- currency conversion for recal committed amount
683 complete_amount_fields (
684 p_currency_1 => l_objfundsum_rec.object_currency,
685 p_amount_1 => l_objfundsum_rec.plan_curr_recal_committed_amt,
686 p_currency_2 => l_objfundsum_rec.fund_currency,
687 p_amount_2 => l_objfundsum_rec.recal_committed_amt,
688 p_amount_3 => l_objfundsum_rec.univ_curr_recal_committed_amt,
689 p_conv_date => p_conv_date, --bug 8532055
690 x_amount_1 => l_amount_1,
691 x_amount_2 => l_amount_2,
692 x_amount_3 => l_amount_3,
693 x_return_status => l_return_status,
694 x_msg_count => x_msg_count,
695 x_msg_data => x_msg_data
696 );
697 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
698 RAISE Fnd_Api.G_EXC_ERROR;
699 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
700 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
701 END IF;
702 l_objfundsum_rec.plan_curr_recal_committed_amt := l_amount_1;
703 l_objfundsum_rec.recal_committed_amt := l_amount_2;
704 l_objfundsum_rec.univ_curr_recal_committed_amt := l_amount_3;
705
706 -- nepanda : Fix for Bug 10077018
707 IF l_objfundsum_rec.plan_curr_utilized_amt = 0
708 AND l_objfundsum_rec.utilized_amt <> 0 THEN
709 l_objfundsum_rec.univ_curr_utilized_amt := 0;
710 l_objfundsum_rec.utilized_amt := 0;
711 END IF;
712 -- currency conversion for utilized amount
713 complete_amount_fields (
714 p_currency_1 => l_objfundsum_rec.object_currency,
715 p_amount_1 => l_objfundsum_rec.plan_curr_utilized_amt,
716 p_currency_2 => l_objfundsum_rec.fund_currency,
717 p_amount_2 => l_objfundsum_rec.utilized_amt,
718 p_amount_3 => l_objfundsum_rec.univ_curr_utilized_amt,
719 p_conv_date => p_conv_date, --bug 8532055
720 x_amount_1 => l_amount_1,
721 x_amount_2 => l_amount_2,
722 x_amount_3 => l_amount_3,
723 x_return_status => l_return_status,
724 x_msg_count => x_msg_count,
725 x_msg_data => x_msg_data
726 );
727 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
728 RAISE Fnd_Api.G_EXC_ERROR;
729 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
730 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
731 END IF;
732 l_objfundsum_rec.plan_curr_utilized_amt := l_amount_1;
733 l_objfundsum_rec.utilized_amt := l_amount_2;
734 l_objfundsum_rec.univ_curr_utilized_amt := l_amount_3;
735
736 -- nepanda : Fix for Bug 10077018
737 IF l_objfundsum_rec.plan_curr_earned_amt = 0
738 AND l_objfundsum_rec.earned_amt <> 0 THEN
739 l_objfundsum_rec.univ_curr_earned_amt := 0;
740 l_objfundsum_rec.earned_amt := 0;
741 END IF;
742 -- currency conversion for earned amount
743 complete_amount_fields (
744 p_currency_1 => l_objfundsum_rec.object_currency,
745 p_amount_1 => l_objfundsum_rec.plan_curr_earned_amt,
746 p_currency_2 => l_objfundsum_rec.fund_currency,
747 p_amount_2 => l_objfundsum_rec.earned_amt,
748 p_amount_3 => l_objfundsum_rec.univ_curr_earned_amt,
749 p_conv_date => p_conv_date, --bug 8532055
750 x_amount_1 => l_amount_1,
751 x_amount_2 => l_amount_2,
752 x_amount_3 => l_amount_3,
753 x_return_status => l_return_status,
754 x_msg_count => x_msg_count,
755 x_msg_data => x_msg_data
756 );
757 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
758 RAISE Fnd_Api.G_EXC_ERROR;
759 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
760 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
761 END IF;
762 l_objfundsum_rec.plan_curr_earned_amt := l_amount_1;
763 l_objfundsum_rec.earned_amt := l_amount_2;
764 l_objfundsum_rec.univ_curr_earned_amt := l_amount_3;
765
766 -- nepanda : Fix for Bug 10077018
767 IF l_objfundsum_rec.plan_curr_paid_amt = 0
768 AND l_objfundsum_rec.paid_amt <> 0 THEN
769 l_objfundsum_rec.univ_curr_paid_amt := 0;
770 l_objfundsum_rec.paid_amt := 0;
771 END IF;
772 -- currency conversion for paid amount
773 complete_amount_fields (
774 p_currency_1 => l_objfundsum_rec.object_currency,
775 p_amount_1 => l_objfundsum_rec.plan_curr_paid_amt,
776 p_currency_2 => l_objfundsum_rec.fund_currency,
777 p_amount_2 => l_objfundsum_rec.paid_amt,
778 p_amount_3 => l_objfundsum_rec.univ_curr_paid_amt,
779 p_conv_date => p_conv_date, --bug 8532055
780 x_amount_1 => l_amount_1,
781 x_amount_2 => l_amount_2,
782 x_amount_3 => l_amount_3,
783 x_return_status => l_return_status,
784 x_msg_count => x_msg_count,
785 x_msg_data => x_msg_data
786 );
787 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
788 RAISE Fnd_Api.G_EXC_ERROR;
789 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
790 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
791 END IF;
792 l_objfundsum_rec.plan_curr_paid_amt := l_amount_1;
793 l_objfundsum_rec.paid_amt := l_amount_2;
794 l_objfundsum_rec.univ_curr_paid_amt := l_amount_3;
795
796 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
797 Validate_objfundsum (
798 p_api_version => l_api_version,
799 p_init_msg_list => p_init_msg_list,
800 p_validation_level => p_validation_level,
801 p_objfundsum_rec => l_objfundsum_rec,
802 x_msg_count => x_msg_count,
803 x_msg_data => x_msg_data,
804 x_return_status => l_return_status
805 );
806 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
807 RAISE Fnd_Api.g_exc_unexpected_error;
808 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
809 RAISE Fnd_Api.g_exc_error;
810 END IF;
811 END IF;
812
813
814 IF (OZF_DEBUG_HIGH_ON) THEN
815 ozf_utility_pvt.debug_message(l_full_name ||': update object fund summary Table');
816 END IF;
817
818 UPDATE ozf_object_fund_summary
819 SET object_version_number= object_version_number + 1,
820 last_update_date = SYSDATE,
821 last_updated_by = Fnd_Global.User_ID,
822 last_update_login = Fnd_Global.Conc_Login_ID,
823 fund_id = l_objfundsum_rec.fund_id,
824 fund_currency = l_objfundsum_rec.fund_currency,
825 object_type = l_objfundsum_rec.object_type,
826 object_id = l_objfundsum_rec.object_id,
827 object_currency = l_objfundsum_rec.object_currency,
828 reference_object_type = l_objfundsum_rec.reference_object_type,
829 reference_object_id = l_objfundsum_rec.reference_object_id,
830 source_from_parent = l_objfundsum_rec.source_from_parent,
831 planned_amt = l_objfundsum_rec.planned_amt,
832 committed_amt = l_objfundsum_rec.committed_amt,
833 recal_committed_amt = l_objfundsum_rec.recal_committed_amt,
834 utilized_amt = l_objfundsum_rec.utilized_amt,
835 earned_amt = l_objfundsum_rec.earned_amt,
836 paid_amt = l_objfundsum_rec.paid_amt,
837 plan_curr_planned_amt = l_objfundsum_rec.plan_curr_planned_amt,
838 plan_curr_committed_amt = l_objfundsum_rec.plan_curr_committed_amt,
839 plan_curr_recal_committed_amt = l_objfundsum_rec.plan_curr_recal_committed_amt,
840 plan_curr_utilized_amt = l_objfundsum_rec.plan_curr_utilized_amt,
841 plan_curr_earned_amt = l_objfundsum_rec.plan_curr_earned_amt,
842 plan_curr_paid_amt = l_objfundsum_rec.plan_curr_paid_amt,
843 univ_curr_planned_amt = l_objfundsum_rec.univ_curr_planned_amt,
844 univ_curr_committed_amt = l_objfundsum_rec.univ_curr_committed_amt,
845 univ_curr_recal_committed_amt = l_objfundsum_rec.univ_curr_recal_committed_amt,
846 univ_curr_utilized_amt = l_objfundsum_rec.univ_curr_utilized_amt,
847 univ_curr_earned_amt = l_objfundsum_rec.univ_curr_earned_amt,
848 univ_curr_paid_amt = l_objfundsum_rec.univ_curr_paid_amt,
849 attribute_category = l_objfundsum_rec.attribute_category,
850 attribute1 = l_objfundsum_rec.attribute1,
851 attribute2 = l_objfundsum_rec.attribute2,
852 attribute3 = l_objfundsum_rec.attribute3,
853 attribute4 = l_objfundsum_rec.attribute4,
854 attribute5 = l_objfundsum_rec.attribute5,
855 attribute6 = l_objfundsum_rec.attribute6,
856 attribute7 = l_objfundsum_rec.attribute7,
857 attribute8 = l_objfundsum_rec.attribute8,
858 attribute9 = l_objfundsum_rec.attribute9,
859 attribute10 = l_objfundsum_rec.attribute10,
860 attribute11 = l_objfundsum_rec.attribute11,
861 attribute12 = l_objfundsum_rec.attribute12,
862 attribute13 = l_objfundsum_rec.attribute13,
863 attribute14 = l_objfundsum_rec.attribute14,
864 attribute15 = l_objfundsum_rec.attribute15
865 WHERE objfundsum_id = l_objfundsum_rec.objfundsum_id
866 AND object_version_number = l_objfundsum_rec.object_version_number;
867
868 IF (SQL%NOTFOUND) THEN
869 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
870 Fnd_Message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
871 Fnd_Msg_Pub.ADD;
872 END IF;
873 RAISE Fnd_Api.g_exc_error;
874 END IF;
875
876 Fnd_Msg_Pub.Count_And_Get (
877 p_count => x_msg_count,
878 p_data => x_msg_data,
879 p_encoded => Fnd_Api.G_FALSE
880 );
881
882 IF (OZF_DEBUG_HIGH_ON) THEN
883 ozf_utility_pvt.debug_message(l_full_name ||': end');
884 END IF;
885
886
887 EXCEPTION
888 WHEN Fnd_Api.G_EXC_ERROR THEN
889 ROLLBACK TO SP_Update_objfundsum;
890 x_return_status := Fnd_Api.G_RET_STS_ERROR;
891 Fnd_Msg_Pub.Count_And_Get (
892 p_count => x_msg_count,
893 p_data => x_msg_data,
894 p_encoded => FND_API.G_FALSE
895 );
896 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
897 ROLLBACK TO SP_Update_objfundsum;
898 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
899 Fnd_Msg_Pub.Count_And_Get (
900 p_count => x_msg_count,
901 p_data => x_msg_data,
902 p_encoded => FND_API.G_FALSE
903 );
904 WHEN OTHERS THEN
905 ROLLBACK TO SP_Update_objfundsum;
906 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
907 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
908 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
909 END IF;
910 Fnd_Msg_Pub.Count_And_Get (
911 p_count => x_msg_count,
912 p_data => x_msg_data,
913 p_encoded => FND_API.G_FALSE
914 );
915 END Update_objfundsum;
916
917
918 -- NAME
919 -- process_objfundsum
920 --
921 -- PURPOSE
922 -- This Procedure creates a record in object fund summary table if it's not there.
923 -- update a record in object fund summary table if it's already there
924 -- for update, it does cumulative update. E.g. existing record has earned_amount=$100
925 -- if p_objfundsum_rec.earned_amount=$50, after this call earned_amount=$150
926 --
927 -- NOTES
928 --
929 --
930 PROCEDURE process_objfundsum (
931 p_api_version IN NUMBER,
932 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
933 p_validation_level IN NUMBER := Fnd_Api.G_Valid_Level_Full,
934 p_objfundsum_rec IN objfundsum_rec_type,
935 x_return_status OUT NOCOPY VARCHAR2,
936 x_msg_count OUT NOCOPY NUMBER,
937 x_msg_data OUT NOCOPY VARCHAR2,
938 x_objfundsum_id OUT NOCOPY NUMBER
939 )
940 IS
941 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_OBJFUNDSUM';
942 l_tmp_objfundsum_rec ozf_objfundsum_pvt.objfundsum_rec_type := NULL;
943 l_objfundsum_rec ozf_objfundsum_pvt.objfundsum_rec_type := p_objfundsum_rec;
944 l_objfundsum_id NUMBER;
945 l_msg_count NUMBER;
946 l_msg_data VARCHAR2(240);
947 l_return_status VARCHAR2(30);
948
949 CURSOR c_get_objfundsum_rec(p_object_type IN VARCHAR2, p_object_id IN NUMBER, p_fund_id IN NUMBER) IS
950 SELECT objfundsum_id
951 , object_version_number
952 , planned_amt
953 , plan_curr_planned_amt
954 , univ_curr_planned_amt
955 , committed_amt
956 , plan_curr_committed_amt
957 , univ_curr_committed_amt
958 , recal_committed_amt
959 , plan_curr_recal_committed_amt
960 , univ_curr_recal_committed_amt
961 , utilized_amt
962 , plan_curr_utilized_amt
963 , univ_curr_utilized_amt
964 , earned_amt
965 , plan_curr_earned_amt
966 , univ_curr_earned_amt
967 , paid_amt
968 , plan_curr_paid_amt
969 , univ_curr_paid_amt
970 FROM ozf_object_fund_summary
971 WHERE object_type = p_object_type
972 AND object_id = p_object_id
973 AND fund_id = p_fund_id;
974
975 BEGIN
976 SAVEPOINT SP_process_objfundsum;
977
978 -- dbms_output.put_line('process_objfunsum: id=' || p_objfundsum_rec.objfundsum_id);
979 IF p_objfundsum_rec.objfundsum_id IS NULL THEN
980 OPEN c_get_objfundsum_rec(p_objfundsum_rec.object_type
981 , p_objfundsum_rec.object_id
982 , p_objfundsum_rec.fund_id);
983 FETCH c_get_objfundsum_rec INTO l_objfundsum_rec.objfundsum_id
984 , l_objfundsum_rec.object_version_number
985 , l_tmp_objfundsum_rec.planned_amt
986 , l_tmp_objfundsum_rec.plan_curr_planned_amt
987 , l_tmp_objfundsum_rec.univ_curr_planned_amt
988 , l_tmp_objfundsum_rec.committed_amt
989 , l_tmp_objfundsum_rec.plan_curr_committed_amt
990 , l_tmp_objfundsum_rec.univ_curr_committed_amt
991 , l_tmp_objfundsum_rec.recal_committed_amt
992 , l_tmp_objfundsum_rec.plan_curr_recal_committed_amt
993 , l_tmp_objfundsum_rec.univ_curr_recal_committed_amt
994 , l_tmp_objfundsum_rec.utilized_amt
995 , l_tmp_objfundsum_rec.plan_curr_utilized_amt
996 , l_tmp_objfundsum_rec.univ_curr_utilized_amt
997 , l_tmp_objfundsum_rec.earned_amt
998 , l_tmp_objfundsum_rec.plan_curr_earned_amt
999 , l_tmp_objfundsum_rec.univ_curr_earned_amt
1000 , l_tmp_objfundsum_rec.paid_amt
1001 , l_tmp_objfundsum_rec.plan_curr_paid_amt
1002 , l_tmp_objfundsum_rec.univ_curr_paid_amt;
1003 IF c_get_objfundsum_rec%NOTFOUND THEN
1004 CLOSE c_get_objfundsum_rec;
1005 ozf_objfundsum_pvt.create_objfundsum(
1006 p_api_version => 1.0,
1007 p_init_msg_list => p_init_msg_list,
1008 p_validation_level => p_validation_level,
1009 p_objfundsum_rec => p_objfundsum_rec,
1010 x_return_status => x_return_status,
1011 x_msg_count => x_msg_count,
1012 x_msg_data => x_msg_data,
1013 x_objfundsum_id => l_objfundsum_id
1014 );
1015 -- dbms_output.put_line('process_objfunsum: create_objfunsum returns ' || l_return_status);
1016 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1017 RAISE fnd_api.g_exc_unexpected_error;
1018 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1019 RAISE fnd_api.g_exc_error;
1020 END IF;
1021 RETURN;
1022 ELSE
1023 CLOSE c_get_objfundsum_rec;
1024 END IF; -- IF c_get_objfundsum_rec%NOTFOUND THEN
1025 END IF; -- IF p_objfundsum_rec.objfundsum_id IS NULL
1026
1027 IF NVL(p_objfundsum_rec.planned_amt, 0) <> 0 THEN
1028 l_objfundsum_rec.planned_amt := NVL(l_tmp_objfundsum_rec.planned_amt, 0) + p_objfundsum_rec.planned_amt;
1029 END IF;
1030 IF NVL(p_objfundsum_rec.plan_curr_planned_amt, 0) <> 0 THEN
1031 l_objfundsum_rec.plan_curr_planned_amt := NVL(l_tmp_objfundsum_rec.plan_curr_planned_amt, 0)
1032 + p_objfundsum_rec.plan_curr_planned_amt;
1033 END IF;
1034 IF NVL(p_objfundsum_rec.univ_curr_planned_amt, 0) <> 0 THEN
1035 l_objfundsum_rec.univ_curr_planned_amt := NVL(l_tmp_objfundsum_rec.univ_curr_planned_amt, 0)
1036 + p_objfundsum_rec.univ_curr_planned_amt;
1037 END IF;
1038 IF NVL(p_objfundsum_rec.committed_amt, 0) <> 0 THEN
1039 l_objfundsum_rec.committed_amt := NVL(l_tmp_objfundsum_rec.committed_amt, 0) + p_objfundsum_rec.committed_amt;
1040 END IF;
1041 IF NVL(p_objfundsum_rec.plan_curr_committed_amt, 0) <> 0 THEN
1042 l_objfundsum_rec.plan_curr_committed_amt := NVL(l_tmp_objfundsum_rec.plan_curr_committed_amt, 0)
1043 + p_objfundsum_rec.plan_curr_committed_amt;
1044 END IF;
1045 IF NVL(p_objfundsum_rec.univ_curr_committed_amt, 0) <> 0 THEN
1046 l_objfundsum_rec.univ_curr_committed_amt := NVL(l_tmp_objfundsum_rec.univ_curr_committed_amt, 0)
1047 + p_objfundsum_rec.univ_curr_committed_amt;
1048 END IF;
1049 IF NVL(p_objfundsum_rec.recal_committed_amt, 0) <> 0 THEN
1050 l_objfundsum_rec.recal_committed_amt := NVL(l_tmp_objfundsum_rec.recal_committed_amt, 0)
1051 + p_objfundsum_rec.recal_committed_amt;
1052 END IF;
1053 IF NVL(p_objfundsum_rec.plan_curr_recal_committed_amt, 0) <> 0 THEN
1054 l_objfundsum_rec.plan_curr_recal_committed_amt := NVL(l_tmp_objfundsum_rec.plan_curr_recal_committed_amt, 0)
1055 + p_objfundsum_rec.plan_curr_recal_committed_amt;
1056 END IF;
1057 IF NVL(p_objfundsum_rec.univ_curr_recal_committed_amt, 0) <> 0 THEN
1058 l_objfundsum_rec.univ_curr_recal_committed_amt := NVL(l_tmp_objfundsum_rec.univ_curr_recal_committed_amt, 0)
1059 + p_objfundsum_rec.univ_curr_recal_committed_amt;
1060 END IF;
1061 IF NVL(p_objfundsum_rec.utilized_amt, 0) <> 0 THEN
1062 l_objfundsum_rec.utilized_amt := NVL(l_tmp_objfundsum_rec.utilized_amt, 0) + p_objfundsum_rec.utilized_amt;
1063 END IF;
1064 IF NVL(p_objfundsum_rec.plan_curr_utilized_amt, 0) <> 0 THEN
1065 l_objfundsum_rec.plan_curr_utilized_amt := NVL(l_tmp_objfundsum_rec.plan_curr_utilized_amt, 0)
1066 + p_objfundsum_rec.plan_curr_utilized_amt;
1067 END IF;
1068 IF NVL(p_objfundsum_rec.univ_curr_utilized_amt, 0) <> 0 THEN
1069 l_objfundsum_rec.univ_curr_utilized_amt := NVL(l_tmp_objfundsum_rec.univ_curr_utilized_amt, 0)
1070 + p_objfundsum_rec.univ_curr_utilized_amt;
1071 END IF;
1072 IF NVL(p_objfundsum_rec.earned_amt, 0) <> 0 THEN
1073 l_objfundsum_rec.earned_amt := NVL(l_tmp_objfundsum_rec.earned_amt, 0) + p_objfundsum_rec.earned_amt;
1074 END IF;
1075 IF NVL(p_objfundsum_rec.plan_curr_earned_amt, 0) <> 0 THEN
1076 l_objfundsum_rec.plan_curr_earned_amt := NVL(l_tmp_objfundsum_rec.plan_curr_earned_amt, 0)
1077 + p_objfundsum_rec.plan_curr_earned_amt;
1078 END IF;
1079 IF NVL(p_objfundsum_rec.univ_curr_earned_amt, 0) <> 0 THEN
1080 l_objfundsum_rec.univ_curr_earned_amt := NVL(l_tmp_objfundsum_rec.univ_curr_earned_amt, 0)
1081 + p_objfundsum_rec.univ_curr_earned_amt;
1082 END IF;
1083 IF NVL(p_objfundsum_rec.paid_amt, 0) <> 0 THEN
1084 l_objfundsum_rec.paid_amt := NVL(l_tmp_objfundsum_rec.paid_amt, 0) + p_objfundsum_rec.paid_amt;
1085 END IF;
1086 IF NVL(p_objfundsum_rec.plan_curr_paid_amt, 0) <> 0 THEN
1087 l_objfundsum_rec.plan_curr_paid_amt := NVL(l_tmp_objfundsum_rec.plan_curr_paid_amt, 0)
1088 + p_objfundsum_rec.plan_curr_paid_amt;
1089 END IF;
1090 IF NVL(p_objfundsum_rec.univ_curr_paid_amt, 0) <> 0 THEN
1091 l_objfundsum_rec.univ_curr_paid_amt := NVL(l_tmp_objfundsum_rec.univ_curr_paid_amt, 0)
1092 + p_objfundsum_rec.univ_curr_paid_amt;
1093 END IF;
1094
1095 ozf_objfundsum_pvt.update_objfundsum(
1096 p_api_version => p_api_version,
1097 p_init_msg_list => p_init_msg_list,
1098 p_validation_level => p_validation_level,
1099 p_objfundsum_rec => l_objfundsum_rec,
1100 x_return_status => x_return_status,
1101 x_msg_count => x_msg_count,
1102 x_msg_data => x_msg_data
1103 );
1104 -- dbms_output.put_line('process_objfunsum: update_objfunsum returns ' || x_return_status);
1105 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1106 RAISE fnd_api.g_exc_unexpected_error;
1107 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1108 RAISE fnd_api.g_exc_error;
1109 END IF;
1110
1111 EXCEPTION
1112 WHEN Fnd_Api.G_EXC_ERROR THEN
1113 ROLLBACK TO SP_process_objfundsum;
1114 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1115 Fnd_Msg_Pub.Count_And_Get (
1116 p_count => x_msg_count,
1117 p_data => x_msg_data,
1118 p_encoded => FND_API.G_FALSE
1119 );
1120 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1121 ROLLBACK TO SP_process_objfundsum;
1122 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1123 Fnd_Msg_Pub.Count_And_Get (
1124 p_count => x_msg_count,
1125 p_data => x_msg_data,
1126 p_encoded => FND_API.G_FALSE
1127 );
1128 WHEN OTHERS THEN
1129 ROLLBACK TO SP_process_objfundsum;
1130 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1131 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
1132 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1133 END IF;
1134 Fnd_Msg_Pub.Count_And_Get (
1135 p_count => x_msg_count,
1136 p_data => x_msg_data,
1137 p_encoded => FND_API.G_FALSE
1138 );
1139 END process_objfundsum;
1140
1141
1142 -- NAME
1143 -- Validate_objfundsum
1144 --
1145 -- PURPOSE
1146 -- Validation API for Activity metrics.
1147 --
1148 -- NOTES
1149 --
1150 -- HISTORY
1151 -- 06/30/2005 yzhao Created.
1152
1153 PROCEDURE Validate_objfundsum (
1154 p_api_version IN NUMBER,
1155 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1156 p_validation_level IN NUMBER := Fnd_Api.G_Valid_Level_Full,
1157 p_objfundsum_rec IN objfundsum_rec_type,
1158 x_return_status OUT NOCOPY VARCHAR2,
1159 x_msg_count OUT NOCOPY NUMBER,
1160 x_msg_data OUT NOCOPY VARCHAR2
1161 )
1162 IS
1163 L_API_VERSION CONSTANT NUMBER := 1.0;
1164 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_OBJFUNDSUM';
1165 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1166
1167 l_table_name VARCHAR2 (30);
1168 l_pk_name VARCHAR2 (30);
1169 l_pk_value VARCHAR2 (30);
1170 l_return_status VARCHAR2(1);
1171
1172 BEGIN
1173 IF (OZF_DEBUG_HIGH_ON) THEN
1174 ozf_utility_pvt.debug_message(l_full_name||': start');
1175 END IF;
1176
1177 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
1178 Fnd_Msg_Pub.Initialize;
1179 END IF;
1180
1181 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
1182 p_api_version,
1183 L_API_NAME,
1184 G_PKG_NAME)
1185 THEN
1186 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1187 END IF;
1188
1189 x_return_status := NULL;
1190
1191 IF (OZF_DEBUG_HIGH_ON) THEN
1192 ozf_utility_pvt.debug_message(l_full_name||': Validation');
1193 END IF;
1194
1195 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
1196 -- fund_id is required
1197 IF p_objfundsum_rec.fund_id IS NULL OR
1198 p_objfundsum_rec.fund_id = fnd_api.g_miss_num
1199 THEN
1200 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1201 THEN
1202 Fnd_Message.Set_Name('OZF', 'OZF_OBJFUNDSUM_MISSING_FUND_ID');
1203 Fnd_Msg_Pub.ADD;
1204 END IF;
1205 RAISE Fnd_Api.G_EXC_ERROR;
1206 END IF;
1207
1208 -- fund_currency is required
1209 IF p_objfundsum_rec.fund_currency IS NULL OR
1210 p_objfundsum_rec.fund_currency = fnd_api.g_miss_char
1211 THEN
1212 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1213 THEN
1214 Fnd_Message.Set_Name('OZF', 'OZF_OBJFUNDSUM_MISSING_FUND_CURRENCY');
1215 Fnd_Msg_Pub.ADD;
1216 END IF;
1217 RAISE Fnd_Api.G_EXC_ERROR;
1218 END IF;
1219
1220 -- object_type is required
1221 IF p_objfundsum_rec.object_type IS NULL OR
1222 p_objfundsum_rec.object_type = fnd_api.g_miss_char
1223 THEN
1224 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1225 THEN
1226 Fnd_Message.Set_Name('OZF', 'OZF_OBJFUNDSUM_MISSING_OBJECT_TYPE');
1227 Fnd_Msg_Pub.ADD;
1228 END IF;
1229 RAISE Fnd_Api.G_EXC_ERROR;
1230 END IF;
1231
1232 -- object_id is required
1233 IF p_objfundsum_rec.object_id IS NULL OR
1234 p_objfundsum_rec.object_id = fnd_api.g_miss_num
1235 THEN
1236 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1237 THEN
1238 Fnd_Message.Set_Name('OZF', 'OZF_OBJFUNDSUM_MISSING_OBJECT_ID');
1239 Fnd_Msg_Pub.ADD;
1240 END IF;
1241 RAISE Fnd_Api.G_EXC_ERROR;
1242 END IF;
1243
1244 -- object_currency is required
1245 IF p_objfundsum_rec.object_currency IS NULL OR
1246 p_objfundsum_rec.object_currency = fnd_api.g_miss_char
1247 THEN
1248 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1249 THEN
1250 Fnd_Message.Set_Name('OZF', 'OZF_OBJFUNDSUM_MISSING_OBJECT_CURRENCY');
1251 Fnd_Msg_Pub.ADD;
1252 END IF;
1253 RAISE Fnd_Api.G_EXC_ERROR;
1254 END IF;
1255
1256 -- object_type should be valid code in lookup type OZF_FUND_TYPE
1257 IF ozf_utility_pvt.check_lookup_exists (
1258 p_lookup_type=> 'OZF_FUND_SOURCE'
1259 ,p_lookup_code=> p_objfundsum_rec.object_type
1260 ) = fnd_api.g_false THEN
1261 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
1262 fnd_message.set_name ('OZF', 'OZF_OBJFUNDSUM_BAD_OBJTYPE');
1263 fnd_msg_pub.ADD;
1264 END IF;
1265 RAISE Fnd_Api.G_EXC_ERROR;
1266 END IF;
1267
1268 -- fund_id should be valid in ozf_funds_all_b
1269 IF ozf_utility_pvt.check_fk_exists (
1270 p_table_name=> 'OZF_FUNDS_ALL_B'
1271 ,p_pk_name=> 'FUND_ID'
1272 ,p_pk_value=> p_objfundsum_rec.fund_id
1273 ) = fnd_api.g_false THEN
1274 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
1275 fnd_message.set_name ('OZF', 'OZF_OBJFUNDSUM_BAD_FUNDID');
1276 fnd_msg_pub.ADD;
1277 END IF;
1278 RAISE Fnd_Api.G_EXC_ERROR;
1279 END IF;
1280
1281 -- Check FK parameter: object_id
1282 IF p_objfundsum_rec.object_type = ('EVEH') THEN
1283 l_table_name := 'AMS_EVENT_HEADERS_B';
1284 l_pk_name := 'EVENT_HEADER_ID';
1285 ELSIF p_objfundsum_rec.object_type IN ( 'EVEO','EONE') THEN
1286 l_table_name := 'AMS_EVENT_OFFERS_B';
1287 l_pk_name := 'EVENT_OFFER_ID';
1288 ELSIF p_objfundsum_rec.object_type = 'DELV' THEN
1289 l_table_name := 'AMS_DELIVERABLES_B';
1290 l_pk_name := 'DELIVERABLE_ID';
1291 ELSIF p_objfundsum_rec.object_type = 'CAMP' THEN
1292 l_table_name := 'AMS_CAMPAIGNS_B';
1293 l_pk_name := 'CAMPAIGN_ID';
1294 ELSIF p_objfundsum_rec.object_type = 'CSCH' THEN
1295 l_table_name := 'AMS_CAMPAIGN_SCHEDULES_B';
1296 l_pk_name := 'SCHEDULE_ID';
1297 ELSIF p_objfundsum_rec.object_type = 'OFFR'
1298 OR p_objfundsum_rec.object_type = 'PRIC' THEN
1299 l_table_name := 'QP_LIST_HEADERS';
1300 l_pk_name := 'LIST_HEADER_ID';
1301 END IF;
1302 l_pk_value := p_objfundsum_rec.object_id;
1303 IF ozf_utility_pvt.check_fk_exists (
1304 p_table_name=> l_table_name
1305 ,p_pk_name=> l_pk_name
1306 ,p_pk_value=> l_pk_value
1307 ) = fnd_api.g_false THEN
1308 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
1309 fnd_message.set_name ('OZF', 'OZF_OBJFUNDSUM_BAD_OBJID');
1310 fnd_msg_pub.ADD;
1311 END IF;
1312 RAISE Fnd_Api.G_EXC_ERROR;
1313 END IF;
1314
1315 END IF;
1316
1317 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1318
1319 Fnd_Msg_Pub.Count_And_Get (
1320 p_count => x_msg_count,
1321 p_data => x_msg_data,
1322 p_encoded => Fnd_Api.G_FALSE
1323 );
1324
1325 IF (OZF_DEBUG_HIGH_ON) THEN
1326 ozf_utility_pvt.debug_message(l_full_name ||': end');
1327 END IF;
1328
1329 EXCEPTION
1330 WHEN Fnd_Api.G_EXC_ERROR THEN
1331 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1332 Fnd_Msg_Pub.Count_And_Get (
1333 p_count => x_msg_count,
1334 p_data => x_msg_data,
1335 p_encoded => FND_API.G_FALSE
1336 );
1337 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1338 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1339 Fnd_Msg_Pub.Count_And_Get (
1340 p_count => x_msg_count,
1341 p_data => x_msg_data,
1342 p_encoded => FND_API.G_FALSE
1343 );
1344 WHEN OTHERS THEN
1345 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1346 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
1347 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1348 END IF;
1349 Fnd_Msg_Pub.Count_And_Get (
1350 p_count => x_msg_count,
1351 p_data => x_msg_data,
1352 p_encoded => FND_API.G_FALSE
1353 );
1354 END Validate_objfundsum;
1355
1356
1357
1358 --
1359 -- NAME
1360 -- Complete_objfundsum_Rec
1361 --
1362 -- PURPOSE
1363 -- Returns the Initialized objectfundsummary Record
1364 --
1365 -- NOTES
1366 --
1367 -- HISTORY
1368 -- 07/19/1999 choang Created.
1369 --
1370 PROCEDURE Complete_objfundsum_Rec(
1371 p_objfundsum_rec IN objfundsum_rec_type,
1372 x_complete_rec IN OUT NOCOPY objfundsum_rec_type
1373 )
1374 IS
1375 CURSOR c_objfundsum IS
1376 SELECT *
1377 FROM ozf_object_fund_summary
1378 WHERE objfundsum_id = p_objfundsum_rec.objfundsum_id;
1379
1380 l_objfundsum_rec c_objfundsum%ROWTYPE;
1381 BEGIN
1382
1383 x_complete_rec := p_objfundsum_rec;
1384
1385 OPEN c_objfundsum;
1386 FETCH c_objfundsum INTO l_objfundsum_rec;
1387 IF c_objfundsum%NOTFOUND THEN
1388 CLOSE c_objfundsum;
1389 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
1390 Fnd_Message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1391 Fnd_Msg_Pub.ADD;
1392 END IF;
1393 RAISE Fnd_Api.g_exc_error;
1394 END IF;
1395 CLOSE c_objfundsum;
1396
1397 IF p_objfundsum_rec.fund_id = Fnd_Api.G_MISS_NUM THEN
1398 x_complete_rec.fund_id := NULL;
1399 END IF;
1400 IF p_objfundsum_rec.fund_id IS NULL THEN
1401 x_complete_rec.fund_id := l_objfundsum_rec.fund_id;
1402 END IF;
1403
1404 IF p_objfundsum_rec.fund_currency = Fnd_Api.G_MISS_CHAR THEN
1405 x_complete_rec.fund_currency := NULL;
1406 END IF;
1407 IF p_objfundsum_rec.fund_currency IS NULL THEN
1408 x_complete_rec.fund_currency := l_objfundsum_rec.fund_currency;
1409 END IF;
1410
1411 IF p_objfundsum_rec.object_type = Fnd_Api.G_MISS_CHAR THEN
1412 x_complete_rec.object_type := NULL;
1413 END IF;
1414 IF p_objfundsum_rec.object_type IS NULL THEN
1415 x_complete_rec.object_type := l_objfundsum_rec.object_type;
1416 END IF;
1417
1418 IF p_objfundsum_rec.object_id = Fnd_Api.G_MISS_NUM THEN
1419 x_complete_rec.object_id := NULL;
1420 END IF;
1421 IF p_objfundsum_rec.object_id IS NULL THEN
1422 x_complete_rec.object_id := l_objfundsum_rec.object_id;
1423 END IF;
1424
1425 IF p_objfundsum_rec.object_currency = Fnd_Api.G_MISS_CHAR THEN
1426 x_complete_rec.object_currency := NULL;
1427 END IF;
1428 IF p_objfundsum_rec.object_currency IS NULL THEN
1429 x_complete_rec.object_currency := l_objfundsum_rec.object_currency;
1430 END IF;
1431
1432 IF p_objfundsum_rec.reference_object_type = Fnd_Api.G_MISS_CHAR THEN
1433 x_complete_rec.reference_object_type := NULL;
1434 END IF;
1435 IF p_objfundsum_rec.reference_object_type IS NULL THEN
1436 x_complete_rec.reference_object_type := l_objfundsum_rec.reference_object_type;
1437 END IF;
1438
1439 IF p_objfundsum_rec.reference_object_id = Fnd_Api.G_MISS_NUM THEN
1440 x_complete_rec.reference_object_id := NULL;
1441 END IF;
1442 IF p_objfundsum_rec.reference_object_id IS NULL THEN
1443 x_complete_rec.reference_object_id := l_objfundsum_rec.reference_object_id;
1444 END IF;
1445
1446 IF p_objfundsum_rec.source_from_parent = Fnd_Api.G_MISS_CHAR THEN
1447 x_complete_rec.source_from_parent := NULL;
1448 END IF;
1449 IF p_objfundsum_rec.source_from_parent IS NULL THEN
1450 x_complete_rec.source_from_parent := l_objfundsum_rec.source_from_parent;
1451 END IF;
1452
1453 IF p_objfundsum_rec.planned_amt = Fnd_Api.G_MISS_NUM THEN
1454 x_complete_rec.planned_amt := NULL;
1455 END IF;
1456 IF p_objfundsum_rec.planned_amt IS NULL THEN
1457 x_complete_rec.planned_amt := l_objfundsum_rec.planned_amt;
1458 END IF;
1459
1460 IF p_objfundsum_rec.committed_amt = Fnd_Api.G_MISS_NUM THEN
1461 x_complete_rec.committed_amt := NULL;
1462 END IF;
1463 IF p_objfundsum_rec.committed_amt IS NULL THEN
1464 x_complete_rec.committed_amt := l_objfundsum_rec.committed_amt;
1465 END IF;
1466
1467 IF p_objfundsum_rec.recal_committed_amt = Fnd_Api.G_MISS_NUM THEN
1468 x_complete_rec.recal_committed_amt := NULL;
1469 END IF;
1470 IF p_objfundsum_rec.recal_committed_amt IS NULL THEN
1471 x_complete_rec.recal_committed_amt := l_objfundsum_rec.recal_committed_amt;
1472 END IF;
1473
1474 IF p_objfundsum_rec.utilized_amt = Fnd_Api.G_MISS_NUM THEN
1475 x_complete_rec.utilized_amt := NULL;
1476 END IF;
1477 IF p_objfundsum_rec.utilized_amt IS NULL THEN
1478 x_complete_rec.utilized_amt := l_objfundsum_rec.utilized_amt;
1479 END IF;
1480
1481 IF p_objfundsum_rec.earned_amt = Fnd_Api.G_MISS_NUM THEN
1482 x_complete_rec.earned_amt := NULL;
1483 END IF;
1484 IF p_objfundsum_rec.earned_amt IS NULL THEN
1485 x_complete_rec.earned_amt := l_objfundsum_rec.earned_amt;
1486 END IF;
1487
1488 IF p_objfundsum_rec.paid_amt = Fnd_Api.G_MISS_NUM THEN
1489 x_complete_rec.paid_amt := NULL;
1490 END IF;
1491 IF p_objfundsum_rec.paid_amt IS NULL THEN
1492 x_complete_rec.paid_amt := l_objfundsum_rec.paid_amt;
1493 END IF;
1494
1495 IF p_objfundsum_rec.plan_curr_planned_amt = Fnd_Api.G_MISS_NUM THEN
1496 x_complete_rec.plan_curr_planned_amt := NULL;
1497 END IF;
1498 IF p_objfundsum_rec.plan_curr_planned_amt IS NULL THEN
1499 x_complete_rec.plan_curr_planned_amt := l_objfundsum_rec.plan_curr_planned_amt;
1500 END IF;
1501
1502 IF p_objfundsum_rec.plan_curr_committed_amt = Fnd_Api.G_MISS_NUM THEN
1503 x_complete_rec.plan_curr_committed_amt := NULL;
1504 END IF;
1505 IF p_objfundsum_rec.plan_curr_committed_amt IS NULL THEN
1506 x_complete_rec.plan_curr_committed_amt := l_objfundsum_rec.plan_curr_committed_amt;
1507 END IF;
1508
1509 IF p_objfundsum_rec.plan_curr_recal_committed_amt = Fnd_Api.G_MISS_NUM THEN
1510 x_complete_rec.plan_curr_recal_committed_amt := NULL;
1511 END IF;
1512 IF p_objfundsum_rec.plan_curr_recal_committed_amt IS NULL THEN
1513 x_complete_rec.plan_curr_recal_committed_amt := l_objfundsum_rec.plan_curr_recal_committed_amt;
1514 END IF;
1515
1516 IF p_objfundsum_rec.plan_curr_utilized_amt = Fnd_Api.G_MISS_NUM THEN
1517 x_complete_rec.plan_curr_utilized_amt := NULL;
1518 END IF;
1519 IF p_objfundsum_rec.plan_curr_utilized_amt IS NULL THEN
1520 x_complete_rec.plan_curr_utilized_amt := l_objfundsum_rec.plan_curr_utilized_amt;
1521 END IF;
1522
1523 IF p_objfundsum_rec.plan_curr_earned_amt = Fnd_Api.G_MISS_NUM THEN
1524 x_complete_rec.plan_curr_earned_amt := NULL;
1525 END IF;
1526 IF p_objfundsum_rec.plan_curr_earned_amt IS NULL THEN
1527 x_complete_rec.plan_curr_earned_amt := l_objfundsum_rec.plan_curr_earned_amt;
1528 END IF;
1529
1530 IF p_objfundsum_rec.plan_curr_paid_amt = Fnd_Api.G_MISS_NUM THEN
1531 x_complete_rec.plan_curr_paid_amt := NULL;
1532 END IF;
1533 IF p_objfundsum_rec.plan_curr_paid_amt IS NULL THEN
1534 x_complete_rec.plan_curr_paid_amt := l_objfundsum_rec.plan_curr_paid_amt;
1535 END IF;
1536
1537 IF p_objfundsum_rec.univ_curr_planned_amt = Fnd_Api.G_MISS_NUM THEN
1538 x_complete_rec.univ_curr_planned_amt := NULL;
1539 END IF;
1540 IF p_objfundsum_rec.univ_curr_planned_amt IS NULL THEN
1541 x_complete_rec.univ_curr_planned_amt := l_objfundsum_rec.univ_curr_planned_amt;
1542 END IF;
1543
1544 IF p_objfundsum_rec.univ_curr_committed_amt = Fnd_Api.G_MISS_NUM THEN
1545 x_complete_rec.univ_curr_committed_amt := NULL;
1546 END IF;
1547 IF p_objfundsum_rec.univ_curr_committed_amt IS NULL THEN
1548 x_complete_rec.univ_curr_committed_amt := l_objfundsum_rec.univ_curr_committed_amt;
1549 END IF;
1550
1551 IF p_objfundsum_rec.univ_curr_recal_committed_amt = Fnd_Api.G_MISS_NUM THEN
1552 x_complete_rec.univ_curr_recal_committed_amt := NULL;
1553 END IF;
1554 IF p_objfundsum_rec.univ_curr_recal_committed_amt IS NULL THEN
1555 x_complete_rec.univ_curr_recal_committed_amt := l_objfundsum_rec.univ_curr_recal_committed_amt;
1556 END IF;
1557
1558 IF p_objfundsum_rec.univ_curr_utilized_amt = Fnd_Api.G_MISS_NUM THEN
1559 x_complete_rec.univ_curr_utilized_amt := NULL;
1560 END IF;
1561 IF p_objfundsum_rec.univ_curr_utilized_amt IS NULL THEN
1562 x_complete_rec.univ_curr_utilized_amt := l_objfundsum_rec.univ_curr_utilized_amt;
1563 END IF;
1564
1565 IF p_objfundsum_rec.univ_curr_earned_amt = Fnd_Api.G_MISS_NUM THEN
1566 x_complete_rec.univ_curr_earned_amt := NULL;
1567 END IF;
1568 IF p_objfundsum_rec.univ_curr_earned_amt IS NULL THEN
1569 x_complete_rec.univ_curr_earned_amt := l_objfundsum_rec.univ_curr_earned_amt;
1570 END IF;
1571
1572 IF p_objfundsum_rec.univ_curr_paid_amt = Fnd_Api.G_MISS_NUM THEN
1573 x_complete_rec.univ_curr_paid_amt := NULL;
1574 END IF;
1575 IF p_objfundsum_rec.univ_curr_paid_amt IS NULL THEN
1576 x_complete_rec.univ_curr_paid_amt := l_objfundsum_rec.univ_curr_paid_amt;
1577 END IF;
1578
1579
1580 IF p_objfundsum_rec.attribute_category = Fnd_Api.G_MISS_CHAR THEN
1581 x_complete_rec.attribute_category := NULL;
1582 END IF;
1583 IF p_objfundsum_rec.attribute_category IS NULL THEN
1584 x_complete_rec.attribute_category := l_objfundsum_rec.attribute_category;
1585 END IF;
1586
1587 IF p_objfundsum_rec.attribute1 = Fnd_Api.G_MISS_CHAR THEN
1588 x_complete_rec.attribute1 := NULL;
1589 END IF;
1590 IF p_objfundsum_rec.attribute1 IS NULL THEN
1591 x_complete_rec.attribute1 := l_objfundsum_rec.attribute1;
1592 END IF;
1593
1594 IF p_objfundsum_rec.attribute2 = Fnd_Api.G_MISS_CHAR THEN
1595 x_complete_rec.attribute2 := NULL;
1596 END IF;
1597 IF p_objfundsum_rec.attribute2 IS NULL THEN
1598 x_complete_rec.attribute2 := l_objfundsum_rec.attribute2;
1599 END IF;
1600
1601 IF p_objfundsum_rec.attribute3 = Fnd_Api.G_MISS_CHAR THEN
1602 x_complete_rec.attribute3 := NULL;
1603 END IF;
1604 IF p_objfundsum_rec.attribute3 IS NULL THEN
1605 x_complete_rec.attribute3 := l_objfundsum_rec.attribute3;
1606 END IF;
1607
1608 IF p_objfundsum_rec.attribute4 = Fnd_Api.G_MISS_CHAR THEN
1609 x_complete_rec.attribute4 := NULL;
1610 END IF;
1611 IF p_objfundsum_rec.attribute4 IS NULL THEN
1612 x_complete_rec.attribute4 := l_objfundsum_rec.attribute4;
1613 END IF;
1614
1615 IF p_objfundsum_rec.attribute5 = Fnd_Api.G_MISS_CHAR THEN
1616 x_complete_rec.attribute5 := NULL;
1617 END IF;
1618 IF p_objfundsum_rec.attribute5 IS NULL THEN
1619 x_complete_rec.attribute5 := l_objfundsum_rec.attribute5;
1620 END IF;
1621
1622 IF p_objfundsum_rec.attribute6 = Fnd_Api.G_MISS_CHAR THEN
1623 x_complete_rec.attribute6 := NULL;
1624 END IF;
1625 IF p_objfundsum_rec.attribute6 IS NULL THEN
1626 x_complete_rec.attribute6 := l_objfundsum_rec.attribute6;
1627 END IF;
1628
1629 IF p_objfundsum_rec.attribute7 = Fnd_Api.G_MISS_CHAR THEN
1630 x_complete_rec.attribute7 := NULL;
1631 END IF;
1632 IF p_objfundsum_rec.attribute7 IS NULL THEN
1633 x_complete_rec.attribute7 := l_objfundsum_rec.attribute7;
1634 END IF;
1635
1636 IF p_objfundsum_rec.attribute8 = Fnd_Api.G_MISS_CHAR THEN
1637 x_complete_rec.attribute8 := NULL;
1638 END IF;
1639 IF p_objfundsum_rec.attribute8 IS NULL THEN
1640 x_complete_rec.attribute8 := l_objfundsum_rec.attribute8;
1641 END IF;
1642
1643 IF p_objfundsum_rec.attribute9 = Fnd_Api.G_MISS_CHAR THEN
1644 x_complete_rec.attribute9 := NULL;
1645 END IF;
1646 IF p_objfundsum_rec.attribute9 IS NULL THEN
1647 x_complete_rec.attribute9 := l_objfundsum_rec.attribute9;
1648 END IF;
1649
1650 IF p_objfundsum_rec.attribute10 = Fnd_Api.G_MISS_CHAR THEN
1651 x_complete_rec.attribute10 := NULL;
1652 END IF;
1653 IF p_objfundsum_rec.attribute10 IS NULL THEN
1654 x_complete_rec.attribute10 := l_objfundsum_rec.attribute10;
1655 END IF;
1656
1657 IF p_objfundsum_rec.attribute11 = Fnd_Api.G_MISS_CHAR THEN
1658 x_complete_rec.attribute11 := NULL;
1659 END IF;
1660 IF p_objfundsum_rec.attribute11 IS NULL THEN
1661 x_complete_rec.attribute11 := l_objfundsum_rec.attribute11;
1662 END IF;
1663
1664 IF p_objfundsum_rec.attribute12 = Fnd_Api.G_MISS_CHAR THEN
1665 x_complete_rec.attribute12 := NULL;
1666 END IF;
1667 IF p_objfundsum_rec.attribute12 IS NULL THEN
1668 x_complete_rec.attribute12 := l_objfundsum_rec.attribute12;
1669 END IF;
1670
1671 IF p_objfundsum_rec.attribute13 = Fnd_Api.G_MISS_CHAR THEN
1672 x_complete_rec.attribute13 := NULL;
1673 END IF;
1674 IF p_objfundsum_rec.attribute13 IS NULL THEN
1675 x_complete_rec.attribute13 := l_objfundsum_rec.attribute13;
1676 END IF;
1677
1678 IF p_objfundsum_rec.attribute14 = Fnd_Api.G_MISS_CHAR THEN
1679 x_complete_rec.attribute14 := NULL;
1680 END IF;
1681 IF p_objfundsum_rec.attribute14 IS NULL THEN
1682 x_complete_rec.attribute14 := l_objfundsum_rec.attribute14;
1683 END IF;
1684
1685 IF p_objfundsum_rec.attribute15 = Fnd_Api.G_MISS_CHAR THEN
1686 x_complete_rec.attribute15 := NULL;
1687 END IF;
1688 IF p_objfundsum_rec.attribute15 IS NULL THEN
1689 x_complete_rec.attribute15 := l_objfundsum_rec.attribute15;
1690 END IF;
1691
1692 END Complete_objfundsum_Rec ;
1693
1694
1695 END Ozf_objfundsum_Pvt;