[Home] [Help]
PACKAGE BODY: APPS.OKL_SUBSIDY_POOL_PVT
Source
1 PACKAGE BODY OKL_SUBSIDY_POOL_PVT AS
2 /* $Header: OKLRSIPB.pls 120.2 2005/09/12 23:45:28 cklee noship $ */
3
4 G_WF_EVT_SUBSIDY_POOL_PENDING CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.subsidy_pool.pool_pending_approval';
5 G_WF_EVT_SUBSIDY_POOL_APPROVED CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.subsidy_pool.pool_approved';
6 G_WF_EVT_SUBSIDY_POOL_REJECTED CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.subsidy_pool.pool_rejected';
7 G_WF_ITM_SUB_POOL_ID CONSTANT VARCHAR2(30) := 'SUBSIDY_POOL_ID';
8
9 -- Cursor to fetch the record with the passed id.
10 CURSOR c_get_pool_id (cp_pool_id IN okl_subsidy_pools_b.id%type) IS
11 SELECT id
12 ,object_version_number
13 ,sfwt_flag
14 ,pool_type_code
15 ,subsidy_pool_name
16 ,short_description
17 ,description
18 ,effective_from_date
19 ,effective_to_date
20 ,currency_code
21 ,currency_conversion_type
22 ,decision_status_code
23 ,subsidy_pool_id
24 ,reporting_pool_limit
25 ,total_budgets
26 ,total_subsidy_amount
27 ,decision_date
28 ,attribute_category
29 ,attribute1
30 ,attribute2
31 ,attribute3
32 ,attribute4
33 ,attribute5
34 ,attribute6
35 ,attribute7
36 ,attribute8
37 ,attribute9
38 ,attribute10
39 ,attribute11
40 ,attribute12
41 ,attribute13
42 ,attribute14
43 ,attribute15
44 ,created_by
45 ,creation_date
46 ,last_updated_by
47 ,last_update_date
48 ,last_update_login
49 FROM OKL_SUBSIDY_POOLS_V
50 WHERE id = cp_pool_id;
51
52 -------------------------------------------------------------------------------
53 -- PROCEDURE raise_business_event
54 -------------------------------------------------------------------------------
55 -- Start of comments
56 --
57 -- Procedure Name : raise_business_event
58 -- Description : This procedure is a wrapper that raises a business event
59 -- : when ever a subsidy pool record is submitted for approval, approved, rejected
60 -- Business Rules : the event is raised based on the decision_status_code passed and
61 -- successful updation of the pool record
62 -- Parameters :
63 -- Version : 1.0
64 -- History :
65 -- End of comments
66 -----------------------------------------------------------------------------------------
67 PROCEDURE raise_business_event(p_api_version IN NUMBER,
68 p_init_msg_list IN VARCHAR2,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2,
72 p_event_name IN VARCHAR2,
73 p_event_param_list IN WF_PARAMETER_LIST_T
74 ) IS
75 l_event_param_list WF_PARAMETER_LIST_T ;
76 BEGIN
77 x_return_status := OKL_API.G_RET_STS_SUCCESS;
78 l_event_param_list := p_event_param_list;
79
80 OKL_WF_PVT.raise_event(p_api_version => p_api_version,
81 p_init_msg_list => p_init_msg_list,
82 x_return_status => x_return_status,
83 x_msg_count => x_msg_count,
84 x_msg_data => x_msg_data,
85 p_event_name => p_event_name,
86 p_parameters => l_event_param_list);
87 EXCEPTION
88 WHEN OTHERS THEN
89 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
90 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
91 END raise_business_event;
92
93 ---------------------------------------------------------------------------
94 -- PROCEDURE create_sub_pool
95 ---------------------------------------------------------------------------
96 -- Start of comments
97 --
98 -- Procedure Name : create_sub_pool
99 -- Description : procedure for inserting the records in
100 -- table OKL_SUBSIDY_POOLS_B AND OKL_SUBSIDY_POOLS_TL
101 -- Business Rules : This procedure creates a subsidy pool with the status "new"
102 -- in the table OKL_SUBSIDY_POOLS_B.
103 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
104 -- x_msg_data, p_sub_pool_rec, x_sub_pool_rec.
105 -- Version : 1.0
106 -- End of comments
107 ---------------------------------------------------------------------------
108 PROCEDURE create_sub_pool ( p_api_version IN NUMBER
109 ,p_init_msg_list IN VARCHAR2
110 ,x_return_status OUT NOCOPY VARCHAR2
111 ,x_msg_count OUT NOCOPY NUMBER
112 ,x_msg_data OUT NOCOPY VARCHAR2
113 ,p_sub_pool_rec IN subsidy_pool_rec
114 ,x_sub_pool_rec OUT NOCOPY subsidy_pool_rec
115 ) IS
116
117 ---------------------------
118 -- DECLARE Local Variables
119 ---------------------------
120 L_MODULE CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_PVT.CREATE_SUB_POOL';
121 L_DEBUG_ENABLED VARCHAR2(10);
122 L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
123 IS_DEBUG_PROCEDURE_ON BOOLEAN;
124 l_sub_pool_rec subsidy_pool_rec ;
125 l_effective_to_date OKL_SUBSIDY_POOLS_B.EFFECTIVE_TO_DATE%TYPE ;
126 l_total_budgets OKL_SUBSIDY_POOLS_B.TOTAL_BUDGETS%TYPE ;
127 l_system_date DATE ;
128 l_api_version NUMBER ;
129 l_init_msg_list VARCHAR2(1) ;
130 l_return_status VARCHAR2(1);
131 l_msg_count NUMBER ;
132 l_msg_data VARCHAR2(2000);
133 l_api_name CONSTANT VARCHAR2(30) := 'create_sub_pool';
134
135 -------------------
136 -- DECLARE Cursors
137 -------------------
138
139
140 BEGIN
141 L_DEBUG_ENABLED := Okl_Debug_Pub.CHECK_LOG_ENABLED;
142 IF(L_DEBUG_ENABLED='Y') THEN
143 L_LEVEL_PROCEDURE :=Fnd_Log.LEVEL_PROCEDURE;
144 IS_DEBUG_PROCEDURE_ON := Okl_Debug_Pub.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
145 END IF;
146 IF(IS_DEBUG_PROCEDURE_ON) THEN
147 BEGIN
148 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSIPB.pls call okl_sip_pvt.insert_row ');
149 END;
150 END IF;
151
152 l_sub_pool_rec := p_sub_pool_rec;
153 l_effective_to_date := l_sub_pool_rec.effective_to_date;
154 l_total_budgets := l_sub_pool_rec.total_budgets;
155 l_system_date := TRUNC(SYSDATE);
156 l_api_version := 1.0;
157 l_init_msg_list := OKL_API.g_false;
158 l_msg_count := 0;
159
160 SAVEPOINT create_sub_pool_PVT;
161 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
162 G_PKG_NAME,
163 p_init_msg_list,
164 l_api_version,
165 p_api_version,
166 '_PVT',
167 l_return_status);
168
169 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
170 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
171 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
172 RAISE OKL_API.G_EXCEPTION_ERROR;
173 END IF;
174
175 -- check trunc(effective to date) < trunc(sysdate)
176 IF ((p_sub_pool_rec.EFFECTIVE_TO_DATE is NOT NULL) AND (p_sub_pool_rec.EFFECTIVE_TO_DATE < TRUNC(SYSDATE))) THEN
177 -- Message Text: Effective to date must be greater than or equal to system date.
178 l_return_status := OKL_API.G_RET_STS_ERROR;
179 OKL_API.set_message( p_app_name => G_APP_NAME,
180 p_msg_name => 'OKL_AM_DATE_EFF_TO_PAST');
181 RAISE G_EXCEPTION_HALT_VALIDATION;
182 END IF;
183
184 -- if the totoal budget amount is null set it to zero.
185 IF (l_total_budgets = OKL_API.G_MISS_NUM OR l_total_budgets IS NULL) THEN
186 l_total_budgets := 0;
187 END IF;
188 l_sub_pool_rec.total_budgets := l_total_budgets;
189
190 -- TAPI call to create a record for subsidy pool in table OKL_SUBSIDY_POOLS_B.
191 okl_sip_pvt.insert_row( l_api_version,
192 l_init_msg_list,
193 l_return_status,
194 l_msg_count,
195 l_msg_data,
196 l_sub_pool_rec,
197 x_sub_pool_rec);
198
199 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
200 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
201 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
202 RAISE OKL_API.G_EXCEPTION_ERROR;
203 END IF;
204
205 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
206 x_return_status := l_return_status;
207 x_msg_data := l_msg_data;
208 x_msg_count := l_msg_count;
209
210 IF(IS_DEBUG_PROCEDURE_ON) THEN
211 BEGIN
212 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSIPB.pls call okl_sip_pvt.insert_row ');
213 END;
214 END IF;
215
216 EXCEPTION
217 WHEN G_EXCEPTION_HALT_VALIDATION THEN
218 x_return_status := OKL_API.HANDLE_EXCEPTIONS
219 (
220 l_api_name,
221 G_PKG_NAME,
222 'OKL_API.G_RET_STS_ERROR',
223 x_msg_count,
224 x_msg_data,
225 '_PVT'
226 );
227
228
229 WHEN OKL_API.G_EXCEPTION_ERROR THEN
230 x_return_status := OKL_API.HANDLE_EXCEPTIONS
231 (
232 l_api_name,
233 G_PKG_NAME,
234 'OKL_API.G_RET_STS_ERROR',
235 x_msg_count,
236 x_msg_data,
237 '_PVT'
238 );
239
240 WHEN OTHERS THEN
241 x_return_status := OKL_API.HANDLE_EXCEPTIONS
242 (
243 l_api_name,
244 G_PKG_NAME,
245 'OKL_API.G_OTHERS',
246 x_msg_count,
247 x_msg_data,
248 '_PVT'
249 );
250
251 END create_sub_pool;
252
253 ---------------------------------------------------------------------------
254 -- PROCEDURE update_sub_pool
255 ---------------------------------------------------------------------------
256 -- Start of comments
257 --
258 -- Procedure Name : update_sub_pool
259 -- Description : procedure for updating the records in
260 -- table OKL_SUBSIDY_POOLS_B AND OKL_SUBSIDY_POOLS_TL
261 -- Business Rules : Procedure to update the subsidy pool.
262 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
263 -- x_msg_data, p_sub_pool_rec, x_sub_pool_rec.
264 -- Version : 1.0
265 -- End of comments
266 ---------------------------------------------------------------------------
267
268 PROCEDURE update_sub_pool ( p_api_version IN NUMBER
269 ,p_init_msg_list IN VARCHAR2
270 ,x_return_status OUT NOCOPY VARCHAR2
271 ,x_msg_count OUT NOCOPY NUMBER
272 ,x_msg_data OUT NOCOPY VARCHAR2
273 ,p_sub_pool_rec IN subsidy_pool_rec
274 ,x_sub_pool_rec OUT NOCOPY subsidy_pool_rec
275 ) IS
276 ---------------------------
277 -- DECLARE Local Variables
278 ---------------------------
279 L_MODULE CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_PVT.UPDATE_SUB_POOL';
280 L_DEBUG_ENABLED VARCHAR2(10);
281 L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
282 IS_DEBUG_PROCEDURE_ON BOOLEAN;
283 l_sub_pool_rec subsidy_pool_rec ;
284 l_effective_to_date OKL_SUBSIDY_POOLS_B.EFFECTIVE_TO_DATE%TYPE ;
285 l_db_effective_to OKL_SUBSIDY_POOLS_B.EFFECTIVE_TO_DATE%TYPE ;
286 l_trx_date OKL_TRX_SUBSIDY_POOLS.TRX_DATE%TYPE;
287 l_status_code OKL_SUBSIDY_POOLS_B.DECISION_STATUS_CODE%TYPE ;
288 l_system_date DATE;
289 l_api_version NUMBER ;
290 l_init_msg_list VARCHAR2(1) ;
291 l_return_status VARCHAR2(1);
292 l_msg_count NUMBER ;
293 l_msg_data VARCHAR2(2000);
294 l_api_name CONSTANT VARCHAR2(30) := 'update_sub_pool';
295 l_dummy VARCHAR2(1);
296 l_pool_name okl_subsidy_pools_b.subsidy_pool_name%TYPE ;
297 -------------------
298 -- DECLARE Cursors
299 -------------------
300 -- cursor to fetch the effective to date of the pool id passed.
301 CURSOR c_get_to_date (cp_pool_id IN okl_subsidy_pools_b.id%type) IS
302 SELECT effective_to_date
303 FROM okl_subsidy_pools_v
304 WHERE id = cp_pool_id;
305
306 -- Cursor to check whether the subsidies dates overlap with the subsidy pool dates.
307 CURSOR c_get_subsidy_date (cp_effective_to okl_subsidy_pools_b.effective_to_date%TYPE, cp_pool_id okl_subsidy_pools_B.id%TYPE) IS
308 SELECT 'X'
309 FROM okl_subsidy_pools_v pool, okl_subsidies_b sub
310 WHERE sub.subsidy_pool_id = pool.id
311 AND pool.id = cp_pool_id
312 AND (
313 NOT ((pool.effective_from_date BETWEEN sub.effective_from_date AND NVL(sub.effective_to_date,(TO_DATE('1','j') + 5300000)))
314 OR (sub.effective_from_date BETWEEN pool.effective_from_date AND cp_effective_to))
315 );
316
317 -- Cursor for fetching the transaction date for the subsidy pool.
318 CURSOR c_get_trx_date (cp_pool_id IN okl_subsidy_pools_b.id%type) IS
319 SELECT max(TRUNC(trx_date))
320 FROM okl_trx_subsidy_pools
321 WHERE subsidy_pool_id = cp_pool_id;
322
323
324
325 BEGIN
326 L_DEBUG_ENABLED := Okl_Debug_Pub.CHECK_LOG_ENABLED;
327 IF(L_DEBUG_ENABLED='Y') THEN
328 L_LEVEL_PROCEDURE :=Fnd_Log.LEVEL_PROCEDURE;
329 IS_DEBUG_PROCEDURE_ON := Okl_Debug_Pub.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
330 END IF;
331 IF(IS_DEBUG_PROCEDURE_ON) THEN
332 BEGIN
333 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSIPB.pls call okl_sip_pvt.update_row ');
334 END;
335 END IF;
336 l_sub_pool_rec := p_sub_pool_rec;
337 l_effective_to_date := l_sub_pool_rec.effective_to_date;
338 l_status_code := l_sub_pool_rec.decision_status_code;
339 --START:cklee 09/12/2005
340 --cklee 09/12/2005 l_sub_pool_rec.decision_status_code := TRUNC(SYSDATE);
341 l_system_date := TRUNC(SYSDATE);
342 --END:cklee 09/12/2005
343 l_api_version := 1.0;
344 l_init_msg_list := OKL_API.g_false;
345 l_msg_count := 0;
346 l_pool_name := l_sub_pool_rec.subsidy_pool_name;
347
348 SAVEPOINT update_sub_pool_PVT;
349 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
350 G_PKG_NAME,
351 p_init_msg_list,
352 l_api_version,
353 p_api_version,
354 '_PVT',
355 l_return_status);
356
357 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
358 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
359 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
360 RAISE OKL_API.G_EXCEPTION_ERROR;
361 END IF;
362
363 OPEN c_get_to_date(l_sub_pool_rec.id);
364 FETCH c_get_to_date INTO l_db_effective_to;
365 CLOSE c_get_to_date;
366
367 -- if user updates a effective to date of subsidy pool to less than the system date
368 -- then set the status of pool to "expired" otherwise update the subsidy pool.
369 IF ((l_effective_to_date is not null) AND
370 (nvl(l_db_effective_to,okl_accounting_util.g_final_date) <> l_effective_to_date)AND
371 (l_effective_to_date < l_system_date)) THEN
372 -- effective to date cannot be less than the system date if pool status is "NEW"
373 -- if pool status is "ACTIVE" and effective to date is less than system date then change
374 -- the pool status to "EXPIRED".
375 IF(l_sub_pool_rec.decision_status_code = 'NEW') THEN
376 l_return_status := OKL_API.G_RET_STS_ERROR;
377 OKL_API.set_message( p_app_name => G_APP_NAME,
378 p_msg_name => 'OKL_AM_DATE_EFF_TO_PAST');
379 RAISE G_EXCEPTION_HALT_VALIDATION;
380 ELSE
381 l_status_code := 'EXPIRED';
382 END IF;
383 END IF;
384
385 l_sub_pool_rec.decision_status_code := l_status_code;
386
387 -- If effective to date of subsidy pool is updated, and the effective dates of the subsidies
388 -- attached to the pool do not overlap then throw an error that the date cannot be updated.
389 IF ((l_effective_to_date is not null) AND
390 (nvl(l_db_effective_to,okl_accounting_util.g_final_date) <> l_effective_to_date)) THEN
391 OPEN c_get_subsidy_date(l_effective_to_date,l_sub_pool_rec.id);
392 FETCH c_get_subsidy_date INTO l_dummy;
393 IF (c_get_subsidy_date%FOUND) THEN
394 CLOSE c_get_subsidy_date;
395 x_return_status := OKC_API.G_RET_STS_ERROR;
396 OKC_API.set_message( p_app_name => G_APP_NAME,
397 p_msg_name => 'OKL_SUB_POOL_NO_DATE_OVERLAP',
398 p_token1 => 'POOL_NAME',
399 p_token1_value => l_pool_name);
400 RAISE G_EXCEPTION_HALT_VALIDATION;
401 ELSE
402 CLOSE c_get_subsidy_date;
403 END IF;
404 END IF;
405
406 -- If the effective to date is less than the transation date for the subsidy pool
407 -- then throw error that user cannot modify the effective to date less than the transaction date.
408 IF ((l_effective_to_date is not null) AND
409 (nvl(l_db_effective_to,okl_accounting_util.g_final_date) <> l_effective_to_date)) THEN
410 OPEN c_get_trx_date(l_sub_pool_rec.id);
411 FETCH c_get_trx_date INTO l_trx_date;
412 IF (c_get_trx_date%FOUND AND l_effective_to_date < l_trx_date) THEN
413 CLOSE c_get_trx_date;
414 x_return_status := OKC_API.G_RET_STS_ERROR;
415 OKC_API.set_message( p_app_name => G_APP_NAME,
416 p_msg_name => 'OKL_SUB_POOL_TRX_DATE',
417 p_token1 => 'POOL_NAME',
418 p_token1_value => l_sub_pool_rec.subsidy_pool_name,
419 p_token2 => 'TRX_DATE',
420 p_token2_value => l_trx_date);
421 RAISE G_EXCEPTION_HALT_VALIDATION;
422 ELSE
423 CLOSE c_get_trx_date;
424 END IF;
425 END IF;
426
427 -- TAPI call to update the subsidy pool in table OKL_SUBSIDY_POOLS_B.
428 okl_sip_pvt.update_row(l_api_version,
429 l_init_msg_list,
430 l_return_status,
431 l_msg_count,
432 l_msg_data,
433 l_sub_pool_rec,
434 x_sub_pool_rec);
435
436 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
437 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
438 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
439 RAISE OKL_API.G_EXCEPTION_ERROR;
440 END IF;
441
442 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
443 x_return_status := l_return_status;
444 x_msg_data := l_msg_data;
445 x_msg_count := l_msg_count;
446
447 IF(IS_DEBUG_PROCEDURE_ON) THEN
448 BEGIN
449 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSIPB.pls call okl_sip_pvt.update_row ');
450 END;
451 END IF;
452
453 EXCEPTION
454 WHEN G_EXCEPTION_HALT_VALIDATION THEN
455 x_return_status := OKL_API.HANDLE_EXCEPTIONS
456 (
457 l_api_name,
458 G_PKG_NAME,
459 'OKL_API.G_RET_STS_ERROR',
460 x_msg_count,
461 x_msg_data,
462 '_PVT'
463 );
464
465
466 WHEN OKL_API.G_EXCEPTION_ERROR THEN
467 x_return_status := OKL_API.HANDLE_EXCEPTIONS
468 (
469 l_api_name,
470 G_PKG_NAME,
471 'OKL_API.G_RET_STS_ERROR',
472 x_msg_count,
473 x_msg_data,
474 '_PVT'
475 );
476
477 WHEN OTHERS THEN
478 x_return_status := OKL_API.HANDLE_EXCEPTIONS
479 (
480 l_api_name,
481 G_PKG_NAME,
482 'OKL_API.G_OTHERS',
483 x_msg_count,
484 x_msg_data,
485 '_PVT'
486 );
487 END update_sub_pool;
488
489 ---------------------------------------------------------------------------
490 -- PROCEDURE expire_sub_pool
491 ---------------------------------------------------------------------------
492 -- Start of comments
493 --
494 -- Procedure Name : expire_sub_pool
495 -- Description : procedure for validating that if the records exist in the
496 -- table OKL_SUBSIDY_POOLS_B then set its status to expire.
497 -- Business Rules : This procedure sets the pool status to "expire"and this is
498 -- an autonomous transaction.
499 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
500 -- x_msg_data, p_subsidy_pool_id.
501 -- Version : 1.0
502 -- End of comments
503 ---------------------------------------------------------------------------
504
505 PROCEDURE expire_sub_pool ( p_api_version IN NUMBER,
506 p_init_msg_list IN VARCHAR2,
507 x_return_status OUT NOCOPY VARCHAR2,
508 x_msg_count OUT NOCOPY NUMBER,
509 x_msg_data OUT NOCOPY VARCHAR2,
510 p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE) IS
511 ---------------------------
512 -- DECLARE Local Variables
513 ---------------------------
514 L_MODULE CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_PVT.EXPIRE_SUB_POOL';
515 L_DEBUG_ENABLED VARCHAR2(10);
516 L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
517 IS_DEBUG_PROCEDURE_ON BOOLEAN;
518 l_sub_pool_rec subsidy_pool_rec;
519 l_subsidy_pool_id OKL_SUBSIDY_POOLS_B.ID%TYPE ;
520 x_sub_pool_rec subsidy_pool_rec;
521 l_row_found BOOLEAN ;
522 l_api_version NUMBER ;
523 l_init_msg_list VARCHAR2(1) ;
524 l_return_status VARCHAR2(1);
525 l_msg_count NUMBER ;
526 l_msg_data VARCHAR2(2000);
527 l_api_name CONSTANT VARCHAR2(30) := 'expire_sub_pool';
528
529 -------------------
530 -- DECLARE Cursors
531 -------------------
532
533 -- this procedure uses autonomous transaction, which will always be commited
534 -- irrespective of the calling procedure.
535
536 PRAGMA AUTONOMOUS_TRANSACTION;
537 BEGIN
538 L_DEBUG_ENABLED := Okl_Debug_Pub.CHECK_LOG_ENABLED;
539 IF(L_DEBUG_ENABLED='Y') THEN
540 L_LEVEL_PROCEDURE :=Fnd_Log.LEVEL_PROCEDURE;
541 IS_DEBUG_PROCEDURE_ON := Okl_Debug_Pub.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
542 END IF;
543 IF(IS_DEBUG_PROCEDURE_ON) THEN
544 BEGIN
545 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSIPB.pls call okl_sip_pvt.update_row ');
546 END;
547 END IF;
548 l_subsidy_pool_id := p_subsidy_pool_id;
549 l_row_found := FALSE;
550 l_api_version := 1.0;
551 l_init_msg_list := OKL_API.g_false;
552 l_msg_count := 0;
553
554 SAVEPOINT expire_sub_pool_PVT;
555 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
556 G_PKG_NAME,
557 p_init_msg_list,
558 l_api_version,
559 p_api_version,
560 '_PVT',
561 l_return_status);
562
563 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
564 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
565 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
566 RAISE OKL_API.G_EXCEPTION_ERROR;
567 END IF;
568
569 -- fetch the record with the passed id .
570 OPEN c_get_pool_id(l_subsidy_pool_id);
571 FETCH c_get_pool_id INTO l_sub_pool_rec;
572 l_row_found := c_get_pool_id%FOUND;
573 CLOSE c_get_pool_id;
574
575 -- If a row exists then set the status of pool to "expired" and commit it.
576 -- this is an autonomous transaction and will get commited irrespective of the calling procedure.
577 IF l_row_found THEN
578 l_sub_pool_rec.decision_status_code := 'EXPIRED';
579 okl_sip_pvt.update_row(l_api_version,
580 l_init_msg_list,
581 l_return_status,
582 l_msg_count,
583 l_msg_data,
584 l_sub_pool_rec,
585 x_sub_pool_rec);
586 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
587 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
588 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
589 RAISE OKL_API.G_EXCEPTION_ERROR;
590 END IF;
591 END IF;
592 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
593 x_return_status := l_return_status;
594 x_msg_data := l_msg_data;
595 x_msg_count := l_msg_count;
596
597 COMMIT;
598 IF(IS_DEBUG_PROCEDURE_ON) THEN
599 BEGIN
600 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSIPB.pls call okl_sip_pvt.update_row ');
601 END;
602 END IF;
603
604 EXCEPTION
605 WHEN G_EXCEPTION_HALT_VALIDATION THEN
606 x_return_status := OKL_API.HANDLE_EXCEPTIONS
607 (
608 l_api_name,
609 G_PKG_NAME,
610 'OKL_API.G_RET_STS_ERROR',
611 x_msg_count,
612 x_msg_data,
613 '_PVT'
614 );
615
616 WHEN OKL_API.G_EXCEPTION_ERROR THEN
617 x_return_status := OKL_API.HANDLE_EXCEPTIONS
618 (
619 l_api_name,
620 G_PKG_NAME,
621 'OKL_API.G_RET_STS_ERROR',
622 x_msg_count,
623 x_msg_data,
624 '_PVT'
625 );
626
627 WHEN OTHERS THEN
628 x_return_status := OKL_API.HANDLE_EXCEPTIONS
629 (
630 l_api_name,
631 G_PKG_NAME,
632 'OKL_API.G_OTHERS',
633 x_msg_count,
634 x_msg_data,
635 '_PVT'
636 );
637 END expire_sub_pool;
638
639 ---------------------------------------------------------------------------
640 -- PROCEDURE update_total_budget
641 ---------------------------------------------------------------------------
642 -- Start of comments
643 --
644 -- Procedure Name : update_total_budget
645 -- Description : procedure for updating the total budget amount
646 -- table OKL_SUBSIDY_POOLS_B.
647 -- Business Rules : As soon as any of the budget line attached to a subsisy pool gets
648 -- approved this procedure is called to update the total budgets of the pool
649 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
650 -- x_msg_data, p_subsidy_pool_id,p_total_budget_amt.
651 -- Version : 1.0
652 -- End of comments
653 ---------------------------------------------------------------------------
654
655 PROCEDURE update_total_budget ( p_api_version IN NUMBER,
656 p_init_msg_list IN VARCHAR2,
657 x_return_status OUT NOCOPY VARCHAR2,
658 x_msg_count OUT NOCOPY NUMBER,
659 x_msg_data OUT NOCOPY VARCHAR2,
660 p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE,
661 p_total_budget_amt IN okl_subsidy_pools_b.total_budgets%TYPE ) IS
662 ---------------------------
663 -- DECLARE Local Variables
664 ---------------------------
665 L_MODULE CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_PVT.UPDATE_TOTAL_BUDGET';
666 L_DEBUG_ENABLED VARCHAR2(10);
667 L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
668 IS_DEBUG_PROCEDURE_ON BOOLEAN;
669 l_sub_pool_rec subsidy_pool_rec;
670 l_subsidy_pool_id OKL_SUBSIDY_POOLS_B.ID%TYPE ;
671 x_sub_pool_rec subsidy_pool_rec;
672 l_row_found BOOLEAN ;
673 l_api_version NUMBER ;
674 l_init_msg_list VARCHAR2(1) ;
675 l_return_status VARCHAR2(1);
676 l_msg_count NUMBER ;
677 l_msg_data VARCHAR2(2000);
678 l_api_name CONSTANT VARCHAR2(30) := 'update_total_budget';
679
680 -------------------
681 -- DECLARE Cursors
682 -------------------
683
684 BEGIN
685 L_DEBUG_ENABLED := Okl_Debug_Pub.CHECK_LOG_ENABLED;
686 IF(L_DEBUG_ENABLED='Y') THEN
687 L_LEVEL_PROCEDURE :=Fnd_Log.LEVEL_PROCEDURE;
688 IS_DEBUG_PROCEDURE_ON := Okl_Debug_Pub.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
689 END IF;
690 IF(IS_DEBUG_PROCEDURE_ON) THEN
691 BEGIN
692 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSIPB.pls call okl_sip_pvt.update_row ');
693 END;
694 END IF;
695 l_subsidy_pool_id := p_subsidy_pool_id;
696 l_row_found := FALSE;
697 l_api_version := 1.0;
698 l_init_msg_list := OKL_API.g_false;
699 l_msg_count := 0;
700
701 SAVEPOINT update_total_budget_PVT;
702 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
703 G_PKG_NAME,
704 p_init_msg_list,
705 l_api_version,
706 p_api_version,
707 '_PVT',
708 l_return_status);
709
710 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
711 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
712 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
713 RAISE OKL_API.G_EXCEPTION_ERROR;
714 END IF;
715
716 -- fetch the record with the passed id.
717 OPEN c_get_pool_id(l_subsidy_pool_id);
718 FETCH c_get_pool_id INTO l_sub_pool_rec;
719 l_row_found := c_get_pool_id%FOUND;
720 CLOSE c_get_pool_id;
721
722 -- if row is found then update the total budget amount with the amount passed to it as a parameter.
723 IF l_row_found THEN
724 l_sub_pool_rec.total_budgets := p_total_budget_amt;
725 okl_sip_pvt.update_row(l_api_version,
726 l_init_msg_list,
727 l_return_status,
728 l_msg_count,
729 l_msg_data,
730 l_sub_pool_rec,
731 x_sub_pool_rec);
732 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
733 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
734 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
735 RAISE OKL_API.G_EXCEPTION_ERROR;
736 END IF;
737 END IF;
738 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
739 x_return_status := l_return_status;
740 x_msg_data := l_msg_data;
741 x_msg_count := l_msg_count;
742
743 IF(IS_DEBUG_PROCEDURE_ON) THEN
744 BEGIN
745 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSIPB.pls call okl_sip_pvt.update_row ');
746 END;
747 END IF;
748
749 EXCEPTION
750 WHEN G_EXCEPTION_HALT_VALIDATION THEN
751 x_return_status := OKL_API.HANDLE_EXCEPTIONS
752 (
753 l_api_name,
754 G_PKG_NAME,
755 'OKL_API.G_RET_STS_ERROR',
756 x_msg_count,
757 x_msg_data,
758 '_PVT'
759 );
760
761 WHEN OKL_API.G_EXCEPTION_ERROR THEN
762 x_return_status := OKL_API.HANDLE_EXCEPTIONS
763 (
764 l_api_name,
765 G_PKG_NAME,
766 'OKL_API.G_RET_STS_ERROR',
767 x_msg_count,
768 x_msg_data,
769 '_PVT'
770 );
771
772 WHEN OTHERS THEN
773 x_return_status := OKL_API.HANDLE_EXCEPTIONS
774 (
775 l_api_name,
776 G_PKG_NAME,
777 'OKL_API.G_OTHERS',
778 x_msg_count,
779 x_msg_data,
780 '_PVT'
781 );
782 END update_total_budget;
783
784 ---------------------------------------------------------------------------
785 -- PROCEDURE update_subsidy_amount
786 ---------------------------------------------------------------------------
787 -- Start of comments
788 --
789 -- Procedure Name : update_subsidy_amount
790 -- Description : procedure for updating the total subsidy amount
791 -- table OKL_SUBSIDY_POOLS_B.
792 -- Business Rules : subsidy amount is updated when the contract is booked, rebooked or a
793 -- quote is created, or a contract is reversed.
794 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
795 -- x_msg_data, p_subsidy_pool_id, p_total_subsidy_amt.
796 -- Version : 1.0
797 -- End of comments
798 ---------------------------------------------------------------------------
799
800 PROCEDURE update_subsidy_amount ( p_api_version IN NUMBER,
801 p_init_msg_list IN VARCHAR2,
802 x_return_status OUT NOCOPY VARCHAR2,
803 x_msg_count OUT NOCOPY NUMBER,
804 x_msg_data OUT NOCOPY VARCHAR2,
805 p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE,
806 p_total_subsidy_amt IN okl_subsidy_pools_b.total_subsidy_amount%TYPE) IS
807
808 ---------------------------
809 -- DECLARE Local Variables
810 ---------------------------
811 L_MODULE CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_PVT.UPDATE_SUBSIDY_AMOUNT';
812 L_DEBUG_ENABLED VARCHAR2(10);
813 L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
814 IS_DEBUG_PROCEDURE_ON BOOLEAN;
815 l_sub_pool_rec subsidy_pool_rec;
816 l_subsidy_pool_id OKL_SUBSIDY_POOLS_B.ID%TYPE ;
817 x_sub_pool_rec subsidy_pool_rec;
818 l_row_found BOOLEAN ;
819 l_api_version NUMBER ;
820 l_init_msg_list VARCHAR2(1);
821 l_return_status VARCHAR2(1);
822 l_msg_count NUMBER;
823 l_msg_data VARCHAR2(2000);
824 l_api_name CONSTANT VARCHAR2(30) := 'update_subsidy_amount';
825
826 -------------------
827 -- DECLARE Cursors
828 -------------------
829
830 BEGIN
831 L_DEBUG_ENABLED := Okl_Debug_Pub.CHECK_LOG_ENABLED;
832 IF(L_DEBUG_ENABLED='Y') THEN
833 L_LEVEL_PROCEDURE :=Fnd_Log.LEVEL_PROCEDURE;
834 IS_DEBUG_PROCEDURE_ON := Okl_Debug_Pub.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
835 END IF;
836 IF(IS_DEBUG_PROCEDURE_ON) THEN
837 BEGIN
838 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSIPB.pls call okl_sip_pvt.update_row ');
839 END;
840 END IF;
841 l_subsidy_pool_id := p_subsidy_pool_id;
842 l_row_found := FALSE;
843 l_api_version := 1.0;
844 l_init_msg_list := OKL_API.g_false;
845 l_msg_count := 0;
846
847 SAVEPOINT update_subsidy_amount_PVT;
848 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
849 G_PKG_NAME,
850 p_init_msg_list,
851 l_api_version,
852 p_api_version,
853 '_PVT',
854 l_return_status);
855
856 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
857 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
858 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
859 RAISE OKL_API.G_EXCEPTION_ERROR;
860 END IF;
861
862 OPEN c_get_pool_id(l_subsidy_pool_id);
863 FETCH c_get_pool_id INTO l_sub_pool_rec;
864 l_row_found := c_get_pool_id%FOUND;
865 CLOSE c_get_pool_id;
866
867 -- if a row is found then update the total subsidy amount to the amount passed.
868 IF l_row_found THEN
869 l_sub_pool_rec.total_subsidy_amount := p_total_subsidy_amt;
870 okl_sip_pvt.update_row(l_api_version,
871 l_init_msg_list,
872 l_return_status,
873 l_msg_count,
874 l_msg_data,
875 l_sub_pool_rec,
876 x_sub_pool_rec);
877 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
878 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
879 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
880 RAISE OKL_API.G_EXCEPTION_ERROR;
881 END IF;
882 END IF;
883 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
884 x_return_status := l_return_status;
885 x_msg_data := l_msg_data;
886 x_msg_count := l_msg_count;
887
888 IF(IS_DEBUG_PROCEDURE_ON) THEN
889 BEGIN
890 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSIPB.pls call okl_sip_pvt.update_row ');
891 END;
892 END IF;
893
894 EXCEPTION
895 WHEN G_EXCEPTION_HALT_VALIDATION THEN
896 x_return_status := OKL_API.HANDLE_EXCEPTIONS
897 (
898 l_api_name,
899 G_PKG_NAME,
900 'OKL_API.G_RET_STS_ERROR',
901 x_msg_count,
902 x_msg_data,
903 '_PVT'
904 );
905
906 WHEN OKL_API.G_EXCEPTION_ERROR THEN
907 x_return_status := OKL_API.HANDLE_EXCEPTIONS
908 (
909 l_api_name,
910 G_PKG_NAME,
911 'OKL_API.G_RET_STS_ERROR',
912 x_msg_count,
913 x_msg_data,
914 '_PVT'
915 );
916
917 WHEN OTHERS THEN
918 x_return_status := OKL_API.HANDLE_EXCEPTIONS
919 (
920 l_api_name,
921 G_PKG_NAME,
922 'OKL_API.G_OTHERS',
923 x_msg_count,
924 x_msg_data,
925 '_PVT'
926 );
927 END update_subsidy_amount;
928
929 ---------------------------------------------------------------------------
930 -- PROCEDURE set_decision_status_code
931 ---------------------------------------------------------------------------
932 -- Start of comments
933 --
934 -- Procedure Name : set_decision_status_code
935 -- Description : procedure for updating the decision status code
936 -- table OKL_SUBSIDY_POOLS_B.
937 -- Business Rules : Procedure sets the decision_status_code to the value passed to this procedure.
938 -- this is a status of a pool.
939 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
940 -- x_msg_data, p_subsidy_pool_id, p_total_subsidy_amt,p_decision_status_code.
941 -- Version : 1.0
942 -- End of comments
943 ---------------------------------------------------------------------------
944
945 PROCEDURE set_decision_status_code ( p_api_version IN NUMBER,
946 p_init_msg_list IN VARCHAR2,
947 x_return_status OUT NOCOPY VARCHAR2,
948 x_msg_count OUT NOCOPY NUMBER,
949 x_msg_data OUT NOCOPY VARCHAR2,
950 p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE,
951 p_decision_status_code IN OUT NOCOPY okl_subsidy_pools_b.decision_status_code%TYPE) IS
952
953 ---------------------------
954 -- DECLARE Local Variables
955 ---------------------------
956 L_MODULE CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_PVT.SET_DECISION_STATUS_CODE';
957 L_DEBUG_ENABLED VARCHAR2(10);
958 L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
959 IS_DEBUG_PROCEDURE_ON BOOLEAN;
960 l_sub_pool_rec subsidy_pool_rec;
961 l_subsidy_pool_id OKL_SUBSIDY_POOLS_B.ID%TYPE ;
962 x_sub_pool_rec subsidy_pool_rec;
963 l_row_found BOOLEAN ;
964 l_api_version NUMBER ;
965 l_init_msg_list VARCHAR2(1) ;
966 l_return_status VARCHAR2(1);
967 l_msg_count NUMBER ;
968 l_msg_data VARCHAR2(2000);
969 l_api_name CONSTANT VARCHAR2(30) := 'set_decision_status_code';
970 l_parameter_list WF_PARAMETER_LIST_T;
971 l_event_name wf_events.name%TYPE;
972 l_system_date CONSTANT DATE DEFAULT TRUNC(SYSDATE);
973 -------------------
974 -- DECLARE Cursors
975 -------------------
976
977 BEGIN
978 L_DEBUG_ENABLED := Okl_Debug_Pub.CHECK_LOG_ENABLED;
979 IF(L_DEBUG_ENABLED='Y') THEN
980 L_LEVEL_PROCEDURE :=Fnd_Log.LEVEL_PROCEDURE;
981 IS_DEBUG_PROCEDURE_ON := Okl_Debug_Pub.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
982 END IF;
983 IF(IS_DEBUG_PROCEDURE_ON) THEN
984 BEGIN
985 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSIPB.pls call okl_sip_pvt.update_row ');
986 END;
987 END IF;
988 l_subsidy_pool_id := p_subsidy_pool_id;
989 l_row_found := FALSE;
990 l_api_version := 1.0;
991 l_init_msg_list := OKL_API.g_false;
992 l_msg_count := 0;
993
994 SAVEPOINT set_decision_status_code_PVT;
995 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
996 G_PKG_NAME,
997 p_init_msg_list,
998 l_api_version,
999 p_api_version,
1000 '_PVT',
1001 l_return_status);
1002
1003 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1004 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1005 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1006 RAISE OKL_API.G_EXCEPTION_ERROR;
1007 END IF;
1008
1009 OPEN c_get_pool_id(l_subsidy_pool_id);
1010 FETCH c_get_pool_id INTO l_sub_pool_rec;
1011 l_row_found := c_get_pool_id%FOUND;
1012 CLOSE c_get_pool_id;
1013
1014 -- update the status of the pool with the valus passed to it.
1015 IF l_row_found THEN
1016 l_sub_pool_rec.decision_status_code := p_decision_status_code;
1017 l_sub_pool_rec.decision_date := l_system_date;
1018 okl_sip_pvt.update_row(l_api_version,
1019 l_init_msg_list,
1020 l_return_status,
1021 l_msg_count,
1022 l_msg_data,
1023 l_sub_pool_rec,
1024 x_sub_pool_rec);
1025 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1026 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1027 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1028 RAISE OKL_API.G_EXCEPTION_ERROR;
1029 END IF;
1030 IF(l_sub_pool_rec.decision_status_code = 'PENDING')THEN
1031 -- add subsidy pool id and subsidy pool to the parameter list and call the corresponding business event.
1032 wf_event.AddParameterToList(G_WF_ITM_SUB_POOL_ID, l_sub_pool_rec.id, l_parameter_list);
1033 l_event_name := G_WF_EVT_SUBSIDY_POOL_PENDING;
1034 ELSIF(l_sub_pool_rec.decision_status_code = 'ACTIVE')THEN
1035 -- add subsidy pool id and subsidy pool to the parameter list call the corresponding business event.
1036 wf_event.AddParameterToList(G_WF_ITM_SUB_POOL_ID, l_sub_pool_rec.id, l_parameter_list);
1037 l_event_name := G_WF_EVT_SUBSIDY_POOL_APPROVED;
1038 ELSIF(l_sub_pool_rec.decision_status_code = 'REJECTED')THEN
1039 -- add subsidy pool id and subsidy pool to the parameter list call the corresponding business event.
1040 wf_event.AddParameterToList(G_WF_ITM_SUB_POOL_ID, l_sub_pool_rec.id, l_parameter_list);
1041 l_event_name := G_WF_EVT_SUBSIDY_POOL_REJECTED;
1042 END IF;
1043 IF (l_event_name IS NOT NULL) THEN
1044 raise_business_event(p_api_version => p_api_version,
1045 p_init_msg_list => p_init_msg_list,
1046 x_return_status => x_return_status,
1047 x_msg_count => x_msg_count,
1048 x_msg_data => x_msg_data,
1049 p_event_name => l_event_name,
1050 p_event_param_list => l_parameter_list
1051 );
1052 END IF;
1053 END IF;
1054 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1055 x_return_status := l_return_status;
1056 x_msg_data := l_msg_data;
1057 x_msg_count := l_msg_count;
1058
1059 IF(IS_DEBUG_PROCEDURE_ON) THEN
1060 BEGIN
1061 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSIPB.pls call okl_sip_pvt.update_row ');
1062 END;
1063 END IF;
1064
1065 EXCEPTION
1066 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1067 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1068 (
1069 l_api_name,
1070 G_PKG_NAME,
1071 'OKL_API.G_RET_STS_ERROR',
1072 x_msg_count,
1073 x_msg_data,
1074 '_PVT'
1075 );
1076
1077 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1078 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1079 (
1080 l_api_name,
1081 G_PKG_NAME,
1082 'OKL_API.G_RET_STS_ERROR',
1083 x_msg_count,
1084 x_msg_data,
1085 '_PVT'
1086 );
1087
1088 WHEN OTHERS THEN
1089 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1090 (
1091 l_api_name,
1092 G_PKG_NAME,
1093 'OKL_API.G_OTHERS',
1094 x_msg_count,
1095 x_msg_data,
1096 '_PVT'
1097 );
1098 END set_decision_status_code;
1099
1100
1101 ---------------------------------------------------------------------------
1102 -- PROCEDURE validate_sub_pool
1103 ---------------------------------------------------------------------------
1104 -- Start of comments
1105 --
1106 -- Procedure Name : validate_sub_pool
1107 -- Description : procedure for validating the records in
1108 -- table OKL_SUBSIDY_POOLS_B AND OKL_SUBSIDY_POOLS_TL
1109 -- Business Rules : Validates the record passed to it.
1110 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
1111 -- x_msg_data, p_sub_pool_rec.
1112 -- Version : 1.0
1113 -- End of comments
1114 ---------------------------------------------------------------------------
1115
1116 PROCEDURE validate_sub_pool( p_api_version IN NUMBER,
1117 p_init_msg_list IN VARCHAR2,
1118 x_return_status OUT NOCOPY VARCHAR2,
1119 x_msg_count OUT NOCOPY NUMBER,
1120 x_msg_data OUT NOCOPY VARCHAR2,
1121 p_sub_pool_rec IN subsidy_pool_rec) IS
1122
1123 ---------------------------
1124 -- DECLARE Local Variables
1125 ---------------------------
1126 L_MODULE CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_PVT.VALIDATE_SUB_POOL';
1127 L_DEBUG_ENABLED VARCHAR2(10);
1128 L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
1129 IS_DEBUG_PROCEDURE_ON BOOLEAN;
1130 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_SUBSIDY_POOL';
1131 l_api_version NUMBER ;
1132 l_return_status VARCHAR2(1) ;
1133 l_sub_pool_rec subsidy_pool_rec ;
1134
1135 BEGIN
1136 L_DEBUG_ENABLED := Okl_Debug_Pub.CHECK_LOG_ENABLED;
1137 IF(L_DEBUG_ENABLED='Y') THEN
1138 L_LEVEL_PROCEDURE :=Fnd_Log.LEVEL_PROCEDURE;
1139 IS_DEBUG_PROCEDURE_ON := Okl_Debug_Pub.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
1140 END IF;
1141 IF(IS_DEBUG_PROCEDURE_ON) THEN
1142 BEGIN
1143 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSIPB.pls call okl_sip_pvt.validate_row ');
1144 END;
1145 END IF;
1146 l_api_version := 1.0;
1147 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1148
1149 -- call START_ACTIVITY to create savepoint, check compatibility
1150 -- and initialize message list
1151
1152 l_return_status := OKL_API.START_ACTIVITY(
1153 p_api_name => l_api_name,
1154 p_pkg_name => g_pkg_name,
1155 p_init_msg_list => p_init_msg_list,
1156 l_api_version => l_api_version,
1157 p_api_version => p_api_version,
1158 p_api_type => g_api_type,
1159 x_return_status => x_return_status);
1160
1161 -- check if activity started successfully
1162 If (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1163 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1164 Elsif (l_return_status = OKL_API.G_RET_STS_ERROR) then
1165 raise OKL_API.G_EXCEPTION_ERROR;
1166 End If;
1167
1168 l_sub_pool_rec := p_sub_pool_rec;
1169
1170 -- TAPI call to validate the records.
1171 okl_sip_pvt.validate_row(
1172 p_api_version => p_api_version,
1173 p_init_msg_list => p_init_msg_list,
1174 x_return_status => x_return_status,
1175 x_msg_count => x_msg_count,
1176 x_msg_data => x_msg_data,
1177 p_sipv_rec => l_sub_pool_rec);
1178
1179 -- check return status
1180 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
1181 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1182 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
1183 raise OKL_API.G_EXCEPTION_ERROR;
1184 End If;
1185
1186 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1187 x_msg_data => x_msg_data);
1188
1189 IF(IS_DEBUG_PROCEDURE_ON) THEN
1190 BEGIN
1191 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSIPB.pls call okl_sip_pvt.validate_row ');
1192 END;
1193 END IF;
1194
1195 EXCEPTION
1196 when OKL_API.G_EXCEPTION_ERROR then
1197 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1198 p_api_name => l_api_name,
1199 p_pkg_name => g_pkg_name,
1200 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1201 x_msg_count => x_msg_count,
1202 x_msg_data => x_msg_data,
1203 p_api_type => g_api_type);
1204
1205 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1206 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1207 p_api_name => l_api_name,
1208 p_pkg_name => g_pkg_name,
1209 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1210 x_msg_count => x_msg_count,
1211 x_msg_data => x_msg_data,
1212 p_api_type => g_api_type);
1213
1214 when OTHERS then
1215 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1216 p_api_name => l_api_name,
1217 p_pkg_name => g_pkg_name,
1218 p_exc_name => 'OTHERS',
1219 x_msg_count => x_msg_count,
1220 x_msg_data => x_msg_data,
1221 p_api_type => g_api_type);
1222 END validate_sub_pool;
1223
1224 END OKL_SUBSIDY_POOL_PVT;