[Home] [Help]
PACKAGE BODY: APPS.OKL_FUNDING_CHECKLIST_PVT
Source
1 PACKAGE BODY OKL_FUNDING_CHECKLIST_PVT AS
2 /* $Header: OKLRCLFB.pls 120.9 2007/03/06 09:44:44 nikshah ship $ */
3 ----------------------------------------------------------------------------
4 -- Data Structures
5 ----------------------------------------------------------------------------
6 subtype tapv_rec_type is okl_tap_pvt.tapv_rec_type;
7 subtype tapv_tbl_type is okl_tap_pvt.tapv_tbl_type;
8 subtype tplv_rec_type is okl_tpl_pvt.tplv_rec_type;
9 subtype tplv_tbl_type is okl_tpl_pvt.tplv_tbl_type;
10
11 ----------------------------------------------------------------------------
12 -- Global Message Constants
13 ----------------------------------------------------------------------------
14 G_STS_CODE constant VARCHAR2(10) := 'NEW';
15 G_APPROVE constant VARCHAR2(30) := 'APPROVE';
16
17 G_FUNDING_CHKLST_TPL CONSTANT VARCHAR2(30) := 'LAFCLH';
18 -- G_FUNDING_CHKLST_TPL_RULE1 CONSTANT VARCHAR2(30) := 'LAFCLT';
19 G_FUNDING_CHKLST_TPL_RULE1 CONSTANT VARCHAR2(30) := 'LAFCLD';
20 G_RGP_TYPE CONSTANT VARCHAR2(30) := 'KRG';
21
22 G_INSERT_MODE VARCHAR2(10) := 'INSERT';
23 G_UPDATE_MODE VARCHAR2(10) := 'UPDATE';
24 G_DELETE_MODE VARCHAR2(10) := 'DELETE';
25
26 ----------------------------------------------------------------------------
27 -- Procedures and Functions
28 ----------------------------------------------------------------------------
29 --start: May-24-2005 cklee okl.h Lease Application ER for Authoring
30 --------------------------------------------------------------------------
31 ----- Validate Function Id
32 --------------------------------------------------------------------------
33 FUNCTION validate_function_id(
34 p_rulv_rec rulv_rec_type
35 ) RETURN VARCHAR2
36 IS
37 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
38 l_dummy number;
39
40 l_row_not_found boolean := false;
41 l_function_id varchar2(100);
42
43 CURSOR c_fun (p_id number)
44 IS
45 SELECT 1
46 FROM OKL_DATA_SRC_FNCTNS_V fun
47 WHERE fun.id = p_id
48 ;
49
50 BEGIN
51
52 l_function_id := p_rulv_rec.RULE_INFORMATION9;
53 -- FK check
54 -- check only if object exists
55 IF (l_function_id IS NOT NULL AND
56 l_function_id <> OKL_API.G_MISS_CHAR)
57 THEN
58
59 OPEN c_fun(l_function_id);
60 FETCH c_fun INTO l_dummy;
61 l_row_not_found := c_fun%NOTFOUND;
62 CLOSE c_fun;
63
64 IF (l_row_not_found) THEN
65 OKL_API.Set_Message(p_app_name => G_APP_NAME,
66 p_msg_name => G_INVALID_VALUE,
67 p_token1 => G_COL_NAME_TOKEN,
68 p_token1_value => 'FUNCTION_ID');
69
70 RAISE G_EXCEPTION_HALT_VALIDATION;
71 END IF;
72 END IF;
73
74 RETURN l_return_status;
75
76 EXCEPTION
77 WHEN G_EXCEPTION_HALT_VALIDATION THEN
78 l_return_status := OKL_API.G_RET_STS_ERROR;
79 RETURN l_return_status;
80 WHEN OTHERS THEN
81 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
82 OKL_API.Set_Message(p_app_name => G_APP_NAME,
83 p_msg_name => G_UNEXPECTED_ERROR,
84 p_token1 => G_SQLCODE_TOKEN,
85 p_token1_value => SQLCODE,
86 p_token2 => G_SQLERRM_TOKEN,
87 p_token2_value => SQLERRM);
88 RETURN l_return_status;
89 END;
90 --------------------------------------------------------------------------
91 ----- Validate Checklist Type
92 --------------------------------------------------------------------------
93 FUNCTION validate_checklist_type(
94 p_rulv_rec rulv_rec_type,
95 p_mode varchar2
96 ) RETURN VARCHAR2
97 IS
98 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
99 l_dummy number;
100
101 l_row_not_found boolean := false;
102 l_checklist_type varchar2(30);
103
104 CURSOR c_lok (p_lookup_code varchar2)
105 IS
106 SELECT 1
107 FROM fnd_lookups lok
108 WHERE lookup_type = 'OKL_CHECKLIST_TYPE'
109 and lok.enabled_flag = 'Y'
110 and lok.lookup_code = p_lookup_code
111 ;
112
113 BEGIN
114
115 l_checklist_type := p_rulv_rec.RULE_INFORMATION10;
116
117 IF (p_mode = G_INSERT_MODE) THEN
118
119 -- column is required:
120 IF (l_checklist_type IS NULL) OR
121 (l_checklist_type = OKL_API.G_MISS_CHAR)
122 THEN
123 OKL_API.Set_Message(p_app_name => G_APP_NAME,
124 p_msg_name => G_REQUIRED_VALUE,
125 p_token1 => G_COL_NAME_TOKEN,
126 p_token1_value => 'CHECKLIST_TYPE');
127 RAISE G_EXCEPTION_HALT_VALIDATION;
128 END IF;
129
130 END IF;
131
132 -- FK check
133 -- check only if object exists
134 IF (l_checklist_type IS NOT NULL AND
135 l_checklist_type <> OKL_API.G_MISS_CHAR)
136 THEN
137
138 OPEN c_lok(l_checklist_type);
139 FETCH c_lok INTO l_dummy;
140 l_row_not_found := c_lok%NOTFOUND;
141 CLOSE c_lok;
142
143 IF (l_row_not_found) THEN
144 OKL_API.Set_Message(p_app_name => G_APP_NAME,
145 p_msg_name => G_INVALID_VALUE,
146 p_token1 => G_COL_NAME_TOKEN,
147 p_token1_value => 'CHECKLIST_TYPE');
148
149 RAISE G_EXCEPTION_HALT_VALIDATION;
150 END IF;
151 END IF;
152
153 RETURN l_return_status;
154
155 EXCEPTION
156 WHEN G_EXCEPTION_HALT_VALIDATION THEN
157 l_return_status := OKL_API.G_RET_STS_ERROR;
158 RETURN l_return_status;
159 WHEN OTHERS THEN
160 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
161 OKL_API.Set_Message(p_app_name => G_APP_NAME,
162 p_msg_name => G_UNEXPECTED_ERROR,
163 p_token1 => G_SQLCODE_TOKEN,
164 p_token1_value => SQLCODE,
165 p_token2 => G_SQLERRM_TOKEN,
166 p_token2_value => SQLERRM);
167 RETURN l_return_status;
168 END;
169 --------------------------------------------------------------------------
170 ----- Validate to-do item
171 --------------------------------------------------------------------------
172 FUNCTION validate_todo_item(
173 p_rulv_rec rulv_rec_type,
174 p_mode varchar2
175 ) RETURN VARCHAR2
176 IS
177 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
178 l_dummy number;
179
180 l_row_not_found boolean := false;
181 l_todo_item varchar2(30);
182
183 CURSOR c_lok (p_lookup_code varchar2)
184 IS
185 SELECT 1
186 FROM fnd_lookups lok
187 WHERE lookup_type = 'OKL_TODO_ITEMS'
188 and lok.enabled_flag = 'Y'
189 and lok.lookup_code = p_lookup_code
190 ;
191
192 BEGIN
193
194 IF (p_mode = G_INSERT_MODE) THEN
195
196 -- column is required:
197 IF (p_rulv_rec.RULE_INFORMATION1 IS NULL) OR
198 (p_rulv_rec.RULE_INFORMATION1 = OKL_API.G_MISS_CHAR)
199 THEN
200 OKL_API.Set_Message(p_app_name => G_APP_NAME,
201 p_msg_name => G_REQUIRED_VALUE,
202 p_token1 => G_COL_NAME_TOKEN,
203 p_token1_value => 'TODO_ITEM');
204 RAISE G_EXCEPTION_HALT_VALIDATION;
205 END IF;
206
207 END IF;
208
209 -- FK check
210 -- check only if object exists
211 IF (p_rulv_rec.RULE_INFORMATION1 IS NOT NULL AND
212 p_rulv_rec.RULE_INFORMATION1 <> OKL_API.G_MISS_CHAR)
213 THEN
214
215 OPEN c_lok(p_rulv_rec.RULE_INFORMATION1);
216 FETCH c_lok INTO l_dummy;
217 l_row_not_found := c_lok%NOTFOUND;
218 CLOSE c_lok;
219
220 IF (l_row_not_found) THEN
221 OKL_API.Set_Message(p_app_name => G_APP_NAME,
222 p_msg_name => G_INVALID_VALUE,
223 p_token1 => G_COL_NAME_TOKEN,
224 p_token1_value => 'TODO_ITEM');
225
226 RAISE G_EXCEPTION_HALT_VALIDATION;
227 END IF;
228 END IF;
229
230 RETURN l_return_status;
231
232 EXCEPTION
233 WHEN G_EXCEPTION_HALT_VALIDATION THEN
234 l_return_status := OKL_API.G_RET_STS_ERROR;
235 RETURN l_return_status;
236 WHEN OTHERS THEN
237 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
238 OKL_API.Set_Message(p_app_name => G_APP_NAME,
239 p_msg_name => G_UNEXPECTED_ERROR,
240 p_token1 => G_SQLCODE_TOKEN,
241 p_token1_value => SQLCODE,
242 p_token2 => G_SQLERRM_TOKEN,
243 p_token2_value => SQLERRM);
244 RETURN l_return_status;
245 END;
246 --------------------------------------------------------------------------
247 ----- Validate duplicated to-do item, function_id
248 --------------------------------------------------------------------------
249 FUNCTION validate_dup_item(
250 p_rulv_rec rulv_rec_type
251 ) RETURN VARCHAR2
252 IS
253 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
254 l_count number := 0;
255 l_row_found boolean := false;
256 l_dummy number;
257 l_item varchar2(30);
258 l_function_id varchar2(100);
259 p_freq_id number;
260
261
262 CURSOR c_unq_fun (p_freq_id number)
263 IS
264 SELECT 1
265 FROM okl_funding_checklists_uv lst
266 WHERE lst.FUND_REQ_ID = p_freq_id
267 group by lst.todo_item_code, lst.function_id
268 having count(1) > 1
269 ;
270
271 BEGIN
272
273 -- Fix bug when invoked by okl_funding_pvt.update_checklist_function
274 IF p_rulv_rec.object1_id1 is not null AND p_rulv_rec.object1_id1 <> OKL_API.G_MISS_CHAR THEN
275 p_freq_id := p_rulv_rec.object1_id1;
276
277 OPEN c_unq_fun(p_freq_id);
278 FETCH c_unq_fun INTO l_dummy;
279 l_row_found := c_unq_fun%FOUND;
280 CLOSE c_unq_fun;
281
282 IF (l_row_found) THEN
283 OKL_API.Set_Message(p_app_name => G_APP_NAME,
284 p_msg_name => G_NOT_UNIQUE,
285 p_token1 => G_COL_NAME_TOKEN,
286 p_token1_value => 'The combinations of the Checklist Item and the Function');
287
288 RAISE G_EXCEPTION_HALT_VALIDATION;
289 END IF;
290 END IF;
291
292 RETURN l_return_status;
293
294 EXCEPTION
295 WHEN G_EXCEPTION_HALT_VALIDATION THEN
296 l_return_status := OKL_API.G_RET_STS_ERROR;
297 RETURN l_return_status;
298 WHEN OTHERS THEN
299 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
300 OKL_API.Set_Message(p_app_name => G_APP_NAME,
301 p_msg_name => G_UNEXPECTED_ERROR,
302 p_token1 => G_SQLCODE_TOKEN,
303 p_token1_value => SQLCODE,
304 p_token2 => G_SQLERRM_TOKEN,
305 p_token2_value => SQLERRM);
306 RETURN l_return_status;
307 END;
308
309 --------------------------------------------------------------------------
310 FUNCTION validate_header_attributes(
311 p_rulv_rec rulv_rec_type,
312 p_mode varchar2
313 ) RETURN VARCHAR2
314 IS
315 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
316 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
317
318 BEGIN
319
320 -- Do formal attribute validation:
321 l_return_status := validate_function_id(p_rulv_rec => p_rulv_rec);
322 --- Store the highest degree of error
323 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
324 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
325 x_return_status := l_return_status;
326 END IF;
327 RAISE G_EXCEPTION_HALT_VALIDATION;
328 END IF;
329
330 l_return_status := validate_checklist_type(p_rulv_rec => p_rulv_rec,
331 p_mode => p_mode);
332 --- Store the highest degree of error
333 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
334 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
335 x_return_status := l_return_status;
336 END IF;
337 RAISE G_EXCEPTION_HALT_VALIDATION;
338 END IF;
339
340 l_return_status := validate_todo_item(p_rulv_rec => p_rulv_rec,
341 p_mode => p_mode);
342 --- Store the highest degree of error
343 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
344 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
345 x_return_status := l_return_status;
346 END IF;
347 RAISE G_EXCEPTION_HALT_VALIDATION;
348 END IF;
349
350 RETURN x_return_status;
351 EXCEPTION
352 WHEN G_EXCEPTION_HALT_VALIDATION THEN
353 RETURN x_return_status;
354 WHEN OTHERS THEN
355 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
356 OKL_API.Set_Message(p_app_name => G_APP_NAME,
357 p_msg_name => G_UNEXPECTED_ERROR,
358 p_token1 => G_SQLCODE_TOKEN,
359 p_token1_value => SQLCODE,
360 p_token2 => G_SQLERRM_TOKEN,
361 p_token2_value => SQLERRM);
362 RETURN l_return_status;
363 END validate_header_attributes;
364 -----------------------------------------------------------------------------
365 --- validate attrs after image-----------------------------------------------
366 -----------------------------------------------------------------------------
367 FUNCTION validate_hdr_attr_aftimg(
368 p_rulv_rec rulv_rec_type
369 ) RETURN VARCHAR2
370 IS
371 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
372 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
373
374 BEGIN
375
376 l_return_status := validate_dup_item(p_rulv_rec => p_rulv_rec);
377 --- Store the highest degree of error
378 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
379 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
380 x_return_status := l_return_status;
381 END IF;
382 RAISE G_EXCEPTION_HALT_VALIDATION;
383 END IF;
384
385 RETURN x_return_status;
386 EXCEPTION
387 WHEN G_EXCEPTION_HALT_VALIDATION THEN
388 RETURN x_return_status;
389 WHEN OTHERS THEN
390 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
391 OKL_API.Set_Message(p_app_name => G_APP_NAME,
392 p_msg_name => G_UNEXPECTED_ERROR,
393 p_token1 => G_SQLCODE_TOKEN,
394 p_token1_value => SQLCODE,
395 p_token2 => G_SQLERRM_TOKEN,
396 p_token2_value => SQLERRM);
397 RETURN l_return_status;
398 END validate_hdr_attr_aftimg;
399
400 ----------------------------------------------------------------------------------
401 -- Start of comments
402 --
403 -- Procedure Name : copy_rulv_rec
404 -- Description :
405 -- Business Rules :
406 -- Parameters :
407 -- Version : 1.0
408 -- End of comments
409 ----------------------------------------------------------------------------------
410 PROCEDURE copy_rulv_rec(
411 p_api_version IN NUMBER
412 ,p_init_msg_list IN VARCHAR2
413 ,x_return_status OUT NOCOPY VARCHAR2
414 ,x_msg_count OUT NOCOPY NUMBER
415 ,x_msg_data OUT NOCOPY VARCHAR2
416 ,p_rulv_rec IN rulv_rec_type
417 ,p_rulv_migr_rec OUT NOCOPY okl_rule_pub.rulv_rec_type
418 ,p_mode IN varchar2
419 )
420 is
421 l_api_name CONSTANT VARCHAR2(30) := 'copy_rulv_rec';
422 l_api_version CONSTANT NUMBER := 1.0;
423 i NUMBER;
424 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
425 l_rgp_id number;
426 l_object1_id1 varchar2(100);
427
428 cursor c_rgp_id (chr_id number)
429 is
430 select id
431 from okc_rule_groups_b
432 where rgd_code = G_FUNDING_CHKLST_TPL--'LAFCLH'
433 and dnz_chr_id = chr_id;
434
435 begin
436 -- Set API savepoint
437 SAVEPOINT copy_rulv_rec;
438
439 -- Check for call compatibility
440 IF (NOT FND_API.Compatible_API_Call (l_api_version,
441 p_api_version,
442 l_api_name,
443 G_PKG_NAME ))
444 THEN
445 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
446 END IF;
447
448 -- Initialize message list if requested
449 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
450 FND_MSG_PUB.initialize;
451 END IF;
452
453 -- Initialize API status to success
454 x_return_status := OKL_API.G_RET_STS_SUCCESS;
455
456
457 /*** Begin API body ****************************************************/
458
459 --
460 -- validate
461 --
462
463 l_return_status := validate_header_attributes(p_rulv_rec => p_rulv_rec,
464 p_mode => p_mode);
465 --- Store the highest degree of error
466 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
467 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
468 x_return_status := l_return_status;
469 END IF;
470 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
471 END IF;
472
473 p_rulv_migr_rec.id := p_rulv_rec.id;
474
475 p_rulv_migr_rec.rule_information1 := p_rulv_rec.rule_information1;
476 p_rulv_migr_rec.rule_information2 := p_rulv_rec.rule_information2;
477 p_rulv_migr_rec.rule_information3 := p_rulv_rec.rule_information3;
478
479 p_rulv_migr_rec.rule_information4 := p_rulv_rec.rule_information4;
480 p_rulv_migr_rec.rule_information5 := p_rulv_rec.rule_information5;
481 p_rulv_migr_rec.rule_information6 := p_rulv_rec.rule_information6;
482
483 p_rulv_migr_rec.rule_information7 := p_rulv_rec.rule_information7;
484
485 p_rulv_migr_rec.rule_information8 := p_rulv_rec.rule_information8;
486 p_rulv_migr_rec.rule_information9 := p_rulv_rec.rule_information9;
487 p_rulv_migr_rec.rule_information10 := p_rulv_rec.rule_information10;
488
489 IF p_mode = G_INSERT_MODE THEN
490 OPEN c_rgp_id(p_rulv_rec.DNZ_CHR_ID);
491 FETCH c_rgp_id into l_rgp_id;
492 CLOSE c_rgp_id;
493
494 p_rulv_migr_rec.RGP_ID := l_rgp_id;
495 p_rulv_migr_rec.DNZ_CHR_ID := p_rulv_rec.DNZ_CHR_ID;
496 p_rulv_migr_rec.OBJECT1_ID1 := p_rulv_rec.object1_id1;
497 p_rulv_migr_rec.OBJECT1_ID2 := '#';
498 p_rulv_migr_rec.RULE_INFORMATION_CATEGORY := G_FUNDING_CHKLST_TPL_RULE1;-- 'LAFCLD'
499
500 p_rulv_migr_rec.STD_TEMPLATE_YN := 'N';
501 p_rulv_migr_rec.WARN_YN := 'N';
502
503 END IF;
504 --end:cklee May-10-2005 cklee okl.h Lease Application ER for Authoring
505
506
507 /*** End API body ******************************************************/
508
509 -- Get message count and if count is 1, get message info
510 FND_MSG_PUB.Count_And_Get
511 (p_count => x_msg_count,
512 p_data => x_msg_data);
513
514 EXCEPTION
515 WHEN OKL_API.G_EXCEPTION_ERROR THEN
516 ROLLBACK TO copy_rulv_rec;
517 x_return_status := OKL_API.G_RET_STS_ERROR;
518 FND_MSG_PUB.Count_And_Get
519 (p_count => x_msg_count,
520 p_data => x_msg_data);
521
522 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
523 ROLLBACK TO copy_rulv_rec;
524 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
525 FND_MSG_PUB.Count_And_Get
526 (p_count => x_msg_count,
527 p_data => x_msg_data);
528
529 WHEN OTHERS THEN
530 ROLLBACK TO copy_rulv_rec;
531 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
532 OKL_API.Set_Message(p_app_name => G_APP_NAME,
533 p_msg_name => G_UNEXPECTED_ERROR,
534 p_token1 => G_SQLCODE_TOKEN,
535 p_token1_value => SQLCODE,
536 p_token2 => G_SQLERRM_TOKEN,
537 p_token2_value => SQLERRM);
538 FND_MSG_PUB.Count_And_Get
539 (p_count => x_msg_count,
540 p_data => x_msg_data);
541 end;
542 ----------------------------------------------------------------------------------
543 -- Start of comments
544 --
545 -- Procedure Name : copy_rulv_tbl
546 -- Description :
547 -- Business Rules :
548 -- Parameters :
549 -- Version : 1.0
550 -- End of comments
551 ----------------------------------------------------------------------------------
552 PROCEDURE copy_rulv_tbl(
553 p_api_version IN NUMBER
554 ,p_init_msg_list IN VARCHAR2
555 ,x_return_status OUT NOCOPY VARCHAR2
556 ,x_msg_count OUT NOCOPY NUMBER
557 ,x_msg_data OUT NOCOPY VARCHAR2
558 ,p_rulv_tbl IN rulv_tbl_type
559 ,p_rulv_migr_tbl OUT NOCOPY okl_rule_pub.rulv_tbl_type
560 ,p_mode IN varchar2)
561 is
562 l_api_name CONSTANT VARCHAR2(30) := 'copy_rulv_tbl';
563 l_api_version CONSTANT NUMBER := 1.0;
564 i NUMBER;
565 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
566
567 begin
568 -- Set API savepoint
569 SAVEPOINT copy_rulv_tbl;
570
571 -- Check for call compatibility
572 IF (NOT FND_API.Compatible_API_Call (l_api_version,
573 p_api_version,
574 l_api_name,
575 G_PKG_NAME ))
576 THEN
577 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
578 END IF;
579
580 -- Initialize message list if requested
581 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
582 FND_MSG_PUB.initialize;
583 END IF;
584
585 -- Initialize API status to success
586 x_return_status := OKL_API.G_RET_STS_SUCCESS;
587
588 /*** Begin API body ****************************************************/
589
590 IF (p_rulv_tbl.COUNT > 0) THEN
591 i := p_rulv_tbl.FIRST;
592 LOOP
593
594 copy_rulv_rec(
595 p_api_version => p_api_version,
596 p_init_msg_list => p_init_msg_list,
597 x_return_status => x_return_status,
598 x_msg_count => x_msg_count,
599 x_msg_data => x_msg_data,
600 p_rulv_rec => p_rulv_tbl(i),
601 p_rulv_migr_rec => p_rulv_migr_tbl(i),
602 p_mode => p_mode
603 );
604
605 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
606 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
607 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
608 raise OKC_API.G_EXCEPTION_ERROR;
609 End If;
610
611 EXIT WHEN (i = p_rulv_tbl.LAST);
612 i := p_rulv_tbl.NEXT(i);
613 END LOOP;
614 END IF;
615
616 /*** End API body ******************************************************/
617
618 -- Get message count and if count is 1, get message info
619 FND_MSG_PUB.Count_And_Get
620 (p_count => x_msg_count,
621 p_data => x_msg_data);
622
623 EXCEPTION
624 WHEN OKL_API.G_EXCEPTION_ERROR THEN
625 ROLLBACK TO copy_rulv_tbl;
626 x_return_status := OKL_API.G_RET_STS_ERROR;
627 FND_MSG_PUB.Count_And_Get
628 (p_count => x_msg_count,
629 p_data => x_msg_data);
630
631 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
632 ROLLBACK TO copy_rulv_tbl;
633 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
634 FND_MSG_PUB.Count_And_Get
635 (p_count => x_msg_count,
636 p_data => x_msg_data);
637
638 WHEN OTHERS THEN
639 ROLLBACK TO copy_rulv_tbl;
640 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
641 OKL_API.Set_Message(p_app_name => G_APP_NAME,
642 p_msg_name => G_UNEXPECTED_ERROR,
643 p_token1 => G_SQLCODE_TOKEN,
644 p_token1_value => SQLCODE,
645 p_token2 => G_SQLERRM_TOKEN,
646 p_token2_value => SQLERRM);
647 FND_MSG_PUB.Count_And_Get
648 (p_count => x_msg_count,
649 p_data => x_msg_data);
650 end;
651
652 --end: May-24-2005 cklee okl.h Lease Application ER for Authoring
653 /*
654 ----------------------------------------------------------------------------------
655 -- Start of comments
656 --
657 -- Procedure Name : copy_rulv_rec
658 -- Description :
659 -- Business Rules :
660 -- Parameters :
661 -- Version : 1.0
662 -- End of comments
663 ----------------------------------------------------------------------------------
664 PROCEDURE copy_rulv_rec(
665 p_rulv_rec IN rulv_rec_type,
666 p_rulv_migr_rec OUT NOCOPY okl_rule_pub.rulv_rec_type)
667 is
668 begin
669
670 p_rulv_migr_rec.id := p_rulv_rec.id;
671
672 -- start : 28-Feb-05 cklee -- fixed bug#4056212
673 IF (p_rulv_rec.rule_information2 = 'Y' or p_rulv_rec.rule_information2 = 'N') THEN
674 p_rulv_migr_rec.rule_information2 := p_rulv_rec.rule_information2;
675 ELSE
676 p_rulv_migr_rec.rule_information2 := 'N';
677 END IF;
678
679 IF (p_rulv_rec.rule_information3 = 'Y' or p_rulv_rec.rule_information3 = 'N') THEN
680 p_rulv_migr_rec.rule_information3 := p_rulv_rec.rule_information3;
681 ELSE
682 p_rulv_migr_rec.rule_information3 := 'N';
683 END IF;
684 -- end : 28-Feb-05 cklee -- fixed bug#4056212
685
686 p_rulv_migr_rec.rule_information4 := p_rulv_rec.rule_information4;
687 p_rulv_migr_rec.rule_information5 := p_rulv_rec.rule_information5;
688 p_rulv_migr_rec.rule_information6 := p_rulv_rec.rule_information6;
689 p_rulv_migr_rec.rule_information7 := p_rulv_rec.rule_information7;
690 p_rulv_migr_rec.rule_information8 := p_rulv_rec.rule_information8;
691 p_rulv_migr_rec.rule_information9 := p_rulv_rec.rule_information9;
692 p_rulv_migr_rec.rule_information10 := p_rulv_rec.rule_information10;
693
694
695 end;
696 ----------------------------------------------------------------------------------
697 -- Start of comments
698 --
699 -- Procedure Name : copy_rulv_tbl
700 -- Description :
701 -- Business Rules :
702 -- Parameters :
703 -- Version : 1.0
704 -- End of comments
705 ----------------------------------------------------------------------------------
706 PROCEDURE copy_rulv_tbl(
707 p_rulv_tbl IN rulv_tbl_type,
708 p_rulv_migr_tbl OUT NOCOPY okl_rule_pub.rulv_tbl_type)
709 is
710 i number;
711 begin
712
713 IF (p_rulv_tbl.COUNT > 0) THEN
714 i := p_rulv_tbl.FIRST;
715 LOOP
716
717 copy_rulv_rec(
718 p_rulv_rec => p_rulv_tbl(i),
719 p_rulv_migr_rec => p_rulv_migr_tbl(i)
720 );
721
722 EXIT WHEN (i = p_rulv_tbl.LAST);
723 i := p_rulv_tbl.NEXT(i);
724 END LOOP;
725 END IF;
726
727 end;
728 */
729 ----------------------------------------------------------------------------------
730 -- Start of comments
731 --
732 -- Procedure Name : create_funding_chklst
733 -- Description : wrapper api for create funding checklists associated
734 -- with credit line contract ID
735 -- Business Rules :
736 -- Parameters :
737 -- Version : 1.0
738 -- End of comments
739 ----------------------------------------------------------------------------------
740 PROCEDURE create_funding_chklst(
741 p_api_version IN NUMBER
742 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
743 ,x_return_status OUT NOCOPY VARCHAR2
744 ,x_msg_count OUT NOCOPY NUMBER
745 ,x_msg_data OUT NOCOPY VARCHAR2
746 ,p_rulv_tbl IN rulv_tbl_type
747 ,x_rulv_tbl OUT NOCOPY rulv_tbl_type
748 )
749 is
750 l_api_name CONSTANT VARCHAR2(30) := 'create_funding_chklst';
751 l_api_version CONSTANT NUMBER := 1.0;
752 i NUMBER;
753 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
754 -- lp_rulv_tbl rulv_tbl_type := p_rulv_tbl;
755 -- xp_rulv_tbl rulv_tbl_type := x_rulv_tbl;
756 lp_rulv_tbl okl_rule_pub.rulv_tbl_type;
757 lx_rulv_tbl okl_rule_pub.rulv_tbl_type;
758
759 --START: Fixed bug 5912358 by nikshah, 06-MAR-2007
760 l_lease_app_found boolean := FALSE;
761 l_lease_app_list_found boolean := FALSE;
762 l_lease_app_found_str VARCHAR2(10);
763 l_lease_app_list_found_str VARCHAR2(10);
764 l_funding_checklist_tpl okc_rules_b.rule_information2%TYPE;
765 l_chr_id okc_k_headers_b.id%type := OKC_API.G_MISS_NUM;
766 l_rgpv_id okc_rule_groups_b.id%type;
767 l_grp_row_not_found boolean;
768 lp_rgpv_rec okl_okc_migration_pvt.rgpv_rec_type;
769 lx_rgpv_rec okl_okc_migration_pvt.rgpv_rec_type;
770 x_lease_app_id number;
771 x_credit_id number;
772 ---------------------------------------------------------------------------------------------------
773 -- Funded contract group
774 ---------------------------------------------------------------------------------------------------
775 cursor c_grp (p_chr_id number) is
776 select rgp.id
777 from okc_rule_groups_b rgp
778 where rgp.dnz_chr_id = p_chr_id
779 and rgp.RGD_CODE = G_FUNDING_CHKLST_TPL
780 ;
781 --END: Fixed bug 5912358 by nikshah, 06-MAR-2007
782
783 begin
784 -- Set API savepoint
785 SAVEPOINT create_funding_chklst;
786
787 -- Check for call compatibility
788 IF (NOT FND_API.Compatible_API_Call (l_api_version,
789 p_api_version,
790 l_api_name,
791 G_PKG_NAME ))
792 THEN
793 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
794 END IF;
795
796 -- Initialize message list if requested
797 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
798 FND_MSG_PUB.initialize;
799 END IF;
800
801 -- Initialize API status to success
802 x_return_status := OKL_API.G_RET_STS_SUCCESS;
803
804
805 /*** Begin API body ****************************************************/
806 --DBMS_OUTPUT.PUT_LINE('before copy_rulv_tbl');
807
808 --START: Fixed Bug 5912358 by nikshah, 06-MAR-2007
809 IF (p_rulv_tbl.COUNT > 0)THEN
810 l_chr_id := p_rulv_tbl(p_rulv_tbl.FIRST).dnz_chr_id;
811 END IF;
812 OKL_FUNDING_PVT.get_checklist_source(
813 p_api_version => p_api_version,
814 p_init_msg_list => p_init_msg_list,
815 x_return_status => x_return_status,
816 x_msg_count => x_msg_count,
817 x_msg_data => x_msg_data,
818 p_chr_id => l_chr_id,
819 x_lease_app_found => l_lease_app_found_str,
820 x_lease_app_list_found => l_lease_app_list_found_str,
821 x_funding_checklist_tpl => l_funding_checklist_tpl,
822 x_lease_app_id => x_lease_app_id,
823 x_credit_id => x_credit_id);
824
825 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
826 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
827 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
828 raise OKC_API.G_EXCEPTION_ERROR;
829 End If;
830
831 IF (l_lease_app_found_str = 'TRUE') THEN
832 l_lease_app_found := TRUE;
833 END IF;
834 IF (l_lease_app_list_found_str = 'TRUE') THEN
835 l_lease_app_list_found := TRUE;
836 END IF;
837 IF ( (NOT l_lease_app_found AND l_funding_checklist_tpl IS NOT NULL) or
838 (l_lease_app_found AND l_lease_app_list_found)
839 ) THEN
840
841 open c_grp(l_chr_id);
842 fetch c_grp into l_rgpv_id;
843
844 l_grp_row_not_found := c_grp%NOTFOUND;
845 close c_grp;
846
847 IF (l_grp_row_not_found) THEN
848
849 lp_rgpv_rec.DNZ_CHR_ID := l_chr_id;
850 lp_rgpv_rec.CHR_ID := l_chr_id;
851 lp_rgpv_rec.RGD_CODE := G_FUNDING_CHKLST_TPL;
852 lp_rgpv_rec.RGP_TYPE := G_RGP_TYPE;
853
854 okl_rule_pub.create_rule_group(
855 p_api_version => p_api_version,
856 p_init_msg_list => p_init_msg_list,
857 x_return_status => x_return_status,
858 x_msg_count => x_msg_count,
859 x_msg_data => x_msg_data,
860 p_rgpv_rec => lp_rgpv_rec,
861 x_rgpv_rec => lx_rgpv_rec);
862
863 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
864 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
865 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
866 raise OKC_API.G_EXCEPTION_ERROR;
867 End If;
868
869 l_rgpv_id := lx_rgpv_rec.id;
870 END IF;
871
872 END IF;
873
874 --END: Fixed Bug 5912358 by nikshah, 06-MAR-2007
875
876 copy_rulv_tbl(
877 p_api_version => p_api_version,
878 p_init_msg_list => p_init_msg_list,
879 x_return_status => x_return_status,
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data,
882 p_rulv_tbl => p_rulv_tbl,
883 p_rulv_migr_tbl => lp_rulv_tbl,
884 p_mode => G_INSERT_MODE
885 );
886
887 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
888 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
889 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
890 raise OKC_API.G_EXCEPTION_ERROR;
891 End If;
892
893 --DBMS_OUTPUT.PUT_LINE('after copy_rulv_tbl');
894 -- validation
895
896 okl_rule_pub.create_rule(
897 p_api_version => p_api_version,
898 p_init_msg_list => p_init_msg_list,
899 x_return_status => x_return_status,
900 x_msg_count => x_msg_count,
901 x_msg_data => x_msg_data,
902 p_rulv_tbl => lp_rulv_tbl,
903 x_rulv_tbl => lx_rulv_tbl);
904
905 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
906 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
907 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
908 raise OKC_API.G_EXCEPTION_ERROR;
909 End If;
910
911 l_return_status := validate_hdr_attr_aftimg(p_rulv_rec => p_rulv_tbl(p_rulv_tbl.FIRST));
912 --- Store the highest degree of error
913 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
914 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
915 x_return_status := l_return_status;
916 END IF;
917 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
918 END IF;
919
920 /*** End API body ******************************************************/
921
922 -- Get message count and if count is 1, get message info
923 FND_MSG_PUB.Count_And_Get
924 (p_count => x_msg_count,
925 p_data => x_msg_data);
926
927 EXCEPTION
928 WHEN OKL_API.G_EXCEPTION_ERROR THEN
929 ROLLBACK TO create_funding_chklst;
930 x_return_status := OKL_API.G_RET_STS_ERROR;
931 FND_MSG_PUB.Count_And_Get
932 (p_count => x_msg_count,
933 p_data => x_msg_data);
934
935 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
936 ROLLBACK TO create_funding_chklst;
937 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
938 FND_MSG_PUB.Count_And_Get
939 (p_count => x_msg_count,
940 p_data => x_msg_data);
941
942 WHEN OTHERS THEN
943 ROLLBACK TO create_funding_chklst;
944 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
945 OKL_API.Set_Message(p_app_name => G_APP_NAME,
946 p_msg_name => G_UNEXPECTED_ERROR,
947 p_token1 => G_SQLCODE_TOKEN,
948 p_token1_value => SQLCODE,
949 p_token2 => G_SQLERRM_TOKEN,
950 p_token2_value => SQLERRM);
951 FND_MSG_PUB.Count_And_Get
952 (p_count => x_msg_count,
953 p_data => x_msg_data);
954
955 end create_funding_chklst;
956
957 ----------------------------------------------------------------------------------
958 -- Start of comments
959 --
960 -- Procedure Name : update_funding_chklst
961 -- Description : wrapper api for update funding checklists associated
962 -- with credit line contract ID
963 -- Business Rules :
964 -- Parameters :
965 -- Version : 1.0
966 -- End of comments
967 ----------------------------------------------------------------------------------
968 PROCEDURE update_funding_chklst(
969 p_api_version IN NUMBER
970 ,p_init_msg_list IN VARCHAR2
971 ,x_return_status OUT NOCOPY VARCHAR2
972 ,x_msg_count OUT NOCOPY NUMBER
973 ,x_msg_data OUT NOCOPY VARCHAR2
974 ,p_rulv_tbl IN rulv_tbl_type
975 ,x_rulv_tbl OUT NOCOPY rulv_tbl_type
976 )
977 is
978 l_api_name CONSTANT VARCHAR2(30) := 'update_funding_chklst';
979 l_api_version CONSTANT NUMBER := 1.0;
980 i NUMBER;
981 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
982 -- lp_rulv_tbl rulv_tbl_type := p_rulv_tbl;
983 -- lx_rulv_tbl rulv_tbl_type := x_rulv_tbl;
984 lp_rulv_tbl okl_rule_pub.rulv_tbl_type;
985 lx_rulv_tbl okl_rule_pub.rulv_tbl_type;
986
987 begin
988 -- Set API savepoint
989 SAVEPOINT update_funding_chklst;
990
991 -- Check for call compatibility
992 IF (NOT FND_API.Compatible_API_Call (l_api_version,
993 p_api_version,
994 l_api_name,
995 G_PKG_NAME ))
996 THEN
997 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
998 END IF;
999
1000 -- Initialize message list if requested
1001 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
1002 FND_MSG_PUB.initialize;
1003 END IF;
1004
1005 -- Initialize API status to success
1006 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1007
1008
1009 /*** Begin API body ****************************************************/
1010 --DBMS_OUTPUT.PUT_LINE('before copy_rulv_tbl');
1011
1012 copy_rulv_tbl(
1013 p_api_version => p_api_version,
1014 p_init_msg_list => p_init_msg_list,
1015 x_return_status => x_return_status,
1016 x_msg_count => x_msg_count,
1017 x_msg_data => x_msg_data,
1018 p_rulv_tbl => p_rulv_tbl,
1019 p_rulv_migr_tbl => lp_rulv_tbl,
1020 p_mode => G_UPDATE_MODE
1021 );
1022
1023 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1024 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1025 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1026 raise OKC_API.G_EXCEPTION_ERROR;
1027 End If;
1028
1029 --DBMS_OUTPUT.PUT_LINE('after copy_rulv_tbl');
1030 -- validation
1031
1032 okl_rule_pub.update_rule(
1033 p_api_version => p_api_version,
1034 p_init_msg_list => p_init_msg_list,
1035 x_return_status => x_return_status,
1036 x_msg_count => x_msg_count,
1037 x_msg_data => x_msg_data,
1038 p_rulv_tbl => lp_rulv_tbl,
1039 x_rulv_tbl => lx_rulv_tbl);
1040
1041 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1042 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1043 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1044 raise OKC_API.G_EXCEPTION_ERROR;
1045 End If;
1046
1047 l_return_status := validate_hdr_attr_aftimg(p_rulv_rec => p_rulv_tbl(p_rulv_tbl.FIRST));
1048 --- Store the highest degree of error
1049 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1050 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1051 x_return_status := l_return_status;
1052 END IF;
1053 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1054 END IF;
1055
1056 /*** End API body ******************************************************/
1057
1058 -- Get message count and if count is 1, get message info
1059 FND_MSG_PUB.Count_And_Get
1060 (p_count => x_msg_count,
1061 p_data => x_msg_data);
1062
1063 EXCEPTION
1064 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1065 ROLLBACK TO update_funding_chklst;
1066 x_return_status := OKL_API.G_RET_STS_ERROR;
1067 FND_MSG_PUB.Count_And_Get
1068 (p_count => x_msg_count,
1069 p_data => x_msg_data);
1070
1071 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1072 ROLLBACK TO update_funding_chklst;
1073 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1074 FND_MSG_PUB.Count_And_Get
1075 (p_count => x_msg_count,
1076 p_data => x_msg_data);
1077
1078 WHEN OTHERS THEN
1079 ROLLBACK TO update_funding_chklst;
1080 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1081 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1082 p_msg_name => G_UNEXPECTED_ERROR,
1083 p_token1 => G_SQLCODE_TOKEN,
1084 p_token1_value => SQLCODE,
1085 p_token2 => G_SQLERRM_TOKEN,
1086 p_token2_value => SQLERRM);
1087 FND_MSG_PUB.Count_And_Get
1088 (p_count => x_msg_count,
1089 p_data => x_msg_data);
1090
1091 end update_funding_chklst;
1092
1093 ----------------------------------------------------------------------------------
1094 -- Start of comments
1095 --
1096 -- Procedure Name : delete_funding_chklst
1097 -- Description : wrapper api for delete funding checklists associated
1098 -- with credit line contract ID
1099 -- Business Rules :
1100 -- Parameters :
1101 -- Version : 1.0
1102 -- End of comments
1103 ----------------------------------------------------------------------------------
1104 PROCEDURE delete_funding_chklst(
1105 p_api_version IN NUMBER
1106 ,p_init_msg_list IN VARCHAR2
1107 ,x_return_status OUT NOCOPY VARCHAR2
1108 ,x_msg_count OUT NOCOPY NUMBER
1109 ,x_msg_data OUT NOCOPY VARCHAR2
1110 ,p_rulv_tbl IN rulv_tbl_type
1111 )
1112 is
1113 l_api_name CONSTANT VARCHAR2(30) := 'delete_funding_chklst';
1114 l_api_version CONSTANT NUMBER := 1.0;
1115 i NUMBER;
1116 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1117 -- lp_rulv_tbl rulv_tbl_type := p_rulv_tbl;
1118 -- xp_rulv_tbl rulv_tbl_type := x_rulv_tbl;
1119 lp_rulv_tbl okl_rule_pub.rulv_tbl_type;
1120 lx_rulv_tbl okl_rule_pub.rulv_tbl_type;
1121
1122 begin
1123 -- Set API savepoint
1124 SAVEPOINT delete_funding_chklst;
1125
1126 -- Check for call compatibility
1127 IF (NOT FND_API.Compatible_API_Call (l_api_version,
1128 p_api_version,
1129 l_api_name,
1130 G_PKG_NAME ))
1131 THEN
1132 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1133 END IF;
1134
1135 -- Initialize message list if requested
1136 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
1137 FND_MSG_PUB.initialize;
1138 END IF;
1139
1140 -- Initialize API status to success
1141 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1142
1143
1144 /*** Begin API body ****************************************************/
1145 --DBMS_OUTPUT.PUT_LINE('before copy_rulv_tbl');
1146
1147 copy_rulv_tbl(
1148 p_api_version => p_api_version,
1149 p_init_msg_list => p_init_msg_list,
1150 x_return_status => x_return_status,
1151 x_msg_count => x_msg_count,
1152 x_msg_data => x_msg_data,
1153 p_rulv_tbl => p_rulv_tbl,
1154 p_rulv_migr_tbl => lp_rulv_tbl,
1155 p_mode => G_DELETE_MODE
1156 );
1157
1158 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1159 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1160 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1161 raise OKC_API.G_EXCEPTION_ERROR;
1162 End If;
1163
1164 --DBMS_OUTPUT.PUT_LINE('after copy_rulv_tbl');
1165
1166 okl_rule_pub.delete_rule(
1167 p_api_version => p_api_version,
1168 p_init_msg_list => p_init_msg_list,
1169 x_return_status => x_return_status,
1170 x_msg_count => x_msg_count,
1171 x_msg_data => x_msg_data,
1172 p_rulv_tbl => lp_rulv_tbl);
1173
1174 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1175 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1176 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1177 raise OKC_API.G_EXCEPTION_ERROR;
1178 End If;
1179
1180 /*** End API body ******************************************************/
1181
1182 -- Get message count and if count is 1, get message info
1183 FND_MSG_PUB.Count_And_Get
1184 (p_count => x_msg_count,
1185 p_data => x_msg_data);
1186
1187 EXCEPTION
1188 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1189 ROLLBACK TO delete_funding_chklst;
1190 x_return_status := OKL_API.G_RET_STS_ERROR;
1191 FND_MSG_PUB.Count_And_Get
1192 (p_count => x_msg_count,
1193 p_data => x_msg_data);
1194
1195 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1196 ROLLBACK TO delete_funding_chklst;
1197 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1198 FND_MSG_PUB.Count_And_Get
1199 (p_count => x_msg_count,
1200 p_data => x_msg_data);
1201
1202 WHEN OTHERS THEN
1203 ROLLBACK TO delete_funding_chklst;
1204 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1205 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1206 p_msg_name => G_UNEXPECTED_ERROR,
1207 p_token1 => G_SQLCODE_TOKEN,
1208 p_token1_value => SQLCODE,
1209 p_token2 => G_SQLERRM_TOKEN,
1210 p_token2_value => SQLERRM);
1211 FND_MSG_PUB.Count_And_Get
1212 (p_count => x_msg_count,
1213 p_data => x_msg_data);
1214
1215 end delete_funding_chklst;
1216
1217 ----------------------------------------------------------------------------------
1218 -- Start of comments
1219 --
1220 -- Procedure Name : approve_funding_request
1221 -- Description : wrapper api for update_funding_header with status = 'APPROVE'
1222 -- Business Rules :
1223 -- Parameters :
1224 -- Version : 1.0
1225 -- End of comments
1226 ----------------------------------------------------------------------------------
1227 PROCEDURE approve_funding_request(
1228 p_api_version IN NUMBER
1229 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
1230 ,x_return_status OUT NOCOPY VARCHAR2
1231 ,x_msg_count OUT NOCOPY NUMBER
1232 ,x_msg_data OUT NOCOPY VARCHAR2
1233 ,p_fund_req_id IN okl_trx_ap_invoices_b.id%TYPE
1234 )
1235 is
1236 l_api_name CONSTANT VARCHAR2(30) := 'approve_funding_request';
1237 l_api_version CONSTANT NUMBER := 1.0;
1238 i NUMBER;
1239 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1240 lp_tapv_rec tapv_rec_type;
1241 lx_tapv_rec tapv_rec_type;
1242
1243 begin
1244 -- Set API savepoint
1245 SAVEPOINT approve_funding_request;
1246
1247 -- Check for call compatibility
1248 IF (NOT FND_API.Compatible_API_Call (l_api_version,
1249 p_api_version,
1250 l_api_name,
1251 G_PKG_NAME ))
1252 THEN
1253 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1254 END IF;
1255
1256 -- Initialize message list if requested
1257 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
1258 FND_MSG_PUB.initialize;
1259 END IF;
1260
1261 -- Initialize API status to success
1262 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1263
1264
1265 /*** Begin API body ****************************************************/
1266
1267 lp_tapv_rec.id := p_fund_req_id;
1268 lp_tapv_rec.TRX_STATUS_CODE := G_APPROVE;
1269 lp_tapv_rec.DATE_FUNDING_APPROVED := sysdate;
1270
1271
1272 OKL_FUNDING_PVT.update_funding_header(
1273 p_api_version => p_api_version,
1274 p_init_msg_list => p_init_msg_list,
1275 x_return_status => x_return_status,
1276 x_msg_count => x_msg_count,
1277 x_msg_data => x_msg_data,
1278 p_tapv_rec => lp_tapv_rec,
1279 x_tapv_rec => lx_tapv_rec);
1280
1281 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1282 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1283 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1284 RAISE OKL_API.G_EXCEPTION_ERROR;
1285 END IF;
1286
1287 /*** End API body ******************************************************/
1288
1289 -- Get message count and if count is 1, get message info
1290 FND_MSG_PUB.Count_And_Get
1291 (p_count => x_msg_count,
1292 p_data => x_msg_data);
1293
1294 EXCEPTION
1295 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1296 ROLLBACK TO approve_funding_request;
1297 x_return_status := OKL_API.G_RET_STS_ERROR;
1298 FND_MSG_PUB.Count_And_Get
1299 (p_count => x_msg_count,
1300 p_data => x_msg_data);
1301
1302 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1303 ROLLBACK TO approve_funding_request;
1304 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1305 FND_MSG_PUB.Count_And_Get
1306 (p_count => x_msg_count,
1307 p_data => x_msg_data);
1308
1309 WHEN OTHERS THEN
1310 ROLLBACK TO approve_funding_request;
1311 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1312 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1313 p_msg_name => G_UNEXPECTED_ERROR,
1314 p_token1 => G_SQLCODE_TOKEN,
1315 p_token1_value => SQLCODE,
1316 p_token2 => G_SQLERRM_TOKEN,
1317 p_token2_value => SQLERRM);
1318 FND_MSG_PUB.Count_And_Get
1319 (p_count => x_msg_count,
1320 p_data => x_msg_data);
1321
1322 end;
1323
1324 ----------------------------------------------------------------------------------
1325 -- Start of comments
1326 --
1327 -- Procedure Name : approve_funding_chklst
1328 -- Description : set funding line checklist sttaus to "Active".
1329 -- Business Rules :
1330 -- Parameters :
1331 -- Version : 1.0
1332 -- End of comments
1333 ----------------------------------------------------------------------------------
1334 PROCEDURE approve_funding_chklst(
1335 p_api_version IN NUMBER
1336 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
1337 ,x_return_status OUT NOCOPY VARCHAR2
1338 ,x_msg_count OUT NOCOPY NUMBER
1339 ,x_msg_data OUT NOCOPY VARCHAR2
1340 ,p_rulv_rec IN rulv_rec_type
1341 )
1342 is
1343 l_api_name CONSTANT VARCHAR2(30) := 'approve_funding_chklst';
1344 l_api_version CONSTANT NUMBER := 1.0;
1345 i NUMBER;
1346 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1347
1348 lp_rulv_tbl okl_rule_pub.rulv_tbl_type;
1349 lx_rulv_tbl okl_rule_pub.rulv_tbl_type;
1350 l_id number;
1351
1352 cursor c_ids (p_chr_id okc_k_headers_b.id%TYPE, p_req_id number)
1353 is
1354 select a.id
1355 from okc_rules_b a
1356 where a.dnz_chr_id = p_chr_id
1357 -- Sep-26-2005 cklee -- Fixed ORA-01722: invalid number for |
1358 -- approve_funding_chklst function cursor issue |
1359 --and a.object1_id1 = p_req_id
1360 and a.object1_id1 = TO_CHAR(p_req_id)
1361 and a.RULE_INFORMATION_CATEGORY = G_FUNDING_CHKLST_TPL_RULE1--'LAFCLD'
1362 ;
1363
1364
1365 begin
1366 -- Set API savepoint
1367 SAVEPOINT approve_funding_chklst;
1368
1369 -- Check for call compatibility
1370 IF (NOT FND_API.Compatible_API_Call (l_api_version,
1371 p_api_version,
1372 l_api_name,
1373 G_PKG_NAME ))
1374 THEN
1375 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1376 END IF;
1377
1378 -- Initialize message list if requested
1379 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
1380 FND_MSG_PUB.initialize;
1381 END IF;
1382
1383 -- Initialize API status to success
1384 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1385
1386
1387 /*** Begin API body ****************************************************/
1388
1389 open c_ids(p_rulv_rec.dnz_chr_id, p_rulv_rec.object1_id1);
1390 i := 0;
1391 loop
1392
1393 fetch c_ids into l_id;
1394 exit when c_ids%NOTFOUND;
1395
1396 lp_rulv_tbl(i).ID := l_id;
1397 lp_rulv_tbl(i).RULE_INFORMATION5 := 'ACTIVE';
1398
1399 i := i+1;
1400 end loop;
1401
1402 -- validation
1403
1404 okl_rule_pub.update_rule(
1405 p_api_version => p_api_version,
1406 p_init_msg_list => p_init_msg_list,
1407 x_return_status => x_return_status,
1408 x_msg_count => x_msg_count,
1409 x_msg_data => x_msg_data,
1410 p_rulv_tbl => lp_rulv_tbl,
1411 x_rulv_tbl => lx_rulv_tbl);
1412
1413 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1414 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1415 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1416 raise OKC_API.G_EXCEPTION_ERROR;
1417 End If;
1418
1419 /*** End API body ******************************************************/
1420
1421 -- Get message count and if count is 1, get message info
1422 FND_MSG_PUB.Count_And_Get
1423 (p_count => x_msg_count,
1424 p_data => x_msg_data);
1425
1426 EXCEPTION
1427 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1428 ROLLBACK TO approve_funding_chklst;
1429 x_return_status := OKL_API.G_RET_STS_ERROR;
1430 FND_MSG_PUB.Count_And_Get
1431 (p_count => x_msg_count,
1432 p_data => x_msg_data);
1433
1434 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1435 ROLLBACK TO approve_funding_chklst;
1436 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1437 FND_MSG_PUB.Count_And_Get
1438 (p_count => x_msg_count,
1439 p_data => x_msg_data);
1440
1441 WHEN OTHERS THEN
1442 ROLLBACK TO approve_funding_chklst;
1443 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1444 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1445 p_msg_name => G_UNEXPECTED_ERROR,
1446 p_token1 => G_SQLCODE_TOKEN,
1447 p_token1_value => SQLCODE,
1448 p_token2 => G_SQLERRM_TOKEN,
1449 p_token2_value => SQLERRM);
1450 FND_MSG_PUB.Count_And_Get
1451 (p_count => x_msg_count,
1452 p_data => x_msg_data);
1453
1454 end approve_funding_chklst;
1455
1456
1457 END OKL_FUNDING_CHECKLIST_PVT;