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