[Home] [Help]
PACKAGE BODY: APPS.OKL_CREDIT_PUB
Source
1 PACKAGE BODY OKL_CREDIT_PUB AS
2 /* $Header: OKLPCRDB.pls 120.22.12020000.2 2012/08/07 12:46:59 racheruv ship $ */
3 ----------------------------------------------------------------------------
4 -- Global Message Constants
5 ----------------------------------------------------------------------------
6 -- see FND_NEW_MESSAGES for full message text
7 G_NOT_FOUND CONSTANT VARCHAR2(30) := 'OKC_NOT_FOUND'; -- message_name
8 G_NOT_FOUND_V1 CONSTANT VARCHAR2(30) := 'VALUE1'; -- token 1
9 G_NOT_FOUND_V2 CONSTANT VARCHAR2(30) := 'VALUE2'; -- token 2
10
11 G_NOT_UNIQUE CONSTANT VARCHAR2(30) := 'OKL_LLA_NOT_UNIQUE';
12 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(30) := 'OKL_UNEXPECTED_ERROR';
13
14 G_SQLERRM_TOKEN CONSTANT VARCHAR2(30) := 'OKL_SQLERRM';
15 G_SQLCODE_TOKEN CONSTANT VARCHAR2(30) := 'OKL_SQLCODE';
16
17 G_NO_INIT_MSG CONSTANT VARCHAR2(1) := OKL_API.G_FALSE;
18 G_VIEW CONSTANT VARCHAR2(30) := 'OKL_TRX_AP_INVOICES_V';
19
20 G_FND_APP CONSTANT VARCHAR2(30) := OKL_API.G_FND_APP;
21 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(30) := OKL_API.G_FORM_UNABLE_TO_RESERVE_REC;
22 G_FORM_RECORD_DELETED CONSTANT VARCHAR2(30) := OKL_API.G_FORM_RECORD_DELETED;
23 G_FORM_RECORD_CHANGED CONSTANT VARCHAR2(30) := OKL_API.G_FORM_RECORD_CHANGED;
24 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(30) := OKL_API.G_RECORD_LOGICALLY_DELETED;
25
26 G_REQUIRED_VALUE CONSTANT VARCHAR2(30) := 'OKL_REQUIRED_VALUE';
27 G_INVALID_VALUE CONSTANT VARCHAR2(30) := OKL_API.G_INVALID_VALUE;
28 G_COL_NAME_TOKEN CONSTANT VARCHAR2(30) := OKL_API.G_COL_NAME_TOKEN;
29 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(30) := OKL_API.G_PARENT_TABLE_TOKEN;
30 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(30) := OKL_API.G_CHILD_TABLE_TOKEN;
31 G_NO_PARENT_RECORD CONSTANT VARCHAR2(30) :='OKL_NO_PARENT_RECORD';
32 G_NOT_SAME CONSTANT VARCHAR2(30) :='OKL_CANNOT_BE_SAME';
33
34 G_API_TYPE VARCHAR2(3) := 'PUB';
35 G_RLE_CODE VARCHAR2(10) := 'LESSEE';
36 G_STS_CODE VARCHAR2(10) := 'NEW';
37 G_SCS_CODE VARCHAR2(30) := 'CREDITLINE_CONTRACT';
38
39 G_CREATE_MODE VARCHAR2(30) := 'CREATE';
40 G_UPDATE_MODE VARCHAR2(30) := 'UPDATE';
41 G_DELETE_MODE VARCHAR2(30) := 'DELETE';
42
43 G_CREDIT_CHKLST_TPL CONSTANT VARCHAR2(30) := 'LACCLH';
44 G_CREDIT_CHKLST_TPL_RULE1 CONSTANT VARCHAR2(30) := 'LACCLT';
45 G_CREDIT_CHKLST_TPL_RULE2 CONSTANT VARCHAR2(30) := 'LACCLD'; -- credit line checklist
46 G_CREDIT_CHKLST_TPL_RULE3 CONSTANT VARCHAR2(30) := 'LACLFD'; /*funding checklist template for a credit line*/
47 G_CREDIT_CHKLST_TPL_RULE4 CONSTANT VARCHAR2(30) := 'LACLFM'; /*funding checklist template header for a credit line*/
48 G_RGP_TYPE CONSTANT VARCHAR2(30) := 'KRG';
49
50 /*
51 -- vthiruva, 08/31/2004
52 -- Added Constants to enable Business Event
53 */
54 G_WF_EVT_CR_LN_CREATED CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.credit_line.created';
55 G_WF_EVT_CR_LN_UPDATED CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.credit_line.updated';
56 G_WF_EVT_CR_LN_ACTIVATED CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.credit_line.activated';
57 G_WF_ITM_CR_LINE_ID CONSTANT VARCHAR2(30) := 'CREDIT_LINE_ID';
58
59 ----------------------------------------------------------------------------
60 -- Data Structures
61 ----------------------------------------------------------------------------
62 subtype rgpv_rec_type is okl_okc_migration_pvt.rgpv_rec_type;
63 subtype rgpv_tbl_type is okl_okc_migration_pvt.rgpv_tbl_type;
64 subtype rulv_rec_type is okl_rule_pub.rulv_rec_type;
65 subtype rulv_tbl_type is okl_rule_pub.rulv_tbl_type;
66
67 ----------------------------------------------------------------------------------
68 -- Start of comments
69 --
70 -- Procedure Name : NULLIF
71 -- Description : local functions to replace 9i new functions
72 -- Business Rules :
73 -- Parameters :
74 -- Version : 1.0
75 -- End of comments
76 ----------------------------------------------------------------------------------
77
78 FUNCTION NULLIF(p1 NUMBER, p2 NUMBER) RETURN NUMBER
79 is
80 l_temp NUMBER;
81
82 begin
83
84 IF p1 = p2 THEN
85 l_temp := NULL;
86 ELSE
87 l_temp := p1;
88 END IF;
89
90 RETURN l_temp;
91
92 exception
93 when others then
94 return NULL;
95
96 END NULLIF;
97
98 FUNCTION NULLIF(p1 VARCHAR2, p2 VARCHAR2) RETURN VARCHAR2
99 is
100 l_temp VARCHAR2(32767);
101
102 begin
103
104 IF p1 = p2 THEN
105 l_temp := NULL;
106 ELSE
107 l_temp := p1;
108 END IF;
109
110 RETURN l_temp;
111
112 exception
113 when others then
114 return NULL;
115
116 END NULLIF;
117
118 FUNCTION NULLIF(p1 DATE, p2 DATE) RETURN DATE
119 is
120 l_temp DATE;
121
122 begin
123
124 IF p1 = p2 THEN
125 l_temp := NULL;
126 ELSE
127 l_temp := p1;
128 END IF;
129
130 RETURN l_temp;
131
132 exception
133 when others then
134 return NULL;
135
136 END NULLIF;
137
138 /*
139 -- vthiruva, 08/31/2004
140 -- START, Added PROCEDURE to enable Business Event
141 */
142 ----------------------------------------------------------------------------------
143 -- Start of comments
144 --
145 -- Procedure Name : raise_business_event
146 -- Description : local_procedure, raises business event by making a call to
147 -- okl_wf_pvt.raise_event
148 -- Business Rules :
149 -- Parameters :
150 -- Version : 1.0
151 -- End of comments
152 ----------------------------------------------------------------------------------
153 PROCEDURE raise_business_event(
154 p_api_version IN NUMBER,
155 p_init_msg_list IN VARCHAR2,
156 x_return_status OUT NOCOPY VARCHAR2,
157 x_msg_count OUT NOCOPY NUMBER,
158 x_msg_data OUT NOCOPY VARCHAR2,
159 p_id IN NUMBER,
160 p_event_name IN VARCHAR2) IS
161
162 l_parameter_list wf_parameter_list_t;
163 BEGIN
164 --create the parameter list to pass to raise_event
165 wf_event.AddParameterToList(G_WF_ITM_CR_LINE_ID,p_id,l_parameter_list);
166
167 OKL_WF_PVT.raise_event(p_api_version => p_api_version,
168 p_init_msg_list => p_init_msg_list,
169 x_return_status => x_return_status,
170 x_msg_count => x_msg_count,
171 x_msg_data => x_msg_data,
172 p_event_name => p_event_name,
173 p_parameters => l_parameter_list);
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
178 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
179 END raise_business_event;
180
181 /*
182 -- vthiruva, 08/31/2004
183 -- END, PROCEDURE to enable Business Event
184 */
185
186
187 ----------------------------------------------------------------------------------
188 -- Start of comments
189 --
190 -- Procedure Name : create_credit_chklst_tpl
191 -- Description : wrapper api for create credit checklist template FK associated
192 -- with credit line contract ID
193 -- Business Rules :
194 -- Parameters :
195 -- Version : 1.0
196 -- End of comments
197 ----------------------------------------------------------------------------------
198 PROCEDURE create_credit_chklst_tpl(
199 p_api_version IN NUMBER
200 ,p_init_msg_list IN VARCHAR2
201 ,x_return_status OUT NOCOPY VARCHAR2
202 ,x_msg_count OUT NOCOPY NUMBER
203 ,x_msg_data OUT NOCOPY VARCHAR2
204 ,p_rgpv_rec IN rgpv_rec_type
205 ,p_rulv_rec IN rulv_rec_type
206 ,x_rgpv_rec OUT NOCOPY rgpv_rec_type
207 ,x_rulv_rec OUT NOCOPY rulv_rec_type
208 )
209 is
210 l_api_name CONSTANT VARCHAR2(30) := 'create_credit_chklst_tpl';
211 l_api_version CONSTANT NUMBER := 1.0;
212 i NUMBER;
213 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
214 lp_rgpv_rec rgpv_rec_type := p_rgpv_rec;
215 lp_rulv_rec rulv_rec_type := p_rulv_rec;
216
217 lp_rule2_rulv_rec rulv_rec_type;
218 lx_rule2_rulv_rec rulv_rec_type;
219
220 lp_rule3_rulv_rec rulv_rec_type;
221 lx_rule3_rulv_rec rulv_rec_type;
222
223 lp_rule4_rulv_rec rulv_rec_type;
224 lx_rule4_rulv_rec rulv_rec_type;
225
226 l_todo_item_code okl_checklist_details.TODO_ITEM_CODE%type;
227 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
228 l_function_id okl_checklist_details_uv.function_id%type;
229 l_inst_checklist_type okl_checklists.checklist_type%type;
230 l_dummy number;
231 l_is_grp_found boolean;
232
233
234 cursor c_is_grp(p_ckl_id number) is
235 select 1
236 from okl_checklists clist
237 where clist.checklist_purpose_code = 'CHECKLIST_TEMPLATE_GROUP'
238 and clist.id = p_ckl_id
239 ;
240
241 -- group checklist template items
242 cursor c_grp_chk (p_ckl_id number) is
243 --start modified abhsaxen for performance SQLID 20562590
244 select cld.todo_item_code,
245 cld.function_id,
246 clh.checklist_type
247 from OKL_CHECKLIST_DTLS_ALL CLD, OKL_CHECKLISTS CLH
248 where cld.ckl_id = clh.id
249 and exists (select 1
250 from okl_checklists chlidren
251 where chlidren.id = cld.ckl_id
252 and chlidren.ckl_id = p_ckl_id)
253 --end modified abhsaxen for performance SQLID 20562590
254 ;
255
256 -- checklist template
257 cursor c_chk_tpl (p_ckl_id number) is
258 select ckd.TODO_ITEM_CODE,
259 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
260 ckd.FUNCTION_ID,
261 ckd.CHECKLIST_TYPE
262 --from okl_checklist_details ckd
263 from okl_checklist_details_uv ckd
264 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
265 where ckd.ckl_id = p_ckl_id;
266
267 begin
268 -- Set API savepoint
269 SAVEPOINT create_credit_chklst_tpl;
270
271 -- Check for call compatibility
272 IF (NOT FND_API.Compatible_API_Call (l_api_version,
273 p_api_version,
274 l_api_name,
275 G_PKG_NAME ))
276 THEN
277 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
278 END IF;
279
280 -- Initialize message list if requested
281 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
282 FND_MSG_PUB.initialize;
283 END IF;
284
285 -- Initialize API status to success
286 x_return_status := OKL_API.G_RET_STS_SUCCESS;
287
288
289 /*** Begin API body ****************************************************/
290 /*
291 -------------------------------------------------------------
292 1. create rule group
293 2. create rule1 : template
294 3. create rule2 : get the source of the checklist template lists
295 4. create rules based on #3. cursor
296 5. create rule3 : get the source of the checklist template lists
297 6. create rules based on #5. cursor
298 -------------------------------------------------------------
299 */
300
301 -------------------------------------------------------------
302 --1. create rule group when user choose either credit line or funding checklist template
303 -------------------------------------------------------------
304 IF ((lp_rulv_rec.RULE_INFORMATION1 is not null AND
305 lp_rulv_rec.RULE_INFORMATION1 <> OKL_API.G_MISS_CHAR)
306 OR
307 (lp_rulv_rec.RULE_INFORMATION2 is not null AND
308 lp_rulv_rec.RULE_INFORMATION2 <> OKL_API.G_MISS_CHAR)) THEN
309
310 -- DNZ_CHR_ID is set by calling program
311 -- CHR_ID is set by calling program
312 lp_rgpv_rec.RGD_CODE := G_CREDIT_CHKLST_TPL;
313 lp_rgpv_rec.RGP_TYPE := G_RGP_TYPE;
314
315 okl_rule_pub.create_rule_group(
316 p_api_version => p_api_version,
317 p_init_msg_list => p_init_msg_list,
318 x_return_status => x_return_status,
319 x_msg_count => x_msg_count,
320 x_msg_data => x_msg_data,
321 p_rgpv_rec => lp_rgpv_rec,
322 x_rgpv_rec => x_rgpv_rec);
323
324 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
325 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
326 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
327 raise OKC_API.G_EXCEPTION_ERROR;
328 End If;
329 -------------------------------------------------------------
330 --2. create rule1 : template
331 -------------------------------------------------------------
332
333 -- RULE_INFORMATION1, RULE_INFORMATION2, DNZ_CHR_ID are set by calling program
334 lp_rulv_rec.RGP_ID := x_rgpv_rec.ID;
335 lp_rulv_rec.RULE_INFORMATION_CATEGORY := G_CREDIT_CHKLST_TPL_RULE1;
336 lp_rulv_rec.STD_TEMPLATE_YN := 'N';
337 lp_rulv_rec.WARN_YN := 'N';
338
339 okl_rule_pub.create_rule(
340 p_api_version => p_api_version,
341 p_init_msg_list => p_init_msg_list,
342 x_return_status => x_return_status,
343 x_msg_count => x_msg_count,
344 x_msg_data => x_msg_data,
345 p_rulv_rec => lp_rulv_rec,
346 x_rulv_rec => x_rulv_rec);
347
348 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
349 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
350 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
351 raise OKC_API.G_EXCEPTION_ERROR;
352 End If;
353
354 END IF;
355 -------------------------------------------------------------
356 -- credit line checklist
357 --
358 --3. create rule2 : get the source of the checklist template lists
359 --4. create rules based on #3. cursor
360 -------------------------------------------------------------
361 IF (lp_rulv_rec.RULE_INFORMATION1 is not null AND
362 lp_rulv_rec.RULE_INFORMATION1 <> OKL_API.G_MISS_CHAR) THEN
363
364 --start: 06-May-2005 cklee okl.h Lease App IA Authoring
365 OPEN c_is_grp(TO_NUMBER(lp_rulv_rec.RULE_INFORMATION1));
366 FETCH c_is_grp INTO l_dummy;
367 l_is_grp_found := c_is_grp%FOUND;
368 CLOSE c_is_grp;
369 --end: 06-May-2005 cklee okl.h Lease App IA Authoring
370
371 -- is not a group template
372 IF l_is_grp_found = false THEN
373 --end: 06-May-2005 cklee okl.h Lease App IA Authoring
374
375 open c_chk_tpl(to_number(lp_rulv_rec.RULE_INFORMATION1));
376 LOOP
377
378 fetch c_chk_tpl into l_todo_item_code,
379 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
380 l_function_id,
381 l_inst_checklist_type;
382 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
383 EXIT WHEN c_chk_tpl%NOTFOUND;
384
385 lp_rule2_rulv_rec.RGP_ID := x_rgpv_rec.ID;
386 lp_rule2_rulv_rec.DNZ_CHR_ID := lp_rgpv_rec.DNZ_CHR_ID;
387 lp_rule2_rulv_rec.RULE_INFORMATION_CATEGORY := G_CREDIT_CHKLST_TPL_RULE2;
388 lp_rule2_rulv_rec.STD_TEMPLATE_YN := 'N';
389 lp_rule2_rulv_rec.WARN_YN := 'N';
390 lp_rule2_rulv_rec.RULE_INFORMATION1 := l_todo_item_code;
391 lp_rule2_rulv_rec.RULE_INFORMATION2 := 'N';
392 lp_rule2_rulv_rec.RULE_INFORMATION3 := 'N';
393 lp_rule2_rulv_rec.RULE_INFORMATION5 := G_STS_CODE; -- set default status
394 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
395 lp_rule2_rulv_rec.RULE_INFORMATION7 := 'UNDETERMINED'; -- set default status
396 lp_rule2_rulv_rec.RULE_INFORMATION9 := l_function_id;
397 lp_rule2_rulv_rec.RULE_INFORMATION10 := l_inst_checklist_type;
398 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
399
400 okl_rule_pub.create_rule(
401 p_api_version => p_api_version,
402 p_init_msg_list => p_init_msg_list,
403 x_return_status => x_return_status,
404 x_msg_count => x_msg_count,
405 x_msg_data => x_msg_data,
406 p_rulv_rec => lp_rule2_rulv_rec,
407 x_rulv_rec => lx_rule2_rulv_rec);
408
409 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
410 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
411 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
412 raise OKC_API.G_EXCEPTION_ERROR;
413 End If;
414
415 END LOOP;
416 CLOSE c_chk_tpl;
417
418 -- is a group template
419 -- IF l_is_grp_found = false THEN
420 ELSE
421
422 open c_grp_chk(to_number(lp_rulv_rec.RULE_INFORMATION1));
423 LOOP
424
425 fetch c_grp_chk into l_todo_item_code,
426 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
427 l_function_id,
428 l_inst_checklist_type;
429 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
430 EXIT WHEN c_grp_chk%NOTFOUND;
431
432 lp_rule2_rulv_rec.RGP_ID := x_rgpv_rec.ID;
433 lp_rule2_rulv_rec.DNZ_CHR_ID := lp_rgpv_rec.DNZ_CHR_ID;
434 lp_rule2_rulv_rec.RULE_INFORMATION_CATEGORY := G_CREDIT_CHKLST_TPL_RULE2;
435 lp_rule2_rulv_rec.STD_TEMPLATE_YN := 'N';
436 lp_rule2_rulv_rec.WARN_YN := 'N';
437 lp_rule2_rulv_rec.RULE_INFORMATION1 := l_todo_item_code;
438 lp_rule2_rulv_rec.RULE_INFORMATION2 := 'N';
439 lp_rule2_rulv_rec.RULE_INFORMATION3 := 'N';
440 lp_rule2_rulv_rec.RULE_INFORMATION5 := G_STS_CODE; -- set default status
441 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
442 lp_rule2_rulv_rec.RULE_INFORMATION7 := 'UNDETERMINED'; -- set default status
443 lp_rule2_rulv_rec.RULE_INFORMATION9 := l_function_id;
444 lp_rule2_rulv_rec.RULE_INFORMATION10 := l_inst_checklist_type;
445 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
446
447 okl_rule_pub.create_rule(
448 p_api_version => p_api_version,
449 p_init_msg_list => p_init_msg_list,
450 x_return_status => x_return_status,
451 x_msg_count => x_msg_count,
452 x_msg_data => x_msg_data,
453 p_rulv_rec => lp_rule2_rulv_rec,
454 x_rulv_rec => lx_rule2_rulv_rec);
455
456 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
457 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
458 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
459 raise OKC_API.G_EXCEPTION_ERROR;
460 End If;
461
462 END LOOP;
463 CLOSE c_grp_chk;
464
465 END IF;
466
467
468 END IF;
469
470 -------------------------------------------------------------
471 -- instance of funding checklist template at credit line level
472 --
473 --
474 -- create funding checklist template header
475 -------------------------------------------------------------
476 IF (lp_rulv_rec.RULE_INFORMATION2 is not null AND
477 lp_rulv_rec.RULE_INFORMATION2 <> OKL_API.G_MISS_CHAR) THEN
478
479 lp_rule3_rulv_rec.RGP_ID := x_rgpv_rec.ID;
480 lp_rule3_rulv_rec.DNZ_CHR_ID := lp_rgpv_rec.DNZ_CHR_ID;
481 lp_rule3_rulv_rec.RULE_INFORMATION_CATEGORY := G_CREDIT_CHKLST_TPL_RULE4;
482 lp_rule3_rulv_rec.STD_TEMPLATE_YN := 'N';
483 lp_rule3_rulv_rec.WARN_YN := 'N';
484 -- lp_rule3_rulv_rec.RULE_INFORMATION1 := null; -- effective from
485 -- lp_rule3_rulv_rec.RULE_INFORMATION2 := null; -- effective to
486 lp_rule3_rulv_rec.RULE_INFORMATION3 := G_STS_CODE;
487
488 okl_rule_pub.create_rule(
489 p_api_version => p_api_version,
490 p_init_msg_list => p_init_msg_list,
491 x_return_status => x_return_status,
492 x_msg_count => x_msg_count,
493 x_msg_data => x_msg_data,
494 p_rulv_rec => lp_rule3_rulv_rec,
495 x_rulv_rec => lx_rule3_rulv_rec);
496
497 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
498 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
499 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
500 raise OKC_API.G_EXCEPTION_ERROR;
501 End If;
502
503 -------------------------------------------------------------
504 --5. create rule3 : get the source of the checklist template lists
505 --6. create rules based on #5. cursor
506 --
507 -------------------------------------------------------------
508 --start: 06-May-2005 cklee okl.h Lease App IA Authoring
509 OPEN c_is_grp(TO_NUMBER(lp_rulv_rec.RULE_INFORMATION2));
510 FETCH c_is_grp INTO l_dummy;
511 l_is_grp_found := c_is_grp%FOUND;
512 CLOSE c_is_grp;
513 --end: 06-May-2005 cklee okl.h Lease App IA Authoring
514
515 -- is not a group template
516 IF l_is_grp_found = false THEN
517 --end: 06-May-2005 cklee okl.h Lease App IA Authoring
518
519 open c_chk_tpl(to_number(lp_rulv_rec.RULE_INFORMATION2));
520 LOOP
521
522 fetch c_chk_tpl into l_todo_item_code,
523 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
524 l_function_id,
525 l_inst_checklist_type;
526 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
527 EXIT WHEN c_chk_tpl%NOTFOUND;
528
529
530 lp_rule4_rulv_rec.RGP_ID := x_rgpv_rec.ID;
531
532 lp_rule4_rulv_rec.OBJECT1_ID1 := lx_rule3_rulv_rec.ID; -- FK
533 lp_rule4_rulv_rec.OBJECT1_ID2 := '#'; -- dummy one
534
535 lp_rule4_rulv_rec.DNZ_CHR_ID := lp_rgpv_rec.DNZ_CHR_ID;
536 lp_rule4_rulv_rec.RULE_INFORMATION_CATEGORY := G_CREDIT_CHKLST_TPL_RULE3;
537 lp_rule4_rulv_rec.STD_TEMPLATE_YN := 'N';
538 lp_rule4_rulv_rec.WARN_YN := 'N';
539 lp_rule4_rulv_rec.RULE_INFORMATION1 := l_todo_item_code;
540 lp_rule4_rulv_rec.RULE_INFORMATION2 := 'N';
541 -- lp_rule4_rulv_rec.RULE_INFORMATION3 := 'N'; -- not applicable
542 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
543 -- lp_rule2_rulv_rec.RULE_INFORMATION6 := l_function_id;
544 -- lp_rule2_rulv_rec.RULE_INFORMATION7 := l_inst_checklist_type;
545 -- START: typo 10/03/2005 cklee
546 lp_rule4_rulv_rec.RULE_INFORMATION6 := l_function_id;
547 lp_rule4_rulv_rec.RULE_INFORMATION7 := l_inst_checklist_type;
548 -- END: typo 10/03/2005 cklee
549 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
550
551 okl_rule_pub.create_rule(
552 p_api_version => p_api_version,
553 p_init_msg_list => p_init_msg_list,
554 x_return_status => x_return_status,
555 x_msg_count => x_msg_count,
556 x_msg_data => x_msg_data,
557 p_rulv_rec => lp_rule4_rulv_rec,
558 x_rulv_rec => lx_rule4_rulv_rec);
559
560 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
561 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
562 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
563 raise OKC_API.G_EXCEPTION_ERROR;
564 End If;
565
566 END LOOP;
567 CLOSE c_chk_tpl;
568
569 -- is not a group template
570 -- IF l_is_grp_found = false THEN
571 ELSE
572
573 open c_grp_chk(to_number(lp_rulv_rec.RULE_INFORMATION2));
574 LOOP
575
576 fetch c_grp_chk into l_todo_item_code,
577 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
578 l_function_id,
579 l_inst_checklist_type;
580 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
581 EXIT WHEN c_grp_chk%NOTFOUND;
582
583
584 lp_rule4_rulv_rec.RGP_ID := x_rgpv_rec.ID;
585
586 lp_rule4_rulv_rec.OBJECT1_ID1 := lx_rule3_rulv_rec.ID; -- FK
587 lp_rule4_rulv_rec.OBJECT1_ID2 := '#'; -- dummy one
588
589 lp_rule4_rulv_rec.DNZ_CHR_ID := lp_rgpv_rec.DNZ_CHR_ID;
590 lp_rule4_rulv_rec.RULE_INFORMATION_CATEGORY := G_CREDIT_CHKLST_TPL_RULE3;
591 lp_rule4_rulv_rec.STD_TEMPLATE_YN := 'N';
592 lp_rule4_rulv_rec.WARN_YN := 'N';
593 lp_rule4_rulv_rec.RULE_INFORMATION1 := l_todo_item_code;
594 lp_rule4_rulv_rec.RULE_INFORMATION2 := 'N';
595 -- lp_rule4_rulv_rec.RULE_INFORMATION3 := 'N'; -- not applicable
596 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
597 -- lp_rule2_rulv_rec.RULE_INFORMATION6 := l_function_id;
598 -- lp_rule2_rulv_rec.RULE_INFORMATION7 := l_inst_checklist_type;
599 -- START: typo 10/03/2005 cklee
600 lp_rule4_rulv_rec.RULE_INFORMATION6 := l_function_id;
601 lp_rule4_rulv_rec.RULE_INFORMATION7 := l_inst_checklist_type;
602 -- END: typo 10/03/2005 cklee
603 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
604
605 okl_rule_pub.create_rule(
606 p_api_version => p_api_version,
607 p_init_msg_list => p_init_msg_list,
608 x_return_status => x_return_status,
609 x_msg_count => x_msg_count,
610 x_msg_data => x_msg_data,
611 p_rulv_rec => lp_rule4_rulv_rec,
612 x_rulv_rec => lx_rule4_rulv_rec);
613
614 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
615 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
616 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
617 raise OKC_API.G_EXCEPTION_ERROR;
618 End If;
619
620 END LOOP;
621 CLOSE c_grp_chk;
622
623 END IF;
624
625 END IF;
626
627 /*** End API body ******************************************************/
628
629 -- Get message count and if count is 1, get message info
630 FND_MSG_PUB.Count_And_Get
631 (p_count => x_msg_count,
632 p_data => x_msg_data);
633
634 EXCEPTION
635
636 WHEN OTHERS THEN
637 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
638 OKL_API.Set_Message(p_app_name => G_APP_NAME,
639 p_msg_name => G_UNEXPECTED_ERROR,
640 p_token1 => G_SQLCODE_TOKEN,
641 p_token1_value => SQLCODE,
642 p_token2 => G_SQLERRM_TOKEN,
643 p_token2_value => SQLERRM);
644
645 end create_credit_chklst_tpl;
646
647 ----------------------------------------------------------------------------------
648 -- Start of comments
649 --
650 -- Procedure Name : update_credit_chklst_tpl
651 -- Description : wrapper api for update credit checklist template FK associated
652 -- with credit line contract ID.
653 -- Business Rules :
654 -- Parameters :
655 -- Version : 1.0
656 -- End of comments
657 ----------------------------------------------------------------------------------
658 PROCEDURE update_credit_chklst_tpl(
659 p_api_version IN NUMBER
660 ,p_init_msg_list IN VARCHAR2
661 ,x_return_status OUT NOCOPY VARCHAR2
662 ,x_msg_count OUT NOCOPY NUMBER
663 ,x_msg_data OUT NOCOPY VARCHAR2
664 ,p_rulv_rec IN rulv_rec_type
665 ,x_rulv_rec OUT NOCOPY rulv_rec_type
666 )
667 is
668 l_api_name CONSTANT VARCHAR2(30) := 'update_credit_chklst_tpl';
669 l_api_version CONSTANT NUMBER := 1.0;
670 i NUMBER;
671 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
672
673 lpcrt_rgpv_rec rgpv_rec_type;
674 lxcrt_rgpv_rec rgpv_rec_type;
675 lpcrt_rulv_rec rulv_rec_type;
676 lxcrt_rulv_rec rulv_rec_type;
677
678 lpcrt2_rulv_rec rulv_rec_type;
679 lxcrt2_rulv_rec rulv_rec_type;
680
681 lpcrt3_rulv_rec rulv_rec_type;
682 lxcrt3_rulv_rec rulv_rec_type;
683
684 lpcrt4_rulv_rec rulv_rec_type;
685 lxcrt4_rulv_rec rulv_rec_type;
686
687 lp_rulv_rec rulv_rec_type := p_rulv_rec;
688 lx_rulv_rec rulv_rec_type := x_rulv_rec;
689
690 l_rule_id okc_rules_b.id%type;
691 ldel_rulv_rec rulv_rec_type;
692 lcrt_rulv_rec rulv_rec_type;
693
694 -- l_dummy number;
695 l_row_found boolean := false;
696 l_RULE_INFORMATION1 okc_rules_b.RULE_INFORMATION1%type;
697 l_RULE_INFORMATION2 okc_rules_b.RULE_INFORMATION2%type;
698
699 l_todo_item_code okl_checklist_details.TODO_ITEM_CODE%type;
700 l_fund_clist_hdr_id okc_rules_b.id%TYPE;
701 l_fund_cl_hdr_notfound boolean := false;
702
703 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
704 l_function_id okl_checklist_details_uv.function_id%type;
705 l_inst_checklist_type okl_checklists.checklist_type%type;
706 l_dummy number;
707 l_is_grp_found boolean;
708
709
710 cursor c_is_grp(p_ckl_id number) is
711 select 1
712 from okl_checklists clist
713 where clist.checklist_purpose_code = 'CHECKLIST_TEMPLATE_GROUP'
714 and clist.id = p_ckl_id
715 ;
716
717 -- group checklist template items
718 cursor c_grp_chk (p_ckl_id number) is
719 --start modified abhsaxen for performance SQLID 20562606
720 select cld.todo_item_code,
721 cld.function_id,
722 clh.checklist_type
723 from OKL_CHECKLIST_DTLS_ALL CLD, OKL_CHECKLISTS CLH
724 where cld.ckl_id = clh.id
725 and exists (select 1
726 from okl_checklists chlidren
727 where chlidren.id = cld.ckl_id
728 and chlidren.ckl_id = p_ckl_id)
729 --end modified abhsaxen for performance SQLID 20562606
730 ;
731
732 ---------------------------------------------------------
733 -- to do item lists from setup
734 ---------------------------------------------------------
735 cursor c_chk_tpl (p_ckl_id okl_checklists.id%type) is
736 select ckd.TODO_ITEM_CODE,
737 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
738 ckd.FUNCTION_ID,
739 ckd.CHECKLIST_TYPE
740 --from okl_checklist_details ckd
741 from okl_checklist_details_uv ckd
742 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
743 where ckd.ckl_id = p_ckl_id
744 ;
745
746 ---------------------------------------------------------
747 -- to do item lists from instance of credit line
748 ---------------------------------------------------------
749 cursor c_del (p_rgp_id okc_rules_b.rgp_id%type,
750 p_rule_category okc_rules_b.RULE_INFORMATION_CATEGORY%type) is
751 select rule.id
752 from okc_rules_b rule
753 where rule.RULE_INFORMATION_CATEGORY = p_rule_category
754 and rule.RGP_ID = p_rgp_id
755 ;
756
757 ---------------------------------------------------------
758 -- existing FK for checklists
759 ---------------------------------------------------------
760 cursor c_chk (p_rule_id okc_rules_b.id%type) is
761 select rule.RULE_INFORMATION1,
762 rule.RULE_INFORMATION2
763 from okc_rules_b rule
764 where rule.id = p_rule_id
765 ;
766
767 ---------------------------------------------------------
768 -- existing funding request checklist template header
769 ---------------------------------------------------------
770 cursor c_chk_tpl_hdr (p_chr_id okc_rules_b.dnz_chr_id%type) is
771 select hdr.ID
772 from okl_crd_fund_chklst_tpl_hdr_uv hdr
773 where hdr.khr_id = p_chr_id
774 ;
775
776
777 begin
778 -- Set API savepoint
779 SAVEPOINT update_credit_chklst_tpl;
780
781 -- Check for call compatibility
782 IF (NOT FND_API.Compatible_API_Call (l_api_version,
783 p_api_version,
784 l_api_name,
785 G_PKG_NAME ))
786 THEN
787 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
788 END IF;
789
790 -- Initialize message list if requested
791 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
792 FND_MSG_PUB.initialize;
793 END IF;
794
795 -- Initialize API status to success
796 x_return_status := OKL_API.G_RET_STS_SUCCESS;
797
798
799 /*** Begin API body ****************************************************/
800 /*
801 -------------------------------------------------------------
802 1. create rule group if rule group doesn't exists
803 (handle existing credit line contract or credit line doesn't have list)
804 call create_credit_chklst_tpl()
805
806 2. check if credit template has been changed
807 3. update rule if changes
808 -- credit line checklist
809 3.1.1 get the source of the checklist template lists from OKL link
810 3.1.2 delete associated checklist
811
812 3.2.1 get the source of the checklist template lists from NEW link
813 3.2.2 create rules based on #3. cursor
814 -- instance of funding checklist template
815 3.3.1 get the source of the checklist template lists from OKL link
816 3.3.2 delete associated checklist
817
818 3.4.1 get the source of the checklist template lists from NEW link
819 3.4.2 create rules based on #3. cursor
820 -------------------------------------------------------------
821 */
822
823 -------------------------------------------------------------
824 --1. create rule group if rule group doesn't exists
825 -- (handle existing credit line contract or credit line doesn't have list)
826 -- call create_credit_chklst_tpl()
827 -------------------------------------------------------------
828 IF ( lp_rulv_rec.RGP_ID is null OR lp_rulv_rec.RGP_ID = OKC_API.G_MISS_NUM ) THEN
829
830 -- rule group FK
831 lpcrt_rgpv_rec.DNZ_CHR_ID := lp_rulv_rec.DNZ_CHR_ID;
832 lpcrt_rgpv_rec.CHR_ID := lp_rulv_rec.DNZ_CHR_ID;
833
834 lpcrt_rulv_rec.DNZ_CHR_ID := lp_rulv_rec.DNZ_CHR_ID;
835 lpcrt_rulv_rec.RULE_INFORMATION1 := lp_rulv_rec.RULE_INFORMATION1;
836 lpcrt_rulv_rec.RULE_INFORMATION2 := lp_rulv_rec.RULE_INFORMATION2;
837
838 create_credit_chklst_tpl(
839 p_api_version => p_api_version,
840 p_init_msg_list => p_init_msg_list,
841 x_return_status => x_return_status,
842 x_msg_count => x_msg_count,
843 x_msg_data => x_msg_data,
844 p_rgpv_rec => lpcrt_rgpv_rec,
845 p_rulv_rec => lpcrt_rulv_rec,
846 x_rgpv_rec => lxcrt_rgpv_rec,
847 x_rulv_rec => lxcrt_rulv_rec);
848
849 -- assign new FK to local rule record
850
851 lp_rulv_rec.RGP_ID := lxcrt_rgpv_rec.ID;
852
853 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
854 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
855 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
856 raise OKC_API.G_EXCEPTION_ERROR;
857 End If;
858
859 ELSE -- Assume lp_rulv_rec.ID is available
860
861 -------------------------------------------------------------
862 --2. check if credit template has been changed
863 -------------------------------------------------------------
864 open c_chk (lp_rulv_rec.ID);
865 fetch c_chk into l_RULE_INFORMATION1,
866 l_RULE_INFORMATION2;
867 close c_chk;
868 ------------------------------------------------------------------------
869 -- check credit checklist
870 ------------------------------------------------------------------------
871 ------------------------------------------------------------------------
872 -- delete rule2
873 -- 1. both old and new ID exist and there are different
874 -- 2. old ID exists, but new ID missing
875 ------------------------------------------------------------------------
876 IF ( l_RULE_INFORMATION1 is not null AND
877 (lp_rulv_rec.RULE_INFORMATION1 is not null AND
878 lp_rulv_rec.RULE_INFORMATION1 <> l_RULE_INFORMATION1)
879 OR
880 lp_rulv_rec.RULE_INFORMATION1 is null
881 ) THEN
882
883 ------------------------------------------------------------------------
884 -- 3.1.1 get the source of the checklist template lists from OKL link
885 -- 3.1.2 delete associated checklist
886 ------------------------------------------------------------------------
887 open c_del (lp_rulv_rec.RGP_ID,G_CREDIT_CHKLST_TPL_RULE2);
888 LOOP
889
890 fetch c_del into l_rule_id;
891 EXIT WHEN c_del%NOTFOUND;
892
893 ldel_rulv_rec.ID := l_rule_id;
894
895 okl_rule_pub.delete_rule(
896 p_api_version => p_api_version,
897 p_init_msg_list => p_init_msg_list,
898 x_return_status => x_return_status,
899 x_msg_count => x_msg_count,
900 x_msg_data => x_msg_data,
901 p_rulv_rec => ldel_rulv_rec);
902
903 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
904 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
905 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
906 raise OKC_API.G_EXCEPTION_ERROR;
907 End If;
908
909 END LOOP;
910 close c_del;
911
912 END IF;
913
914 ------------------------------------------------------------------------
915 -- create rule2
916 ------------------------------------------------------------------------
917 -- 1. both old and new ID exist and there are different
918 -- 2. old ID missing, but new ID exists
919 ------------------------------------------------------------------------
920 IF (lp_rulv_rec.RULE_INFORMATION1 is not null AND
921 (l_RULE_INFORMATION1 is not null AND
922 lp_rulv_rec.RULE_INFORMATION1 <> l_RULE_INFORMATION1)
923 OR
924 l_RULE_INFORMATION1 is null
925 ) THEN
926
927 ------------------------------------------------------------------------
928 -- 3.2.1 get the source of the checklist template lists from NEW link
929 -- 3.2.2 create rules based on #3. cursor
930 ------------------------------------------------------------------------
931 --start: 06-May-2005 cklee okl.h Lease App IA Authoring
932 OPEN c_is_grp(TO_NUMBER(lp_rulv_rec.RULE_INFORMATION1));
933 FETCH c_is_grp INTO l_dummy;
934 l_is_grp_found := c_is_grp%FOUND;
935 CLOSE c_is_grp;
936 --end: 06-May-2005 cklee okl.h Lease App IA Authoring
937
938 -- is not a group template
939 IF l_is_grp_found = false THEN
940 --end: 06-May-2005 cklee okl.h Lease App IA Authoring
941 open c_chk_tpl(to_number(lp_rulv_rec.RULE_INFORMATION1));
942 LOOP
943
944 fetch c_chk_tpl into l_todo_item_code,
945 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
946 l_function_id,
947 l_inst_checklist_type;
948 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
949
950 EXIT WHEN c_chk_tpl%NOTFOUND;
951
952 lpcrt2_rulv_rec.RGP_ID := lp_rulv_rec.RGP_ID;
953 lpcrt2_rulv_rec.DNZ_CHR_ID := lp_rulv_rec.DNZ_CHR_ID;
954
955 lpcrt2_rulv_rec.RULE_INFORMATION_CATEGORY := G_CREDIT_CHKLST_TPL_RULE2;
956 lpcrt2_rulv_rec.STD_TEMPLATE_YN := 'N';
957 lpcrt2_rulv_rec.WARN_YN := 'N';
958 lpcrt2_rulv_rec.RULE_INFORMATION1 := l_todo_item_code;
959 lpcrt2_rulv_rec.RULE_INFORMATION2 := 'N';
960 lpcrt2_rulv_rec.RULE_INFORMATION3 := 'N';
961 lpcrt2_rulv_rec.RULE_INFORMATION5 := G_STS_CODE; -- set default status
962 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
963 lpcrt2_rulv_rec.RULE_INFORMATION7 := 'UNDETERMINED'; -- set default status
964 lpcrt2_rulv_rec.RULE_INFORMATION9 := l_function_id;
965 lpcrt2_rulv_rec.RULE_INFORMATION10 := l_inst_checklist_type;
966 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
967
968 okl_rule_pub.create_rule(
969 p_api_version => p_api_version,
970 p_init_msg_list => p_init_msg_list,
971 x_return_status => x_return_status,
972 x_msg_count => x_msg_count,
973 x_msg_data => x_msg_data,
974 p_rulv_rec => lpcrt2_rulv_rec,
975 x_rulv_rec => lxcrt2_rulv_rec);
976
977 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
978 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
979 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
980 raise OKC_API.G_EXCEPTION_ERROR;
981 End If;
982
983 END LOOP;
984 CLOSE c_chk_tpl;
985
986 -- is a group template
987 -- IF l_is_grp_found = false THEN
988 ELSE
989
990 open c_grp_chk(to_number(lp_rulv_rec.RULE_INFORMATION1));
991 LOOP
992
993 fetch c_grp_chk into l_todo_item_code,
994 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
995 l_function_id,
996 l_inst_checklist_type;
997 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
998
999 EXIT WHEN c_grp_chk%NOTFOUND;
1000
1001 lpcrt2_rulv_rec.RGP_ID := lp_rulv_rec.RGP_ID;
1002 lpcrt2_rulv_rec.DNZ_CHR_ID := lp_rulv_rec.DNZ_CHR_ID;
1003
1004 lpcrt2_rulv_rec.RULE_INFORMATION_CATEGORY := G_CREDIT_CHKLST_TPL_RULE2;
1005 lpcrt2_rulv_rec.STD_TEMPLATE_YN := 'N';
1006 lpcrt2_rulv_rec.WARN_YN := 'N';
1007 lpcrt2_rulv_rec.RULE_INFORMATION1 := l_todo_item_code;
1008 lpcrt2_rulv_rec.RULE_INFORMATION2 := 'N';
1009 lpcrt2_rulv_rec.RULE_INFORMATION3 := 'N';
1010 lpcrt2_rulv_rec.RULE_INFORMATION5 := G_STS_CODE; -- set default status
1011 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
1012 lpcrt2_rulv_rec.RULE_INFORMATION7 := 'UNDETERMINED'; -- set default status
1013 lpcrt2_rulv_rec.RULE_INFORMATION9 := l_function_id;
1014 lpcrt2_rulv_rec.RULE_INFORMATION10 := l_inst_checklist_type;
1015 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
1016
1017 okl_rule_pub.create_rule(
1018 p_api_version => p_api_version,
1019 p_init_msg_list => p_init_msg_list,
1020 x_return_status => x_return_status,
1021 x_msg_count => x_msg_count,
1022 x_msg_data => x_msg_data,
1023 p_rulv_rec => lpcrt2_rulv_rec,
1024 x_rulv_rec => lxcrt2_rulv_rec);
1025
1026 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1027 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1028 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1029 raise OKC_API.G_EXCEPTION_ERROR;
1030 End If;
1031
1032 END LOOP;
1033 CLOSE c_grp_chk;
1034
1035 END IF;
1036
1037
1038 END IF;
1039
1040 ------------------------------------------------------------------------
1041 -- check instance of funding checklist template at credit line level
1042 ------------------------------------------------------------------------
1043 ------------------------------------------------------------------------
1044 -- check if funding request checklist header already exists
1045 ------------------------------------------------------------------------
1046 open c_chk_tpl_hdr(lp_rulv_rec.DNZ_CHR_ID);
1047 fetch c_chk_tpl_hdr into l_fund_clist_hdr_id;
1048 l_fund_cl_hdr_notfound := c_chk_tpl_hdr%NOTFOUND;
1049 close c_chk_tpl_hdr;
1050
1051 ------------------------------------------------------------------------
1052 -- delete rule3
1053 -- 1. both old and new ID exists and there are different
1054 -- 2. old ID exists, but new ID missing
1055 ------------------------------------------------------------------------
1056 IF ( l_RULE_INFORMATION2 is not null AND
1057 (lp_rulv_rec.RULE_INFORMATION2 is not null AND
1058 lp_rulv_rec.RULE_INFORMATION2 <> l_RULE_INFORMATION2)
1059 OR
1060 lp_rulv_rec.RULE_INFORMATION2 is null
1061 ) THEN
1062
1063 ------------------------------------------------------------------------
1064 -- 3.3.1 get the source of the checklist template lists from OKL link
1065 -- 3.3.2 delete associated checklist
1066 ------------------------------------------------------------------------
1067 open c_del (lp_rulv_rec.RGP_ID,G_CREDIT_CHKLST_TPL_RULE3);
1068 LOOP
1069
1070 fetch c_del into l_rule_id;
1071 EXIT WHEN c_del%NOTFOUND;
1072
1073 ldel_rulv_rec.ID := l_rule_id;
1074
1075 okl_rule_pub.delete_rule(
1076 p_api_version => p_api_version,
1077 p_init_msg_list => p_init_msg_list,
1078 x_return_status => x_return_status,
1079 x_msg_count => x_msg_count,
1080 x_msg_data => x_msg_data,
1081 p_rulv_rec => ldel_rulv_rec);
1082
1083 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1084 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1085 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1086 raise OKC_API.G_EXCEPTION_ERROR;
1087 End If;
1088
1089 END LOOP;
1090 close c_del;
1091
1092 ------------------------------------------------------------------------
1093 -- delete funding checklist template header
1094 ------------------------------------------------------------------------
1095 ldel_rulv_rec.ID := l_fund_clist_hdr_id;
1096
1097 okl_rule_pub.delete_rule(
1098 p_api_version => p_api_version,
1099 p_init_msg_list => p_init_msg_list,
1100 x_return_status => x_return_status,
1101 x_msg_count => x_msg_count,
1102 x_msg_data => x_msg_data,
1103 p_rulv_rec => ldel_rulv_rec);
1104
1105 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1106 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1107 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1108 raise OKC_API.G_EXCEPTION_ERROR;
1109 End If;
1110
1111 END IF;
1112
1113 ------------------------------------------------------------------------
1114 -- create rule3
1115 ------------------------------------------------------------------------
1116 -- 1. both old and new ID exist and there are different
1117 -- 2. old ID missing, but new ID exists
1118 ------------------------------------------------------------------------
1119 IF (lp_rulv_rec.RULE_INFORMATION2 is not null AND
1120 (l_RULE_INFORMATION2 is not null AND
1121 lp_rulv_rec.RULE_INFORMATION2 <> l_RULE_INFORMATION2)
1122 OR
1123 l_RULE_INFORMATION2 is null
1124 ) THEN
1125
1126 ------------------------------------------------------------------------
1127 -- create funding checklist template header
1128 ------------------------------------------------------------------------
1129 open c_chk_tpl_hdr(lp_rulv_rec.DNZ_CHR_ID);
1130 fetch c_chk_tpl_hdr into l_fund_clist_hdr_id;
1131 l_fund_cl_hdr_notfound := c_chk_tpl_hdr%NOTFOUND;
1132 close c_chk_tpl_hdr;
1133
1134 IF (l_fund_cl_hdr_notfound) THEN
1135
1136 lpcrt3_rulv_rec.RGP_ID := lp_rulv_rec.RGP_ID;
1137 lpcrt3_rulv_rec.DNZ_CHR_ID := lp_rulv_rec.DNZ_CHR_ID;
1138
1139 lpcrt3_rulv_rec.RULE_INFORMATION_CATEGORY := G_CREDIT_CHKLST_TPL_RULE4;
1140 lpcrt3_rulv_rec.STD_TEMPLATE_YN := 'N';
1141 lpcrt3_rulv_rec.WARN_YN := 'N';
1142 -- lpcrt3_rulv_rec.RULE_INFORMATION1 := null -- effective from
1143 -- lpcrt3_rulv_rec.RULE_INFORMATION2 := null; -- effective to
1144 lpcrt3_rulv_rec.RULE_INFORMATION3 := G_STS_CODE;
1145
1146 okl_rule_pub.create_rule(
1147 p_api_version => p_api_version,
1148 p_init_msg_list => p_init_msg_list,
1149 x_return_status => x_return_status,
1150 x_msg_count => x_msg_count,
1151 x_msg_data => x_msg_data,
1152 p_rulv_rec => lpcrt3_rulv_rec,
1153 x_rulv_rec => lxcrt3_rulv_rec);
1154
1155 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1156 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1157 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1158 raise OKC_API.G_EXCEPTION_ERROR;
1159 End If;
1160
1161 -- assign to this variable for later
1162 l_fund_clist_hdr_id := lxcrt3_rulv_rec.ID;
1163
1164 ------------------------------------------------------------------------
1165 -- update funding checklist template header
1166 ------------------------------------------------------------------------
1167 ELSE
1168
1169 lpcrt3_rulv_rec.ID := l_fund_clist_hdr_id;
1170 -- lpcrt3_rulv_rec.RGP_ID := lp_rulv_rec.RGP_ID;
1171 -- lpcrt3_rulv_rec.DNZ_CHR_ID := lp_rulv_rec.DNZ_CHR_ID;
1172
1173 -- lpcrt3_rulv_rec.RULE_INFORMATION_CATEGORY := G_CREDIT_CHKLST_TPL_RULE4;
1174 -- lpcrt3_rulv_rec.STD_TEMPLATE_YN := 'N';
1175 -- lpcrt3_rulv_rec.WARN_YN := 'N';
1176 lpcrt3_rulv_rec.RULE_INFORMATION1 := null; -- effective from
1177 lpcrt3_rulv_rec.RULE_INFORMATION2 := null; -- effective to
1178 lpcrt3_rulv_rec.RULE_INFORMATION3 := G_STS_CODE;
1179 lpcrt3_rulv_rec.RULE_INFORMATION4 := null; -- note
1180
1181 okl_rule_pub.update_rule(
1182 p_api_version => p_api_version,
1183 p_init_msg_list => p_init_msg_list,
1184 x_return_status => x_return_status,
1185 x_msg_count => x_msg_count,
1186 x_msg_data => x_msg_data,
1187 p_rulv_rec => lpcrt3_rulv_rec,
1188 x_rulv_rec => lxcrt3_rulv_rec);
1189
1190 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1191 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1192 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1193 raise OKC_API.G_EXCEPTION_ERROR;
1194 End If;
1195
1196 END IF;
1197 ------------------------------------------------------------------------
1198 -- 3.4.1 get the source of the checklist template lists from NEW link
1199 -- 3.4.2 create rules based on #3.3 cursor
1200 ------------------------------------------------------------------------
1201 --start: 06-May-2005 cklee okl.h Lease App IA Authoring
1202 OPEN c_is_grp(TO_NUMBER(lp_rulv_rec.RULE_INFORMATION2));
1203 FETCH c_is_grp INTO l_dummy;
1204 l_is_grp_found := c_is_grp%FOUND;
1205 CLOSE c_is_grp;
1206 --end: 06-May-2005 cklee okl.h Lease App IA Authoring
1207
1208 -- is not a group template
1209 IF l_is_grp_found = false THEN
1210 --end: 06-May-2005 cklee okl.h Lease App IA Authoring
1211 open c_chk_tpl(to_number(lp_rulv_rec.RULE_INFORMATION2));
1212 LOOP
1213
1214 fetch c_chk_tpl into l_todo_item_code,
1215 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
1216 l_function_id,
1217 l_inst_checklist_type;
1218 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
1219
1220 EXIT WHEN c_chk_tpl%NOTFOUND;
1221
1222 lpcrt4_rulv_rec.RGP_ID := lp_rulv_rec.RGP_ID;
1223 lpcrt4_rulv_rec.DNZ_CHR_ID := lp_rulv_rec.DNZ_CHR_ID;
1224
1225 lpcrt4_rulv_rec.OBJECT1_ID1 := l_fund_clist_hdr_id;--lpcrt3_rulv_rec.ID; -- FK
1226 lpcrt4_rulv_rec.OBJECT1_ID2 := '#'; -- dummy one
1227
1228 lpcrt4_rulv_rec.RULE_INFORMATION_CATEGORY := G_CREDIT_CHKLST_TPL_RULE3;
1229 lpcrt4_rulv_rec.STD_TEMPLATE_YN := 'N';
1230 lpcrt4_rulv_rec.WARN_YN := 'N';
1231 lpcrt4_rulv_rec.RULE_INFORMATION1 := l_todo_item_code;
1232 lpcrt4_rulv_rec.RULE_INFORMATION2 := 'N';
1233 --Commented by cklee 10-May-2005 lpcrt4_rulv_rec.RULE_INFORMATION3 := 'N';
1234 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
1235 lpcrt4_rulv_rec.RULE_INFORMATION6 := l_function_id;
1236 lpcrt4_rulv_rec.RULE_INFORMATION7 := l_inst_checklist_type;
1237 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
1238
1239 okl_rule_pub.create_rule(
1240 p_api_version => p_api_version,
1241 p_init_msg_list => p_init_msg_list,
1242 x_return_status => x_return_status,
1243 x_msg_count => x_msg_count,
1244 x_msg_data => x_msg_data,
1245 p_rulv_rec => lpcrt4_rulv_rec,
1246 x_rulv_rec => lxcrt4_rulv_rec);
1247
1248 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1249 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1250 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1251 raise OKC_API.G_EXCEPTION_ERROR;
1252 End If;
1253
1254 END LOOP;
1255 CLOSE c_chk_tpl;
1256
1257 -- is not a group template
1258 -- IF l_is_grp_found = false THEN
1259 ELSE
1260 open c_grp_chk(to_number(lp_rulv_rec.RULE_INFORMATION2));
1261 LOOP
1262
1263 fetch c_grp_chk into l_todo_item_code,
1264 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
1265 l_function_id,
1266 l_inst_checklist_type;
1267 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
1268
1269 EXIT WHEN c_grp_chk%NOTFOUND;
1270
1271 lpcrt4_rulv_rec.RGP_ID := lp_rulv_rec.RGP_ID;
1272 lpcrt4_rulv_rec.DNZ_CHR_ID := lp_rulv_rec.DNZ_CHR_ID;
1273
1274 lpcrt4_rulv_rec.OBJECT1_ID1 := l_fund_clist_hdr_id;--lpcrt3_rulv_rec.ID; -- FK
1275 lpcrt4_rulv_rec.OBJECT1_ID2 := '#'; -- dummy one
1276
1277 lpcrt4_rulv_rec.RULE_INFORMATION_CATEGORY := G_CREDIT_CHKLST_TPL_RULE3;
1278 lpcrt4_rulv_rec.STD_TEMPLATE_YN := 'N';
1279 lpcrt4_rulv_rec.WARN_YN := 'N';
1280 lpcrt4_rulv_rec.RULE_INFORMATION1 := l_todo_item_code;
1281 lpcrt4_rulv_rec.RULE_INFORMATION2 := 'N';
1282 --Commented by cklee 10-May-2005 lpcrt4_rulv_rec.RULE_INFORMATION3 := 'N';
1283 --start: 06-May-2005 cklee okl.h Lease App IA Authoring |
1284 lpcrt4_rulv_rec.RULE_INFORMATION6 := l_function_id;
1285 lpcrt4_rulv_rec.RULE_INFORMATION7 := l_inst_checklist_type;
1286 --end: 06-May-2005 cklee okl.h Lease App IA Authoring |
1287
1288 okl_rule_pub.create_rule(
1289 p_api_version => p_api_version,
1290 p_init_msg_list => p_init_msg_list,
1291 x_return_status => x_return_status,
1292 x_msg_count => x_msg_count,
1293 x_msg_data => x_msg_data,
1294 p_rulv_rec => lpcrt4_rulv_rec,
1295 x_rulv_rec => lxcrt4_rulv_rec);
1296
1297 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1298 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1299 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1300 raise OKC_API.G_EXCEPTION_ERROR;
1301 End If;
1302
1303 END LOOP;
1304 CLOSE c_grp_chk;
1305 END IF;
1306
1307 END IF;
1308
1309 ------------------------------------------------------------------------
1310 -- 4 update rule1 : always update with the new IDs
1311 ------------------------------------------------------------------------
1312 -- credit line checklist template
1313 -- 1. both old ID and new ID exist and there are different
1314 -- 2. old ID missing, new ID exists
1315 -- funding checklist template
1316 -- 3. both old ID and new ID exist and there are different
1317 -- 4. old ID missing, new ID exists
1318 ------------------------------------------------------------------------
1319 okl_rule_pub.update_rule(
1320 p_api_version => p_api_version,
1321 p_init_msg_list => p_init_msg_list,
1322 x_return_status => x_return_status,
1323 x_msg_count => x_msg_count,
1324 x_msg_data => x_msg_data,
1325 p_rulv_rec => lp_rulv_rec,
1326 x_rulv_rec => lx_rulv_rec);
1327
1328 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1329 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1330 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1331 raise OKC_API.G_EXCEPTION_ERROR;
1332 End If;
1333
1334 END IF; -- end of IF ( lp_rulv_rec.RGP_ID is null...
1335
1336 /*** End API body ******************************************************/
1337
1338 -- Get message count and if count is 1, get message info
1339 FND_MSG_PUB.Count_And_Get
1340 (p_count => x_msg_count,
1341 p_data => x_msg_data);
1342
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1346 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1347 p_msg_name => G_UNEXPECTED_ERROR,
1348 p_token1 => G_SQLCODE_TOKEN,
1349 p_token1_value => SQLCODE,
1350 p_token2 => G_SQLERRM_TOKEN,
1351 p_token2_value => SQLERRM);
1352
1353 end update_credit_chklst_tpl;
1354
1355 --------------------------------------------------------------------------
1356 -- Validate Credit Checklist
1357 -- Description: Check checklist templates when activate a credit line
1358 --
1359 --------------------------------------------------------------------------
1360 FUNCTION validate_credit_checklist(
1361 p_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type
1362 ,p_rulv_rec okl_rule_pub.rulv_rec_type
1363 ,p_mode VARCHAR2
1364 ) RETURN VARCHAR2
1365 IS
1366 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1367 l_req_row_found boolean;
1368 l_grp_row_not_found boolean;
1369 l_active_row_not_found boolean;
1370 l_expired_row_found boolean;
1371 l_chklist_sts_row_found boolean;
1372 l_chklist_hdr_row_notfound boolean;
1373
1374 l_credit_checklist_tpl okc_rules_b.rule_information1%TYPE;
1375 l_funding_checklist_tpl okc_rules_b.rule_information2%TYPE;
1376 l_checklists_row_found boolean;
1377
1378 l_dummy number;
1379
1380 l_status okl_crd_fund_chklst_tpl_hdr_uv.status%TYPE;
1381 l_effective_to okl_crd_fund_chklst_tpl_hdr_uv.effective_to%TYPE;
1382
1383 --------------------------------------------------------------------------------------------
1384 --1 This is used for existing credit line and user try to activate credit line w/o activate
1385 -- checklists
1386 --------------------------------------------------------------------------------------------
1387 CURSOR c_chklst_header(p_chr_id okc_k_headers_b.id%type)
1388 IS
1389 select 1
1390 from okc_rule_groups_b rgp
1391 where rgp.dnz_chr_id = p_chr_id
1392 and rgp.RGD_CODE = G_CREDIT_CHKLST_TPL
1393 ;
1394
1395 --------------------------------------------------------------------------------------------
1396 --2. Credit line checklist must activated before activate credit line
1397 --------------------------------------------------------------------------------------------
1398 CURSOR c_chklst_sts (p_chr_id okc_k_headers_b.id%type)
1399 IS
1400 --start modified abhsaxen for performance SQLID 20562641
1401 select 1
1402 from OKC_RULES_B RULT
1403 where rult.DNZ_CHR_ID = p_chr_id and
1404 nvl(rult.RULE_INFORMATION5, 'NEW') <> 'ACTIVE'and
1405 rult.rule_information_category = 'LACCLD'
1406 --end modified abhsaxen for performance SQLID 20562641
1407 ;
1408 --------------------------------------------------------------------------------------------
1409 --3.1 Funding request checklist template must activated before activate credit line
1410 --------------------------------------------------------------------------------------------
1411 --------------------------------------------------------------------------------------------
1412 --3.2 Funding request checklist template must not expired before activate credit line
1413 --------------------------------------------------------------------------------------------
1414 CURSOR c_fund_chklst_sts (p_chr_id okc_k_headers_b.id%type)
1415 IS
1416 select chk.status,
1417 chk.effective_to
1418 from okl_crd_fund_chklst_tpl_hdr_uv chk
1419 where chk.khr_id = p_chr_id
1420 ;
1421
1422 --------------------------------------------------------------------------------------------
1423 --4. Credit line checklist has not met requirement before activate credit line
1424 --------------------------------------------------------------------------------------------
1425 CURSOR c_chklst (p_chr_id okc_k_headers_b.id%type)
1426 IS
1427 select 1
1428 from okc_rules_b rult
1429 where rult.rule_information_category = G_CREDIT_CHKLST_TPL_RULE2--'LACCLD'
1430 and rult.dnz_chr_id = p_chr_id
1431 and rult.RULE_INFORMATION2 = 'Y'
1432 and (rult.RULE_INFORMATION3 <> 'Y' or rult.RULE_INFORMATION3 is null)
1433 ;
1434
1435 --------------------------------------------------------------------------------------------
1436 -- credit line status
1437 --------------------------------------------------------------------------------------------
1438 CURSOR c_active (p_chr_id okc_k_headers_b.id%type)
1439 IS
1440 select 1
1441 from okc_k_headers_b k
1442 where k.id = p_chr_id
1443 and k.sts_code = 'ACTIVE'
1444 ;
1445
1446 --------------------------------------------------------------------------------------------
1447 -- Checklists link check
1448 --------------------------------------------------------------------------------------------
1449 CURSOR c_checklists (p_chr_id NUMBER)
1450 IS
1451 select rule.rule_information1,
1452 rule.rule_information2
1453 from okc_rules_b rule
1454 where rule.dnz_chr_id = p_chr_id
1455 and rule.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1
1456 ;
1457
1458
1459 BEGIN
1460
1461 OPEN c_checklists(p_chrv_rec.id);
1462 FETCH c_checklists INTO l_credit_checklist_tpl,
1463 l_funding_checklist_tpl;
1464 l_checklists_row_found := c_checklists%FOUND;
1465 CLOSE c_checklists;
1466
1467 OPEN c_active(p_chrv_rec.id);
1468 FETCH c_active INTO l_dummy;
1469 l_active_row_not_found := c_active%NOTFOUND;
1470 CLOSE c_active;
1471
1472 -- check only when record becomes ACTIVE at 1st time. we don't check once sts_code becomes active
1473 -- start: 06-May-2005 cklee okl.h Lease App IA Authoring |
1474 IF (p_chrv_rec.sts_code IN ('SUBMITTED', 'ACTIVE') AND l_active_row_not_found) THEN
1475 -- end: 06-May-2005 cklee okl.h Lease App IA Authoring |
1476
1477 --------------------------------------------------------------------------------------------
1478 --1 This is used for existing credit line and user try to activate credit line w/o activate
1479 -- checklists (user select all or one of the checklist template, but has not been updated credit line 1st)
1480 --------------------------------------------------------------------------------------------
1481 OPEN c_chklst_header(p_chrv_rec.id);
1482 FETCH c_chklst_header INTO l_dummy;
1483 l_chklist_hdr_row_notfound := c_chklst_header%NOTFOUND;
1484 CLOSE c_chklst_header;
1485
1486 IF (l_chklist_hdr_row_notfound
1487 AND (p_rulv_rec.RULE_INFORMATION1 is not null OR
1488 p_rulv_rec.RULE_INFORMATION2 is not null)) THEN
1489 -- Credit line checklists not found. Please update credit line and setup checklists before activate credit line.
1490 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1491 p_msg_name => 'OKL_LLA_CREDIT_CHKLST1');
1492
1493 RAISE G_EXCEPTION_HALT_VALIDATION;
1494 END IF;
1495
1496 --------------------------------------------------------------------------------------------
1497 --2. Credit line checklist must activated before activate credit line
1498 --------------------------------------------------------------------------------------------
1499 OPEN c_chklst_sts(p_chrv_rec.id);
1500 FETCH c_chklst_sts INTO l_dummy;
1501 l_chklist_sts_row_found := c_chklst_sts%FOUND;
1502 CLOSE c_chklst_sts;
1503
1504 -- 2. checklist has not been activate yet
1505 IF (l_credit_checklist_tpl IS NOT NULL and l_chklist_sts_row_found) THEN
1506 -- Credit line checklist status is new. Please activate credit line checklist before activate credit line.
1507 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1508 p_msg_name => 'OKL_LLA_CREDIT_CHKLST3');
1509
1510 RAISE G_EXCEPTION_HALT_VALIDATION;
1511 END IF;
1512
1513 --------------------------------------------------------------------------------------------
1514 --3.1 Funding request checklist template must activated before activate credit line
1515 --------------------------------------------------------------------------------------------
1516 OPEN c_fund_chklst_sts(p_chrv_rec.id);
1517 FETCH c_fund_chklst_sts INTO l_status,
1518 l_effective_to;
1519 CLOSE c_fund_chklst_sts;
1520
1521 -- 3.1 funding checklist template has not been activate yet
1522 IF (l_funding_checklist_tpl IS NOT NULL and l_status <> 'ACTIVE') THEN
1523 -- Funding request checklist template status is new.
1524 -- Please activate Funding request checklist template before activate credit line.
1525 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1526 p_msg_name => 'OKL_LLA_FUND_CHKLST_CHECK5');
1527
1528 RAISE G_EXCEPTION_HALT_VALIDATION;
1529 END IF;
1530
1531 --------------------------------------------------------------------------------------------
1532 --3.2 Funding request checklist template must not expired before activate credit line
1533 --------------------------------------------------------------------------------------------
1534 -- 3.2 funding checklist template expired.
1535 IF (l_funding_checklist_tpl IS NOT NULL and trunc(l_effective_to) < trunc(SYSDATE)) THEN
1536 -- Funding request checklist template expired. Please modify effective date of Funding request checklist template.
1537 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1538 p_msg_name => 'OKL_LLA_FUND_CHKLST_CHECK6');
1539
1540
1541 RAISE G_EXCEPTION_HALT_VALIDATION;
1542 END IF;
1543
1544 -- start: 06-May-2005 cklee okl.h Lease App IA Authoring |
1545 /* check @ WF
1546 OPEN c_chklst(p_chrv_rec.id);
1547 FETCH c_chklst INTO l_dummy;
1548 l_req_row_found := c_chklst%FOUND;
1549 CLOSE c_chklst;
1550
1551 --------------------------------------------------------------------------------------------
1552 --4. Credit line checklist has not met requirement before activate credit line
1553 --------------------------------------------------------------------------------------------
1554 -- 4. all required items have not met requirement
1555 IF (l_credit_checklist_tpl IS NOT NULL and l_req_row_found) THEN
1556 -- Credit line has not met all checklist items. Please check off all mandatory checklist items.
1557 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1558 p_msg_name => 'OKL_LLA_CREDIT_CHKLST');
1559
1560 RAISE G_EXCEPTION_HALT_VALIDATION;
1561 END IF;
1562 */
1563 -- end: 06-May-2005 cklee okl.h Lease App IA Authoring |
1564
1565 END IF;
1566
1567
1568 RETURN l_return_status;
1569
1570 EXCEPTION
1571 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1572 l_return_status := OKL_API.G_RET_STS_ERROR;
1573 RETURN l_return_status;
1574 WHEN OTHERS THEN
1575 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1576 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1577 p_msg_name => G_UNEXPECTED_ERROR,
1578 p_token1 => G_SQLCODE_TOKEN,
1579 p_token1_value => SQLCODE,
1580 p_token2 => G_SQLERRM_TOKEN,
1581 p_token2_value => SQLERRM);
1582 RETURN l_return_status;
1583 END;
1584
1585 ---------------------------------------------------------------
1586 -- validate credit nature after row have been insert or update
1587 -- in DB
1588 ---------------------------------------------------------------
1589 FUNCTION validate_credit_limit_after(
1590 p_chr_id IN NUMBER,
1591 p_mode IN VARCHAR2
1592 ) RETURN VARCHAR2
1593 IS
1594 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1595
1596 l_msg_name VARCHAR2(200);
1597 l_dummy_n number;
1598 l_dummy VARCHAR2(1) := '?';
1599 l_dup BOOLEAN := false;
1600 l_date_check1 BOOLEAN := false;
1601 l_date_check2 BOOLEAN := false;
1602 l_amount_check1 BOOLEAN := false;
1603 l_amount_check2 BOOLEAN := false;
1604
1605
1606 l_not_exists BOOLEAN := false;
1607
1608 l_api_version NUMBER := 1.0;
1609 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
1610 x_return_status VARCHAR2(1);
1611 x_msg_count NUMBER;
1612 x_msg_data VARCHAR2(2000);
1613 x_value NUMBER := 0;
1614 l_amount NUMBER;
1615
1616
1617 -- line amount check 1: Can not be negative
1618 CURSOR c_amount_check1 (p_contract_id NUMBER)
1619 IS
1620 select 1
1621 from OKL_K_LINES kln,
1622 OKC_K_LINES_B cln
1623 where kln.id = cln.id
1624 and cln.dnz_chr_id = p_contract_id
1625 and kln.amount < 0
1626 ;
1627
1628 -- line amount check 2: Can not be Zero
1629 CURSOR c_amount_check2 (p_contract_id NUMBER)
1630 IS
1631 select 1
1632 from OKL_K_LINES kln,
1633 OKC_K_LINES_B cln
1634 where kln.id = cln.id
1635 and cln.dnz_chr_id = p_contract_id
1636 and kln.amount = 0
1637 ;
1638
1639 -- New limit exsiting check
1640 CURSOR c_not_exists (p_contract_id NUMBER)
1641 IS
1642 select 'X'
1643 from OKL_K_LINES kln,
1644 OKC_K_LINES_B cln
1645 where kln.id = cln.id
1646 and cln.dnz_chr_id = p_contract_id
1647 and kln.CREDIT_NATURE = 'NEW'
1648 ;
1649
1650 -- New limit duplication check
1651 CURSOR c_dup (p_contract_id NUMBER)
1652 IS
1653 select cln.dnz_chr_id
1654 from OKL_K_LINES kln,
1655 OKC_K_LINES_B cln
1656 where kln.id = cln.id
1657 and cln.dnz_chr_id = p_contract_id
1658 and kln.CREDIT_NATURE = 'NEW'
1659 group by cln.dnz_chr_id
1660 having count(1) > 1
1661 ;
1662
1663 -- line start date < header start date
1664 CURSOR c_date_check1 (p_chr_id NUMBER)
1665 IS
1666 SELECT 1
1667 FROM okc_k_headers_b chr,
1668 okc_k_lines_b cln
1669 WHERE chr.id = cln.dnz_chr_id
1670 AND chr.start_date IS NOT NULL
1671 AND trunc(cln.start_date) < trunc(chr.start_date)
1672 AND chr.id = p_chr_id
1673 ;
1674
1675 -- line start date > header end date
1676 CURSOR c_date_check2 (p_chr_id NUMBER)
1677 IS
1678 SELECT 1
1679 FROM okc_k_headers_b chr,
1680 okc_k_lines_b cln
1681 WHERE chr.id = cln.dnz_chr_id
1682 AND chr.end_date IS NOT NULL
1683 AND trunc(cln.start_date) > trunc(chr.end_date)
1684 AND chr.id = p_chr_id
1685 ;
1686
1687 BEGIN
1688
1689 --------------------------------------------------
1690 -- duplication check
1691 --------------------------------------------------
1692 OPEN c_dup(p_chr_id);
1693 FETCH c_dup INTO l_dummy_n;
1694 l_dup := c_dup%FOUND;
1695 CLOSE c_dup;
1696
1697 IF ( l_dup ) THEN
1698
1699 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1700 p_msg_name => 'OKL_LLA_NOT_UNIQUE',
1701 p_token1 => 'COL_NAME',
1702 p_token1_value => 'New Limit');
1703 RAISE G_EXCEPTION_HALT_VALIDATION;
1704
1705 END IF;
1706
1707 --------------------------------------------------
1708 -- new limit check
1709 --------------------------------------------------
1710 OPEN c_not_exists(p_chr_id);
1711 FETCH c_not_exists INTO l_dummy;
1712 l_not_exists := c_not_exists%NOTFOUND;
1713 CLOSE c_not_exists;
1714
1715 IF ( l_not_exists ) THEN
1716
1717 IF (p_mode = 'NEW') THEN
1718 l_msg_name := 'OKL_LLA_CREDIT_LIMIT_CHECK1';
1719 ELSIF (p_mode = 'DELETE') THEN
1720 l_msg_name := 'OKL_LLA_CREDIT_LIMIT_CHECK2';
1721 ELSIF (p_mode = 'UPDATE') THEN
1722 l_msg_name := 'OKL_LLA_CREDIT_LIMIT_CHECK3';
1723 END IF;
1724
1725 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1726 p_msg_name => l_msg_name);
1727 RAISE G_EXCEPTION_HALT_VALIDATION;
1728
1729 END IF;
1730
1731 --------------------------------------------------
1732 -- date range check 1
1733 --------------------------------------------------
1734 OPEN c_date_check1(p_chr_id);
1735 FETCH c_date_check1 INTO l_dummy_n;
1736 l_date_check1 := c_date_check1%FOUND;
1737 CLOSE c_date_check1;
1738
1739 IF ( l_date_check1 ) THEN
1740
1741 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1742 p_msg_name => 'OKL_LLA_RANGE_CHECK2',
1743 p_token1 => 'COL_NAME1',
1744 p_token1_value => 'Effective From',
1745 p_token2 => 'COL_NAME2',
1746 p_token2_value => 'Effective From of Credit Line');
1747 RAISE G_EXCEPTION_HALT_VALIDATION;
1748
1749 END IF;
1750
1751 --------------------------------------------------
1752 -- date range check 2
1753 --------------------------------------------------
1754 OPEN c_date_check2(p_chr_id);
1755 FETCH c_date_check2 INTO l_dummy_n;
1756 l_date_check2 := c_date_check2%FOUND;
1757 CLOSE c_date_check2;
1758
1759 IF ( l_date_check2 ) THEN
1760
1761 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1762 p_msg_name => 'OKL_LESS_THAN',
1763 p_token1 => 'COL_NAME1',
1764 p_token1_value => 'Effective From',
1765 p_token2 => 'COL_NAME2',
1766 p_token2_value => 'Effective To of Credit Line');
1767 RAISE G_EXCEPTION_HALT_VALIDATION;
1768
1769 END IF;
1770
1771 --------------------------------------------------
1772 -- line amount check 1: Can not be negative
1773 --------------------------------------------------
1774 OPEN c_amount_check1(p_chr_id);
1775 FETCH c_amount_check1 INTO l_dummy_n;
1776 l_amount_check1 := c_amount_check1%FOUND;
1777 CLOSE c_amount_check1;
1778
1779 IF ( l_amount_check1 ) THEN
1780
1781 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1782 p_msg_name => 'OKL_LLA_POSITIVE_AMOUNT_ONLY',
1783 p_token1 => 'COL_NAME',
1784 p_token1_value => 'Amount');
1785 RAISE G_EXCEPTION_HALT_VALIDATION;
1786
1787 END IF;
1788
1789 --------------------------------------------------
1790 -- line amount check 2: Can not be zero
1791 --------------------------------------------------
1792 OPEN c_amount_check2(p_chr_id);
1793 FETCH c_amount_check2 INTO l_dummy_n;
1794 l_amount_check2 := c_amount_check2%FOUND;
1795 CLOSE c_amount_check2;
1796
1797 IF ( l_amount_check2 ) THEN
1798
1799 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1800 p_msg_name => 'OKL_LLA_AMOUNT_CHECK');
1801 RAISE G_EXCEPTION_HALT_VALIDATION;
1802
1803 END IF;
1804
1805 --------------------------------------------------
1806 -- Credit limt Remaining check
1807 --------------------------------------------------
1808 OKL_EXECUTE_FORMULA_PUB.execute(
1809 p_api_version => l_api_version,
1810 p_init_msg_list => l_init_msg_list,
1811 x_return_status => x_return_status,
1812 x_msg_count => x_msg_count,
1813 x_msg_data => x_msg_data,
1814 p_formula_name => 'CONTRACT_TOT_CRDT_LMT',
1815 p_contract_id => p_chr_id,
1816 x_value => x_value);
1817
1818 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1819 --RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1820 x_value := 0;
1821 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1822 --RAISE OKL_API.G_EXCEPTION_ERROR;
1823 x_value := 0;
1824 END IF;
1825
1826 l_amount := x_value;
1827
1828 IF (l_amount < 0 ) THEN
1829
1830 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1831 p_msg_name => 'OKL_LLA_CREDIT_LIMIT_CHECK');
1832 RAISE G_EXCEPTION_HALT_VALIDATION;
1833
1834 END IF;
1835
1836
1837 RETURN l_return_status;
1838 EXCEPTION
1839 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1840 l_return_status := OKL_API.G_RET_STS_ERROR;
1841 RETURN l_return_status;
1842 WHEN OTHERS THEN
1843 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1844 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1845 p_msg_name => G_UNEXPECTED_ERROR,
1846 p_token1 => G_SQLCODE_TOKEN,
1847 p_token1_value => SQLCODE,
1848 p_token2 => G_SQLERRM_TOKEN,
1849 p_token2_value => SQLERRM);
1850 RETURN l_return_status;
1851 END;
1852 --------------------------------------------------------------------------
1853 ----- Validate credit nature: NEW LIMIT
1854 ----- only check @ create mode
1855 ----- UI will skip delete or modify the NEW LIMIT record
1856 --------------------------------------------------------------------------
1857 FUNCTION validate_credit_nature(
1858 p_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type
1859 ,p_klev_rec OKL_CONTRACT_PVT.klev_rec_type
1860 ) RETURN VARCHAR2
1861 IS
1862 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1863 l_existing_status VARCHAR2(1) := '?';
1864
1865 CURSOR c (p_contract_id NUMBER)
1866 IS
1867 select 'X'
1868 from OKL_K_LINES_FULL_V a
1869 where a.dnz_chr_id = p_contract_id
1870 and a.CREDIT_NATURE = 'NEW'
1871 ;
1872
1873 BEGIN
1874
1875 -- start: cklee 03/24/2004
1876 -- credit_nature is required:
1877 IF (p_klev_rec.credit_nature IS NULL) THEN
1878 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1879 p_msg_name => G_REQUIRED_VALUE,
1880 p_token1 => G_COL_NAME_TOKEN,
1881 p_token1_value => 'Credit Nature');
1882 RAISE G_EXCEPTION_HALT_VALIDATION;
1883 END IF;
1884 -- end: cklee 03/24/2004
1885
1886 RETURN l_return_status;
1887 EXCEPTION
1888
1889 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1890 l_return_status := OKL_API.G_RET_STS_ERROR;
1891 RETURN l_return_status;
1892 WHEN OTHERS THEN
1893 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1894 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1895 p_msg_name => G_UNEXPECTED_ERROR,
1896 p_token1 => G_SQLCODE_TOKEN,
1897 p_token1_value => SQLCODE,
1898 p_token2 => G_SQLERRM_TOKEN,
1899 p_token2_value => SQLERRM);
1900 RETURN l_return_status;
1901 END;
1902
1903 --------------------------------------------------------------------------
1904 ----- Validate credit limit amount
1905 --------------------------------------------------------------------------
1906 FUNCTION validate_amount(
1907 p_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type
1908 ,p_klev_rec OKL_CONTRACT_PVT.klev_rec_type
1909 ) RETURN VARCHAR2
1910 IS
1911 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1912 BEGIN
1913
1914 -- start: cklee 03/24/2004
1915 -- amount is required:
1916 IF (p_klev_rec.amount IS NULL) THEN
1917 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1918 p_msg_name => G_REQUIRED_VALUE,
1919 p_token1 => G_COL_NAME_TOKEN,
1920 p_token1_value => 'Amount');
1921 RAISE G_EXCEPTION_HALT_VALIDATION;
1922 END IF;
1923 -- end: cklee 03/24/2004
1924
1925 IF (p_klev_rec.amount IS NOT NULL AND
1926 p_klev_rec.amount <> OKL_API.G_MISS_NUM)
1927 THEN
1928
1929 IF (p_klev_rec.amount < 0 ) THEN
1930
1931 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1932 p_msg_name => 'OKL_LLA_POSITIVE_AMOUNT_ONLY',
1933 p_token1 => 'COL_NAME',
1934 p_token1_value => 'Amount');
1935
1936 RAISE G_EXCEPTION_HALT_VALIDATION;
1937 END IF;
1938
1939 IF (p_klev_rec.amount = 0 ) THEN
1940
1941 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1942 p_msg_name => 'OKL_LLA_AMOUNT_CHECK');
1943
1944 RAISE G_EXCEPTION_HALT_VALIDATION;
1945 END IF;
1946
1947 END IF;
1948
1949 RETURN l_return_status;
1950 EXCEPTION
1951 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1952 l_return_status := OKL_API.G_RET_STS_ERROR;
1953 RETURN l_return_status;
1954 WHEN OTHERS THEN
1955 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1956 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1957 p_msg_name => G_UNEXPECTED_ERROR,
1958 p_token1 => G_SQLCODE_TOKEN,
1959 p_token1_value => SQLCODE,
1960 p_token2 => G_SQLERRM_TOKEN,
1961 p_token2_value => SQLERRM);
1962 RETURN l_return_status;
1963 END;
1964 --------------------------------------------------------------------------
1965 ----- Validate Credit Limit start date
1966 --------------------------------------------------------------------------
1967 FUNCTION validate_start_date(
1968 p_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type
1969 ,p_klev_rec OKL_CONTRACT_PVT.klev_rec_type
1970 ) RETURN VARCHAR2
1971 IS
1972 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1973 l_header_start_date DATE;
1974 l_header_end_date DATE;
1975
1976 CURSOR c (p_chr_id NUMBER)
1977 IS
1978 SELECT k.start_date,
1979 k.end_date
1980 FROM okc_k_headers_b k
1981 WHERE k.id = p_chr_id
1982 ;
1983
1984 BEGIN
1985
1986 -- start: cklee 03/24/2004
1987 -- start date is required:
1988 IF (p_clev_rec.start_date IS NULL) THEN
1989 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1990 p_msg_name => G_REQUIRED_VALUE,
1991 p_token1 => G_COL_NAME_TOKEN,
1992 p_token1_value => 'Effective From');
1993 RAISE G_EXCEPTION_HALT_VALIDATION;
1994 END IF;
1995 -- end: cklee 03/24/2004
1996
1997 OPEN c(p_clev_rec.dnz_chr_id);
1998 FETCH c INTO l_header_start_date,
1999 l_header_end_date;
2000 CLOSE c;
2001
2002 IF (p_clev_rec.start_date IS NOT NULL AND
2003 p_clev_rec.start_date <> OKL_API.G_MISS_DATE)
2004 THEN
2005
2006 IF (l_header_start_date IS NOT NULL) THEN
2007 IF (trunc(p_clev_rec.start_date) < trunc(l_header_start_date)) THEN
2008
2009 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2010 p_msg_name => 'OKL_LLA_RANGE_CHECK2',
2011 p_token1 => 'COL_NAME1',
2012 p_token1_value => 'Effective From',
2013 p_token2 => 'COL_NAME2',
2014 p_token2_value => 'Effective From of Credit Line');
2015 RAISE G_EXCEPTION_HALT_VALIDATION;
2016 END IF;
2017 END IF;
2018
2019 IF (l_header_end_date IS NOT NULL) THEN
2020 IF (trunc(p_clev_rec.start_date) > trunc(l_header_end_date)) THEN
2021
2022 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2023 p_msg_name => 'OKL_LESS_THAN',
2024 p_token1 => 'COL_NAME1',
2025 p_token1_value => 'Effective From',
2026 p_token2 => 'COL_NAME2',
2027 p_token2_value => 'Effective To of Credit Line');
2028 RAISE G_EXCEPTION_HALT_VALIDATION;
2029 END IF;
2030 END IF;
2031
2032 END IF;
2033
2034 RETURN l_return_status;
2035 EXCEPTION
2036 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2037 l_return_status := OKL_API.G_RET_STS_ERROR;
2038 RETURN l_return_status;
2039 WHEN OTHERS THEN
2040 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2041 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2042 p_msg_name => G_UNEXPECTED_ERROR,
2043 p_token1 => G_SQLCODE_TOKEN,
2044 p_token1_value => SQLCODE,
2045 p_token2 => G_SQLERRM_TOKEN,
2046 p_token2_value => SQLERRM);
2047 RETURN l_return_status;
2048
2049 END;
2050
2051 --------------------------------------------------------------------------
2052 ----- Validate Description
2053 --------------------------------------------------------------------------
2054 FUNCTION validate_description(
2055 p_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type
2056 ) RETURN VARCHAR2
2057 IS
2058 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2059 BEGIN
2060
2061 IF (p_chrv_rec.description IS NOT NULL AND
2062 p_chrv_rec.description <> OKL_API.G_MISS_CHAR)
2063 THEN
2064
2065 IF (length(p_chrv_rec.description) > 600) THEN
2066
2067 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2068 p_msg_name => 'OKL_LLA_EXCEED_MAXIMUM_LENGTH',
2069 p_token1 => 'MAX_CHARS',
2070 p_token1_value => '600',
2071 p_token2 => 'COL_NAME',
2072 p_token2_value => 'Description');
2073
2074 RAISE G_EXCEPTION_HALT_VALIDATION;
2075 END IF;
2076 END IF;
2077
2078 RETURN l_return_status;
2079 EXCEPTION
2080 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2081 l_return_status := OKL_API.G_RET_STS_ERROR;
2082 RETURN l_return_status;
2083 WHEN OTHERS THEN
2084 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2085 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2086 p_msg_name => G_UNEXPECTED_ERROR,
2087 p_token1 => G_SQLCODE_TOKEN,
2088 p_token1_value => SQLCODE,
2089 p_token2 => G_SQLERRM_TOKEN,
2090 p_token2_value => SQLERRM);
2091 RETURN l_return_status;
2092 END;
2093
2094 --------------------------------------------------------------------------
2095 ----- Validate Credit Number uniqueness check
2096 --------------------------------------------------------------------------
2097 FUNCTION validate_credit_number(
2098 p_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type
2099 ,p_mode VARCHAR2
2100 ) RETURN VARCHAR2
2101 IS
2102 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2103 l_dummy VARCHAR2(1) := '?';
2104
2105 CURSOR c (p_credit_number VARCHAR2)
2106 IS
2107 SELECT 'X'
2108 FROM okc_k_headers_b k
2109 WHERE k.contract_number = p_credit_number
2110 -- bug fixed for creditline contarct sub-class
2111 -- and k.scs_code = 'CREDITLINE_CONTRACT'
2112 ;
2113
2114
2115 CURSOR c2 (p_credit_number VARCHAR2, p_id NUMBER)
2116 IS
2117 SELECT 'X'
2118 FROM okc_k_headers_b k
2119 WHERE k.contract_number = p_credit_number
2120 AND k.id <> p_id -- except itself
2121 ;
2122
2123 BEGIN
2124
2125 -- check only if credit number exists
2126 IF (p_chrv_rec.contract_number IS NOT NULL AND
2127 p_chrv_rec.contract_number <> OKL_API.G_MISS_CHAR)
2128 THEN
2129
2130 IF (p_mode = 'C') THEN
2131 OPEN c(p_chrv_rec.contract_number);
2132 FETCH c INTO l_dummy;
2133 CLOSE c;
2134 ELSIF (p_mode = 'U') THEN
2135 OPEN c2(p_chrv_rec.contract_number, p_chrv_rec.id);
2136 FETCH c2 INTO l_dummy;
2137 CLOSE c2;
2138 END IF;
2139
2140 IF (l_dummy = 'X')
2141 THEN
2142 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2143 p_msg_name => 'OKL_CONTRACT_EXISTS',
2144 p_token1 => 'COL_NAME',
2145 p_token1_value => p_chrv_rec.contract_number);
2146
2147 RAISE G_EXCEPTION_HALT_VALIDATION;
2148 END IF;
2149
2150 END IF;
2151
2152 RETURN l_return_status;
2153
2154 EXCEPTION
2155 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2156 l_return_status := OKL_API.G_RET_STS_ERROR;
2157 RETURN l_return_status;
2158 WHEN OTHERS THEN
2159 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2160 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2161 p_msg_name => G_UNEXPECTED_ERROR,
2162 p_token1 => G_SQLCODE_TOKEN,
2163 p_token1_value => SQLCODE,
2164 p_token2 => G_SQLERRM_TOKEN,
2165 p_token2_value => SQLERRM);
2166 RETURN l_return_status;
2167 END;
2168 --------------------------------------------------------------------------
2169 ----- Validate Effective From
2170 --------------------------------------------------------------------------
2171 FUNCTION validate_start_date(
2172 p_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type
2173 ,p_mode VARCHAR2
2174 ) RETURN VARCHAR2
2175 IS
2176 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2177 l_min_start_date DATE;
2178
2179 l_row_found boolean := false;
2180
2181 CURSOR c (p_contract_id NUMBER)
2182 IS
2183 -- cklee 12-10-2003 fixed sql performance
2184 select min(start_date)
2185 from OKC_K_LINES_B a
2186 where a.dnz_chr_id = p_contract_id
2187 ;
2188
2189 BEGIN
2190
2191 -- start date is required:
2192 IF (p_chrv_rec.start_date IS NULL) OR
2193 (p_chrv_rec.start_date = OKL_API.G_MISS_DATE)
2194 THEN
2195 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2196 p_msg_name => G_REQUIRED_VALUE,
2197 p_token1 => G_COL_NAME_TOKEN,
2198 p_token1_value => 'Effective From');
2199 RAISE G_EXCEPTION_HALT_VALIDATION;
2200 END IF;
2201
2202 IF (p_mode = 'U') THEN
2203
2204 -- get credit limit minimum start date
2205 OPEN c(p_chrv_rec.id);
2206 FETCH c INTO l_min_start_date;
2207 l_row_found := c%FOUND;
2208 CLOSE c;
2209
2210 --
2211 -- cklee 30-OCT-2002 check only when credit limit exists
2212 --
2213 IF (l_row_found AND
2214 trunc(p_chrv_rec.start_date) > trunc(l_min_start_date))
2215 THEN
2216 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2217 p_msg_name => 'OKL_LESS_THAN',
2218 p_token1 => 'COL_NAME1',
2219 p_token1_value => 'Effective From',
2220 p_token2 => 'COL_NAME2',
2221 p_token2_value => 'Effective From of Credit Limit');
2222 RAISE G_EXCEPTION_HALT_VALIDATION;
2223 END IF;
2224 END IF;
2225
2226 RETURN l_return_status;
2227 EXCEPTION
2228 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2229 l_return_status := OKL_API.G_RET_STS_ERROR;
2230 RETURN l_return_status;
2231 WHEN OTHERS THEN
2232 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2233 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2234 p_msg_name => G_UNEXPECTED_ERROR,
2235 p_token1 => G_SQLCODE_TOKEN,
2236 p_token1_value => SQLCODE,
2237 p_token2 => G_SQLERRM_TOKEN,
2238 p_token2_value => SQLERRM);
2239 RETURN l_return_status;
2240 END;
2241 --------------------------------------------------------------------------
2242 ----- Validate Effective To
2243 --------------------------------------------------------------------------
2244 FUNCTION validate_end_date(
2245 p_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type
2246 ,p_mode VARCHAR2
2247 ) RETURN VARCHAR2
2248 IS
2249 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2250 l_max_start_date DATE;
2251 l_row_found boolean := false;
2252
2253 -- 11-19-2003 cklee fixed sql performance bug
2254 CURSOR c2 (p_contract_id NUMBER)
2255 IS
2256 select max(start_date)
2257 from OKC_K_LINES_B a
2258 where a.dnz_chr_id = p_contract_id
2259 ;
2260
2261 BEGIN
2262
2263 -- end date is required:
2264 IF (p_chrv_rec.end_date IS NULL) OR
2265 (p_chrv_rec.end_date = OKL_API.G_MISS_DATE)
2266 THEN
2267 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2268 p_msg_name => G_REQUIRED_VALUE,
2269 p_token1 => G_COL_NAME_TOKEN,
2270 p_token1_value => 'Effective To');
2271 RAISE G_EXCEPTION_HALT_VALIDATION;
2272 END IF;
2273
2274 IF (trunc(p_chrv_rec.end_date) < trunc(sysdate))
2275 THEN
2276 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2277 p_msg_name => 'OKL_LLA_RANGE_CHECK',
2278 p_token1 => 'COL_NAME1',
2279 p_token1_value => 'Effective To',
2280 p_token2 => 'COL_NAME2',
2281 p_token2_value => 'today');
2282
2283 RAISE G_EXCEPTION_HALT_VALIDATION;
2284 END IF;
2285
2286 IF (p_mode = 'U') THEN
2287
2288 -- get credit limit maximum start date
2289 OPEN c2(p_chrv_rec.id);
2290 FETCH c2 INTO l_max_start_date;
2291 l_row_found := c2%FOUND;
2292 CLOSE c2;
2293
2294 --
2295 -- cklee 30-OCT-2002 check only when credit limit exists
2296 --
2297 IF (l_row_found AND
2298 trunc(p_chrv_rec.end_date) < trunc(l_max_start_date))
2299 THEN
2300 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2301 p_msg_name => 'OKL_LLA_RANGE_CHECK',
2302 p_token1 => 'COL_NAME1',
2303 p_token1_value => 'Effective To',
2304 p_token2 => 'COL_NAME2',
2305 p_token2_value => 'Effective From of Credit Limit');
2306
2307 RAISE G_EXCEPTION_HALT_VALIDATION;
2308 END IF;
2309 END IF;
2310
2311 RETURN l_return_status;
2312 EXCEPTION
2313 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2314 l_return_status := OKL_API.G_RET_STS_ERROR;
2315 RETURN l_return_status;
2316 WHEN OTHERS THEN
2317 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2318 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2319 p_msg_name => G_UNEXPECTED_ERROR,
2320 p_token1 => G_SQLCODE_TOKEN,
2321 p_token1_value => SQLCODE,
2322 p_token2 => G_SQLERRM_TOKEN,
2323 p_token2_value => SQLERRM);
2324 RETURN l_return_status;
2325 END;
2326 --------------------------------------------------------------------------
2327 ----- Validate Effective From and Effective To
2328 --------------------------------------------------------------------------
2329 FUNCTION validate_start_end_date(
2330
2331 p_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type
2332 ,p_mode VARCHAR2
2333 ) RETURN VARCHAR2
2334 IS
2335 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2336 l_header_start_date DATE;
2337
2338 CURSOR c (p_chr_id NUMBER)
2339 IS
2340 SELECT k.start_date
2341 FROM okc_k_headers_b k
2342 WHERE k.id = p_chr_id
2343 ;
2344
2345 BEGIN
2346
2347 -- start: 06-May-2005 cklee okl.h Lease App IA Authoring |
2348 IF (p_mode = 'U' AND p_chrv_rec.sts_code IN ('SUBMITTED', 'ACTIVE')) THEN
2349 -- end: 06-May-2005 cklee okl.h Lease App IA Authoring |
2350 OPEN c(p_chrv_rec.id);
2351 FETCH c INTO l_header_start_date;
2352 CLOSE c;
2353 ELSE
2354 l_header_start_date := p_chrv_rec.start_date;
2355 END IF;
2356
2357 IF (trunc(l_header_start_date) > trunc(p_chrv_rec.end_date))
2358 THEN
2359 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2360 p_msg_name => 'OKL_LLA_RANGE_CHECK',
2361 p_token1 => 'COL_NAME1',
2362 p_token1_value => 'Effective To',
2363 p_token2 => 'COL_NAME2',
2364 p_token2_value => 'Effective From');
2365
2366 RAISE G_EXCEPTION_HALT_VALIDATION;
2367 END IF;
2368 RETURN l_return_status;
2369 EXCEPTION
2370 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2371
2372
2373 l_return_status := OKL_API.G_RET_STS_ERROR;
2374 RETURN l_return_status;
2375 WHEN OTHERS THEN
2376 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2377 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2378 p_msg_name => G_UNEXPECTED_ERROR,
2379 p_token1 => G_SQLCODE_TOKEN,
2380 p_token1_value => SQLCODE,
2381 p_token2 => G_SQLERRM_TOKEN,
2382 p_token2_value => SQLERRM);
2383 RETURN l_return_status;
2384 END;
2385 --------------------------------------------------------------------------
2386 ----- Validate Credit Amount when status change to 'Active'
2387 --------------------------------------------------------------------------
2388 FUNCTION validate_credit_amount(
2389 p_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type
2390 ) RETURN VARCHAR2
2391 IS
2392 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2393 l_amount NUMBER := 0;
2394 l_api_version NUMBER := 1.0;
2395 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
2396 x_return_status VARCHAR2(1);
2397 x_msg_count NUMBER;
2398 x_msg_data VARCHAR2(2000);
2399 x_value NUMBER := 0;
2400
2401 BEGIN
2402
2403 -- start: 06-May-2005 cklee okl.h Lease App IA Authoring |
2404 IF (p_chrv_rec.sts_code IN ('SUBMITTED', 'ACTIVE')) THEN
2405 -- end: 06-May-2005 cklee okl.h Lease App IA Authoring |
2406
2407 --l_amount := OKL_SEEDED_FUNCTIONS_PVT.creditline_total_limit(p_chrv_rec.id);
2408
2409 OKL_EXECUTE_FORMULA_PUB.execute(
2410 p_api_version => l_api_version,
2411 p_init_msg_list => l_init_msg_list,
2412 x_return_status => x_return_status,
2413 x_msg_count => x_msg_count,
2414 x_msg_data => x_msg_data,
2415 p_formula_name => 'CONTRACT_TOT_CRDT_LMT',
2416 p_contract_id => p_chrv_rec.id,
2417 x_value => x_value);
2418
2419 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2420 --RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2421 x_value := 0;
2422 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2423 --RAISE OKL_API.G_EXCEPTION_ERROR;
2424 x_value := 0;
2425 END IF;
2426
2427 l_amount := x_value;
2428
2429 -- check amount
2430 IF (l_amount = 0 ) THEN
2431 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2432 p_msg_name => 'OKL_LLA_AMOUNT_CHECK');
2433 RAISE G_EXCEPTION_HALT_VALIDATION;
2434 END IF;
2435 END IF;
2436
2437 RETURN l_return_status;
2438 EXCEPTION
2439 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2440 l_return_status := OKL_API.G_RET_STS_ERROR;
2441 RETURN l_return_status;
2442 WHEN OTHERS THEN
2443 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2444 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2445 p_msg_name => G_UNEXPECTED_ERROR,
2446 p_token1 => G_SQLCODE_TOKEN,
2447 p_token1_value => SQLCODE,
2448 p_token2 => G_SQLERRM_TOKEN,
2449 p_token2_value => SQLERRM);
2450 RETURN l_return_status;
2451 END;
2452
2453 --------------------------------------------------------------------------
2454 ----- Validate currency code
2455 --------------------------------------------------------------------------
2456 FUNCTION validate_currency_code(
2457 p_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type
2458 ,p_mode VARCHAR2
2459 ) RETURN VARCHAR2
2460 IS
2461 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2462 l_dummy VARCHAR2(1) := OKL_API.G_TRUE;
2463 l_currency_code okc_k_headers_b.currency_code%TYPE;
2464 l_amount OKL_K_LINES_FULL_V.AMOUNT%type;
2465
2466 cursor c_old_curr
2467 is
2468 select khr.currency_code
2469 from okc_k_headers_b khr
2470 where khr.id = p_chrv_rec.id
2471 ;
2472
2473 CURSOR c_limit_amt
2474 IS
2475 select nvl(sum(nvl(a.amount,0)),0)
2476 from OKL_K_LINES_FULL_V a
2477 where a.dnz_chr_id = p_chrv_rec.id
2478 ;
2479
2480 BEGIN
2481
2482 IF (p_mode = 'C') THEN
2483 IF (p_chrv_rec.currency_code IS NULL) OR
2484 (p_chrv_rec.currency_code = OKL_API.G_MISS_CHAR)
2485 THEN
2486 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2487 p_msg_name => G_REQUIRED_VALUE,
2488 p_token1 => G_COL_NAME_TOKEN,
2489 p_token1_value => 'Currency Code');
2490
2491 RAISE G_EXCEPTION_HALT_VALIDATION;
2492 END IF;
2493 ELSE
2494 open c_old_curr;
2495 fetch c_old_curr into l_currency_code;
2496 close c_old_curr;
2497
2498 open c_limit_amt;
2499 fetch c_limit_amt into l_amount;
2500 close c_limit_amt;
2501
2502 -- check if no credit limit created then allow user to change the currency code
2503 -- message: You are not allow to change the currency if Total Credit Limit greater than 0
2504 IF l_amount > 0 AND l_currency_code <> p_chrv_rec.currency_code THEN
2505 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2506 p_msg_name => 'OKL_LA_CREDIT_CURRENCY_CHK');
2507 RAISE G_EXCEPTION_HALT_VALIDATION;
2508
2509 END IF;
2510 END IF;
2511
2512 RETURN l_return_status;
2513 EXCEPTION
2514 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2515 l_return_status := OKL_API.G_RET_STS_ERROR;
2516 RETURN l_return_status;
2517 WHEN OTHERS THEN
2518 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2519 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2520 p_msg_name => G_UNEXPECTED_ERROR,
2521 p_token1 => G_SQLCODE_TOKEN,
2522 p_token1_value => SQLCODE,
2523 p_token2 => G_SQLERRM_TOKEN,
2524 p_token2_value => SQLERRM);
2525 RETURN l_return_status;
2526 END;
2527 --------------------------------------------------------------------------
2528 ----- Validate credit checklist template FK
2529 --------------------------------------------------------------------------
2530 FUNCTION validate_crd_chklst_tpl(
2531 p_rulv_rec IN rulv_rec_type
2532 ,p_mode VARCHAR2
2533 ) RETURN VARCHAR2
2534 IS
2535 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2536 l_dummy VARCHAR2(1) := OKL_API.G_TRUE;
2537 l_dirty_row_found boolean;
2538 l_active_row_found boolean;
2539
2540 l_credit_checklist_tpl okc_rules_b.rule_information1%TYPE;
2541 l_funding_checklist_tpl okc_rules_b.rule_information2%TYPE;
2542 l_checklists_row_found boolean;
2543
2544 --------------------------------------------------------------------------------------------
2545 -- Checklists link check
2546 --------------------------------------------------------------------------------------------
2547 CURSOR c_checklists (p_chr_id NUMBER)
2548 IS
2549 select rule.rule_information1,
2550 rule.rule_information2
2551 from okc_rules_b rule
2552 where rule.dnz_chr_id = p_chr_id
2553 and rule.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1
2554 ;
2555
2556 --------------------------------------------------------------------
2557 --1. read only if credit line checklist status is active
2558 --------------------------------------------------------------------
2559 CURSOR c_checklist_active (p_chr_id okc_k_headers_b.id%type,
2560 p_crd_chklst_id varchar2)
2561 IS
2562 select 1
2563 from okc_rules_b rult
2564 where rult.rule_information_category = G_CREDIT_CHKLST_TPL_RULE2--'LACCLD'
2565 and rult.dnz_chr_id = p_chr_id
2566 and rult.RULE_INFORMATION5 = 'ACTIVE'
2567 and exists (select null
2568 from okc_rules_b rult1
2569 where rult1.dnz_chr_id = rult.dnz_chr_id
2570 and rult1.dnz_chr_id = p_chr_id -- impove performance
2571 and rult1.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1--'LACCLT'
2572 and rult1.RULE_INFORMATION1 <> p_crd_chklst_id) -- id changes
2573 ;
2574
2575 --------------------------------------------------------------------
2576 --2. read only if credit line checklist have been touch by approver
2577 --------------------------------------------------------------------
2578 CURSOR c_approver_dirty (p_chr_id okc_k_headers_b.id%type,
2579 p_crd_chklst_id varchar2)
2580 IS
2581 select 1
2582 from okc_rules_b rult
2583 where rult.rule_information_category = G_CREDIT_CHKLST_TPL_RULE2--'LACCLD'
2584 and rult.dnz_chr_id = p_chr_id
2585 and rult.RULE_INFORMATION2 = 'Y' -- dirty bit check
2586 and exists (select null
2587 from okc_k_headers_b khr
2588 where khr.id = rult.dnz_chr_id
2589 and khr.id = p_chr_id -- impove performance
2590 and khr.sts_code = 'NEW')
2591 and exists (select null
2592 from okc_rules_b rult1
2593 where rult1.dnz_chr_id = rult.dnz_chr_id
2594 and rult1.dnz_chr_id = p_chr_id -- impove performance
2595 and rult1.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1--'LACCLT'
2596 and rult1.RULE_INFORMATION1 <> p_crd_chklst_id) -- id changes
2597 ;
2598
2599 BEGIN
2600
2601 IF (p_mode = 'U') THEN
2602
2603 OPEN c_checklists(p_rulv_rec.dnz_chr_id);
2604 FETCH c_checklists INTO l_credit_checklist_tpl,
2605 l_funding_checklist_tpl;
2606 l_checklists_row_found := c_checklists%FOUND;
2607 CLOSE c_checklists;
2608
2609 --------------------------------------------------------------------
2610 --1. read only if credit line checklist status is active
2611 --------------------------------------------------------------------
2612 OPEN c_checklist_active(p_rulv_rec.dnz_chr_id, p_rulv_rec.RULE_INFORMATION1);
2613 FETCH c_checklist_active INTO l_dummy;
2614 l_active_row_found := c_checklist_active%FOUND;
2615 CLOSE c_checklist_active;
2616
2617 IF (l_credit_checklist_tpl IS NOT NULL and l_active_row_found) THEN
2618 -- You are not allow to change credit line checklist template if credit line checklist status is active.
2619 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2620 p_msg_name => 'OKL_LLA_CREDIT_CHKLST4');
2621
2622 RAISE G_EXCEPTION_HALT_VALIDATION;
2623 END IF;
2624
2625 --------------------------------------------------------------------
2626 --2. read only if credit line checklist have been touch by approver
2627 --------------------------------------------------------------------
2628 OPEN c_approver_dirty(p_rulv_rec.dnz_chr_id, p_rulv_rec.RULE_INFORMATION1);
2629 FETCH c_approver_dirty INTO l_dummy;
2630 l_dirty_row_found := c_approver_dirty%FOUND;
2631 CLOSE c_approver_dirty;
2632
2633 IF (l_credit_checklist_tpl IS NOT NULL and l_dirty_row_found) THEN
2634 -- You are not allow to change credit line checklist template if credit line checklist mandatory flags have been check.
2635 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2636 p_msg_name => 'OKL_LLA_CREDIT_CHKLST2');
2637
2638 RAISE G_EXCEPTION_HALT_VALIDATION;
2639 END IF;
2640
2641 END IF;
2642
2643 RETURN l_return_status;
2644 EXCEPTION
2645 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2646 l_return_status := OKL_API.G_RET_STS_ERROR;
2647 RETURN l_return_status;
2648 WHEN OTHERS THEN
2649 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2650 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2651 p_msg_name => G_UNEXPECTED_ERROR,
2652 p_token1 => G_SQLCODE_TOKEN,
2653 p_token1_value => SQLCODE,
2654 p_token2 => G_SQLERRM_TOKEN,
2655 p_token2_value => SQLERRM);
2656 RETURN l_return_status;
2657 END;
2658
2659 --------------------------------------------------------------------------
2660 ----- Validate funding request checklist template FK
2661 --------------------------------------------------------------------------
2662 FUNCTION validate_fund_chklst_tpl(
2663 p_rulv_rec IN rulv_rec_type
2664 ,p_mode VARCHAR2
2665 ) RETURN VARCHAR2
2666 IS
2667 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2668 l_dummy VARCHAR2(1) := OKL_API.G_TRUE;
2669
2670 l_dirty_row_found boolean;
2671 l_active_row_found boolean;
2672
2673 l_credit_checklist_tpl okc_rules_b.rule_information1%TYPE;
2674
2675 l_funding_checklist_tpl okc_rules_b.rule_information2%TYPE;
2676 l_checklists_row_found boolean;
2677
2678 --------------------------------------------------------------------------------------------
2679 -- Checklists link check
2680 --------------------------------------------------------------------------------------------
2681 CURSOR c_checklists (p_chr_id NUMBER)
2682 IS
2683 select rule.rule_information1,
2684 rule.rule_information2
2685 from okc_rules_b rule
2686 where rule.dnz_chr_id = p_chr_id
2687 and rule.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1
2688 ;
2689
2690 --------------------------------------------------------------------
2691 --1. read only if funding requrest checklist template status is active
2692 --------------------------------------------------------------------
2693 CURSOR c_checklist_active (p_chr_id okc_k_headers_b.id%type,
2694 p_fund_chklst_id varchar2)
2695 IS
2696 select 1
2697 from okc_rules_b rult
2698 where rult.rule_information_category = G_CREDIT_CHKLST_TPL_RULE4--'LACLFM'
2699 and rult.dnz_chr_id = p_chr_id
2700 and rult.RULE_INFORMATION3 = 'ACTIVE'
2701 and exists (select null
2702 from okc_rules_b rult1
2703 where rult1.dnz_chr_id = rult.dnz_chr_id
2704 and rult1.dnz_chr_id = p_chr_id -- impove performance
2705 and rult1.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1--'LACCLT'
2706 and rult1.RULE_INFORMATION2 <> p_fund_chklst_id) -- id changes
2707 ;
2708
2709 --------------------------------------------------------------------
2710 --2. read only if funding requrest checklist template have been touch by approver
2711 --------------------------------------------------------------------
2712 CURSOR c_approver_dirty (p_chr_id okc_k_headers_b.id%type,
2713 p_fund_chklst_id varchar2)
2714 IS
2715 select 1
2716 from okc_rules_b rult
2717 where rult.rule_information_category = G_CREDIT_CHKLST_TPL_RULE3--'LACLFD'
2718 and rult.dnz_chr_id = p_chr_id
2719 and rult.RULE_INFORMATION2 = 'Y' -- dirty bit check
2720 and exists (select null
2721 from okc_k_headers_b khr
2722 where khr.id = rult.dnz_chr_id
2723 and khr.id = p_chr_id -- impove performance
2724 and khr.sts_code = 'NEW')
2725 and exists (select null
2726 from okc_rules_b rult1
2727 where rult1.dnz_chr_id = rult.dnz_chr_id
2728 and rult1.dnz_chr_id = p_chr_id -- impove performance
2729 and rult1.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1--'LACCLT'
2730 and rult1.RULE_INFORMATION2 <> p_fund_chklst_id) -- id changes
2731 ;
2732
2733
2734 BEGIN
2735
2736 IF (p_mode = 'U') THEN
2737
2738 OPEN c_checklists(p_rulv_rec.dnz_chr_id);
2739 FETCH c_checklists INTO l_credit_checklist_tpl,
2740 l_funding_checklist_tpl;
2741 l_checklists_row_found := c_checklists%FOUND;
2742 CLOSE c_checklists;
2743
2744
2745 --------------------------------------------------------------------
2746 --1. read only if funding requrest checklist template status is active
2747 --------------------------------------------------------------------
2748 OPEN c_checklist_active(p_rulv_rec.dnz_chr_id, p_rulv_rec.RULE_INFORMATION2);
2749 FETCH c_checklist_active INTO l_dummy;
2750 l_active_row_found := c_checklist_active%FOUND;
2751 CLOSE c_checklist_active;
2752
2753 IF (l_funding_checklist_tpl IS NOT NULL and l_active_row_found) THEN
2754 -- You are not allowed to change funding request checklist template if funding request checklist template status is Active.
2755 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2756 p_msg_name => 'OKL_LLA_FUND_CHKLST_CHECK8');
2757
2758 RAISE G_EXCEPTION_HALT_VALIDATION;
2759 END IF;
2760
2761 --------------------------------------------------------------------
2762 --2. read only if funding requrest checklist template have been touch by approver
2763 --------------------------------------------------------------------
2764 OPEN c_approver_dirty(p_rulv_rec.dnz_chr_id, p_rulv_rec.RULE_INFORMATION2);
2765 FETCH c_approver_dirty INTO l_dummy;
2766 l_dirty_row_found := c_approver_dirty%FOUND;
2767 CLOSE c_approver_dirty;
2768
2769 IF (l_funding_checklist_tpl IS NOT NULL and l_dirty_row_found) THEN
2770 --You are not allowed to change credit line checklist template if credit line checklist mandatory flags have been check.
2771 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2772 p_msg_name => 'OKL_LLA_FUND_CHKLST_CHECK9');
2773
2774 RAISE G_EXCEPTION_HALT_VALIDATION;
2775 END IF;
2776
2777 END IF;
2778
2779 RETURN l_return_status;
2780 EXCEPTION
2781 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2782 l_return_status := OKL_API.G_RET_STS_ERROR;
2783 RETURN l_return_status;
2784 WHEN OTHERS THEN
2785 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2786 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2787 p_msg_name => G_UNEXPECTED_ERROR,
2788 p_token1 => G_SQLCODE_TOKEN,
2789 p_token1_value => SQLCODE,
2790 p_token2 => G_SQLERRM_TOKEN,
2791 p_token2_value => SQLERRM);
2792 RETURN l_return_status;
2793 END;
2794 --------------------------------------------------------------------------
2795 FUNCTION validate_header_attributes(
2796 p_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type
2797 ,p_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type
2798 ,p_rulv_rec okl_rule_pub.rulv_rec_type
2799 ,p_mode VARCHAR2
2800 ) RETURN VARCHAR2
2801 IS
2802 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2803 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2804
2805 BEGIN
2806
2807 -- Do formal attribute validation:
2808 l_return_status := validate_credit_number(p_chrv_rec, p_mode);
2809 --- Store the highest degree of error
2810 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2811 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2812 x_return_status := l_return_status;
2813 END IF;
2814 RAISE G_EXCEPTION_HALT_VALIDATION;
2815 END IF;
2816
2817 l_return_status := validate_description(p_chrv_rec);
2818 --- Store the highest degree of error
2819 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2820 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2821 x_return_status := l_return_status;
2822 END IF;
2823 RAISE G_EXCEPTION_HALT_VALIDATION;
2824 END IF;
2825
2826 l_return_status := validate_start_date(p_chrv_rec,p_mode);
2827 --- Store the highest degree of error
2828 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2829 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2830 x_return_status := l_return_status;
2831 END IF;
2832 RAISE G_EXCEPTION_HALT_VALIDATION;
2833 END IF;
2834
2835 l_return_status := validate_end_date(p_chrv_rec,p_mode);
2836 --- Store the highest degree of error
2837 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2838 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2839 x_return_status := l_return_status;
2840 END IF;
2841 RAISE G_EXCEPTION_HALT_VALIDATION;
2842 END IF;
2843
2844 l_return_status := validate_start_end_date(p_chrv_rec,p_mode);
2845 --- Store the highest degree of error
2846 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2847 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2848 x_return_status := l_return_status;
2849 END IF;
2850 RAISE G_EXCEPTION_HALT_VALIDATION;
2851 END IF;
2852
2853 l_return_status := validate_credit_amount(p_chrv_rec);
2854 --- Store the highest degree of error
2855 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2856 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2857 x_return_status := l_return_status;
2858 END IF;
2859 RAISE G_EXCEPTION_HALT_VALIDATION;
2860 END IF;
2861
2862 -- multi-currency support
2863
2864 l_return_status := validate_currency_code(p_chrv_rec,p_mode);
2865 --- Store the highest degree of error
2866 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2867
2868 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2869 x_return_status := l_return_status;
2870 END IF;
2871
2872 RAISE G_EXCEPTION_HALT_VALIDATION;
2873 END IF;
2874 -- funding checklist for 11.5.9.x
2875
2876 l_return_status := validate_credit_checklist(p_chrv_rec,p_rulv_rec,p_mode);
2877 --- Store the highest degree of error
2878 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2879 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2880 x_return_status := l_return_status;
2881 END IF;
2882 RAISE G_EXCEPTION_HALT_VALIDATION;
2883 END IF;
2884
2885 -- funding checklist for 11.5.9.x
2886
2887 RETURN x_return_status;
2888 EXCEPTION
2889 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2890 RETURN x_return_status;
2891 WHEN OTHERS THEN
2892 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2893 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2894 p_msg_name => G_UNEXPECTED_ERROR,
2895 p_token1 => G_SQLCODE_TOKEN,
2896 p_token1_value => SQLCODE,
2897 p_token2 => G_SQLERRM_TOKEN,
2898 p_token2_value => SQLERRM);
2899 RETURN l_return_status;
2900 END validate_header_attributes;
2901
2902 --------------------------------------------------------------------------
2903 FUNCTION validate_status_is_active(
2904 p_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type
2905 ,p_mode VARCHAR2
2906 ) RETURN VARCHAR2
2907 IS
2908 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2909 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2910
2911 BEGIN
2912
2913 l_return_status := validate_description(p_chrv_rec);
2914 --- Store the highest degree of error
2915 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2916 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2917 x_return_status := l_return_status;
2918 END IF;
2919 RAISE G_EXCEPTION_HALT_VALIDATION;
2920 END IF;
2921
2922 l_return_status := validate_end_date(p_chrv_rec,p_mode);
2923 --- Store the highest degree of error
2924 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2925 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2926 x_return_status := l_return_status;
2927 END IF;
2928 RAISE G_EXCEPTION_HALT_VALIDATION;
2929 END IF;
2930
2931 l_return_status := validate_start_end_date(p_chrv_rec,p_mode);
2932 --- Store the highest degree of error
2933 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2934 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2935 x_return_status := l_return_status;
2936 END IF;
2937 RAISE G_EXCEPTION_HALT_VALIDATION;
2938 END IF;
2939
2940 RETURN x_return_status;
2941 EXCEPTION
2942 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2943 RETURN x_return_status;
2944 WHEN OTHERS THEN
2945 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2946 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2947 p_msg_name => G_UNEXPECTED_ERROR,
2948 p_token1 => G_SQLCODE_TOKEN,
2949 p_token1_value => SQLCODE,
2950 p_token2 => G_SQLERRM_TOKEN,
2951 p_token2_value => SQLERRM);
2952 RETURN l_return_status;
2953 END validate_status_is_active;
2954
2955 --------------------------------------------------------------------------
2956 --------------------------------------------------------------------------
2957 ----- Validate customer name
2958 --------------------------------------------------------------------------
2959 FUNCTION validate_customer(
2960 p_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type
2961 ,p_customer_name VARCHAR2
2962 ) RETURN VARCHAR2
2963 IS
2964
2965 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2966 l_existing_status VARCHAR2(1) := '?';
2967
2968 CURSOR c (p_customer_name VARCHAR2)
2969 IS
2970 select 'X'
2971 from okx_parties_v a
2972 where TRIM(a.name) = TRIM(p_customer_name) -- vsgandhi: Bug 13996614
2973 ;
2974
2975 BEGIN
2976
2977 -- start: cklee 03/24/2004
2978 IF (p_cplv_rec.object1_id1 IS NULL) OR
2979 (p_cplv_rec.object1_id1 = OKL_API.G_MISS_CHAR)
2980 THEN
2981 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2982 p_msg_name => G_REQUIRED_VALUE,
2983 p_token1 => G_COL_NAME_TOKEN,
2984 p_token1_value => 'object1_id1');
2985 RAISE G_EXCEPTION_HALT_VALIDATION;
2986 END IF;
2987
2988 IF (p_cplv_rec.object1_id2 IS NULL) OR
2989 (p_cplv_rec.object1_id2 = OKL_API.G_MISS_CHAR)
2990 THEN
2991 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2992 p_msg_name => G_REQUIRED_VALUE,
2993 p_token1 => G_COL_NAME_TOKEN,
2994 p_token1_value => 'object1_id2');
2995 RAISE G_EXCEPTION_HALT_VALIDATION;
2996 END IF;
2997
2998 IF (p_cplv_rec.jtot_object1_code IS NULL) OR
2999 (p_cplv_rec.jtot_object1_code = OKL_API.G_MISS_CHAR)
3000 THEN
3001 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3002 p_msg_name => G_REQUIRED_VALUE,
3003 p_token1 => G_COL_NAME_TOKEN,
3004 p_token1_value => 'jtot_object1_code');
3005 RAISE G_EXCEPTION_HALT_VALIDATION;
3006 END IF;
3007 -- end: cklee 03/24/2004
3008
3009
3010 IF (p_customer_name IS NULL) OR
3011 (p_customer_name = OKL_API.G_MISS_CHAR)
3012 THEN
3013 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3014 p_msg_name => G_REQUIRED_VALUE,
3015 p_token1 => G_COL_NAME_TOKEN,
3016 p_token1_value => 'Customer Name');
3017 RAISE G_EXCEPTION_HALT_VALIDATION;
3018 END IF;
3019
3020 OPEN c(p_customer_name);
3021 FETCH c INTO l_existing_status;
3022 CLOSE c;
3023
3024 IF (l_existing_status = '?' ) THEN
3025
3026 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3027 p_msg_name => 'OKL_LLA_NO_DATA_FOUND',
3028 p_token1 => 'COL_NAME',
3029 p_token1_value => 'Customer Name ' || p_customer_name);
3030 RAISE G_EXCEPTION_HALT_VALIDATION;
3031 END IF;
3032
3033 RETURN l_return_status;
3034 EXCEPTION
3035 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3036 l_return_status := OKL_API.G_RET_STS_ERROR;
3037 RETURN l_return_status;
3038 WHEN OTHERS THEN
3039 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3040 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3041 p_msg_name => G_UNEXPECTED_ERROR,
3042 p_token1 => G_SQLCODE_TOKEN,
3043 p_token1_value => SQLCODE,
3044 p_token2 => G_SQLERRM_TOKEN,
3045 p_token2_value => SQLERRM);
3046 RETURN l_return_status;
3047 END;
3048 --------------------------------------------------------------------------
3049 ----- Validate customer acct number
3050 --------------------------------------------------------------------------
3051 FUNCTION validate_account_number(
3052 p_cust_acct_id IN NUMBER,
3053 p_cust_acct_number IN VARCHAR2
3054 ) RETURN VARCHAR2
3055 IS
3056
3057 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3058 l_cust_acct_id number;
3059 l_notfound boolean;
3060
3061 CURSOR c_acct(p_cust_acct_id number)
3062 IS
3063 select a.id1
3064 from okx_customer_accounts_v a
3065 where a.id1 = p_cust_acct_id
3066 ;
3067
3068 CURSOR c_acct_num(p_cust_acct_number VARCHAR2)
3069 IS
3070 select a.id1
3071 from okx_customer_accounts_v a
3072 where a.description = p_cust_acct_number
3073 ;
3074
3075 BEGIN
3076
3077 -- customer account # is not required
3078 IF (p_cust_acct_number IS NOT NULL AND
3079 p_cust_acct_number <> OKL_API.G_MISS_CHAR)
3080 THEN
3081
3082 OPEN c_acct_num(p_cust_acct_number);
3083 FETCH c_acct_num INTO l_cust_acct_id;
3084 l_notfound := c_acct_num%NOTFOUND;
3085 CLOSE c_acct_num;
3086
3087 IF (l_notfound) THEN
3088
3089 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3090 p_msg_name => 'OKL_LLA_NO_DATA_FOUND',
3091 p_token1 => 'COL_NAME',
3092 p_token1_value => 'Customer Account '|| p_cust_acct_number);
3093 RAISE G_EXCEPTION_HALT_VALIDATION;
3094 END IF;
3095
3096 END IF;
3097
3098 -- customer account # is not required
3099 IF (p_cust_acct_id IS NOT NULL AND
3100 p_cust_acct_id <> OKL_API.G_MISS_NUM)
3101 THEN
3102
3103 OPEN c_acct(p_cust_acct_id);
3104 FETCH c_acct INTO l_cust_acct_id;
3105 l_notfound := c_acct%NOTFOUND;
3106 CLOSE c_acct;
3107
3108 IF (l_notfound) THEN
3109
3110 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3111 p_msg_name => 'OKL_LLA_NO_DATA_FOUND',
3112 p_token1 => 'COL_NAME',
3113 p_token1_value => 'cust_acct_id '|| p_cust_acct_id);
3114 RAISE G_EXCEPTION_HALT_VALIDATION;
3115 END IF;
3116
3117 END IF;
3118
3119 RETURN l_return_status;
3120 EXCEPTION
3121 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3122 l_return_status := OKL_API.G_RET_STS_ERROR;
3123 RETURN l_return_status;
3124 WHEN OTHERS THEN
3125 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3126 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3127 p_msg_name => G_UNEXPECTED_ERROR,
3128 p_token1 => G_SQLCODE_TOKEN,
3129 p_token1_value => SQLCODE,
3130 p_token2 => G_SQLERRM_TOKEN,
3131 p_token2_value => SQLERRM);
3132
3133 RETURN l_return_status;
3134 END;
3135 --------------------------------------------------------------------------
3136 --------------------------------------------------------------------------
3137 FUNCTION validate_chklst_tpl(
3138 p_rulv_rec IN rulv_rec_type
3139 ,p_mode VARCHAR2
3140 ) RETURN VARCHAR2
3141 IS
3142 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3143 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3144
3145 BEGIN
3146
3147 -- Do formal attribute validation:
3148 l_return_status := validate_crd_chklst_tpl(p_rulv_rec, p_mode);
3149 --- Store the highest degree of error
3150 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3151 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3152 x_return_status := l_return_status;
3153 END IF;
3154 RAISE G_EXCEPTION_HALT_VALIDATION;
3155 END IF;
3156
3157 -- Do formal attribute validation:
3158 l_return_status := validate_fund_chklst_tpl(p_rulv_rec, p_mode);
3159 --- Store the highest degree of error
3160 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3161 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3162 x_return_status := l_return_status;
3163 END IF;
3164 RAISE G_EXCEPTION_HALT_VALIDATION;
3165 END IF;
3166
3167 RETURN x_return_status;
3168 EXCEPTION
3169 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3170 RETURN x_return_status;
3171 WHEN OTHERS THEN
3172 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3173 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3174 p_msg_name => G_UNEXPECTED_ERROR,
3175 p_token1 => G_SQLCODE_TOKEN,
3176 p_token1_value => SQLCODE,
3177 p_token2 => G_SQLERRM_TOKEN,
3178 p_token2_value => SQLERRM);
3179 RETURN l_return_status;
3180 END validate_chklst_tpl;
3181 ----------------------------------------------------------------------------------
3182 PROCEDURE validate_credit(
3183 p_api_version IN NUMBER,
3184 p_init_msg_list IN VARCHAR2,
3185 x_return_status OUT NOCOPY VARCHAR2,
3186 x_msg_count OUT NOCOPY NUMBER,
3187 x_msg_data OUT NOCOPY VARCHAR2,
3188 p_chr_id IN NUMBER,
3189 p_contract_number IN VARCHAR2,
3190 p_description IN VARCHAR2,
3191 p_customer_id1 IN VARCHAR2,
3192 p_customer_id2 IN VARCHAR2,
3193 p_customer_code IN VARCHAR2,
3194 p_customer_name IN VARCHAR2,
3195 p_effective_from IN DATE,
3196 p_effective_to IN DATE,
3197 p_currency_code IN VARCHAR2,
3198 -- multi-currency support
3199 p_currency_conv_type IN VARCHAR2,
3200 p_currency_conv_rate IN NUMBER,
3201 p_currency_conv_date IN DATE,
3202 -- multi-currency support
3203 -- funding checklist enhancement
3204 p_credit_ckl_id IN NUMBER,
3205 p_funding_ckl_id IN NUMBER,
3206 -- funding checklist enhancement
3207 p_cust_acct_id IN NUMBER, -- 11.5.10 rule migration project
3208 p_cust_acct_number IN VARCHAR2, -- 11.5.10 rule migration project
3209 p_sts_code IN VARCHAR2)
3210
3211 AS
3212 l_api_version CONSTANT NUMBER := 1.0;
3213 l_api_name VARCHAR2(30) := 'VALIDATE_CREDIT';
3214 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3215 l_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
3216 l_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
3217
3218 lp_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
3219
3220 l_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
3221
3222
3223 -- funding checklist enhancement for 11.5.9
3224 lp_rulv_rec rulv_rec_type;
3225 lx_rulv_rec rulv_rec_type;
3226 -- funding checklist enhancement for 11.5.9
3227
3228 Cursor c_sts_code (p_chr_id NUMBER) is
3229 select sts_code
3230 from okc_k_headers_b
3231 where id = p_chr_id
3232 ;
3233
3234 BEGIN
3235
3236 x_return_status := OKC_API.START_ACTIVITY(
3237 p_api_name => l_api_name,
3238
3239 p_pkg_name => g_pkg_name,
3240 p_init_msg_list => p_init_msg_list,
3241 l_api_version => l_api_version,
3242 p_api_version => p_api_version,
3243 p_api_type => g_api_type,
3244 x_return_status => x_return_status);
3245
3246 -- check if activity started successfully
3247 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3248 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3249 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3250 raise OKC_API.G_EXCEPTION_ERROR;
3251 End If;
3252
3253 -- get sts_code from database
3254 OPEN c_sts_code(p_chr_id);
3255 FETCH c_sts_code INTO l_sts_code;
3256 CLOSE c_sts_code;
3257
3258
3259 l_chrv_rec.id := p_chr_id;
3260 l_chrv_rec.contract_number := p_contract_number;
3261 l_chrv_rec.description := p_description;
3262
3263 l_chrv_rec.start_date := p_effective_from;
3264 l_chrv_rec.end_date := p_effective_to;
3265 l_chrv_rec.sts_code := p_sts_code;
3266 -- l_chrv_rec.revolving_credit_yn := p_revolving_credit_yn;
3267 l_chrv_rec.currency_code := p_currency_code;
3268
3269 -- multi-currency support
3270 l_khrv_rec.currency_conversion_type := p_currency_conv_type;
3271 l_khrv_rec.currency_conversion_rate := p_currency_conv_rate;
3272 l_khrv_rec.currency_conversion_date := p_currency_conv_date;
3273 -- multi-currency support
3274
3275 -- funding checklist enhancement
3276 --
3277 -- Credit header checklist template
3278 --
3279
3280 -- rule FKs
3281 lp_rulv_rec.DNZ_CHR_ID := p_chr_id;
3282 lp_rulv_rec.RULE_INFORMATION1 := p_credit_ckl_id;
3283 lp_rulv_rec.RULE_INFORMATION2 := p_funding_ckl_id;
3284
3285 x_return_status := validate_chklst_tpl(lp_rulv_rec, 'U');
3286 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3287 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3288 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3289 raise OKC_API.G_EXCEPTION_ERROR;
3290 End If;
3291
3292 --
3293 -- funding checklist enhancement
3294
3295 IF (l_sts_code = 'ACTIVE')THEN -- check if status already active
3296
3297 x_return_status := validate_status_is_active(l_chrv_rec, 'U');
3298 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3299 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3300 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3301 raise OKC_API.G_EXCEPTION_ERROR;
3302 End If;
3303
3304 ELSE -- check include status become active
3305
3306 x_return_status := validate_header_attributes(l_chrv_rec, l_khrv_rec, lp_rulv_rec, 'U');
3307 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3308 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3309 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3310 raise OKC_API.G_EXCEPTION_ERROR;
3311 End If;
3312
3313 -- 11.5.10 rule migration start
3314 x_return_status := validate_account_number(p_cust_acct_id => p_cust_acct_id,
3315 p_cust_acct_number => p_cust_acct_number);
3316 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3317 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3318 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3319 raise OKC_API.G_EXCEPTION_ERROR;
3320 End If;
3321 -- 11.5.10 rule migration end
3322
3323 END IF;
3324
3325 -- validate customer name only
3326 lp_cplv_rec.dnz_chr_id := p_chr_id;
3327 lp_cplv_rec.chr_id := p_chr_id;
3328 lp_cplv_rec.cle_id := null;
3329 lp_cplv_rec.object1_id1 := p_customer_id1;
3330 lp_cplv_rec.object1_id2 := p_customer_id2;
3331 lp_cplv_rec.jtot_object1_code := p_customer_code;
3332 lp_cplv_rec.rle_code := G_RLE_CODE;
3333
3334 IF (l_sts_code <> 'ACTIVE')THEN
3335 x_return_status := validate_customer(lp_cplv_rec, p_customer_name);
3336 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3337 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3338 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3339 raise OKC_API.G_EXCEPTION_ERROR;
3340 End If;
3341 END IF;
3342
3343 x_return_status := l_return_status;
3344
3345 EXCEPTION
3346 when OKC_API.G_EXCEPTION_ERROR then
3347 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3348 p_api_name => l_api_name,
3349 p_pkg_name => g_pkg_name,
3350 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3351 x_msg_count => x_msg_count,
3352 x_msg_data => x_msg_data,
3353 p_api_type => g_api_type);
3354
3355 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3356 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3357 p_api_name => l_api_name,
3358 p_pkg_name => g_pkg_name,
3359 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3360 x_msg_count => x_msg_count,
3361 x_msg_data => x_msg_data,
3362
3363 p_api_type => g_api_type);
3364
3365 when OTHERS then
3366 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3367 p_api_name => l_api_name,
3368 p_pkg_name => g_pkg_name,
3369 p_exc_name => 'OTHERS',
3370 x_msg_count => x_msg_count,
3371 x_msg_data => x_msg_data,
3372 p_api_type => g_api_type);
3373 END validate_credit;
3374 --------------------------------------------------------------------------
3375
3376 PROCEDURE validate_account_number(
3377 p_api_version IN NUMBER,
3378 p_init_msg_list IN VARCHAR2,
3379 x_return_status OUT NOCOPY VARCHAR2,
3380 x_msg_count OUT NOCOPY NUMBER,
3381 x_msg_data OUT NOCOPY VARCHAR2,
3382 p_account_number IN VARCHAR2)
3383 AS
3384 l_api_version CONSTANT NUMBER := 1.0;
3385 l_api_name VARCHAR2(30) := 'VALIDATE_ACCOUNT';
3386 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3387
3388 BEGIN
3389
3390 x_return_status := OKC_API.START_ACTIVITY(
3391 p_api_name => l_api_name,
3392 p_pkg_name => g_pkg_name,
3393 p_init_msg_list => p_init_msg_list,
3394 l_api_version => l_api_version,
3395 p_api_version => p_api_version,
3396 p_api_type => g_api_type,
3397 x_return_status => x_return_status);
3398
3399 -- check if activity started successfully
3400 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3401 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3402 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3403 raise OKC_API.G_EXCEPTION_ERROR;
3404 End If;
3405
3406 x_return_status := validate_account_number(p_cust_acct_id => NULL,
3407 p_cust_acct_number => p_account_number);
3408 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3409 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3410 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3411 raise OKC_API.G_EXCEPTION_ERROR;
3412 End If;
3413
3414 x_return_status := l_return_status;
3415
3416 EXCEPTION
3417 when OKC_API.G_EXCEPTION_ERROR then
3418 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3419 p_api_name => l_api_name,
3420 p_pkg_name => g_pkg_name,
3421 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3422 x_msg_count => x_msg_count,
3423 x_msg_data => x_msg_data,
3424 p_api_type => g_api_type);
3425
3426 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3427 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3428 p_api_name => l_api_name,
3429 p_pkg_name => g_pkg_name,
3430 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3431 x_msg_count => x_msg_count,
3432 x_msg_data => x_msg_data,
3433 p_api_type => g_api_type);
3434
3435 when OTHERS then
3436 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3437 p_api_name => l_api_name,
3438 p_pkg_name => g_pkg_name,
3439 p_exc_name => 'OTHERS',
3440 x_msg_count => x_msg_count,
3441 x_msg_data => x_msg_data,
3442 p_api_type => g_api_type);
3443 END validate_account_number;
3444
3445 --------------------------------------------------------------------------
3446 FUNCTION validate_line_attributes(
3447 p_mode IN VARCHAR2,
3448 p_clev_rec IN okl_okc_migration_pvt.clev_rec_type,
3449 p_klev_rec IN klev_rec_type
3450 ) RETURN VARCHAR2
3451 IS
3452 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3453 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3454
3455 BEGIN
3456
3457 -- Do formal attribute validation:
3458 IF (upper(p_mode) <> 'DELETE') THEN
3459 l_return_status := validate_start_date(p_clev_rec,p_klev_rec);
3460 --- Store the highest degree of error
3461 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3462 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3463 x_return_status := l_return_status;
3464 END IF;
3465 RAISE G_EXCEPTION_HALT_VALIDATION;
3466 END IF;
3467
3468 END IF;
3469
3470 l_return_status := validate_amount(p_clev_rec, p_klev_rec);
3471 --- Store the highest degree of error
3472 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3473 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3474 x_return_status := l_return_status;
3475 END IF;
3476 RAISE G_EXCEPTION_HALT_VALIDATION;
3477 END IF;
3478
3479 --un-comment by cklee 03/24/2004
3480 IF (upper(p_mode) = 'CREATE') THEN
3481 l_return_status := validate_credit_nature(p_clev_rec, p_klev_rec);
3482 --- Store the highest degree of error
3483 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3484 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3485 x_return_status := l_return_status;
3486 END IF;
3487 RAISE G_EXCEPTION_HALT_VALIDATION;
3488 END IF;
3489 END IF;
3490
3491 RETURN x_return_status;
3492
3493 EXCEPTION
3494 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3495 RETURN x_return_status;
3496 WHEN OTHERS THEN
3497 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3498 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3499 p_msg_name => G_UNEXPECTED_ERROR,
3500 p_token1 => G_SQLCODE_TOKEN,
3501 p_token1_value => SQLCODE,
3502 p_token2 => G_SQLERRM_TOKEN,
3503 p_token2_value => SQLERRM);
3504
3505 RETURN l_return_status;
3506 END validate_line_attributes;
3507
3508 --------------------------------------------------------------------------
3509 PROCEDURE validate_credit_limit(
3510 p_api_version IN NUMBER,
3511 p_init_msg_list IN VARCHAR2,
3512 x_return_status OUT NOCOPY VARCHAR2,
3513 x_msg_count OUT NOCOPY NUMBER,
3514 x_msg_data OUT NOCOPY VARCHAR2,
3515 p_mode IN VARCHAR2,
3516 p_chr_id IN NUMBER,
3517 p_cle_id IN NUMBER,
3518 p_cle_start_date IN DATE,
3519 p_description IN VARCHAR2,
3520 p_credit_nature IN VARCHAR2,
3521 p_amount IN NUMBER)
3522 AS
3523 l_api_version CONSTANT NUMBER := 1.0;
3524 l_api_name VARCHAR2(30) := 'VALIDATE_CREDIT_LIMIT';
3525 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3526 l_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
3527 l_klev_rec OKL_CONTRACT_PVT.klev_rec_type;
3528
3529 BEGIN
3530 x_return_status := OKC_API.START_ACTIVITY(
3531 p_api_name => l_api_name,
3532 p_pkg_name => g_pkg_name,
3533 p_init_msg_list => p_init_msg_list,
3534 l_api_version => l_api_version,
3535 p_api_version => p_api_version,
3536 p_api_type => g_api_type,
3537 x_return_status => x_return_status);
3538
3539 -- check if activity started successfully
3540 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3541 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3542 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3543 raise OKC_API.G_EXCEPTION_ERROR;
3544 End If;
3545
3546 l_clev_rec.id := p_cle_id;
3547 l_clev_rec.dnz_chr_id := p_chr_id;
3548 l_clev_rec.start_date := p_cle_start_date;
3549 l_clev_rec.item_description := p_description;
3550 l_klev_rec.amount := p_amount;
3551 l_klev_rec.credit_nature := p_credit_nature;
3552
3553 -- Do formal attribute validation:
3554 x_return_status := validate_start_date(l_clev_rec,l_klev_rec);
3555 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3556 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3557 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3558 raise OKC_API.G_EXCEPTION_ERROR;
3559 End If;
3560
3561 x_return_status := validate_amount(l_clev_rec,l_klev_rec);
3562 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3563 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3564 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3565 raise OKC_API.G_EXCEPTION_ERROR;
3566 End If;
3567
3568 x_return_status := l_return_status;
3569
3570 EXCEPTION
3571 when OKC_API.G_EXCEPTION_ERROR then
3572 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3573 p_api_name => l_api_name,
3574 p_pkg_name => g_pkg_name,
3575 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3576 x_msg_count => x_msg_count,
3577 x_msg_data => x_msg_data,
3578 p_api_type => g_api_type);
3579
3580 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3581 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3582 p_api_name => l_api_name,
3583 p_pkg_name => g_pkg_name,
3584 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3585 x_msg_count => x_msg_count,
3586 x_msg_data => x_msg_data,
3587 p_api_type => g_api_type);
3588
3589 when OTHERS then
3590 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3591 p_api_name => l_api_name,
3592 p_pkg_name => g_pkg_name,
3593 p_exc_name => 'OTHERS',
3594 x_msg_count => x_msg_count,
3595 x_msg_data => x_msg_data,
3596 p_api_type => g_api_type);
3597 END validate_credit_limit;
3598 --------------------------------------------------------------------------
3599 PROCEDURE validate_credit_limit(
3600 p_api_version IN NUMBER,
3601 p_init_msg_list IN VARCHAR2,
3602 x_return_status OUT NOCOPY VARCHAR2,
3603 x_msg_count OUT NOCOPY NUMBER,
3604 x_msg_data OUT NOCOPY VARCHAR2,
3605 p_mode IN VARCHAR2,
3606 p_clev_rec IN okl_okc_migration_pvt.clev_rec_type,
3607 p_klev_rec IN klev_rec_type)
3608 AS
3609 l_api_version CONSTANT NUMBER := 1.0;
3610 l_api_name VARCHAR2(30) := 'VALIDATE_CREDIT_LIMIT1';
3611 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3612
3613 BEGIN
3614 x_return_status := OKC_API.START_ACTIVITY(
3615 p_api_name => l_api_name,
3616 p_pkg_name => g_pkg_name,
3617 p_init_msg_list => p_init_msg_list,
3618 l_api_version => l_api_version,
3619 p_api_version => p_api_version,
3620 p_api_type => g_api_type,
3621 x_return_status => x_return_status);
3622
3623 -- check if activity started successfully
3624 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3625 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3626 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3627 raise OKC_API.G_EXCEPTION_ERROR;
3628 End If;
3629
3630 x_return_status := l_return_status;
3631
3632 EXCEPTION
3633 when OKC_API.G_EXCEPTION_ERROR then
3634 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3635 p_api_name => l_api_name,
3636 p_pkg_name => g_pkg_name,
3637 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3638 x_msg_count => x_msg_count,
3639 x_msg_data => x_msg_data,
3640 p_api_type => g_api_type);
3641
3642 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3643 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3644 p_api_name => l_api_name,
3645 p_pkg_name => g_pkg_name,
3646 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3647 x_msg_count => x_msg_count,
3648 x_msg_data => x_msg_data,
3649 p_api_type => g_api_type);
3650
3651 when OTHERS then
3652 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3653 p_api_name => l_api_name,
3654 p_pkg_name => g_pkg_name,
3655 p_exc_name => 'OTHERS',
3656 x_msg_count => x_msg_count,
3657 x_msg_data => x_msg_data,
3658
3659 p_api_type => g_api_type);
3660 END validate_credit_limit;
3661 --------------------------------------------------------------------------
3662 PROCEDURE validate_credit_limit(
3663 p_api_version IN NUMBER,
3664 p_init_msg_list IN VARCHAR2,
3665 x_return_status OUT NOCOPY VARCHAR2,
3666 x_msg_count OUT NOCOPY NUMBER,
3667 x_msg_data OUT NOCOPY VARCHAR2,
3668 p_mode IN VARCHAR2,
3669 p_clev_tbl IN okl_okc_migration_pvt.clev_tbl_type,
3670 p_klev_tbl IN klev_tbl_type)
3671 AS
3672 l_api_version CONSTANT NUMBER := 1.0;
3673 l_api_name VARCHAR2(30) := 'VALIDATE_CREDIT_LIMIT2';
3674 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3675 l_clev_tbl okl_okc_migration_pvt.clev_tbl_type := p_clev_tbl;
3676 l_klev_tbl klev_tbl_type := p_klev_tbl;
3677 i NUMBER;
3678
3679 BEGIN
3680
3681 x_return_status := OKC_API.START_ACTIVITY(
3682 p_api_name => l_api_name,
3683 p_pkg_name => g_pkg_name,
3684 p_init_msg_list => p_init_msg_list,
3685 l_api_version => l_api_version,
3686 p_api_version => p_api_version,
3687 p_api_type => g_api_type,
3688 x_return_status => x_return_status);
3689
3690 IF (p_clev_tbl.COUNT > 0) THEN
3691 i := p_clev_tbl.FIRST;
3692 LOOP
3693
3694 x_return_status := validate_line_attributes(p_mode, p_clev_tbl(i),p_klev_tbl(i));
3695 -- check if activity started successfully
3696 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3697 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3698 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3699 raise OKC_API.G_EXCEPTION_ERROR;
3700 End If;
3701
3702 EXIT WHEN (i = p_clev_tbl.LAST);
3703 i := p_clev_tbl.NEXT(i);
3704 END LOOP;
3705 END IF;
3706
3707 x_return_status := l_return_status;
3708
3709 EXCEPTION
3710
3711 when OKC_API.G_EXCEPTION_ERROR then
3712 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3713 p_api_name => l_api_name,
3714 p_pkg_name => g_pkg_name,
3715 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3716 x_msg_count => x_msg_count,
3717 x_msg_data => x_msg_data,
3718 p_api_type => g_api_type);
3719
3720 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3721 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3722 p_api_name => l_api_name,
3723 p_pkg_name => g_pkg_name,
3724 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3725 x_msg_count => x_msg_count,
3726 x_msg_data => x_msg_data,
3727 p_api_type => g_api_type);
3728
3729 when OTHERS then
3730 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3731 p_api_name => l_api_name,
3732 p_pkg_name => g_pkg_name,
3733 p_exc_name => 'OTHERS',
3734 x_msg_count => x_msg_count,
3735 x_msg_data => x_msg_data,
3736 p_api_type => g_api_type);
3737
3738 END validate_credit_limit;
3739
3740 ----------------------------------------------------------------------------------
3741 -- Start of comments
3742 --
3743 -- Procedure Name : create_credit_header
3744 -- Description : wrapper api for credit_credit
3745 -- Business Rules :
3746 -- Parameters :
3747 -- Version : 1.0
3748 -- End of comments
3749 ----------------------------------------------------------------------------------
3750 PROCEDURE create_credit_header(
3751 p_api_version IN NUMBER,
3752 p_init_msg_list IN VARCHAR2,
3753 x_return_status OUT NOCOPY VARCHAR2,
3754 x_msg_count OUT NOCOPY NUMBER,
3755 x_msg_data OUT NOCOPY VARCHAR2,
3756 -- funding checklist enhancement
3757 p_credit_ckl_id IN NUMBER,
3758 p_funding_ckl_id IN NUMBER,
3759 -- funding checklist enhancement
3760 p_chrv_rec IN okl_okc_migration_pvt.chrv_rec_type,
3761 p_khrv_rec IN khrv_rec_type,
3762 x_chrv_rec OUT NOCOPY okl_okc_migration_pvt.chrv_rec_type,
3763 x_khrv_rec OUT NOCOPY khrv_rec_type)
3764 is
3765 l_api_name CONSTANT VARCHAR2(30) := 'create_credit_header_pub';
3766 l_api_version CONSTANT NUMBER := 1.0;
3767 i NUMBER;
3768 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3769
3770 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
3771 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
3772 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
3773 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
3774
3775 begin
3776 -- Set API savepoint
3777 SAVEPOINT create_credit_header_pub;
3778
3779 -- Check for call compatibility
3780 IF (NOT FND_API.Compatible_API_Call (l_api_version,
3781 p_api_version,
3782 l_api_name,
3783 G_PKG_NAME ))
3784 THEN
3785 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3786 END IF;
3787
3788 -- Initialize message list if requested
3789 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3790 FND_MSG_PUB.initialize;
3791
3792 END IF;
3793
3794
3795 -- Initialize API status to success
3796 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3797
3798
3799 /*** Begin API body ****************************************************/
3800 /*** End API body ******************************************************/
3801
3802 -- Get message count and if count is 1, get message info
3803 FND_MSG_PUB.Count_And_Get
3804 (p_count => x_msg_count,
3805 p_data => x_msg_data);
3806
3807 EXCEPTION
3808 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3809 ROLLBACK TO create_credit_header_pub;
3810 x_return_status := OKL_API.G_RET_STS_ERROR;
3811 FND_MSG_PUB.Count_And_Get
3812 (p_count => x_msg_count,
3813 p_data => x_msg_data);
3814
3815 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3816 ROLLBACK TO create_credit_header_pub;
3817 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3818 FND_MSG_PUB.Count_And_Get
3819 (p_count => x_msg_count,
3820 p_data => x_msg_data);
3821
3822 WHEN OTHERS THEN
3823 ROLLBACK TO create_credit_header_pub;
3824 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3825 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3826 p_msg_name => G_UNEXPECTED_ERROR,
3827 p_token1 => G_SQLCODE_TOKEN,
3828 p_token1_value => SQLCODE,
3829 p_token2 => G_SQLERRM_TOKEN,
3830 p_token2_value => SQLERRM);
3831 FND_MSG_PUB.Count_And_Get
3832 (p_count => x_msg_count,
3833 p_data => x_msg_data);
3834
3835 end;
3836
3837 ----------------------------------------------------------------------------------
3838 -- Start of comments
3839 --
3840 -- Procedure Name : update_credit_header
3841 -- Description : wrapper api for update_contract_header
3842 -- Business Rules :
3843 -- Parameters :
3844 -- Version : 1.0
3845 -- End of comments
3846 ----------------------------------------------------------------------------------
3847 PROCEDURE update_credit_header(
3848 p_api_version IN NUMBER,
3849 p_init_msg_list IN VARCHAR2,
3850 x_return_status OUT NOCOPY VARCHAR2,
3851 x_msg_count OUT NOCOPY NUMBER,
3852 x_msg_data OUT NOCOPY VARCHAR2,
3853 p_restricted_update IN VARCHAR2,
3854 -- funding checklist enhancement
3855 p_chklst_tpl_rgp_id IN NUMBER, -- LACCLH
3856 p_chklst_tpl_rule_id IN NUMBER, -- LACCLT
3857 p_credit_ckl_id IN NUMBER,
3858 p_funding_ckl_id IN NUMBER,
3859 -- funding checklist enhancement
3860 p_chrv_rec IN okl_okc_migration_pvt.chrv_rec_type,
3861 p_khrv_rec IN khrv_rec_type,
3862 x_chrv_rec OUT NOCOPY okl_okc_migration_pvt.chrv_rec_type,
3863 x_khrv_rec OUT NOCOPY khrv_rec_type)
3864 is
3865
3866 -- vthiruva Code change to enable Business Event START
3867 CURSOR c_old_sts_code(p_chr_id okc_k_headers_b.id%TYPE) IS
3868 SELECT sts_code
3869 FROM okc_k_headers_b
3870 WHERE id = p_chr_id;
3871
3872 l_old_status okc_k_headers_b.sts_code%TYPE;
3873 -- vthiruva Code change to enable Business Event END
3874
3875 l_api_name CONSTANT VARCHAR2(30) := 'update_credit_header_pub';
3876 l_api_version CONSTANT NUMBER := 1.0;
3877 i NUMBER;
3878 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3879
3880 -- multi-currency support
3881 lp_chrv_rec okl_okc_migration_pvt.chrv_rec_type := p_chrv_rec;
3882 lp_khrv_rec khrv_rec_type := p_khrv_rec;
3883 -- multi-currency support
3884
3885 -- funding checklist enhancement for 11.5.9
3886 lp_rulv_rec rulv_rec_type;
3887 lx_rulv_rec rulv_rec_type;
3888 -- funding checklist enhancement for 11.5.9
3889
3890 -- strat: bug#4218700
3891 l_cust_acct_id number;
3892
3893 cursor l_cust_acct(p_chr_id number) is
3894 select chrb.cust_acct_id cust_acct_id
3895 from OKC_K_HEADERS_B CHRB
3896 where CHRB.id = p_chr_id;
3897
3898 -- end: bug#4218700
3899
3900 -- start: 06-May-2005 cklee okl.h Lease App IA Authoring |
3901 l_approval_option varchar2(10);
3902 -- end: 06-May-2005 cklee okl.h Lease App IA Authoring |
3903
3904 begin
3905 -- Set API savepoint
3906 SAVEPOINT update_credit_header_pub;
3907
3908 -- Check for call compatibility
3909 IF (NOT FND_API.Compatible_API_Call (l_api_version,
3910 p_api_version,
3911 l_api_name,
3912 G_PKG_NAME ))
3913 THEN
3914 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3915 END IF;
3916
3917 -- Initialize message list if requested
3918 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3919 FND_MSG_PUB.initialize;
3920 END IF;
3921
3922 -- Initialize API status to success
3923
3924 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3925
3926
3927 /*** Begin API body ****************************************************/
3928 -- vthiruva Code change to enable Business Event START
3929 OPEN c_old_sts_code(lp_chrv_rec.id);
3930 FETCH c_old_sts_code INTO l_old_status;
3931 CLOSE c_old_sts_code;
3932 -- vthiruva Code change to enable Business Event END
3933
3934 -- strat: bug#4218700
3935 IF lp_chrv_rec.sts_code = 'ACTIVE' THEN
3936
3937 open l_cust_acct(lp_chrv_rec.id);
3938 fetch l_cust_acct into l_cust_acct_id;
3939 close l_cust_acct;
3940
3941 lp_chrv_rec.cust_acct_id := l_cust_acct_id;
3942
3943 END IF;
3944 -- end: bug#4218700
3945
3946 -- start: 06-May-2005 cklee okl.h Lease App IA Authoring |
3947 ------------------------------------------------------------------
3948 -- added for approval process
3949 ------------------------------------------------------------------
3950 l_approval_option := fnd_profile.value('OKL_CREDIT_LINE_APPROVAL_PROCESS');
3951 IF (lp_chrv_rec.sts_code = 'SUBMITTED' AND
3952 -- start: cklee 07/13/2005
3953 (l_approval_option is null or l_approval_option = 'NONE')) THEN
3954 -- l_approval_option not in ('WF', 'AME')) THEN
3955 -- end: cklee 07/13/2005
3956
3957 -- update item function validation results
3958 update_checklist_function(
3959 p_api_version => p_api_version,
3960 p_init_msg_list => p_init_msg_list,
3961 x_return_status => x_return_status,
3962 x_msg_count => x_msg_count,
3963 x_msg_data => x_msg_data,
3964 p_contract_id => lp_chrv_rec.id);
3965
3966 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3967 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3968 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3969 raise OKC_API.G_EXCEPTION_ERROR;
3970 End If;
3971
3972 -- lp_chrv_rec.sts_code := 'APPROVED';
3973 lp_chrv_rec.sts_code := 'ACTIVE'; -- update to Active directly w/o WF implementation
3974 lp_chrv_rec.DATE_APPROVED := sysdate;
3975
3976 END IF;
3977 -- end: 06-May-2005 cklee okl.h Lease App IA Authoring |
3978
3979 --dbms_output.put_line('1: before OKL_CONTRACT_PUB.update_contract_header');
3980
3981 OKL_CONTRACT_PUB.update_contract_header(
3982 p_api_version => p_api_version,
3983 p_init_msg_list => p_init_msg_list,
3984 x_return_status => x_return_status,
3985 x_msg_count => x_msg_count,
3986 x_msg_data => x_msg_data,
3987 p_chrv_rec => lp_chrv_rec,
3988 p_khrv_rec => lp_khrv_rec,
3989 x_chrv_rec => x_chrv_rec,
3990 x_khrv_rec => x_khrv_rec);
3991
3992 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3993 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3994 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3995 raise OKC_API.G_EXCEPTION_ERROR;
3996 End If;
3997
3998 --dbms_output.put_line('2: after OKL_CONTRACT_PUB.update_contract_header');
3999
4000 -- funding checklist enhancement for 11.5.9
4001
4002 IF lp_chrv_rec.sts_code = 'NEW' THEN
4003 -- rule FKs
4004 lp_rulv_rec.RGP_ID := p_chklst_tpl_rgp_id; -- reference purpose
4005 lp_rulv_rec.DNZ_CHR_ID := lp_chrv_rec.id; -- reference purpose
4006 lp_rulv_rec.ID := p_chklst_tpl_rule_id; -- MUST 'LACCLT'
4007 lp_rulv_rec.RULE_INFORMATION1 := p_credit_ckl_id;
4008 lp_rulv_rec.RULE_INFORMATION2 := p_funding_ckl_id;
4009
4010 --dbms_output.put_line('3: before update_credit_chklst_tpl');
4011
4012 update_credit_chklst_tpl(
4013 p_api_version => p_api_version,
4014 p_init_msg_list => p_init_msg_list,
4015 x_return_status => x_return_status,
4016 x_msg_count => x_msg_count,
4017 x_msg_data => x_msg_data,
4018 p_rulv_rec => lp_rulv_rec,
4019 x_rulv_rec => lx_rulv_rec);
4020
4021 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4022 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4023 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4024 raise OKC_API.G_EXCEPTION_ERROR;
4025 End If;
4026
4027 END IF;
4028 --dbms_output.put_line('4: after update_credit_chklst_tpl');
4029
4030
4031 -- funding checklist enhancement for 11.5.9
4032
4033 /*
4034 -- vthiruva, 08/31/2004
4035 -- START, Code change to enable Business Event
4036 */
4037 -- start: 06-May-2005 cklee okl.h Lease App IA Authoring |
4038 IF(l_old_status IN ('NEW', 'SUBMITTED', 'PENDING_APPROVAL') AND lp_chrv_rec.sts_code = 'ACTIVE')THEN
4039 -- end: 06-May-2005 cklee okl.h Lease App IA Authoring |
4040 --raise business event for activate credit line
4041 --if sts_code is ACTIVE and old status is NEW
4042 raise_business_event(p_api_version => p_api_version,
4043 p_init_msg_list => p_init_msg_list,
4044 x_return_status => x_return_status,
4045 x_msg_count => x_msg_count,
4046 x_msg_data => x_msg_data,
4047 p_id => lp_chrv_rec.id,
4048 p_event_name => G_WF_EVT_CR_LN_ACTIVATED);
4049
4050 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4051 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4052 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4053 RAISE OKL_API.G_EXCEPTION_ERROR;
4054 END IF;
4055 ELSE
4056 --raise business event for credit line update
4057 raise_business_event(p_api_version => p_api_version,
4058 p_init_msg_list => p_init_msg_list,
4059 x_return_status => x_return_status,
4060 x_msg_count => x_msg_count,
4061 x_msg_data => x_msg_data,
4062 p_id => lp_chrv_rec.id,
4063 p_event_name => G_WF_EVT_CR_LN_UPDATED);
4064
4065 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4066 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4067 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4068 RAISE OKL_API.G_EXCEPTION_ERROR;
4069 END IF;
4070 END IF;
4071
4072 /*
4073 -- vthiruva, 08/31/2004
4074 -- END, Code change to enable Business Event
4075 */
4076
4077 -- start: 06-May-2005 cklee okl.h Lease App IA Authoring |
4078 -----------------------------------------------------------
4079 -- trigger WF event if l_tapv_rec.trx_status_code = 'SUBMITTED' and
4080 -- profile option is WF or AME
4081 -----------------------------------------------------------
4082 IF (lp_chrv_rec.sts_code = 'SUBMITTED' AND
4083 l_approval_option in ('WF', 'AME')) THEN
4084 --dbms_output.put_line('5: OKL_CREDIT_LINE_WF.raise_approval_event');
4085
4086 -- update item function validation results
4087 update_checklist_function(
4088 p_api_version => p_api_version,
4089 p_init_msg_list => p_init_msg_list,
4090 x_return_status => x_return_status,
4091 x_msg_count => x_msg_count,
4092 x_msg_data => x_msg_data,
4093 p_contract_id => lp_chrv_rec.id);
4094
4095 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4096 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4097 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4098 raise OKC_API.G_EXCEPTION_ERROR;
4099 End If;
4100
4101 OKL_CREDIT_LINE_WF.raise_approval_event(
4102 p_api_version => p_api_version,
4103 p_init_msg_list => p_init_msg_list,
4104 x_return_status => x_return_status,
4105 x_msg_count => x_msg_count,
4106 x_msg_data => x_msg_data,
4107 p_contract_id => lp_chrv_rec.id);
4108
4109 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4110 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4111 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
4112 RAISE OKL_API.G_EXCEPTION_ERROR;
4113 END IF;
4114
4115 END IF;
4116 -- end: 06-May-2005 cklee okl.h Lease App IA Authoring |
4117 --dbms_output.put_line('6: OKL_CREDIT_LINE_WF.raise_approval_event');
4118
4119
4120 /*** End API body ******************************************************/
4121
4122 -- Get message count and if count is 1, get message info
4123 FND_MSG_PUB.Count_And_Get
4124 (p_count => x_msg_count,
4125 p_data => x_msg_data);
4126
4127 EXCEPTION
4128 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4129 ROLLBACK TO update_credit_header_pub;
4130 x_return_status := OKL_API.G_RET_STS_ERROR;
4131 FND_MSG_PUB.Count_And_Get
4132 (p_count => x_msg_count,
4133 p_data => x_msg_data);
4134
4135
4136 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4137 ROLLBACK TO update_credit_header_pub;
4138 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4139 FND_MSG_PUB.Count_And_Get
4140 (p_count => x_msg_count,
4141 p_data => x_msg_data);
4142
4143 WHEN OTHERS THEN
4144 ROLLBACK TO update_credit_header_pub;
4145 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4146 OKL_API.Set_Message(p_app_name => G_APP_NAME,
4147 p_msg_name => G_UNEXPECTED_ERROR,
4148 p_token1 => G_SQLCODE_TOKEN,
4149 p_token1_value => SQLCODE,
4150 p_token2 => G_SQLERRM_TOKEN,
4151 p_token2_value => SQLERRM);
4152 FND_MSG_PUB.Count_And_Get
4153 (p_count => x_msg_count,
4154 p_data => x_msg_data);
4155
4156 end;
4157 ----------------------------------------------------------------------------------
4158 -- Start of comments
4159 --
4160 -- Procedure Name : copy_clev_rec
4161 -- Description :
4162 -- Business Rules :
4163 -- Parameters :
4164 -- Version : 1.0
4165 -- End of comments
4166 ----------------------------------------------------------------------------------
4167 PROCEDURE copy_clev_rec(
4168 p_clev_rec IN clev_rec_type,
4169 p_clev_migr_rec OUT NOCOPY okl_okc_migration_pvt.clev_rec_type)
4170 is
4171 begin
4172 p_clev_migr_rec.id := p_clev_rec.id;
4173 p_clev_migr_rec.dnz_chr_id := p_clev_rec.dnz_chr_id;
4174 p_clev_migr_rec.chr_id := p_clev_rec.chr_id;
4175 -- p_clev_migr_rec.credit_nature := p_clev_rec.credit_nature;
4176 p_clev_migr_rec.lse_id := p_clev_rec.lse_id;
4177 p_clev_migr_rec.line_number := p_clev_rec.line_number;
4178 p_clev_migr_rec.sts_code := p_clev_rec.sts_code;
4179 p_clev_migr_rec.display_sequence := p_clev_rec.display_sequence;
4180 p_clev_migr_rec.exception_yn := p_clev_rec.exception_yn;
4181 p_clev_migr_rec.start_date := p_clev_rec.start_date;
4182 p_clev_migr_rec.item_description := p_clev_rec.item_description;
4183 -- popRecKlev.amount := new BigDecimal(sAmt);
4184 end;
4185 ----------------------------------------------------------------------------------
4186 -- Start of comments
4187 --
4188 -- Procedure Name : copy_clev_tbl
4189 -- Description :
4190 -- Business Rules :
4191 -- Parameters :
4192 -- Version : 1.0
4193 -- End of comments
4194 ----------------------------------------------------------------------------------
4195 PROCEDURE copy_clev_tbl(
4196 p_clev_tbl IN clev_tbl_type,
4197 p_clev_migr_tbl OUT NOCOPY okl_okc_migration_pvt.clev_tbl_type)
4198 is
4199 i number;
4200 begin
4201
4202 IF (p_clev_tbl.COUNT > 0) THEN
4203 i := p_clev_tbl.FIRST;
4204 LOOP
4205
4206 copy_clev_rec(
4207 p_clev_rec => p_clev_tbl(i),
4208 p_clev_migr_rec => p_clev_migr_tbl(i)
4209 );
4210
4211 EXIT WHEN (i = p_clev_tbl.LAST);
4212 i := p_clev_tbl.NEXT(i);
4213 END LOOP;
4214 END IF;
4215
4216 end;
4217
4218 ----------------------------------------------------------------------------------
4219 -- Start of comments
4220 --
4221 -- Procedure Name : copy_clmv_rec
4222 -- Description : copy from clmv_rec to x_clev_rec and x_klev_rec
4223 -- and set default attributes
4224 -- Business Rules :
4225 -- Parameters :
4226 -- Version : 1.0
4227 -- End of comments
4228 ----------------------------------------------------------------------------------
4229 PROCEDURE copy_clmv_rec(
4230 p_chr_id IN NUMBER,
4231 p_clmv_rec IN clmv_rec_type,
4232 x_clev_rec OUT NOCOPY clev_rec_type,
4233 x_klev_rec OUT NOCOPY klev_rec_type)
4234 is
4235
4236 l_lse_id number;
4237
4238 cursor c_lse_id is
4239 select lse.id
4240 from okc_line_styles_b lse,
4241 okc_subclass_top_line sctl
4242 where lse.lty_code = 'FREE_FORM'
4243 and sctl.lse_id = lse.id
4244 and sctl.scs_code = 'CREDITLINE_CONTRACT';
4245
4246
4247 begin
4248
4249 -------------------------------------------
4250 -- get lse_id
4251 -------------------------------------------
4252 open c_lse_id;
4253 Fetch c_lse_id into l_lse_id;
4254 close c_lse_id;
4255
4256 -------------------------------------------
4257 -- assign ID
4258 -------------------------------------------
4259 x_clev_rec.id := NULLIF(p_clmv_rec.id, OKC_API.G_MISS_NUM);
4260 x_klev_rec.id := NULLIF(p_clmv_rec.id, OKC_API.G_MISS_NUM);
4261
4262 x_clev_rec.dnz_chr_id := p_chr_id;
4263 x_clev_rec.chr_id := p_chr_id;
4264
4265 -- set default?
4266 x_clev_rec.lse_id := l_lse_id;
4267 x_clev_rec.line_number := '1';
4268 x_clev_rec.display_sequence := 1;
4269 x_clev_rec.sts_code := 'ENTERED';
4270 x_clev_rec.exception_yn := 'N';
4271
4272 -------------------------------------------
4273 -- assign okc_k_lines_v
4274 -------------------------------------------
4275 x_clev_rec.start_date := NULLIF(p_clmv_rec.start_date, OKC_API.G_MISS_DATE);
4276 x_clev_rec.item_description := NULLIF(p_clmv_rec.item_description, OKC_API.G_MISS_CHAR);
4277 -------------------------------------------
4278 -- assign okl_k_lines
4279 -------------------------------------------
4280 x_klev_rec.credit_nature := NULLIF(p_clmv_rec.credit_nature, OKC_API.G_MISS_CHAR);
4281 x_klev_rec.amount := NULLIF(p_clmv_rec.amount, OKC_API.G_MISS_NUM);
4282
4283 exception
4284 when others then
4285 if c_lse_id%isopen then
4286 close c_lse_id;
4287 end if;
4288
4289 end;
4290 ----------------------------------------------------------------------------------
4291 -- Start of comments
4292 --
4293 -- Procedure Name : copy_clmv_tbl
4294 -- Description : copy from clmv_tbl to x_clev_tbl and x_klev_tbl
4295 -- Business Rules :
4296 -- Parameters :
4297 -- Version : 1.0
4298 -- End of comments
4299 ----------------------------------------------------------------------------------
4300 PROCEDURE copy_clmv_tbl(
4301 p_chr_id IN NUMBER,
4302 p_clmv_tbl IN clmv_tbl_type,
4303 x_clev_tbl OUT NOCOPY clev_tbl_type,
4304 x_klev_tbl OUT NOCOPY klev_tbl_type)
4305 is
4306 i number;
4307 begin
4308
4309 IF (p_clmv_tbl.COUNT > 0) THEN
4310 i := p_clmv_tbl.FIRST;
4311 LOOP
4312
4313 copy_clmv_rec(
4314 p_chr_id => p_chr_id,
4315 p_clmv_rec => p_clmv_tbl(i),
4316 x_clev_rec => x_clev_tbl(i),
4317 x_klev_rec => x_klev_tbl(i)
4318 );
4319
4320 EXIT WHEN (i = p_clmv_tbl.LAST);
4321 i := p_clmv_tbl.NEXT(i);
4322 END LOOP;
4323 END IF;
4324
4325 end;
4326
4327 ----------------------------------------------------------------------------------
4328 -- Start of comments
4329 --
4330 -- Procedure Name : create_credit_limit
4331 -- Description : wrapper api for create_contract_line
4332 -- Business Rules :
4333 -- Parameters :
4334 -- Version : 1.0
4335 -- End of comments
4336 ----------------------------------------------------------------------------------
4337 PROCEDURE create_credit_limit(
4338 p_api_version IN NUMBER,
4339 p_init_msg_list IN VARCHAR2,
4340 x_return_status OUT NOCOPY VARCHAR2,
4341 x_msg_count OUT NOCOPY NUMBER,
4342 x_msg_data OUT NOCOPY VARCHAR2,
4343 -- p_clev_tbl IN okl_okc_migration_pvt.clev_tbl_type,
4344 p_clev_tbl IN clev_tbl_type,
4345 p_klev_tbl IN klev_tbl_type,
4346 -- x_clev_tbl OUT NOCOPY okl_okc_migration_pvt.clev_tbl_type,
4347 x_clev_tbl OUT NOCOPY clev_tbl_type,
4348 x_klev_tbl OUT NOCOPY klev_tbl_type)
4349 is
4350 l_api_name CONSTANT VARCHAR2(30) := 'create_credit_limit_pub';
4351 l_api_version CONSTANT NUMBER := 1.0;
4352 i NUMBER;
4353 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4354 lp_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
4355 lx_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
4356
4357 begin
4358 -- Set API savepoint
4359 SAVEPOINT create_credit_limit_pub;
4360
4361 -- Check for call compatibility
4362 IF (NOT FND_API.Compatible_API_Call (l_api_version,
4363 p_api_version,
4364 l_api_name,
4365 G_PKG_NAME ))
4366 THEN
4367 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4368 END IF;
4369
4370 -- Initialize message list if requested
4371 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
4372 FND_MSG_PUB.initialize;
4373 END IF;
4374
4375 -- Initialize API status to success
4376 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4377
4378
4379 /*** Begin API body ****************************************************/
4380
4381 --DBMS_OUTPUT.PUT_LINE('before copy_clev_tbl');
4382
4383 copy_clev_tbl(
4384 p_clev_tbl => p_clev_tbl,
4385 p_clev_migr_tbl => lp_clev_tbl
4386 );
4387
4388 --DBMS_OUTPUT.PUT_LINE('after copy_clev_tbl');
4389 validate_credit_limit(
4390 p_api_version => p_api_version,
4391 p_init_msg_list => p_init_msg_list,
4392 x_return_status => x_return_status,
4393 x_msg_count => x_msg_count,
4394 x_msg_data => x_msg_data,
4395 p_mode => G_CREATE_MODE,
4396 p_clev_tbl => lp_clev_tbl,
4397 p_klev_tbl => p_klev_tbl);
4398
4399 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4400 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4401 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4402 raise OKC_API.G_EXCEPTION_ERROR;
4403 End If;
4404
4405
4406 OKL_CONTRACT_PUB.create_contract_line(
4407 p_api_version => p_api_version,
4408 p_init_msg_list => p_init_msg_list,
4409 x_return_status => x_return_status,
4410 x_msg_count => x_msg_count,
4411 x_msg_data => x_msg_data,
4412 p_clev_tbl => lp_clev_tbl,
4413 p_klev_tbl => p_klev_tbl,
4414 x_clev_tbl => lx_clev_tbl,
4415 x_klev_tbl => x_klev_tbl);
4416
4417 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4418 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4419 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4420 raise OKC_API.G_EXCEPTION_ERROR;
4421 End If;
4422
4423 -- check after record created
4424 x_return_status := validate_credit_limit_after(
4425 p_chr_id => lp_clev_tbl(lp_clev_tbl.FIRST).dnz_chr_id
4426 ,p_mode => 'NEW');
4427
4428 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4429 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4430 -- Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4431 -- raise OKC_API.G_EXCEPTION_ERROR;
4432 End If;
4433
4434 /*** End API body ******************************************************/
4435
4436 -- Get message count and if count is 1, get message info
4437 FND_MSG_PUB.Count_And_Get
4438 (p_count => x_msg_count,
4439 p_data => x_msg_data);
4440
4441 EXCEPTION
4442 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4443 ROLLBACK TO create_credit_limit_pub;
4444 x_return_status := OKL_API.G_RET_STS_ERROR;
4445 FND_MSG_PUB.Count_And_Get
4446 (p_count => x_msg_count,
4447 p_data => x_msg_data);
4448
4449 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4450 ROLLBACK TO create_credit_limit_pub;
4451 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4452 FND_MSG_PUB.Count_And_Get
4453 (p_count => x_msg_count,
4454 p_data => x_msg_data);
4455
4456 WHEN OTHERS THEN
4457 ROLLBACK TO create_credit_limit_pub;
4458 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4459 OKL_API.Set_Message(p_app_name => G_APP_NAME,
4460 p_msg_name => G_UNEXPECTED_ERROR,
4461 p_token1 => G_SQLCODE_TOKEN,
4462 p_token1_value => SQLCODE,
4463 p_token2 => G_SQLERRM_TOKEN,
4464 p_token2_value => SQLERRM);
4465 FND_MSG_PUB.Count_And_Get
4466 (p_count => x_msg_count,
4467 p_data => x_msg_data);
4468
4469 end;
4470 -- rabhupat bug 4435390 start (cklee okl.h Bug 4506351 (okl.g bug#4435390))
4471 ----------------------------------------------------------------------------------
4472 -- Start of comments
4473 --
4474 -- Procedure Name : is_updated
4475 -- Description : check if the text fields are updated
4476 -- Business Rules :
4477 -- Parameters :
4478 -- Version : 1.0
4479 -- End of comments
4480 ----------------------------------------------------------------------------------
4481 FUNCTION is_updated(p_old_val IN VARCHAR2,
4482 p_new_val IN VARCHAR2) RETURN BOOLEAN IS
4483 l_return_val BOOLEAN;
4484 BEGIN
4485 l_return_val := FALSE;
4486 -- if the value in the database is NULL and the value passed from UI is not null
4487 IF(p_old_val IS NULL AND (p_new_val IS NOT NULL AND p_new_val <> OKL_API.G_MISS_CHAR)) THEN
4488 l_return_val := TRUE;
4489 -- if the value in the database is not null and the value passed from UI is NULL
4490 ELSIF(p_old_val IS NOT NULL AND (p_new_val IS NULL OR p_new_val = OKL_API.G_MISS_CHAR)) THEN
4491 l_return_val := TRUE;
4492 -- if the value in the database and the value passed from the database are not null and not equal
4493 ELSIF(p_old_val IS NOT NULL AND (p_new_val IS NOT NULL AND p_new_val <> OKL_API.G_MISS_CHAR)) THEN
4494 IF(p_old_val <> p_new_val) THEN
4495 l_return_val := TRUE;
4496 END IF;
4497 END IF;
4498
4499 RETURN l_return_val;
4500
4501 END is_updated;
4502
4503 ----------------------------------------------------------------------------------
4504 -- Start of comments
4505 --
4506 -- Procedure Name : is_updated
4507 -- Description : check if the number fields are updated
4508 -- Business Rules :
4509 -- Parameters :
4510 -- Version : 1.0
4511 -- End of comments
4512 ----------------------------------------------------------------------------------
4513 FUNCTION is_updated(p_old_val IN NUMBER,
4514 p_new_val IN NUMBER) RETURN BOOLEAN IS
4515 l_return_val BOOLEAN;
4516 BEGIN
4517 l_return_val := FALSE;
4518 -- if the value in the database is NULL and the value passed from UI is not null
4519 IF(p_old_val IS NULL AND (p_new_val IS NOT NULL AND p_new_val <> OKL_API.G_MISS_NUM)) THEN
4520 l_return_val := TRUE;
4521 -- if the value in the database is not null and the value passed from UI is NULL
4522 ELSIF(p_old_val IS NOT NULL AND (p_new_val IS NULL OR p_new_val = OKL_API.G_MISS_NUM)) THEN
4523 l_return_val := TRUE;
4524 -- if the value in the database and the value passed from the database are not null and not equal
4525 ELSIF(p_old_val IS NOT NULL AND (p_new_val IS NOT NULL AND p_new_val <> OKL_API.G_MISS_NUM)) THEN
4526 IF(p_old_val <> p_new_val) THEN
4527 l_return_val := TRUE;
4528 END IF;
4529 END IF;
4530
4531 RETURN l_return_val;
4532
4533 END is_updated;
4534
4535 ----------------------------------------------------------------------------------
4536 -- Start of comments
4537 --
4538 -- Procedure Name : is_updated
4539 -- Description : check if the date fields are updated
4540 -- Business Rules :
4541 -- Parameters :
4542 -- Version : 1.0
4543 -- End of comments
4544 ----------------------------------------------------------------------------------
4545 FUNCTION is_updated(p_old_val IN DATE,
4546 p_new_val IN DATE) RETURN BOOLEAN IS
4547 l_return_val BOOLEAN;
4548 BEGIN
4549 l_return_val := FALSE;
4550 -- if the value in the database is NULL and the value passed from UI is not null
4551 IF(p_old_val IS NULL AND (p_new_val IS NOT NULL AND p_new_val <> OKL_API.G_MISS_DATE)) THEN
4552 l_return_val := TRUE;
4553 -- if the value in the database is not null and the value passed from UI is NULL
4554 ELSIF(p_old_val IS NOT NULL AND (p_new_val IS NULL OR p_new_val = OKL_API.G_MISS_DATE)) THEN
4555 l_return_val := TRUE;
4556 -- if the value in the database and the value passed from the database are not null and not equal
4557 ELSIF(p_old_val IS NOT NULL AND (p_new_val IS NOT NULL AND p_new_val <> OKL_API.G_MISS_DATE)) THEN
4558 IF(p_old_val <> p_new_val) THEN
4559 l_return_val := TRUE;
4560 END IF;
4561 END IF;
4562
4563 RETURN l_return_val;
4564
4565 END is_updated;
4566
4567 ----------------------------------------------------------------------------------
4568 -- Start of comments
4569 --
4570 -- Procedure Name : update_credit_limit
4571 -- Description : check if the record is actually updated or not
4572 -- Business Rules :
4573 -- Parameters :
4574 -- Version : 1.0
4575 -- End of comments
4576 ----------------------------------------------------------------------------------
4577 FUNCTION is_credit_limit_updated(p_clev_rec IN clev_rec_type,
4578 p_klev_rec IN klev_rec_type) RETURN BOOLEAN IS
4579 -- cursor to fetch the okc_k_lines fields which are updatable by user
4580 CURSOR c_clev_csr IS
4581 SELECT ITEM_DESCRIPTION,
4582 START_DATE
4583 FROM OKC_K_LINES_V
4584 WHERE ID = p_clev_rec.ID;
4585 -- cursor to fetch the okl_k_lines fields which are updatable by user
4586 CURSOR c_klev_csr IS
4587 SELECT AMOUNT,
4588 CREDIT_NATURE
4589 FROM OKL_K_LINES_V
4590 WHERE ID = p_clev_rec.ID;
4591
4592 l_return_val BOOLEAN;
4593
4594 BEGIN
4595
4596 l_return_val := FALSE;
4597 -- fetch the okc_k_lines fields for the credit limit record
4598 FOR l_clev_csr_rec IN c_clev_csr
4599 LOOP
4600 -- if start date or item description is updated then return true
4601 IF((is_updated(l_clev_csr_rec.ITEM_DESCRIPTION, p_clev_rec.ITEM_DESCRIPTION)) OR
4602 (is_updated(l_clev_csr_rec.START_DATE, p_clev_rec.START_DATE))) THEN
4603 l_return_val := TRUE;
4604 END IF;
4605 END LOOP;
4606 -- fetch the okl_k_lines fields for the credit limit record
4607 FOR l_klev_csr_rec IN c_klev_csr
4608 LOOP
4609 -- if amount or credit nature is updated then return true
4610 IF((is_updated(l_klev_csr_rec.AMOUNT, p_klev_rec.AMOUNT)) OR
4611 (is_updated(l_klev_csr_rec.CREDIT_NATURE, p_klev_rec.CREDIT_NATURE))) THEN
4612 l_return_val := TRUE;
4613 END IF;
4614 END LOOP;
4615
4616 RETURN l_return_val;
4617
4618 END is_credit_limit_updated;
4619 -- rabhupat bug 4435390 end (cklee okl.h Bug 4506351 (okl.g bug#4435390))
4620
4621 ----------------------------------------------------------------------------------
4622 -- Start of comments
4623 --
4624 -- Procedure Name : update_credit_limit
4625 -- Description : wrapper api for update_contract_line
4626 -- Business Rules :
4627 -- Parameters :
4628 -- Version : 1.0
4629 -- End of comments
4630 ----------------------------------------------------------------------------------
4631 PROCEDURE update_credit_limit(
4632 p_api_version IN NUMBER,
4633 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4634 x_return_status OUT NOCOPY VARCHAR2,
4635 x_msg_count OUT NOCOPY NUMBER,
4636 x_msg_data OUT NOCOPY VARCHAR2,
4637 -- p_clev_tbl IN okl_okc_migration_pvt.clev_tbl_type,
4638 p_clev_tbl IN clev_tbl_type,
4639 p_klev_tbl IN klev_tbl_type,
4640 -- x_clev_tbl OUT NOCOPY okl_okc_migration_pvt.clev_tbl_type,
4641 x_clev_tbl OUT NOCOPY clev_tbl_type,
4642 x_klev_tbl OUT NOCOPY klev_tbl_type)
4643 is
4644 l_api_name CONSTANT VARCHAR2(30) := 'update_credit_limit_pub';
4645 l_api_version CONSTANT NUMBER := 1.0;
4646 i NUMBER;
4647 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4648 lp_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
4649 lx_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
4650
4651 -- rabhupat bug 4435390 start (cklee okl.h Bug 4506351 (okl.g bug#4435390))
4652 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4653 -- rabhupat bug 4435390 end (cklee okl.h Bug 4506351 (okl.g bug#4435390))
4654
4655 begin
4656 -- Set API savepoint
4657 SAVEPOINT update_credit_limit_pub;
4658
4659 -- Check for call compatibility
4660 IF (NOT FND_API.Compatible_API_Call (l_api_version,
4661 p_api_version,
4662 l_api_name,
4663 G_PKG_NAME ))
4664 THEN
4665 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4666 END IF;
4667
4668 -- Initialize message list if requested
4669 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
4670 FND_MSG_PUB.initialize;
4671 END IF;
4672
4673 -- Initialize API status to success
4674 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4675
4676
4677 /*** Begin API body ****************************************************/
4678
4679 copy_clev_tbl(
4680 p_clev_tbl => p_clev_tbl,
4681 p_clev_migr_tbl => lp_clev_tbl
4682 );
4683
4684 validate_credit_limit(
4685 p_api_version => p_api_version,
4686 p_init_msg_list => p_init_msg_list,
4687 x_return_status => x_return_status,
4688 x_msg_count => x_msg_count,
4689 x_msg_data => x_msg_data,
4690 p_mode => G_UPDATE_MODE,
4691 p_clev_tbl => lp_clev_tbl,
4692 p_klev_tbl => p_klev_tbl);
4693
4694 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4695 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4696 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4697 raise OKC_API.G_EXCEPTION_ERROR;
4698 End If;
4699 /*comment out: cklee okl.h Bug 4506351 (okl.g bug#4435390)
4700
4701 OKL_CONTRACT_PUB.update_contract_line(
4702 p_api_version => p_api_version,
4703 p_init_msg_list => p_init_msg_list,
4704 x_return_status => x_return_status,
4705 x_msg_count => x_msg_count,
4706 x_msg_data => x_msg_data,
4707 p_clev_tbl => lp_clev_tbl,
4708 p_klev_tbl => p_klev_tbl,
4709 x_clev_tbl => lx_clev_tbl,
4710 x_klev_tbl => x_klev_tbl);
4711 */
4712 -- rabhupat bug 4435390 start (cklee okl.h Bug 4506351 (okl.g bug#4435390))
4713 IF(p_clev_tbl.COUNT > 0) THEN
4714 i := p_clev_tbl.FIRST;
4715 LOOP
4716 -- check if the credit limit record is actually updated. Then only pass
4717 -- it to the update api
4718 IF(is_credit_limit_updated(p_clev_tbl(i),p_klev_tbl(i)))THEN
4719 OKL_CONTRACT_PUB.update_contract_line(
4720 p_api_version => p_api_version,
4721 p_init_msg_list => p_init_msg_list,
4722 x_return_status => x_return_status,
4723 x_msg_count => x_msg_count,
4724 x_msg_data => x_msg_data,
4725 p_clev_rec => lp_clev_tbl(i),
4726 p_klev_rec => p_klev_tbl(i),
4727 x_clev_rec => lx_clev_tbl(i),
4728 x_klev_rec => x_klev_tbl(i));
4729 If x_return_status <> OKL_API.G_RET_STS_SUCCESS Then
4730 If l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR Then
4731 l_overall_status := x_return_status;
4732 End If;
4733 End If;
4734 END IF;
4735 EXIT WHEN (i = p_clev_tbl.LAST);
4736 i := p_clev_tbl.NEXT(i);
4737 END LOOP;
4738 -- return overall status
4739 x_return_status := l_overall_status;
4740 END IF;
4741 -- rabhupat bug 4435390 end (cklee okl.h Bug 4506351 (okl.g bug#4435390))
4742
4743 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4744 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4745 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4746 raise OKC_API.G_EXCEPTION_ERROR;
4747 End If;
4748
4749 -- check after record created
4750 x_return_status := validate_credit_limit_after(
4751 p_chr_id => lp_clev_tbl(lp_clev_tbl.FIRST).dnz_chr_id
4752 ,p_mode => 'UPDATE');
4753
4754 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4755 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4756 -- Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4757 -- raise OKC_API.G_EXCEPTION_ERROR;
4758 End If;
4759
4760 /*** End API body ******************************************************/
4761
4762 -- Get message count and if count is 1, get message info
4763 FND_MSG_PUB.Count_And_Get
4764 (p_count => x_msg_count,
4765 p_data => x_msg_data);
4766
4767 EXCEPTION
4768 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4769 ROLLBACK TO update_credit_limit_pub;
4770 x_return_status := OKL_API.G_RET_STS_ERROR;
4771 FND_MSG_PUB.Count_And_Get
4772 (p_count => x_msg_count,
4773 p_data => x_msg_data);
4774
4775 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4776 ROLLBACK TO update_credit_limit_pub;
4777 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4778
4779 FND_MSG_PUB.Count_And_Get
4780 (p_count => x_msg_count,
4781 p_data => x_msg_data);
4782
4783 WHEN OTHERS THEN
4784 ROLLBACK TO update_credit_limit_pub;
4785 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4786 OKL_API.Set_Message(p_app_name => G_APP_NAME,
4787 p_msg_name => G_UNEXPECTED_ERROR,
4788 p_token1 => G_SQLCODE_TOKEN,
4789 p_token1_value => SQLCODE,
4790 p_token2 => G_SQLERRM_TOKEN,
4791 p_token2_value => SQLERRM);
4792 FND_MSG_PUB.Count_And_Get
4793 (p_count => x_msg_count,
4794 p_data => x_msg_data);
4795
4796 end;
4797
4798 ----------------------------------------------------------------------------------
4799 -- Start of comments
4800 --
4801 -- Procedure Name : delete_credit_limit
4802 -- Description : wrapper api for delete_contract_line
4803 -- Business Rules :
4804 -- Parameters :
4805 -- Version : 1.0
4806 -- End of comments
4807 ----------------------------------------------------------------------------------
4808 PROCEDURE delete_credit_limit(
4809 p_api_version IN NUMBER,
4810 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4811 x_return_status OUT NOCOPY VARCHAR2,
4812 x_msg_count OUT NOCOPY NUMBER,
4813 x_msg_data OUT NOCOPY VARCHAR2,
4814 -- p_clev_tbl IN okl_okc_migration_pvt.clev_tbl_type,
4815 p_clev_tbl IN clev_tbl_type,
4816 p_klev_tbl IN klev_tbl_type)
4817 is
4818
4819 l_api_name CONSTANT VARCHAR2(30) := 'delete_credit_limit_pub';
4820 l_api_version CONSTANT NUMBER := 1.0;
4821 i NUMBER;
4822 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4823 lp_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
4824 -- lx_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
4825
4826 begin
4827 -- Set API savepoint
4828 SAVEPOINT delete_credit_limit_pub;
4829
4830
4831 -- Check for call compatibility
4832 IF (NOT FND_API.Compatible_API_Call (l_api_version,
4833 p_api_version,
4834 l_api_name,
4835 G_PKG_NAME ))
4836 THEN
4837 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4838 END IF;
4839
4840 -- Initialize message list if requested
4841 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
4842 FND_MSG_PUB.initialize;
4843 END IF;
4844
4845 -- Initialize API status to success
4846 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4847
4848
4849 /*** Begin API body ****************************************************/
4850
4851 copy_clev_tbl(
4852 p_clev_tbl => p_clev_tbl,
4853 p_clev_migr_tbl => lp_clev_tbl
4854 );
4855
4856 validate_credit_limit(
4857 p_api_version => p_api_version,
4858 p_init_msg_list => p_init_msg_list,
4859 x_return_status => x_return_status,
4860 x_msg_count => x_msg_count,
4861 x_msg_data => x_msg_data,
4862 p_mode => G_DELETE_MODE,
4863 p_clev_tbl => lp_clev_tbl,
4864 p_klev_tbl => p_klev_tbl);
4865
4866 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4867 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4868 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4869 raise OKC_API.G_EXCEPTION_ERROR;
4870 End If;
4871
4872
4873 OKL_CONTRACT_PUB.delete_contract_line(
4874 p_api_version => p_api_version,
4875 p_init_msg_list => p_init_msg_list,
4876 x_return_status => x_return_status,
4877 x_msg_count => x_msg_count,
4878 x_msg_data => x_msg_data,
4879 p_clev_tbl => lp_clev_tbl,
4880 p_klev_tbl => p_klev_tbl);
4881
4882 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4883 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4884 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4885 raise OKC_API.G_EXCEPTION_ERROR;
4886 End If;
4887
4888 -- check after record created
4889 x_return_status := validate_credit_limit_after(
4890 p_chr_id => lp_clev_tbl(lp_clev_tbl.FIRST).dnz_chr_id
4891 ,p_mode => 'DELETE');
4892
4893 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4894 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4895 -- Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
4896 -- raise OKC_API.G_EXCEPTION_ERROR;
4897 End If;
4898
4899
4900 /*** End API body ******************************************************/
4901
4902 -- Get message count and if count is 1, get message info
4903 FND_MSG_PUB.Count_And_Get
4904 (p_count => x_msg_count,
4905 p_data => x_msg_data);
4906
4907
4908 EXCEPTION
4909 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4910 ROLLBACK TO delete_credit_limit_pub;
4911 x_return_status := OKL_API.G_RET_STS_ERROR;
4912 FND_MSG_PUB.Count_And_Get
4913 (p_count => x_msg_count,
4914 p_data => x_msg_data);
4915
4916 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4917 ROLLBACK TO delete_credit_limit_pub;
4918 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4919 FND_MSG_PUB.Count_And_Get
4920 (p_count => x_msg_count,
4921 p_data => x_msg_data);
4922
4923 WHEN OTHERS THEN
4924 ROLLBACK TO delete_credit_limit_pub;
4925 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4926 OKL_API.Set_Message(p_app_name => G_APP_NAME,
4927 p_msg_name => G_UNEXPECTED_ERROR,
4928 p_token1 => G_SQLCODE_TOKEN,
4929 p_token1_value => SQLCODE,
4930 p_token2 => G_SQLERRM_TOKEN,
4931 p_token2_value => SQLERRM);
4932 FND_MSG_PUB.Count_And_Get
4933 (p_count => x_msg_count,
4934 p_data => x_msg_data);
4935
4936 end;
4937
4938 ----------------------------------------------------------------------------------
4939 -- Start of comments
4940 --
4941 -- Procedure Name : create_credit
4942 -- Description : creates a credit line and credit limits
4943
4944 -- Business Rules :
4945 -- Parameters :
4946 -- Version : 1.0
4947 -- End of comments
4948 ----------------------------------------------------------------------------------
4949 PROCEDURE create_credit(
4950 p_api_version IN NUMBER,
4951 p_init_msg_list IN VARCHAR2,
4952 x_return_status OUT NOCOPY VARCHAR2,
4953 x_msg_count OUT NOCOPY NUMBER,
4954 x_msg_data OUT NOCOPY VARCHAR2,
4955 p_crdv_rec IN crdv_rec_type,
4956 p_clmv_tbl IN clmv_tbl_type,
4957 x_crdv_rec OUT NOCOPY crdv_rec_type,
4958 x_clmv_tbl OUT NOCOPY clmv_tbl_type)
4959 As
4960 l_api_name CONSTANT VARCHAR2(30) := 'create_credit_pub';
4961 l_api_version CONSTANT NUMBER := 1.0;
4962 i NUMBER;
4963 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4964 l_effective_from DATE;
4965 l_sts_code VARCHAR2(30);
4966
4967 lp_clev_tbl clev_tbl_type;
4968 lp_klev_tbl klev_tbl_type;
4969 x_clev_tbl clev_tbl_type;
4970 x_klev_tbl klev_tbl_type;
4971
4972 cursor c_crd(p_chr_id number) is
4973 --start modified abhsaxen for performance SQLID 20562820
4974
4975 SELECT
4976 chrb.contract_number contract_number,
4977 chrb.start_date start_date,
4978 chrb.end_date end_date,
4979 chrb.currency_code currency_code,
4980 chrb.sts_code sts_code,
4981 chrb.cust_acct_id cust_acct_id,
4982 chrt.description description,
4983 khr.currency_conversion_type currency_conversion_type,
4984 khr.currency_conversion_rate currency_conversion_rate,
4985 khr.currency_conversion_date currency_conversion_date,
4986 khr.revolving_credit_yn revolving_credit_yn,
4987 cpl.id party_roles_id,
4988 cpl.object1_id1 customer_id1,
4989 cpl.object1_id2 customer_id2,
4990 cpl.jtot_object1_code customer_jtot_object_code,
4991 party.name customer_name,
4992 rul.rule_information1 creditline_ckl_id,
4993 rul.rule_information2 funding_ckl_id,
4994 rul.rgp_id chklst_tpl_rgp_id,
4995 rul.id chklst_tpl_rule_id,
4996 CA.ACCOUNT_NUMBER cust_acct_number
4997 FROM
4998 OKC_K_HEADERS_B CHRB,
4999 OKC_K_HEADERS_TL CHRT,
5000 OKL_K_HEADERS KHR,
5001 HZ_CUST_ACCOUNTS CA,
5002 OKC_RULES_B RUL,
5003 OKX_PARTIES_V PARTY,
5004 OKC_K_PARTY_ROLES_B CPL
5005 WHERE chrb.id = chrt.id
5006 AND chrt.language = USERENV('LANG')
5007 AND chrb.id = khr.id
5008 AND chrb.scs_code = 'CREDITLINE_CONTRACT'
5009 AND CA.CUST_ACCOUNT_ID(+) = chrb.cust_acct_id
5010 AND rul.rule_information_category(+) = 'LACCLT'
5011 AND rul.dnz_chr_id(+) = chrb.id
5012 AND party.id1 = cpl.object1_id1
5013 AND party.id2 = cpl.object1_id2
5014 AND cpl.rle_code = 'LESSEE'
5015 AND cpl.chr_id = chrb.id
5016 AND cpl.DNZ_CHR_ID = cpl.chr_id
5017 AND CHRB.ID = p_chr_id
5018 --end modified abhsaxen for performance SQLID 20562820
5019 ;
5020 cursor c_clm(p_chr_id number) is
5021 select
5022 okc.id,
5023 okc.dnz_chr_id,
5024 okc.item_description,
5025 okc.start_date,
5026 okl.credit_nature,
5027 okl.amount
5028 from OKC_K_LINES_V okc,
5029 OKL_K_LINES okl
5030 where okc.id = okl.id
5031 and okc.dnz_chr_id = p_chr_id;
5032
5033 r_crd c_crd%ROWTYPE;
5034 r_clm c_clm%ROWTYPE;
5035
5036 begin
5037 -- Set API savepoint
5038 SAVEPOINT create_credit_pub;
5039
5040 -- Check for call compatibility
5041 IF (NOT FND_API.Compatible_API_Call (l_api_version,
5042 p_api_version,
5043 l_api_name,
5044 G_PKG_NAME ))
5045 THEN
5046 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5047 END IF;
5048
5049 -- Initialize message list if requested
5050 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
5051 FND_MSG_PUB.initialize;
5052 END IF;
5053
5054 -- Initialize API status to success
5055 x_return_status := OKL_API.G_RET_STS_SUCCESS;
5056
5057
5058 /*** Begin API body ****************************************************/
5059
5060 ----------------------------------------------------------------
5061 -- create credit line
5062 ----------------------------------------------------------------
5063 -- set default date for effective from
5064 IF (NULLIF(p_crdv_rec.effective_from,OKC_API.G_MISS_DATE) IS NULL) THEN
5065 l_effective_from := TRUNC(SYSDATE);
5066 ELSE
5067 l_effective_from := p_crdv_rec.effective_from;
5068 END IF;
5069
5070 -- set default 'NEW' for sts_code
5071 IF (NULLIF(p_crdv_rec.sts_code,OKC_API.G_MISS_CHAR) IS NULL) THEN
5072 l_sts_code := 'NEW';
5073 ELSE
5074 l_sts_code := p_crdv_rec.sts_code;
5075 END IF;
5076
5077 create_credit(
5078 p_api_version => p_api_version,
5079 p_init_msg_list => p_init_msg_list,
5080 x_return_status => x_return_status,
5081 x_msg_count => x_msg_count,
5082 x_msg_data => x_msg_data,
5083 p_contract_number => NULLIF(p_crdv_rec.contract_number,OKC_API.G_MISS_CHAR),
5084 p_description => NULLIF(p_crdv_rec.description,OKC_API.G_MISS_CHAR),
5085 p_customer_id1 => NULLIF(p_crdv_rec.customer_id1,OKC_API.G_MISS_CHAR),
5086 p_customer_id2 => NULLIF(p_crdv_rec.customer_id2,OKC_API.G_MISS_CHAR),
5087 p_customer_code => NULLIF(p_crdv_rec.customer_code,OKC_API.G_MISS_CHAR),
5088 p_customer_name => NULLIF(p_crdv_rec.customer_name,OKC_API.G_MISS_CHAR),
5089 p_effective_from => l_effective_from,
5090 p_effective_to => NULLIF(p_crdv_rec.effective_to,OKC_API.G_MISS_DATE),
5091 p_currency_code => NULLIF(p_crdv_rec.currency_code,OKC_API.G_MISS_CHAR),
5092 p_currency_conv_type => NULLIF(p_crdv_rec.currency_conv_type,OKC_API.G_MISS_CHAR),
5093 p_currency_conv_rate => NULLIF(p_crdv_rec.currency_conv_rate,OKC_API.G_MISS_NUM),
5094 p_currency_conv_date => NULLIF(p_crdv_rec.currency_conv_date,OKC_API.G_MISS_DATE),
5095 p_revolving_credit_yn => NULLIF(p_crdv_rec.revolving_credit_yn,OKC_API.G_MISS_CHAR),
5096 p_sts_code => l_sts_code,
5097 p_credit_ckl_id => NULLIF(p_crdv_rec.credit_ckl_id,OKC_API.G_MISS_NUM),
5098 p_funding_ckl_id => NULLIF(p_crdv_rec.funding_ckl_id,OKC_API.G_MISS_NUM),
5099 p_cust_acct_id => NULLIF(p_crdv_rec.cust_acct_id,OKC_API.G_MISS_NUM),
5100 p_cust_acct_number => NULLIF(p_crdv_rec.cust_acct_number,OKC_API.G_MISS_CHAR),
5101 x_chr_id => x_crdv_rec.id
5102 );
5103
5104 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5105 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5106 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5107 raise OKC_API.G_EXCEPTION_ERROR;
5108 End If;
5109
5110 ----------------------------------------------------------------
5111 -- copy out record
5112 ----------------------------------------------------------------
5113 IF (NOT (x_crdv_rec.id IS NULL OR
5114 x_crdv_rec.id = OKC_API.G_MISS_NUM)) THEN
5115
5116 OPEN c_crd(x_crdv_rec.id);
5117 FETCH c_crd INTO r_crd;
5118 CLOSE c_crd;
5119
5120 x_crdv_rec.contract_number := r_crd.contract_number;
5121 x_crdv_rec.description := r_crd.description;
5122 x_crdv_rec.party_roles_id := r_crd.party_roles_id;
5123 x_crdv_rec.customer_id1 := r_crd.customer_id1;
5124 x_crdv_rec.customer_id2 := r_crd.customer_id2;
5125 x_crdv_rec.customer_code := r_crd.customer_jtot_object_code;
5126 x_crdv_rec.customer_name := r_crd.customer_name;
5127 x_crdv_rec.effective_from := r_crd.start_date;
5128 x_crdv_rec.effective_to := r_crd.end_date;
5129 x_crdv_rec.currency_code := r_crd.currency_code;
5130 x_crdv_rec.currency_conv_type := r_crd.currency_conversion_type;
5131 x_crdv_rec.currency_conv_rate := r_crd.currency_conversion_rate;
5132 x_crdv_rec.currency_conv_date := r_crd.currency_conversion_date;
5133 x_crdv_rec.revolving_credit_yn := r_crd.revolving_credit_yn;
5134 x_crdv_rec.sts_code := r_crd.sts_code;
5135 x_crdv_rec.credit_ckl_id := TO_NUMBER(r_crd.creditline_ckl_id);
5136 x_crdv_rec.funding_ckl_id := TO_NUMBER(r_crd.funding_ckl_id);
5137 x_crdv_rec.chklst_tpl_rgp_id := r_crd.chklst_tpl_rgp_id;
5138 x_crdv_rec.chklst_tpl_rule_id := r_crd.chklst_tpl_rule_id;
5139 x_crdv_rec.cust_acct_id := r_crd.cust_acct_id;
5140 x_crdv_rec.cust_acct_number := r_crd.cust_acct_number;
5141
5142 ----------------------------------------------------------------
5143 -- create credit limits
5144 ----------------------------------------------------------------
5145 IF (p_clmv_tbl.COUNT > 0) THEN
5146
5147 copy_clmv_tbl(
5148 p_chr_id => x_crdv_rec.id,
5149 p_clmv_tbl => p_clmv_tbl,
5150 x_clev_tbl => lp_clev_tbl,
5151 x_klev_tbl => lp_klev_tbl
5152 );
5153
5154 create_credit_limit(
5155 p_api_version => p_api_version,
5156 p_init_msg_list => p_init_msg_list,
5157 x_return_status => x_return_status,
5158 x_msg_count => x_msg_count,
5159 x_msg_data => x_msg_data,
5160 p_clev_tbl => lp_clev_tbl,
5161 p_klev_tbl => lp_klev_tbl,
5162 x_clev_tbl => x_clev_tbl,
5163 x_klev_tbl => x_klev_tbl
5164 );
5165
5166 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5167 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5168 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5169 raise OKC_API.G_EXCEPTION_ERROR;
5170 End If;
5171
5172 ----------------------------------------------------------------
5173 -- copy out record
5174 ----------------------------------------------------------------
5175 OPEN c_clm(x_crdv_rec.id);
5176 i := 1;
5177 LOOP
5178 FETCH c_clm INTO r_clm;
5179 EXIT WHEN c_clm%NOTFOUND;
5180
5181 x_clmv_tbl(i).id := r_clm.id;
5182 x_clmv_tbl(i).chr_id := r_clm.dnz_chr_id;
5183 x_clmv_tbl(i).item_description := r_clm.item_description;
5184 x_clmv_tbl(i).start_date := r_clm.start_date;
5185 x_clmv_tbl(i).credit_nature := r_clm.credit_nature;
5186 x_clmv_tbl(i).amount := r_clm.amount;
5187
5188 i := i + 1;
5189 END LOOP;
5190 CLOSE c_clm;
5191
5192 END IF;
5193
5194 END IF;
5195
5196
5197 /*** End API body ******************************************************/
5198
5199 -- Get message count and if count is 1, get message info
5200 FND_MSG_PUB.Count_And_Get
5201 (p_count => x_msg_count,
5202 p_data => x_msg_data);
5203
5204 EXCEPTION
5205 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5206 ROLLBACK TO create_credit_pub;
5207 x_return_status := OKL_API.G_RET_STS_ERROR;
5208 FND_MSG_PUB.Count_And_Get
5209 (p_count => x_msg_count,
5210 p_data => x_msg_data);
5211
5212 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5213 ROLLBACK TO create_credit_pub;
5214 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
5215 FND_MSG_PUB.Count_And_Get
5216 (p_count => x_msg_count,
5217 p_data => x_msg_data);
5218
5219 WHEN OTHERS THEN
5220 ROLLBACK TO create_credit_pub;
5221 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
5222 OKL_API.Set_Message(p_app_name => G_APP_NAME,
5223 p_msg_name => G_UNEXPECTED_ERROR,
5224 p_token1 => G_SQLCODE_TOKEN,
5225 p_token1_value => SQLCODE,
5226 p_token2 => G_SQLERRM_TOKEN,
5227 p_token2_value => SQLERRM);
5228 FND_MSG_PUB.Count_And_Get
5229 (p_count => x_msg_count,
5230 p_data => x_msg_data);
5231
5232 end create_credit;
5233
5234 ----------------------------------------------------------------------------------
5235 -- Start of comments
5236 --
5237 -- Procedure Name : create_credit
5238 -- Description : creates a credit
5239
5240 -- Business Rules :
5241 -- Parameters :
5242 -- Version : 1.0
5243 -- End of comments
5244 ----------------------------------------------------------------------------------
5245 PROCEDURE create_credit(
5246 p_api_version IN NUMBER,
5247 p_init_msg_list IN VARCHAR2,
5248 x_return_status OUT NOCOPY VARCHAR2,
5249 x_msg_count OUT NOCOPY NUMBER,
5250 x_msg_data OUT NOCOPY VARCHAR2,
5251 --
5252 p_contract_number IN VARCHAR2,
5253 p_description IN VARCHAR2,
5254 -- p_version_no IN VARCHAR2,
5255 -- p_scs_code IN VARCHAR2,
5256 p_customer_id1 IN VARCHAR2,
5257 p_customer_id2 IN VARCHAR2,
5258 p_customer_code IN VARCHAR2,
5259 p_customer_name IN VARCHAR2,
5260 p_effective_from IN DATE,
5261 p_effective_to IN DATE,
5262 p_currency_code IN VARCHAR2,
5263 -- multi-currency support
5264 p_currency_conv_type IN VARCHAR2,
5265 p_currency_conv_rate IN NUMBER,
5266 p_currency_conv_date IN DATE,
5267 -- multi-currency support
5268 p_revolving_credit_yn IN VARCHAR2,
5269 p_sts_code IN VARCHAR2,
5270 --
5271 -- funding checklist enhancement
5272 p_credit_ckl_id IN NUMBER,
5273 p_funding_ckl_id IN NUMBER,
5274 -- funding checklist enhancement
5275 p_cust_acct_id IN NUMBER, -- 11.5.10 rule migration project
5276 p_cust_acct_number IN VARCHAR2, -- 11.5.10 rule migration project
5277 p_org_id IN NUMBER,
5278 p_organization_id IN NUMBER,
5279 p_source_chr_id IN NUMBER,
5280 x_chr_id OUT NOCOPY NUMBER)
5281 AS
5282 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
5283
5284
5285 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
5286 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
5287 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
5288
5289 lp_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
5290 lx_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
5291
5292 -- funding checklist enhancement for 11.5.9
5293 lp_rgpv_rec rgpv_rec_type;
5294 lp_rulv_rec rulv_rec_type;
5295 lx_rgpv_rec rgpv_rec_type;
5296 lx_rulv_rec rulv_rec_type;
5297 -- funding checklist enhancement for 11.5.9
5298
5299 l_api_version CONSTANT NUMBER := 1.0;
5300
5301 l_api_name VARCHAR2(30) := 'CREATE_CREDIT';
5302 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5303
5304 CURSOR role_csr(p_rle_code VARCHAR2) IS
5305 Select access_level
5306 from OKC_ROLE_SOURCES
5307 where rle_code = p_rle_code
5308 and buy_or_sell = 'S';
5309
5310 l_access_level OKC_ROLE_SOURCES.access_level%TYPE;
5311
5312
5313 BEGIN
5314 x_return_status := OKC_API.START_ACTIVITY(
5315 p_api_name => l_api_name,
5316 p_pkg_name => g_pkg_name,
5317 p_init_msg_list => p_init_msg_list,
5318 l_api_version => l_api_version,
5319 p_api_version => p_api_version,
5320 p_api_type => g_api_type,
5321 x_return_status => x_return_status);
5322
5323 -- check if activity started successfully
5324 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5325 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5326 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5327 raise OKC_API.G_EXCEPTION_ERROR;
5328 End If;
5329
5330 -- fix bug# for set_org_id
5331 okl_context.set_okc_org_context();
5332 --
5333 lp_chrv_rec.sfwt_flag := 'N';
5334 lp_chrv_rec.object_version_number := 1.0;
5335 lp_chrv_rec.sts_code := G_STS_CODE; -- 'ENTERED';
5336 lp_chrv_rec.scs_code := G_SCS_CODE;--p_scs_code;
5337 lp_chrv_rec.contract_number := p_contract_number;
5338
5339 lp_chrv_rec.sts_code := p_sts_code;
5340 lp_chrv_rec.description := p_description;
5341 lp_chrv_rec.short_description := p_description;
5342 -- fixed bug # 2855402
5343 lp_chrv_rec.start_date := TRUNC(p_effective_from);
5344 lp_chrv_rec.end_date := TRUNC(p_effective_to);
5345 -- fixed bug # 2855402
5346 -- to resolve the validation for sign_by_date
5347 -- lp_chrv_rec.sign_by_date := lp_chrv_rec.end_date;
5348 lp_chrv_rec.sign_by_date := null;
5349 lp_chrv_rec.currency_code := p_currency_code;
5350
5351 -- Start bug fix 4148019 27-JAN-05 cklee
5352 If (p_org_id is null or p_org_id = OKC_API.G_MISS_NUM ) then
5353 lp_chrv_rec.authoring_org_id := OKL_CONTEXT.GET_OKC_ORG_ID;
5354 else
5355 lp_chrv_rec.authoring_org_id := p_org_id;
5356 end If;
5357 If (p_organization_id is null or p_organization_id = OKC_API.G_MISS_NUM ) then
5358 lp_chrv_rec.inv_organization_id := OKL_CONTEXT.get_okc_organization_id;
5359 else
5360 lp_chrv_rec.inv_organization_id := p_organization_id;
5361 End If;
5362 -- End bug fix 4148019 27-JAN-05 cklee
5363
5364 -- lp_chrv_rec.currency_code := OKC_CURRENCY_API.GET_OU_CURRENCY(OKL_CONTEXT.GET_OKC_ORG_ID);
5365 lp_chrv_rec.currency_code_renewed := null;
5366 lp_chrv_rec.template_yn := 'N';
5367 lp_chrv_rec.chr_type := 'CYA';
5368 lp_chrv_rec.archived_yn := 'N';
5369 lp_chrv_rec.deleted_yn := 'N';
5370 lp_chrv_rec.buy_or_sell := 'S';
5371 lp_chrv_rec.issue_or_receive := 'I';
5372
5373 lp_chrv_rec.cust_acct_id := p_cust_acct_id; -- 11.5.10 rule migration project
5374
5375 lp_khrv_rec.object_version_number := 1.0;
5376 -- lp_khrv_rec.khr_id := 1;
5377 lp_khrv_rec.generate_accrual_yn := 'Y';
5378 lp_khrv_rec.generate_accrual_override_yn := 'N';
5379 lp_khrv_rec.revolving_credit_yn := p_revolving_credit_yn;
5380 -- multi-currency code support
5381 lp_khrv_rec.currency_conversion_type := p_currency_conv_type;
5382 lp_khrv_rec.currency_conversion_rate := p_currency_conv_rate;
5383 lp_khrv_rec.currency_conversion_date := p_currency_conv_date;
5384 -- multi-currency code support
5385
5386 --
5387 -- Credit header specific validation
5388 --
5389
5390 x_return_status := validate_header_attributes(lp_chrv_rec, lp_khrv_rec, lp_rulv_rec, 'C');
5391 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5392 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5393 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5394 raise OKC_API.G_EXCEPTION_ERROR;
5395 End If;
5396 -- 11.5.10 rule migration start
5397 x_return_status := validate_account_number(p_cust_acct_id => p_cust_acct_id,
5398 p_cust_acct_number => p_cust_acct_number);
5399 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5400 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5401 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5402 raise OKC_API.G_EXCEPTION_ERROR;
5403 End If;
5404 -- 11.5.10 rule migration start
5405
5406 OKL_CONTRACT_PUB.create_contract_header(
5407 p_api_version => p_api_version,
5408 p_init_msg_list => p_init_msg_list,
5409 x_return_status => x_return_status,
5410 x_msg_count => x_msg_count,
5411 x_msg_data => x_msg_data,
5412 p_chrv_rec => lp_chrv_rec,
5413 p_khrv_rec => lp_khrv_rec,
5414 x_chrv_rec => lx_chrv_rec,
5415 x_khrv_rec => lx_khrv_rec);
5416
5417 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5418 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5419 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5420 raise OKC_API.G_EXCEPTION_ERROR;
5421 End If;
5422
5423 x_chr_id := lx_chrv_rec.id;
5424
5425 -- now we attach the party to the header
5426 lp_cplv_rec.object_version_number := 1.0;
5427 lp_cplv_rec.sfwt_flag := OKC_API.G_FALSE;
5428 lp_cplv_rec.dnz_chr_id := x_chr_id;
5429 lp_cplv_rec.chr_id := x_chr_id;
5430 lp_cplv_rec.cle_id := null;
5431 lp_cplv_rec.object1_id1 := p_customer_id1;
5432 lp_cplv_rec.object1_id2 := p_customer_id2;
5433 lp_cplv_rec.jtot_object1_code := p_customer_code;
5434 lp_cplv_rec.rle_code := G_RLE_CODE;
5435
5436 x_return_status := validate_customer(lp_cplv_rec, p_customer_name);
5437 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5438 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5439 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5440 raise OKC_API.G_EXCEPTION_ERROR;
5441 End If;
5442
5443 OKC_CONTRACT_PARTY_PUB.validate_k_party_role(
5444 p_api_version => p_api_version,
5445 p_init_msg_list => p_init_msg_list,
5446 x_return_status => x_return_status,
5447 x_msg_count => x_msg_count,
5448 x_msg_data => x_msg_data,
5449 p_cplv_rec => lp_cplv_rec);
5450
5451
5452 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5453 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5454 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5455 raise OKC_API.G_EXCEPTION_ERROR;
5456 End If;
5457
5458 ----- Changes by Kanti
5459 ----- Validate the JTOT Object code, ID1 and ID2
5460
5461 OPEN role_csr(lp_cplv_rec.rle_code);
5462 FETCH role_csr INTO l_access_level;
5463 CLOSE role_csr;
5464
5465 IF (l_access_level = 'S') THEN
5466
5467 okl_la_validation_util_pvt.VALIDATE_ROLE_JTOT (p_api_version => p_api_version,
5468 p_init_msg_list => OKC_API.G_FALSE,
5469 x_return_status => x_return_status,
5470 x_msg_count => x_msg_count,
5471 x_msg_data => x_msg_data,
5472 p_object_name => lp_cplv_rec.jtot_object1_code,
5473 p_id1 => lp_cplv_rec.object1_id1,
5474 p_id2 => lp_cplv_rec.object1_id2);
5475 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5476 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5477 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5478 RAISE OKC_API.G_EXCEPTION_ERROR;
5479 END IF;
5480
5481 END IF;
5482
5483 ---- Changes End
5484
5485 OKC_CONTRACT_PARTY_PUB.create_k_party_role(
5486 p_api_version => p_api_version,
5487 p_init_msg_list => p_init_msg_list,
5488 x_return_status => x_return_status,
5489 x_msg_count => x_msg_count,
5490 x_msg_data => x_msg_data,
5491 p_cplv_rec => lp_cplv_rec,
5492 x_cplv_rec => lx_cplv_rec);
5493
5494 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5495 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5496 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5497 raise OKC_API.G_EXCEPTION_ERROR;
5498 End If;
5499
5500 -- funding checklist enhancement for 11.5.9
5501 -- rule group FK
5502 lp_rgpv_rec.DNZ_CHR_ID := lx_chrv_rec.id;
5503 lp_rgpv_rec.CHR_ID := lx_chrv_rec.id; -- MUST
5504
5505 -- rule FKs
5506 lp_rulv_rec.DNZ_CHR_ID := lx_chrv_rec.id; -- MUST
5507 lp_rulv_rec.RULE_INFORMATION1 := p_credit_ckl_id;
5508 lp_rulv_rec.RULE_INFORMATION2 := p_funding_ckl_id;
5509
5510 --
5511 -- Credit header checklist template
5512 --
5513
5514 x_return_status := validate_chklst_tpl(lp_rulv_rec, 'C');
5515 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5516 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5517 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5518 raise OKC_API.G_EXCEPTION_ERROR;
5519 End If;
5520
5521 create_credit_chklst_tpl(
5522 p_api_version => p_api_version,
5523 p_init_msg_list => p_init_msg_list,
5524 x_return_status => x_return_status,
5525 x_msg_count => x_msg_count,
5526 x_msg_data => x_msg_data,
5527 p_rgpv_rec => lp_rgpv_rec,
5528 p_rulv_rec => lp_rulv_rec,
5529 x_rgpv_rec => lx_rgpv_rec,
5530 x_rulv_rec => lx_rulv_rec);
5531
5532 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
5533 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5534 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
5535 raise OKC_API.G_EXCEPTION_ERROR;
5536 End If;
5537
5538 -- funding checklist enhancement for 11.5.9
5539
5540 /*
5541 -- vthiruva, 08/31/2004
5542 -- START, Code change to enable Business Event
5543 */
5544 --raise business event for new credit line record
5545 --if sts_code is NEW
5546 IF(lp_chrv_rec.sts_code = 'NEW')THEN
5547 raise_business_event(p_api_version => p_api_version,
5548 p_init_msg_list => p_init_msg_list,
5549 x_return_status => x_return_status,
5550 x_msg_count => x_msg_count,
5551 x_msg_data => x_msg_data,
5552 p_id => lx_chrv_rec.id,
5553 p_event_name => G_WF_EVT_CR_LN_CREATED);
5554
5555 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5556 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5557 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5558 RAISE OKL_API.G_EXCEPTION_ERROR;
5559 END IF;
5560 END IF;
5561
5562 /*
5563 -- vthiruva, 08/31/2004
5564 -- END, Code change to enable Business Event
5565 */
5566
5567 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
5568 x_msg_data => x_msg_data);
5569 EXCEPTION
5570 when OKC_API.G_EXCEPTION_ERROR then
5571 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
5572 p_api_name => l_api_name,
5573 p_pkg_name => g_pkg_name,
5574 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
5575 x_msg_count => x_msg_count,
5576 x_msg_data => x_msg_data,
5577 p_api_type => g_api_type);
5578
5579 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
5580 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
5581 p_api_name => l_api_name,
5582 p_pkg_name => g_pkg_name,
5583 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
5584 x_msg_count => x_msg_count,
5585 x_msg_data => x_msg_data,
5586 p_api_type => g_api_type);
5587
5588 when OTHERS then
5589 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
5590 p_api_name => l_api_name,
5591 p_pkg_name => g_pkg_name,
5592 p_exc_name => 'OTHERS',
5593 x_msg_count => x_msg_count,
5594 x_msg_data => x_msg_data,
5595 p_api_type => g_api_type);
5596 END;
5597 /*---------------------------------------------------------------------------+
5598 | |
5599 | FUNCTION: get_total_credit_limit |
5600
5601 | DESC : Sum of all credit limit (contract line) for specfiic contract |
5602 | scs_code = 'CREDITLINE_CONTRACT' |
5603 | IN : p_contract_id |
5604 | OUT : amount |
5605 | HISTORY: 26-JAN-02 [email protected] -- Created |
5606 | |
5607
5608 *-------------------------------------------------------------------------- */
5609 FUNCTION get_total_credit_limit(
5610 p_contract_id IN NUMBER
5611 ,p_contract_line_id IN NUMBER
5612 ) RETURN NUMBER
5613 IS
5614 l_amount NUMBER := 0;
5615 l_amount_add NUMBER := 0;
5616 l_amount_new NUMBER := 0;
5617 l_amount_reduce NUMBER := 0;
5618
5619 BEGIN
5620
5621 l_amount_add := nvl(OKL_CREDIT_PUB.get_total_credit_addition(p_contract_id),0);
5622 l_amount_new := nvl(OKL_CREDIT_PUB.get_total_credit_new_limit(p_contract_id),0);
5623 l_amount_reduce := nvl(OKL_CREDIT_PUB.get_total_credit_reduction(p_contract_id),0);
5624
5625 l_amount := l_amount_new + l_amount_add - l_amount_reduce;
5626
5627 RETURN l_amount;
5628 EXCEPTION
5629 WHEN OTHERS THEN
5630 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
5631 OKL_API.Set_Message(p_app_name => OKL_API.G_APP_NAME,
5632 p_msg_name => 'OKL_UNEXPECTED_ERROR',
5633 p_token1 => 'OKL_SQLCODE',
5634 p_token1_value => SQLCODE,
5635 p_token2 => 'OKL_SQLERRM',
5636 p_token2_value => SQLERRM);
5637 RETURN null;
5638
5639 END;
5640
5641 /*---------------------------------------------------------------------------+
5642 | |
5643 | FUNCTION: get_credit_remaining |
5644 | DESC : Sum of all credit limit (contract line) for specfiic contract |
5645 | scs_code = 'CREDITLINE_CONTRACT' and substract from Funding total|
5646 | IN : p_contract_id |
5647 | OUT : amount |
5648 | HISTORY: 26-JAN-02 [email protected] -- Created |
5649 | |
5650 *-------------------------------------------------------------------------- */
5651 FUNCTION get_credit_remaining(
5652 p_contract_id IN NUMBER
5653 ,p_contract_line_id IN NUMBER
5654 ) RETURN NUMBER
5655 IS
5656 l_amount NUMBER := 0;
5657 l_amount_funded NUMBER := 0;
5658 l_amount_credit_limit NUMBER := 0;
5659
5660 BEGIN
5661
5662 --l_amount_funded := nvl(OKL_FUNDING_PVT.get_total_funded(p_contract_id),0);
5663 l_amount_credit_limit := nvl(OKL_CREDIT_PUB.get_total_credit_limit(p_contract_id),0);
5664
5665 l_amount := l_amount_credit_limit - l_amount_funded;
5666
5667 RETURN l_amount;
5668 EXCEPTION
5669 WHEN OTHERS THEN
5670 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
5671 OKL_API.Set_Message(p_app_name => OKL_API.G_APP_NAME,
5672 p_msg_name => 'OKL_UNEXPECTED_ERROR',
5673 p_token1 => 'OKL_SQLCODE',
5674 p_token1_value => SQLCODE,
5675 p_token2 => 'OKL_SQLERRM',
5676 p_token2_value => SQLERRM);
5677 RETURN null;
5678
5679 END;
5680 /*---------------------------------------------------------------------------+
5681 | |
5682 | FUNCTION: get_total_credit_new_limit |
5683 | DESC : Sum of all credit new limit (contract line) for specfiic contract|
5684 | scs_code = 'CREDITLINE_CONTRACT' |
5685 | IN : p_contract_id |
5686 | OUT : amount |
5687 | HISTORY: 26-JAN-02 [email protected] -- Created |
5688
5689 | |
5690 *-------------------------------------------------------------------------- */
5691 FUNCTION get_total_credit_new_limit(
5692 p_contract_id IN NUMBER
5693 ,p_contract_line_id IN NUMBER
5694 ) RETURN NUMBER
5695 IS
5696 l_amount NUMBER := 0;
5697
5698 CURSOR c (p_contract_id NUMBER)
5699 IS
5700 select nvl(sum(a.amount),0)
5701 from OKL_K_LINES_FULL_V a
5702 where a.dnz_chr_id = p_contract_id
5703 and a.CREDIT_NATURE = 'NEW'
5704 and nvl(a.start_date,sysdate) <= sysdate
5705 ;
5706
5707 BEGIN
5708
5709 OPEN c (p_contract_id);
5710 FETCH c INTO l_amount;
5711 CLOSE c;
5712
5713 RETURN l_amount;
5714 EXCEPTION
5715 WHEN OTHERS THEN
5716 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
5717 OKL_API.Set_Message(p_app_name => OKL_API.G_APP_NAME,
5718 p_msg_name => 'OKL_UNEXPECTED_ERROR',
5719 p_token1 => 'OKL_SQLCODE',
5720 p_token1_value => SQLCODE,
5721 p_token2 => 'OKL_SQLERRM',
5722 p_token2_value => SQLERRM);
5723 RETURN null;
5724
5725 END;
5726 /*---------------------------------------------------------------------------+
5727 | |
5728 | FUNCTION: get_total_credit_addition |
5729 | DESC : Sum of all credit addition (contract line) for specfiic contract |
5730 | scs_code = 'CREDITLINE_CONTRACT' |
5731 | IN : p_contract_id |
5732 | OUT : amount |
5733 | HISTORY: 26-JAN-02 [email protected] -- Created |
5734 | |
5735 *-------------------------------------------------------------------------- */
5736 FUNCTION get_total_credit_addition(
5737 p_contract_id IN NUMBER
5738 ,p_contract_line_id IN NUMBER
5739 ) RETURN NUMBER
5740 IS
5741 l_amount NUMBER := 0;
5742
5743 CURSOR c (p_contract_id NUMBER)
5744 IS
5745 select nvl(sum(a.amount),0)
5746 from OKL_K_LINES_FULL_V a
5747 where a.dnz_chr_id = p_contract_id
5748 and a.CREDIT_NATURE = 'ADD'
5749 and nvl(a.start_date,sysdate) <= sysdate
5750 ;
5751
5752 BEGIN
5753
5754 OPEN c (p_contract_id);
5755 FETCH c INTO l_amount;
5756 CLOSE c;
5757
5758 RETURN l_amount;
5759 EXCEPTION
5760 WHEN OTHERS THEN
5761 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
5762 OKL_API.Set_Message(p_app_name => OKL_API.G_APP_NAME,
5763 p_msg_name => 'OKL_UNEXPECTED_ERROR',
5764 p_token1 => 'OKL_SQLCODE',
5765 p_token1_value => SQLCODE,
5766 p_token2 => 'OKL_SQLERRM',
5767 p_token2_value => SQLERRM);
5768 RETURN null;
5769
5770 END;
5771
5772 /*---------------------------------------------------------------------------+
5773 | |
5774 | FUNCTION: get_total_credit_reduction |
5775 | DESC : Sum of all credit reduction (contract line) for specfiic contract|
5776 | scs_code = 'CREDITLINE_CONTRACT' |
5777 | IN : p_contract_id |
5778 | OUT : amount |
5779 | HISTORY: 26-JAN-02 [email protected] -- Created |
5780 | |
5781 *-------------------------------------------------------------------------- */
5782 FUNCTION get_total_credit_reduction(
5783 p_contract_id IN NUMBER
5784 ,p_contract_line_id IN NUMBER
5785 ) RETURN NUMBER
5786 IS
5787 l_amount NUMBER := 0;
5788
5789 CURSOR c (p_contract_id NUMBER)
5790 IS
5791 select nvl(sum(a.amount),0)
5792 from OKL_K_LINES_FULL_V a
5793 where a.dnz_chr_id = p_contract_id
5794 and a.CREDIT_NATURE = 'REDUCE'
5795 and nvl(a.start_date,sysdate) <= sysdate
5796 ;
5797
5798 BEGIN
5799
5800 OPEN c (p_contract_id);
5801 FETCH c INTO l_amount;
5802 CLOSE c;
5803
5804 RETURN l_amount;
5805 EXCEPTION
5806 WHEN OTHERS THEN
5807 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
5808 OKL_API.Set_Message(p_app_name => OKL_API.G_APP_NAME,
5809 p_msg_name => 'OKL_UNEXPECTED_ERROR',
5810 p_token1 => 'OKL_SQLCODE',
5811 p_token1_value => SQLCODE,
5812 p_token2 => 'OKL_SQLERRM',
5813 p_token2_value => SQLERRM);
5814 RETURN null;
5815
5816 END;
5817 /*---------------------------------------------------------------------------+
5818 | |
5819 | FUNCTION: get_checklist_number |
5820 | IN : p_ckl_id |
5821 | OUT : checkist_number |
5822 | HISTORY: 26-JAN-02 [email protected] -- Created |
5823 | |
5824 *-------------------------------------------------------------------------- */
5825 FUNCTION get_checklist_number(
5826 p_chr_id IN NUMBER
5827 ,p_attr IN VARCHAR2
5828 ) RETURN VARCHAR2
5829 IS
5830 l_number okl_checklists.CHECKLIST_NUMBER%type;
5831 l_ckl_id number;
5832
5833 CURSOR c (p_ckl_id NUMBER)
5834 IS
5835 select CHECKLIST_NUMBER
5836 from okl_checklists ckl
5837 where ckl.id = to_number(p_ckl_id)
5838 ;
5839
5840 CURSOR c_fk (p_chr_id NUMBER)
5841 IS
5842 select DECODE(p_attr, 'RULE_INFORMATION1', to_number(rule.rule_information1),
5843 'RULE_INFORMATION2', to_number(rule.rule_information2))
5844 from okc_rules_b rule
5845 where rule.dnz_chr_id = p_chr_id
5846 and rule.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1
5847 ;
5848
5849 BEGIN
5850
5851 OPEN c_fk (p_chr_id);
5852 FETCH c_fk INTO l_ckl_id;
5853 CLOSE c_fk;
5854
5855 OPEN c (l_ckl_id);
5856 FETCH c INTO l_number;
5857 CLOSE c;
5858
5859 RETURN l_number;
5860 EXCEPTION
5861 WHEN OTHERS THEN
5862 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
5863 OKL_API.Set_Message(p_app_name => OKL_API.G_APP_NAME,
5864 p_msg_name => 'OKL_UNEXPECTED_ERROR',
5865 p_token1 => 'OKL_SQLCODE',
5866 p_token1_value => SQLCODE,
5867 p_token2 => 'OKL_SQLERRM',
5868 p_token2_value => SQLERRM);
5869 RETURN null;
5870
5871 END;
5872
5873 /*---------------------------------------------------------------------------+
5874 | |
5875 | FUNCTION: fnd_profile_value |
5876 | IN : p_opt_name |
5877 | OUT : profile option value |
5878 | HISTORY: 26-JAN-02 [email protected] -- Created |
5879 | |
5880 *-------------------------------------------------------------------------- */
5881 FUNCTION fnd_profile_value(
5882 p_opt_name IN VARCHAR2
5883 ) RETURN VARCHAR2
5884 is
5885 begin
5886 return fnd_profile.value(p_opt_name);
5887 end;
5888
5889 /*---------------------------------------------------------------------------+
5890 | |
5891 | FUNCTION: get_func_curr_code |
5892 | IN : |
5893 | OUT : currency code |
5894 | HISTORY: 26-JAN-02 [email protected] -- Created |
5895 | |
5896 *-------------------------------------------------------------------------- */
5897 FUNCTION get_func_curr_code
5898 RETURN VARCHAR2
5899 is
5900 begin
5901 return okl_accounting_util.get_func_curr_code;
5902 end;
5903
5904 /*---------------------------------------------------------------------------+
5905 | |
5906 | FUNCTION: get_checklist_attr |
5907 | IN : p_ckl_id |
5908 | OUT : checkist_number |
5909 | HISTORY: 26-JAN-02 [email protected] -- Created |
5910 | |
5911 *-------------------------------------------------------------------------- */
5912 FUNCTION get_checklist_attr(
5913 p_chr_id IN NUMBER
5914 ,p_attr IN VARCHAR2
5915 ) RETURN VARCHAR2
5916 is
5917 l_rgp_id okc_rules_b.rgp_id%type;
5918 l_id okc_rules_b.id%type;
5919 l_rule_information1 okc_rules_b.rule_information1%type;
5920 l_rule_information2 okc_rules_b.rule_information2%type;
5921
5922
5923 CURSOR c (p_chr_id NUMBER)
5924 IS
5925 select rule.id,
5926 rule.rgp_id,
5927 rule.rule_information1,
5928 rule.rule_information2
5929 from okc_rules_b rule
5930 where rule.dnz_chr_id = p_chr_id
5931 and rule.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1
5932 ;
5933
5934 BEGIN
5935
5936 OPEN c (p_chr_id);
5937 FETCH c INTO l_id,
5938 l_rgp_id,
5939 l_rule_information1,
5940 l_rule_information2;
5941 CLOSE c;
5942
5943 IF p_attr = 'ID' THEN
5944 RETURN to_char(l_id);
5945 ELSIF p_attr = 'RGP_ID' THEN
5946 RETURN to_char(l_rgp_id);
5947 ELSIF p_attr = 'RULE_INFORMATION1' THEN
5948 RETURN l_rule_information1;
5949 ELSIF p_attr = 'RULE_INFORMATION2' THEN
5950 RETURN l_rule_information2;
5951 END IF;
5952
5953 EXCEPTION
5954 WHEN OTHERS THEN
5955 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
5956 OKL_API.Set_Message(p_app_name => OKL_API.G_APP_NAME,
5957 p_msg_name => 'OKL_UNEXPECTED_ERROR',
5958 p_token1 => 'OKL_SQLCODE',
5959 p_token1_value => SQLCODE,
5960 p_token2 => 'OKL_SQLERRM',
5961 p_token2_value => SQLERRM);
5962 RETURN null;
5963 end;
5964
5965 -- start cklee bug# 2901495
5966 ----------------------------------------------------------------------------------
5967 -- Start of comments
5968 --
5969 -- Procedure Name : get_creditline_by_chrid
5970 -- Description : search associated credit line by contract id
5971 -- Business Rules :
5972
5973 -- Parameters :
5974 -- Version : 1.0
5975 -- End of comments
5976 ----------------------------------------------------------------------------------
5977 FUNCTION get_creditline_by_chrid(
5978 p_contract_id IN NUMBER -- contract hdr
5979 ) RETURN NUMBER
5980 IS
5981 l_credit_id okc_k_headers_b.id%TYPE := NULL;
5982 l_row_not_found boolean := false;
5983
5984 CURSOR c_credit (p_contract_id NUMBER)
5985 IS
5986 select a.ID
5987 from OKC_K_HEADERS_B a,
5988 okc_Governances_v g
5989 where a.id = g.chr_id_referred
5990 and a.sts_code = 'ACTIVE'
5991 and g.dnz_chr_id = p_contract_id
5992 and a.scs_code = 'CREDITLINE_CONTRACT'
5993 ;
5994 --cklee, fixed bug# 3149922
5995 CURSOR c_MLA_credit (p_contract_id NUMBER)
5996 IS
5997 select a.ID
5998 from OKC_K_HEADERS_B a,
5999 okc_Governances_v g
6000 where a.id = g.chr_id_referred
6001 and a.sts_code = 'ACTIVE'
6002 and a.scs_code = 'CREDITLINE_CONTRACT'
6003 and g.dnz_chr_id = (select a1.ID -- MLA chrid
6004 from OKC_K_HEADERS_B a1,
6005 okc_Governances_v g1
6006 where a1.id = g1.chr_id_referred
6007 and g1.dnz_chr_id = p_contract_id
6008 and a1.scs_code = 'MASTER_LEASE')
6009 ;
6010
6011
6012 BEGIN
6013
6014 -- 1) get credit_id by associated contract
6015 OPEN c_credit(p_contract_id);
6016 FETCH c_credit INTO l_credit_id;
6017 l_row_not_found := c_credit%NOTFOUND;
6018 CLOSE c_credit;
6019
6020 IF (l_row_not_found) THEN
6021
6022 -- 2) get credit_id by associated MLA contract
6023 OPEN c_MLA_credit(p_contract_id);
6024 FETCH c_MLA_credit INTO l_credit_id;
6025 CLOSE c_MLA_credit;
6026
6027 END IF;
6028
6029 RETURN l_credit_id;
6030
6031 EXCEPTION
6032 WHEN OTHERS THEN
6033 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
6034 OKL_API.Set_Message(p_app_name => OKL_API.G_APP_NAME,
6035 p_msg_name => 'OKL_UNEXPECTED_ERROR',
6036 p_token1 => 'OKL_SQLCODE',
6037 p_token1_value => SQLCODE,
6038 p_token2 => 'OKL_SQLERRM',
6039 p_token2_value => SQLERRM);
6040 RETURN NULL;
6041
6042 END get_creditline_by_chrid;
6043 -- end cklee bug# 2901495
6044
6045 -- start: 06-May-2005 cklee okl.h Lease App IA Authoring
6046 PROCEDURE update_credit_line_status(
6047 p_api_version IN NUMBER,
6048 p_init_msg_list IN VARCHAR2,
6049 x_return_status OUT NOCOPY VARCHAR2,
6050 x_msg_count OUT NOCOPY NUMBER,
6051 x_msg_data OUT NOCOPY VARCHAR2,
6052 x_status_code OUT NOCOPY VARCHAR2,
6053 p_status_code IN VARCHAR2,
6054 p_credit_line_id IN NUMBER)
6055 is
6056 l_api_name CONSTANT VARCHAR2(30) := 'update_credit_line_status';
6057 l_api_version CONSTANT NUMBER := 1.0;
6058 i NUMBER;
6059 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
6060
6061 lp_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
6062 lp_khrv_rec khrv_rec_type;
6063 lx_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
6064 lx_khrv_rec khrv_rec_type;
6065
6066 begin
6067 -- Set API savepoint
6068 SAVEPOINT update_credit_line_status;
6069
6070 -- Check for call compatibility
6071 IF (NOT FND_API.Compatible_API_Call (l_api_version,
6072 p_api_version,
6073 l_api_name,
6074 G_PKG_NAME ))
6075 THEN
6076 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
6077 END IF;
6078
6079 -- Initialize message list if requested
6080 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
6081 FND_MSG_PUB.initialize;
6082 END IF;
6083
6084 -- Initialize API status to success
6085 x_return_status := OKL_API.G_RET_STS_SUCCESS;
6086
6087
6088 /*** Begin API body ****************************************************/
6089 -- set values
6090 lp_chrv_rec.ID := p_credit_line_id;
6091 lp_khrv_rec.ID := p_credit_line_id;
6092 lp_chrv_rec.sts_code := p_status_code;
6093
6094 IF p_status_code = 'APPROVED' THEN
6095
6096 -- trun to Active directly if Credit Line got approved
6097 lp_chrv_rec.sts_code := 'ACTIVE';
6098
6099 ELSIF (p_status_code NOT IN ('SUBMITTED', 'APPROVED','PENDING_APPROVAL','DECLINED')) THEN
6100
6101 OKL_API.Set_Message(p_app_name => G_APP_NAME,
6102 p_msg_name => G_INVALID_VALUE,
6103 p_token1 => G_COL_NAME_TOKEN,
6104 p_token1_value => p_status_code);
6105
6106 RAISE G_EXCEPTION_HALT_VALIDATION;
6107
6108 END IF;
6109
6110 update_credit_header(
6111 p_api_version => p_api_version,
6112 p_init_msg_list => p_init_msg_list,
6113 x_return_status => x_return_status,
6114 x_msg_count => x_msg_count,
6115 x_msg_data => x_msg_data,
6116 p_chrv_rec => lp_chrv_rec,
6117 p_khrv_rec => lp_khrv_rec,
6118 x_chrv_rec => lx_chrv_rec,
6119 x_khrv_rec => lx_khrv_rec
6120 );
6121
6122 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
6123 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
6124 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
6125 raise OKC_API.G_EXCEPTION_ERROR;
6126 End If;
6127
6128 x_status_code := lx_chrv_rec.sts_code;
6129
6130 /*** End API body ******************************************************/
6131
6132 -- Get message count and if count is 1, get message info
6133 FND_MSG_PUB.Count_And_Get
6134 (p_count => x_msg_count,
6135 p_data => x_msg_data);
6136
6137 EXCEPTION
6138 WHEN OKL_API.G_EXCEPTION_ERROR THEN
6139 ROLLBACK TO update_credit_line_status;
6140 x_return_status := OKL_API.G_RET_STS_ERROR;
6141 x_status_code := lx_chrv_rec.sts_code;
6142
6143 FND_MSG_PUB.Count_And_Get
6144 (p_count => x_msg_count,
6145 p_data => x_msg_data);
6146
6147 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
6148 ROLLBACK TO update_credit_line_status;
6149 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
6150 x_status_code := lx_chrv_rec.sts_code;
6151
6152 FND_MSG_PUB.Count_And_Get
6153 (p_count => x_msg_count,
6154 p_data => x_msg_data);
6155
6156 WHEN OTHERS THEN
6157 ROLLBACK TO update_credit_line_status;
6158 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
6159 x_status_code := lx_chrv_rec.sts_code;
6160
6161 OKL_API.Set_Message(p_app_name => G_APP_NAME,
6162 p_msg_name => G_UNEXPECTED_ERROR,
6163 p_token1 => G_SQLCODE_TOKEN,
6164 p_token1_value => SQLCODE,
6165 p_token2 => G_SQLERRM_TOKEN,
6166 p_token2_value => SQLERRM);
6167 FND_MSG_PUB.Count_And_Get
6168 (p_count => x_msg_count,
6169 p_data => x_msg_data);
6170 end update_credit_line_status;
6171
6172 ----------------------------------------------------------------------------------
6173 -- Start of comments
6174 --
6175 -- Procedure Name : update_checklist_function
6176 -- Description : This API will execute function for each item and
6177 -- update the execution results for the function.
6178 -- Business Rules :
6179 -- Parameters :
6180 -- Version : 1.0
6181 -- End of comments
6182 ----------------------------------------------------------------------------------
6183 PROCEDURE update_checklist_function(
6184 p_api_version IN NUMBER
6185 ,p_init_msg_list IN VARCHAR2
6186 ,x_return_status OUT NOCOPY VARCHAR2
6187 ,x_msg_count OUT NOCOPY NUMBER
6188 ,x_msg_data OUT NOCOPY VARCHAR2
6189 ,p_contract_id IN NUMBER
6190 ) is
6191 l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_function';
6192 l_api_version CONSTANT NUMBER := 1.0;
6193 i NUMBER;
6194 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
6195 l_dummy number;
6196
6197 l_row_not_found boolean := false;
6198
6199 lp_rulv_tbl okl_credit_checklist_pvt.rulv_tbl_type;
6200 lx_rulv_tbl okl_credit_checklist_pvt.rulv_tbl_type;
6201 plsql_block VARCHAR2(500);
6202
6203 lp_return_status okl_credit_checklists_uv.FUNCTION_VALIDATE_RSTS%type;
6204 lp_fund_rst okl_credit_checklists_uv.FUNCTION_VALIDATE_RSTS%type;
6205 lp_msg_data okl_credit_checklists_uv.FUNCTION_VALIDATE_MSG%type;
6206
6207 -- get checklist template attributes
6208 cursor c_clist_funs (p_contract_id number) is
6209 --start modified abhsaxen for performance SQLID 20562912
6210 SELECT
6211 rult.ID,
6212 fun.source function_source
6213 FROM OKC_RULES_B RULT,
6214 OKL_DATA_SRC_FNCTNS_b FUN
6215 WHERE rult.rule_information_category = 'LACCLD' and
6216 rult.RULE_INFORMATION9 = fun.ID and
6217 rult.DNZ_CHR_ID = p_contract_id
6218 --end modified abhsaxen for performance SQLID 20562912
6219 ;
6220
6221 begin
6222 -- Set API savepoint
6223 SAVEPOINT update_checklist_function;
6224
6225 -- Check for call compatibility
6226 IF (NOT FND_API.Compatible_API_Call (l_api_version,
6227 p_api_version,
6228 l_api_name,
6229 G_PKG_NAME ))
6230 THEN
6231 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
6232 END IF;
6233
6234 -- Initialize message list if requested
6235 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
6236 FND_MSG_PUB.initialize;
6237 END IF;
6238
6239 -- Initialize API status to success
6240 x_return_status := OKL_API.G_RET_STS_SUCCESS;
6241
6242
6243 /*** Begin API body ****************************************************/
6244
6245 ------------------------------------------------------------------------
6246 -- execute function for each to do item and save the return to each row
6247 ------------------------------------------------------------------------
6248 i := 0;
6249 FOR r_this_row IN c_clist_funs (p_contract_id) LOOP
6250
6251 BEGIN
6252
6253 plsql_block := 'BEGIN :l_rtn := '|| r_this_row.FUNCTION_SOURCE ||'(:p_contract_id); END;';
6254 EXECUTE IMMEDIATE plsql_block USING OUT lp_return_status, p_contract_id;
6255
6256 IF lp_return_status = 'P' THEN
6257 lp_fund_rst := 'PASSED';
6258 lp_msg_data := 'Passed';
6259 ELSIF lp_return_status = 'F' THEN
6260 lp_fund_rst := 'FAILED';
6261 lp_msg_data := 'Failed';
6262 ELSE
6263 lp_fund_rst := 'ERROR';
6264 lp_msg_data := r_this_row.FUNCTION_SOURCE || ' returns: ' || lp_return_status;
6265 END IF;
6266
6267 EXCEPTION
6268 WHEN OKL_API.G_EXCEPTION_ERROR THEN
6269 lp_fund_rst := 'ERROR';
6270 FND_MSG_PUB.Count_And_Get
6271 (p_count => x_msg_count,
6272 p_data => x_msg_data);
6273 lp_msg_data := substr('Application error: ' || x_msg_data, 240);
6274
6275 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
6276 lp_fund_rst := 'ERROR';
6277 FND_MSG_PUB.Count_And_Get
6278 (p_count => x_msg_count,
6279 p_data => x_msg_data);
6280 lp_msg_data := substr('Unexpected application error: ' || x_msg_data, 240);
6281
6282 WHEN OTHERS THEN
6283 lp_fund_rst := 'ERROR';
6284 lp_msg_data := substr('Unexpected system error: ' || SQLERRM, 240);
6285
6286 END;
6287
6288 lp_rulv_tbl(i).ID := r_this_row.ID;
6289 lp_rulv_tbl(i).RULE_INFORMATION7 := lp_fund_rst;
6290 lp_rulv_tbl(i).RULE_INFORMATION8 := lp_msg_data;
6291 i := i + 1;
6292
6293 END LOOP;
6294
6295 IF lp_rulv_tbl.count > 0 THEN
6296
6297 okl_credit_checklist_pvt.update_credit_chklst(
6298 p_api_version => p_api_version,
6299 p_init_msg_list => p_init_msg_list,
6300 x_return_status => x_return_status,
6301 x_msg_count => x_msg_count,
6302 x_msg_data => x_msg_data,
6303 p_rulv_tbl => lp_rulv_tbl,
6304 x_rulv_tbl => lx_rulv_tbl);
6305
6306 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
6307 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
6308 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
6309 raise OKC_API.G_EXCEPTION_ERROR;
6310 End If;
6311
6312 END IF;
6313
6314 /*** End API body ******************************************************/
6315
6316 -- Get message count and if count is 1, get message info
6317 FND_MSG_PUB.Count_And_Get
6318 (p_count => x_msg_count,
6319 p_data => x_msg_data);
6320
6321 EXCEPTION
6322 WHEN OKL_API.G_EXCEPTION_ERROR THEN
6323 ROLLBACK TO update_checklist_function;
6324 x_return_status := OKL_API.G_RET_STS_ERROR;
6325 FND_MSG_PUB.Count_And_Get
6326 (p_count => x_msg_count,
6327 p_data => x_msg_data);
6328
6329 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
6330 ROLLBACK TO update_checklist_function;
6331 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
6332 FND_MSG_PUB.Count_And_Get
6333 (p_count => x_msg_count,
6334 p_data => x_msg_data);
6335
6336 WHEN OTHERS THEN
6337
6338 ROLLBACK TO update_checklist_function;
6339 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
6340 OKL_API.Set_Message(p_app_name => G_APP_NAME,
6341 p_msg_name => G_UNEXPECTED_ERROR,
6342 p_token1 => G_SQLCODE_TOKEN,
6343 p_token1_value => SQLCODE,
6344 p_token2 => G_SQLERRM_TOKEN,
6345 p_token2_value => SQLERRM);
6346 FND_MSG_PUB.Count_And_Get
6347 (p_count => x_msg_count,
6348 p_data => x_msg_data);
6349
6350 end update_checklist_function;
6351 -- end: 06-May-2005 cklee okl.h Lease App IA Authoring
6352
6353 -- start: cklee 07/12/2005
6354 ----------------------------------------------------------------------------------
6355 -- Start of comments
6356 --
6357 -- Procedure Name : activate_credit
6358 -- Description : activates a credit line
6359 --
6360 -- Business Rules : This procedure will validate credit line and then activate
6361 -- the credit line.
6362 -- It will return to the caller without raise error if credit
6363 -- has been activated already.
6364 --
6365 -- Parameters : p_chr_id : Credit Line PK
6366 -- x_sts_code : Credit Line status code
6367 --
6368 -- Version : 1.0
6369 -- End of comments
6370 ----------------------------------------------------------------------------------
6371 PROCEDURE activate_credit(
6372 p_api_version IN NUMBER,
6373 p_init_msg_list IN VARCHAR2,
6374 x_return_status OUT NOCOPY VARCHAR2,
6375 x_msg_count OUT NOCOPY NUMBER,
6376 x_msg_data OUT NOCOPY VARCHAR2,
6377 p_chr_id IN NUMBER,
6378 x_sts_code OUT NOCOPY VARCHAR2)
6379 is
6380
6381 l_api_name CONSTANT VARCHAR2(30) := 'activate_credit_pvt';
6382 l_api_version CONSTANT NUMBER := 1.0;
6383 i NUMBER;
6384 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
6385
6386 cursor c_credit (p_chr_id number) is
6387 --start modified abhsaxen for performance SQLID 20562919
6388 SELECT
6389 chrb.contract_number contract_number,
6390 chrb.start_date start_date,
6391 chrb.end_date end_date,
6392 chrb.currency_code currency_code,
6393 chrb.sts_code sts_code,
6394 chrb.cust_acct_id cust_acct_id,
6395 chrt.description description,
6396 khr.currency_conversion_type currency_conversion_type,
6397 khr.currency_conversion_rate currency_conversion_rate,
6398 khr.currency_conversion_date currency_conversion_date,
6399 khr.revolving_credit_yn revolving_credit_yn,
6400 cpl.id party_roles_id,
6401 cpl.object1_id1 customer_id1,
6402 cpl.object1_id2 customer_id2,
6403 cpl.jtot_object1_code customer_jtot_object_code,
6404 party.name customer_name,
6405 rul.rule_information1 creditline_ckl_id,
6406 rul.rule_information2 funding_ckl_id,
6407 rul.rgp_id chklst_tpl_rgp_id,
6408 rul.id chklst_tpl_rule_id,
6409 CA.ACCOUNT_NUMBER cust_acct_number
6410 FROM
6411 OKC_K_HEADERS_B CHRB,
6412 OKC_K_HEADERS_TL CHRT,
6413 OKL_K_HEADERS KHR,
6414 HZ_CUST_ACCOUNTS CA,
6415 OKC_RULES_B RUL,
6416 OKX_PARTIES_V PARTY,
6417 OKC_K_PARTY_ROLES_B CPL
6418 WHERE chrb.id = chrt.id
6419 AND chrt.language = USERENV('LANG')
6420 AND chrb.id = khr.id
6421 AND chrb.scs_code = 'CREDITLINE_CONTRACT'
6422 AND CA.CUST_ACCOUNT_ID(+) = chrb.cust_acct_id
6423 AND rul.rule_information_category(+) = 'LACCLT'
6424 AND rul.dnz_chr_id(+) = chrb.id
6425 AND party.id1 = cpl.object1_id1
6426 AND party.id2 = cpl.object1_id2
6427 AND cpl.rle_code = 'LESSEE'
6428 AND cpl.chr_id = chrb.id
6429 AND cpl.DNZ_CHR_ID = cpl.chr_id
6430 AND CHRB.ID = p_chr_id;
6431 --end modified abhsaxen for performance SQLID 20562919
6432
6433
6434 l_credit_rec c_credit%ROWTYPE;
6435
6436 lp_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
6437 lp_khrv_rec khrv_rec_type;
6438 lx_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
6439 lx_khrv_rec khrv_rec_type;
6440
6441
6442 BEGIN
6443 -- Set API savepoint
6444 SAVEPOINT activate_credit_pvt;
6445
6446 -- Check for call compatibility
6447 IF (NOT FND_API.Compatible_API_Call (l_api_version,
6448 p_api_version,
6449 l_api_name,
6450 G_PKG_NAME ))
6451 THEN
6452 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
6453 END IF;
6454
6455 -- Initialize message list if requested
6456 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
6457 FND_MSG_PUB.initialize;
6458 END IF;
6459
6460 -- Initialize API status to success
6461 x_return_status := OKL_API.G_RET_STS_SUCCESS;
6462
6463
6464 /*** Begin API body ****************************************************/
6465
6466 OPEN c_credit(p_chr_id => p_chr_id);
6467 FETCH c_credit INTO l_credit_rec;
6468 CLOSE c_credit;
6469
6470 validate_credit(
6471 p_api_version => p_api_version,
6472 p_init_msg_list => p_init_msg_list,
6473 x_return_status => x_return_status,
6474 x_msg_count => x_msg_count,
6475 x_msg_data => x_msg_data,
6476 p_chr_id => p_chr_id,
6477 p_contract_number => l_credit_rec.CONTRACT_NUMBER,
6478 p_description => l_credit_rec.DESCRIPTION,
6479 p_customer_id1 => l_credit_rec.CUSTOMER_ID1,
6480 p_customer_id2 => l_credit_rec.CUSTOMER_ID2,
6481 p_customer_code => l_credit_rec.CUSTOMER_JTOT_OBJECT_CODE,
6482 p_customer_name => l_credit_rec.CUSTOMER_NAME,
6483 p_effective_from => l_credit_rec.START_DATE,
6484 p_effective_to => l_credit_rec.END_DATE,
6485 p_currency_code => l_credit_rec.CURRENCY_CODE,
6486 -- multi-currency support
6487 p_currency_conv_type => l_credit_rec.CURRENCY_CONVERSION_TYPE,
6488 p_currency_conv_rate => l_credit_rec.CURRENCY_CONVERSION_RATE,
6489 p_currency_conv_date => l_credit_rec.CURRENCY_CONVERSION_DATE,
6490 -- multi-currency support
6491 -- funding checklist enhancement
6492 p_credit_ckl_id => l_credit_rec.CREDITLINE_CKL_ID,
6493 p_funding_ckl_id => l_credit_rec.FUNDING_CKL_ID,
6494 -- funding checklist enhancement
6495 p_cust_acct_id => l_credit_rec.CUST_ACCT_ID, -- 11.5.10 rule migration project
6496 p_cust_acct_number => l_credit_rec.CUST_ACCT_NUMBER, -- 11.5.10 rule migration project
6497 p_sts_code => l_credit_rec.STS_CODE
6498 );
6499
6500 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
6501 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
6502 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
6503 RAISE OKL_API.G_EXCEPTION_ERROR;
6504 END IF;
6505
6506
6507 lp_chrv_rec.id := p_chr_id;
6508 lp_chrv_rec.contract_number := l_credit_rec.CONTRACT_NUMBER;
6509 lp_chrv_rec.description := l_credit_rec.DESCRIPTION;
6510 lp_chrv_rec.short_description := l_credit_rec.DESCRIPTION;
6511 lp_chrv_rec.currency_code := l_credit_rec.CURRENCY_CODE;
6512
6513 lp_khrv_rec.currency_conversion_type := l_credit_rec.CURRENCY_CONVERSION_TYPE;
6514 lp_khrv_rec.currency_conversion_rate := l_credit_rec.CURRENCY_CONVERSION_RATE;
6515 lp_khrv_rec.currency_conversion_date := l_credit_rec.CURRENCY_CONVERSION_DATE;
6516
6517 lp_chrv_rec.sts_code := 'SUBMITTED';--'ACTIVE';
6518 lp_chrv_rec.start_date := l_credit_rec.START_DATE;
6519 lp_chrv_rec.end_date := l_credit_rec.END_DATE;
6520 lp_chrv_rec.sign_by_date := null;
6521
6522 lp_khrv_rec.revolving_credit_yn := l_credit_rec.REVOLVING_CREDIT_YN;
6523
6524 lp_chrv_rec.cust_acct_id := l_credit_rec.CUST_ACCT_ID;
6525
6526 update_credit_header(
6527 p_api_version => p_api_version,
6528 p_init_msg_list => p_init_msg_list,
6529 x_return_status => x_return_status,
6530 x_msg_count => x_msg_count,
6531 x_msg_data => x_msg_data,
6532 p_restricted_update => 'F',
6533 -- funding checklist enhancement
6534 p_chklst_tpl_rgp_id => l_credit_rec.CHKLST_TPL_RGP_ID,-- LACCLH
6535 p_chklst_tpl_rule_id => l_credit_rec.CHKLST_TPL_RULE_ID,-- LACCLT
6536 p_credit_ckl_id => l_credit_rec.CREDITLINE_CKL_ID,
6537 p_funding_ckl_id => l_credit_rec.FUNDING_CKL_ID,
6538 -- funding checklist enhancement
6539 p_chrv_rec => lp_chrv_rec,
6540 p_khrv_rec => lp_khrv_rec,
6541 x_chrv_rec => lx_chrv_rec,
6542 x_khrv_rec => lx_khrv_rec
6543 );
6544
6545 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
6546 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
6547 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
6548 RAISE OKL_API.G_EXCEPTION_ERROR;
6549 END IF;
6550
6551 x_sts_code := lx_chrv_rec.sts_code;
6552 /*** End API body ******************************************************/
6553
6554 -- Get message count and if count is 1, get message info
6555 FND_MSG_PUB.Count_And_Get
6556 (p_count => x_msg_count,
6557 p_data => x_msg_data);
6558
6559 EXCEPTION
6560 WHEN OKL_API.G_EXCEPTION_ERROR THEN
6561 ROLLBACK TO activate_credit_pvt;
6562 x_return_status := OKL_API.G_RET_STS_ERROR;
6563 FND_MSG_PUB.Count_And_Get
6564 (p_count => x_msg_count,
6565 p_data => x_msg_data);
6566
6567 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
6568 ROLLBACK TO activate_credit_pvt;
6569 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
6570 FND_MSG_PUB.Count_And_Get
6571 (p_count => x_msg_count,
6572 p_data => x_msg_data);
6573
6574 WHEN OTHERS THEN
6575 ROLLBACK TO activate_credit_pvt;
6576 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
6577 OKL_API.Set_Message(p_app_name => G_APP_NAME,
6578 p_msg_name => G_UNEXPECTED_ERROR,
6579 p_token1 => G_SQLCODE_TOKEN,
6580 p_token1_value => SQLCODE,
6581 p_token2 => G_SQLERRM_TOKEN,
6582 p_token2_value => SQLERRM);
6583 FND_MSG_PUB.Count_And_Get
6584 (p_count => x_msg_count,
6585 p_data => x_msg_data);
6586
6587 END activate_credit;
6588 -- end: cklee 07/12/2005
6589
6590
6591 END OKL_CREDIT_PUB;