[Home] [Help]
PACKAGE BODY: APPS.OZF_FUND_RECONCILE_PVT
Source
1 PACKAGE BODY OZF_FUND_RECONCILE_PVT AS
2 /*$Header: ozfvrecb.pls 120.25.12020000.2 2013/03/01 06:40:26 nepanda ship $*/
3
4 -----------------------------------------------------------
5 -- PACKAGE
6 -- ozf_fund_reconcile_pvt
7 --
8 -- PROCEDURES
9 -- Release-Committed_Amount
10 -- Release-Committed_fund_conc
11 -- Update_lumpsum_amount
12 -- HISTORY
13 -- 10/14/2001 Feliu Create.
14 -- 29/11/2001 Feliu Changed some query for recalculating committed.
15 -- 12/12/2001 Feliu Fixed recalculating committed bug 2128015.
16 -- 12/17/2001 MPande Fixed Lumsum AMount conc program exception handling
17 -- 8/19/2002 MPande Changed Status of objects for reconciliation , donot set adjsutment type_id to null
18 -- 10/28/2002 feliu Change for 11.5.9
19 -- 10/28/2002 feliu changed flow for for recalculating committed.
20 -- moved: recal_comm_fund_conc,reconcile_budget_line from ozf_fund_adjustment_pvt.
21 -- release_fund_conc to replace release_committed_fund_conc.
22 -- post_utilized_budget_conc to replace update_lumpsum_amount_conc.
23 -- 12/05/2002 feliu Change cursor query for release_fund_conc.
24 -- 01/05/2004 feliu add softfund and special pricing for release fund conc.
25 -- 06/02/2004 Ribha Bug fix for 3654855
26 -- 21/07/2004 Ribha Changed recal_comm_fund_conc to commit separately for each offer.
27 -- 03/31/2005 kdass fixed bug 4261335
28 -- 04/12/2005 kdass fixed bug 4285094
29 -- 06/07/2005 Ribha Performance Fix.
30 -- 07/27/2005 Ribha Replace ozf_object_checkbook_v by ozf_object_fund_summmary
31 -- 02/27/2006 asylvia copy business unit and other fields to next period budget
32 -- 03/31/2006 kdass fixed bug 5101720 - query fund_request_curr_code if offer has no currency defined
33 -- 04/25/2006 kdass fixed bug 5177593
34 -- 08/04/2008 nirprasa fixed bug 7030415
35 -- 10/08/2008 nirprasa fixed bug 7425189
36 -- 10/08/2008 nirprasa fixed rounding issues, due to currency conversion.
37 -- Since, committed and utilized amounts are already stored in object currency
38 -- in ozf_object_fund_summary table, use the stored values instead of conversion.
39 -- fix is done for bug 7505085.
40 -- 2/17/2010 nepanda Bug 9131648 : multi currency changes
41 -- 28-OCT-2011 APYADAV fix for FP 13252796 Bug 13112685 OFFER RECONCIALTION NOT POPULATING OBJECT_ID,OBJECT_TYPE IN OZF_FUNDS_UTILIZED_A
42 -- 7/10/2012 nepanda Bug 14062938 - budget reconciliation failing for multi currency with error
43 -- Note
44 ------------------------------------------------------------
45
46 g_pkg_name CONSTANT VARCHAR2 (30) := 'ozf_fund_reconcile_pvt';
47 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
48 g_bulk_limit CONSTANT NUMBER := 5000;
49
50 /* =========================================================
51 --tbl_type to hold the object
52 --This is a private rec type to be used by this API only
53 */
54 /* =========================================================
55 --tbl_type to hold the object
56 --This is a private rec type to be used by this API only
57 */
58 TYPE object_rec_type IS RECORD (
59 object_id NUMBER
60 ,object_curr VARCHAR2 (30));
61
62 /* =========================================================
63 --tbl_type to hold the amount
64 --This is a private rec type to be used by this API only
65 ============================================================*/
66
67 TYPE object_tbl_type IS TABLE OF object_rec_type
68 INDEX BY BINARY_INTEGER;
69
70 /* =========================================================
71 --tbl_type to hold the object
72 --This is a private rec type to be used by this API only
73 */
74 TYPE forecast_rec_type IS RECORD (
75 start_date DATE
76 ,end_date DATE
77 ,forecast_value NUMBER);
78
79 /* =========================================================
80 --tbl_type to hold the amount
81 --This is a private rec type to be used by this API only
82 ============================================================*/
83
84 TYPE forecast_tbl_type IS TABLE OF forecast_rec_type
85 INDEX BY BINARY_INTEGER;
86 ----------------------------------------------------------------------
87 -- PROCEDURE
88 -- Release-Committed_fund_conc
89 --
90 -- PURPOSE
91 --
92 -- PARAMETERS
93 -- p_object_type IN VARCHAR2
94 -- p_object_status IN VARCHAR2 :=FND_API.G_MISS_CHAR
95 -- p_object_code IN VARCHAR2 :=fnd_api.G_MISS_CHAR
96 -- p_object_end_date IN DATE := FND_API.G_MISS_DATE
97 -- x_errbuf OUT VARCHAR2 STANDARD OUT PARAMETER
98 -- x_retcode OUT NUMBER STANDARD OUT PARAMETER
99 -- NOTES
100 -- This API will release the committed amounts for all offers that are closed or inactivated
101 -- Right now we only release budget committment from a offer
102 -- HISTORY
103 -- 02/05/2001 Mumu Pande Create.
104 -- 10/14/2002 feng Modified.
105 -- 12/04/2002 feng Modified cursor query.
106 ----------------------------------------------------------------------
107
108
109 PROCEDURE release_fund_conc (
110 x_errbuf OUT NOCOPY VARCHAR2
111 ,x_retcode OUT NOCOPY NUMBER
112 ,p_object_type IN VARCHAR2
113 ,p_object_status IN VARCHAR2 :=null
114 ,p_object_code IN VARCHAR2 :=null
115 ,p_object_end_date IN VARCHAR2 := null
116 ,p_util_paid IN VARCHAR2 := null
117 ) IS
118 l_grace_date DATE;
119 l_api_version CONSTANT NUMBER := 1.0;
120 l_api_name CONSTANT VARCHAR2 (30) := 'Release_fund_conc';
121 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
122 || '.'
123 || l_api_name;
124 l_return_status VARCHAR2 (1);
125 l_msg_data VARCHAR2 (2000);
126 l_msg_count NUMBER;
127 l_object_curr VARCHAR2 (30);
128 l_object_id NUMBER;
129 l_object_tbl object_tbl_type;
130 i NUMBER := 1;
131 l_has_Grace_date VARCHAR2(1) := NVL(fnd_profile.VALUE('OZF_HAS_GRACE_PERIOD'), 'N');
132 l_object_status VARCHAR2(30);
133 l_util_paid VARCHAR2(1) := NVL(p_util_paid, 'N');
134 l_object_end_date DATE;
135 l_object_type VARCHAR2 (30) := p_object_type ;
136
137 CURSOR c_offer (p_grace_date IN DATE,
138 p_status IN VARCHAR2,
139 p_code IN VARCHAR2,
140 p_end_date IN DATE) IS
141 SELECT qp_list_header_id object_id
142 ,nvl(transaction_currency_code,fund_request_curr_code) object_curr
143 --, qp.end_date_active object_date
144 FROM ozf_offers off,qp_list_headers_b qp
145 WHERE off.qp_list_header_id = qp.list_header_id
146 AND NVL (qp.end_date_active, p_grace_date) <= p_grace_date
147 AND status_code IN (p_status)
148 --AND offr.status_code IN ('CLOSED','COMPLETED','TERMINATED') -- inactive offers;
149 AND NVL (off.account_closed_flag, 'N') = 'N'
150 AND off.offer_code = NVL(p_code,off.offer_code)
151 --AND qp.end_date_active <= NVL(p_end_date, qp.end_date_active);
152 AND NVL(qp.end_date_active,SYSDATE) <= NVL(p_end_date, NVL(qp.end_date_active,SYSDATE));
153
154 CURSOR c_campaign (p_grace_date IN DATE,
155 p_status IN VARCHAR2,
156 p_code IN VARCHAR2,
157 p_end_date IN DATE) IS
158 SELECT ozf.campaign_id object_id
159 ,ozf.transaction_currency_code object_curr
160 --, ozf.actual_execution_end_date
161 FROM ams_campaigns_all_b ozf
162 WHERE NVL (ozf.actual_exec_end_date, p_grace_date) <= p_grace_date
163 -- changed to archived 08/20/2001 mpande
164 --AND ozf.status_code IN ('CLOSED','COMPLETED','CANCELLED','ARCHIVED') -- inactive camps;
165 AND ozf.status_code IN (p_status)
166 AND ozf.source_code = NVL(p_code, ozf.source_code)
167 AND NVL(ozf.actual_exec_end_date,SYSDATE) <= NVL(p_end_date, NVL(ozf.actual_exec_end_date,SYSDATE))
168 AND NVL (ozf.accounts_closed_flag, 'N') = 'N';
169
170 CURSOR c_eheader (p_grace_date IN DATE,
171 p_status IN VARCHAR2,
172 p_code IN VARCHAR2,
173 p_end_date IN DATE) IS
174 SELECT ozf.event_header_id object_id
175 ,ozf.currency_code_tc object_curr
176 FROM ams_event_headers_all_b ozf
177 WHERE NVL (ozf.active_to_date, p_grace_date) <= p_grace_date
178 -- changed to archived 08/20/2001 mpande
179 --AND ozf.system_status_code IN ('CLOSED','CANCELLED','ARCHIVED') -- inactive;
180 AND ozf.system_status_code IN (p_status)
181 AND ozf.source_code = NVL(p_code, ozf.source_code)
182 --AND ozf.active_to_date <= NVL(p_end_date, ozf.active_to_date)
183 AND NVL(ozf.active_to_date,SYSDATE) <= NVL(p_end_date, NVL(ozf.active_to_date,SYSDATE))
184 AND NVL (ozf.accounts_closed_flag, 'N') = 'N';
185
186 CURSOR c_esch (p_grace_date IN DATE,
187 p_status IN VARCHAR2,
188 p_code IN VARCHAR2,
189 p_end_date IN DATE) IS
190 SELECT ozf.event_offer_id object_id
191 ,ozf.currency_code_tc object_curr
192 --, ozf.event_end_date
193 FROM ams_event_offers_all_b ozf
194 WHERE NVL (ozf.event_end_date, p_grace_date) <= p_grace_date
195 -- changed to archived 08/20/2001 mpande
196 --AND ozf.system_status_code IN ('CLOSED','CANCELLED','ARCHIVED') -- inactive ;
197 AND ozf.system_status_code IN (p_status)
198 AND ozf.source_code = NVL(p_code, ozf.source_code)
199 --AND ozf.event_end_date <= NVL(p_end_date, ozf.event_end_date)
200 AND NVL(ozf.event_end_date,SYSDATE) <= NVL(p_end_date, NVL(ozf.event_end_date,SYSDATE))
201 AND NVL (ozf.accounts_closed_flag, 'N') = 'N'
202 AND ozf.event_object_type = 'EVEO';
203
204 CURSOR c_eoffer (p_grace_date IN DATE,
205 p_status IN VARCHAR2,
206 p_code IN VARCHAR2,
207 p_end_date IN DATE) IS
208 SELECT ozf.event_offer_id object_id
209 ,ozf.currency_code_tc object_curr
210 --, ozf.event_end_date
211 FROM ams_event_offers_all_b ozf
212 WHERE NVL (ozf.event_end_date, p_grace_date) <= p_grace_date
213 -- changed to archived 08/20/2001 mpande
214 --AND ozf.system_status_code IN ('CLOSED','CANCELLED','ARCHIVED') -- inactive ;
215 AND ozf.system_status_code IN (p_status)
216 AND ozf.source_code = NVL(p_code,ozf.source_code)
217 --AND ozf.event_end_date <= NVL(p_end_date, ozf.event_end_date)
218 AND NVL(ozf.event_end_date,SYSDATE) <= NVL(p_end_date, NVL(ozf.event_end_date,SYSDATE))
219 AND NVL (ozf.accounts_closed_flag, 'N') = 'N'
220 AND ozf.event_object_type = 'EONE';
221
222
223 -- Ribha: remove decodes from join for performance fix.
224 CURSOR c_deliverable (p_grace_date IN DATE,
225 p_status IN VARCHAR2,
226 p_code IN VARCHAR2,
227 p_end_date IN DATE) IS
228 SELECT ozf.deliverable_id object_id
229 ,ozf.transaction_currency_code object_curr
230 FROM ams_deliverables_vl ozf
231 WHERE
232 NVL(ozf.actual_complete_date, p_grace_date) <= p_grace_date
233 AND ozf.status_code IN (p_status)
234 AND ozf.deliverable_name = NVL(p_code, ozf.deliverable_name)
235 --AND ozf.actual_complete_date <= NVL(p_end_date, ozf.actual_complete_date)
236 AND NVL(ozf.actual_complete_date,SYSDATE) <= NVL(p_end_date, NVL(ozf.actual_complete_date,SYSDATE))
237 AND NVL(ozf.accounts_closed_flag,'N') = 'N';
238
239 CURSOR c_campaign_schl (p_grace_date IN DATE,
240 p_status IN VARCHAR2,
241 p_code IN VARCHAR2,
242 p_end_date IN DATE) IS
243 SELECT ozf.schedule_id object_id
244 ,ozf.transaction_currency_code object_curr
245 -- , ozf.end_date_time
246 FROM ams_campaign_schedules_vl ozf
247 WHERE NVL (ozf.end_date_time, p_grace_date) <= p_grace_date
248 -- changed to archived 08/20/2001 mpande
249 --AND ozf.status_code IN ('CLOSED','COMPLETED','CANCELLED','ARCHIVED') -- inactive ;
250 AND ozf.status_code IN (p_status)
251 AND ozf.source_code = NVL(p_code, ozf.source_code)
252 --AND ozf.end_date_time <= NVL(p_end_date,ozf.end_date_time)
253 AND NVL(ozf.end_date_time,SYSDATE) <= NVL(p_end_date, NVL(ozf.end_date_time,SYSDATE))
254 AND NVL (ozf.accounts_closed_flag, 'N') = 'N';
255
256 CURSOR c_sf_request (p_grace_date IN DATE,
257 p_status IN VARCHAR2,
258 p_code IN VARCHAR2,
259 p_end_date IN DATE) IS
260 SELECT qp_list_header_id object_id
261 ,nvl(transaction_currency_code,fund_request_curr_code) object_curr
262 FROM ozf_offers off,ozf_request_headers_all_b req
263 WHERE off.qp_list_header_id = req.offer_id
264 AND req.request_class ='SOFT_FUND'
265 AND NVL (req.approved_date, p_grace_date) <= p_grace_date
266 AND req.status_code ='APPROVED'
267 AND req.request_number = NVL(p_code, req.request_number)
268 --AND req.end_date <= NVL(p_end_date,req.end_date);
269 AND NVL(req.end_date,SYSDATE) <= NVL(p_end_date, NVL(req.end_date,SYSDATE));
270
271 CURSOR c_sp_request (p_grace_date IN DATE,
272 p_status IN VARCHAR2,
273 p_code IN VARCHAR2,
274 p_end_date IN DATE) IS
275 SELECT qp_list_header_id object_id
276 ,nvl(transaction_currency_code,fund_request_curr_code) object_curr
277 FROM ozf_offers off,ozf_request_headers_all_b req
278 WHERE off.qp_list_header_id = req.offer_id
279 AND req.request_class ='SPECIAL_PRICE'
280 AND NVL (req.end_date, p_grace_date) <= p_grace_date
281 AND req.status_code ='APPROVED'
282 AND req.request_number = NVL(p_code, req.request_number)
283 -- AND req.end_date <= NVL(p_end_date,req.end_date);
284 AND NVL(req.end_date,SYSDATE) <= NVL(p_end_date, NVL(req.end_date,SYSDATE));
285 BEGIN
286
287 SAVEPOINT release_fund_conc;
288
289 -- get grace date from profile
290 IF l_has_Grace_date = 'N' THEN
291 l_grace_date := SYSDATE;
292 ELSE
293 IF p_object_type = 'SOFT_FUND' THEN
294 l_grace_date := TRUNC(SYSDATE)
295 - NVL (to_number(fnd_profile.VALUE ('OZF_SF_GRACE_DAYS')), 0); --bug fix for 3654855. Added to_number
296 l_object_type := 'OFFR';
297 ELSIF p_object_type = 'SPECIAL_PRICE' THEN
298 l_grace_date := TRUNC(SYSDATE)
299 - NVL (to_number(fnd_profile.VALUE ('OZF_SP_GRACE_DAYS')), 0);
300 l_object_type := 'OFFR';
301 ELSE
302 l_grace_date := TRUNC(SYSDATE)
303 - NVL (to_number(fnd_profile.VALUE ('OZF_BUDGET_ADJ_GRACE_PERIOD')), 0);
304 END IF;
305
306 END IF;
307
308 IF p_object_end_date IS NOT NULL THEN
309 l_object_end_date := FND_DATE.CANONICAL_TO_DATE(p_object_end_date);
310 END IF;
311
312 IF G_DEBUG THEN
313 ozf_utility_pvt.debug_message ( l_full_name
314 || ': '
315 || l_grace_date);
316 END IF;
317
318
319 IF G_DEBUG THEN
320 ozf_utility_pvt.debug_message ( 'object type: ' || p_object_type
321 || 'object status: ' ||p_object_status
322 || 'object code: ' || p_object_code
323 || 'end date: ' || l_object_end_date
324 || 'recon paid: ' || l_util_paid);
325 END IF;
326
327 -- Campaign
328 IF p_object_type = 'CAMP' THEN
329 IF p_object_status = FND_API.G_MISS_CHAR THEN
330 l_object_status := 'CLOSED,COMPLETED,CANCELLED,ARCHIVED';
331 ELSE
332 l_object_status := p_object_status;
333 END IF;
334 OPEN c_campaign (l_grace_date,l_object_status,p_object_code,l_object_end_date);
335
336 LOOP
337 FETCH c_campaign INTO l_object_tbl (i).object_id, l_object_tbl (i).object_curr;
338 EXIT WHEN c_campaign%NOTFOUND;
339 i := i
340 + 1;
341 END LOOP;
342
343 CLOSE c_campaign;
344 -- Campaign Schdules
345 ELSIF p_object_type = 'CSCH' THEN
346 IF p_object_status = FND_API.G_MISS_CHAR THEN
347 l_object_status := 'CLOSED,COMPLETED,CANCELLED,ARCHIVED';
348 ELSE
349 l_object_status := p_object_status;
350 END IF;
351
352 OPEN c_campaign_schl (l_grace_date,l_object_status,p_object_code,l_object_end_date);
353
354 LOOP
355 FETCH c_campaign_schl INTO l_object_tbl (i).object_id, l_object_tbl (i).object_curr;
356 EXIT WHEN c_campaign_schl%NOTFOUND;
357 i := i
358 + 1;
359 END LOOP;
360
361 CLOSE c_campaign_schl;
362 -- Event Header/Rollup Event
363 ELSIF p_object_type = 'EVEH' THEN
364 IF p_object_status = FND_API.G_MISS_CHAR THEN
365 l_object_status := 'CLOSED,CANCELLED,ARCHIVED,COMPLETED';
366 ELSE
367 l_object_status := p_object_status;
368 END IF;
369
370 OPEN c_eheader (l_grace_date,l_object_status,p_object_code,l_object_end_date);
371
372 LOOP
373 FETCH c_eheader INTO l_object_tbl (i).object_id, l_object_tbl (i).object_curr;
374 EXIT WHEN c_eheader%NOTFOUND;
375 i := i
376 + 1;
377 END LOOP;
378
379 CLOSE c_eheader;
380 -- Event one Offer
381 ELSIF p_object_type = 'EONE' THEN
382 IF p_object_status = FND_API.G_MISS_CHAR THEN
383 l_object_status := 'CLOSED,CANCELLED,ARCHIVED,COMPLETED';
384 ELSE
385 l_object_status := p_object_status;
386 END IF;
387
388 OPEN c_eoffer (l_grace_date,l_object_status,p_object_code,l_object_end_date);
389
390 LOOP
391 FETCH c_eoffer INTO l_object_tbl (i).object_id, l_object_tbl (i).object_curr;
392 EXIT WHEN c_eoffer%NOTFOUND;
393 i := i
394 + 1;
395 END LOOP;
396
397 CLOSE c_eoffer;
398 ELSIF p_object_type = 'EVEO' THEN --event schedule
399 IF p_object_status = FND_API.G_MISS_CHAR THEN
400 l_object_status := 'CLOSED,CANCELLED,ARCHIVED,COMPLETED';
401 ELSE
402 l_object_status := p_object_status;
403 END IF;
404
405 OPEN c_esch (l_grace_date,l_object_status,p_object_code,l_object_end_date);
406
407 LOOP
408 FETCH c_esch INTO l_object_tbl (i).object_id, l_object_tbl (i).object_curr;
409 EXIT WHEN c_esch%NOTFOUND;
410 i := i
411 + 1;
412 END LOOP;
413
414 CLOSE c_esch;
415
416 -- Deliverable
417 ELSIF p_object_type = 'DELV' THEN
418 IF p_object_status = FND_API.G_MISS_CHAR THEN
419 l_object_status := 'ARCHIVED';
420 ELSE
421 l_object_status := p_object_status;
422 END IF;
423
424 OPEN c_deliverable (l_grace_date,l_object_status,p_object_code,l_object_end_date);
425
426 LOOP
427 FETCH c_deliverable INTO l_object_tbl (i).object_id, l_object_tbl (i).object_curr;
428 EXIT WHEN c_deliverable%NOTFOUND;
429 i := i
430 + 1;
431 END LOOP;
432
433 CLOSE c_deliverable;
434 ELSIF p_object_type = 'OFFR' THEN
435 IF p_object_status = FND_API.G_MISS_CHAR THEN
436 l_object_status := 'CLOSED,COMPLETED,TERMINATED';
437 ELSE
438 l_object_status := p_object_status;
439 END IF;
440
441
442 OPEN c_offer (l_grace_date,l_object_status,p_object_code,l_object_end_date);
443
444 LOOP
445 FETCH c_offer INTO l_object_tbl (i).object_id, l_object_tbl (i).object_curr;
446 EXIT WHEN c_offer%NOTFOUND;
447 i := i
448 + 1;
449 END LOOP;
450
451 CLOSE c_offer;
452 ELSIF p_object_type = 'SOFT_FUND' THEN -- for softfund, add by feliu on 01/11/04
453 IF p_object_status = FND_API.G_MISS_CHAR OR p_object_status is NULL THEN
454 l_object_status := 'APPROVED';
455 ELSE
456 l_object_status := p_object_status;
457 END IF;
458
459 OPEN c_sf_request (l_grace_date,l_object_status,p_object_code,l_object_end_date);
460
461 LOOP
462 FETCH c_sf_request INTO l_object_tbl (i).object_id, l_object_tbl (i).object_curr;
463 EXIT WHEN c_sf_request%NOTFOUND;
464 i := i
465 + 1;
466 END LOOP;
467
468 CLOSE c_sf_request;
469 ELSIF p_object_type = 'SPECIAL_PRICE' THEN -- for special pricing, add by feliu on 01/11/04
470 IF p_object_status = FND_API.G_MISS_CHAR THEN
471 l_object_status := 'APPROVED';
472 ELSE
473 l_object_status := p_object_status;
474 END IF;
475
476 OPEN c_sp_request (l_grace_date,l_object_status,p_object_code,l_object_end_date);
477
478 LOOP
479 FETCH c_sp_request INTO l_object_tbl (i).object_id, l_object_tbl (i).object_curr;
480 EXIT WHEN c_sp_request%NOTFOUND;
481 i := i
482 + 1;
483 END LOOP;
484
485 CLOSE c_sp_request;
486
487 END IF;
488
489 IF G_DEBUG THEN
490 ozf_utility_pvt.debug_message ('table count: ' || l_object_tbl.count);
491 END IF;
492
493 FOR k IN NVL (l_object_tbl.FIRST, 1) .. NVL (l_object_tbl.LAST, 0)
494 LOOP
495 SAVEPOINT release_fund_conc;
496 IF G_DEBUG THEN
497 ozf_utility_pvt.debug_message ( l_full_name
498 || ': start loop');
499 END IF;
500
501 -- call release fund for the respective offers
502 reconcile_line(
503 p_budget_used_by_id=> l_object_tbl (k).object_id
504 ,p_budget_used_by_type=> l_object_type
505 ,p_object_currency=> l_object_tbl (k).object_curr
506 ,p_from_paid => l_util_paid
507 ,p_api_version=> l_api_version
508 ,x_return_status=> l_return_status
509 ,x_msg_count=> l_msg_count
510 ,x_msg_data=> l_msg_data
511 );
512
513 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
514 ROLLBACK TO release_fund_conc;
515 -- x_retcode := 1;
516 --x_errbuf := l_msg_data;
517 ozf_utility_pvt.write_conc_log('ERROR: Could not perform reconcile for Object: '||l_object_type||' : '||l_object_tbl (k).object_id);
518
519 /* fnd_msg_pub.count_and_get (
520 p_encoded=> fnd_api.g_false
521 ,p_count=> x_msg_count
522 ,p_data=> x_msg_data
523 );*/
524
525 END IF;
526
527 IF l_return_status = fnd_api.g_ret_sts_success THEN
528 IF p_object_type = 'CAMP' THEN
529 UPDATE ams_campaigns_all_b
530 SET accounts_closed_flag = 'Y'
531 WHERE campaign_id = l_object_tbl (k).object_id;
532 -- Campaign Schdules
533 ELSIF p_object_type = 'CSCH' THEN
534 UPDATE ams_campaign_schedules_b
535 SET accounts_closed_flag = 'Y'
536 WHERE schedule_id = l_object_tbl (k).object_id;
537 -- Event Header/Rollup Event
538 ELSIF p_object_type = 'EVEH' THEN
539 UPDATE ams_event_headers_all_b
540 SET accounts_closed_flag = 'Y'
541 WHERE event_header_id = l_object_tbl (k).object_id;
542 -- Event Offer/Execution Event
543 ELSIF p_object_type = 'EVEO' OR p_object_type = 'EONE' THEN
544 UPDATE ams_event_offers_all_b
545 SET accounts_closed_flag = 'Y'
546 WHERE event_offer_id = l_object_tbl (k).object_id;
547 -- Deliverable
548 ELSIF p_object_type = 'DELV' THEN
549 UPDATE ams_campaigns_all_b
550 SET accounts_closed_flag = 'Y'
551 WHERE campaign_id = l_object_tbl (k).object_id;
552 -- we do not need to check this for fund
553 ELSIF p_object_type = 'OFFR' THEN
554 UPDATE ozf_offers
555 SET account_closed_flag = 'Y'
556 WHERE qp_list_header_id = l_object_tbl (k).object_id;
557 ELSIF p_object_type = 'SOFT_FUND' THEN
558 UPDATE ozf_offers
559 SET account_closed_flag = 'Y'
560 WHERE qp_list_header_id = l_object_tbl (k).object_id;
561 UPDATE ozf_request_headers_all_b
562 SET status_code = 'CLOSED'
563 WHERE offer_id = l_object_tbl (k).object_id;
564 ELSIF p_object_type = 'SPECIAL_PRICE' THEN
565 UPDATE ozf_offers
566 SET account_closed_flag = 'Y'
567 WHERE qp_list_header_id = l_object_tbl (k).object_id;
568 UPDATE ozf_request_headers_all_b
569 SET status_code = 'CLOSED'
570 WHERE offer_id = l_object_tbl (k).object_id;
571 END IF;
572
573 COMMIT;
574 x_retcode := 0;
575 END IF;
576
577
578 END LOOP;
579
580 ozf_utility_pvt.write_conc_log (l_msg_data);
581 /* fnd_msg_pub.count_and_get (
582 p_encoded=> fnd_api.g_false
583 ,p_count=> x_msg_count
584 ,p_data=> x_msg_data
585 );
586 EXCEPTION
587
588 WHEN OTHERS THEN
589 -ROLLBACK TO release_fund_conc;
590 x_return_status := fnd_api.g_ret_sts_unexp_error;
591
592 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
593 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
594 END IF;
595
596 fnd_msg_pub.count_and_get (
597 p_count=> x_msg_count
598 ,p_data=> x_msg_data
599 ,p_encoded=> fnd_api.g_false
600 );
601 */
602 EXCEPTION
603 WHEN OTHERS THEN
604 ROLLBACK TO release_fund_conc;
605 x_retcode := 1;
606 x_errbuf := l_msg_data;
607 ozf_utility_pvt.write_conc_log (x_errbuf);
608
609 END release_fund_conc;
610
611 /*****************************************************************************************/
612 -- Start of Comments
613 -- NAME
614 -- Reconcile_budget_line
615 -- PURPOSE
616 -- This API is called from the java layer from the reconcile button on budget_sourcing screen
617 -- It releases all th ebudget that was requested from a fund to the respective fund by creating transfer records
618 -- and negative committment.
619 -- HISTORY
620 -- 04/30/2001 mpande CREATED
621 -- 08/24/2005 feliu modified based on new table ozf_object_fund_summary for R12.
622 ---------------------------------------------------------------------
623
624 PROCEDURE reconcile_budget_line (
625 p_budget_used_by_id IN NUMBER
626 ,p_budget_used_by_type IN VARCHAR2
627 ,p_object_currency IN VARCHAR2
628 ,p_api_version IN NUMBER
629 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
630 ,p_commit IN VARCHAR2 := fnd_api.g_false
631 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
632 ,x_return_status OUT NOCOPY VARCHAR2
633 ,x_msg_count OUT NOCOPY NUMBER
634 ,x_msg_data OUT NOCOPY VARCHAR2
635 ) IS
636
637 -- for these objects sourced from parent.
638 CURSOR c_parent_source_obj IS
639 SELECT SUM (amount) total_amount
640 FROM (SELECT --- request amount
641 NVL (SUM (a1.approved_amount), 0) amount
642 FROM ozf_act_budgets a1
643 WHERE a1.act_budget_used_by_id = p_budget_used_by_id
644 AND a1.arc_act_budget_used_by = p_budget_used_by_type
645 AND a1.status_code = 'APPROVED'
646 AND a1.transfer_type ='REQUEST'
647 AND parent_act_budget_id is null
648 UNION -- transfer and utilized amount
649 SELECT -NVL (SUM (a2.approved_original_amount), 0) amount
650 FROM ozf_act_budgets a2
651 WHERE a2.budget_source_id = p_budget_used_by_id
652 AND a2.budget_source_type = p_budget_used_by_type
653 AND a2.status_code = 'APPROVED'
654 AND a2.transfer_type <>'REQUEST'
655 AND parent_act_budget_id is null
656 );
657
658 -- used to get parent_id, parent currency, and source from parent flag.
659 CURSOR c_offer_data(p_offer_id IN NUMBER) IS
660 SELECT offr.budget_source_id,NVL(offr.source_from_parent,'N'),camp.transaction_currency_code
661 FROM ozf_offers offr,ams_campaigns_all_b camp
662 WHERE offr.qp_list_header_id = p_offer_id
663 AND camp.campaign_id = offr.budget_source_id;
664
665 CURSOR c_schedule_data(p_schedule_id IN NUMBER) IS
666 SELECT sch.campaign_id,NVL(source_from_parent,'N'),camp.transaction_currency_code
667 FROM ams_campaign_schedules_b sch,ams_campaigns_all_b camp
668 WHERE sch.schedule_id = p_schedule_id
669 AND camp.campaign_id = sch.campaign_id;
670
671 CURSOR c_event_sch_data(pschedule_id IN NUMBER) IS
672 SELECT sch.event_header_id, NVL(sch.source_from_parent,'N'),evt.currency_code_tc
673 FROM ams_event_offers_all_b sch,ams_event_headers_all_b evt
674 WHERE sch.event_offer_id = pschedule_id
675 AND sch.event_header_id = evt.event_header_id;
676
677 -- for sourcing from budgets.
678 --added plan_curr_total_amount for bug 7505085 and rounding issues found in bug 7425189
679 CURSOR c_parent_source_fund IS
680 SELECT fund_id parent_source_id
681 ,fund_currency parent_curr
682 ,NVL(committed_amt,0)-NVL(utilized_amt,0) total_amount
683 ,NVL(plan_curr_committed_amt,0)-NVL(plan_curr_utilized_amt,0) plan_curr_total_amount
684 FROM ozf_object_fund_summary
685 WHERE object_id =p_budget_used_by_id
686 AND object_type = p_budget_used_by_type;
687
688 CURSOR c_offr_reusable IS
689 SELECT count(*)
690 FROM ozf_offers off
691 WHERE off.qp_list_header_id = p_budget_used_by_id
692 AND off.reusable = 'Y' ;
693
694 CURSOR c_offr_source IS
695 SELECT count(activity_offer_id)
696 FROM ozf_offers off,ozf_act_offers act
697 WHERE off.qp_list_header_id = p_budget_used_by_id
698 AND off.reusable = 'N'
699 AND off.qp_list_header_id = act.qp_list_header_id ;
700
701 --nirprasa for bug 7425189, use request approval date to get exchnage rate
702 CURSOR c_exchange_rate_date(p_fund_id IN NUMBER) IS
703 SELECT approval_date
704 FROM ozf_act_budgets a1
705 WHERE a1.act_budget_used_by_id = p_budget_used_by_id
706 AND a1.arc_act_budget_used_by = p_budget_used_by_type
707 AND a1.status_code = 'APPROVED'
708 AND a1.transfer_type = 'REQUEST'
709 AND a1.budget_source_id = p_fund_id;
710
711 l_parent_source_rec c_parent_source_fund%ROWTYPE;
712 l_api_version NUMBER := 1.0;
713 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
714 l_api_name VARCHAR2 (60) := 'reconcile_budget_line';
715 l_act_budget_id NUMBER;
716 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
717 l_source_from_par_flag VARCHAR2 (1);
718 l_fund_source VARCHAR2 (1) := 'T';
719 --l_parent_source_rec_obj c_parent_source_obj%ROWTYPE;
720 l_dummy NUMBER;
721 l_parent_id NUMBER;
722 l_parent_type VARCHAR2 (30);
723 l_parent_amount NUMBER;
724 l_currency_code VARCHAR2 (30);
725
726 BEGIN
727 SAVEPOINT reconcile_budget_line;
728 x_return_status := fnd_api.g_ret_sts_success;
729 IF G_DEBUG THEN
730 ozf_utility_pvt.debug_message (': before parent source cursor ');
731 END IF;
732
733 IF fnd_api.to_boolean (p_init_msg_list) THEN
734 fnd_msg_pub.initialize;
735 END IF;
736
737 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
738 RAISE fnd_api.g_exc_unexpected_error;
739 END IF;
740
741 IF p_budget_used_by_type = 'OFFR' THEN
742 OPEN c_offer_data(p_budget_used_by_id);
743 FETCH c_offer_data INTO l_parent_id,l_source_from_par_flag,l_currency_code;
744 CLOSE c_offer_data;
745 l_parent_type := 'CAMP';
746 ELSIF p_budget_used_by_type = 'CSCH' THEN
747 OPEN c_schedule_data(p_budget_used_by_id);
748 FETCH c_schedule_data INTO l_parent_id,l_source_from_par_flag,l_currency_code;
749 CLOSE c_schedule_data;
750 l_parent_type := 'CAMP';
751 ELSIF p_budget_used_by_type = 'EVEO' THEN
752 OPEN c_event_sch_data(p_budget_used_by_id);
753 FETCH c_event_sch_data INTO l_parent_id,l_source_from_par_flag,l_currency_code;
754 CLOSE c_event_sch_data;
755 l_parent_type := 'EVEH';
756 ELSE
757 l_source_from_par_flag := 'N';
758 END IF;
759
760 -- commented for R12 by feliu.
761 -- flag indicating wether funding is done by fund or parent
762 -- l_source_from_par_flag := fnd_profile.VALUE ('OZF_SOURCE_FROM_PARENT');
763 /* l_source_from_par_flag := 'Y';
764
765 IF l_source_from_par_flag = 'Y' THEN
766 -- for all these high level objects sourcing is always from funds
767 IF p_budget_used_by_type IN ('EVEH', 'CAMP', 'OFFR', 'DELV', 'EONE') THEN
768 l_fund_source := 'T';
769 ELSE
770 l_fund_source := 'F';
771 END IF;
772 ELSE
773 l_fund_source := 'T';
774 END IF;
775
776 IF l_source_from_par_flag = 'Y' THEN
777 --mp 8/19/2002
778 IF p_budget_used_by_type IN ('CSCH', 'EVEO') THEN
779 l_fund_source := 'F';
780 ELSIF p_budget_used_by_type = 'OFFR' THEN
781 -- Resuable offer can not sourcing from campaign.
782 -- already handle in offer validation.
783 OPEN c_offr_reusable;
784 FETCH c_offr_reusable INTO l_dummy ;
785 CLOSE c_offr_reusable ;
786
787 IF l_dummy = 0 THEN
788 OPEN c_offr_source;
789 FETCH c_offr_source INTO l_dummy ;
790 CLOSE c_offr_source;
791
792 IF l_dummy <> 0 THEN
793 l_fund_source := 'F';
794 END IF;
795 END IF;
796 END IF;
797 END IF;
798 */
799 IF NVL(l_source_from_par_flag,'N') = 'N' THEN -- for souring from budget.
800 OPEN c_parent_source_fund;
801
802 LOOP
803 FETCH c_parent_source_fund INTO l_parent_source_rec;
804 EXIT WHEN c_parent_source_fund%NOTFOUND;
805 EXIT WHEN l_parent_source_rec.parent_source_id IS NULL;
806 IF G_DEBUG THEN
807 ozf_utility_pvt.debug_message (': in loop of soucing from budgets.');
808 END IF;
809 l_act_budgets_rec :=NULL;
810
811 l_act_budgets_rec.act_budget_used_by_id := l_parent_source_rec.parent_source_id;
812 l_act_budgets_rec.arc_act_budget_used_by := 'FUND';
813 l_act_budgets_rec.budget_source_type := p_budget_used_by_type;
814 l_act_budgets_rec.budget_source_id := p_budget_used_by_id;
815 l_act_budgets_rec.transaction_type := 'DEBIT';
816 l_act_budgets_rec.transfer_type := 'TRANSFER';
817 l_act_budgets_rec.request_amount := NVL (l_parent_source_rec.total_amount, 0); -- in arc_Act_used_by currency
818 l_act_budgets_rec.src_curr_req_amt := NVL (l_parent_source_rec.plan_curr_total_amount, 0); -- in plan currency
819 l_act_budgets_rec.request_currency := l_parent_source_rec.parent_curr;
820 l_act_budgets_rec.request_date := SYSDATE;
821 l_act_budgets_rec.status_code := 'APPROVED';
822 l_act_budgets_rec.user_status_id :=
823 ozf_utility_pvt.get_default_user_status (
824 'OZF_BUDGETSOURCE_STATUS'
825 ,l_act_budgets_rec.status_code
826 );
827 l_act_budgets_rec.approved_amount := NVL (l_parent_source_rec.total_amount, 0); -- in arc_Act_used_by currency
828 l_act_budgets_rec.approved_in_currency := p_object_currency;
829 --This is for transfer_type='REQUEST'/'TRANSFER'. hence no chnage needed.
830
831 --nirprasa for bug 7425189, get approval_date as exchange_rate_date
832 OPEN c_exchange_rate_date(l_parent_source_rec.parent_source_id);
833 FETCH c_exchange_rate_date INTO l_act_budgets_rec.exchange_rate_date;
834 CLOSE c_exchange_rate_date;
835 --skip the conversion, and use the amounts stored in object currency,
836 --so as to avoid the rounding by gl APi.
837 --For bug 7425189
838 /* ozf_utility_pvt.convert_currency (
839 x_return_status=> l_return_status
840 ,p_from_currency=> l_parent_source_rec.parent_curr
841 ,p_to_currency=> p_object_currency
842 ,p_conv_date=> l_act_budgets_rec.exchange_rate_date --nirma
843 ,p_from_amount=> l_parent_source_rec.total_amount
844 ,x_to_amount=> l_act_budgets_rec.approved_original_amount
845 );*/
846
847 l_act_budgets_rec.approved_original_amount := l_parent_source_rec.plan_curr_total_amount;
848
849 IF l_return_status = fnd_api.g_ret_sts_error THEN
850 RAISE fnd_api.g_exc_error;
851 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
852 RAISE fnd_api.g_exc_unexpected_error;
853 END IF;
854
855
856 l_act_budgets_rec.approval_date := SYSDATE;
857 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
858 l_act_budgets_rec.requester_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
859 l_act_budgets_rec.justification :=
860 fnd_message.get_string ('OZF', 'OZF_FUND_RECONCILE');
861 -- nepanda : fix for bug # 14062938 - Converting the amount to be transferred from offer currency to budget currency
862 ozf_utility_pvt.convert_currency (
863 x_return_status=> l_return_status
864 ,p_from_currency=> p_object_currency
865 ,p_to_currency=> l_parent_source_rec.parent_curr
866 ,p_conv_date=> l_act_budgets_rec.exchange_rate_date --nirma
867 ,p_from_amount=> l_parent_source_rec.plan_curr_total_amount
868 ,x_to_amount=> l_act_budgets_rec.request_amount);
869
870 l_act_budgets_rec.approved_amount := l_act_budgets_rec.request_amount ;
871
872 -- -- nepanda : fix for bug # 14062938 - Transfer Record to be created in case of both +ve and -ve total amount
873 IF NVL (l_parent_source_rec.total_amount, 0) <> 0 THEN
874 ozf_actbudgets_pvt.create_act_budgets (
875 p_api_version=> l_api_version
876 ,x_return_status=> l_return_status
877 ,x_msg_count=> x_msg_count
878 ,x_msg_data=> x_msg_data
879 ,p_act_budgets_rec=> l_act_budgets_rec
880 ,x_act_budget_id=> l_act_budget_id
881 );
882
883 IF l_return_status = fnd_api.g_ret_sts_error THEN
884 RAISE fnd_api.g_exc_error;
885 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
886 RAISE fnd_api.g_exc_unexpected_error;
887 END IF;
888 END IF;
889 END LOOP;
890
891 CLOSE c_parent_source_fund;
892 ELSE -- for sourcing from parent.
893 OPEN c_parent_source_obj;
894
895 --LOOP
896 FETCH c_parent_source_obj INTO l_parent_amount;
897 IF G_DEBUG THEN
898 ozf_utility_pvt.debug_message (': soucing from parent - l_parent_amount: ' || l_parent_amount);
899 END IF;
900 l_act_budgets_rec.act_budget_used_by_id := l_parent_id;
901 l_act_budgets_rec.arc_act_budget_used_by := l_parent_type;
902 l_act_budgets_rec.budget_source_type := p_budget_used_by_type;
903 l_act_budgets_rec.budget_source_id := p_budget_used_by_id;
904 l_act_budgets_rec.transaction_type := 'DEBIT';
905 l_act_budgets_rec.transfer_type := 'TRANSFER';
906 l_act_budgets_rec.request_currency := l_currency_code;
907
908 --nirma
909 OPEN c_exchange_rate_date(l_parent_source_rec.parent_source_id);
910 FETCH c_exchange_rate_date INTO l_act_budgets_rec.exchange_rate_date;
911 CLOSE c_exchange_rate_date;
912
913 IF l_currency_code = p_object_currency THEN
914 l_act_budgets_rec.request_amount := l_parent_amount; -- in arc_Act_used_by currency
915 ELSE
916 --nirprasa for bug 7425189, pass exchange_rate_date for conversion
917 ozf_utility_pvt.convert_currency (
918 x_return_status=> l_return_status
919 ,p_from_currency=> p_object_currency
920 ,p_to_currency=> l_currency_code
921 ,p_conv_date=> l_act_budgets_rec.exchange_rate_date --nirma
922 ,p_from_amount=> l_parent_amount
923 ,x_to_amount=> l_act_budgets_rec.request_amount
924 );
925
926 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
927 RAISE fnd_api.g_exc_unexpected_error;
928 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
929 RAISE fnd_api.g_exc_error;
930 END IF;
931
932 END IF;
933
934 l_act_budgets_rec.request_date := SYSDATE;
935 l_act_budgets_rec.status_code := 'APPROVED';
936 l_act_budgets_rec.user_status_id :=
937 ozf_utility_pvt.get_default_user_status (
938 'OZF_BUDGETSOURCE_STATUS'
939 ,l_act_budgets_rec.status_code
940 );
941 l_act_budgets_rec.approved_amount := l_act_budgets_rec.request_amount; -- in arc_Act_used_by currency
942 l_act_budgets_rec.approved_in_currency := p_object_currency;
943 l_act_budgets_rec.approved_original_amount := l_parent_amount;
944 l_act_budgets_rec.approval_date := SYSDATE;
945 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
946 l_act_budgets_rec.requester_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
947 l_act_budgets_rec.justification :=
948 fnd_message.get_string ('OZF', 'OZF_FUND_RECONCILE');
949
950
951 IF NVL (l_act_budgets_rec.request_amount, 0) > 0 THEN
952 ozf_actbudgets_pvt.create_act_budgets (
953 p_api_version=> l_api_version
954 ,x_return_status=> l_return_status
955 ,x_msg_count=> x_msg_count
956 ,x_msg_data=> x_msg_data
957 ,p_act_budgets_rec=> l_act_budgets_rec
958 ,x_act_budget_id=> l_act_budget_id
959 );
960
961 IF l_return_status = fnd_api.g_ret_sts_error THEN
962 RAISE fnd_api.g_exc_error;
963 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
964 RAISE fnd_api.g_exc_unexpected_error;
965 END IF;
966 END IF;
967 --END LOOP;
968
969 CLOSE c_parent_source_obj;
970 END IF;
971
972 IF G_DEBUG THEN
973 ozf_utility_pvt.debug_message ( l_api_name || ': end');
974 END IF;
975
976 fnd_msg_pub.count_and_get (
977 p_count=> x_msg_count
978 ,p_data=> x_msg_data
979 ,p_encoded=> fnd_api.g_false
980 );
981
982
983
984 EXCEPTION
985 WHEN fnd_api.g_exc_error THEN
986 ROLLBACK TO reconcile_budget_line;
987 x_return_status := fnd_api.g_ret_sts_error;
988 fnd_msg_pub.count_and_get (
989 p_count=> x_msg_count
990 ,p_data=> x_msg_data
991 ,p_encoded=> fnd_api.g_false
992 );
993 WHEN fnd_api.g_exc_unexpected_error THEN
994 ROLLBACK TO reconcile_budget_line;
995 x_return_status := fnd_api.g_ret_sts_unexp_error;
996 fnd_msg_pub.count_and_get (
997 p_count=> x_msg_count
998 ,p_data=> x_msg_data
999 ,p_encoded=> fnd_api.g_false
1000 );
1001 WHEN OTHERS THEN
1002 ROLLBACK TO reconcile_budget_line;
1003 x_return_status := fnd_api.g_ret_sts_unexp_error;
1004
1005 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1006 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1007 END IF;
1008
1009 fnd_msg_pub.count_and_get (
1010 p_count=> x_msg_count
1011 ,p_data=> x_msg_data
1012 ,p_encoded=> fnd_api.g_false
1013 );
1014 END reconcile_budget_line;
1015
1016 /* ---------------------------------------------------------------------
1017 -- PROCEDURE
1018 -- Recalculating-Committed_fund_conc
1019 -- PURPOSE
1020 -- This API is called from the concurrent program manager.
1021 -- It recalculats committed amount base on fund utilization
1022 ---during certain period
1023 -- and creating request or transfer records.
1024 -- PARAMETERS
1025 -- x_errbuf OUT VARCHAR2 STANDARD OUT PARAMETER
1026 -- x_retcode OUT NUMBER STANDARD OUT PARAMETER
1027
1028 -- HISTORY
1029 -- 10/05/2001 feliu CREATED
1030 -- 12/27/2001 mpande UPDATED
1031 --- 10/28/2002 feliu changed flow.
1032 -- 01/18/2005 feliu changed:
1033 -- 1. cursor for performance.
1034 -- 2. remove default recal period. if period is null, then recal upto date.
1035 -- 3. For last recal, calculate amount based on amount.
1036 -- 3. Use bulk fetch for performance.
1037 */
1038 ----------------------------------------------------------------------------
1039
1040 PROCEDURE recal_comm_fund_conc
1041 (
1042 x_errbuf OUT NOCOPY VARCHAR2
1043 , x_retcode OUT NOCOPY NUMBER
1044 )
1045
1046 IS
1047 l_recal_flag VARCHAR2 (1);
1048 l_api_version CONSTANT NUMBER := 1.0;
1049 l_api_name CONSTANT VARCHAR2 (50) := 'recal_comm_fund_conc';
1050 l_full_name CONSTANT VARCHAR2 (80) := g_pkg_name
1051 || '.'
1052 || l_api_name;
1053 l_return_status VARCHAR2 (1);
1054 l_msg_data VARCHAR2 (10000);
1055 l_msg_count NUMBER;
1056 l_committed_amt NUMBER;
1057 l_utilized_amt NUMBER;
1058 l_object_id NUMBER;
1059 l_budget_id NUMBER;
1060 l_period_end_date DATE;
1061 l_period_start_date DATE;
1062 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1063 l_util_rec ozf_actbudgets_pvt.act_util_rec_type;
1064 l_act_budget_id NUMBER;
1065 l_total_budget NUMBER;
1066 l_tot_recal_comm_amt NUMBER;
1067 l_count NUMBER := 1;
1068 l_budget_currency_code VARCHAR2 (150);
1069 l_fund_curr_req_amount NUMBER;
1070 l_forecast_tbl forecast_tbl_type;
1071
1072 l_exceed_flag VARCHAR2 (1)
1073 := NVL (fnd_profile.VALUE ('OZF_COMM_BUDGET_EXCEED'), 'N');
1074 l_parent_flag VARCHAR2 (1)
1075 := NVL (fnd_profile.VALUE ('OZF_SOURCE_FROM_PARENT'), 'N');
1076 --l_recal_period NUMBER := TO_NUMBER (NVL (fnd_profile.VALUE ('OZF_BUDGET_ADJ_RECAL_PERIOD'), '7'));
1077 l_recal_period NUMBER := fnd_profile.VALUE ('OZF_BUDGET_ADJ_RECAL_PERIOD'); -- changed by feliu on 12/01/05
1078 l_max_committed NUMBER;
1079 l_commit BOOLEAN := TRUE;
1080 l_last_flag BOOLEAN := FALSE;
1081 l_remaining_amt NUMBER;
1082
1083 TYPE listHeaderIdTbl IS TABLE OF ozf_offers.qp_list_header_id%TYPE;
1084 TYPE transCurrCodeTbl IS TABLE OF ozf_offers.transaction_currency_code%TYPE;
1085 TYPE lastRecalDateTbl IS TABLE OF ozf_offers.last_recal_date%TYPE;
1086 TYPE startDateTbl IS TABLE OF qp_list_headers_b.start_date_active%TYPE;
1087 TYPE endDateTbl IS TABLE OF qp_list_headers_b.end_date_active%TYPE;
1088 l_listHeaderIdTbl listHeaderIdTbl;
1089 l_transCurrCodeTbl transCurrCodeTbl;
1090 l_lastRecalDateTbl lastRecalDateTbl;
1091 l_startDateTbl startDateTbl;
1092 l_endDateTbl endDateTbl;
1093
1094 CURSOR l_offer_csr IS
1095 SELECT offs.qp_list_header_id
1096 ,nvl(offs.transaction_currency_code,fund_request_curr_code)
1097 ,qpl.start_date_active start_date_active
1098 ,qpl.end_date_active end_date_active
1099 ,NVL(offs.last_recal_date,qpl.start_date_active) last_recal_date -- changed to new column for last recal date
1100 --,qpl.description description
1101 FROM ozf_offers offs, qp_list_headers_b qpl
1102 WHERE offs.offer_type NOT IN ('LUMPSUM', 'TERMS','SCAN_DATA')
1103 AND offs.status_code = 'ACTIVE'
1104 AND NVL (offs.account_closed_flag, 'N') = 'N'
1105 AND offs.qp_list_header_id = qpl.list_header_id(+)
1106 AND qpl.start_date_active < SYSDATE
1107 AND qpl.end_date_active is not NULL
1108 --AND qpl.end_date_active > SYSDATE
1109 AND NVL (offs.budget_offer_yn, 'N') = 'N'
1110 AND NVL(offs.last_recal_date,qpl.start_date_active) <= qpl.end_date_active;
1111
1112
1113 --Total committed amount in offer currency
1114 -- Ribha, changed cursor query to avoid using ozf_object_checkbook_v (non mergeable view)
1115 -- Ribha: use ozf_object_fund_summary instead of ozf_object_checkbook_v
1116 CURSOR l_budget_csr (p_object_id IN NUMBER) IS
1117 SELECT fund_id,SUM(NVL(plan_curr_committed_amt,0)) total_amt
1118 FROM ozf_object_fund_summary
1119 WHERE object_id = p_object_id
1120 AND object_type = 'OFFR'
1121 --AND NVL(recal_flag,'N') ='N'
1122 GROUP BY fund_id;
1123
1124 --Utilized amount in offer currency.
1125 CURSOR l_utilized_csr (
1126 p_object_id IN NUMBER
1127 ,p_budget_id IN NUMBER
1128 ,p_start_date IN DATE
1129 ,p_end_date IN DATE
1130 ) IS
1131 SELECT SUM (NVL(plan_curr_amount,0)) utilized_amt
1132 FROM ozf_funds_utilized_all_b
1133 WHERE plan_id = p_object_id
1134 AND plan_type = 'OFFR'
1135 AND fund_id = p_budget_id
1136 -- AND utilization_type NOT IN ('TRANSFER', 'REQUEST')
1137 AND NVL(adjustment_date,creation_date) BETWEEN p_start_date AND p_end_date + 1;
1138
1139 --get budget information.
1140 -- rimehrot fixed sql repository violation 14894255
1141 CURSOR l_tot_budget_csr (p_budget_id IN NUMBER) IS
1142 SELECT (NVL(original_budget, 0) + NVL(transfered_in_amt,0) - NVL(transfered_out_amt, 0))
1143 ,recal_committed
1144 ,currency_code_tc
1145 FROM ozf_funds_all_b
1146 WHERE fund_id = p_budget_id;
1147
1148
1149 --get forecast information.
1150 CURSOR l_forecast_csr (p_offer_id IN NUMBER) IS
1151 SELECT DISTINCT metr.from_date -- need distince for multiple dimension forecast
1152 ,metr.TO_DATE
1153 ,metr.fact_percent
1154 FROM ozf_act_forecasts_all fore, ozf_act_metric_facts_all metr
1155 WHERE fore.arc_act_fcast_used_by = 'OFFR'
1156 AND fore.act_fcast_used_by_id = p_offer_id
1157 AND fore.base_quantity_type <>'BASELINE'
1158 AND metr.arc_act_metric_used_by = 'FCST'
1159 AND metr.act_metric_used_by_id(+) = fore.forecast_id
1160 AND metr.fact_type= 'TIME'
1161 AND freeze_flag = 'N';
1162
1163 -- to decide if max==committed
1164 CURSOR l_max_csr(p_offer_id IN NUMBER) IS
1165 SELECT count(1)
1166 FROM qp_limits ql
1167 WHERE ql.list_header_id = p_offer_id
1168 AND ql.list_line_id = -1
1169 AND limit_number = 1
1170 AND basis='COST'
1171 AND organization_flag='N'
1172 AND limit_level_code='ACROSS_TRANSACTION'
1173 AND limit_exceed_action_code = 'SOFT'
1174 AND limit_hold_flag='Y';
1175
1176 CURSOR l_last_util_csr (
1177 p_object_id IN NUMBER
1178 ,p_budget_id IN NUMBER
1179 ) IS
1180 SELECT NVL(plan_curr_recal_committed_amt,0)- NVL(PLAN_CURR_UTILIZED_AMT,0)
1181 FROM ozf_object_fund_summary
1182 WHERE object_id = p_object_id
1183 AND fund_id = p_budget_id
1184 AND object_type = 'OFFR';
1185
1186 BEGIN
1187 -- Standard Start of API savepoint
1188 SAVEPOINT recal_comm_fund_conc;
1189 -- Debug Message
1190 IF G_DEBUG THEN
1191 ozf_utility_pvt.debug_message ( 'Private API: '
1192 || l_api_name
1193 || 'start');
1194 END IF;
1195
1196 -- get recalculating flag from profile
1197 l_recal_flag := NVL (fnd_profile.VALUE ('OZF_BUDGET_ADJ_ALLOW_RECAL'), 'N');
1198
1199 IF G_DEBUG THEN
1200 ozf_utility_pvt.debug_message ( 'Recalculating flag: '|| l_recal_flag || ' l_parent_flag: ' || l_parent_flag ) ;
1201 END IF;
1202
1203 IF l_recal_flag = 'Y' AND l_parent_flag = 'N' THEN
1204 OPEN l_offer_csr;
1205 LOOP
1206 FETCH l_offer_csr BULK COLLECT INTO l_listHeaderIdTbl ,
1207 l_transCurrCodeTbl ,
1208 l_startDateTbl,
1209 l_endDateTbl,
1210 l_lastRecalDateTbl
1211 LIMIT g_bulk_limit;
1212 IF G_DEBUG THEN
1213 ozf_utility_pvt.debug_message ( 'l_listHeaderIdTbl count: '|| l_listHeaderIdTbl.COUNT ) ;
1214 END IF;
1215
1216 FOR i IN NVL(l_listHeaderIdTbl.FIRST, 1) .. NVL(l_listHeaderIdTbl.LAST, 0) LOOP
1217
1218 BEGIN
1219 SAVEPOINT offer_loop_savepoint;
1220 l_commit := TRUE;
1221 OPEN l_max_csr(l_listHeaderIdTbl(i));
1222 FETCH l_max_csr INTO l_max_committed;
1223 CLOSE l_max_csr;
1224 --if max == committed is true, do not recal for this offer.
1225 IF l_max_committed = 0 THEN
1226
1227 OPEN l_forecast_csr (l_listHeaderIdTbl(i));
1228 LOOP
1229 FETCH l_forecast_csr INTO l_forecast_tbl(l_count).start_date, l_forecast_tbl(l_count).end_date,
1230 l_forecast_tbl(l_count).forecast_value;
1231 EXIT WHEN l_forecast_csr%NOTFOUND;
1232 l_count := l_count + 1;
1233 END LOOP;
1234 CLOSE l_forecast_csr;
1235 l_count := 0;
1236
1237 IF l_forecast_tbl.COUNT > 0 THEN --with forecast
1238 --get last recal date and escape previous period.
1239 FOR j IN 1..l_forecast_tbl.LAST LOOP
1240 IF l_forecast_tbl(j).start_date = l_lastRecalDateTbl(i) THEN
1241 l_count := j;
1242 EXIT;
1243 END IF;
1244 END LOOP;
1245 --get next period since last recal.
1246 l_period_start_date := l_forecast_tbl(l_count).start_date;
1247 l_period_end_date := l_forecast_tbl(l_count).end_date;
1248 ELSE -- without forecast.
1249 l_period_start_date := TRUNC(l_lastRecalDateTbl(i));
1250
1251 IF TRUNC(l_endDateTbl(i)) > TRUNC(SYSDATE) THEN
1252 IF l_recal_period is NOT NULL THEN
1253 l_period_end_date := l_period_start_date + TRUNC((TRUNC(SYSDATE) - l_period_start_date)/l_recal_period ) *l_recal_period - 1;
1254 ELSE
1255 l_period_end_date := TRUNC(SYSDATE) - 1;
1256 END IF;
1257 l_last_flag := false;
1258 ELSE
1259 l_period_end_date := TRUNC(l_endDateTbl(i));
1260 l_last_flag := true;
1261 END IF;
1262 END IF;
1263
1264 IF G_DEBUG THEN
1265 ozf_utility_pvt.debug_message ( 'forecast count:' || l_count);
1266 ozf_utility_pvt.debug_message ( 'Start Period: '|| l_period_start_date);
1267 ozf_utility_pvt.debug_message ( 'End Period: ' || l_period_end_date);
1268 ozf_utility_pvt.debug_message ( 'Offer ID: ' || l_listHeaderIdTbl(i));
1269 END IF;
1270
1271 -- check end period is less than sysdate.
1272 WHILE l_period_end_date >=l_period_start_date AND l_period_end_date < TRUNC(SYSDATE) AND
1273 TRUNC(l_endDateTbl(i)) >= l_period_end_date LOOP
1274 --For each budget, recaling amount
1275 FOR budget_rec IN l_budget_csr (l_listHeaderIdTbl(i))
1276 LOOP
1277 -- for offer with forcast, committed amount in this period equal to total
1278 -- budget amount multipled by forcast percent.
1279 IF l_last_flag = FALSE THEN
1280 IF l_forecast_tbl.COUNT > 0 THEN
1281 l_committed_amt := budget_rec.total_amt * l_forecast_tbl(l_count).forecast_value/100;
1282 l_committed_amt :=ozf_utility_pvt.currround(l_committed_amt ,l_transCurrCodeTbl(i));
1283 ELSE --for offer without forcast. committed amount in this period is equal to
1284 -- daily amount multipled by period days.
1285 l_committed_amt :=budget_rec.total_amt * ( (l_period_end_date - l_period_start_date + 1) / (TRUNC(l_endDateTbl(i)) - TRUNC(l_startDateTbl(i)) + 1) ) ;
1286 l_committed_amt :=ozf_utility_pvt.currround(l_committed_amt ,l_transCurrCodeTbl(i));
1287 END IF; -- end of l_forecast_tbl.COUNT > 0
1288
1289 OPEN l_utilized_csr (
1290 l_listHeaderIdTbl(i)
1291 ,budget_rec.fund_id
1292 ,l_period_start_date
1293 ,l_period_end_date
1294 );
1295 FETCH l_utilized_csr INTO l_utilized_amt;
1296 l_utilized_amt := NVL (l_utilized_amt, 0); -- in offer currency
1297 CLOSE l_utilized_csr;
1298
1299 l_remaining_amt := l_committed_amt - l_utilized_amt;
1300 ELSE
1301 OPEN l_last_util_csr (
1302 l_listHeaderIdTbl(i)
1303 ,budget_rec.fund_id
1304 );
1305 FETCH l_last_util_csr INTO l_remaining_amt;
1306 CLOSE l_last_util_csr;
1307 END IF;
1308
1309 IF l_remaining_amt > 0 THEN
1310 l_remaining_amt :=ozf_utility_pvt.currround(l_remaining_amt ,l_transCurrCodeTbl(i));
1311 END IF;
1312
1313 IF G_DEBUG THEN
1314 ozf_utility_pvt.debug_message ( 'budget_rec.total_amt: '|| budget_rec.total_amt);
1315 ozf_utility_pvt.debug_message ( 'offer_rec.transaction_currency_code: '|| l_transCurrCodeTbl(i));
1316 ozf_utility_pvt.debug_message ( 'committed amount: '|| l_committed_amt);
1317 ozf_utility_pvt.debug_message ( 'Utilized amount: '|| l_utilized_amt);
1318 ozf_utility_pvt.debug_message ( 'l_remaining_amt: '|| l_remaining_amt);
1319 END IF;
1320 -- if committed budget is not equal to utilized budget, then go on with recalculating commitment.
1321 IF l_remaining_amt <> 0 THEN
1322 l_act_budgets_rec :=NULL;
1323 l_util_rec := NULL;
1324 OPEN l_tot_budget_csr (budget_rec.fund_id);
1325 FETCH l_tot_budget_csr INTO l_total_budget
1326 ,l_tot_recal_comm_amt
1327 ,l_budget_currency_code;
1328 CLOSE l_tot_budget_csr;
1329 --if utilized is more than committed, create request act budget.
1330 IF l_remaining_amt < 0 THEN
1331 l_act_budgets_rec.act_budget_used_by_id :=l_listHeaderIdTbl(i);
1332 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
1333 l_act_budgets_rec.budget_source_type := 'FUND';
1334 l_act_budgets_rec.budget_source_id := budget_rec.fund_id;
1335 l_act_budgets_rec.transaction_type := 'CREDIT';
1336 l_act_budgets_rec.transfer_type := 'REQUEST';
1337 l_util_rec.adjustment_type := 'INCREASE_COMMITTED';
1338 l_util_rec.adjustment_type_id := -2;
1339 l_util_rec.adjustment_date := sysdate;
1340 /* Added by mpande 12/19/2001 for Multi Currency bug*/
1341 l_act_budgets_rec.request_amount := -l_remaining_amt;
1342 --l_fund_curr_req_amount :=l_utilized_amt - l_committed_amt;
1343 l_act_budgets_rec.request_currency :=l_transCurrCodeTbl(i);
1344 l_act_budgets_rec.approved_amount := l_act_budgets_rec.request_amount;
1345 IF l_budget_currency_code = l_transCurrCodeTbl(i) THEN
1346 l_act_budgets_rec.approved_original_amount := l_act_budgets_rec.request_amount;
1347 ELSE
1348 -- call the currency conversion since request amount is in object currency.
1349 ozf_utility_pvt.convert_currency (
1350 x_return_status=> l_return_status
1351 ,p_from_currency=> l_transCurrCodeTbl(i)
1352 ,p_to_currency=> l_budget_currency_code
1353 ,p_from_amount=> l_act_budgets_rec.request_amount
1354 ,x_to_amount=> l_act_budgets_rec.approved_original_amount
1355 );
1356
1357 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
1358 -- ROLLBACK TO offer_loop_savepoint;
1359 --x_retcode := 1;
1360 --x_errbuf := l_msg_data;
1361 l_commit := FALSE;
1362 EXIT; -- exit budget loop
1363 END IF;
1364 END IF;
1365 l_act_budgets_rec.approved_in_currency := l_budget_currency_code;
1366 l_fund_curr_req_amount :=l_act_budgets_rec.approved_original_amount;
1367 ELSE -- Create transfer act budget.
1368 l_act_budgets_rec.act_budget_used_by_id :=budget_rec.fund_id;
1369 l_act_budgets_rec.arc_act_budget_used_by := 'FUND';
1370 l_act_budgets_rec.budget_source_type := 'OFFR';
1371 l_act_budgets_rec.budget_source_id := l_listHeaderIdTbl(i);
1372 l_act_budgets_rec.transaction_type := 'DEBIT';
1373 l_act_budgets_rec.transfer_type := 'TRANSFER';
1374 l_util_rec.adjustment_type := 'DECREASE_COMMITTED';
1375 l_util_rec.adjustment_type_id := -3;
1376 l_util_rec.adjustment_date := sysdate;
1377 l_act_budgets_rec.request_currency := l_budget_currency_code; -- in act used by curr
1378 l_act_budgets_rec.approved_in_currency := l_transCurrCodeTbl(i); -- in offer curr
1379 l_act_budgets_rec.approved_original_amount :=l_remaining_amt; -- in offer curr
1380
1381 IF l_budget_currency_code = l_transCurrCodeTbl(i) THEN
1382 l_act_budgets_rec.request_amount := l_act_budgets_rec.approved_original_amount;
1383 ELSE
1384 -- call the currency conversion wrapper
1385 ozf_utility_pvt.convert_currency (
1386 x_return_status=> l_return_status
1387 ,p_from_currency=> l_transCurrCodeTbl(i) -- source curr
1388 ,p_to_currency=> l_budget_currency_code -- from budget curr
1389 ,p_from_amount=> l_act_budgets_rec.approved_original_amount -- in offer curr
1390 ,x_to_amount=> l_act_budgets_rec.request_amount -- in budget curr
1391 );
1392
1393 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
1394 l_commit := FALSE;
1395 EXIT; -- exit budget loop
1396 END IF;
1397 END IF;
1398
1399 l_act_budgets_rec.approved_amount :=l_act_budgets_rec.request_amount; -- in act_used_by curr
1400 l_fund_curr_req_amount := -l_act_budgets_rec.request_amount;
1401 END IF; -- end of creation 'TRANSFER'
1402
1403 l_act_budgets_rec.status_code := 'APPROVED';
1404 l_act_budgets_rec.recal_flag := 'Y';
1405 l_act_budgets_rec.request_date := SYSDATE;
1406 l_act_budgets_rec.user_status_id :=
1407 ozf_utility_pvt.get_default_user_status (
1408 'OZF_BUDGETSOURCE_STATUS'
1409 ,l_act_budgets_rec.status_code
1410 );
1411 IF G_DEBUG THEN
1412 ozf_utility_pvt.debug_message ('Recalculated amount: '
1413 || l_act_budgets_rec.request_amount
1414 );
1415 END IF;
1416 l_act_budgets_rec.approval_date := SYSDATE;
1417 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
1418 l_act_budgets_rec.requester_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
1419 l_act_budgets_rec.justification :=
1420 fnd_message.get_string ('OZF', 'OZF_ACT_BUDGET_RECAL_COMM');
1421 IF G_DEBUG THEN
1422 ozf_utility_pvt.debug_message ('Allow exceed: '|| l_exceed_flag);
1423 END IF;
1424
1425 -- check if it allows committed amount exceed total budget in budget currency.
1426 IF ( NVL (l_tot_recal_comm_amt, 0)
1427 + l_fund_curr_req_amount < l_total_budget
1428 )
1429 OR l_exceed_flag = 'Y' THEN
1430
1431 IF G_DEBUG THEN
1432 ozf_utility_pvt.debug_message ('Create act budget: ');
1433 END IF;
1434
1435 IF l_act_budgets_rec.request_amount > 0 THEN
1436 ozf_actbudgets_pvt.create_act_budgets (
1437 p_api_version=> l_api_version
1438 ,x_return_status=> l_return_status
1439 ,x_msg_count=> l_msg_count
1440 ,x_msg_data=> l_msg_data
1441 ,p_act_budgets_rec=> l_act_budgets_rec
1442 ,p_act_util_rec=> l_util_rec
1443 ,x_act_budget_id=> l_act_budget_id
1444 ,p_approval_flag=> fnd_api.g_true
1445 );
1446
1447 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
1448 l_commit := FALSE;
1449 EXIT; -- exit budget loop
1450 END IF;
1451 END IF;
1452
1453 END IF; -- End of if for check if it allow total committment exceed total budget
1454
1455 END IF; -- end of if l_remaining_amt <>0.
1456 END LOOP; -- end of loop for budgets.
1457
1458 IF NOT (l_commit)THEN
1459 EXIT; -- exit end period loop, if error occured so far.
1460 END IF;
1461
1462 -- with forecast, get next period.
1463 IF l_forecast_tbl.COUNT > 0 THEN
1464 l_count := l_count + 1;
1465 -- if count larger than total, then exit.
1466 IF l_count > l_forecast_tbl.COUNT THEN
1467 EXIT;
1468 END IF;
1469 l_period_start_date := l_forecast_tbl(l_count).start_date;
1470 l_period_end_date := l_forecast_tbl(l_count).end_date;
1471 ELSE -- without forecast, get next period by adding period days.
1472 l_period_start_date := l_period_end_date + 1;
1473 l_period_end_date := l_period_start_date + NVL(l_recal_period,0) - 1 ;
1474 END IF;
1475
1476 END LOOP; -- End of loop for end period is equal to system date.
1477 l_count := 0;
1478
1479 -- update ozf_offer's last recal column with l_period_start_date - 1 which is last period end day.
1480 IF l_act_budget_id is not null THEN
1481 UPDATE ozf_offers
1482 SET last_recal_date = l_period_start_date
1483 WHERE qp_list_header_id = l_listHeaderIdTbl(i);
1484 END IF;
1485
1486 END IF; -- end of l_max_committed.
1487
1488 IF (l_commit) THEN -- commit separately for each offer in the loop.
1489 x_retcode := 0;
1490 COMMIT;
1491 ELSE
1492 ozf_utility_pvt.write_conc_log('ERROR: Could not perform recalculated committed for Offer: '|| l_listHeaderIdTbl(i));
1493 ROLLBACK TO offer_loop_savepoint; --rollback for the current offer if error occured.
1494 END IF;
1495
1496 END;
1497 END LOOP; --l_listHeaderIdTbl.FIRST,
1498 EXIT WHEN l_offer_csr%NOTFOUND;
1499
1500 END LOOP ; -- bulk fetch loop
1501 CLOSE l_offer_csr;
1502
1503 END IF; -- End of if for allow recalculating profile.
1504
1505 IF G_DEBUG THEN
1506 ozf_utility_pvt.debug_message ( 'Private API: '
1507 || l_api_name
1508 || ' end');
1509 END IF;
1510
1511 ozf_utility_pvt.write_conc_log(l_msg_data);
1512
1513 EXCEPTION
1514 WHEN OTHERS THEN
1515 ROLLBACK TO recal_comm_fund_conc;
1516 ozf_utility_pvt.write_conc_log (l_msg_data);
1517 x_retcode := 1;
1518 x_errbuf := l_msg_data;
1519 END recal_comm_fund_conc;
1520 ---------------------------------------------------------------------
1521 -- PROCEDURE
1522 -- post_utilized_budget_conc
1523 --
1524 -- PURPOSE
1525 --This API will be called by claim to automatic increase committed and utilized budget
1526 --when automatic adjustment is allowed for scan data offer.
1527 --It will increase both committed and utilized amount.
1528
1529 -- PARAMETERS
1530 -- ,p_api_version IN NUMBER
1531 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1532 -- ,p_commit IN VARCHAR2 := fnd_api.g_false
1533 -- ,x_msg_count OUT NOCOPY NUMBER
1534 -- ,x_msg_data OUT NOCOPY VARCHAR2
1535 -- ,x_return_status OUT NOCOPY VARCHAR2)
1536
1537 -- NOTES
1538 -- HISTORY
1539 -- 09/24/2002 feliu Create.
1540 -- 06/17/2003 feliu fixed bug 3007282 for save point.
1541 -- 08/13/2003 feliu add debug message and commit each line instead of all lines.
1542 ----------------------------------------------------------------------
1543 PROCEDURE post_utilized_budget_conc
1544 (
1545 x_errbuf OUT NOCOPY VARCHAR2
1546 ,x_retcode OUT NOCOPY NUMBER
1547 /*p_api_version IN NUMBER
1548 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1549 ,p_commit IN VARCHAR2 := fnd_api.g_false
1550 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1551 ,x_return_status OUT NOCOPY VARCHAR2
1552 ,x_msg_count OUT NOCOPY NUMBER
1553 ,x_msg_data OUT NOCOPY VARCHAR2 */
1554 ) IS
1555 l_recal_flag VARCHAR2 (1);
1556 l_api_version CONSTANT NUMBER := 1.0;
1557 l_api_name CONSTANT VARCHAR2 (50) := 'Post_utilized_budget_conc';
1558 l_full_name CONSTANT VARCHAR2 (80) := g_pkg_name
1559 || '.'
1560 || l_api_name;
1561 l_return_status VARCHAR2 (1);
1562 l_msg_data VARCHAR2 (10000);
1563 l_msg_count NUMBER;
1564
1565 CURSOR c_offer_rec IS
1566 SELECT offr.qp_list_header_id offer_id,offr.offer_type
1567 ,offr.transaction_currency_code offer_curr
1568 FROM qp_list_headers_b qpoffr, ozf_offers offr
1569 WHERE offr.qp_list_header_id = qpoffr.list_header_id
1570 AND offr.status_code IN ('ACTIVE')
1571 AND offr.offer_type IN ('LUMPSUM', 'SCAN_DATA')
1572 AND NVL (offr.account_closed_flag, 'N') = 'N'
1573 --AND offr.qp_list_header_id IN(11257,11258);
1574 AND qpoffr.start_date_active <= SYSDATE;-- fix bug 3091987.
1575
1576 BEGIN
1577 SAVEPOINT post_utilized_budget_conc;
1578 IF G_DEBUG THEN
1579 ozf_utility_pvt.debug_message (': begin ' || l_full_name);
1580 END IF;
1581 -- x_return_status := fnd_api.g_ret_sts_success;
1582
1583 /* IF fnd_api.to_boolean (p_init_msg_list) THEN
1584 fnd_msg_pub.initialize;
1585 END IF;
1586
1587 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1588 RAISE fnd_api.g_exc_unexpected_error;
1589 END IF;
1590 */
1591 FOR l_off_budget_rec IN c_offer_rec
1592 LOOP
1593 SAVEPOINT offer_budget;
1594 ozf_fund_adjustment_pvt.post_utilized_budget (
1595 p_offer_id=> l_off_budget_rec.offer_id
1596 ,p_offer_type=> l_off_budget_rec.offer_type
1597 ,p_api_version=> 1
1598 ,p_init_msg_list=> fnd_api.g_false
1599 ,p_commit=> fnd_api.g_false
1600 ,p_check_date => fnd_api.g_false -- no date validation
1601 ,x_msg_count=> l_msg_count
1602 ,x_msg_data=> l_msg_data
1603 ,x_return_status=> l_return_status
1604 );
1605
1606
1607 IF l_return_status = fnd_api.g_ret_sts_success THEN
1608 COMMIT;
1609 x_retcode := 0;
1610 ELSE
1611 IF G_DEBUG THEN
1612 ozf_utility_pvt.debug_message ('Error out offer: ' || l_off_budget_rec.offer_id);
1613 END IF;
1614
1615 ROLLBACK TO offer_budget;
1616 END IF;
1617 END LOOP;
1618
1619 /* fnd_msg_pub.count_and_get (
1620 p_encoded=> fnd_api.g_false
1621 ,p_count=> x_msg_count
1622 ,p_data=> x_msg_data
1623 );
1624 IF G_DEBUG THEN
1625 ozf_utility_pvt.debug_message ( l_full_name || ': end');
1626 END IF;
1627 EXCEPTION
1628 WHEN fnd_api.g_exc_error THEN
1629 ROLLBACK TO Post_utilized_budget_conc;
1630 x_return_status := fnd_api.g_ret_sts_error;
1631 fnd_msg_pub.count_and_get (
1632 p_count=> x_msg_count
1633 ,p_data=> x_msg_data
1634 ,p_encoded=> fnd_api.g_false
1635 );
1636 WHEN fnd_api.g_exc_unexpected_error THEN
1637 ROLLBACK TO Post_utilized_budget_conc;
1638 x_return_status := fnd_api.g_ret_sts_unexp_error;
1639 fnd_msg_pub.count_and_get (
1640 p_count=> x_msg_count
1641 ,p_data=> x_msg_data
1642 ,p_encoded=> fnd_api.g_false
1643 );
1644 WHEN OTHERS THEN
1645 ROLLBACK TO Post_utilized_budget_conc;
1646 x_return_status := fnd_api.g_ret_sts_unexp_error;
1647
1648 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1649 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1650 END IF;
1651
1652 fnd_msg_pub.count_and_get (
1653 p_count=> x_msg_count
1654 ,p_data=> x_msg_data
1655 ,p_encoded=> fnd_api.g_false
1656 ); */
1657 -- COMMIT;
1658 -- x_retcode := 0;
1659 ozf_utility_pvt.write_conc_log (l_msg_data);
1660
1661 EXCEPTION
1662 WHEN OTHERS THEN
1663 ROLLBACK TO post_utilized_budget_conc;
1664 x_retcode := 1;
1665 x_errbuf := l_msg_data;
1666 ozf_utility_pvt.write_conc_log (x_errbuf);
1667
1668 END post_utilized_budget_conc;
1669
1670 ---------------------------------------------------------------------
1671 -- PROCEDURE
1672 -- reconcile_budget_utilized
1673 --
1674 -- PURPOSE
1675 --This API will be reconcile un_paid amount. it is called by concurrent program.
1676
1677 -- PARAMETERS
1678 -- p_budget_used_by_id IN object id,
1679 -- p_budget_used_by_type IN object type,
1680 -- p_object_currency IN object currency,
1681
1682 -- NOTES
1683 -- HISTORY
1684 -- 09/24/2002 feliu Create.
1685 ----------------------------------------------------------------------
1686
1687 PROCEDURE reconcile_budget_utilized (
1688 p_budget_used_by_id IN NUMBER
1689 ,p_budget_used_by_type IN VARCHAR2
1690 ,p_object_currency IN VARCHAR2
1691 ,p_api_version IN NUMBER
1692 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1693 ,p_commit IN VARCHAR2 := fnd_api.g_false
1694 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1695 ,x_return_status OUT NOCOPY VARCHAR2
1696 ,x_msg_count OUT NOCOPY NUMBER
1697 ,x_msg_data OUT NOCOPY VARCHAR2
1698 ) IS
1699
1700 -- nirprasa for bug 7425189, added exchange_rate_date in select clause
1701 -- nirprasa 12.2 ER 8399134, added fund_request_currency_code and plan_currency_code
1702 -- in select clause.
1703 CURSOR c_parent_source_fund IS
1704 SELECT a1.fund_id parent_source_id
1705 ,a1.currency_code parent_curr
1706 ,NVL (SUM (a1.plan_curr_amount_remaining), 0) amount
1707 ,a1.product_id,a1.product_level_type,a1.scan_unit,a1.scan_unit_remaining,
1708 a1.activity_product_id,a1.cust_account_id,a1.gl_posted_flag,a1.utilization_id orig_utilization_id
1709 ,a1.exchange_rate_type ,a1.exchange_rate_date,a1.org_id --Added for bug 7030415
1710 ,a1.fund_request_currency_code,a1.plan_currency_code
1711 ,a1.object_type, a1.object_id, a1.order_line_id -- Added for FP 13252796
1712 FROM ozf_funds_utilized_all_b a1
1713 WHERE a1.component_id = p_budget_used_by_id
1714 AND a1.component_type = p_budget_used_by_type
1715 AND a1.utilization_type IN -- feliu on 11/11/05: remove UTILIZED and SALES ACCRUAL.
1716 ('ADJUSTMENT', 'ACCRUAL','CHARGEBACK','LEAD_ACCRUAL') -- yzhao: 11.5.10 added chargeback
1717 GROUP BY a1.fund_id, a1.currency_code,a1.product_id,
1718 a1.product_level_type,a1.scan_unit,a1.scan_unit_remaining,
1719 a1.activity_product_id,a1.cust_account_id,a1.gl_posted_flag,
1720 a1.exchange_rate_type,a1.exchange_rate_date,
1721 a1.org_id,a1.fund_request_currency_code,a1.plan_currency_code,
1722 a1.utilization_id
1723 ,a1.object_type, a1.object_id, a1.order_line_id -- Added for FP 13252796
1724 ORDER BY parent_source_id;
1725
1726
1727
1728 l_rate NUMBER;
1729 l_parent_source_rec c_parent_source_fund%ROWTYPE;
1730 l_api_version NUMBER := 1.0;
1731 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1732 l_api_name VARCHAR2 (60) := 'reconcile_budget_utilized';
1733 l_full_name CONSTANT VARCHAR2 (80) := g_pkg_name
1734 || '.'
1735 || l_api_name;
1736 l_act_budget_id NUMBER;
1737 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1738 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type ;
1739 l_converted_amt NUMBER;
1740 l_object_currency VARCHAR2(15) := p_object_currency;
1741
1742 BEGIN
1743 SAVEPOINT reconcile_budget_utilized;
1744 x_return_status := fnd_api.g_ret_sts_success;
1745 IF G_DEBUG THEN
1746 ozf_utility_pvt.debug_message (': before parent source cursor ');
1747 END IF;
1748
1749 IF fnd_api.to_boolean (p_init_msg_list) THEN
1750 fnd_msg_pub.initialize;
1751 END IF;
1752
1753 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1754 RAISE fnd_api.g_exc_unexpected_error;
1755 END IF;
1756
1757 OPEN c_parent_source_fund;
1758
1759 LOOP
1760 FETCH c_parent_source_fund INTO l_parent_source_rec;
1761 EXIT WHEN c_parent_source_fund%NOTFOUND;
1762 EXIT WHEN l_parent_source_rec.parent_source_id IS NULL;
1763 l_act_budgets_rec :=NULL;
1764 IF G_DEBUG THEN
1765 ozf_utility_pvt.debug_message (': in loop ');
1766 END IF;
1767 l_act_budgets_rec.act_budget_used_by_id := p_budget_used_by_id;
1768 l_act_budgets_rec.arc_act_budget_used_by := p_budget_used_by_type;
1769 l_act_budgets_rec.budget_source_type := p_budget_used_by_type;
1770 l_act_budgets_rec.budget_source_id := p_budget_used_by_id;
1771 l_act_budgets_rec.transaction_type := 'DEBIT';
1772 l_act_budgets_rec.transfer_type := 'UTILIZED';
1773 l_act_budgets_rec.request_amount := - NVL (l_parent_source_rec.amount, 0); -- in object currency
1774 l_act_budgets_rec.request_currency := l_parent_source_rec.plan_currency_code; --parent_curr;
1775 l_act_budgets_rec.request_date := SYSDATE;
1776 l_act_budgets_rec.status_code := 'APPROVED';
1777 l_act_budgets_rec.user_status_id :=
1778 ozf_utility_pvt.get_default_user_status (
1779 'OZF_BUDGETSOURCE_STATUS'
1780 ,l_act_budgets_rec.status_code
1781 );
1782 l_act_budgets_rec.approved_amount := NVL (l_parent_source_rec.amount, 0); -- in arc_Act_used_by currency
1783 l_act_budgets_rec.approved_in_currency := l_parent_source_rec.plan_currency_code; --p_object_currency;
1784
1785 IF G_DEBUG THEN
1786 ozf_utility_pvt.debug_message ('l_object_currency: '||l_object_currency);
1787 END IF;
1788
1789 IF l_object_currency <> l_parent_source_rec.plan_currency_code THEN
1790 l_object_currency := l_parent_source_rec.plan_currency_code;
1791 END IF;
1792
1793 IF G_DEBUG THEN
1794 ozf_utility_pvt.debug_message ('l_object_currency: '||l_object_currency);
1795 ozf_utility_pvt.debug_message ('fund_request_currency_code: '||l_parent_source_rec.fund_request_currency_code);
1796 ozf_utility_pvt.debug_message ('parent_curr '||l_parent_source_rec.parent_curr);
1797 ozf_utility_pvt.debug_message ('l_parent_source_rec.amount '|| l_parent_source_rec.amount);
1798 END IF;
1799
1800 IF l_parent_source_rec.parent_curr = l_object_currency THEN
1801 l_converted_amt := l_parent_source_rec.amount;
1802 ELSE
1803 --Added for bug 7030415, this is for returning unpaid amount to the budget, so
1804 --we need to pass the conv_type. here transfer_type='UTILIZED'
1805
1806 ozf_utility_pvt.convert_currency (
1807 x_return_status=> l_return_status
1808 ,p_from_currency=> l_object_currency
1809 ,p_to_currency=> l_parent_source_rec.parent_curr
1810 ,p_conv_type=>l_parent_source_rec.exchange_rate_type --Added for bug 7030415
1811 ,p_conv_date=>l_parent_source_rec.exchange_rate_date --Added for bug 7425189
1812 ,p_from_amount=> l_parent_source_rec.amount
1813 ,x_to_amount=> l_converted_amt
1814 ,x_rate=> l_rate
1815 );
1816 END IF;
1817
1818 IF G_DEBUG THEN
1819 ozf_utility_pvt.debug_message ('l_converted_amt '|| l_converted_amt);
1820 END IF;
1821
1822 IF l_return_status = fnd_api.g_ret_sts_error THEN
1823 RAISE fnd_api.g_exc_error;
1824 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1825 RAISE fnd_api.g_exc_unexpected_error;
1826 END IF;
1827 l_act_budgets_rec.approval_date := SYSDATE;
1828 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
1829 l_act_budgets_rec.justification := fnd_message.get_string ('OZF', 'OZF_ACT_BUDG_CST_UTIL');
1830 l_act_budgets_rec.parent_source_id := l_parent_source_rec.parent_source_id;
1831 l_act_budgets_rec.parent_src_curr := l_parent_source_rec.parent_curr;
1832 l_act_budgets_rec.parent_src_apprvd_amt := - l_converted_amt; -- in budget currency.
1833 l_act_budgets_rec.exchange_rate_date := l_parent_source_rec.exchange_rate_date; --Added for bug 7425189
1834 l_act_util_rec.product_id := l_parent_source_rec.product_id ;
1835 l_act_util_rec.product_level_type := l_parent_source_rec.product_level_type;
1836 l_act_util_rec.gl_date := sysdate;
1837 l_act_util_rec.scan_unit := - l_parent_source_rec.scan_unit;
1838 l_act_util_rec.scan_unit_remaining := - l_parent_source_rec.scan_unit_remaining;
1839 l_act_util_rec.activity_product_id := l_parent_source_rec.activity_product_id;
1840 l_act_util_rec.utilization_type := 'ADJUSTMENT';
1841 l_act_util_rec.adjustment_type := 'DECREASE_EARNED';
1842 l_act_util_rec.adjustment_type_id := -9;
1843 l_act_util_rec.cust_account_id := l_parent_source_rec.cust_account_id;
1844 l_act_util_rec.gl_posted_flag := l_parent_source_rec.gl_posted_flag;
1845 l_act_util_rec.orig_utilization_id := l_parent_source_rec.orig_utilization_id;
1846 l_act_util_rec.org_id := l_parent_source_rec.org_id;
1847 l_act_util_rec.fund_request_currency_code := l_parent_source_rec.fund_request_currency_code;
1848 --Bug8712883
1849 l_act_util_rec.plan_currency_code := l_parent_source_rec.plan_currency_code;
1850 -- Bug 13112685
1851 l_act_util_rec.object_type := l_parent_source_rec.object_type;
1852 l_act_util_rec.object_id := l_parent_source_rec.object_id;
1853 l_act_util_rec.order_line_id := l_parent_source_rec.order_line_id;
1854
1855 ozf_fund_adjustment_pvt.process_act_budgets (x_return_status => l_return_status,
1856 x_msg_count => x_msg_count,
1857 x_msg_data => x_msg_data,
1858 p_act_budgets_rec => l_act_budgets_rec,
1859 p_act_util_rec =>l_act_util_rec,
1860 x_act_budget_id => l_act_budget_id
1861 ) ;
1862
1863 IF l_return_status = fnd_api.g_ret_sts_error THEN
1864 RAISE fnd_api.g_exc_error;
1865 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1866 RAISE fnd_api.g_exc_unexpected_error;
1867 END IF;
1868 --END IF;
1869 END LOOP;
1870
1871 CLOSE c_parent_source_fund;
1872
1873 fnd_msg_pub.count_and_get (
1874 p_encoded=> fnd_api.g_false
1875 ,p_count=> x_msg_count
1876 ,p_data=> x_msg_data
1877 );
1878 IF G_DEBUG THEN
1879 ozf_utility_pvt.debug_message ( l_full_name || ': end');
1880 END IF;
1881
1882 EXCEPTION
1883 WHEN fnd_api.g_exc_error THEN
1884 ROLLBACK TO reconcile_budget_utilized;
1885 x_return_status := fnd_api.g_ret_sts_error;
1886 fnd_msg_pub.count_and_get (
1887 p_count=> x_msg_count
1888 ,p_data=> x_msg_data
1889 ,p_encoded=> fnd_api.g_false
1890 );
1891 WHEN fnd_api.g_exc_unexpected_error THEN
1892 ROLLBACK TO reconcile_budget_utilized;
1893 x_return_status := fnd_api.g_ret_sts_unexp_error;
1894 fnd_msg_pub.count_and_get (
1895 p_count=> x_msg_count
1896 ,p_data=> x_msg_data
1897 ,p_encoded=> fnd_api.g_false
1898 );
1899 WHEN OTHERS THEN
1900 ROLLBACK TO reconcile_budget_utilized;
1901 x_return_status := fnd_api.g_ret_sts_unexp_error;
1902
1903 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1904 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1905 END IF;
1906
1907 fnd_msg_pub.count_and_get (
1908 p_count=> x_msg_count
1909 ,p_data=> x_msg_data
1910 ,p_encoded=> fnd_api.g_false
1911 );
1912 END reconcile_budget_utilized;
1913
1914
1915 /*****************************************************************************************/
1916 -- Start of Comments
1917 -- NAME
1918 -- Reconcile_line
1919 -- PURPOSE
1920 -- This API is called from the java layer from the reconcile button on budget_sourcing screen
1921 -- It releases all th ebudget that was requested from a fund to the respective fund by creating transfer records
1922 -- and negative committment.
1923 -- HISTORY
1924 -- 10/08/2002 feliu CREATED
1925 ---------------------------------------------------------------------
1926
1927 PROCEDURE reconcile_line (
1928 p_budget_used_by_id IN NUMBER
1929 ,p_budget_used_by_type IN VARCHAR2
1930 ,p_object_currency IN VARCHAR2
1931 ,p_from_paid IN VARCHAR2
1932 ,p_api_version IN NUMBER
1933 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1934 ,p_commit IN VARCHAR2 := fnd_api.g_false
1935 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1936 ,x_return_status OUT NOCOPY VARCHAR2
1937 ,x_msg_count OUT NOCOPY NUMBER
1938 ,x_msg_data OUT NOCOPY VARCHAR2
1939 ) IS
1940 l_api_version CONSTANT NUMBER := 1.0;
1941 l_api_name CONSTANT VARCHAR2 (50) := 'reconcile_line';
1942 l_full_name CONSTANT VARCHAR2 (80) := g_pkg_name
1943 || '.'
1944 || l_api_name;
1945 l_return_status VARCHAR2 (1);
1946 l_msg_data VARCHAR2 (10000);
1947 l_msg_count NUMBER;
1948
1949 BEGIN
1950 SAVEPOINT reconcile_line;
1951 IF G_DEBUG THEN
1952 ozf_utility_pvt.debug_message (': begin ' || l_full_name);
1953 END IF;
1954 x_return_status := fnd_api.g_ret_sts_success;
1955
1956 IF fnd_api.to_boolean (p_init_msg_list) THEN
1957 fnd_msg_pub.initialize;
1958 END IF;
1959
1960 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1961 RAISE fnd_api.g_exc_unexpected_error;
1962 END IF;
1963
1964 IF p_from_paid = 'Y' THEN
1965
1966 reconcile_budget_utilized (
1967 p_budget_used_by_id=> p_budget_used_by_id
1968 ,p_budget_used_by_type=> p_budget_used_by_type
1969 ,p_object_currency=> p_object_currency
1970 ,p_api_version=> l_api_version
1971 ,x_return_status=> l_return_status
1972 ,x_msg_count=> l_msg_count
1973 ,x_msg_data=> l_msg_data
1974 );
1975
1976 IF l_return_status = fnd_api.g_ret_sts_error THEN
1977 RAISE fnd_api.g_exc_error;
1978 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1979 RAISE fnd_api.g_exc_unexpected_error;
1980 END IF;
1981
1982 END IF;
1983
1984 reconcile_budget_line (
1985 p_budget_used_by_id=> p_budget_used_by_id
1986 ,p_budget_used_by_type=> p_budget_used_by_type
1987 ,p_object_currency=> p_object_currency
1988 ,p_api_version=> l_api_version
1989 ,x_return_status=> l_return_status
1990 ,x_msg_count=> l_msg_count
1991 ,x_msg_data=> l_msg_data
1992 );
1993
1994 IF l_return_status = fnd_api.g_ret_sts_error THEN
1995 RAISE fnd_api.g_exc_error;
1996 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1997 RAISE fnd_api.g_exc_unexpected_error;
1998 END IF;
1999
2000 fnd_msg_pub.count_and_get (
2001 p_encoded=> fnd_api.g_false
2002 ,p_count=> x_msg_count
2003 ,p_data=> x_msg_data
2004 );
2005
2006 IF G_DEBUG THEN
2007 ozf_utility_pvt.debug_message ( l_full_name || ': end');
2008 END IF;
2009 EXCEPTION
2010 WHEN fnd_api.g_exc_error THEN
2011 ROLLBACK TO reconcile_line;
2012 x_return_status := fnd_api.g_ret_sts_error;
2013 fnd_msg_pub.count_and_get (
2014 p_count=> x_msg_count
2015 ,p_data=> x_msg_data
2016 ,p_encoded=> fnd_api.g_false
2017 );
2018 WHEN fnd_api.g_exc_unexpected_error THEN
2019 ROLLBACK TO reconcile_line;
2020 x_return_status := fnd_api.g_ret_sts_unexp_error;
2021 fnd_msg_pub.count_and_get (
2022 p_count=> x_msg_count
2023 ,p_data=> x_msg_data
2024 ,p_encoded=> fnd_api.g_false
2025 );
2026 WHEN OTHERS THEN
2027 ROLLBACK TO reconcile_line;
2028 x_return_status := fnd_api.g_ret_sts_unexp_error;
2029
2030 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2031 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2032 END IF;
2033
2034 fnd_msg_pub.count_and_get (
2035 p_count=> x_msg_count
2036 ,p_data=> x_msg_data
2037 ,p_encoded=> fnd_api.g_false
2038 );
2039 END reconcile_line;
2040
2041
2042 /*****************************************************************************************/
2043 -- Start of Comments
2044 -- NAME
2045 -- get_query
2046 -- PURPOSE
2047 -- The API generates the Personalized Query
2048 -- HISTORY
2049 -- 09/09/2003 niprakas CREATED
2050 ---------------------------------------------------------------------
2051 FUNCTION get_query(p_query_id IN NUMBER)
2052 RETURN VARCHAR2
2053 AS
2054 l_str_query VARCHAR2(10000);
2055 l_column VARCHAR2(100);
2056 l_operator VARCHAR2(100);
2057 l_resource_id NUMBER;
2058 l_value VARCHAR2(100);
2059 i NUMBER;
2060 l_query_id NUMBER := p_query_id;
2061 l_api_name CONSTANT VARCHAR2(30)
2062 := 'get_query';
2063 l_full_name CONSTANT VARCHAR2(60)
2064 := g_pkg_name || '.' || l_api_name;
2065 CURSOR c_query_parameters IS
2066 SELECT DECODE(PARAMETER_NAME,'parentId', 'parent_fund_id','statusId','status_code','num','fund_number','typeId',
2067 'fund_type','name','short_name','startDate','start_date_active','endDate','end_date_active','currency','currency_code_tc',
2068 'ownerId','owner','startPeriodName','start_period_name','endPeriodName','end_period_name','categoryId','category_id',
2069 'baseQueryType','baseQueryType','1'), DECODE(PARAMETER_CONDITION,'CONS','LIKE',PARAMETER_CONDITION),PARAMETER_VALUE
2070 FROM JTF_PERZ_QUERY_PARAM WHERE QUERY_ID = l_query_id AND PARAMETER_TYPE = 'condition';
2071 BEGIN
2072 IF G_DEBUG THEN
2073 ozf_utility_pvt.write_conc_log(l_full_name || ': start');
2074 END IF;
2075 l_str_query := 'SELECT fund_id FROM ozf_fund_details_v, ams_act_access_denorm accd WHERE ';
2076
2077 IF l_query_id IS NOT NULL THEN
2078 OPEN c_query_parameters;
2079 LOOP
2080 FETCH c_query_parameters INTO l_column,l_operator,l_value;
2081 IF c_query_parameters%NOTFOUND THEN
2082 EXIT;
2083 END IF;
2084
2085 IF l_operator is not NULL and l_column <> 'baseQueryType' and l_column <> '1' THEN
2086 --kdass 08-MAR-2004 added for date conversion from long format
2087 IF (l_column = 'start_date_active' OR l_column = 'end_date_active') THEN
2088 l_value := TO_DATE('01/01/1970','DD/MM/YYYY') + ROUND(l_value / 86400000);
2089 END IF;
2090
2091 IF l_operator = 'LIKE' THEN
2092 l_str_query := l_str_query || ' ' || 'UPPER' || '(' || l_column || ')';
2093 l_str_query := l_str_query || ' ' || l_operator || ' ';
2094 l_str_query := l_str_query || 'UPPER(''%' || l_value || '%'')' || ' ' || 'AND ';
2095 ELSIF l_operator = 'EW' THEN
2096 l_str_query := l_str_query || ' ' || 'UPPER' || '(' || l_column || ')';
2097 l_str_query := l_str_query || ' LIKE ';
2098 l_str_query := l_str_query || 'UPPER(''%' || l_value || ''')' || ' ' || 'AND ';
2099 ELSIF l_operator = 'SW' THEN
2100 l_str_query := l_str_query || ' ' || 'UPPER' || '(' || l_column || ')';
2101 l_str_query := l_str_query || ' LIKE ';
2102 l_str_query := l_str_query || 'UPPER(''' || l_value || '%'')' || ' ' || 'AND ';
2103 ELSIF l_operator = 'BW1' THEN
2104 l_str_query := l_str_query || ' ' || 'UPPER' || '(' || l_column || ')';
2105 l_str_query := l_str_query || ' >= ';
2106 l_str_query := l_str_query || l_value || ' AND ';
2107 ELSIF l_operator = 'BW2' THEN
2108 l_str_query := l_str_query || ' ' || 'UPPER' || '(' || l_column || ')';
2109 l_str_query := l_str_query || ' <= ';
2110 l_str_query := l_str_query || l_value || ' AND ';
2111 ELSE
2112 l_str_query := l_str_query || ' ' || l_column;
2113 l_str_query := l_str_query || ' ' || l_operator || ' ';
2114 l_str_query := l_str_query || '''' || l_value || '''' || ' '|| 'AND ' ;
2115 END IF;
2116 END IF;
2117 END LOOP;
2118 CLOSE c_query_parameters;
2119 END IF;
2120 l_resource_id := ozf_utility_pvt.get_resource_id(p_user_id => fnd_global.user_id);
2121 IF G_DEBUG THEN
2122 ozf_utility_pvt.write_conc_log(l_full_name || ': The resource Id ' || l_resource_id);
2123 END IF;
2124 l_str_query := l_str_query || 'fund_id = accd.object_id '|| ' AND ';
2125 l_str_query := l_str_query || 'accd.object_Type= ''FUND'' ' || ' AND ';
2126 l_str_query := l_str_query || 'accd.edit_metrics_yn = ''Y'' ';
2127 l_str_query := l_str_query || ' AND ';
2128 l_str_query := l_str_query || 'accd.resource_id=' || l_resource_id;
2129 IF G_DEBUG THEN
2130 ams_utility_pvt.write_conc_log(l_full_name || ': The Personalized SQL formed ' || l_str_query);
2131 END IF;
2132 return l_str_query;
2133 /*EXCEPTION
2134 WHEN others THEN
2135 dbms_output.put_line('The other ' || sqlerrm);
2136 */
2137 END get_query;
2138 /*****************************************************************************************/
2139 -- Start of Comments
2140 -- NAME
2141 -- transferring_unutilized_amount
2142 -- PURPOSE
2143 -- The API transfers the unutilized committed amount of old budgets to the newly
2144 -- created budgets
2145 -- HISTORY
2146 -- 09/09/2003 niprakas CREATED
2147 ---------------------------------------------------------------------
2148 procedure transferring_unutilized_amount(
2149 p_api_version IN NUMBER
2150 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2151 ,p_commit IN VARCHAR2 := fnd_api.g_false
2152 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2153 , x_return_status OUT NOCOPY VARCHAR2
2154 ,x_msg_count OUT NOCOPY NUMBER
2155 ,x_msg_data OUT NOCOPY VARCHAR2
2156 ,p_fund_id IN NUMBER
2157 ,p_prev_year_fund_id IN NUMBER
2158 ,p_original_budget IN NUMBER -- for the new budget
2159 ,p_fund_currency IN VARCHAR2 -- for the new budget
2160 )
2161 IS
2162 l_fund_id NUMBER := p_fund_id;
2163 l_prev_year_fund_id NUMBER := p_prev_year_fund_id;
2164 l_api_version CONSTANT NUMBER := 1.0;
2165 l_api_name CONSTANT VARCHAR2 (50) := 'transferring_unutilized_amount';
2166 l_full_name CONSTANT VARCHAR2(60)
2167 := g_pkg_name || '.' || l_api_name;
2168 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
2169 l_util_rec ozf_actbudgets_pvt.act_util_rec_type;
2170 l_original_budget NUMBER := p_original_budget;
2171 l_committed_amt NUMBER;
2172 l_earned_amt NUMBER;
2173 l_msg_count NUMBER;
2174 l_return_status VARCHAR2(30);
2175 l_approved_in_currency VARCHAR2(30);
2176 l_act_budget_used_by VARCHAR2(30);
2177 l_msg_data VARCHAR2(30);
2178 l_act_budget_id NUMBER;
2179 l_profile_value VARCHAR2(10) := NVL(FND_PROFILE.value('OZF_COMM_BUDGET_EXCEED'),'N');
2180 l_message VARCHAR2(500);
2181 l_status_code VARCHAR2(30);
2182
2183 -- getting the old budgets amount details ....
2184 CURSOR c_get_fund_details IS
2185 SELECT committed_amt,earned_amt
2186 from OZF_FUNDS_ALL_B where fund_id = l_prev_year_fund_id;
2187
2188 -- to get the objects associated with old budget
2189 CURSOR c_get_old_fund_obj(p_fund_id IN NUMBER) IS
2190 SELECT object_type, object_id
2191 ,NVL(committed_amt,0) - NVL(earned_amt,0) amount
2192 ,NVL(plan_curr_committed_amt,0) - NVL(plan_curr_utilized_amt,0) plan_curr_amount
2193 ,fund_currency,object_currency
2194 FROM ozf_object_fund_summary
2195 WHERE fund_id = p_fund_id;
2196
2197 CURSOR c_campaign(p_object_id IN NUMBER) IS
2198 SELECT status_code
2199 FROM ams_campaigns_vl
2200 WHERE campaign_id = p_object_id;
2201
2202 CURSOR c_campaign_schl(p_object_id IN NUMBER) IS
2203 SELECT status_code
2204 FROM ams_campaign_schedules_vl
2205 WHERE schedule_id = p_object_id;
2206
2207 CURSOR c_eheader(p_object_id IN NUMBER) IS
2208 SELECT system_status_code
2209 FROM ams_event_headers_vl
2210 WHERE event_header_id = p_object_id;
2211
2212 CURSOR c_eoffer(p_object_id IN NUMBER) IS
2213 SELECT system_status_code
2214 FROM ams_event_offers_vl
2215 WHERE event_offer_id = p_object_id;
2216
2217 CURSOR c_deliverable(p_object_id IN NUMBER) IS
2218 SELECT status_code
2219 FROM ams_deliverables_vl
2220 WHERE deliverable_id = p_object_id;
2221
2222 CURSOR c_offer(p_object_id IN NUMBER) IS
2223 SELECT status_code
2224 FROM ozf_offers
2225 WHERE qp_list_header_id = p_object_id;
2226
2227 l_old_fund_obj c_get_old_fund_obj%ROWTYPE;
2228
2229 BEGIN
2230 IF G_DEBUG THEN
2231 ozf_utility_pvt.write_conc_log(l_full_name || ': start');
2232 END IF;
2233 OPEN c_get_fund_details;
2234 -- LOOP
2235 FETCH c_get_fund_details into
2236 l_committed_amt,l_earned_amt;
2237 -- EXIT WHEN c_get_fund_details%NOTFOUND;
2238 -- END LOOP;
2239 CLOSE c_get_fund_details;
2240
2241 --- Transfer unutilized amount if
2242 --1. New budget's original amount is more than unutilized amount.
2243 --2. Or profile 'OZF_COMM_BUDGET_EXCEED' is 'Y'.
2244 --3. only transfer if object's status is in 'ACTIVE'.IF object status is in completed, use reconcile function
2245 -- to transfer back budget.
2246 IF NVL(l_original_budget,0) > (NVL(l_committed_amt,0) - NVL(l_earned_amt,0)) OR
2247 l_profile_value = 'Y' THEN
2248
2249 IF G_DEBUG THEN
2250 ozf_utility_pvt.write_conc_log('l_profile_value: ' || l_profile_value);
2251 END IF;
2252
2253 OPEN c_get_old_fund_obj(l_prev_year_fund_id);
2254 LOOP
2255 FETCH c_get_old_fund_obj into l_old_fund_obj;
2256 EXIT WHEN c_get_old_fund_obj%NOTFOUND;
2257
2258 -- get status of object:
2259 IF l_old_fund_obj.object_type = 'OFFR' THEN
2260 OPEN c_offer(l_old_fund_obj.object_id);
2261 FETCH c_offer INTO l_status_code;
2262 CLOSE c_offer;
2263 ELSIF l_old_fund_obj.object_type = 'CAMP' THEN
2264 OPEN c_campaign(l_old_fund_obj.object_id);
2265 FETCH c_campaign INTO l_status_code;
2266 CLOSE c_campaign;
2267 -- Campaign Schdules
2268 ELSIF l_old_fund_obj.object_type = 'CSCH' THEN
2269 OPEN c_campaign_schl(l_old_fund_obj.object_id);
2270 FETCH c_campaign_schl INTO l_status_code;
2271 CLOSE c_campaign_schl;
2272 -- Event Header/Rollup Event
2273 ELSIF l_old_fund_obj.object_type = 'EVEH' THEN
2274 OPEN c_eheader(l_old_fund_obj.object_id);
2275 FETCH c_eheader INTO l_status_code;
2276 CLOSE c_eheader;
2277 -- Event Offer/Execution Event
2278 ELSIF l_old_fund_obj.object_type IN ('EONE','EVEO') THEN
2279 OPEN c_eoffer(l_old_fund_obj.object_id);
2280 FETCH c_eoffer INTO l_status_code;
2281 CLOSE c_eoffer;
2282 -- Deliverable
2283 ELSIF l_old_fund_obj.object_type = 'DELV' THEN
2284 OPEN c_deliverable(l_old_fund_obj.object_id);
2285 FETCH c_deliverable INTO l_status_code;
2286 CLOSE c_deliverable;
2287 -- making the tem variable status_code = ACTIVE to make a cleaner code
2288 IF l_status_code = 'AVAILABLE' THEN
2289 l_status_code := 'ACTIVE';
2290 END IF;
2291 END IF;
2292
2293 IF NVL (l_old_fund_obj.amount, 0) > 0 AND l_status_code = 'ACTIVE' THEN
2294 l_act_budgets_rec :=NULL;
2295 l_act_budgets_rec.act_budget_used_by_id := l_prev_year_fund_id;
2296 l_act_budgets_rec.arc_act_budget_used_by := 'FUND';
2297 l_act_budgets_rec.budget_source_type := l_old_fund_obj.object_type;
2298 l_act_budgets_rec.budget_source_id := l_old_fund_obj.object_id;
2299 l_act_budgets_rec.transaction_type := 'DEBIT';
2300 l_act_budgets_rec.transfer_type := 'TRANSFER';
2301 l_act_budgets_rec.request_amount := l_old_fund_obj.amount;
2302 l_act_budgets_rec.request_currency := l_old_fund_obj.fund_currency;
2303 l_act_budgets_rec.request_date := SYSDATE;
2304 l_act_budgets_rec.status_code := 'APPROVED';
2305 l_act_budgets_rec.user_status_id :=
2306 ozf_utility_pvt.get_default_user_status (
2307 'OZF_BUDGETSOURCE_STATUS'
2308 ,l_act_budgets_rec.status_code
2309 );
2310
2311 l_act_budgets_rec.approved_amount := l_old_fund_obj.amount; -- in arc_Act_used_by currency
2312 /* l_object_currency := ozf_actbudgets_pvt.get_object_currency(
2313 l_old_fund_obj.object_type
2314 ,l_old_fund_obj.object_id
2315 ,x_return_status);
2316 ozf_utility_pvt.convert_currency (
2317 x_return_status=> x_return_status
2318 ,p_from_currency=> p_fund_currency
2319 ,p_to_currency=> l_object_currency
2320 ,p_from_amount=> l_old_fund_obj.total_amount
2321 ,x_to_amount=> l_object_curr_amount
2322 );
2323
2324 IF x_return_status = fnd_api.g_ret_sts_error THEN
2325 RAISE fnd_api.g_exc_error;
2326 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2327 RAISE fnd_api.g_exc_unexpected_error;
2328 END IF;
2329 */
2330
2331 l_act_budgets_rec.approved_in_currency := l_old_fund_obj.object_currency;
2332 l_act_budgets_rec.approved_original_amount := l_old_fund_obj.plan_curr_amount;
2333 l_act_budgets_rec.approval_date := SYSDATE;
2334 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
2335 l_act_budgets_rec.requester_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
2336 l_act_budgets_rec.justification := fnd_message.get_string ('OZF', 'OZF_FUND_RECONCILE');
2337
2338 IF G_DEBUG THEN
2339 ozf_utility_pvt.write_conc_log('Create transfer record for original budget: ' || l_old_fund_obj.amount);
2340 END IF;
2341
2342 ozf_actbudgets_pvt.create_act_budgets (
2343 p_api_version=> l_api_version
2344 ,x_return_status=> l_return_status
2345 ,x_msg_count=> l_msg_count
2346 ,x_msg_data=> l_msg_data
2347 ,p_act_budgets_rec=> l_act_budgets_rec
2348 ,x_act_budget_id=> l_act_budget_id
2349 );
2350
2351 IF l_return_status = fnd_api.g_ret_sts_error THEN
2352 RAISE fnd_api.g_exc_error;
2353 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2354 RAISE fnd_api.g_exc_unexpected_error;
2355 END IF;
2356
2357 IF G_DEBUG THEN
2358 ozf_utility_pvt.write_conc_log(l_full_name || ' New entries created for unutilized committed amount for old budget, fund_id '
2359 || l_prev_year_fund_id );
2360 END IF;
2361
2362 ozf_utility_pvt.write_conc_log(l_full_name || ' l_old_fund_obj.object_id ' || l_old_fund_obj.object_id);
2363
2364 -- creation of new budget request for new budgets
2365 l_act_budgets_rec.act_budget_used_by_id := l_old_fund_obj.object_id;
2366 l_act_budgets_rec.arc_act_budget_used_by := l_old_fund_obj.object_type;
2367 l_act_budgets_rec.budget_source_type := 'FUND';
2368 l_act_budgets_rec.budget_source_id := l_fund_id;
2369 l_act_budgets_rec.request_currency := l_old_fund_obj.object_currency;
2370 l_act_budgets_rec.request_amount :=l_old_fund_obj.plan_curr_amount;
2371 l_act_budgets_rec.request_date := SYSDATE;
2372 l_act_budgets_rec.status_code := 'APPROVED';
2373 l_act_budgets_rec.approved_amount := l_old_fund_obj.plan_curr_amount;
2374 l_act_budgets_rec.approved_original_amount := l_old_fund_obj.amount;
2375 l_act_budgets_rec.approved_in_currency := l_old_fund_obj.fund_currency;
2376 l_act_budgets_rec.approval_date := SYSDATE;
2377 l_act_budgets_rec.justification := fnd_message.get_string ('OZF', 'OZF_FUND_MASS_TRANSFER');
2378 -- Here the trasnsfer type would be request ....
2379 l_act_budgets_rec.transfer_type := 'REQUEST';
2380 -- This would create the objects for new budgets also which
2381 -- were associated with the old budget ...
2382
2383 IF G_DEBUG THEN
2384 ozf_utility_pvt.write_conc_log( 'Create budget request for new budget: ' || l_old_fund_obj.plan_curr_amount);
2385 END IF;
2386
2387 ozf_actbudgets_pvt.create_act_budgets(
2388 p_api_version=> l_api_version
2389 ,x_return_status=> l_return_status
2390 ,x_msg_count=> x_msg_count
2391 ,x_msg_data=> x_msg_data
2392 ,p_act_budgets_rec=> l_act_budgets_rec
2393 ,p_act_util_rec=> l_util_rec
2394 ,x_act_budget_id=> l_act_budget_id
2395 ,p_approval_flag=> fnd_api.g_true
2396 );
2397
2398 IF G_DEBUG THEN
2399 ozf_utility_pvt.write_conc_log( 'l_return_status for create_act_budgets: ' || l_return_status);
2400 END IF;
2401
2402 IF l_return_status = fnd_api.g_ret_sts_error THEN
2403 RAISE fnd_api.g_exc_error;
2404 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2405 RAISE fnd_api.g_exc_unexpected_error;
2406 END IF;
2407
2408 IF G_DEBUG THEN
2409 ozf_utility_pvt.write_conc_log(l_full_name || ' New entries created for new budgets and unutilized committed amount is transferred '
2410 || ' for the new budget ' || l_fund_id);
2411 ozf_utility_pvt.write_conc_log(l_full_name || ' The new object created with id ' || l_old_fund_obj.object_id
2412 || ' and of type ' || l_old_fund_obj.object_type);
2413 END IF;
2414
2415 END IF; --- NVL (l_old_fund_obj.total_amount, 0) > 0
2416
2417
2418 END LOOP;
2419
2420 CLOSE c_get_old_fund_obj;
2421 ELSE
2422 l_message := fnd_message.get_string ('OZF', 'OZF_FUND_NO_MASS_TRANS') || fnd_global.local_chr(10);
2423 ozf_utility_pvt.write_conc_log(l_message || l_fund_id);
2424 END IF; -- The main IF loop
2425
2426 x_return_status := fnd_api.g_ret_sts_success;
2427
2428 fnd_msg_pub.count_and_get(
2429 p_encoded => fnd_api.g_false
2430 ,p_count => x_msg_count
2431 ,p_data => x_msg_data);
2432 EXCEPTION
2433 WHEN fnd_api.g_exc_error THEN
2434 x_return_status := fnd_api.g_ret_sts_error;
2435 fnd_msg_pub.count_and_get(
2436 p_encoded => fnd_api.g_false
2437 ,p_count => x_msg_count
2438 ,p_data => x_msg_data);
2439 WHEN fnd_api.g_exc_unexpected_error THEN
2440 x_return_status := fnd_api.g_ret_sts_unexp_error;
2441 fnd_msg_pub.count_and_get(
2442 p_encoded => fnd_api.g_false
2443 ,p_count => x_msg_count
2444 ,p_data => x_msg_data);
2445 WHEN OTHERS THEN
2446 x_return_status := fnd_api.g_ret_sts_unexp_error;
2447 fnd_msg_pub.count_and_get(
2448 p_encoded => fnd_api.g_false
2449 ,p_count => x_msg_count
2450 ,p_data => x_msg_data);
2451 END transferring_unutilized_amount;
2452 /*****************************************************************************************/
2453 -- Start of Comments
2454 -- NAME
2455 -- create_new_funds
2456 -- PURPOSE
2457 -- The API creates new funds
2458 -- created budgets
2459 -- HISTORY
2460 -- 09/09/2003 niprakas CREATED
2461 ---------------------------------------------------------------------
2462
2463 procedure create_new_funds(
2464 p_api_version IN NUMBER
2465 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2466 ,p_commit IN VARCHAR2 := fnd_api.g_false
2467 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2468 ,x_return_status OUT NOCOPY VARCHAR2
2469 ,x_msg_count OUT NOCOPY NUMBER
2470 ,x_msg_data OUT NOCOPY VARCHAR2
2471 ,p_fund_rec IN OZF_FUNDS_ALL_VL%ROWTYPE,
2472 x_new_fund_id OUT NOCOPY NUMBER
2473 )
2474 IS
2475
2476 l_api_version CONSTANT NUMBER := 1.0;
2477 l_api_name CONSTANT VARCHAR2(30)
2478 := 'create_new_funds';
2479 l_full_name CONSTANT VARCHAR2(60)
2480 := g_pkg_name || '.' || l_api_name;
2481 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
2482 l_msg_count NUMBER;
2483 l_msg_data VARCHAR2(10000);
2484
2485 l_fund_rec OZF_FUNDS_ALL_VL%ROWTYPE := p_fund_rec;
2486 l_fund_rec_type OZF_Funds_PVT.fund_rec_type;
2487 l_start_date_active Date;
2488 l_end_date_active date;
2489 -- l_return_status Varchar2(30);
2490 l_fund_id NUMBER;
2491 l_new_fund_id NUMBER;
2492 l_user_status_code VARCHAR2(30) := 'DRAFT';
2493 l_user_status_type VARCHAR2(30) := 'OZF_FUND_STATUS';
2494 l_user_status_id VARCHAR2(30);
2495 l_org_id VARCHAR2(30);
2496 l_errcode VARCHAR2(80);
2497 l_errnum NUMBER;
2498 l_errmsg VARCHAR2(3000);
2499
2500 BEGIN
2501
2502 SAVEPOINT create_new_funds;
2503
2504 IF G_DEBUG THEN
2505 ozf_utility_pvt.write_conc_log(l_full_name || ': start');
2506 END IF;
2507
2508 IF fnd_api.to_boolean(p_init_msg_list) THEN
2509 fnd_msg_pub.initialize;
2510 END IF;
2511
2512 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2513 RAISE fnd_api.g_exc_unexpected_error;
2514 END IF;
2515
2516 x_return_status := fnd_api.g_ret_sts_success;
2517
2518 l_fund_rec_type.parent_fund_id := l_fund_rec.parent_fund_id;
2519 l_fund_rec_type.status_code := l_fund_rec.status_code;
2520 l_fund_rec_type.original_budget := l_fund_rec.original_budget;
2521 l_fund_rec_type.prev_fund_id := l_fund_rec.fund_id;
2522 l_fund_rec_type.category_id := l_fund_rec.category_id;
2523 l_fund_rec_type.fund_type := l_fund_rec.fund_type;
2524 l_fund_rec_type.user_status_id := OZF_Utility_PVT.get_default_user_status
2525 (l_user_status_type,l_user_status_code);
2526 l_fund_rec_type.owner := l_fund_rec.owner;
2527 l_fund_rec_type.custom_setup_id := l_fund_rec.custom_setup_id;
2528 l_start_date_active := l_fund_rec.start_date_active;
2529 l_end_date_active := l_fund_rec.end_date_active;
2530 l_fund_rec_type.start_date_active := l_fund_rec.end_date_active + 1;
2531 l_fund_rec_type.end_date_active := l_fund_rec_type.start_date_active + (l_end_date_active - l_start_date_active);
2532 l_fund_rec_type.short_name := l_fund_rec.short_name;
2533 l_fund_rec_type.currency_code_tc := l_fund_rec.currency_code_tc;
2534 l_fund_rec_type.country_id := l_fund_rec.country_id;
2535 --25-APR-2006 fixed bug 5177593 initialized ledger_id
2536 l_fund_rec_type.ledger_id := l_fund_rec.ledger_id;
2537 l_fund_rec_type.org_id := l_fund_rec.org_id;
2538
2539 --asylvia 27-FEB-2006 - fixed bug 5057212 - copy business unit and other fields to next period budget
2540 l_fund_rec_type.business_unit_id := l_fund_rec.business_unit_id;
2541 l_fund_rec_type.accrued_liable_account := l_fund_rec.accrued_liable_account;
2542 l_fund_rec_type.ded_adjustment_account := l_fund_rec.ded_adjustment_account;
2543 l_fund_rec_type.threshold_id := l_fund_rec.threshold_id;
2544
2545
2546 IF G_DEBUG THEN
2547 ozf_utility_pvt.write_conc_log('l_fund_rec_type.parent_fund_id: ' || l_fund_rec_type.parent_fund_id);
2548 ozf_utility_pvt.write_conc_log('l_fund_rec_type.prev_fund_id: ' || l_fund_rec_type.prev_fund_id);
2549 ozf_utility_pvt.write_conc_log('l_fund_rec_type.start_date_active: ' || l_fund_rec_type.start_date_active);
2550 ozf_utility_pvt.write_conc_log('l_fund_rec_type.end_date_active: ' || l_fund_rec_type.end_date_active);
2551 ozf_utility_pvt.write_conc_log('l_fund_rec_type.ledger_id: ' || l_fund_rec_type.ledger_id);
2552 END IF;
2553
2554 OZF_funds_pvt.create_fund(p_api_version => 1.0
2555 ,p_init_msg_list => fnd_api.g_false
2556 ,p_commit => fnd_api.g_false
2557 ,p_validation_level => fnd_api.g_valid_level_full
2558 ,x_return_status => l_return_status
2559 ,x_msg_count => l_msg_count
2560 ,x_msg_data => l_msg_data
2561 ,p_fund_rec => l_fund_rec_type
2562 ,x_fund_id => l_fund_id);
2563
2564 x_return_status := l_return_status;
2565
2566 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2567 ROLLBACK TO create_new_funds;
2568 RETURN;
2569 END IF;
2570
2571 IF G_DEBUG THEN
2572 ozf_utility_pvt.write_conc_log('The new Fund Id created is ' || l_fund_id);
2573 END IF;
2574
2575 l_errcode := NULL;
2576 l_errnum := 0;
2577 l_errmsg := NULL;
2578
2579 /* To copy the market eligibility of old budget to new budget */
2580 ams_copyelements_pvt.copy_act_market_segments(p_src_act_type => 'FUND'
2581 ,p_src_act_id =>l_fund_rec.fund_id
2582 ,p_new_act_id =>l_fund_id
2583 ,p_errnum =>l_errnum
2584 ,p_errcode =>l_errcode
2585 ,p_errmsg =>l_errmsg
2586 );
2587 IF l_errcode IS NOT NULL THEN
2588 x_return_status := fnd_api.g_ret_sts_error;
2589 ROLLBACK TO create_new_funds;
2590 RAISE fnd_api.g_exc_error;
2591 RETURN;
2592 END IF;
2593
2594 /* To copy the products of old budget to the new budget */
2595 ams_copyelements_pvt.copy_act_prod(p_src_act_type => 'FUND'
2596 ,p_src_act_id =>l_fund_rec.fund_id
2597 ,p_new_act_id =>l_fund_id
2598 ,p_errnum =>l_errnum
2599 ,p_errcode =>l_errcode
2600 ,p_errmsg =>l_errmsg
2601 );
2602
2603 IF l_errcode IS NOT NULL THEN
2604 x_return_status := fnd_api.g_ret_sts_error;
2605 ROLLBACK TO create_new_funds;
2606 RAISE fnd_api.g_exc_error;
2607 RETURN;
2608 END IF;
2609
2610 x_new_fund_id := l_fund_id;
2611
2612 IF G_DEBUG THEN
2613 ozf_utility_pvt.debug_message(l_full_name || ': end');
2614 END IF;
2615
2616 END create_new_funds;
2617
2618
2619 /*****************************************************************************************/
2620 -- Start of Comments
2621 -- NAME
2622 -- get_new_funds
2623 -- PURPOSE
2624 -- The API gets all the newly created funds for the corresponding fund.
2625 -- HISTORY
2626 -- 09/09/2003 niprakas CREATED
2627 ---------------------------------------------------------------------
2628
2629 procedure get_new_funds(
2630 p_api_version IN NUMBER
2631 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2632 ,p_commit IN VARCHAR2 := fnd_api.g_false
2633 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2634 , x_return_status OUT NOCOPY VARCHAR2
2635 ,x_msg_count OUT NOCOPY NUMBER
2636 ,x_msg_data OUT NOCOPY VARCHAR2
2637 )
2638
2639 IS
2640 l_object_version NUMBER;
2641 l_new_user_status_id NUMBER;
2642 l_new_fund_rec OZF_FUNDS_ALL_VL%ROWTYPE;
2643 l_new_fund_rec_type OZF_Funds_PVT.fund_rec_type;
2644 l_fund_rec OZF_Funds_PVT.fund_rec_type;
2645 l_return_status VARCHAR2(30) := fnd_api.g_ret_sts_success;
2646 l_api_name CONSTANT VARCHAR2(30)
2647 := 'get_new_funds';
2648 l_full_name CONSTANT VARCHAR2(60)
2649 := g_pkg_name || '.' || l_api_name;
2650 l_new_user_status_type VARCHAR2(30) := 'OZF_FUND_STATUS';
2651 l_new_user_status_code VARCHAR2(30) := 'ACTIVE';
2652 l_init_msg_list VARCHAR2(30);
2653 l_commit VARCHAR2(30);
2654 l_validation_level NUMBER;
2655 l_msg_count NUMBER;
2656 l_msg_data VARCHAR2(30);
2657 l_mode VARCHAR2(30);
2658 l_end_date DATE;
2659 -- get all the newly created budgets
2660 CURSOR c_get_new_funds IS
2661 SELECT * FROM OZF_FUNDS_ALL_VL
2662 WHERE PREV_FUND_ID IS NOT NULL
2663 AND TRANSFERED_FLAG IS NULL
2664 AND STATUS_CODE IN('ACTIVE','DRAFT');
2665
2666 --- get end date for previous fund.
2667 CURSOR c_prec_fund (p_fund_id IN NUMBER) IS
2668 SELECT end_date_active,object_version_number
2669 FROM OZF_FUNDS_ALL_VL
2670 WHERE fund_id = p_fund_id;
2671
2672 BEGIN
2673
2674 x_return_status := fnd_api.g_ret_sts_success;
2675
2676 OPEN c_get_new_funds;
2677 LOOP
2678 SAVEPOINT new_budget;
2679
2680 IF G_DEBUG THEN
2681 ozf_utility_pvt.write_conc_log(l_full_name || ' Getting the newly Created Budgets ');
2682 END IF;
2683
2684 FETCH c_get_new_funds INTO l_new_fund_rec;
2685
2686 IF c_get_new_funds%NOTFOUND THEN
2687 EXIT;
2688 END IF;
2689
2690 OPEN c_prec_fund(l_new_fund_rec.prev_fund_id);
2691 FETCH c_prec_fund into l_end_date,l_object_version;
2692 CLOSE c_prec_fund;
2693
2694 IF G_DEBUG THEN
2695 ozf_utility_pvt.write_conc_log(l_full_name || ' The fund_id of newly created budget is ' || l_new_fund_rec.fund_id);
2696 ozf_utility_pvt.write_conc_log(l_full_name || ' status code ' || l_new_fund_rec.status_code);
2697 END IF;
2698
2699 IF l_end_date < TRUNC(SYSDATE) THEN
2700
2701 -- activate budget if status is still in DRAFT and the end date for the previous budget passed sysdate.
2702 IF l_new_fund_rec.status_code = 'DRAFT' THEN
2703 l_new_fund_rec_type.fund_id := l_new_fund_rec.fund_id;
2704 l_new_fund_rec_type.user_status_id := OZF_Utility_PVT.get_default_user_status
2705 (l_new_user_status_type,l_new_user_status_code);
2706 l_new_fund_rec_type.original_budget := 0;
2707 l_new_fund_rec_type.status_code := 'ACTIVE';
2708 l_new_fund_rec_type.fund_usage := 'MTRAN';
2709 l_new_fund_rec_type.object_version_number := l_new_fund_rec.object_version_number;
2710 l_new_fund_rec_type.prev_fund_id := l_new_fund_rec.prev_fund_id;
2711
2712 IF G_DEBUG THEN
2713 ozf_utility_pvt.write_conc_log(l_full_name || ' update draft budget to active. ' || l_new_fund_rec_type.fund_id);
2714 END IF;
2715
2716 OZF_funds_pvt.update_fund(p_api_version => 1.0
2717 ,p_init_msg_list => FND_API.G_FALSE
2718 ,p_commit => FND_API.G_FALSE
2719 ,p_validation_level => fnd_api.g_valid_level_full
2720 ,x_return_status => l_return_status
2721 ,x_msg_count => l_msg_count
2722 ,x_msg_data => l_msg_data
2723 ,p_fund_rec => l_new_fund_rec_type
2724 ,p_mode => l_mode
2725 );
2726
2727 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
2728 GOTO end_loop;
2729 END IF;
2730
2731 IF G_DEBUG THEN
2732 ozf_utility_pvt.write_conc_log(l_full_name || 'Status Updated for the fund ' || l_new_fund_rec.fund_id || ' without notifying workflow');
2733 END IF;
2734
2735 END IF; --end of l_new_fund_rec.status_code = 'DRAFT'
2736
2737 IF G_DEBUG THEN
2738 ozf_utility_pvt.write_conc_log(l_full_name || ' Just Before invoking the transfer fund API ');
2739 END IF;
2740
2741 -- transfer untilized amount from precious budget to new budget when new budget is ACTIVE
2742 -- and and the end date for the previous budget passed sysdate.
2743 -- if original_budget = 0, which is the case for activating budget above, no transfer happens. fix for R12.
2744
2745 IF l_new_fund_rec.original_budget <> 0 THEN
2746 transferring_unutilized_amount(p_api_version => 1.0
2747 ,p_init_msg_list => FND_API.G_FALSE
2748 ,p_commit => FND_API.G_FALSE
2749 ,p_validation_level => fnd_api.g_valid_level_full
2750 ,x_return_status => l_return_status
2751 ,x_msg_count => l_msg_count
2752 ,x_msg_data => l_msg_data
2753 ,p_fund_id => l_new_fund_rec.fund_id
2754 ,p_prev_year_fund_id => l_new_fund_rec.prev_fund_id
2755 ,p_original_budget => l_new_fund_rec.original_budget
2756 ,p_fund_currency =>l_new_fund_rec.currency_code_tc
2757 );
2758
2759 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
2760 ozf_utility_pvt.write_conc_log(' Transferring failed for the fund : '|| l_new_fund_rec.fund_id);
2761 ozf_utility_pvt.write_conc_log(l_msg_data);
2762 GOTO end_loop;
2763 END IF;
2764
2765 IF G_DEBUG THEN
2766 ozf_utility_pvt.write_conc_log(l_full_name || ' Transferring done for the fund ' || l_new_fund_rec.fund_id);
2767 END IF;
2768
2769 END IF; -- l_new_fund_rec.original_budget <> 0.
2770
2771 -- get new object version number.
2772 OPEN c_prec_fund(l_new_fund_rec.fund_id);
2773 FETCH c_prec_fund into l_end_date,l_object_version;
2774 CLOSE c_prec_fund;
2775
2776 IF G_DEBUG THEN
2777 ozf_utility_pvt.write_conc_log(l_full_name || ' l_object_version is ' || l_object_version);
2778 END IF;
2779
2780 -- set transfered_flag for new budget to 'Y'.
2781 l_fund_rec.fund_id := l_new_fund_rec.fund_id;
2782 l_fund_rec.object_version_number := l_object_version;
2783 l_fund_rec.TRANSFERED_FLAG := 'Y' ;
2784
2785 ozf_funds_pvt.update_fund(p_api_version=> 1.0
2786 ,p_init_msg_list=> fnd_api.g_false
2787 ,p_commit=> fnd_api.g_false
2788 ,p_validation_level=> fnd_api.g_valid_level_full
2789 ,x_return_status=> l_return_status
2790 ,x_msg_count=> x_msg_count
2791 ,x_msg_data=> x_msg_data
2792 ,p_fund_rec=> l_fund_rec
2793 ,p_mode=> l_mode
2794 );
2795
2796 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
2797 GOTO end_loop;
2798 END IF;
2799
2800 IF G_DEBUG THEN
2801 ozf_utility_pvt.write_conc_log(l_full_name || ' The transferred flag set to Yes ');
2802 END IF;
2803
2804 END IF; -- end of l_end_date.
2805
2806 <<end_loop>>
2807 IF l_return_status = fnd_api.g_ret_sts_success THEN
2808 COMMIT;
2809 ELSE
2810 ROLLBACK TO new_budget;
2811 END IF;
2812
2813 END LOOP;
2814
2815 CLOSE c_get_new_funds;
2816
2817 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false
2818 ,p_count => x_msg_count
2819 ,p_data => x_msg_data);
2820
2821 IF G_DEBUG THEN
2822 ozf_utility_pvt.debug_message(l_full_name || ': end');
2823 END IF;
2824
2825 EXCEPTION
2826 WHEN fnd_api.g_exc_error THEN
2827 ROLLBACK;
2828 x_return_status := fnd_api.g_ret_sts_error;
2829 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false
2830 ,p_count => x_msg_count
2831 ,p_data => x_msg_data);
2832 WHEN fnd_api.g_exc_unexpected_error THEN
2833 ROLLBACK;
2834 x_return_status := fnd_api.g_ret_sts_unexp_error;
2835 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false
2836 ,p_count => x_msg_count
2837 ,p_data => x_msg_data);
2838 WHEN OTHERS THEN
2839 ROLLBACK;
2840 x_return_status := fnd_api.g_ret_sts_unexp_error;
2841
2842 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2843 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2844 END IF;
2845
2846 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false
2847 ,p_count => x_msg_count
2848 ,p_data => x_msg_data);
2849
2850 END get_new_funds;
2851
2852 /*****************************************************************************************/
2853 -- Start of Comments
2854 -- NAME
2855 -- open_next_years_budget
2856 -- PURPOSE
2857 -- The API creates new budgets after the proper validation
2858 -- HISTORY
2859 -- 09/09/2003 niprakas CREATED
2860 -- 12/28/2004 feliu modified.
2861 ---------------------------------------------------------------------
2862 procedure open_next_years_budget (
2863 x_errbuf OUT NOCOPY VARCHAR2,
2864 x_retcode OUT NOCOPY NUMBER,
2865 p_query_id IN NUMBER,
2866 p_fund_id IN NUMBER,
2867 p_hierarchy_flag IN VARCHAR2,
2868 p_amount_flag IN VARCHAR2
2869 )
2870 IS
2871 l_api_name CONSTANT VARCHAR2(30)
2872 := 'open_next_years_budget';
2873 l_full_name CONSTANT VARCHAR2(60)
2874 := g_pkg_name || '.' || l_api_name;
2875 l_status_code VARCHAR2(30);
2876 l_init_msg_list VARCHAR2(30);
2877 l_commit VARCHAR2(30);
2878 l_validation_level NUMBER;
2879 l_mode VARCHAR2(30);
2880 l_count NUMBER;
2881 x_msg_count NUMBER;
2882 x_msg_data VARCHAR2(10000);
2883 x_return_status VARCHAR2(30);
2884 l_sql VARCHAR2(2000);
2885 l_query_id NUMBER := p_query_id;
2886 l_fund_id NUMBER := p_fund_id;
2887 l_hierarchy_flag VARCHAR2(30) := NVL(p_hierarchy_flag,'N');
2888 l_amount_flag VARCHAR2(30) := NVL(p_amount_flag,'N');
2889 -- to store the parent budget information
2890 l_fund_rec OZF_FUNDS_ALL_VL%ROWTYPE;
2891 l_fund_rec_type OZF_Funds_PVT.fund_rec_type;
2892 -- to store the child budget informations
2893 l_child_fund_rec OZF_FUNDS_ALL_VL%ROWTYPE;
2894 l_child_fund_rec_type OZF_Funds_PVT.fund_rec_type;
2895 l_start_date_active DATE;
2896 l_end_date_active DATE;
2897 l_parent_fund_id NUMBER;
2898 l_child_fund_id NUMBER;
2899 l_return_status VARCHAR2(30);
2900 -- root budget
2901 l_root_fund_id NUMBER;
2902 -- old budget fund id
2903 l_old_fund_id NUMBER;
2904 l_prev_fund_id NUMBER;
2905 l_child_prev_fund_id NUMBER;
2906 l_original_budget NUMBER;
2907 l_new_fund_id NUMBER;
2908 l_par_fund_id NUMBER;
2909 TYPE dyna_get_fund_id IS REF CURSOR;
2910 c_get_fund_id dyna_get_fund_id;
2911
2912 CURSOR c_get_fund_details(p_fund_id IN NUMBER) IS
2913 SELECT * FROM ozf_funds_all_vl
2914 WHERE fund_id = p_fund_id
2915 AND fund_id NOT IN (SELECT NVL(prev_fund_id,-99) FROM ozf_funds_all_b
2916 WHERE prev_fund_id = p_fund_id);
2917
2918 -- Gets the child budgets details
2919 CURSOR c_get_child_budget(p_fund_id IN NUMBER) IS
2920 SELECT * from ozf_funds_all_vl
2921 WHERE fund_id = p_fund_id
2922 AND status_code = 'ACTIVE'
2923 AND fund_id NOT IN (SELECT NVL(prev_fund_id,-99) FROM ozf_funds_all_b
2924 WHERE prev_fund_id = p_fund_id);
2925
2926 -- gets the next period budget for child's parent budget
2927 CURSOR c_get_parent_budget(p_fund_id IN NUMBER) IS
2928 SELECT fund_id FROM ozf_funds_all_b
2929 WHERE prev_fund_id = p_fund_id;
2930
2931 -- This cursor gets the budgets in the hierarchy which are
2932 -- active and do not have next years budget open
2933 CURSOR c_get_hierarchy_budgets(p_fund_id IN NUMBER) IS
2934 SELECT fund_id, parent_fund_id
2935 FROM ozf_funds_all_b
2936 WHERE prev_fund_id is NULL
2937 AND status_code = 'ACTIVE'
2938 CONNECT BY PRIOR fund_id = parent_fund_id
2939 START WITH parent_fund_id = p_fund_id;
2940
2941 BEGIN
2942
2943 IF G_DEBUG THEN
2944 ozf_utility_pvt.write_conc_log(l_full_name || ': start');
2945 ozf_utility_pvt.write_conc_log(l_full_name || ': fund_id passed ' || l_fund_id);
2946 ozf_utility_pvt.write_conc_log(l_full_name || ': query_id passed ' || p_query_id);
2947 ozf_utility_pvt.write_conc_log(l_full_name || ': hierarchy_flag passed ' || p_hierarchy_flag);
2948 ozf_utility_pvt.write_conc_log(l_full_name || ': amount_flag passed ' || p_amount_flag);
2949 END IF;
2950
2951 -- only create new budget in following case, otherwise this concurrent program only update and transfer unutilized for
2952 -- budgets created through mass transfer.
2953 IF l_fund_id is NOT NULL OR l_query_id is NOT NULL THEN
2954
2955 l_sql := get_query(l_query_id);
2956
2957 /* If the personalized query return NULL then return else fetch the budgets which are active,
2958 having end data and shall be fixed budgets
2959 */
2960 IF l_sql IS NULL THEN
2961 RETURN;
2962 ELSE
2963 l_sql := l_sql || ' AND STATUS_CODE=''ACTIVE''';
2964 l_sql := l_sql || ' AND END_DATE_ACTIVE IS NOT NULL';
2965 l_sql := l_sql || ' AND FUND_TYPE = ''FIXED''';
2966 END IF;
2967
2968 /* If particular fund_id is passed then append the
2969 fund_id to the personalized query
2970 */
2971 IF l_fund_id IS NOT NULL THEN
2972 --kdass 31-MAR-2005 fixed bug 4261335
2973 --l_sql := l_sql ||' AND FUND_ID = ' || l_fund_id;
2974 l_sql := l_sql ||' AND FUND_ID = :1 ';
2975 OPEN c_get_fund_id FOR l_sql USING l_fund_id;
2976 ELSE
2977 OPEN c_get_fund_id FOR l_sql;
2978 END IF;
2979
2980 IF G_DEBUG THEN
2981 ozf_utility_pvt.write_conc_log(l_full_name || ' The Final SQL Formed: ' || l_sql);
2982 END IF;
2983
2984 -- Here getting the fund_id returned by the personalized Query
2985 LOOP
2986 SAVEPOINT open_next_years_budget;
2987 FETCH c_get_fund_id INTO l_root_fund_id;
2988
2989 fnd_msg_pub.initialize;
2990
2991 l_return_status := fnd_api.g_ret_sts_success;
2992
2993 IF c_get_fund_id%NOTFOUND THEN
2994 EXIT;
2995 END IF;
2996
2997 l_fund_rec := NULL;
2998
2999 OPEN c_get_fund_details(l_root_fund_id);
3000 FETCH c_get_fund_details into l_fund_rec;
3001 CLOSE c_get_fund_details;
3002
3003 IF G_DEBUG THEN
3004 ozf_utility_pvt.write_conc_log(l_full_name || ' fund id: ' || l_root_fund_id);
3005 END IF;
3006
3007 IF l_fund_rec.fund_id is NOT NULL THEN
3008
3009 IF G_DEBUG THEN
3010 ozf_utility_pvt.write_conc_log(l_full_name || ' inside loop for creating new fund for fund id: ' || l_fund_rec.fund_id);
3011 END IF;
3012
3013 l_fund_rec.status_code := 'DRAFT';
3014 -- If the flag is false set amount to 0 else take the default one ....
3015 IF l_amount_flag <> 'Y' THEN
3016 l_fund_rec.original_budget := 0;
3017 END IF;
3018
3019 -- if this is a child budget, then get the parent_fund_id which is the next period budget of the parent budget
3020 IF l_fund_rec.parent_fund_id IS NOT NULL THEN
3021
3022 l_par_fund_id := NULL;
3023
3024 OPEN c_get_parent_budget(l_fund_rec.parent_fund_id);
3025 FETCH c_get_parent_budget into l_par_fund_id;
3026 CLOSE c_get_parent_budget;
3027
3028 IF l_par_fund_id IS NOT NULL THEN
3029 l_fund_rec.parent_fund_id := l_par_fund_id;
3030 ELSE
3031 ozf_utility_pvt.write_conc_log('Error in creating new fund for fund id ' || l_root_fund_id);
3032 ozf_utility_pvt.write_conc_log('----Next period budget doesn''t exist for parent fund id ' || l_fund_rec.parent_fund_id);
3033 l_return_status := fnd_api.g_ret_sts_error;
3034 GOTO end_loop;
3035 END IF;
3036 END IF;
3037
3038 -- create new Budget
3039 create_new_funds(p_api_version => 1.0
3040 ,p_init_msg_list => FND_API.G_FALSE
3041 ,p_commit => FND_API.G_FALSE
3042 ,p_validation_level => fnd_api.g_valid_level_full
3043 ,x_return_status => l_return_status
3044 ,x_msg_count => x_msg_count
3045 ,x_msg_data => x_msg_data
3046 ,p_fund_rec => l_fund_rec
3047 ,x_new_fund_id => l_new_fund_id
3048 );
3049
3050 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
3051 ozf_utility_pvt.write_conc_log('Error in creating new fund for fund id ' || l_root_fund_id);
3052 GOTO end_loop;
3053 END IF;
3054
3055 ozf_utility_pvt.write_conc_log('New fund created for budget ' || l_root_fund_id || ' with fund_id ' || l_new_fund_id);
3056
3057 END IF; -- end of l_fund_rec.fund_id is NOT NULL.
3058
3059 IF l_hierarchy_flag = 'Y' THEN
3060
3061 OPEN c_get_hierarchy_budgets(l_root_fund_id);
3062 LOOP
3063 FETCH c_get_hierarchy_budgets INTO l_child_fund_id, l_parent_fund_id;
3064 EXIT WHEN c_get_hierarchy_budgets%NOTFOUND;
3065
3066 IF G_DEBUG THEN
3067 ozf_utility_pvt.write_conc_log(l_full_name || ' child budget fund id: ' || l_child_fund_id);
3068 END IF;
3069
3070 l_child_fund_rec := NULL;
3071
3072 OPEN c_get_child_budget(l_child_fund_id);
3073 FETCH c_get_child_budget into l_child_fund_rec;
3074 CLOSE c_get_child_budget;
3075
3076 IF l_child_fund_rec.fund_id is NOT NULL THEN
3077 l_child_fund_rec.status_code := 'DRAFT';
3078
3079 l_child_fund_rec.parent_fund_id := NULL;
3080
3081 OPEN c_get_parent_budget(l_parent_fund_id);
3082 FETCH c_get_parent_budget into l_child_fund_rec.parent_fund_id;
3083 CLOSE c_get_parent_budget;
3084
3085 IF l_child_fund_rec.parent_fund_id IS NULL THEN
3086 ozf_utility_pvt.write_conc_log('Error in creating new fund for fund id ' || l_child_fund_id);
3087 ozf_utility_pvt.write_conc_log('----Next period budget doesn''t exist for parent fund id ' || l_parent_fund_id);
3088 l_return_status := fnd_api.g_ret_sts_error;
3089 GOTO end_loop;
3090 END IF;
3091
3092 -- if the amount_flag is not yes then set the original budget to 0 else it would remain default...
3093 IF l_amount_flag <> 'Y' THEN
3094 l_child_fund_rec.original_budget := 0;
3095 END IF;
3096
3097 IF G_DEBUG THEN
3098 ozf_utility_pvt.write_conc_log(l_full_name || ' parent_fund_id: ' || l_child_fund_rec.parent_fund_id);
3099 END IF;
3100
3101 -- create fund corresonding to this child budget
3102 create_new_funds(p_api_version => 1.0
3103 ,p_init_msg_list => FND_API.G_FALSE
3104 ,p_commit => FND_API.G_FALSE
3105 ,p_validation_level => fnd_api.g_valid_level_full
3106 ,x_return_status => l_return_status
3107 ,x_msg_count => x_msg_count
3108 ,x_msg_data => x_msg_data
3109 ,p_fund_rec => l_child_fund_rec
3110 ,x_new_fund_id => l_new_fund_id
3111 );
3112
3113 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
3114 ozf_utility_pvt.write_conc_log('Error in creating new fund for fund id ' || l_child_fund_id);
3115 GOTO end_loop;
3116 END IF;
3117
3118 ozf_utility_pvt.write_conc_log('New fund created for child budget ' || l_child_fund_id ||' with fund id ' || l_new_fund_id);
3119
3120 ELSE
3121 IF G_DEBUG THEN
3122 ozf_utility_pvt.write_conc_log(l_full_name || ' this budget already has a next period budget');
3123 END IF;
3124 END IF; -- end of l_child_fund_rec.fund_id is NOT NULL.
3125
3126 END LOOP; -- end of loop for c_get_hierarchy_budgets.
3127
3128 CLOSE c_get_hierarchy_budgets;
3129 END IF; -- hierarchy flag check loop ends here
3130
3131 <<end_loop>>
3132 IF l_return_status = fnd_api.g_ret_sts_success THEN
3133 COMMIT;
3134 x_retcode := 0;
3135 ELSE
3136 ROLLBACK TO open_next_years_budget;
3137 fnd_msg_pub.count_and_get(p_count => x_msg_count
3138 ,p_data => x_msg_data
3139 ,p_encoded => fnd_api.g_false
3140 );
3141 ozf_utility_pvt.write_conc_log (x_msg_data);
3142 END IF;
3143
3144 END LOOP; -- end of c_get_fund_id.
3145 CLOSE c_get_fund_id;
3146 END IF; --l_fund_id is NOT NULL OR l_query_id is NOT NULL
3147
3148 -- activate draft budget from mass transfer and transfer unutilized amount to new budget.
3149 get_new_funds(p_api_version => 1.0
3150 ,p_init_msg_list => FND_API.G_FALSE
3151 ,p_commit => FND_API.G_FALSE
3152 ,p_validation_level => fnd_api.g_valid_level_full
3153 ,x_return_status => l_return_status
3154 ,x_msg_count => x_msg_count
3155 ,x_msg_data => x_msg_data
3156 );
3157
3158 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
3159 ROLLBACK;
3160 x_retcode := 1;
3161 x_errbuf := x_msg_data;
3162 RAISE fnd_api.g_exc_error;
3163 END IF;
3164
3165 IF G_DEBUG THEN
3166 ozf_utility_pvt.write_conc_log(l_full_name || ' :ends ');
3167 END IF;
3168
3169 EXCEPTION
3170 WHEN fnd_api.g_exc_error THEN
3171 ROLLBACK;
3172 x_retcode := 1;
3173 x_errbuf := x_msg_data;
3174 fnd_msg_pub.count_and_get(p_count => x_msg_count
3175 ,p_data => x_msg_data
3176 ,p_encoded => fnd_api.g_false
3177 );
3178 ozf_utility_pvt.write_conc_log (x_errbuf);
3179 WHEN fnd_api.g_exc_unexpected_error THEN
3180 ROLLBACK;
3181 x_retcode := 1;
3182 x_errbuf := x_msg_data;
3183 fnd_msg_pub.count_and_get(p_count => x_msg_count
3184 ,p_data => x_msg_data
3185 ,p_encoded => fnd_api.g_false
3186 );
3187 ozf_utility_pvt.write_conc_log (x_errbuf);
3188 WHEN OTHERS THEN
3189 ROLLBACK;
3190 x_retcode := 1;
3191 x_errbuf := x_msg_data;
3192 fnd_msg_pub.count_and_get(p_count => x_msg_count
3193 ,p_data => x_msg_data
3194 ,p_encoded => fnd_api.g_false
3195 );
3196 ozf_utility_pvt.write_conc_log (x_errbuf);
3197
3198 END open_next_years_budget;
3199
3200 END ozf_fund_reconcile_pvt;
3201