[Home] [Help]
PACKAGE BODY: APPS.OKL_SUBSIDY_POOL_RPT_PVT
Source
1 PACKAGE BODY okl_subsidy_pool_rpt_pvt AS
2 /* $Header: OKLRSIOB.pls 120.15 2007/01/09 12:37:08 udhenuko noship $ */
3
4 G_WF_EVT_POOL_NEAR_EXPIR CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.subsidy_pool.pool_nearing_expiration';
5 G_WF_EVT_POOL_NEAR_BUDGLMT CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.subsidy_pool.pool_nearing_bdgt_limit';
6 G_WF_ITM_SUB_POOL_ID CONSTANT VARCHAR2(30) := 'SUBSIDY_POOL_ID';
7
8 -------------------------------------------------------------------------------
9 -- PROCEDURE raise_business_event
10 -------------------------------------------------------------------------------
11 -- Start of comments
12 --
13 -- Procedure Name : raise_business_event
14 -- Description : This procedure is a wrapper that raises a business event
15 -- : when ever a subsidy pool record is submitted for approval, approved, rejected
16 -- Business Rules : the event is raised based on the decision_status_code passed and
17 -- successful updation of the pool record
18 -- Parameters :
19 -- Version : 1.0
20 -- History :
21 -- End of comments
22 -----------------------------------------------------------------------------------------
23 PROCEDURE raise_business_event(p_api_version IN NUMBER,
24 p_init_msg_list IN VARCHAR2,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2,
28 p_event_name IN VARCHAR2,
29 p_event_param_list IN WF_PARAMETER_LIST_T
30 ) IS
31 l_event_param_list WF_PARAMETER_LIST_T;
32 BEGIN
33 x_return_status := OKL_API.G_RET_STS_SUCCESS;
34 l_event_param_list := p_event_param_list;
35
36 OKL_WF_PVT.raise_event(p_api_version => p_api_version,
37 p_init_msg_list => p_init_msg_list,
38 x_return_status => x_return_status,
39 x_msg_count => x_msg_count,
40 x_msg_data => x_msg_data,
41 p_event_name => p_event_name,
42 p_parameters => l_event_param_list);
43 EXCEPTION
44 WHEN OTHERS THEN
45 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
46 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
47 END raise_business_event;
48
49 ---------------------------------------------------------------------------
50 -- FUNCTION GET_PROPER_LENGTH
51 ---------------------------------------------------------------------------
52 ---------------------------------------------------------------------------
53 -- Start of comments
54 --
55 -- Function Name : GET_PROPER_LENGTH
56 -- Description : function to display the record with their proper lengths.
57 -- If they exceed the specified length then truncate it.
58 -- Business Rules :
59 -- Parameters : p_input_data, p_input_length, p_input_type
60 -- Version : 1.0
61 -- History : 08-Mar-2005 ABINDAL created.
62 -- End of comments
63 ---------------------------------------------------------------------------
64
65 FUNCTION GET_PROPER_LENGTH(p_input_data IN VARCHAR2,
66 p_input_length IN NUMBER,
67 p_input_type IN VARCHAR2)
68 RETURN VARCHAR2
69
70 IS
71
72 x_return_data VARCHAR2(1000);
73
74 BEGIN
75
76 IF (p_input_type = 'TITLE') THEN
77 IF (p_input_data IS NOT NULL) THEN
78 x_return_data := RPAD(SUBSTR(ltrim(rtrim(p_input_data)),1,p_input_length),p_input_length,' ');
79 ELSE
80 x_return_data := RPAD(' ',p_input_length,' ');
81 END IF;
82 ELSE
83 IF (p_input_data IS NOT NULL) THEN
84 IF (length(p_input_data) > p_input_length) THEN
85 x_return_data := SUBSTR(p_input_data,1,p_input_length);
86 ELSE
87 x_return_data := RPAD(p_input_data,p_input_length,' ');
88 END IF;
89 ELSE
90 x_return_data := RPAD(' ',p_input_length,' ');
91 END IF;
92 END IF;
93
94 RETURN x_return_data;
95
96 END GET_PROPER_LENGTH;
97
98 ---------------------------------------------------------------------------
99 -- FUNCTION CURRENCY_CONVERSION
100 ---------------------------------------------------------------------------
101 ---------------------------------------------------------------------------
102 -- Start of comments
103 --
104 -- Function Name : CURRENCY_CONVERSION
105 -- Description : To convert the given amount in one currency to
106 -- the amount in other currency.
107 -- Business Rules :
108 -- Parameters : p_amount, p_from_currency_code,p_to_currency_code
109 -- p_conv_type, p_conv_date,x_conv_rate.
110 -- Version : 1.0
111 -- History : 08-Mar-2005 ABINDAL created.
112 -- End of comments
113 ---------------------------------------------------------------------------
114 FUNCTION currency_conversion (p_amount IN NUMBER,
115 p_from_currency_code IN VARCHAR2,
116 p_to_currency_code IN VARCHAR2,
117 p_conv_type IN VARCHAR2,
118 p_conv_date IN DATE,
119 x_conv_rate OUT NOCOPY NUMBER)
120 RETURN NUMBER
121 IS
122
123 l_api_version NUMBER ;
124 l_init_msg_list VARCHAR2(1) ;
125 l_return_status VARCHAR2(1);
126 l_msg_count NUMBER ;
127 l_msg_data VARCHAR2(2000);
128 l_conv_rate NUMBER ;
129 l_round_amount NUMBER ;
130 l_amount NUMBER ;
131 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CURRENCY_CONVERSION';
132 l_conv_date DATE ;
133 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.CURRENCY_CONVERSION';
134 l_debug_enabled VARCHAR2(10);
135 is_debug_procedure_on BOOLEAN;
136 is_debug_statement_on BOOLEAN;
137
138 BEGIN
139 l_debug_enabled := okl_debug_pub.check_log_enabled;
140 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
141
142 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
143 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call currency_conversion');
144 END IF;
145 -- check for logging on STATEMENT level
146 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
147
148 l_api_version := 1.0;
149 l_init_msg_list := Okl_Api.g_false;
150 l_msg_count := 0;
151 l_conv_rate := 0;
152 l_round_amount := 0;
153 l_amount := 0;
154 l_conv_date := TRUNC(SYSDATE);
155
156 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
157 G_PKG_NAME,
158 l_init_msg_list,
159 l_api_version,
160 l_api_version,
161 '_PVT',
162 l_return_status);
163
164 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
165 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
166 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
167 RAISE OKL_API.G_EXCEPTION_ERROR;
168 END IF;
169
170 IF(p_conv_date is null) THEN
171 l_conv_date := TRUNC(SYSDATE);
172 ELSE
173 l_conv_date := TRUNC(p_conv_date);
174 END IF;
175 l_conv_rate := 0;
176 l_round_amount := 0;
177 l_amount := 0;
178 -- If both the from currency code and to currency code are equal, there is no need
179 -- for conversion. simply return back the amount.
180 IF( p_from_currency_code <> p_to_currency_code) THEN
181 -- get the currency conversion rate.
182 okl_accounting_util.get_curr_con_rate(p_api_version => l_api_version
183 ,p_init_msg_list => l_init_msg_list
184 ,x_return_status => l_return_status
185 ,x_msg_count => l_msg_count
186 ,x_msg_data => l_msg_data
187 ,p_from_curr_code => p_from_currency_code
188 ,p_to_curr_code => p_to_currency_code
189 ,p_con_date => l_conv_date
190 ,p_con_type => p_conv_type
191 ,x_conv_rate => l_conv_rate
192 );
193 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
194 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
195 l_module,
196 'p_from_currency_code '||p_from_currency_code||' p_to_currency_code '
197 ||p_to_currency_code||' l_conv_date '||l_conv_date||'p_conv_type'||p_conv_type
198 ||'l_conv_rate'||l_conv_rate
199 );
200 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
201
202 IF(l_conv_rate IS NULL OR l_conv_rate <= 0)THEN
203 return -1;
204 END IF;
205 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
206 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
207 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
208 RAISE OKL_API.G_EXCEPTION_ERROR;
209 END IF;
210 -- calculate the amount in terms of the required currency, by multiplying the rate with the given amount.
211 x_conv_rate := l_conv_rate;
212 l_amount := NVL(p_amount,0) * l_conv_rate;
213 -- Then round of this converted amount.
214 okl_accounting_util.cross_currency_round_amount(p_api_version => l_api_version
215 ,p_init_msg_list => l_init_msg_list
216 ,x_return_status => l_return_status
217 ,x_msg_count => l_msg_count
218 ,x_msg_data => l_msg_data
219 ,p_amount => l_amount
220 ,p_currency_code => p_to_currency_code
221 ,x_rounded_amount => l_round_amount
222 );
223 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
224 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
225 l_module,
226 'l_amount '||l_amount||' l_round_amount '||l_round_amount
227 );
228 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
229
230 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
231 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
232 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)THEN
233 RAISE OKL_API.G_EXCEPTION_ERROR;
234 END IF;
235 ELSE
236 l_round_amount := NVL(p_amount,0);
237 x_conv_rate := 1;
238 END IF;
239 okl_api.END_ACTIVITY(l_msg_count, l_msg_data);
240 RETURN l_round_amount;
241 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
242 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call currency_conversion');
243 END IF;
244
245 EXCEPTION
246 WHEN OKL_API.G_EXCEPTION_ERROR THEN
247 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
248 p_api_name => l_api_name,
249 p_pkg_name => G_PKG_NAME,
250 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
251 x_msg_count => l_msg_count,
252 x_msg_data => l_msg_data,
253 p_api_type => g_api_type);
254
255 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
256 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
257 p_api_name => l_api_name,
258 p_pkg_name => G_PKG_NAME,
259 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
260 x_msg_count => l_msg_count,
261 x_msg_data => l_msg_data,
262 p_api_type => g_api_type);
263
264 WHEN OTHERS THEN
265 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
266 p_api_name => l_api_name,
267 p_pkg_name => G_PKG_NAME,
268 p_exc_name => 'OTHERS',
269 x_msg_count => l_msg_count,
270 x_msg_data => l_msg_data,
271 p_api_type => g_api_type);
272 END currency_conversion;
273
274 ---------------------------------------------------------------------------
275 -- FUNCTION TOTAL_BUDGETS
276 ---------------------------------------------------------------------------
277 ---------------------------------------------------------------------------
278 -- Start of comments
279 --
280 -- Function Name : TOTAL_BUDGETS
281 -- Description : To calculate the total budgets of a subsidy pool
282 -- till the specified date.
283 -- Business Rules :
284 -- Parameters : p_pool_id, p_input_date, p_from_currency_code
285 -- p_to_currency_code, p_conversion_type
286 -- Version : 1.0
287 -- History : 08-Mar-2005 ABINDAL created.
288 -- End of comments
289 ---------------------------------------------------------------------------
290 FUNCTION total_budgets (p_pool_id IN VARCHAR2,
291 p_to_date IN DATE,
292 p_from_currency_code IN VARCHAR2,
293 p_to_currency_code IN VARCHAR2,
294 p_conversion_type IN VARCHAR2,
295 x_return_status OUT NOCOPY VARCHAR2,
296 x_msg_count OUT NOCOPY NUMBER,
297 x_msg_data OUT NOCOPY VARCHAR2 )
298 RETURN NUMBER
299 IS
300
301 CURSOR c_total_budget(cp_pool_id VARCHAR2, cp_to_date DATE)IS
302 SELECT budget_type_code,
303 budget_amount,
304 decision_status_code,
305 effective_from_date
306 FROM okl_subsidy_pool_budgets_b
307 WHERE subsidy_pool_id = cp_pool_id
308 AND TRUNC(effective_from_date) <= NVL(TRUNC(cp_to_date),TRUNC(effective_from_date));
309
310 l_total_budget NUMBER ;
311 l_amount NUMBER ;
312 l_conv_rate NUMBER ;
313 l_api_name CONSTANT VARCHAR2(30) := 'total_budgets';
314 l_msg_count NUMBER ;
315 l_msg_data VARCHAR2(2000);
316 l_return_status VARCHAR2(1);
317 l_api_version NUMBER ;
318 l_init_msg_list VARCHAR2(1) ;
319 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.TOTAL_BUDGETS';
320 l_debug_enabled VARCHAR2(10);
321 is_debug_procedure_on BOOLEAN;
322 is_debug_statement_on BOOLEAN;
323 BEGIN
324 l_debug_enabled := okl_debug_pub.check_log_enabled;
325 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
326
327 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
328 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call total_budgets');
329 END IF;
330 -- check for logging on STATEMENT level
331 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
332
333 l_api_version := 1.0;
334 l_init_msg_list := Okl_Api.g_false;
335 l_msg_count := 0;
336 l_total_budget := 0;
337 l_amount := 0;
338 l_conv_rate := 0;
339
340 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
341 G_PKG_NAME,
342 l_init_msg_list,
343 l_api_version,
344 l_api_version,
345 '_PVT',
346 l_return_status);
347
348 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
349 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
350 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
351 RAISE OKL_API.G_EXCEPTION_ERROR;
352 END IF;
353
354 l_total_budget := 0;
355 -- Get the total budgets for the subsidy pool till the p_input_date specified.
356 FOR each_row IN c_total_budget(p_pool_id,p_to_date)
357 LOOP
358 l_amount := 0;
359 -- Convert the budget line amount, from its subsidy pool currency to the Parent pool currency
360 -- entered by the user. The currency conversion rate will be derived based on the effective from
361 -- date of the respective budget lines.
362 l_amount := currency_conversion( each_row.budget_amount,
363 p_from_currency_code,
364 p_to_currency_code,
365 p_conversion_type,
366 each_row.effective_from_date,
367 l_conv_rate
368 );
369 IF (l_amount < 0) THEN
370 fnd_message.set_name(G_APP_NAME,
371 'OKL_POOL_CURR_CONV');
372 fnd_message.set_token('FROM_CURR',
373 p_from_currency_code);
374 fnd_message.set_token('TO_CURR',
375 p_to_currency_code);
376 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
377 END IF;
378
379 IF (each_row.budget_type_code = 'ADDITION' AND each_row.decision_status_code = 'ACTIVE') THEN
380 l_total_budget := l_total_budget + l_amount;
381 ELSIF(each_row.budget_type_code = 'REDUCTION' AND (each_row.decision_status_code IN ('ACTIVE', 'PENDING'))) THEN
382 l_total_budget := l_total_budget - l_amount;
383 END IF;
384
385 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
386 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
387 l_module,
388 'l_amount '||l_amount||' l_total_budget '||l_total_budget
389 );
390 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
391
392 END LOOP;
393 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
394 x_return_status := l_return_status;
395 x_msg_data := l_msg_data;
396 x_msg_count := l_msg_count;
397 RETURN l_total_budget;
398
399 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
400 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call total_budgets');
401 END IF;
402
403 EXCEPTION
404 WHEN G_EXCEPTION_HALT_VALIDATION THEN
405 x_return_status := OKL_API.HANDLE_EXCEPTIONS
406 (
407 l_api_name,
408 G_PKG_NAME,
409 'OKL_API.G_RET_STS_ERROR',
410 x_msg_count,
411 x_msg_data,
412 '_PVT'
413 );
414
415 WHEN OKL_API.G_EXCEPTION_ERROR THEN
416 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
417 p_api_name => l_api_name,
418 p_pkg_name => G_PKG_NAME,
419 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
420 x_msg_count => x_msg_count,
421 x_msg_data => x_msg_data,
422 p_api_type => g_api_type);
423
424 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
425 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
426 p_api_name => l_api_name,
427 p_pkg_name => G_PKG_NAME,
428 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
429 x_msg_count => x_msg_count,
430 x_msg_data => x_msg_data,
431 p_api_type => g_api_type);
432
433 WHEN OTHERS THEN
434 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
435 p_api_name => l_api_name,
436 p_pkg_name => G_PKG_NAME,
437 p_exc_name => 'OTHERS',
438 x_msg_count => x_msg_count,
439 x_msg_data => x_msg_data,
440 p_api_type => g_api_type);
441
442 END total_budgets;
443
444 ---------------------------------------------------------------------------
445 -- FUNCTION TRANSACTION_AMOUNT
446 ---------------------------------------------------------------------------
447 ---------------------------------------------------------------------------
448 -- Start of comments
449 --
450 -- Function Name : TRANSACTION_AMOUNT
451 -- Description : To get the total transaction amount for the subsidy
452 -- pool till the specified date.
453 -- Business Rules :
454 -- Parameters : p_pool_id, p_input_date, p_from_currency_code
455 -- p_to_currency_code, p_conversion_type
456 -- Version : 1.0
457 -- History : 08-Mar-2005 ABINDAL created.
458 -- End of comments
459 ---------------------------------------------------------------------------
460 FUNCTION transaction_amount (p_pool_id IN VARCHAR2,
461 p_to_date IN DATE,
462 p_from_currency_code IN VARCHAR2,
463 p_to_currency_code IN VARCHAR2,
464 p_conversion_type IN VARCHAR2,
465 x_return_status OUT NOCOPY VARCHAR2,
466 x_msg_count OUT NOCOPY NUMBER,
467 x_msg_data OUT NOCOPY VARCHAR2 )
468 RETURN NUMBER
469 IS
470
471 -- cursor for calcualting the remaining balance of pool till the date specified.
472 CURSOR c_remaining_balance(cp_pool_id VARCHAR2, cp_to_date DATE)IS
473 SELECT trx_type_code,
474 --STRAT: 02-NOV-05 cklee - Fixed bug#4705629 |
475 trx_amount,
476 trx_currency_code,
477 -- subsidy_pool_amount,
478 --END : 02-NOV-05 cklee - Fixed bug#4705629 |
479 source_trx_date,
480 trx_date
481 FROM okl_trx_subsidy_pools
482 WHERE subsidy_pool_id = cp_pool_id
483 AND TRUNC(source_trx_date) <= NVL(TRUNC(cp_to_date), TRUNC(source_trx_date));
484
485 l_trx_amount NUMBER ;
486 l_amount NUMBER ;
487 l_conv_rate NUMBER ;
488 l_api_name CONSTANT VARCHAR2(30) := 'transaction_amount';
489 l_msg_count NUMBER ;
490 l_msg_data VARCHAR2(2000);
491 l_return_status VARCHAR2(1);
492 l_api_version NUMBER ;
493 l_init_msg_list VARCHAR2(1);
494 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.TRANSACTION_AMOUNT';
495 l_debug_enabled VARCHAR2(10);
496 is_debug_procedure_on BOOLEAN;
497 is_debug_statement_on BOOLEAN;
498
499 BEGIN
500 l_debug_enabled := okl_debug_pub.check_log_enabled;
501 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
502 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
503 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call transaction_amount');
504 END IF;
505 -- check for logging on STATEMENT level
506 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
507
508 l_api_version := 1.0;
509 l_init_msg_list := Okl_Api.g_false;
510 l_msg_count := 0;
511 l_trx_amount := 0;
512 l_amount := 0;
513 l_conv_rate := 0;
514
515 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
516 G_PKG_NAME,
517 l_init_msg_list,
518 l_api_version,
519 l_api_version,
520 '_PVT',
521 l_return_status);
522
523 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
524 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
525 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
526 RAISE OKL_API.G_EXCEPTION_ERROR;
527 END IF;
528
529 l_trx_amount := 0;
530 -- Calculate the total transactions for the given subsidy pool uptill the p_input_date.
531 FOR each_row IN c_remaining_balance(p_pool_id,p_to_date)
532 LOOP
533 l_amount := 0;
534 -- Convert the transaction amount, from its subsidy pool currency to the Parent pool currency
535 -- entered by the user. The currency conversion rate will be derived based on the transaction
536 -- date of the respective transaction.
537 --STRAT: 02-NOV-05 cklee - Fixed bug#4705629 |
538 -- l_amount := currency_conversion( each_row.trx_amount,
539 l_amount := currency_conversion( each_row.trx_amount,
540 -- this function will NOT use the passed in pool's curreny code to convert to
541 -- the destination curreny code, instead, will use trx curreny code to convert to destination pool
542 -- curreny directly to avoid inconsist between report header trx amount and the details' trx amount
543 -- if the passed in pool is reporting pool
544 -- p_from_currency_code,
545 each_row.trx_currency_code,
546 --END: 02-NOV-05 cklee - Fixed bug#4705629 |
547 p_to_currency_code,
548 p_conversion_type,
549 each_row.trx_date,
550 l_conv_rate
551 );
552 IF (l_amount < 0) THEN
553 fnd_message.set_name(G_APP_NAME,
554 'OKL_POOL_CURR_CONV');
555 fnd_message.set_token('FROM_CURR',
556 p_from_currency_code);
557 fnd_message.set_token('TO_CURR',
558 p_to_currency_code);
559 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
560 END IF;
561 IF (each_row.trx_type_code = 'ADDITION') THEN
562 l_trx_amount := l_trx_amount - l_amount;
563 ELSIF(each_row.trx_type_code = 'REDUCTION') THEN
564 l_trx_amount := l_trx_amount + l_amount;
565 END IF;
566 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
567 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
568 l_module,
569 'l_amount '||l_amount||' l_trx_amount '||l_trx_amount
570 );
571 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
572
573 END LOOP;
574 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
575 x_return_status := l_return_status;
576 x_msg_data := l_msg_data;
577 x_msg_count := l_msg_count;
578 RETURN l_trx_amount;
579 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
580 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call transaction_amount');
581 END IF;
582 EXCEPTION
583 WHEN G_EXCEPTION_HALT_VALIDATION THEN
584 x_return_status := OKL_API.HANDLE_EXCEPTIONS
585 (
586 l_api_name,
587 G_PKG_NAME,
588 'OKL_API.G_RET_STS_ERROR',
589 x_msg_count,
590 x_msg_data,
591 '_PVT'
592 );
593
594 WHEN OKL_API.G_EXCEPTION_ERROR THEN
595 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
596 p_api_name => l_api_name,
597 p_pkg_name => G_PKG_NAME,
598 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
599 x_msg_count => x_msg_count,
600 x_msg_data => x_msg_data,
601 p_api_type => g_api_type);
602
603 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
604 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
605 p_api_name => l_api_name,
606 p_pkg_name => G_PKG_NAME,
607 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
608 x_msg_count => x_msg_count,
609 x_msg_data => x_msg_data,
610 p_api_type => g_api_type);
611
612 WHEN OTHERS THEN
613 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
614 p_api_name => l_api_name,
615 p_pkg_name => G_PKG_NAME,
616 p_exc_name => 'OTHERS',
617 x_msg_count => x_msg_count,
618 x_msg_data => x_msg_data,
619 p_api_type => g_api_type);
620
621 END transaction_amount;
622
623 ---------------------------------------------------------------------------
624 -- FUNCTION GET_PARENT_RECORD
625 ---------------------------------------------------------------------------
626 ---------------------------------------------------------------------------
627 -- Start of comments
628 --
629 -- Function Name : GET_PARENT_RECORD
630 -- Description : To get the parent subsidy pool record.
631 -- Business Rules :
632 -- Parameters : p_parent_id
633 -- Version : 1.0
634 -- History : 08-Mar-2005 ABINDAL created.
635 -- End of comments
636 ---------------------------------------------------------------------------
637 FUNCTION get_parent_record ( p_parent_id IN okl_subsidy_pools_b.id%TYPE )
638
639 RETURN okl_sub_pool_rec
640
641 IS
642
643 CURSOR c_parent_summary(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
644 SELECT id,
645 subsidy_pool_name,
646 pool_type_code,
647 currency_code,
648 currency_conversion_type,
649 reporting_pool_limit,
650 effective_from_date
651 FROM okl_subsidy_pools_b
652 WHERE id = cp_pool_id
653 AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
654 else
655 (case when exists (select 'x'
656 from okl_subsidies_v
657 where subsidy_pool_id = okl_subsidy_pools_b.id
658 and org_id <> mo_global.get_current_org_id()) then 0
659 else 1 end)
660 end));
661
662 l_pool_rec okl_sub_pool_rec;
663
664 BEGIN
665 -- Fetch the Parent subsidy pool record and return a record type.
666 OPEN c_parent_summary(p_parent_id);
667 FETCH c_parent_summary INTO l_pool_rec;
668 CLOSE c_parent_summary;
669
670 RETURN l_pool_rec;
671
672 END get_parent_record;
673
674
675 ---------------------------------------------------------------------------
676 -- PROCEDURE PRINT_PARENT_RECORD
677 ---------------------------------------------------------------------------
678 ---------------------------------------------------------------------------
679 -- Start of comments
680 --
681 -- Procedure Name : PRINT_PARENT_RECORD
682 -- Description : To print the parent subsidy pool record.
683 -- Business Rules :
684 -- Parameters : p_pool_rec, p_input_date, p_to_currency_code
685 -- Version : 1.0
686 -- History : 08-Mar-2005 ABINDAL created.
687 -- End of comments
688 ---------------------------------------------------------------------------
689 PROCEDURE print_parent_record (p_pool_rec IN okl_sub_pool_rec,
690 p_input_date IN DATE,
691 p_to_currency_code IN VARCHAR2,
692 p_conv_type IN VARCHAR2,
693 x_return_status OUT NOCOPY VARCHAR2,
694 x_msg_count OUT NOCOPY NUMBER,
695 x_msg_data OUT NOCOPY VARCHAR2 )
696 IS
697
698 -- Cursor fetches all the records, which are children of a given pool till the pool,
699 -- does not have any more children.
700 CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
701 SELECT id ,
702 currency_code,
703 currency_conversion_type
704 FROM okl_subsidy_pools_b pool
705 WHERE pool_type_code = 'BUDGET'
706 CONNECT BY PRIOR id = subsidy_pool_id
707 START WITH id = cp_pool_id;
708
709 l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
710 l_budget okl_subsidy_pools_b.total_budgets%TYPE;
711 l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
712 l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
713 l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
714 l_conv_rate NUMBER;
715 l_Pool_Name_len CONSTANT NUMBER DEFAULT 30;
716 l_Pool_Type_len CONSTANT NUMBER DEFAULT 30;
717 l_Currency_Code_len CONSTANT NUMBER DEFAULT 15;
718 l_Pool_Limit_len CONSTANT NUMBER DEFAULT 20;
719 l_Budget_len CONSTANT NUMBER DEFAULT 20;
720 l_Remaining_Balance_len CONSTANT NUMBER DEFAULT 20;
721 l_total_length CONSTANT NUMBER DEFAULT 152;
722 l_reporting_limit okl_subsidy_pools_b.reporting_pool_limit%TYPE ;
723 l_api_name CONSTANT VARCHAR2(30) := 'print_parent_record';
724 l_msg_count NUMBER ;
725 l_msg_data VARCHAR2(2000);
726 l_return_status VARCHAR2(1);
727 l_api_version NUMBER ;
728 l_init_msg_list VARCHAR2(1);
729 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.PRINT_PARENT_RECORD';
730 l_debug_enabled VARCHAR2(10);
731 is_debug_procedure_on BOOLEAN;
732 is_debug_statement_on BOOLEAN;
733
734 BEGIN
735 l_debug_enabled := okl_debug_pub.check_log_enabled;
736 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
737 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
738 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call print_parent_record');
739 END IF;
740 -- check for logging on STATEMENT level
741 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
742
743 l_api_version := 1.0;
744 l_init_msg_list := Okl_Api.g_false;
745 l_msg_count := 0;
746 l_total_budget := 0;
747 l_budget := 0;
748 l_trx_amount := 0;
749 l_trx_amt := 0;
750 l_remaining_balance := 0;
751 l_conv_rate := 0;
752 l_reporting_limit := p_pool_rec.reporting_pool_limit;
753
754 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
755 G_PKG_NAME,
756 l_init_msg_list,
757 l_api_version,
758 l_api_version,
759 '_PVT',
760 l_return_status);
761
762 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
763 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
764 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
765 RAISE OKL_API.G_EXCEPTION_ERROR;
766 END IF;
767
768 -- Parent pool header with the parent pool name.
769 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
770 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
771 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_PARENT_POOL') || ' : '
772 || fnd_message.get_string('OKL',p_pool_rec.subsidy_pool_name));
773 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', 30 , '-' ));
774 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
775 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
776 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_SUBSIDY_POOL_NAME'),l_Pool_Name_len,'TITLE')||' '||
777 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_POOL_TYPE_TXT'),l_Pool_Type_len,'TITLE')||' '||
778 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_AGN_RPT_CURRENCY'),l_Currency_Code_len,'TITLE')||' '||
779 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_SUBSIDY_POOL_LIMIT'),l_Pool_Limit_len,'TITLE')||' '||
780 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_POOL_BUDGET'),l_Budget_len,'TITLE')||' '||
781 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_POOL_BALANCE'),l_Remaining_Balance_len,'TITLE'));
782 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=', l_total_length+8 , '=' ));
783 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
784 -- Convert the reporting pool limit amount from pool currency to the parent pool(user entered) currency.
785 l_reporting_limit := currency_conversion(p_pool_rec.reporting_pool_limit,
786 p_pool_rec.currency_code,
787 p_to_currency_code,
788 p_conv_type,
789 p_pool_rec.effective_from_date,
790 l_conv_rate
791 );
792 IF (l_reporting_limit < 0) THEN
793 fnd_message.set_name( G_APP_NAME,
794 'OKL_POOL_CURR_CONV');
795 fnd_message.set_token('FROM_CURR',
796 p_pool_rec.currency_code);
797 fnd_message.set_token('TO_CURR',
798 p_to_currency_code);
799 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
800 END IF;
801 -- If subsidy pool is of type reporting then, calculate the total budgets and remaining balance for that pool,
802 -- from all its children which are of type budget.
803 IF(p_pool_rec.pool_type_code = 'REPORTING') THEN
804 FOR each_row IN get_amounts(p_pool_rec.id) LOOP
805 l_budget := total_budgets( each_row.id,
806 p_input_date,
807 each_row.currency_code,
808 p_to_currency_code,
809 p_conv_type,
810 l_return_status,
811 l_msg_count,
812 l_msg_data
813 );
814 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
815 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
816 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
817 RAISE OKL_API.G_EXCEPTION_ERROR;
818 END IF;
819 l_total_budget := l_total_budget + l_budget;
820 l_trx_amt := transaction_amount( each_row.id,
821 p_input_date,
822 each_row.currency_code,
823 p_to_currency_code,
824 p_conv_type,
825 l_return_status,
826 l_msg_count,
827 l_msg_data
828 );
829 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
830 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
831 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
832 RAISE OKL_API.G_EXCEPTION_ERROR;
833 END IF;
834 l_trx_amount := l_trx_amount + l_trx_amt;
835 END LOOP;
836 -- if subsidy pool type is budget, simply calculate the total budgets and remaining balance.
837 ELSE
838 l_budget := total_budgets( p_pool_rec.id,
839 p_input_date,
840 p_pool_rec.currency_code,
841 p_to_currency_code,
842 p_conv_type,
843 l_return_status,
844 l_msg_count,
845 l_msg_data
846 );
847 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
848 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
849 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
850 RAISE OKL_API.G_EXCEPTION_ERROR;
851 END IF;
852 l_total_budget := l_budget;
853 l_trx_amt := transaction_amount( p_pool_rec.id,
854 p_input_date,
855 p_pool_rec.currency_code,
856 p_to_currency_code,
857 p_conv_type,
858 l_return_status,
859 l_msg_count,
860 l_msg_data
861 );
862 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
863 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
864 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
865 RAISE OKL_API.G_EXCEPTION_ERROR;
866 END IF;
867 l_trx_amount := l_trx_amt;
868 END IF;
869 l_remaining_balance := l_total_budget - l_trx_amount;
870
871 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
872 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
873 l_module,
874 'l_reporting_limit '||l_reporting_limit||' l_total_budget '
875 ||l_total_budget||' l_trx_amount '||l_trx_amount
876 ||'l_remaining_balance'||l_remaining_balance
877 );
878 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
879
880 -- Print the parent pool record
881 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
882 GET_PROPER_LENGTH(p_pool_rec.subsidy_pool_name,l_Pool_Name_len,'DATA')||' '||
883 GET_PROPER_LENGTH(p_pool_rec.pool_type_code,l_Pool_Type_len,'DATA')||' '||
884 GET_PROPER_LENGTH(p_to_currency_code,l_Currency_Code_len,'DATA')||' '||
885 GET_PROPER_LENGTH(okl_accounting_util.format_amount(l_reporting_limit,p_to_currency_code),l_Pool_Limit_len,'DATA')||' '||
886 GET_PROPER_LENGTH(okl_accounting_util.format_amount(l_total_budget,p_to_currency_code),l_Budget_len,'DATA')||' '||
887 GET_PROPER_LENGTH(okl_accounting_util.format_amount(l_remaining_balance,p_to_currency_code),l_Remaining_Balance_len,'DATA'));
888 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
889
890 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
891 x_return_status := l_return_status;
892 x_msg_data := l_msg_data;
893 x_msg_count := l_msg_count;
894
895 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
896 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call print_parent_record');
897 END IF;
898
899 EXCEPTION
900 WHEN G_EXCEPTION_HALT_VALIDATION THEN
901 x_return_status := OKL_API.HANDLE_EXCEPTIONS
902 (
903 l_api_name,
904 G_PKG_NAME,
905 'OKL_API.G_RET_STS_ERROR',
906 x_msg_count,
907 x_msg_data,
908 '_PVT'
909 );
910
911 WHEN OKL_API.G_EXCEPTION_ERROR THEN
912 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
913 p_api_name => l_api_name,
914 p_pkg_name => G_PKG_NAME,
915 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
916 x_msg_count => x_msg_count,
917 x_msg_data => x_msg_data,
918 p_api_type => g_api_type);
919
920 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
921 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
922 p_api_name => l_api_name,
923 p_pkg_name => G_PKG_NAME,
924 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
925 x_msg_count => x_msg_count,
926 x_msg_data => x_msg_data,
927 p_api_type => g_api_type);
928
929 WHEN OTHERS THEN
930 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
931 p_api_name => l_api_name,
932 p_pkg_name => G_PKG_NAME,
933 p_exc_name => 'OTHERS',
934 x_msg_count => x_msg_count,
935 x_msg_data => x_msg_data,
936 p_api_type => g_api_type);
937
938 END print_parent_record;
939
940 ---------------------------------------------------------------------------
941 -- FUNCTION GET_CHILD_RECORD
942 ---------------------------------------------------------------------------
943 ---------------------------------------------------------------------------
944 -- Start of comments
945 --
946 -- Function Name : GET_CHILD_RECORD
947 -- Description : To get the all the child subsidy pool records.
948 -- Business Rules :
949 -- Parameters : p_pool_id
950 -- Version : 1.0
951 -- History : 08-Mar-2005 ABINDAL created.
952 -- End of comments
953 ---------------------------------------------------------------------------
954 FUNCTION get_child_record ( p_pool_id IN okl_subsidy_pools_b.id%TYPE )
955
956 RETURN subsidy_pool_tbl_type
957
958 IS
959
960 --Cursor for displaying the summary of all the children of a parent pool.
961 CURSOR c_child_summary(cp_pool_id VARCHAR2) IS
962 SELECT id,
963 subsidy_pool_name,
964 pool_type_code,
965 currency_code,
966 currency_conversion_type,
967 reporting_pool_limit,
968 effective_from_date
969 FROM okl_subsidy_pools_b
970 WHERE subsidy_pool_id = cp_pool_id
971 AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
972 else
973 (case when exists (select 'x'
974 from okl_subsidies_v
975 where subsidy_pool_id = okl_subsidy_pools_b.id
976 and org_id <> mo_global.get_current_org_id()) then 0
977 else 1 end)
978 end))
979 ORDER BY subsidy_pool_name;
980
981 l_subsidy_pool_tbl subsidy_pool_tbl_type;
982 i NUMBER ;
983 BEGIN
984 i := 0;
985 -- Fetch all the child pool records and store it in a table of records.
986 -- return this table of records.
987 i := 1;
988 FOR each_row IN c_child_summary(p_pool_id)LOOP
989 l_subsidy_pool_tbl(i) := each_row;
990 i := i + 1;
991 END LOOP;
992
993 RETURN l_subsidy_pool_tbl;
994
995 END get_child_record;
996
997 ---------------------------------------------------------------------------
998 -- PROCEDURE PRINT_CHILD_RECORD
999 ---------------------------------------------------------------------------
1000 ---------------------------------------------------------------------------
1001 -- Start of comments
1002 --
1003 -- Procedure Name : PRINT_CHILD_RECORD
1004 -- Description : To print all the child subsidy pool records.
1005 -- Business Rules :
1006 -- Parameters : p_pool_tbl, p_input_date, p_to_currency_code
1007 -- Version : 1.0
1008 -- History : 08-Mar-2005 ABINDAL created.
1009 -- End of comments
1010 ---------------------------------------------------------------------------
1011 PROCEDURE print_child_record (p_pool_tbl IN subsidy_pool_tbl_type,
1012 p_input_date IN DATE,
1013 p_to_currency_code IN VARCHAR2,
1014 p_conv_type IN VARCHAR2,
1015 x_return_status OUT NOCOPY VARCHAR2,
1016 x_msg_count OUT NOCOPY NUMBER ,
1017 x_msg_data OUT NOCOPY VARCHAR2 )
1018 IS
1019
1020 CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
1021 SELECT id ,
1022 currency_code,
1023 currency_conversion_type
1024 FROM okl_subsidy_pools_b pool
1025 WHERE pool_type_code = 'BUDGET'
1026 CONNECT BY PRIOR id = subsidy_pool_id
1027 START WITH id = cp_pool_id;
1028
1029 l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
1030 l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE;
1031 l_budget okl_subsidy_pools_b.total_budgets%TYPE;
1032 l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE;
1033 l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
1034 l_conv_rate NUMBER;
1035 l_Pool_Name_len CONSTANT NUMBER DEFAULT 30;
1036 l_Pool_Type_len CONSTANT NUMBER DEFAULT 30;
1037 l_Currency_Code_len CONSTANT NUMBER DEFAULT 15;
1038 l_Pool_Limit_len CONSTANT NUMBER DEFAULT 20;
1039 l_Budget_len CONSTANT NUMBER DEFAULT 20;
1040 l_Remaining_Balance_len CONSTANT NUMBER DEFAULT 20;
1041 l_total_length CONSTANT NUMBER DEFAULT 152;
1042 i NUMBER;
1043 l_reporting_limit okl_subsidy_pools_b.reporting_pool_limit%TYPE DEFAULT NULL;
1044 l_api_name CONSTANT VARCHAR2(30) := 'print_child_record';
1045 l_msg_count NUMBER;
1046 l_msg_data VARCHAR2(2000);
1047 l_return_status VARCHAR2(1);
1048 l_api_version NUMBER;
1049 l_init_msg_list VARCHAR2(1);
1050 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.PRINT_CHILD_RECORD';
1051 l_debug_enabled VARCHAR2(10);
1052 is_debug_procedure_on BOOLEAN;
1053 is_debug_statement_on BOOLEAN;
1054
1055 BEGIN
1056 l_debug_enabled := okl_debug_pub.check_log_enabled;
1057 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1058 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1059 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call print_child_record');
1060 END IF;
1061 -- check for logging on STATEMENT level
1062 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
1063
1064 l_api_version := 1.0;
1065 l_init_msg_list := Okl_Api.g_false;
1066 l_msg_count := 0;
1067 l_total_budget := 0;
1068 l_budget := 0;
1069 l_trx_amount := 0;
1070 l_trx_amt := 0;
1071 l_remaining_balance := 0;
1072 l_conv_rate := 0;
1073 i := 0;
1074
1075 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
1076 G_PKG_NAME,
1077 l_init_msg_list,
1078 l_api_version,
1079 l_api_version,
1080 '_PVT',
1081 l_return_status);
1082
1083 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1084 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1085 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1086 RAISE OKL_API.G_EXCEPTION_ERROR;
1087 END IF;
1088
1089 -- Print child header.
1090 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1091 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CHILDREN_POOL'));
1092 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', 30 , '-' ));
1093 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
1094 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1095 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_SUBSIDY_POOL_NAME'),l_Pool_Name_len,'TITLE')||' '||
1096 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_POOL_TYPE_TXT'),l_Pool_Type_len,'TITLE')||' '||
1097 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_AGN_RPT_CURRENCY'),l_Currency_Code_len,'TITLE')||' '||
1098 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_SUBSIDY_POOL_LIMIT'),l_Pool_Limit_len,'TITLE')||' '||
1099 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_POOL_BUDGET'),l_Budget_len,'TITLE')||' '||
1100 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_POOL_BALANCE'),l_Remaining_Balance_len,'TITLE'));
1101 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=', l_total_length+8 , '=' ));
1102 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1103
1104 -- For all the records in a table, print the records.
1105 FOR i IN p_pool_tbl.first..p_pool_tbl.last LOOP
1106 l_total_budget := 0;
1107 l_trx_amount := 0;
1108 -- If subsidy pool is of type reporting then, calculate the total budgets and remaining balance for that pool,
1109 -- from all its children which are of type budget.
1110 IF(p_pool_tbl(i).pool_type_code = 'REPORTING') THEN
1111 FOR each_row IN get_amounts(p_pool_tbl(i).id) LOOP
1112 l_budget := total_budgets( each_row.id,
1113 p_input_date,
1114 each_row.currency_code,
1115 p_to_currency_code,
1116 p_conv_type,
1117 l_return_status,
1118 l_msg_count,
1119 l_msg_data
1120 );
1121 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1122 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1123 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1124 RAISE OKL_API.G_EXCEPTION_ERROR;
1125 END IF;
1126 l_total_budget := l_total_budget + l_budget;
1127 l_trx_amt := transaction_amount( each_row.id,
1128 p_input_date,
1129 each_row.currency_code,
1130 p_to_currency_code,
1131 p_conv_type,
1132 l_return_status,
1133 l_msg_count,
1134 l_msg_data
1135 );
1136 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1137 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1138 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1139 RAISE OKL_API.G_EXCEPTION_ERROR;
1140 END IF;
1141 l_trx_amount := l_trx_amount + l_trx_amt;
1142 END LOOP;
1143 ELSE
1144 -- if pool type is budget then simply calculate the total budgets and remining balance of a pool.
1145 l_budget := total_budgets( p_pool_tbl(i).id,
1146 p_input_date,
1147 p_pool_tbl(i).currency_code,
1148 p_to_currency_code,
1149 p_conv_type,
1150 l_return_status,
1151 l_msg_count,
1152 l_msg_data
1153 );
1154 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1155 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1156 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1157 RAISE OKL_API.G_EXCEPTION_ERROR;
1158 END IF;
1159 l_total_budget := l_budget;
1160 l_trx_amt := transaction_amount( p_pool_tbl(i).id,
1161 p_input_date,
1162 p_pool_tbl(i).currency_code,
1163 p_to_currency_code,
1164 p_conv_type,
1165 l_return_status,
1166 l_msg_count,
1167 l_msg_data
1168 );
1169 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1170 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1171 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1172 RAISE OKL_API.G_EXCEPTION_ERROR;
1173 END IF;
1174 l_trx_amount := l_trx_amt;
1175 END IF;
1176 l_remaining_balance := l_total_budget - l_trx_amount;
1177 -- Convert the reporting pool limit amount from pool currency to the parent pool(user entered) currency.
1178 l_reporting_limit := currency_conversion(p_pool_tbl(i).reporting_pool_limit,
1179 p_pool_tbl(i).currency_code,
1180 p_to_currency_code,
1181 p_conv_type,
1182 p_pool_tbl(i).effective_from_date,
1183 l_conv_rate
1184 );
1185 IF (l_reporting_limit < 0) THEN
1186 fnd_message.set_name( G_APP_NAME,
1187 'OKL_POOL_CURR_CONV');
1188 fnd_message.set_token('FROM_CURR',
1189 p_pool_tbl(i).currency_code);
1190 fnd_message.set_token('TO_CURR',
1191 p_to_currency_code);
1192 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
1193 END IF;
1194
1195 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1196 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1197 l_module,
1198 'l_reporting_limit '||l_reporting_limit||' l_total_budget '
1199 ||l_total_budget||' l_trx_amount '||l_trx_amount
1200 ||'l_remaining_balance'||l_remaining_balance
1201 );
1202 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1203
1204 -- Print the child records
1205 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1206 GET_PROPER_LENGTH(p_pool_tbl(i).subsidy_pool_name,l_Pool_Name_len,'DATA')||' '||
1207 GET_PROPER_LENGTH(p_pool_tbl(i).pool_type_code,l_Pool_Type_len,'DATA')||' '||
1208 GET_PROPER_LENGTH(p_to_currency_code,l_Currency_Code_len,'DATA')||' '||
1209 GET_PROPER_LENGTH(okl_accounting_util.format_amount(l_reporting_limit,p_to_currency_code)
1210 ,l_Pool_Limit_len,'DATA')||' '||
1211 GET_PROPER_LENGTH(okl_accounting_util.format_amount(l_total_budget,p_to_currency_code),l_Budget_len,'DATA')||' '||
1212 GET_PROPER_LENGTH(okl_accounting_util.format_amount(l_remaining_balance,p_to_currency_code),l_Remaining_Balance_len,'DATA'));
1213 END LOOP;
1214 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
1215
1216 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1217 x_return_status := l_return_status;
1218 x_msg_data := l_msg_data;
1219 x_msg_count := l_msg_count;
1220
1221 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1222 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call print_child_record');
1223 END IF;
1224
1225 EXCEPTION
1226 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1227 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1228 (
1229 l_api_name,
1230 G_PKG_NAME,
1231 'OKL_API.G_RET_STS_ERROR',
1232 x_msg_count,
1233 x_msg_data,
1234 '_PVT'
1235 );
1236
1237 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1238 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1239 p_api_name => l_api_name,
1240 p_pkg_name => G_PKG_NAME,
1241 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1242 x_msg_count => x_msg_count,
1243 x_msg_data => x_msg_data,
1244 p_api_type => g_api_type);
1245
1246 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1247 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1248 p_api_name => l_api_name,
1249 p_pkg_name => G_PKG_NAME,
1250 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1251 x_msg_count => x_msg_count,
1252 x_msg_data => x_msg_data,
1253 p_api_type => g_api_type);
1254
1255 WHEN OTHERS THEN
1256 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1257 p_api_name => l_api_name,
1258 p_pkg_name => G_PKG_NAME,
1259 p_exc_name => 'OTHERS',
1260 x_msg_count => x_msg_count,
1261 x_msg_data => x_msg_data,
1262 p_api_type => g_api_type);
1263
1264 END print_child_record;
1265
1266 -------------------------------------------------------------------------------
1267 -- PROCEDURE POOL_ASSOC_REPORT
1268 -------------------------------------------------------------------------------
1269 -- Start of comments
1270 --
1271 -- Procedure Name : POOL_ASSOC_REPORT
1272 -- Description : Procedure for Subsidy pool association Report Generation
1273 -- Business Rules :
1274 -- Parameters : required parameters are p_pool_name
1275 -- Version : 1.0
1276 -- History : 08-Mar-2005 ABINDAL created
1277 -- End of comments
1278 -------------------------------------------------------------------------------
1279 PROCEDURE POOL_ASSOC_REPORT(x_errbuf OUT NOCOPY VARCHAR2,
1280 x_retcode OUT NOCOPY NUMBER,
1281 p_pool_id IN okl_subsidy_pools_b.id%TYPE,
1282 p_date IN VARCHAR2)
1283
1284 IS
1285
1286
1287 l_subsidy_pool_tbl subsidy_pool_tbl_type;
1288 l_tbl subsidy_pool_tbl_type;
1289 l_from_date DATE;
1290 i NUMBER ;
1291 j NUMBER ;
1292 k NUMBER ;
1293 l_count NUMBER ;
1294 l_pool_rec okl_sub_pool_rec;
1295 l_total_budget okl_subsidy_pools_b.total_budgets%TYPE ;
1296 l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
1297 l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
1298 l_api_name CONSTANT VARCHAR2(30) := 'POOL_ASSOC_REPORT';
1299 l_msg_count NUMBER;
1300 l_msg_data VARCHAR2(2000);
1301 l_return_status VARCHAR2(1);
1302 l_api_version NUMBER;
1303 l_init_msg_list VARCHAR2(1);
1304 --length
1305 l_total_length CONSTANT NUMBER DEFAULT 152;
1306 l_sysdate DATE ;
1307 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.POOL_ASSOC_REPORT';
1308 l_debug_enabled VARCHAR2(10);
1309 is_debug_procedure_on BOOLEAN;
1310 is_debug_statement_on BOOLEAN;
1311
1312 BEGIN
1313 l_debug_enabled := okl_debug_pub.check_log_enabled;
1314 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1315 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1316 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call pool_assoc_report');
1317 END IF;
1318 -- check for logging on STATEMENT level
1319 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
1320
1321 l_api_version := 1.0;
1322 l_init_msg_list := Okl_Api.g_false;
1323 l_msg_count := 0;
1324 i := 0;
1325 j := 0;
1326 k := 0;
1327 l_count := 0;
1328 l_total_budget := 0;
1329 l_trx_amount := 0;
1330 l_remaining_balance := 0;
1331 l_sysdate := TRUNC(SYSDATE);
1332
1333 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
1334 G_PKG_NAME,
1335 l_init_msg_list,
1336 l_api_version,
1337 l_api_version,
1338 '_PVT',
1339 l_return_status);
1340 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1341 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1342 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1343 RAISE OKL_API.G_EXCEPTION_ERROR;
1344 END IF;
1345
1346 l_from_date:= FND_DATE.CANONICAL_TO_DATE(p_date);
1347
1348 -- Printing Subsidy pools report header.
1349 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 52 , ' ' ) || fnd_message.get_string('OKL','OKLHOMENAVTITLE') ||
1350 RPAD(' ', 53 , ' ' ));
1351 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1352 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1353 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 52 , ' ' ) || fnd_message.get_string('OKL','OKL_SUBSIDY_POOL_REPORT') ||
1354 RPAD(' ', 53 , ' ' ));
1355 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || '-------------------------------' || RPAD(' ', 51, ' ' ));
1356 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1357 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1358 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1359
1360 -- Get the parent record, the record for the pool which user has entered.
1361 l_pool_rec := get_parent_record(p_pool_id);
1362 -- if that record is found then print the parent header and record.
1363
1364 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1365 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1366 l_module,
1367 'l_pool_rec.id '||l_pool_rec.id
1368 );
1369 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1370
1371 IF (l_pool_rec.id is not null) THEN
1372 print_parent_record(l_pool_rec,
1373 l_from_date,
1374 l_pool_rec.currency_code,
1375 l_pool_rec.currency_conversion_type,
1376 l_return_status,
1377 l_msg_count,
1378 l_msg_data
1379 );
1380 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1381 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1382 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1383 RAISE OKL_API.G_EXCEPTION_ERROR;
1384 END IF;
1385 END IF;
1386 -- Get all the child records of this parent subsidy pool.
1387 l_subsidy_pool_tbl := get_child_record(l_pool_rec.id);
1388 -- If the child record exists then print the child header and all the
1389 -- child records of the parent pool.
1390
1391 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1392 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1393 l_module,
1394 'l_subsidy_pool_tbl.count '||l_subsidy_pool_tbl.count
1395 );
1396 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1397
1398 IF l_subsidy_pool_tbl.count > 0 THEN
1399 print_child_record(l_subsidy_pool_tbl,
1400 l_from_date,
1401 l_pool_rec.currency_code,
1402 l_pool_rec.currency_conversion_type,
1403 l_return_status,
1404 l_msg_count,
1405 l_msg_data
1406 );
1407 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1408 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1409 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1410 RAISE OKL_API.G_EXCEPTION_ERROR;
1411 END IF;
1412 END IF;
1413
1414 -- for all the child records, search if any further childs of these records
1415 -- exists, if yes print those records also.
1416 -- Take a table of records of the child subsidy pools.For each records in the table
1417 -- if further child pools are found then append these records in this array and the
1418 -- size of the array increases. Run the loop till this array ends.
1419 IF l_subsidy_pool_tbl.count > 0 THEN
1420 i := l_subsidy_pool_tbl.first;
1421 j := l_subsidy_pool_tbl.count;
1422 LOOP EXIT WHEN i > j;
1423 k := 0;
1424 l_tbl := get_child_record(l_subsidy_pool_tbl(i).id);
1425 -- If child record is found then print this record as a parent and then print all the
1426 -- child records of this pool.
1427 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1428 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1429 l_module,
1430 'l_tbl.count '||l_tbl.count
1431 );
1432 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1433
1434 IF l_tbl.count > 0 THEN
1435 print_parent_record(l_subsidy_pool_tbl(i),
1436 l_from_date,
1437 l_pool_rec.currency_code,
1438 l_pool_rec.currency_conversion_type,
1439 l_return_status,
1440 l_msg_count,
1441 l_msg_data
1442 );
1443 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1444 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1445 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1446 RAISE OKL_API.G_EXCEPTION_ERROR;
1447 END IF;
1448 print_child_record(l_tbl,
1449 l_from_date,
1450 l_pool_rec.currency_code,
1451 l_pool_rec.currency_conversion_type,
1452 l_return_status,
1453 l_msg_count,
1454 l_msg_data
1455 );
1456 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1457 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1458 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1459 RAISE OKL_API.G_EXCEPTION_ERROR;
1460 END IF;
1461 l_count := l_subsidy_pool_tbl.count + 1;
1462 -- Appending in the array if the child records are found.
1463 FOR k IN l_tbl.first..l_tbl.last LOOP
1464 l_subsidy_pool_tbl(l_count) := l_tbl(k);
1465 l_count := l_count + 1;
1466 END LOOP;
1467 END IF;
1468 j := l_subsidy_pool_tbl.count;
1469 i := i + 1;
1470 END LOOP;
1471 END IF;
1472 okl_api.END_ACTIVITY(l_msg_count, l_msg_data);
1473
1474 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1475 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call pool_assoc_report');
1476 END IF;
1477
1478 EXCEPTION
1479 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1480 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
1481 p_api_name => l_api_name,
1482 p_pkg_name => G_PKG_NAME,
1483 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1484 x_msg_count => l_msg_count,
1485 x_msg_data => l_msg_data,
1486 p_api_type => g_api_type);
1487
1488 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1489 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
1490 p_api_name => l_api_name,
1491 p_pkg_name => G_PKG_NAME,
1492 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1493 x_msg_count => l_msg_count,
1494 x_msg_data => l_msg_data,
1495 p_api_type => g_api_type);
1496
1497 WHEN OTHERS THEN
1498 x_errbuf := SQLERRM;
1499 x_retcode := 2;
1500
1501 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
1502
1503 IF (SQLCODE <> -20001) THEN
1504 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
1505 --APP_EXCEPTION.RAISE_EXCEPTION;
1506 RAISE;
1507 ELSE
1508 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
1509 --g_error_message := Sqlerrm;
1510 APP_EXCEPTION.RAISE_EXCEPTION;
1511 END IF;
1512
1513 END pool_assoc_report;
1514
1515 ---------------------------------------------------------------------------
1516 -- PROCEDURE PRINT_POOL_SUMMARY
1517 ---------------------------------------------------------------------------
1518 ---------------------------------------------------------------------------
1519 -- Start of comments
1520 --
1521 -- Procedure Name : PRINT_POOL_SUMMARY
1522 -- Description : To print the subsidy pool summary.
1523 -- Business Rules :
1524 -- Parameters : p_pool_rec, p_from_date,p_to_date, x_return_status,
1525 -- x_msg_count,x_msg_data
1526 -- Version : 1.0
1527 -- History : 08-Mar-2005 ABINDAL created.
1528 -- End of comments
1529 ---------------------------------------------------------------------------
1530 PROCEDURE print_pool_summary (p_pool_rec IN okl_sub_pool_rec,
1531 p_from_date IN DATE,
1532 p_to_date IN DATE,
1533 x_return_status OUT NOCOPY VARCHAR2,
1534 x_msg_count OUT NOCOPY NUMBER,
1535 x_msg_data OUT NOCOPY VARCHAR2 )
1536 IS
1537
1538 -- Cursor to fetch all the children pools of a subsidy pool entered.
1539 CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
1540 SELECT id ,
1541 currency_code
1542 FROM okl_subsidy_pools_v pool
1543 WHERE pool_type_code = 'BUDGET'
1544 CONNECT BY PRIOR id = subsidy_pool_id
1545 START WITH id = cp_pool_id;
1546
1547 l_budget okl_subsidy_pools_b.total_budgets%TYPE;
1548 l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
1549 l_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
1550 l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
1551 l_Pool_Name_len CONSTANT NUMBER DEFAULT 30;
1552 l_Pool_Type_len CONSTANT NUMBER DEFAULT 30;
1553 l_Currency_Code_len CONSTANT NUMBER DEFAULT 15;
1554 l_Pool_Limit_len CONSTANT NUMBER DEFAULT 20;
1555 l_Budget_len CONSTANT NUMBER DEFAULT 20;
1556 l_trx_amt_len CONSTANT NUMBER DEFAULT 20;
1557 l_Remaining_Balance_len CONSTANT NUMBER DEFAULT 20;
1558 l_total_length CONSTANT NUMBER DEFAULT 152;
1559 l_api_name CONSTANT VARCHAR2(30) := 'print_pool_summary';
1560 l_msg_count NUMBER;
1561 l_msg_data VARCHAR2(2000);
1562 l_return_status VARCHAR2(1);
1563 l_api_version NUMBER;
1564 l_init_msg_list VARCHAR2(1);
1565 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.PRINT_POOL_SUMMARY';
1566 l_debug_enabled VARCHAR2(10);
1567 is_debug_procedure_on BOOLEAN;
1568 is_debug_statement_on BOOLEAN;
1569
1570 BEGIN
1571 l_debug_enabled := okl_debug_pub.check_log_enabled;
1572 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1573 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1574 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call print_pool_summary');
1575 END IF;
1576 -- check for logging on STATEMENT level
1577 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
1578
1579 l_api_version := 1.0;
1580 l_init_msg_list := Okl_Api.g_false;
1581 l_msg_count := 0;
1582 l_budget := 0;
1583 l_trx_amt := 0;
1584 l_amount := 0;
1585 l_remaining_balance := 0;
1586
1587 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
1588 G_PKG_NAME,
1589 l_init_msg_list,
1590 l_api_version,
1591 l_api_version,
1592 '_PVT',
1593 l_return_status);
1594
1595 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1596 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1597 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1598 RAISE OKL_API.G_EXCEPTION_ERROR;
1599 END IF;
1600
1601 -- If subsidy pool type is "Budget" then pick the values for total budgets
1602 -- from the okl_subsidy_pools_b table and calculate the total transaction amount
1603 -- from the okl_trx_subsidy_pools table.
1604 IF (p_pool_rec.pool_type_code = 'BUDGET') THEN
1605 -- Parent pool header with the parent pool name.
1606 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1607 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1608 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
1609 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1610 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_SUBSIDY_POOL_NAME'),l_Pool_Name_len,'TITLE')||' '||
1611 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_POOL_TYPE_TXT'),l_Pool_Type_len,'TITLE')||' '||
1612 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_AGN_RPT_CURRENCY'),l_Currency_Code_len,'TITLE')||' '||
1613 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_POOL_BUDGET'),l_Budget_len,'TITLE')||' '||
1614 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_TRX_AMOUNT'),l_trx_amt_len,'TITLE')||' '||
1615 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_POOL_BALANCE'),l_Remaining_Balance_len,'TITLE'));
1616 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=', l_total_length+8 , '=' ));
1617 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1618 -- Calculate the total budgets for subsidy pool till the date specified.
1619 l_budget := total_budgets( p_pool_rec.id,
1620 p_to_date,
1621 p_pool_rec.currency_code,
1622 p_pool_rec.currency_code,
1623 p_pool_rec.currency_conversion_type,
1624 l_return_status,
1625 l_msg_count,
1626 l_msg_data
1627 );
1628 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1629 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1630 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1631 RAISE OKL_API.G_EXCEPTION_ERROR;
1632 END IF;
1633 -- calculate the total transaction amount for the subsidy pool.
1634 l_trx_amt := transaction_amount( p_pool_rec.id,
1635 p_to_date,
1636 p_pool_rec.currency_code,
1637 p_pool_rec.currency_code,
1638 p_pool_rec.currency_conversion_type,
1639 l_return_status,
1640 l_msg_count,
1641 l_msg_data
1642 );
1643 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1644 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1645 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1646 RAISE OKL_API.G_EXCEPTION_ERROR;
1647 END IF;
1648 -- remaining balance for subsidy pool is total budgets minus the total transaction amount.
1649 l_remaining_balance := l_budget - l_trx_amt;
1650
1651 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1652 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1653 l_module,
1654 ' l_budget '||l_budget||' l_trx_amt '||l_trx_amt
1655 ||'l_remaining_balance'||l_remaining_balance
1656 );
1657 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1658
1659 -- Print the parent pool record
1660 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1661 GET_PROPER_LENGTH(p_pool_rec.subsidy_pool_name,l_Pool_Name_len,'DATA')||' '||
1662 GET_PROPER_LENGTH(p_pool_rec.pool_type_code,l_Pool_Type_len,'DATA')||' '||
1663 GET_PROPER_LENGTH(p_pool_rec.currency_code,l_Currency_Code_len,'DATA')||' '||
1664 GET_PROPER_LENGTH(okl_accounting_util.format_amount(l_budget,p_pool_rec.currency_code),l_Budget_len,'DATA')||' '||
1665 GET_PROPER_LENGTH(okl_accounting_util.format_amount(l_trx_amt,p_pool_rec.currency_code),l_trx_amt_len,'DATA')||' '||
1666 GET_PROPER_LENGTH(okl_accounting_util.format_amount(l_remaining_balance,p_pool_rec.currency_code),l_Remaining_Balance_len,'DATA'));
1667 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
1668
1669 -- if subsidy pool type is "Reporting" then the total budgets and total transaction amount
1670 -- is calculated from its children as there is no transaction and budgets for pool type "Reporting"
1671 ELSIF (p_pool_rec.pool_type_code = 'REPORTING') THEN
1672 -- Parent pool header with the parent pool name.
1673 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1674 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1675 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
1676 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1677 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_SUBSIDY_POOL_NAME'),l_Pool_Name_len,'TITLE')||' '||
1678 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_POOL_TYPE_TXT'),l_Pool_Type_len,'TITLE')||' '||
1679 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_AGN_RPT_CURRENCY'),l_Currency_Code_len,'TITLE')||' '||
1680 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_SUBSIDY_POOL_LIMIT'),l_Pool_Limit_len,'TITLE')||' '||
1681 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_TRX_AMOUNT'),l_trx_amt_len,'TITLE'));
1682 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=', l_total_length+8 , '=' ));
1683 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1684
1685 -- For each of the children found for "Reporting" pool type
1686 FOR each_row IN get_amounts(p_pool_rec.id) LOOP
1687 -- calculate the transaction amount and convert the children pool currency
1688 -- in to the "Reporting" pool currency.
1689 l_amount := transaction_amount ( each_row.id,
1690 p_to_date,
1691 each_row.currency_code,
1692 p_pool_rec.currency_code,
1693 p_pool_rec.currency_conversion_type,
1694 l_return_status,
1695 l_msg_count,
1696 l_msg_data
1697 );
1698 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1699 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1700 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1701 RAISE OKL_API.G_EXCEPTION_ERROR;
1702 END IF;
1703 l_trx_amt := l_trx_amt + l_amount;
1704 END LOOP;
1705 -- Print the subsidy pool sumaary.
1706 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1707 GET_PROPER_LENGTH(p_pool_rec.subsidy_pool_name,l_Pool_Name_len,'DATA')||' '||
1708 GET_PROPER_LENGTH(p_pool_rec.pool_type_code,l_Pool_Type_len,'DATA')||' '||
1709 GET_PROPER_LENGTH(p_pool_rec.currency_code,l_Currency_Code_len,'DATA')||' '||
1710 GET_PROPER_LENGTH(okl_accounting_util.format_amount(p_pool_rec.reporting_pool_limit,p_pool_rec.currency_code),l_Pool_Limit_len,'DATA')||' '||
1711 GET_PROPER_LENGTH(okl_accounting_util.format_amount(l_trx_amt,p_pool_rec.currency_code),l_trx_amt_len,'DATA'));
1712 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
1713 END IF;
1714
1715 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1716 x_return_status := l_return_status;
1717 x_msg_data := l_msg_data;
1718 x_msg_count := l_msg_count;
1719
1720 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1721 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call print_pool_summary');
1722 END IF;
1723
1724 EXCEPTION
1725 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1726 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1727 (
1728 l_api_name,
1729 G_PKG_NAME,
1730 'OKL_API.G_RET_STS_ERROR',
1731 x_msg_count,
1732 x_msg_data,
1733 '_PVT'
1734 );
1735
1736 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1737 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1738 p_api_name => l_api_name,
1739 p_pkg_name => G_PKG_NAME,
1740 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1741 x_msg_count => x_msg_count,
1742 x_msg_data => x_msg_data,
1743 p_api_type => g_api_type);
1744
1745 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1746 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1747 p_api_name => l_api_name,
1748 p_pkg_name => G_PKG_NAME,
1749 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1750 x_msg_count => x_msg_count,
1751 x_msg_data => x_msg_data,
1752 p_api_type => g_api_type);
1753
1754 WHEN OTHERS THEN
1755 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1756 p_api_name => l_api_name,
1757 p_pkg_name => G_PKG_NAME,
1758 p_exc_name => 'OTHERS',
1759 x_msg_count => x_msg_count,
1760 x_msg_data => x_msg_data,
1761 p_api_type => g_api_type);
1762
1763 END print_pool_summary;
1764
1765 ---------------------------------------------------------------------------
1766 -- PROCEDURE PRINT_TRANSACTION_SUMMARY
1767 ---------------------------------------------------------------------------
1768 ---------------------------------------------------------------------------
1769 -- Start of comments
1770 --
1771 -- Procedure Name : PRINT_TRANSACTION_SUMMARY
1772 -- Description : To print the subsidy pool summary.
1773 -- Business Rules :
1774 -- Parameters : p_pool_rec, p_from_date,p_to_date, x_return_status,
1775 -- x_msg_count,x_msg_data
1776 -- Version : 1.0
1777 -- History : 08-Mar-2005 ABINDAL created.
1778 -- End of comments
1779 ---------------------------------------------------------------------------
1780 PROCEDURE print_transaction_summary (p_pool_id IN okl_subsidy_pools_b.id%TYPE,
1781 p_from_date IN DATE,
1782 p_to_date IN DATE,
1783 p_pool_type IN okl_subsidy_pools_b.pool_type_code%TYPE,
1784 p_pool_currency IN okl_subsidy_pools_b.currency_code%TYPE,
1785 p_conv_type IN okl_subsidy_pools_b.currency_conversion_type%TYPE,
1786 x_return_status OUT NOCOPY VARCHAR2,
1787 x_msg_count OUT NOCOPY NUMBER ,
1788 x_msg_data OUT NOCOPY VARCHAR2 )
1789 IS
1790
1791 -- cursor to fetch all the transactions details for the subsidy pool between the dates entered by user.
1792 CURSOR c_transaction_detail(cp_pool_id okl_subsidy_pools_b.id%TYPE, cp_from_date DATE, cp_to_date DATE) IS
1793 SELECT flk1.meaning trx_reason,
1794 --START:14-DEC-05 cklee - Fixed bug#4884558 |
1795 -- khr.contract_number,
1796 (case
1797 when pool.source_type_code = 'LEASE_CONTRACT' then
1798 (select khr.contract_number
1799 from okc_k_headers_b khr
1800 where khr.id = pool.source_object_id)
1801 --START:| 26-JAN-05 cklee - Fixed bug#5002229 |
1802 -- when pool.source_type_code in ('SALES_QUOTE', 'LEASE_APPLICATION') then
1803 when pool.source_type_code = 'SALES_QUOTE' then
1804 --END| 26-JAN-05 cklee - Fixed bug#5002229 |
1805 (select sq.reference_number
1806 from okl_lease_quotes_b sq
1807 where sq.id = pool.source_object_id)
1808 --START:| 26-JAN-05 cklee - Fixed bug#5002229 |
1809 when pool.source_type_code = 'LEASE_APPLICATION' then
1810 --END| 26-JAN-05 cklee - Fixed bug#5002229 |
1811 (select lap.reference_number
1812 from okl_lease_applications_b lap,
1813 okl_lease_quotes_b lsq
1814 where lsq.parent_object_id = lap.id
1815 and lsq.parent_object_code = 'LEASEAPP'
1816 and lsq.id = pool.source_object_id)
1817 --END| 26-JAN-05 cklee - Fixed bug#5002229 |
1818 end) contract_number,
1819 --END:14-DEC-05 cklee - Fixed bug#4884558 |
1820 dnz_asset_number,
1821 vend.vendor_name Vendor,
1822 sub.name subsidy_name,
1823 trx_type_code,
1824 source_trx_date,
1825 trx_currency_code,
1826 trx_amount,
1827 subsidy_pool_currency_code,
1828 pool.conversion_rate,
1829 subsidy_pool_amount,
1830 trx_date,
1831 -- abindal start bug# 4873705 --
1832 hru.name operating_unit
1833 -- abindal end bug# 4873705 --
1834 FROM okl_trx_subsidy_pools pool,
1835 fnd_lookups flk1,
1836 po_vendors vend,
1837 okl_subsidies_b sub,
1838 -- abindal start bug# 4873705 --
1839 hr_organization_units hru
1840 -- abindal end bug# 4873705 --
1841 --START:14-DEC-05 cklee - Fixed bug#4884558 |
1842 --, okc_k_headers_b khr
1843 --END:14-DEC-05 cklee - Fixed bug#4884558 |
1844 WHERE flk1.lookup_type = 'OKL_SUB_POOL_TRX_REASON_TYPE'
1845 AND flk1.lookup_code = pool.trx_reason_code
1846 AND vend.vendor_id = pool.vendor_id
1847 AND sub.id = pool.subsidy_id
1848 -- AND TRUNC(source_trx_date) >= NVL(TRUNC(cp_from_date),TRUNC(source_trx_date))
1849 -- AND TRUNC(source_trx_date) <= NVL(TRUNC(cp_to_date), TRUNC(source_trx_date))
1850 AND pool.subsidy_pool_id IN ( SELECT id
1851 FROM okl_subsidy_pools_b
1852 WHERE pool_type_code = 'BUDGET'
1853 CONNECT BY PRIOR id = subsidy_pool_id
1854 START WITH id = cp_pool_id
1855 )
1856 --START:14-DEC-05 cklee - Fixed bug#4884558 |
1857 -- AND khr.id = pool.source_object_id
1858 --END:14-DEC-05 cklee - Fixed bug#4884558 |
1859 -- abindal start bug# 4873705 --
1860 AND sub.org_id = hru.organization_id
1861 -- abindal end bug# 4873705 --
1862 --START: 09-Mar-05 cklee - Fixed bug#4659748 |
1863 --ORDER BY source_trx_date;
1864 ORDER BY trx_date asc;
1865 --END: 09-Mar-05 cklee - Fixed bug#4659748 |
1866
1867 l_trx_reason_len CONSTANT NUMBER DEFAULT 27;
1868 l_source_len CONSTANT NUMBER DEFAULT 20;
1869 l_asset_len CONSTANT NUMBER DEFAULT 15;
1870 l_vendor_len CONSTANT NUMBER DEFAULT 25;
1871 l_subsidy_len CONSTANT NUMBER DEFAULT 20;
1872 l_src_trx_date_len CONSTANT NUMBER DEFAULT 13;
1873 l_trx_amt_len CONSTANT NUMBER DEFAULT 15;
1874 l_conv_rate_len CONSTANT NUMBER DEFAULT 10;
1875 l_pool_amt_len CONSTANT NUMBER DEFAULT 20;
1876 -- abindal start bug# 4873705 --
1877 l_oper_unit_len CONSTANT NUMBER DEFAULT 30;
1878 -- abindal end bug# 4873705 --
1879 l_total_length CONSTANT NUMBER DEFAULT 250;
1880 l_total_amt_len CONSTANT NUMBER DEFAULT 15;
1881 l_tot_bdgt_len CONSTANT NUMBER DEFAULT 137;
1882 l_curr_len CONSTANT NUMBER DEFAULT 3;
1883 l_pool_amount NUMBER;
1884 l_amount NUMBER;
1885 l_conv_rate NUMBER;
1886 l_sub_pool_amount NUMBER;
1887 l_trx_amount NUMBER;
1888 l_api_name CONSTANT VARCHAR2(30) := 'print_transaction_summary';
1889 l_msg_count NUMBER;
1890 l_msg_data VARCHAR2(2000);
1891 l_return_status VARCHAR2(1);
1892 l_api_version NUMBER;
1893 l_init_msg_list VARCHAR2(1);
1894 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.PRINT_TRANSACTION_SUMMARY';
1895 l_debug_enabled VARCHAR2(10);
1896 is_debug_procedure_on BOOLEAN;
1897 is_debug_statement_on BOOLEAN;
1898
1899 BEGIN
1900
1901 l_debug_enabled := okl_debug_pub.check_log_enabled;
1902 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1903
1904 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1905 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call print_transaction_summary');
1906 END IF;
1907 -- check for logging on STATEMENT level
1908 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
1909
1910 l_api_version := 1.0;
1911 l_init_msg_list := Okl_Api.g_false;
1912 l_msg_count := 0;
1913 l_pool_amount := 0;
1914 l_amount := 0;
1915 l_conv_rate := 0;
1916 l_sub_pool_amount := 0;
1917 l_trx_amount := 0;
1918
1919 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
1920 G_PKG_NAME,
1921 l_init_msg_list,
1922 l_api_version,
1923 l_api_version,
1924 '_PVT',
1925 l_return_status);
1926 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1927 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1928 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1929 RAISE OKL_API.G_EXCEPTION_ERROR;
1930 END IF;
1931 -- Print the transaction header.
1932 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
1933 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_TRANSACTION'));
1934 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', 30 , '-' ));
1935 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length , '-' ));
1936 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1937 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_SOURCE'),l_trx_reason_len,'TITLE')||' '||
1938 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_NUMBER'),l_source_len,'TITLE')||' '||
1939 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_ASSET_NUMBER'),l_asset_len,'TITLE')||' '||
1940 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_VENDOR'),l_vendor_len,'TITLE')||' '||
1941 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_SUBSIDY'),l_subsidy_len,'TITLE')||' '||
1942 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_EXPT_DATE'),l_src_trx_date_len,'TITLE')||' '||
1943 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_TRX_AMOUNT'),l_trx_amt_len + l_curr_len,'TITLE')||' '||
1944 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_CURR_CONV_FACT'),l_conv_rate_len,'TITLE')||' '||
1945 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_RPT_AMOUNT'),l_pool_amt_len + l_curr_len,'TITLE')||' '||
1946 -- abindal start bug# 4873705 --
1947 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_OPERATING_UNIT'),l_oper_unit_len,'TITLE'));
1948 -- abindal end bug# 4873705 --
1949 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=', l_total_length , '=' ));
1950 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1951
1952 IF(p_pool_type = 'BUDGET') THEN
1953 FOR each_row IN c_transaction_detail(p_pool_id,p_from_date,p_to_date) LOOP
1954 -- If transaction line type is "Reduction" display the transaction amount as
1955 -- <transaction amount>.
1956 IF(each_row.trx_type_code = 'ADDITION') THEN
1957 l_trx_amount := each_row.trx_amount;
1958 l_sub_pool_amount := each_row.subsidy_pool_amount;
1959 ELSIF(each_row.trx_type_code = 'REDUCTION') THEN
1960 l_trx_amount := each_row.trx_amount * -1;
1961 l_sub_pool_amount := each_row.subsidy_pool_amount * -1;
1962 END IF;
1963 -- Display the transactions record for the subsidy pool type "Budget".
1964 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1965 GET_PROPER_LENGTH(each_row.trx_reason,l_trx_reason_len,'DATA')||' '||
1966 GET_PROPER_LENGTH(each_row.contract_number,l_source_len,'DATA')||' '||
1967 GET_PROPER_LENGTH(each_row.dnz_asset_number,l_asset_len,'DATA')||' '||
1968 GET_PROPER_LENGTH(each_row.Vendor,l_vendor_len,'DATA')||' '||
1969 GET_PROPER_LENGTH(each_row.subsidy_name,l_subsidy_len,'DATA')||' '||
1970 GET_PROPER_LENGTH(each_row.source_trx_date,l_src_trx_date_len,'DATA')||' '||
1971 LPAD(okl_accounting_util.format_amount(l_trx_amount,each_row.trx_currency_code)
1972 ||' '||each_row.trx_currency_code,l_trx_amt_len + l_curr_len,' ')||' '||
1973 LPAD(each_row.conversion_rate,l_conv_rate_len,' ')||' '||
1974 LPAD(okl_accounting_util.format_amount(l_sub_pool_amount,each_row.subsidy_pool_currency_code)
1975 ||' '|| each_row.subsidy_pool_currency_code,l_pool_amt_len + l_curr_len,' ')||' '||
1976 -- abindal start bug# 4873705 --
1977 GET_PROPER_LENGTH(each_row.operating_unit,l_oper_unit_len,'DATA'));
1978 -- abindal end bug# 4873705 --
1979
1980 -- for all the transactions record found add the transaction amount with type
1981 -- "Addition" and reduce the amount with type "Reduction".
1982 IF(each_row.trx_type_code = 'ADDITION') THEN
1983 l_pool_amount := l_pool_amount + each_row.subsidy_pool_amount;
1984 ELSE
1985 l_pool_amount := l_pool_amount - each_row.subsidy_pool_amount;
1986 END IF;
1987 END LOOP;
1988 -- Print the total transaction amount, calculated above, after all transactions data
1989 -- is displayed.
1990 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length , '-' ));
1991 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1992 RPAD(' ', l_tot_bdgt_len , ' ' )||
1993 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_TOTAL_AMOUNT'),l_total_amt_len,'TITLE')||' : '||
1994 LPAD(okl_accounting_util.format_amount(l_pool_amount,p_pool_currency),l_pool_amt_len,' ')||' '||
1995 GET_PROPER_LENGTH(p_pool_currency,l_curr_len,'DATA'));
1996 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length , '-' ));
1997
1998 ELSIF(p_pool_type = 'REPORTING') THEN
1999 FOR each_row IN c_transaction_detail(p_pool_id,p_from_date,p_to_date) LOOP
2000 -- If pool type is "Reporting", the transaction amount for all children "Budget"
2001 -- pool is converted in to the parent "Reporting" pool currency and this amount
2002 -- is displayed as a "Reporting amount".
2003 l_amount := currency_conversion(each_row.trx_amount,
2004 each_row.trx_currency_code,
2005 p_pool_currency,
2006 p_conv_type,
2007 each_row.trx_date,
2008 l_conv_rate
2009 );
2010 -- if negative value is returned display the error that the conversion between
2011 -- the two currencies is not found.
2012 IF (l_amount < 0) THEN
2013 fnd_message.set_name( G_APP_NAME,
2014 'OKL_POOL_CURR_CONV');
2015 fnd_message.set_token('FROM_CURR',
2016 each_row.subsidy_pool_currency_code);
2017 fnd_message.set_token('TO_CURR',
2018 p_pool_currency);
2019 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
2020 END IF;
2021 -- If transaction line type is "Reduction" display the transaction amount as
2022 -- <transaction amount>.
2023 IF(each_row.trx_type_code = 'ADDITION') THEN
2024 l_trx_amount := each_row.trx_amount;
2025 l_sub_pool_amount := l_amount;
2026 ELSIF(each_row.trx_type_code = 'REDUCTION') THEN
2027 l_trx_amount := each_row.trx_amount * -1;
2028 l_sub_pool_amount := l_amount * -1;
2029 END IF;
2030 -- Print the transactions record for the subsidy pool.
2031 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
2032 GET_PROPER_LENGTH(each_row.trx_reason,l_trx_reason_len,'DATA')||' '||
2033 GET_PROPER_LENGTH(each_row.contract_number,l_source_len,'DATA')||' '||
2034 GET_PROPER_LENGTH(each_row.dnz_asset_number,l_asset_len,'DATA')||' '||
2035 GET_PROPER_LENGTH(each_row.Vendor,l_vendor_len,'DATA')||' '||
2036 GET_PROPER_LENGTH(each_row.subsidy_name,l_subsidy_len,'DATA')||' '||
2037 GET_PROPER_LENGTH(each_row.source_trx_date,l_src_trx_date_len,'DATA')||' '||
2038 LPAD(okl_accounting_util.format_amount(l_trx_amount,each_row.trx_currency_code)
2039 ||' '||each_row.trx_currency_code,l_trx_amt_len + l_curr_len,' ')||' '||
2040 LPAD(l_conv_rate,l_conv_rate_len,' ')||' '||
2041 LPAD(okl_accounting_util.format_amount(l_sub_pool_amount,p_pool_currency)
2042 ||' '|| p_pool_currency,l_pool_amt_len + l_curr_len,' ')||' '||
2043 -- abindal start bug# 4873705 --
2044 GET_PROPER_LENGTH(each_row.operating_unit,l_oper_unit_len,'DATA'));
2045 -- abindal end bug# 4873705 --
2046
2047 -- for all the transactions record found add the transaction amount with type
2048 -- "Addition" and reduce the amount with type "Reduction".
2049 IF(each_row.trx_type_code = 'ADDITION') THEN
2050 l_pool_amount := l_pool_amount + l_amount;
2051 ELSE
2052 l_pool_amount := l_pool_amount - l_amount;
2053 END IF;
2054 END LOOP;
2055 -- Print the total subsidy amount at the end of all the transactions record.
2056 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length , '-' ));
2057 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
2058 RPAD(' ', l_tot_bdgt_len , ' ' )||
2059 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_TOTAL_AMOUNT'),l_total_amt_len,'TITLE')||' : '||
2060 LPAD(okl_accounting_util.format_amount(l_pool_amount,p_pool_currency),l_pool_amt_len,' ')||' '||
2061 GET_PROPER_LENGTH(p_pool_currency,l_curr_len,'DATA'));
2062 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length , '-' ));
2063 END IF;
2064
2065 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2066 x_return_status := l_return_status;
2067 x_msg_data := l_msg_data;
2068 x_msg_count := l_msg_count;
2069
2070 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
2071 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call print_transaction_summary');
2072 END IF;
2073
2074 EXCEPTION
2075 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2076 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2077 (
2078 l_api_name,
2079 G_PKG_NAME,
2080 'OKL_API.G_RET_STS_ERROR',
2081 x_msg_count,
2082 x_msg_data,
2083 '_PVT'
2084 );
2085
2086 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2087 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2088 p_api_name => l_api_name,
2089 p_pkg_name => G_PKG_NAME,
2090 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2091 x_msg_count => x_msg_count,
2092 x_msg_data => x_msg_data,
2093 p_api_type => g_api_type);
2094
2095 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2096 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2097 p_api_name => l_api_name,
2098 p_pkg_name => G_PKG_NAME,
2099 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2100 x_msg_count => x_msg_count,
2101 x_msg_data => x_msg_data,
2102 p_api_type => g_api_type);
2103
2104 WHEN OTHERS THEN
2105 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2106 p_api_name => l_api_name,
2107 p_pkg_name => G_PKG_NAME,
2108 p_exc_name => 'OTHERS',
2109 x_msg_count => x_msg_count,
2110 x_msg_data => x_msg_data,
2111 p_api_type => g_api_type);
2112
2113 END print_transaction_summary;
2114
2115 -------------------------------------------------------------------------------
2116 -- PROCEDURE POOL_RECONC_REPORT
2117 -------------------------------------------------------------------------------
2118 -- Start of comments
2119 --
2120 -- Procedure Name : POOL_RECONC_REPORT
2121 -- Description : Procedure for Subsidy pool reconciliation Report Generation
2122 -- Business Rules :
2123 -- Parameters : required parameters are p_pool_name
2124 -- Version : 1.0
2125 -- History : 08-Mar-2005 ABINDAL created
2126 -- End of comments
2127 -------------------------------------------------------------------------------
2128
2129 PROCEDURE POOL_RECONC_REPORT(x_errbuf OUT NOCOPY VARCHAR2,
2130 x_retcode OUT NOCOPY NUMBER,
2131 p_pool_id IN okl_subsidy_pools_b.id%TYPE,
2132 p_from_date IN VARCHAR2,
2133 p_to_date IN VARCHAR2)
2134 IS
2135
2136 l_pool_rec okl_sub_pool_rec;
2137 l_bdgt_pool_rec okl_sub_pool_rec;
2138 l_from_date DATE;
2139 l_to_date DATE;
2140 l_api_name CONSTANT VARCHAR2(30) := 'POOL_RECONC_REPORT';
2141 l_msg_count NUMBER;
2142 l_msg_data VARCHAR2(2000);
2143 l_return_status VARCHAR2(1);
2144 l_api_version NUMBER;
2145 l_init_msg_list VARCHAR2(1);
2146 l_count NUMBER;
2147 --length
2148 l_total_length CONSTANT NUMBER DEFAULT 152;
2149 l_sysdate DATE;
2150 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.POOL_RECONC_REPORT';
2151 l_debug_enabled VARCHAR2(10);
2152 is_debug_procedure_on BOOLEAN;
2153 is_debug_statement_on BOOLEAN;
2154
2155 BEGIN
2156 l_debug_enabled := okl_debug_pub.check_log_enabled;
2157 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
2158 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
2159 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call pool_reconc_report');
2160 END IF;
2161 -- check for logging on STATEMENT level
2162 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
2163
2164 l_api_version := 1.0;
2165 l_init_msg_list := Okl_Api.g_false;
2166 l_msg_count := 0;
2167 l_sysdate := TRUNC(SYSDATE);
2168
2169 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
2170 G_PKG_NAME,
2171 l_init_msg_list,
2172 l_api_version,
2173 l_api_version,
2174 '_PVT',
2175 l_return_status);
2176 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2177 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2178 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2179 RAISE OKL_API.G_EXCEPTION_ERROR;
2180 END IF;
2181
2182 l_from_date:= FND_DATE.CANONICAL_TO_DATE(p_from_date);
2183 l_to_date:= FND_DATE.CANONICAL_TO_DATE(p_to_date);
2184
2185 -- Printing Subsidy pools report header.
2186 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 52 , ' ' ) || fnd_message.get_string('OKL','OKLHOMENAVTITLE') ||
2187 RPAD(' ', 53 , ' ' ));
2188 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
2189 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
2190 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 52 , ' ' ) || fnd_message.get_string('OKL','OKL_SUBSIDY_POOL_REPORT') ||
2191 RPAD(' ', 53 , ' ' ));
2192 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || '-------------------------------' || RPAD(' ', 51, ' ' ));
2193 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
2194 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
2195 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
2196
2197 -- Get the record for user entered pool name.
2198 l_pool_rec := get_parent_record(p_pool_id);
2199 -- Prints the user entered parameters.
2200 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', 100 , '-' ));
2201 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_SUBSIDY_POOL_NAME') || ' : '
2202 || l_pool_rec.subsidy_pool_name);
2203 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_FROM_DATE') || ' : ' || l_from_date);
2204 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_TO_DATE') || ' : ' || l_to_date);
2205 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', 100 , '-' ));
2206
2207 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
2208 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2209 l_module,
2210 'l_pool_rec.id '||l_pool_rec.id
2211 );
2212 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
2213
2214 l_count := 0;
2215
2216 -- If record is found in the table then print the pool details region
2217 -- and the transaction details region.
2218 IF (l_pool_rec.id is not null) THEN
2219 print_pool_summary(l_pool_rec,
2220 l_from_date,
2221 l_to_date,
2222 l_return_status,
2223 l_msg_count,
2224 l_msg_data
2225 );
2226 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2227 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2228 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2229 RAISE OKL_API.G_EXCEPTION_ERROR;
2230 END IF;
2231 print_transaction_summary (p_pool_id,
2232 l_from_date,
2233 l_to_date,
2234 l_pool_rec.pool_type_code,
2235 l_pool_rec.currency_code,
2236 l_pool_rec.currency_conversion_type,
2237 l_return_status,
2238 l_msg_count,
2239 l_msg_data
2240 );
2241 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2242 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2243 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2244 RAISE OKL_API.G_EXCEPTION_ERROR;
2245 END IF;
2246 END IF;
2247
2248 okl_api.END_ACTIVITY(l_msg_count, l_msg_data);
2249
2250 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
2251 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call pool_reconc_report');
2252 END IF;
2253
2254 EXCEPTION
2255 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2256 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2257 p_api_name => l_api_name,
2258 p_pkg_name => G_PKG_NAME,
2259 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2260 x_msg_count => l_msg_count,
2261 x_msg_data => l_msg_data,
2262 p_api_type => g_api_type);
2263
2264 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2265 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2266 p_api_name => l_api_name,
2267 p_pkg_name => G_PKG_NAME,
2268 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2269 x_msg_count => l_msg_count,
2270 x_msg_data => l_msg_data,
2271 p_api_type => g_api_type);
2272
2273 WHEN OTHERS THEN
2274 x_errbuf := SQLERRM;
2275 x_retcode := 2;
2276
2277 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
2278
2279 IF (SQLCODE <> -20001) THEN
2280 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
2281 --APP_EXCEPTION.RAISE_EXCEPTION;
2282 RAISE;
2283 ELSE
2284 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
2285 --g_error_message := Sqlerrm;
2286 APP_EXCEPTION.RAISE_EXCEPTION;
2287 END IF;
2288
2289 END pool_reconc_report;
2290
2291 ---------------------------------------------------------------------------
2292 -- PROCEDURE PRINT_ATLIMIT_DETAIL
2293 ---------------------------------------------------------------------------
2294 ---------------------------------------------------------------------------
2295 -- Start of comments
2296 --
2297 -- Procedure Name : PRINT_ATLIMIT_DETAIL
2298 -- Description : To print the At-Limit subsidy pool detail.
2299 -- Business Rules :
2300 -- Parameters :
2301 -- Version : 1.0
2302 -- History : 08-Mar-2005 ABINDAL created.
2303 -- End of comments
2304 ---------------------------------------------------------------------------
2305 PROCEDURE print_atlimit_detail (p_percent IN NUMBER,
2306 p_remaining IN NUMBER,
2307 p_currency IN okl_subsidy_pools_b.currency_code%TYPE,
2308 p_date IN DATE,
2309 p_days IN NUMBER,
2310 x_return_status OUT NOCOPY VARCHAR2,
2311 x_msg_count OUT NOCOPY NUMBER ,
2312 x_msg_data OUT NOCOPY VARCHAR2)
2313 IS
2314
2315 -- Cursor to fetch all the subsidy pools whose % reamining balance is
2316 -- less than or equal to the specified % balance.
2317 CURSOR c_get_percent(cp_percent NUMBER) IS
2318 SELECT id,
2319 subsidy_pool_name,
2320 currency_code,
2321 total_budgets,
2322 NVL(total_budgets - NVL(total_subsidy_amount,0),0) remaining_balance,
2323 case when NVL(total_budgets,0) = 0 then 0
2324 else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
2325 effective_to_date,
2326 decision_status_code
2327 FROM okl_subsidy_pools_b
2328 WHERE pool_type_code = 'BUDGET'
2329 AND decision_status_code = 'ACTIVE'
2330 AND CASE WHEN NVL(total_budgets,0) = 0 THEN 0
2331 ELSE ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets END <= cp_percent
2332 AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
2333 else
2334 (case when exists (select 'x'
2335 from okl_subsidies_v
2336 where subsidy_pool_id = okl_subsidy_pools_b.id
2337 and org_id <> mo_global.get_current_org_id()) then 0
2338 else 1 end)
2339 end));
2340
2341 -- Cursor to fetch all the subsidy pools whose reamining budget is
2342 -- less than or equal to the specified remaining budget.
2343 CURSOR c_get_budget(cp_remaining NUMBER, cp_currency okl_subsidy_pools_b.currency_code%TYPE) IS
2344 SELECT id,
2345 subsidy_pool_name,
2346 currency_code,
2347 total_budgets,
2348 NVL(total_budgets - NVL(total_subsidy_amount,0),0) remaining_balance,
2349 case when NVL(total_budgets,0) = 0 then 0
2350 else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
2351 effective_to_date,
2352 decision_status_code
2353 FROM okl_subsidy_pools_b
2354 WHERE pool_type_code = 'BUDGET'
2355 AND decision_status_code = 'ACTIVE'
2356 AND NVL(total_budgets - NVL(total_subsidy_amount,0),0) <= cp_remaining
2357 AND currency_code = cp_currency
2358 AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
2359 else
2360 (case when exists (select 'x'
2361 from okl_subsidies_v
2362 where subsidy_pool_id = okl_subsidy_pools_b.id
2363 and org_id <> mo_global.get_current_org_id()) then 0
2364 else 1 end)
2365 end));
2366
2367 -- Cursor to fetch all the subsidy pools whose effective to date lies
2368 -- between sysdate and user entered date.
2369 CURSOR c_get_dates(cp_date DATE) IS
2370 SELECT id,
2371 subsidy_pool_name,
2372 currency_code,
2373 total_budgets,
2374 NVL(total_budgets - NVL(total_subsidy_amount,0),0) remaining_balance,
2375 case when NVL(total_budgets,0) = 0 then 0
2376 else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
2377 effective_to_date
2378 FROM okl_subsidy_pools_b
2379 WHERE pool_type_code = 'BUDGET'
2380 AND decision_status_code = 'ACTIVE'
2381 AND TRUNC(effective_to_date) >= TRUNC(SYSDATE)
2382 AND TRUNC(effective_to_date) <= TRUNC(cp_date)
2383 AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
2384 else
2385 (case when exists (select 'x'
2386 from okl_subsidies_v
2387 where subsidy_pool_id = okl_subsidy_pools_b.id
2388 and org_id <> mo_global.get_current_org_id()) then 0
2389 else 1 end)
2390 end));
2391
2392 -- Cursor to fetch all the subsidy pools whose end of term days, calculated by pool's
2393 -- effective to date - sysdate, is less than or equal to the entered value.
2394 CURSOR c_get_days(cp_days NUMBER) IS
2395 SELECT id,
2396 subsidy_pool_name,
2397 currency_code,
2398 total_budgets,
2399 NVL(total_budgets - NVL(total_subsidy_amount,0),0) remaining_balance,
2400 case when NVL(total_budgets,0) = 0 then 0
2401 else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
2402 effective_to_date,
2403 trunc(effective_to_date) - trunc(sysdate) remaining_days
2404 FROM okl_subsidy_pools_b
2405 WHERE pool_type_code = 'BUDGET'
2406 AND decision_status_code = 'ACTIVE'
2407 AND trunc(effective_to_date) - trunc(sysdate) between 0 and cp_days
2408 AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
2409 else
2410 (case when exists (select 'x'
2411 from okl_subsidies_v
2412 where subsidy_pool_id = okl_subsidy_pools_b.id
2413 and org_id <> mo_global.get_current_org_id()) then 0
2414 else 1 end)
2415 end));
2416
2417 l_Pool_Name_len CONSTANT NUMBER DEFAULT 30;
2418 l_Currency_Code_len CONSTANT NUMBER DEFAULT 15;
2419 l_Budget_len CONSTANT NUMBER DEFAULT 20;
2420 l_Remaining_len CONSTANT NUMBER DEFAULT 20;
2421 l_percent_len CONSTANT NUMBER DEFAULT 30;
2422 l_effective_to_len CONSTANT NUMBER DEFAULT 20;
2423 l_remaining_days_len CONSTANT NUMBER DEFAULT 20;
2424 l_total_length CONSTANT NUMBER DEFAULT 152;
2425 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.PRINT_ATLIMIT_DETAIL';
2426 l_debug_enabled VARCHAR2(10);
2427 is_debug_procedure_on BOOLEAN;
2428 is_debug_statement_on BOOLEAN;
2429 l_parameter_list WF_PARAMETER_LIST_T;
2430 l_event_name wf_events.name%TYPE;
2431 l_api_version NUMBER;
2432 l_init_msg_list VARCHAR2(1);
2433 l_return_status VARCHAR2(1);
2434 l_msg_count NUMBER;
2435 l_msg_data VARCHAR2(2000);
2436 l_api_name CONSTANT VARCHAR2(30) := 'print_atlimit_detail';
2437 l_decision_status_code okl_subsidy_pools_b.decision_status_code%TYPE;
2438
2439 BEGIN
2440
2441 l_debug_enabled := okl_debug_pub.check_log_enabled;
2442 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
2443 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
2444 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call print_atlimit_detail');
2445 END IF;
2446 -- check for logging on STATEMENT level
2447 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
2448
2449 l_api_version := 1.0;
2450 l_init_msg_list := Okl_Api.g_false;
2451 l_msg_count := 0;
2452
2453 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
2454 G_PKG_NAME,
2455 l_init_msg_list,
2456 l_api_version,
2457 l_api_version,
2458 '_PVT',
2459 l_return_status);
2460 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2461 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2462 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2463 RAISE OKL_API.G_EXCEPTION_ERROR;
2464 END IF;
2465 -- Print the subsidy pool summary header.
2466 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
2467 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
2468 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
2469 FND_FILE.PUT(FND_FILE.OUTPUT,
2470 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_SUBSIDY_POOL_NAME'),l_Pool_Name_len,'TITLE')||' '||
2471 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_AGN_RPT_CURRENCY'),l_Currency_Code_len,'TITLE')||' '||
2472 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_POOL_BUDGET'),l_Budget_len,'TITLE')||' '||
2473 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_POOL_BALANCE'),l_Remaining_len,'TITLE')||' '||
2474 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_PERCENT_BUDGET'),l_percent_len,'TITLE')||' '||
2475 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_END_TERM'),l_effective_to_len,'TITLE'));
2476 IF(p_days is not null) THEN
2477 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
2478 GET_PROPER_LENGTH(fnd_message.get_string('OKL','OKL_REMAINING_DAYS'),l_remaining_days_len,'TITLE'));
2479 END IF;
2480 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=', l_total_length+8 , '=' ));
2481 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2482
2483 -- If p_percent parameter value is entered by user and other parameters value is nul.
2484 IF(p_percent is not null) THEN
2485 FOR each_row IN c_get_percent(p_percent) LOOP
2486 wf_event.AddParameterToList(G_WF_ITM_SUB_POOL_ID, each_row.id, l_parameter_list);
2487 l_event_name := G_WF_EVT_POOL_NEAR_BUDGLMT;
2488 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
2489 GET_PROPER_LENGTH(each_row.subsidy_pool_name,l_Pool_Name_len,'DATA')||' '||
2490 GET_PROPER_LENGTH(each_row.currency_code,l_Currency_Code_len,'DATA')||' '||
2491 GET_PROPER_LENGTH(okl_accounting_util.format_amount(each_row.total_budgets,each_row.currency_code),l_Budget_len,'DATA')||' '||
2492 GET_PROPER_LENGTH(okl_accounting_util.format_amount(each_row.remaining_balance,each_row.currency_code),l_Remaining_len,'DATA')||' '||
2493 GET_PROPER_LENGTH(okl_accounting_util.format_amount(each_row.percent_remaining,each_row.currency_code),l_percent_len,'DATA')||' '||
2494 GET_PROPER_LENGTH(each_row.effective_to_date,l_effective_to_len,'DATA'));
2495 -- Raise the business event "Pool nearing budget limit" for all the subsidy pool records found.
2496 raise_business_event(p_api_version => l_api_version,
2497 p_init_msg_list => l_init_msg_list,
2498 x_return_status => l_return_status,
2499 x_msg_count => l_msg_count,
2500 x_msg_data => l_msg_data,
2501 p_event_name => l_event_name,
2502 p_event_param_list => l_parameter_list
2503 );
2504 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2505 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2506 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2507 RAISE OKL_API.G_EXCEPTION_ERROR;
2508 END IF;
2509 -- If subsidy pool effective to date is less than the current sysdate and the subsidy pool
2510 -- status is other than "EXPIRED' than set the stauts to "EXPIRED'.
2511 IF(each_row.effective_to_date < TRUNC(SYSDATE) AND each_row.decision_status_code <> 'EXPIRED')THEN
2512 l_decision_status_code := 'EXPIRED';
2513 okl_subsidy_pool_pvt.set_decision_status_code ( l_api_version,
2514 l_init_msg_list,
2515 l_return_status,
2516 l_msg_count,
2517 l_msg_data,
2518 each_row.id,
2519 l_decision_status_code);
2520 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2521 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2522 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2523 RAISE OKL_API.G_EXCEPTION_ERROR;
2524 END IF;
2525 END IF;
2526 END LOOP;
2527 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
2528 ELSIF(p_remaining is not null) THEN
2529 FOR each_row IN c_get_budget(p_remaining,p_currency) LOOP
2530 wf_event.AddParameterToList(G_WF_ITM_SUB_POOL_ID, each_row.id, l_parameter_list);
2531 l_event_name := G_WF_EVT_POOL_NEAR_BUDGLMT;
2532 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
2533 GET_PROPER_LENGTH(each_row.subsidy_pool_name,l_Pool_Name_len,'DATA')||' '||
2534 GET_PROPER_LENGTH(each_row.currency_code,l_Currency_Code_len,'DATA')||' '||
2535 GET_PROPER_LENGTH(okl_accounting_util.format_amount(each_row.total_budgets,each_row.currency_code),l_Budget_len,'DATA')||' '||
2536 GET_PROPER_LENGTH(okl_accounting_util.format_amount(each_row.remaining_balance,each_row.currency_code),l_Remaining_len,'DATA')||' '||
2537 GET_PROPER_LENGTH(okl_accounting_util.format_amount(each_row.percent_remaining,each_row.currency_code),l_percent_len,'DATA')||' '||
2538 GET_PROPER_LENGTH(each_row.effective_to_date,l_effective_to_len,'DATA'));
2539 -- Raise the business event "Pool nearing budget limit" for all the subsidy pool records found.
2540 raise_business_event(p_api_version => l_api_version,
2541 p_init_msg_list => l_init_msg_list,
2542 x_return_status => l_return_status,
2543 x_msg_count => l_msg_count,
2544 x_msg_data => l_msg_data,
2545 p_event_name => l_event_name,
2546 p_event_param_list => l_parameter_list
2547 );
2548 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2549 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2550 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2551 RAISE OKL_API.G_EXCEPTION_ERROR;
2552 END IF;
2553 -- If subsidy pool effective to date is less than the current sysdate and the subsidy pool
2554 -- status is other than "EXPIRED' than set the stauts to "EXPIRED'.
2555 IF(each_row.effective_to_date < TRUNC(SYSDATE) AND each_row.decision_status_code <> 'EXPIRED')THEN
2556 l_decision_status_code := 'EXPIRED';
2557 okl_subsidy_pool_pvt.set_decision_status_code ( l_api_version,
2558 l_init_msg_list,
2559 l_return_status,
2560 l_msg_count,
2561 l_msg_data,
2562 each_row.id,
2563 l_decision_status_code);
2564 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2565 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2566 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2567 RAISE OKL_API.G_EXCEPTION_ERROR;
2568 END IF;
2569 END IF;
2570 END LOOP;
2571 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
2572 ELSIF(p_date is not null) THEN
2573 FOR each_row IN c_get_dates(p_date) LOOP
2574 wf_event.AddParameterToList(G_WF_ITM_SUB_POOL_ID, each_row.id, l_parameter_list);
2575 l_event_name := G_WF_EVT_POOL_NEAR_EXPIR;
2576 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
2577 GET_PROPER_LENGTH(each_row.subsidy_pool_name,l_Pool_Name_len,'DATA')||' '||
2578 GET_PROPER_LENGTH(each_row.currency_code,l_Currency_Code_len,'DATA')||' '||
2579 GET_PROPER_LENGTH(okl_accounting_util.format_amount(each_row.total_budgets,each_row.currency_code),l_Budget_len,'DATA')||' '||
2580 GET_PROPER_LENGTH(okl_accounting_util.format_amount(each_row.remaining_balance,each_row.currency_code),l_Remaining_len,'DATA')||' '||
2581 GET_PROPER_LENGTH(okl_accounting_util.format_amount(each_row.percent_remaining,each_row.currency_code),l_percent_len,'DATA')||' '||
2582 GET_PROPER_LENGTH(each_row.effective_to_date,l_effective_to_len,'DATA'));
2583 -- Raise the business event "Pool nearing expiration" for all the subsidy pool records found.
2584 raise_business_event(p_api_version => l_api_version,
2585 p_init_msg_list => l_init_msg_list,
2586 x_return_status => l_return_status,
2587 x_msg_count => l_msg_count,
2588 x_msg_data => l_msg_data,
2589 p_event_name => l_event_name,
2590 p_event_param_list => l_parameter_list
2591 );
2592 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2593 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2594 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2595 RAISE OKL_API.G_EXCEPTION_ERROR;
2596 END IF;
2597 END LOOP;
2598 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
2599 ELSIF(p_days is not null) THEN
2600 FOR each_row IN c_get_days(p_days) LOOP
2601 wf_event.AddParameterToList(G_WF_ITM_SUB_POOL_ID, each_row.id, l_parameter_list);
2602 l_event_name := G_WF_EVT_POOL_NEAR_EXPIR;
2603 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
2604 GET_PROPER_LENGTH(each_row.subsidy_pool_name,l_Pool_Name_len,'DATA')||' '||
2605 GET_PROPER_LENGTH(each_row.currency_code,l_Currency_Code_len,'DATA')||' '||
2606 GET_PROPER_LENGTH(okl_accounting_util.format_amount(each_row.total_budgets,each_row.currency_code),l_Budget_len,'DATA')||' '||
2607 GET_PROPER_LENGTH(okl_accounting_util.format_amount(each_row.remaining_balance,each_row.currency_code),l_Remaining_len,'DATA')||' '||
2608 GET_PROPER_LENGTH(okl_accounting_util.format_amount(each_row.percent_remaining,each_row.currency_code),l_percent_len,'DATA')||' '||
2609 GET_PROPER_LENGTH(each_row.effective_to_date,l_effective_to_len,'DATA')||' '||
2610 GET_PROPER_LENGTH(each_row.remaining_days,l_remaining_days_len,'DATA'));
2611 -- Raise the business event "Pool nearing expiration" for all the subsidy pool records found.
2612 raise_business_event(p_api_version => l_api_version,
2613 p_init_msg_list => l_init_msg_list,
2614 x_return_status => l_return_status,
2615 x_msg_count => l_msg_count,
2616 x_msg_data => l_msg_data,
2617 p_event_name => l_event_name,
2618 p_event_param_list => l_parameter_list
2619 );
2620 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2621 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2622 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2623 RAISE OKL_API.G_EXCEPTION_ERROR;
2624 END IF;
2625 END LOOP;
2626 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_total_length+8 , '-' ));
2627 END IF;
2628 COMMIT;
2629
2630 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2631 x_return_status := l_return_status;
2632 x_msg_data := l_msg_data;
2633 x_msg_count := l_msg_count;
2634
2635 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
2636 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call print_atlimit_detail');
2637 END IF;
2638
2639 EXCEPTION
2640 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2641 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2642 p_api_name => l_api_name,
2643 p_pkg_name => G_PKG_NAME,
2644 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2645 x_msg_count => x_msg_count,
2646 x_msg_data => x_msg_data,
2647 p_api_type => g_api_type);
2648
2649 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2650 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2651 p_api_name => l_api_name,
2652 p_pkg_name => G_PKG_NAME,
2653 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2654 x_msg_count => x_msg_count,
2655 x_msg_data => x_msg_data,
2656 p_api_type => g_api_type);
2657
2658 WHEN OTHERS THEN
2659 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2660 p_api_name => l_api_name,
2661 p_pkg_name => G_PKG_NAME,
2662 p_exc_name => 'OTHERS',
2663 x_msg_count => x_msg_count,
2664 x_msg_data => x_msg_data,
2665 p_api_type => g_api_type);
2666
2667 END print_atlimit_detail;
2668
2669 -------------------------------------------------------------------------------
2670 -- PROCEDURE POOL_ATLIMIT_REPORT
2671 -------------------------------------------------------------------------------
2672 -- Start of comments
2673 --
2674 -- Procedure Name : POOL_ATLIMIT_REPORT
2675 -- Description : Procedure for Subsidy pool association Report Generation
2676 -- Business Rules :
2677 -- Parameters : parameter p_currency is required if p_remaining is entered.
2678 -- Version : 1.0
2679 -- History : 08-Mar-2005 ABINDAL created
2680 -- End of comments
2681 -------------------------------------------------------------------------------
2682
2683 PROCEDURE POOL_ATLIMIT_REPORT(x_errbuf OUT NOCOPY VARCHAR2,
2684 x_retcode OUT NOCOPY NUMBER,
2685 p_percent IN NUMBER,
2686 p_remaining IN NUMBER,
2687 p_currency IN okl_subsidy_pools_b.currency_code%TYPE,
2688 p_end_date IN VARCHAR2,
2689 p_days IN NUMBER )
2690 IS
2691
2692 l_date DATE;
2693 l_api_name CONSTANT VARCHAR2(30) := 'POOL_ATLIMIT_REPORT';
2694 l_msg_count NUMBER;
2695 l_msg_data VARCHAR2(2000);
2696 l_return_status VARCHAR2(1);
2697 l_api_version NUMBER;
2698 l_init_msg_list VARCHAR2(1);
2699 l_total_length CONSTANT NUMBER DEFAULT 152;
2700 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.POOL_ATLIMIT_REPORT';
2701 l_debug_enabled VARCHAR2(10);
2702 is_debug_procedure_on BOOLEAN;
2703 is_debug_statement_on BOOLEAN;
2704
2705 BEGIN
2706 l_debug_enabled := okl_debug_pub.check_log_enabled;
2707 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
2708 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
2709 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call pool_atlimit_report');
2710 END IF;
2711 -- check for logging on STATEMENT level
2712 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
2713
2714 l_api_version := 1.0;
2715 l_init_msg_list := Okl_Api.g_false;
2716 l_msg_count := 0;
2717
2718 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
2719 G_PKG_NAME,
2720 l_init_msg_list,
2721 l_api_version,
2722 l_api_version,
2723 '_PVT',
2724 l_return_status);
2725 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2726 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2727 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2728 RAISE OKL_API.G_EXCEPTION_ERROR;
2729 END IF;
2730
2731 l_date:= FND_DATE.CANONICAL_TO_DATE(p_end_date);
2732
2733 -- Printing Subsidy pools report header.
2734 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 52 , ' ' ) || fnd_message.get_string('OKL','OKLHOMENAVTITLE') ||
2735 RPAD(' ', 53 , ' ' ));
2736 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
2737 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
2738 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 52 , ' ' ) || fnd_message.get_string('OKL','OKL_SUBSIDY_POOL_REPORT') ||
2739 RPAD(' ', 53 , ' ' ));
2740 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',50, ' ' ) || '-------------------------------' || RPAD(' ', 51, ' ' ));
2741 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
2742 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
2743 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));
2744
2745 -- If p_remaining is entered and p_currency is not entered or vice versa
2746 -- then throw an error, if any one of these parameter is entered then other
2747 -- one is mandatory parameter.
2748 IF ( p_remaining is not null AND p_currency is null) THEN
2749 fnd_message.set_name( G_APP_NAME,
2750 'OKL_SUBPOOL_CURR_REQ');
2751 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
2752 ELSIF ( p_remaining is null AND p_currency is not null) THEN
2753 fnd_message.set_name( G_APP_NAME,
2754 'OKL_SUBPOOL_REM_BDGT');
2755 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
2756 ELSIF (p_percent is not null AND p_percent > 100)THEN
2757 fnd_message.set_name( G_APP_NAME,
2758 'OKL_LLA_PERCENT');
2759 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
2760 -- if more than one parameters is entered, considering p_remaining and p_currency
2761 -- as a single parameter, or no parameter is entered then throw an error.
2762 -- At least one of the parameter is mandatory.
2763 ELSIF( (p_percent is not null AND p_remaining is not null) OR
2764 (p_percent is not null AND l_date is not null) OR
2765 (l_date is not null AND p_remaining is not null) OR
2766 ( p_percent is not null AND p_days is not null) OR
2767 ( p_remaining is not null AND p_days is not null) OR
2768 ( l_date is not null AND p_days is not null) OR
2769 (p_percent is null AND p_remaining is null AND l_date is null AND p_days is null AND p_currency is null)) THEN
2770 fnd_message.set_name( G_APP_NAME,
2771 'OKL_SUBPOOL_ATLIMIT_PARAMS');
2772 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
2773 -- if user has entered one of the parameters then print the report.
2774 ELSE
2775 print_atlimit_detail (p_percent,
2776 p_remaining,
2777 p_currency,
2778 l_date,
2779 p_days,
2780 l_return_status,
2781 l_msg_count,
2782 l_msg_data);
2783 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2784 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2785 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2786 RAISE OKL_API.G_EXCEPTION_ERROR;
2787 END IF;
2788 END IF;
2789
2790 okl_api.END_ACTIVITY(l_msg_count, l_msg_data);
2791
2792 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
2793 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call pool_atlimit_report');
2794 END IF;
2795
2796 EXCEPTION
2797 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2798 l_return_status := OKL_API.HANDLE_EXCEPTIONS
2799 (
2800 l_api_name,
2801 G_PKG_NAME,
2802 'OKL_API.G_RET_STS_ERROR',
2803 l_msg_count,
2804 l_msg_data,
2805 '_PVT'
2806 );
2807
2808 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2809 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2810 p_api_name => l_api_name,
2811 p_pkg_name => G_PKG_NAME,
2812 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2813 x_msg_count => l_msg_count,
2814 x_msg_data => l_msg_data,
2815 p_api_type => g_api_type);
2816
2817 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2818 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2819 p_api_name => l_api_name,
2820 p_pkg_name => G_PKG_NAME,
2821 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2822 x_msg_count => l_msg_count,
2823 x_msg_data => l_msg_data,
2824 p_api_type => g_api_type);
2825
2826 WHEN OTHERS THEN
2827 x_errbuf := SQLERRM;
2828 x_retcode := 2;
2829
2830 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
2831
2832 IF (SQLCODE <> -20001) THEN
2833 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
2834 --APP_EXCEPTION.RAISE_EXCEPTION;
2835 RAISE;
2836 ELSE
2837 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
2838 --g_error_message := Sqlerrm;
2839 APP_EXCEPTION.RAISE_EXCEPTION;
2840 END IF;
2841
2842 END pool_atlimit_report;
2843
2844 ---------------------------------------------------------------------------
2845 -- FUNCTION GET_POOL_AMOUNTS
2846 ---------------------------------------------------------------------------
2847 ---------------------------------------------------------------------------
2848 -- Start of comments
2849 --
2850 -- Procedure Name : GET_POOL_AMOUNTS
2851 -- Description : To determine the amounts for subsidy pool record.
2852 -- Business Rules :
2853 -- Parameters : p_pool_rec, p_input_date, p_to_currency_code, p_conv_type
2854 -- Version : 1.0
2855 -- History : 03-Jan-2007 UDHENUKO created.
2856 -- End of comments
2857 ---------------------------------------------------------------------------
2858 FUNCTION GET_POOL_AMOUNTS (p_pool_rec IN okl_sub_pool_rec,
2859 p_input_date IN DATE,
2860 p_to_currency_code IN VARCHAR2,
2861 p_conv_type IN VARCHAR2,
2862 x_return_status OUT NOCOPY VARCHAR2,
2863 x_msg_count OUT NOCOPY NUMBER,
2864 x_msg_data OUT NOCOPY VARCHAR2)
2865 RETURN pool_dtl_rec_type
2866 IS
2867
2868 -- Cursor fetches all the records, which are children of a given pool till the pool,
2869 -- does not have any more children.
2870 CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
2871 SELECT id ,
2872 currency_code,
2873 currency_conversion_type
2874 FROM okl_subsidy_pools_b pool
2875 WHERE pool_type_code = 'BUDGET'
2876 CONNECT BY PRIOR id = subsidy_pool_id
2877 START WITH id = cp_pool_id;
2878
2879 x_errbuf VARCHAR2(2000);
2880 x_retcode NUMBER;
2881 x_pool_dtl_rec pool_dtl_rec_type;
2882 l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
2883 l_budget okl_subsidy_pools_b.total_budgets%TYPE;
2884 l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
2885 l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
2886 l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
2887 l_conv_rate NUMBER;
2888 l_reporting_limit okl_subsidy_pools_b.reporting_pool_limit%TYPE ;
2889 l_api_name CONSTANT VARCHAR2(30) := 'GET_POOL_AMOUNTS';
2890 l_msg_count NUMBER ;
2891 l_msg_data VARCHAR2(2000);
2892 l_return_status VARCHAR2(1);
2893 l_api_version NUMBER ;
2894 l_init_msg_list VARCHAR2(1);
2895 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.GET_POOL_AMOUNTS';
2896 l_debug_enabled VARCHAR2(10);
2897 is_debug_procedure_on BOOLEAN;
2898 is_debug_statement_on BOOLEAN;
2899
2900 BEGIN
2901 l_debug_enabled := okl_debug_pub.check_log_enabled;
2902 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
2903 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
2904 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call GET_POOL_AMOUNTS');
2905 END IF;
2906 -- check for logging on STATEMENT level
2907 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
2908
2909 l_api_version := 1.0;
2910 l_init_msg_list := Okl_Api.g_false;
2911 l_msg_count := 0;
2912 l_total_budget := 0;
2913 l_budget := 0;
2914 l_trx_amount := 0;
2915 l_trx_amt := 0;
2916 l_remaining_balance := 0;
2917 l_conv_rate := 0;
2918 l_reporting_limit := p_pool_rec.reporting_pool_limit;
2919
2920 x_pool_dtl_rec.reporting_limit := okl_accounting_util.format_amount(0,p_to_currency_code);
2921 x_pool_dtl_rec.total_budget := okl_accounting_util.format_amount(0,p_to_currency_code);
2922 x_pool_dtl_rec.remaining_balance := okl_accounting_util.format_amount(0,p_to_currency_code);
2923
2924 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
2925 G_PKG_NAME,
2926 l_init_msg_list,
2927 l_api_version,
2928 l_api_version,
2929 '_PVT',
2930 l_return_status);
2931
2932 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2933 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2934 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2935 RAISE OKL_API.G_EXCEPTION_ERROR;
2936 END IF;
2937
2938 -- Convert the reporting pool limit amount from pool currency to the pool currency.
2939 l_reporting_limit := currency_conversion(p_pool_rec.reporting_pool_limit,
2940 p_pool_rec.currency_code,
2941 p_to_currency_code,
2942 p_conv_type,
2943 p_pool_rec.effective_from_date,
2944 l_conv_rate
2945 );
2946 IF (l_reporting_limit < 0) THEN
2947 fnd_message.set_name( G_APP_NAME,
2948 'OKL_POOL_CURR_CONV');
2949 fnd_message.set_token('FROM_CURR',
2950 p_pool_rec.currency_code);
2951 fnd_message.set_token('TO_CURR',
2952 p_to_currency_code);
2953 x_pool_dtl_rec.error_message := fnd_message.get;
2954 END IF;
2955 -- If subsidy pool is of type reporting then, calculate the total budgets and remaining balance for that pool,
2956 -- from all its children which are of type budget.
2957 IF(p_pool_rec.pool_type_code = 'REPORTING') THEN
2958 FOR each_row IN get_amounts(p_pool_rec.id) LOOP
2959 l_budget := total_budgets( each_row.id,
2960 p_input_date,
2961 each_row.currency_code,
2962 p_to_currency_code,
2963 p_conv_type,
2964 l_return_status,
2965 l_msg_count,
2966 l_msg_data
2967 );
2968 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2969 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2970 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2971 RAISE OKL_API.G_EXCEPTION_ERROR;
2972 END IF;
2973 l_total_budget := l_total_budget + l_budget;
2974 l_trx_amt := transaction_amount( each_row.id,
2975 p_input_date,
2976 each_row.currency_code,
2977 p_to_currency_code,
2978 p_conv_type,
2979 l_return_status,
2980 l_msg_count,
2981 l_msg_data
2982 );
2983 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2984 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2985 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2986 RAISE OKL_API.G_EXCEPTION_ERROR;
2987 END IF;
2988 l_trx_amount := l_trx_amount + l_trx_amt;
2989 END LOOP;
2990 -- if subsidy pool type is budget, simply calculate the total budgets and remaining balance.
2991 ELSE
2992 l_budget := total_budgets( p_pool_rec.id,
2993 p_input_date,
2994 p_pool_rec.currency_code,
2995 p_to_currency_code,
2996 p_conv_type,
2997 l_return_status,
2998 l_msg_count,
2999 l_msg_data
3000 );
3001 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3002 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3003 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3004 RAISE OKL_API.G_EXCEPTION_ERROR;
3005 END IF;
3006 l_total_budget := l_budget;
3007 l_trx_amt := transaction_amount( p_pool_rec.id,
3008 p_input_date,
3009 p_pool_rec.currency_code,
3010 p_to_currency_code,
3011 p_conv_type,
3012 l_return_status,
3013 l_msg_count,
3014 l_msg_data
3015 );
3016 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3017 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3018 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3019 RAISE OKL_API.G_EXCEPTION_ERROR;
3020 END IF;
3021 l_trx_amount := l_trx_amt;
3022 END IF;
3023 l_remaining_balance := l_total_budget - l_trx_amount;
3024
3025 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
3026 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
3027 l_module,
3028 'l_reporting_limit '||l_reporting_limit||' l_total_budget '
3029 ||l_total_budget||' l_trx_amount '||l_trx_amount
3030 ||'l_remaining_balance'||l_remaining_balance
3031 );
3032 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
3033
3034 x_pool_dtl_rec.reporting_limit := okl_accounting_util.format_amount(l_reporting_limit,p_to_currency_code);
3035 x_pool_dtl_rec.total_budget := okl_accounting_util.format_amount(l_total_budget,p_to_currency_code);
3036 x_pool_dtl_rec.remaining_balance := okl_accounting_util.format_amount(l_remaining_balance,p_to_currency_code);
3037
3038 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
3039 x_return_status := l_return_status;
3040 x_msg_data := l_msg_data;
3041 x_msg_count := l_msg_count;
3042
3043 RETURN x_pool_dtl_rec;
3044
3045 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
3046 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call GET_POOL_AMOUNTS');
3047 END IF;
3048
3049 EXCEPTION
3050 WHEN OTHERS THEN
3051 x_errbuf := SQLERRM;
3052 x_retcode := 2;
3053
3054 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
3055
3056 IF (SQLCODE <> -20001) THEN
3057 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
3058 ELSE
3059 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
3060 END IF;
3061
3062 END GET_POOL_AMOUNTS;
3063 -------------------------------------------------------------------------------
3064 -- FUNCTION XML_POOL_ASSOC_REPORT
3065 -------------------------------------------------------------------------------
3066 -- Start of comments
3067 --
3068 -- Procedure Name : XML_POOL_ASSOC_REPORT
3069 -- Description : Function for Subsidy pool association Report Generation for
3070 -- XML Publisher
3071 -- Business Rules :
3072 -- Parameters :
3073 -- Version : 1.0
3074 -- History : 03-Jan-2007 UDHENUKO created.
3075 -- End of comments
3076 -------------------------------------------------------------------------------
3077 FUNCTION XML_POOL_ASSOC_REPORT RETURN BOOLEAN
3078 IS
3079
3080 CURSOR get_subsidy_pools(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
3081 SELECT nvl(parent.id,chld.id) parent_id,
3082 nvl(parent.subsidy_pool_name,chld.subsidy_pool_name) parent_subsidy_pool_name,
3083 LEVEL,
3084 decode(parent.id,NULL,parent.id,chld.id) chld_id,
3085 decode(parent.subsidy_pool_name,NULL,parent.subsidy_pool_name,chld.subsidy_pool_name) chld_subsidy_pool_name
3086 FROM okl_subsidy_pools_b parent,
3087 okl_subsidy_pools_b chld
3088 WHERE chld.pool_type_code IN('BUDGET', 'REPORTING')
3089 AND parent.id(+) = chld.subsidy_pool_id
3090 CONNECT BY PRIOR chld.id = chld.subsidy_pool_id START WITH chld.id = cp_pool_id
3091 AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
3092 else
3093 (case when exists (select 'x'
3094 from okl_subsidies_v
3095 where subsidy_pool_id = chld.id
3096 and org_id <> mo_global.get_current_org_id()) then 0
3097 else 1 end)
3098 end))
3099 ORDER BY LEVEL,
3100 parent.subsidy_pool_name;
3101
3102 CURSOR get_pool_dtls(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
3103 SELECT id,
3104 subsidy_pool_name,
3105 pool_type_code,
3106 currency_code,
3107 currency_conversion_type,
3108 reporting_pool_limit,
3109 effective_from_date
3110 FROM okl_subsidy_pools_b
3111 WHERE id = cp_pool_id;
3112
3113 x_errbuf VARCHAR2(1000);
3114 x_retcode NUMBER;
3115 x_pool_dtl_rec pool_dtl_rec_type;
3116 x_chld_pool_dtl_rec pool_dtl_rec_type;
3117 l_subsidy_pool_tbl subsidy_pool_tbl_type;
3118 l_from_date DATE;
3119 l_pool_rec okl_sub_pool_rec;
3120 l_chld_pool_rec okl_sub_pool_rec;
3121 l_input_pool_rec okl_sub_pool_rec;
3122 l_total_budget okl_subsidy_pools_b.total_budgets%TYPE ;
3123 l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
3124 l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
3125 l_api_name CONSTANT VARCHAR2(30) := 'XML_POOL_ASSOC_REPORT';
3126 l_msg_count NUMBER;
3127 l_msg_data VARCHAR2(2000);
3128 l_return_status VARCHAR2(1);
3129 l_api_version NUMBER;
3130 l_init_msg_list VARCHAR2(1);
3131 l_sysdate DATE ;
3132 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.XML_POOL_ASSOC_REPORT';
3133 l_debug_enabled VARCHAR2(10);
3134 is_debug_procedure_on BOOLEAN;
3135 is_debug_statement_on BOOLEAN;
3136
3137 BEGIN
3138 l_debug_enabled := okl_debug_pub.check_log_enabled;
3139 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
3140 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
3141 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call XML_POOL_ASSOC_REPORT');
3142 END IF;
3143 -- check for logging on STATEMENT level
3144 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
3145
3146 l_api_version := 1.0;
3147 l_init_msg_list := Okl_Api.g_false;
3148 l_msg_count := 0;
3149 l_total_budget := 0;
3150 l_trx_amount := 0;
3151 l_remaining_balance := 0;
3152 l_sysdate := TRUNC(SYSDATE);
3153
3154 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
3155 G_PKG_NAME,
3156 l_init_msg_list,
3157 l_api_version,
3158 l_api_version,
3159 '_PVT',
3160 l_return_status);
3161 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3162 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3163 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3164 RAISE OKL_API.G_EXCEPTION_ERROR;
3165 END IF;
3166
3167 l_from_date:= FND_DATE.CANONICAL_TO_DATE(P_DATE);
3168
3169 -- Use this record to retain the currency of the input pool(user entered)
3170 OPEN get_pool_dtls(P_POOL_ID);
3171 FETCH get_pool_dtls INTO l_input_pool_rec;
3172 CLOSE get_pool_dtls;
3173
3174 FOR pool_row IN get_subsidy_pools(P_POOL_ID) LOOP
3175
3176 -- Get all the details of this subsidy pool.
3177 OPEN get_pool_dtls(pool_row.parent_id);
3178 FETCH get_pool_dtls INTO l_pool_rec;
3179 CLOSE get_pool_dtls;
3180
3181
3182 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
3183 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
3184 l_module,
3185 'l_pool_rec.id '||l_pool_rec.id
3186 );
3187 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
3188 -- if that record is found then determine the pool amounts.
3189 IF (l_pool_rec.id is not null) THEN
3190 x_pool_dtl_rec := GET_POOL_AMOUNTS(l_pool_rec,
3191 l_from_date,
3192 l_input_pool_rec.currency_code,
3193 l_input_pool_rec.currency_conversion_type,
3194 l_return_status,
3195 l_msg_count,
3196 l_msg_data);
3197 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3198 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3199 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3200 RAISE OKL_API.G_EXCEPTION_ERROR;
3201 END IF;
3202 ELSE
3203 l_pool_rec.subsidy_pool_name := NULL;
3204 l_pool_rec.pool_type_code := NULL;
3205 l_pool_rec.currency_code := NULL;
3206 x_pool_dtl_rec.reporting_limit := '0';
3207 x_pool_dtl_rec.total_budget := '0';
3208 x_pool_dtl_rec.remaining_balance := '0';
3209 END IF;
3210 -- Get all the details of this subsidy pool.
3211 OPEN get_pool_dtls(pool_row.chld_id);
3212 FETCH get_pool_dtls INTO l_chld_pool_rec;
3213 CLOSE get_pool_dtls;
3214
3215 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
3216 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
3217 l_module,
3218 'l_chld_pool_rec.id '||l_chld_pool_rec.id
3219 );
3220 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
3221
3222 -- if that record is found then determine the pool amounts.
3223 IF (l_chld_pool_rec.id is not null) THEN
3224 x_chld_pool_dtl_rec := GET_POOL_AMOUNTS(l_chld_pool_rec,
3225 l_from_date,
3226 l_input_pool_rec.currency_code,
3227 l_input_pool_rec.currency_conversion_type,
3228 l_return_status,
3229 l_msg_count,
3230 l_msg_data);
3231 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3232 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3233 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3234 RAISE OKL_API.G_EXCEPTION_ERROR;
3235 END IF;
3236 ELSE
3237 l_chld_pool_rec.subsidy_pool_name := NULL;
3238 l_chld_pool_rec.pool_type_code := NULL;
3239 l_chld_pool_rec.currency_code := NULL;
3240 x_chld_pool_dtl_rec.reporting_limit := okl_accounting_util.format_amount(0,l_pool_rec.currency_code);
3241 x_chld_pool_dtl_rec.total_budget := okl_accounting_util.format_amount(0,l_pool_rec.currency_code);
3242 x_chld_pool_dtl_rec.remaining_balance := okl_accounting_util.format_amount(0,l_pool_rec.currency_code);
3243 END IF;
3244 INSERT INTO
3245 OKL_G_REPORTS_GT (VALUE1_TEXT,
3246 VALUE2_TEXT,
3247 VALUE3_TEXT,
3248 VALUE4_TEXT,
3249 VALUE5_TEXT,
3250 VALUE6_TEXT,
3251 VALUE7_TEXT,
3252 VALUE8_TEXT,
3253 VALUE9_TEXT,
3254 VALUE10_TEXT,
3255 VALUE11_TEXT,
3256 VALUE12_TEXT,
3257 VALUE13_TEXT,
3258 VALUE14_TEXT)
3259 VALUES
3260 (l_pool_rec.subsidy_pool_name,
3261 l_pool_rec.pool_type_code,
3262 l_pool_rec.currency_code,
3263 x_pool_dtl_rec.reporting_limit,
3264 x_pool_dtl_rec.total_budget,
3265 x_pool_dtl_rec.remaining_balance,
3266 x_pool_dtl_rec.error_message,
3267 l_chld_pool_rec.subsidy_pool_name,
3268 l_chld_pool_rec.pool_type_code,
3269 l_chld_pool_rec.currency_code,
3270 x_chld_pool_dtl_rec.reporting_limit,
3271 x_chld_pool_dtl_rec.total_budget,
3272 x_chld_pool_dtl_rec.remaining_balance,
3273 x_chld_pool_dtl_rec.error_message
3274 );
3275 END LOOP;
3276 okl_api.END_ACTIVITY(l_msg_count, l_msg_data);
3277
3278 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
3279 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call XML_POOL_ASSOC_REPORT');
3280 END IF;
3281 RETURN TRUE;
3282 EXCEPTION
3283 WHEN OTHERS THEN
3284 x_errbuf := SQLERRM;
3285 x_retcode := 2;
3286
3287 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
3288
3289 IF (SQLCODE <> -20001) THEN
3290 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
3291 ELSE
3292 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
3293 END IF;
3294
3295 END XML_POOL_ASSOC_REPORT;
3296
3297 ---------------------------------------------------------------------------
3298 -- FUNCTION xml_print_atlimit_detail
3299 ---------------------------------------------------------------------------
3300 ---------------------------------------------------------------------------
3301 -- Start of comments
3302 --
3303 -- Procedure Name : xml_print_atlimit_detail
3304 -- Description : To insert the At-Limit subsidy pool detail into the
3305 -- Global Temporary Table for XML Publisher.
3306 -- Business Rules :
3307 -- Parameters :
3308 -- Version : 1.0
3309 -- History : 03-Jan-2007 UDHENUKO created.
3310 -- End of comments
3311 ---------------------------------------------------------------------------
3312 FUNCTION xml_print_atlimit_detail
3313 RETURN BOOLEAN
3314 IS
3315
3316
3317 -- Cursor to fetch all the subsidy pools whose % reamining balance is
3318 -- less than or equal to the specified % balance.
3319 CURSOR c_get_percent(cp_percent NUMBER) IS
3320 SELECT id,
3321 subsidy_pool_name,
3322 currency_code,
3323 total_budgets,
3324 NVL(total_budgets - NVL(total_subsidy_amount,0),0) remaining_balance,
3325 case when NVL(total_budgets,0) = 0 then 0
3326 else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
3327 effective_to_date,
3328 decision_status_code
3329 FROM okl_subsidy_pools_b
3330 WHERE pool_type_code = 'BUDGET'
3331 AND decision_status_code = 'ACTIVE'
3332 AND CASE WHEN NVL(total_budgets,0) = 0 THEN 0
3333 ELSE ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets END <= cp_percent
3334 AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
3335 else
3336 (case when exists (select 'x'
3337 from okl_subsidies_v
3338 where subsidy_pool_id = okl_subsidy_pools_b.id
3339 and org_id <> mo_global.get_current_org_id()) then 0
3340 else 1 end)
3341 end));
3342
3343 -- Cursor to fetch all the subsidy pools whose reamining budget is
3344 -- less than or equal to the specified remaining budget.
3345 CURSOR c_get_budget(cp_remaining NUMBER, cp_currency okl_subsidy_pools_b.currency_code%TYPE) IS
3346 SELECT id,
3347 subsidy_pool_name,
3348 currency_code,
3349 total_budgets,
3350 NVL(total_budgets - NVL(total_subsidy_amount,0),0) remaining_balance,
3351 case when NVL(total_budgets,0) = 0 then 0
3352 else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
3353 effective_to_date,
3354 decision_status_code
3355 FROM okl_subsidy_pools_b
3356 WHERE pool_type_code = 'BUDGET'
3357 AND decision_status_code = 'ACTIVE'
3358 AND NVL(total_budgets - NVL(total_subsidy_amount,0),0) <= cp_remaining
3359 AND currency_code = cp_currency
3360 AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
3361 else
3362 (case when exists (select 'x'
3363 from okl_subsidies_v
3364 where subsidy_pool_id = okl_subsidy_pools_b.id
3365 and org_id <> mo_global.get_current_org_id()) then 0
3366 else 1 end)
3367 end));
3368
3369 -- Cursor to fetch all the subsidy pools whose effective to date lies
3370 -- between sysdate and user entered date.
3371 CURSOR c_get_dates(cp_date DATE) IS
3372 SELECT id,
3373 subsidy_pool_name,
3374 currency_code,
3375 total_budgets,
3376 NVL(total_budgets - NVL(total_subsidy_amount,0),0) remaining_balance,
3377 case when NVL(total_budgets,0) = 0 then 0
3378 else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
3379 effective_to_date
3380 FROM okl_subsidy_pools_b
3381 WHERE pool_type_code = 'BUDGET'
3382 AND decision_status_code = 'ACTIVE'
3383 AND TRUNC(effective_to_date) >= TRUNC(SYSDATE)
3384 AND TRUNC(effective_to_date) <= TRUNC(cp_date)
3385 AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
3386 else
3387 (case when exists (select 'x'
3388 from okl_subsidies_v
3389 where subsidy_pool_id = okl_subsidy_pools_b.id
3390 and org_id <> mo_global.get_current_org_id()) then 0
3391 else 1 end)
3392 end));
3393
3394 -- Cursor to fetch all the subsidy pools whose end of term days, calculated by pool's
3395 -- effective to date - sysdate, is less than or equal to the entered value.
3396 CURSOR c_get_days(cp_days NUMBER) IS
3397 SELECT id,
3398 subsidy_pool_name,
3399 currency_code,
3400 total_budgets,
3401 NVL(total_budgets - NVL(total_subsidy_amount,0),0) remaining_balance,
3402 case when NVL(total_budgets,0) = 0 then 0
3403 else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
3404 effective_to_date,
3405 trunc(effective_to_date) - trunc(sysdate) remaining_days
3406 FROM okl_subsidy_pools_b
3407 WHERE pool_type_code = 'BUDGET'
3408 AND decision_status_code = 'ACTIVE'
3409 AND trunc(effective_to_date) - trunc(sysdate) between 0 and cp_days
3410 AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
3411 else
3412 (case when exists (select 'x'
3413 from okl_subsidies_v
3414 where subsidy_pool_id = okl_subsidy_pools_b.id
3415 and org_id <> mo_global.get_current_org_id()) then 0
3416 else 1 end)
3417 end));
3418
3419 x_errbuf VARCHAR2(1000);
3420 x_retcode NUMBER;
3421 x_return_status VARCHAR2(1);
3422 x_msg_count NUMBER;
3423 x_msg_data VARCHAR2(2000);
3424 l_date DATE;
3425 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.XML_PRINT_ATLIMIT_DETAIL';
3426 l_debug_enabled VARCHAR2(10);
3427 is_debug_procedure_on BOOLEAN;
3428 is_debug_statement_on BOOLEAN;
3429 l_parameter_list WF_PARAMETER_LIST_T;
3430 l_event_name wf_events.name%TYPE;
3431 l_api_version NUMBER;
3432 l_init_msg_list VARCHAR2(1);
3433 l_return_status VARCHAR2(1);
3434 l_msg_count NUMBER;
3435 l_msg_data VARCHAR2(2000);
3436 l_api_name CONSTANT VARCHAR2(30) := 'xml_print_atlimit_detail';
3437 l_decision_status_code okl_subsidy_pools_b.decision_status_code%TYPE;
3438
3439 BEGIN
3440
3441 l_debug_enabled := okl_debug_pub.check_log_enabled;
3442 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
3443 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
3444 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call xml_print_atlimit_detail');
3445 END IF;
3446 -- check for logging on STATEMENT level
3447 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
3448
3449 l_api_version := 1.0;
3450 l_init_msg_list := Okl_Api.g_false;
3451 l_msg_count := 0;
3452 l_date:= FND_DATE.CANONICAL_TO_DATE(P_END_DATE);
3453 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
3454 G_PKG_NAME,
3455 l_init_msg_list,
3456 l_api_version,
3457 l_api_version,
3458 '_PVT',
3459 l_return_status);
3460 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3461 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3462 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3463 RAISE OKL_API.G_EXCEPTION_ERROR;
3464 END IF;
3465
3466
3467 -- If p_remaining is entered and p_currency is not entered or vice versa
3468 -- then throw an error, if any one of these parameter is entered then other
3469 -- one is mandatory parameter.
3470 IF ( P_REMAINING is not null AND P_CURRENCY is null) THEN
3471 fnd_message.set_name( G_APP_NAME,
3472 'OKL_SUBPOOL_CURR_REQ');
3473 INSERT INTO
3474 OKL_G_REPORTS_GT(VALUE1_TEXT,
3475 VALUE2_TEXT)
3476 VALUES
3477 ('ERROR',
3478 fnd_message.get
3479 );
3480
3481 ELSIF ( P_REMAINING is null AND P_CURRENCY is not null) THEN
3482 fnd_message.set_name( G_APP_NAME,
3483 'OKL_SUBPOOL_REM_BDGT');
3484 INSERT INTO
3485 OKL_G_REPORTS_GT(VALUE1_TEXT,
3486 VALUE2_TEXT)
3487 VALUES
3488 ('ERROR',
3489 fnd_message.get
3490 );
3491
3492 ELSIF (P_PERCENT is not null AND P_PERCENT > 100)THEN
3493 fnd_message.set_name( G_APP_NAME,
3494 'OKL_LLA_PERCENT');
3495 INSERT INTO
3496 OKL_G_REPORTS_GT(VALUE1_TEXT,
3497 VALUE2_TEXT)
3498 VALUES
3499 ('ERROR',
3500 fnd_message.get
3501 );
3502
3503 -- if more than one parameters is entered, considering p_remaining and p_currency
3504 -- as a single parameter, or no parameter is entered then throw an error.
3505 -- At least one of the parameter is mandatory.
3506 ELSIF( (P_PERCENT is not null AND P_REMAINING is not null) OR
3507 (P_PERCENT is not null AND l_date is not null) OR
3508 (l_date is not null AND P_REMAINING is not null) OR
3509 ( P_PERCENT is not null AND P_DAYS is not null) OR
3510 ( P_REMAINING is not null AND P_DAYS is not null) OR
3511 ( l_date is not null AND P_DAYS is not null) OR
3512 (P_PERCENT is null AND P_REMAINING is null AND l_date is null AND P_DAYS is null AND P_CURRENCY is null)) THEN
3513 fnd_message.set_name( G_APP_NAME,
3514 'OKL_SUBPOOL_ATLIMIT_PARAMS');
3515 INSERT INTO
3516 OKL_G_REPORTS_GT(VALUE1_TEXT,
3517 VALUE2_TEXT)
3518 VALUES
3519 ('ERROR',
3520 fnd_message.get
3521 );
3522 ELSE
3523
3524 -- If p_percent parameter value is entered by user and other parameters value is null.
3525 IF(P_PERCENT is not null) THEN
3526 FOR each_row IN c_get_percent(P_PERCENT) LOOP
3527 wf_event.AddParameterToList(G_WF_ITM_SUB_POOL_ID, each_row.id, l_parameter_list);
3528 l_event_name := G_WF_EVT_POOL_NEAR_BUDGLMT;
3529 INSERT INTO
3530 OKL_G_REPORTS_GT(VALUE1_TEXT,
3531 VALUE2_TEXT,
3532 VALUE3_TEXT,
3533 VALUE4_TEXT,
3534 VALUE5_TEXT,
3535 VALUE6_TEXT,
3536 VALUE1_DATE)
3537 VALUES
3538 ('PERCENT',
3539 each_row.subsidy_pool_name,
3540 each_row.currency_code,
3541 okl_accounting_util.format_amount(each_row.total_budgets,each_row.currency_code),
3542 okl_accounting_util.format_amount(each_row.remaining_balance,each_row.currency_code),
3543 okl_accounting_util.format_amount(each_row.percent_remaining,each_row.currency_code),
3544 each_row.effective_to_date
3545 );
3546
3547 -- Raise the business event "Pool nearing budget limit" for all the subsidy pool records found.
3548 raise_business_event(p_api_version => l_api_version,
3549 p_init_msg_list => l_init_msg_list,
3550 x_return_status => l_return_status,
3551 x_msg_count => l_msg_count,
3552 x_msg_data => l_msg_data,
3553 p_event_name => l_event_name,
3554 p_event_param_list => l_parameter_list
3555 );
3556 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3557 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3558 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3559 RAISE OKL_API.G_EXCEPTION_ERROR;
3560 END IF;
3561 -- If subsidy pool effective to date is less than the current sysdate and the subsidy pool
3562 -- status is other than "EXPIRED' than set the stauts to "EXPIRED'.
3563 IF(each_row.effective_to_date < TRUNC(SYSDATE) AND each_row.decision_status_code <> 'EXPIRED')THEN
3564 l_decision_status_code := 'EXPIRED';
3565 okl_subsidy_pool_pvt.set_decision_status_code ( l_api_version,
3566 l_init_msg_list,
3567 l_return_status,
3568 l_msg_count,
3569 l_msg_data,
3570 each_row.id,
3571 l_decision_status_code);
3572 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3573 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3574 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3575 RAISE OKL_API.G_EXCEPTION_ERROR;
3576 END IF;
3577 END IF;
3578 END LOOP;
3579 ELSIF(P_REMAINING is not null) THEN
3580 FOR each_row IN c_get_budget(P_REMAINING,P_CURRENCY) LOOP
3581 wf_event.AddParameterToList(G_WF_ITM_SUB_POOL_ID, each_row.id, l_parameter_list);
3582 l_event_name := G_WF_EVT_POOL_NEAR_BUDGLMT;
3583 INSERT INTO
3584 OKL_G_REPORTS_GT(VALUE1_TEXT,
3585 VALUE2_TEXT,
3586 VALUE3_TEXT,
3587 VALUE4_TEXT,
3588 VALUE5_TEXT,
3589 VALUE6_TEXT,
3590 VALUE1_DATE)
3591 VALUES
3592 ('REMAINING',
3593 each_row.subsidy_pool_name,
3594 each_row.currency_code,
3595 okl_accounting_util.format_amount(each_row.total_budgets,each_row.currency_code),
3596 okl_accounting_util.format_amount(each_row.remaining_balance,each_row.currency_code),
3597 okl_accounting_util.format_amount(each_row.percent_remaining,each_row.currency_code),
3598 each_row.effective_to_date
3599 );
3600
3601 -- Raise the business event "Pool nearing budget limit" for all the subsidy pool records found.
3602 raise_business_event(p_api_version => l_api_version,
3603 p_init_msg_list => l_init_msg_list,
3604 x_return_status => l_return_status,
3605 x_msg_count => l_msg_count,
3606 x_msg_data => l_msg_data,
3607 p_event_name => l_event_name,
3608 p_event_param_list => l_parameter_list
3609 );
3610 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3611 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3612 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3613 RAISE OKL_API.G_EXCEPTION_ERROR;
3614 END IF;
3615 -- If subsidy pool effective to date is less than the current sysdate and the subsidy pool
3616 -- status is other than "EXPIRED' than set the stauts to "EXPIRED'.
3617 IF(each_row.effective_to_date < TRUNC(SYSDATE) AND each_row.decision_status_code <> 'EXPIRED')THEN
3618 l_decision_status_code := 'EXPIRED';
3619 okl_subsidy_pool_pvt.set_decision_status_code ( l_api_version,
3620 l_init_msg_list,
3621 l_return_status,
3622 l_msg_count,
3623 l_msg_data,
3624 each_row.id,
3625 l_decision_status_code);
3626 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3627 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3628 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3629 RAISE OKL_API.G_EXCEPTION_ERROR;
3630 END IF;
3631 END IF;
3632 END LOOP;
3633 ELSIF(l_date is not null) THEN
3634 l_date:= FND_DATE.CANONICAL_TO_DATE(l_date);
3635 FOR each_row IN c_get_dates(l_date) LOOP
3636 wf_event.AddParameterToList(G_WF_ITM_SUB_POOL_ID, each_row.id, l_parameter_list);
3637 l_event_name := G_WF_EVT_POOL_NEAR_EXPIR;
3638 INSERT INTO
3639 OKL_G_REPORTS_GT(VALUE1_TEXT,
3640 VALUE2_TEXT,
3641 VALUE3_TEXT,
3642 VALUE4_TEXT,
3643 VALUE5_TEXT,
3644 VALUE6_TEXT,
3645 VALUE1_DATE)
3646 VALUES
3647 ('DATE',
3648 each_row.subsidy_pool_name,
3649 each_row.currency_code,
3650 okl_accounting_util.format_amount(each_row.total_budgets,each_row.currency_code),
3651 okl_accounting_util.format_amount(each_row.remaining_balance,each_row.currency_code),
3652 okl_accounting_util.format_amount(each_row.percent_remaining,each_row.currency_code),
3653 each_row.effective_to_date
3654 );
3655
3656 -- Raise the business event "Pool nearing expiration" for all the subsidy pool records found.
3657 raise_business_event(p_api_version => l_api_version,
3658 p_init_msg_list => l_init_msg_list,
3659 x_return_status => l_return_status,
3660 x_msg_count => l_msg_count,
3661 x_msg_data => l_msg_data,
3662 p_event_name => l_event_name,
3663 p_event_param_list => l_parameter_list
3664 );
3665 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3666 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3667 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3668 RAISE OKL_API.G_EXCEPTION_ERROR;
3669 END IF;
3670 END LOOP;
3671 ELSIF(P_DAYS is not null) THEN
3672 FOR each_row IN c_get_days(P_DAYS) LOOP
3673 wf_event.AddParameterToList(G_WF_ITM_SUB_POOL_ID, each_row.id, l_parameter_list);
3674 l_event_name := G_WF_EVT_POOL_NEAR_EXPIR;
3675 INSERT INTO
3676 OKL_G_REPORTS_GT(VALUE1_TEXT,
3677 VALUE2_TEXT,
3678 VALUE3_TEXT,
3679 VALUE4_TEXT,
3680 VALUE5_TEXT,
3681 VALUE6_TEXT,
3682 VALUE1_DATE,
3683 VALUE1_NUM)
3684 VALUES
3685 ('DAYS',
3686 each_row.subsidy_pool_name,
3687 each_row.currency_code,
3688 okl_accounting_util.format_amount(each_row.total_budgets,each_row.currency_code),
3689 okl_accounting_util.format_amount(each_row.remaining_balance,each_row.currency_code),
3690 okl_accounting_util.format_amount(each_row.percent_remaining,each_row.currency_code),
3691 each_row.effective_to_date,
3692 each_row.remaining_days
3693 );
3694
3695 -- Raise the business event "Pool nearing expiration" for all the subsidy pool records found.
3696 raise_business_event(p_api_version => l_api_version,
3697 p_init_msg_list => l_init_msg_list,
3698 x_return_status => l_return_status,
3699 x_msg_count => l_msg_count,
3700 x_msg_data => l_msg_data,
3701 p_event_name => l_event_name,
3702 p_event_param_list => l_parameter_list
3703 );
3704 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3705 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3706 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3707 RAISE OKL_API.G_EXCEPTION_ERROR;
3708 END IF;
3709 END LOOP;
3710 END IF;
3711
3712 END IF;
3713
3714 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
3715 x_return_status := l_return_status;
3716 x_msg_data := l_msg_data;
3717 x_msg_count := l_msg_count;
3718
3719 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
3720 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call xml_print_atlimit_detail');
3721 END IF;
3722 RETURN TRUE;
3723 EXCEPTION
3724 WHEN OTHERS THEN
3725 x_errbuf := SQLERRM;
3726 x_retcode := 2;
3727
3728 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
3729
3730 IF (SQLCODE <> -20001) THEN
3731 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
3732 ELSE
3733 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
3734 END IF;
3735
3736 END xml_print_atlimit_detail;
3737
3738 ---------------------------------------------------------------------------
3739 -- PROCEDURE xml_print_pool_summary
3740 ---------------------------------------------------------------------------
3741 ---------------------------------------------------------------------------
3742 -- Start of comments
3743 --
3744 -- Procedure Name : xml_print_pool_summary
3745 -- Description : To insert the subsidy pool summary in
3746 -- Global Temporary Table for XML Publisher.
3747 -- Business Rules :
3748 -- Parameters : p_pool_rec, p_from_date,p_to_date, x_return_status,
3749 -- x_msg_count,x_msg_data
3750 -- Version : 1.0
3751 -- History : 03-Jan-2007 UDHENUKO created.
3752 -- End of comments
3753 ---------------------------------------------------------------------------
3754 PROCEDURE xml_print_pool_summary (p_pool_rec IN okl_sub_pool_rec,
3755 p_from_date IN DATE,
3756 p_to_date IN DATE,
3757 x_return_status OUT NOCOPY VARCHAR2,
3758 x_msg_count OUT NOCOPY NUMBER,
3759 x_msg_data OUT NOCOPY VARCHAR2 )
3760 IS
3761
3762 -- Cursor to fetch all the children pools of a subsidy pool entered.
3763 CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
3764 SELECT id ,
3765 currency_code
3766 FROM okl_subsidy_pools_v pool
3767 WHERE pool_type_code = 'BUDGET'
3768 CONNECT BY PRIOR id = subsidy_pool_id
3769 START WITH id = cp_pool_id;
3770
3771 x_errbuf VARCHAR2(1000);
3772 x_retcode NUMBER;
3773 l_budget okl_subsidy_pools_b.total_budgets%TYPE;
3774 l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
3775 l_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
3776 l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
3777 l_api_name CONSTANT VARCHAR2(30) := 'xml_print_pool_summary';
3778 l_msg_count NUMBER;
3779 l_msg_data VARCHAR2(2000);
3780 l_return_status VARCHAR2(1);
3781 l_api_version NUMBER;
3782 l_init_msg_list VARCHAR2(1);
3783 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.xml_print_pool_summary';
3784 l_debug_enabled VARCHAR2(10);
3785 is_debug_procedure_on BOOLEAN;
3786 is_debug_statement_on BOOLEAN;
3787
3788 BEGIN
3789 l_debug_enabled := okl_debug_pub.check_log_enabled;
3790 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
3791 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
3792 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call xml_print_pool_summary');
3793 END IF;
3794 -- check for logging on STATEMENT level
3795 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
3796
3797 l_api_version := 1.0;
3798 l_init_msg_list := Okl_Api.g_false;
3799 l_msg_count := 0;
3800 l_budget := 0;
3801 l_trx_amt := 0;
3802 l_amount := 0;
3803 l_remaining_balance := 0;
3804
3805 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
3806 G_PKG_NAME,
3807 l_init_msg_list,
3808 l_api_version,
3809 l_api_version,
3810 '_PVT',
3811 l_return_status);
3812
3813 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3814 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3815 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3816 RAISE OKL_API.G_EXCEPTION_ERROR;
3817 END IF;
3818
3819 -- If subsidy pool type is "Budget" then pick the values for total budgets
3820 -- from the okl_subsidy_pools_b table and calculate the total transaction amount
3821 -- from the okl_trx_subsidy_pools table.
3822 IF (p_pool_rec.pool_type_code = 'BUDGET') THEN
3823 -- Parent pool header with the parent pool name.
3824 -- Calculate the total budgets for subsidy pool till the date specified.
3825 l_budget := total_budgets( p_pool_rec.id,
3826 p_to_date,
3827 p_pool_rec.currency_code,
3828 p_pool_rec.currency_code,
3829 p_pool_rec.currency_conversion_type,
3830 l_return_status,
3831 l_msg_count,
3832 l_msg_data
3833 );
3834 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3835 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3836 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3837 RAISE OKL_API.G_EXCEPTION_ERROR;
3838 END IF;
3839 -- calculate the total transaction amount for the subsidy pool.
3840 l_trx_amt := transaction_amount( p_pool_rec.id,
3841 p_to_date,
3842 p_pool_rec.currency_code,
3843 p_pool_rec.currency_code,
3844 p_pool_rec.currency_conversion_type,
3845 l_return_status,
3846 l_msg_count,
3847 l_msg_data
3848 );
3849 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3850 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3851 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3852 RAISE OKL_API.G_EXCEPTION_ERROR;
3853 END IF;
3854 -- remaining balance for subsidy pool is total budgets minus the total transaction amount.
3855 l_remaining_balance := l_budget - l_trx_amt;
3856
3857 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
3858 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
3859 l_module,
3860 ' l_budget '||l_budget||' l_trx_amt '||l_trx_amt
3861 ||'l_remaining_balance'||l_remaining_balance
3862 );
3863 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
3864
3865 -- Insert the parent pool record into the Global Temporary Table
3866 INSERT INTO
3867 OKL_G_REPORTS_GT(VALUE1_TEXT,
3868 VALUE2_TEXT,
3869 VALUE3_TEXT,
3870 VALUE4_TEXT,
3871 VALUE5_TEXT,
3872 VALUE6_TEXT,
3873 VALUE7_TEXT,
3874 VALUE8_TEXT)
3875 VALUES
3876 ('POOL_SUMMRY',
3877 p_pool_rec.subsidy_pool_name,
3878 p_pool_rec.pool_type_code,
3879 p_pool_rec.currency_code,
3880 okl_accounting_util.format_amount(l_budget,p_pool_rec.currency_code),
3881 okl_accounting_util.format_amount(0,p_pool_rec.currency_code), -- Pool Limit
3882 okl_accounting_util.format_amount(l_trx_amt,p_pool_rec.currency_code),
3883 okl_accounting_util.format_amount(l_remaining_balance,p_pool_rec.currency_code)
3884 );
3885
3886 -- if subsidy pool type is "Reporting" then the total budgets and total transaction amount
3887 -- is calculated from its children as there is no transaction and budgets for pool type "Reporting"
3888 ELSIF (p_pool_rec.pool_type_code = 'REPORTING') THEN
3889
3890 -- For each of the children found for "Reporting" pool type
3891 FOR each_row IN get_amounts(p_pool_rec.id) LOOP
3892 -- calculate the transaction amount and convert the children pool currency
3893 -- in to the "Reporting" pool currency.
3894 l_amount := transaction_amount ( each_row.id,
3895 p_to_date,
3896 each_row.currency_code,
3897 p_pool_rec.currency_code,
3898 p_pool_rec.currency_conversion_type,
3899 l_return_status,
3900 l_msg_count,
3901 l_msg_data
3902 );
3903 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3904 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3905 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3906 RAISE OKL_API.G_EXCEPTION_ERROR;
3907 END IF;
3908 l_trx_amt := l_trx_amt + l_amount;
3909 END LOOP;
3910 -- Insert the parent pool record into the Global Temporary Table
3911 INSERT INTO
3912 OKL_G_REPORTS_GT(VALUE1_TEXT,
3913 VALUE2_TEXT,
3914 VALUE3_TEXT,
3915 VALUE4_TEXT,
3916 VALUE5_TEXT,
3917 VALUE6_TEXT,
3918 VALUE7_TEXT,
3919 VALUE8_TEXT)
3920 VALUES
3921 ('POOL_SUMMRY',
3922 p_pool_rec.subsidy_pool_name,
3923 p_pool_rec.pool_type_code,
3924 p_pool_rec.currency_code,
3925 okl_accounting_util.format_amount(0,p_pool_rec.currency_code), -- Pool Budget
3926 okl_accounting_util.format_amount(p_pool_rec.reporting_pool_limit,p_pool_rec.currency_code),
3927 okl_accounting_util.format_amount(l_trx_amt,p_pool_rec.currency_code),
3928 okl_accounting_util.format_amount(0,p_pool_rec.currency_code) -- Pool Balance
3929 );
3930 END IF;
3931
3932 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
3933 x_return_status := l_return_status;
3934 x_msg_data := l_msg_data;
3935 x_msg_count := l_msg_count;
3936
3937 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
3938 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call xml_print_pool_summary');
3939 END IF;
3940
3941 EXCEPTION
3942 WHEN OTHERS THEN
3943 x_errbuf := SQLERRM;
3944 x_retcode := 2;
3945
3946 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
3947
3948 IF (SQLCODE <> -20001) THEN
3949 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
3950 ELSE
3951 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
3952 END IF;
3953
3954 END xml_print_pool_summary;
3955
3956 ---------------------------------------------------------------------------
3957 -- PROCEDURE xml_print_transaction_summary
3958 ---------------------------------------------------------------------------
3959 ---------------------------------------------------------------------------
3960 -- Start of comments
3961 --
3962 -- Procedure Name : xml_print_transaction_summary
3963 -- Description : To insert the subsidy pool transaction summary in
3964 -- Global Temporary Table for XML Publisher.
3965 -- Business Rules :
3966 -- Parameters : p_pool_id, p_from_date, p_to_date, p_pool_type, p_pool_currency,
3967 -- p_conv_type, x_return_status, x_msg_count, x_msg_data
3968 -- Version : 1.0
3969 -- History : 03-Jan-2007 UDHENUKO created.
3970 -- End of comments
3971 ---------------------------------------------------------------------------
3972 PROCEDURE xml_print_transaction_summary (p_pool_id IN okl_subsidy_pools_b.id%TYPE,
3973 p_from_date IN DATE,
3974 p_to_date IN DATE,
3975 p_pool_type IN okl_subsidy_pools_b.pool_type_code%TYPE,
3976 p_pool_currency IN okl_subsidy_pools_b.currency_code%TYPE,
3977 p_conv_type IN okl_subsidy_pools_b.currency_conversion_type%TYPE,
3978 x_return_status OUT NOCOPY VARCHAR2,
3979 x_msg_count OUT NOCOPY NUMBER ,
3980 x_msg_data OUT NOCOPY VARCHAR2 )
3981 IS
3982
3983 -- cursor to fetch all the transactions details for the subsidy pool between the dates entered by user.
3984 CURSOR c_transaction_detail(cp_pool_id okl_subsidy_pools_b.id%TYPE, cp_from_date DATE, cp_to_date DATE) IS
3985 SELECT flk1.meaning trx_reason,
3986 (case
3987 when pool.source_type_code = 'LEASE_CONTRACT' then
3988 (select khr.contract_number
3989 from okc_k_headers_b khr
3990 where khr.id = pool.source_object_id)
3991 when pool.source_type_code = 'SALES_QUOTE' then
3992 (select sq.reference_number
3993 from okl_lease_quotes_b sq
3994 where sq.id = pool.source_object_id)
3995 when pool.source_type_code = 'LEASE_APPLICATION' then
3996 (select lap.reference_number
3997 from okl_lease_applications_b lap,
3998 okl_lease_quotes_b lsq
3999 where lsq.parent_object_id = lap.id
4000 and lsq.parent_object_code = 'LEASEAPP'
4001 and lsq.id = pool.source_object_id)
4002 end) contract_number,
4003 dnz_asset_number,
4004 vend.vendor_name Vendor,
4005 sub.name subsidy_name,
4006 trx_type_code,
4007 source_trx_date,
4008 trx_currency_code,
4009 trx_amount,
4010 subsidy_pool_currency_code,
4011 pool.conversion_rate,
4012 subsidy_pool_amount,
4013 trx_date,
4014 hru.name operating_unit
4015 FROM okl_trx_subsidy_pools pool,
4016 fnd_lookups flk1,
4017 po_vendors vend,
4018 okl_subsidies_b sub,
4019 hr_organization_units hru
4020 WHERE flk1.lookup_type = 'OKL_SUB_POOL_TRX_REASON_TYPE'
4021 AND flk1.lookup_code = pool.trx_reason_code
4022 AND vend.vendor_id = pool.vendor_id
4023 AND sub.id = pool.subsidy_id
4024 AND pool.subsidy_pool_id IN ( SELECT id
4025 FROM okl_subsidy_pools_b
4026 WHERE pool_type_code = 'BUDGET'
4027 CONNECT BY PRIOR id = subsidy_pool_id
4028 START WITH id = cp_pool_id
4029 )
4030 AND sub.org_id = hru.organization_id
4031 ORDER BY trx_date asc;
4032
4033 x_errbuf VARCHAR2(1000);
4034 x_retcode NUMBER;
4035 l_pool_amount NUMBER;
4036 l_amount NUMBER;
4037 l_conv_rate NUMBER;
4038 l_sub_pool_amount NUMBER;
4039 l_trx_amount NUMBER;
4040 l_api_name CONSTANT VARCHAR2(30) := 'xml_print_transaction_summary';
4041 l_msg_count NUMBER;
4042 l_msg_data VARCHAR2(2000);
4043 l_return_status VARCHAR2(1);
4044 l_api_version NUMBER;
4045 l_init_msg_list VARCHAR2(1);
4046 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.xml_print_transaction_summary';
4047 l_debug_enabled VARCHAR2(10);
4048 is_debug_procedure_on BOOLEAN;
4049 is_debug_statement_on BOOLEAN;
4050
4051 BEGIN
4052
4053 l_debug_enabled := okl_debug_pub.check_log_enabled;
4054 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
4055
4056 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
4057 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call xml_print_transaction_summary');
4058 END IF;
4059 -- check for logging on STATEMENT level
4060 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
4061
4062 l_api_version := 1.0;
4063 l_init_msg_list := Okl_Api.g_false;
4064 l_msg_count := 0;
4065 l_pool_amount := 0;
4066 l_amount := 0;
4067 l_conv_rate := 0;
4068 l_sub_pool_amount := 0;
4069 l_trx_amount := 0;
4070
4071 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
4072 G_PKG_NAME,
4073 l_init_msg_list,
4074 l_api_version,
4075 l_api_version,
4076 '_PVT',
4077 l_return_status);
4078 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4079 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4080 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4081 RAISE OKL_API.G_EXCEPTION_ERROR;
4082 END IF;
4083
4084 IF(p_pool_type = 'BUDGET') THEN
4085 FOR each_row IN c_transaction_detail(p_pool_id,p_from_date,p_to_date) LOOP
4086 -- If transaction line type is "Reduction" display the transaction amount as
4087 -- <transaction amount>.
4088 IF(each_row.trx_type_code = 'ADDITION') THEN
4089 l_trx_amount := each_row.trx_amount;
4090 l_sub_pool_amount := each_row.subsidy_pool_amount;
4091 ELSIF(each_row.trx_type_code = 'REDUCTION') THEN
4092 l_trx_amount := each_row.trx_amount * -1;
4093 l_sub_pool_amount := each_row.subsidy_pool_amount * -1;
4094 END IF;
4095 -- Insert the transactions record for the subsidy pool type "Budget" in
4096 -- Global Temporary Table.
4097 INSERT INTO
4098 OKL_G_REPORTS_GT(VALUE1_TEXT,
4099 VALUE2_TEXT,
4100 VALUE3_TEXT,
4101 VALUE4_TEXT,
4102 VALUE5_TEXT,
4103 VALUE6_TEXT,
4104 VALUE1_DATE,
4105 VALUE7_TEXT,
4106 VALUE1_NUM,
4107 VALUE8_TEXT,
4108 VALUE2_NUM,
4109 VALUE9_TEXT,
4110 VALUE10_TEXT)
4111 VALUES
4112 ('TRANS_SUMMRY',
4113 each_row.trx_reason,
4114 each_row.contract_number,
4115 each_row.dnz_asset_number,
4116 each_row.Vendor,
4117 each_row.subsidy_name,
4118 each_row.source_trx_date,
4119 okl_accounting_util.format_amount(l_trx_amount,each_row.trx_currency_code)
4120 ||' '||each_row.trx_currency_code,
4121 each_row.conversion_rate,
4122 okl_accounting_util.format_amount(l_sub_pool_amount,each_row.subsidy_pool_currency_code)
4123 ||' '|| each_row.subsidy_pool_currency_code,
4124 l_sub_pool_amount,
4125 each_row.operating_unit,
4126 each_row.subsidy_pool_currency_code
4127 );
4128
4129 -- for all the transactions record found add the transaction amount with type
4130 -- "Addition" and reduce the amount with type "Reduction".
4131 IF(each_row.trx_type_code = 'ADDITION') THEN
4132 l_pool_amount := l_pool_amount + each_row.subsidy_pool_amount;
4133 ELSE
4134 l_pool_amount := l_pool_amount - each_row.subsidy_pool_amount;
4135 END IF;
4136 END LOOP;
4137
4138 ELSIF(p_pool_type = 'REPORTING') THEN
4139 FOR each_row IN c_transaction_detail(p_pool_id,p_from_date,p_to_date) LOOP
4140 -- If pool type is "Reporting", the transaction amount for all children "Budget"
4141 -- pool is converted in to the parent "Reporting" pool currency and this amount
4142 -- is displayed as a "Reporting amount".
4143 l_amount := currency_conversion(each_row.trx_amount,
4144 each_row.trx_currency_code,
4145 p_pool_currency,
4146 p_conv_type,
4147 each_row.trx_date,
4148 l_conv_rate
4149 );
4150 -- if negative value is returned display the error that the conversion between
4151 -- the two currencies is not found.
4152 IF (l_amount < 0) THEN
4153 fnd_message.set_name( G_APP_NAME,
4154 'OKL_POOL_CURR_CONV');
4155 fnd_message.set_token('FROM_CURR',
4156 each_row.subsidy_pool_currency_code);
4157 fnd_message.set_token('TO_CURR',
4158 p_pool_currency);
4159 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
4160 END IF;
4161 -- If transaction line type is "Reduction" display the transaction amount as
4162 -- <transaction amount>.
4163 IF(each_row.trx_type_code = 'ADDITION') THEN
4164 l_trx_amount := each_row.trx_amount;
4165 l_sub_pool_amount := l_amount;
4166 ELSIF(each_row.trx_type_code = 'REDUCTION') THEN
4167 l_trx_amount := each_row.trx_amount * -1;
4168 l_sub_pool_amount := l_amount * -1;
4169 END IF;
4170 -- Insert the transactions record for the subsidy pool in
4171 -- Global Temporary Table.
4172 INSERT INTO
4173 OKL_G_REPORTS_GT(VALUE1_TEXT,
4174 VALUE2_TEXT,
4175 VALUE3_TEXT,
4176 VALUE4_TEXT,
4177 VALUE5_TEXT,
4178 VALUE6_TEXT,
4179 VALUE1_DATE,
4180 VALUE7_TEXT,
4181 VALUE1_NUM,
4182 VALUE8_TEXT,
4183 VALUE2_NUM,
4184 VALUE9_TEXT,
4185 VALUE10_TEXT)
4186 VALUES
4187 ('TRANS_SUMMRY',
4188 each_row.trx_reason,
4189 each_row.contract_number,
4190 each_row.dnz_asset_number,
4191 each_row.Vendor,
4192 each_row.subsidy_name,
4193 each_row.source_trx_date,
4194 okl_accounting_util.format_amount(l_trx_amount,each_row.trx_currency_code)
4195 ||' '||each_row.trx_currency_code,
4196 l_conv_rate,
4197 okl_accounting_util.format_amount(l_sub_pool_amount,p_pool_currency)
4198 ||' '|| p_pool_currency,
4199 l_sub_pool_amount,
4200 each_row.operating_unit,
4201 each_row.subsidy_pool_currency_code
4202 );
4203
4204 -- for all the transactions record found add the transaction amount with type
4205 -- "Addition" and reduce the amount with type "Reduction".
4206 IF(each_row.trx_type_code = 'ADDITION') THEN
4207 l_pool_amount := l_pool_amount + l_amount;
4208 ELSE
4209 l_pool_amount := l_pool_amount - l_amount;
4210 END IF;
4211 END LOOP;
4212 END IF;
4213
4214 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
4215 x_return_status := l_return_status;
4216 x_msg_data := l_msg_data;
4217 x_msg_count := l_msg_count;
4218
4219 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
4220 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call xml_print_transaction_summary');
4221 END IF;
4222
4223 EXCEPTION
4224 WHEN OTHERS THEN
4225 x_errbuf := SQLERRM;
4226 x_retcode := 2;
4227
4228 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
4229
4230 IF (SQLCODE <> -20001) THEN
4231 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
4232 ELSE
4233 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
4234 END IF;
4235
4236 END xml_print_transaction_summary;
4237
4238 -------------------------------------------------------------------------------
4239 -- FUNCTION XML_POOL_RECONC_REPORT
4240 -------------------------------------------------------------------------------
4241 -- Start of comments
4242 --
4243 -- Procedure Name : XML_POOL_RECONC_REPORT
4244 -- Description : Function for Subsidy pool reconciliation Report Generation
4245 -- in XML Publisher
4246 -- Business Rules :
4247 -- Parameters :
4248 -- Version : 1.0
4249 -- History : 03-Jan-2007 UDHENUKO created.
4250 -- End of comments
4251 -------------------------------------------------------------------------------
4252
4253 FUNCTION XML_POOL_RECONC_REPORT RETURN BOOLEAN
4254 IS
4255
4256 x_errbuf VARCHAR2(2000);
4257 x_retcode NUMBER;
4258 l_pool_rec okl_sub_pool_rec;
4259 l_bdgt_pool_rec okl_sub_pool_rec;
4260 l_from_date DATE;
4261 l_to_date DATE;
4262 l_api_name CONSTANT VARCHAR2(30) := 'XML_POOL_RECONC_REPORT';
4263 l_msg_count NUMBER;
4264 l_msg_data VARCHAR2(2000);
4265 l_return_status VARCHAR2(1);
4266 l_api_version NUMBER;
4267 l_init_msg_list VARCHAR2(1);
4268 l_count NUMBER;
4269 l_sysdate DATE;
4270 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_RPT_PVT.XML_POOL_RECONC_REPORT';
4271 l_debug_enabled VARCHAR2(10);
4272 is_debug_procedure_on BOOLEAN;
4273 is_debug_statement_on BOOLEAN;
4274
4275 BEGIN
4276 l_debug_enabled := okl_debug_pub.check_log_enabled;
4277 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
4278 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
4279 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIOB.pls call XML_POOL_RECONC_REPORT');
4280 END IF;
4281 -- check for logging on STATEMENT level
4282 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
4283
4284 l_api_version := 1.0;
4285 l_init_msg_list := Okl_Api.g_false;
4286 l_msg_count := 0;
4287 l_sysdate := TRUNC(SYSDATE);
4288
4289 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
4290 G_PKG_NAME,
4291 l_init_msg_list,
4292 l_api_version,
4293 l_api_version,
4294 '_PVT',
4295 l_return_status);
4296 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4297 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4298 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4299 RAISE OKL_API.G_EXCEPTION_ERROR;
4300 END IF;
4301
4302 l_from_date:= FND_DATE.CANONICAL_TO_DATE(P_FROM_DATE);
4303 l_to_date:= FND_DATE.CANONICAL_TO_DATE(P_TO_DATE);
4304
4305 -- Get the record for user entered pool name.
4306 l_pool_rec := get_parent_record(P_POOL_ID);
4307
4308 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
4309 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
4310 l_module,
4311 'l_pool_rec.id '||l_pool_rec.id
4312 );
4313 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
4314
4315 l_count := 0;
4316
4317 -- If record is found in the table then print the pool details region
4318 -- and the transaction details region.
4319 IF (l_pool_rec.id is not null) THEN
4320 xml_print_pool_summary(l_pool_rec,
4321 l_from_date,
4322 l_to_date,
4323 l_return_status,
4324 l_msg_count,
4325 l_msg_data
4326 );
4327 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4328 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4329 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4330 RAISE OKL_API.G_EXCEPTION_ERROR;
4331 END IF;
4332 xml_print_transaction_summary (P_POOL_ID,
4333 l_from_date,
4334 l_to_date,
4335 l_pool_rec.pool_type_code,
4336 l_pool_rec.currency_code,
4337 l_pool_rec.currency_conversion_type,
4338 l_return_status,
4339 l_msg_count,
4340 l_msg_data
4341 );
4342 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4343 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4344 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4345 RAISE OKL_API.G_EXCEPTION_ERROR;
4346 END IF;
4347 END IF;
4348
4349 okl_api.END_ACTIVITY(l_msg_count, l_msg_data);
4350
4351 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
4352 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIOB.pls call XML_POOL_RECONC_REPORT');
4353 END IF;
4354 RETURN TRUE;
4355 EXCEPTION
4356 WHEN OTHERS THEN
4357 x_errbuf := SQLERRM;
4358 x_retcode := 2;
4359
4360 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
4361
4362 IF (SQLCODE <> -20001) THEN
4363 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
4364 ELSE
4365 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
4366 END IF;
4367
4368 END XML_POOL_RECONC_REPORT;
4369
4370 END okl_subsidy_pool_rpt_pvt;