[Home] [Help]
PACKAGE BODY: APPS.OKL_CONVERT_INT_TYPE_WF
Source
1 PACKAGE BODY OKL_CONVERT_INT_TYPE_WF as
2 /* $Header: OKLRITWB.pls 120.4 2006/07/21 13:10:58 akrangan noship $ */
3
4 --rkuttiya added for fixing problem identified during bug:2923037
5 l_ntf_result VARCHAR2(30);
6
7 -----get messages from the server side-----------------
8 PROCEDURE Get_Messages (
9 p_message_count IN NUMBER,
10 x_msgs OUT NOCOPY VARCHAR2)
11 IS
12 l_msg_list VARCHAR2(5000) := '';
13 l_temp_msg VARCHAR2(2000);
14 l_appl_short_name VARCHAR2(50) ;
15 l_message_name VARCHAR2(30) ;
16 l_id NUMBER;
17 l_message_num NUMBER;
18 l_msg_count NUMBER;
19 l_msg_data VARCHAR2(2000);
20
21 Cursor Get_Appl_Id (x_short_name VARCHAR2) IS
22 SELECT application_id
23 FROM fnd_application_vl
24 WHERE application_short_name = x_short_name;
25
26 Cursor Get_Message_Num (x_msg VARCHAR2, x_id NUMBER, x_lang_id NUMBER) IS
27 SELECT msg.message_number
28 FROM fnd_new_messages msg, fnd_languages_vl lng
29 WHERE msg.message_name = x_msg
30 and msg.application_id = x_id
31 and lng.LANGUAGE_CODE = msg.language_code
32 and lng.language_id = x_lang_id;
33 BEGIN
34 FOR l_count in 1..p_message_count LOOP
35
36 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_true);
37 fnd_message.parse_encoded(l_temp_msg, l_appl_short_name, l_message_name);
38 OPEN Get_Appl_Id (l_appl_short_name);
39 FETCH Get_Appl_Id into l_id;
40 CLOSE Get_Appl_Id;
41 l_message_num := NULL;
42
43 IF l_id is not NULL
44 THEN
45 OPEN Get_Message_Num (l_message_name, l_id,
46 to_number(NVL(FND_PROFILE.Value('LANGUAGE'), '0')));
47 FETCH Get_Message_Num into l_message_num;
48 CLOSE Get_Message_Num;
49 END IF;
50
51 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_previous, fnd_api.g_true);
52
53 IF NVL(l_message_num, 0) <> 0
54 THEN
55 l_temp_msg := 'APP-' || to_char(l_message_num) || ': ';
56 ELSE
57 l_temp_msg := NULL;
58 END IF;
59
60 IF l_count = 1
61 THEN
62 l_msg_list := l_msg_list || l_temp_msg ||
63 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
64 ELSE
65 l_msg_list := l_msg_list || l_temp_msg ||
66 fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
67 END IF;
68
69 l_msg_list := l_msg_list || '';
70
71 END LOOP;
72
73 x_msgs := l_msg_list;
74 END Get_Messages;
75
76 --------------------------------------------------------------------------------------------------
77 ----------------------------------Rasing Business Event ------------------------------------------
78 --------------------------------------------------------------------------------------------------
79 PROCEDURE raise_convert_interest_event (p_request_id IN NUMBER,
80 p_contract_id IN NUMBER,
81 x_return_status OUT NOCOPY VARCHAR2) AS
82 l_parameter_list wf_parameter_list_t;
83 l_key varchar2(240);
84 l_event_name varchar2(240) := 'oracle.apps.okl.cs.convertinteresttype';
85
86 l_seq NUMBER;
87 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
88 CURSOR okl_key_csr IS
89 SELECT okl_wf_item_s.nextval
90 FROM dual;
91 BEGIN
92 SAVEPOINT raise_convert_interest_event;
93 OPEN okl_key_csr;
94 FETCH okl_key_csr INTO l_seq;
95 CLOSE okl_key_csr;
96 l_key := l_event_name ||l_seq;
97 wf_event.AddParameterToList('TAS_ID',p_request_id,l_parameter_list);
98 wf_event.AddParameterToList('CONTRACT_ID',p_contract_id,l_parameter_list);
99 --added by akrangan
100 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
101
102 -- Raise Event
103 wf_event.raise(p_event_name => l_event_name
104 ,p_event_key => l_key
105 ,p_parameters => l_parameter_list);
106 x_return_status := l_return_status;
107 l_parameter_list.DELETE;
108 EXCEPTION
109 WHEN OTHERS THEN
110 x_return_status := OKL_API.G_RET_STS_ERROR;
111 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
112 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
113 FND_MSG_PUB.ADD;
114 ROLLBACK TO raise_convert_interest_event;
115 END raise_convert_interest_event;
116
117 --------------------------------------------------------------------------------------------------
118 ----------------------------Main Populate Notification ------------------------------------------
119 --------------------------------------------------------------------------------------------------
120 procedure populate_attributes(itemtype in varchar2,
121 itemkey in varchar2,
122 actid in number,
123 funcmode in varchar2,
124 resultout out nocopy varchar2)
125 AS
126 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
127 l_api_version NUMBER := 1.0;
128 l_msg_count NUMBER;
129 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
130 l_msg_data VARCHAR2(2000);
131
132 l_request_num OKL_TRX_REQUESTS.REQUEST_NUMBER%TYPE;
133 l_contract_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
134 l_trx_id NUMBER;
135 l_chrv_id NUMBER;
136 l_request_details OKL_TRX_REQUESTS%ROWTYPE;
137 l_cit_details OKL_CONVERT_INT_RATE_REQUEST_V%ROWTYPE;
138 l_int_details OKL_K_RATE_PARAMS_V%ROWTYPE;
139 l_var_method VARCHAR2(30);
140 l_int_method VARCHAR2(30);
141 l_calc_method VARCHAR2(30);
142 l_adj_freq VARCHAR2(30);
143 l_approver VARCHAR2(100);
144 l_index_name VARCHAR2(150);
145 l_days_in_year VARCHAR2(80);
146 l_days_in_month VARCHAR2(80);
147 l_principal_basis VARCHAR2(80);
148 l_interest_basis VARCHAR2(80);
149 l_rate_delay VARCHAR2(80);
150 l_comp_frequency VARCHAR2(80);
151 l_catchup_stlmnt VARCHAR2(80);
152 l_catchup_basis VARCHAR2(80);
153 l_conversion_type VARCHAR2(80);
154 l_conversion_option VARCHAR2(80);
155 l_formula_name VARCHAR2(150);
156
157
158 CURSOR c_fetch_k_number(p_contract_id OKC_K_HEADERS_V.ID%TYPE)
159 IS
160 SELECT chrv.contract_number
161 FROM okc_k_headers_v chrv
162 WHERE chrv.id = p_contract_id;
163
164 CURSOR c_fetch_req_details(p_request_id OKL_TRX_REQUESTS.ID%TYPE)
165 IS
166 SELECT *
167 FROM okl_trx_requests trx
168 WHERE trx.id = p_request_id;
169
170 CURSOR c_cit_details(p_request_id IN NUMBER)
171 IS
172 SELECT *
173 FROM OKL_CONVERT_INT_RATE_REQUEST_V
174 WHERE TRQ_ID = p_request_id;
175
176 CURSOR c_int_details(p_contract_id IN NUMBER,
177 p_effective_date IN DATE,
178 p_parameter_type_code IN VARCHAR2)
179 IS
180 SELECT *
181 FROM OKL_K_RATE_PARAMS_V
182 WHERE KHR_ID = p_contract_id
183 AND EFFECTIVE_FROM_DATE = p_effective_date
184 AND PARAMETER_TYPE_CODE = p_parameter_type_code;
185
186 CURSOR c_get_index(p_index_id IN NUMBER)
187 IS
188 SELECT NAME
189 FROM OKL_INDICES
190 WHERE ID = p_index_id;
191
192 CURSOR c_formula(p_formula_id IN NUMBER)
193 IS
194 SELECT NAME
195 FROM OKL_FORMULAE_V
196 WHERE ID = p_formula_id;
197
198
199 CURSOR c_get_lkp_meaning(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2)
200 IS
201 SELECT meaning
202 FROM fnd_lookups
203 WHERE lookup_type = p_lookup_type
204 AND lookup_code = p_lookup_code;
205
206 BEGIN
207
208 IF (funcmode = 'RUN') THEN
209 --rkuttiya added for bug:2923037
210 l_approver := fnd_profile.value('OKL_BILL_REQ_REP');
211 IF l_approver IS NULL THEN
212 l_approver := 'SYSADMIN';
213 END IF;
214 wf_engine.SetItemAttrText (itemtype => itemtype,
215 itemkey => itemkey,
216 aname => 'APPROVER_ROLE',
217 avalue => l_approver);
218 l_trx_id := wf_engine.GetItemAttrText(itemtype => itemtype,
219 itemkey => itemkey,
220 aname => 'TAS_ID');
221 l_chrv_id := wf_engine.GetItemAttrText(itemtype => itemtype,
222 itemkey => itemkey,
223 aname => 'CONTRACT_ID');
224
225 OPEN c_fetch_req_details(l_trx_id);
226 FETCH c_fetch_req_details INTO l_request_details;
227 CLOSE c_fetch_req_details;
228
229 OPEN c_fetch_k_number(l_chrv_id);
230 FETCH c_fetch_k_number INTO l_contract_num;
231 CLOSE c_fetch_k_number;
232
233 OPEN c_cit_details(l_trx_id);
234 FETCH c_cit_details INTO l_cit_details ;
235 CLOSE c_cit_details;
236
237 OPEN c_int_details(l_chrv_id,
238 l_cit_details.effective_from_date,
239 l_cit_details.parameter_type_code);
240 FETCH c_int_details INTO l_int_details;
241 CLOSE c_int_details;
242
243 OPEN c_get_index(l_int_details.interest_index_id);
244 FETCH c_get_index INTO l_index_name;
245 CLOSE c_get_index;
246
247 OPEN c_formula(l_int_details.calculation_formula_id);
248 FETcH c_formula INTO l_formula_name;
249 CLOSE c_formula;
250
251 wf_engine.SetItemAttrText (itemtype => itemtype,
252 itemkey => itemkey,
253 aname => 'REQUEST_NUMBER',
254 avalue => l_request_details.request_number);
255 wf_engine.SetItemAttrText (itemtype => itemtype,
256 itemkey => itemkey,
257 aname => 'CONTRACT_NUMBER',
258 avalue => l_contract_num);
259 wf_engine.SetItemAttrText (itemtype => itemtype,
260 itemkey => itemkey,
261 aname => 'EFFECTIVE_DATE',
262 avalue => l_int_details.effective_from_date);
263
264 wf_engine.SetItemAttrText (itemtype => itemtype,
265 itemkey => itemkey,
266 aname => 'INDEX_NAME',
267 avalue => l_index_name);
268
269 wf_engine.SetItemAttrText (itemtype => itemtype,
270 itemkey => itemkey,
271 aname => 'BASE_RATE',
272 avalue => l_int_details.base_rate);
273
274 wf_engine.SetItemAttrText (itemtype => itemtype,
275 itemkey => itemkey,
276 aname => 'ADDER',
277 avalue => l_int_details.adder_rate);
278
279 wf_engine.SetItemAttrText (itemtype => itemtype,
280 itemkey => itemkey,
281 aname => 'MIN_RATE',
282 avalue => l_int_details.minimum_rate);
283
284 wf_engine.SetItemAttrText (itemtype => itemtype,
285 itemkey => itemkey,
286 aname => 'MAX_RATE',
287 avalue => l_int_details.maximum_rate);
288
289 --commented out for 11i OKL.H Variable Rate
290 /* wf_engine.SetItemAttrText (itemtype => itemtype,
291 itemkey => itemkey,
292 aname => 'TOLERANCE',
293 avalue => l_request_details.tolerance);*/
294
295 OPEN c_get_lkp_meaning('OKL_YEAR_TYPE',l_int_details.days_in_a_year_code);
296 FETCH c_get_lkp_meaning INTO l_days_in_year;
297 CLOSE c_get_lkp_meaning;
298
299 OPEN c_get_lkp_meaning('OKL_MONTH_TYPE',l_int_details.days_in_a_month_code);
300 FETCH c_get_lkp_meaning INTO l_days_in_month;
301 CLOSE c_get_lkp_meaning;
302
303 OPEN c_get_lkp_meaning('OKL_PRINCIPAL_BASIS_CODE',l_int_details.principal_basis_code);
304 FETCH c_get_lkp_meaning INTO l_principal_basis;
305 CLOSE c_get_lkp_meaning;
306
307 OPEN c_get_lkp_meaning('OKL_VAR_INTCALC',l_int_details.interest_basis_code);
308 FETCH c_get_lkp_meaning INTO l_interest_basis;
309 CLOSE c_get_lkp_meaning;
310
311 OPEN c_get_lkp_meaning('OKL_RATE_DELAY_CODE',l_int_details.rate_delay_code);
312 FETCH c_get_lkp_meaning INTO l_rate_delay;
313 CLOSE c_get_lkp_meaning;
314
315 OPEN c_get_lkp_meaning('OKL_COMPOUNDING_FREQUENCY_CODE',l_int_details.compounding_frequency_code);
316 FETCH c_get_lkp_meaning INTO l_comp_frequency;
317 CLOSE c_get_lkp_meaning;
318
319 OPEN c_get_lkp_meaning('OKL_CATCHUP_SETTLEMENT_CODE',l_int_details.catchup_settlement_code);
320 FETCH c_get_lkp_meaning INTO l_catchup_stlmnt;
321 CLOSE c_get_lkp_meaning;
322
323 OPEN c_get_lkp_meaning('OKL_CATCHUP_BASIS_CODE',l_int_details.catchup_basis_code);
324 FETCH c_get_lkp_meaning INTO l_catchup_basis;
325 CLOSE c_get_lkp_meaning;
326
327 OPEN c_get_lkp_meaning('OKL_CONVERT_TYPE',l_int_details.conversion_type_code);
328 FETCH c_get_lkp_meaning INTO l_conversion_type;
329 CLOSE c_get_lkp_meaning;
330
331 OPEN c_get_lkp_meaning('OKL_CONVERSION_OPTION_CODE',l_int_details.conversion_option_code);
332 FETCH c_get_lkp_meaning INTO l_conversion_option;
333 CLOSE c_get_lkp_meaning;
334
335
336 wf_engine.SetItemAttrText (itemtype => itemtype,
337 itemkey => itemkey,
338 aname => 'DAYS_IN_YEAR',
339 avalue => l_days_in_year);
340
341 wf_engine.SetItemAttrText (itemtype => itemtype,
342 itemkey => itemkey,
343 aname => 'DAYS_IN_MONTH',
344 avalue => l_days_in_month);
345
346 wf_engine.SetItemAttrText (itemtype => itemtype,
347 itemkey => itemkey,
348 aname => 'INTEREST_START_DATE',
349 avalue => l_int_details.interest_start_date);
350
351 wf_engine.SetItemAttrText (itemtype => itemtype,
352 itemkey => itemkey,
353 aname => 'PRINCIPAL_BASIS',
354 avalue => l_principal_basis);
355
356 wf_engine.SetItemAttrText (itemtype => itemtype,
357 itemkey => itemkey,
358 aname => 'INTEREST_BASIS',
359 avalue => l_interest_basis);
360
361 wf_engine.SetItemAttrText (itemtype => itemtype,
362 itemkey => itemkey,
363 aname => 'RATE_DELAY',
364 avalue => l_rate_delay);
365
366 wf_engine.SetItemAttrText (itemtype => itemtype,
367 itemkey => itemkey,
368 aname => 'COMP_FREQ',
369 avalue => l_comp_frequency);
370
371 wf_engine.SetItemAttrText (itemtype => itemtype,
372 itemkey => itemkey,
373 aname => 'FORMULA_NAME',
374 avalue => l_formula_name);
375
376 wf_engine.SetItemAttrText (itemtype => itemtype,
377 itemkey => itemkey,
378 aname => 'RATE_DELAY_FREQ',
379 avalue => l_int_details.rate_delay_frequency);
380
381 wf_engine.SetItemAttrText (itemtype => itemtype,
382 itemkey => itemkey,
383 aname => 'CATCHUP_START_DT',
384 avalue => l_int_details.catchup_start_date);
385
386 wf_engine.SetItemAttrText (itemtype => itemtype,
387 itemkey => itemkey,
388 aname => 'CATCHUP_STLMNT',
389 avalue => l_catchup_stlmnt);
390
391 wf_engine.SetItemAttrText (itemtype => itemtype,
392 itemkey => itemkey,
393 aname => 'RATE_CHNG_START_DT',
394 avalue => l_int_details.rate_change_start_date);
395
396 wf_engine.SetItemAttrText (itemtype => itemtype,
397 itemkey => itemkey,
398 aname => 'RATE_CHNG_VALUE',
399 avalue => l_int_details.rate_change_value);
400
401 wf_engine.SetItemAttrText (itemtype => itemtype,
402 itemkey => itemkey,
403 aname => 'CATCHUP_BASIS',
404 avalue => l_catchup_basis);
405
406 wf_engine.SetItemAttrText (itemtype => itemtype,
407 itemkey => itemkey,
408 aname => 'CONVERT_TYPE',
409 avalue => l_conversion_type);
410
411 wf_engine.SetItemAttrText (itemtype => itemtype,
412 itemkey => itemkey,
413 aname => 'NEXT_CONVERSION_DT',
414 avalue => l_int_details.next_conversion_date);
415
416 wf_engine.SetItemAttrText (itemtype => itemtype,
417 itemkey => itemkey,
418 aname => 'CONVERT_OPTION',
419 avalue => l_conversion_option);
420
421
422 --populate the proposed interest rate parameters
423 OPEN c_get_lkp_meaning('OKL_DAYS_IN_A_YEAR_CODE',l_cit_details.days_in_a_year_code);
424 FETCH c_get_lkp_meaning INTO l_days_in_year;
425 CLOSE c_get_lkp_meaning;
426
427 OPEN c_get_lkp_meaning('OKL_DAYS_IN_A_MONTH_CODE',l_cit_details.days_in_a_month_code);
428 FETCH c_get_lkp_meaning INTO l_days_in_month;
429 CLOSE c_get_lkp_meaning;
430
431 OPEN c_get_lkp_meaning('OKL_PRINCIPAL_BASIS_CODE',l_cit_details.principal_basis_code);
432 FETCH c_get_lkp_meaning INTO l_principal_basis;
433 CLOSE c_get_lkp_meaning;
434
435 OPEN c_get_lkp_meaning('OKL_VAR_INTCALC',l_cit_details.interest_basis_code);
436 FETCH c_get_lkp_meaning INTO l_interest_basis;
437 CLOSE c_get_lkp_meaning;
438
439 OPEN c_get_lkp_meaning('OKL_RATE_DELAY_CODE',l_cit_details.rate_delay_code);
440 FETCH c_get_lkp_meaning INTO l_rate_delay;
441 CLOSE c_get_lkp_meaning;
442
443 OPEN c_get_lkp_meaning('OKL_COMPOUNDING_FREQUENCY_CODE',l_cit_details.compound_frequency_code);
444 FETCH c_get_lkp_meaning INTO l_comp_frequency;
445 CLOSE c_get_lkp_meaning;
446
447 OPEN c_get_lkp_meaning('OKL_CATCHUP_SETTLEMENT_CODE',l_cit_details.catchup_settlement_code);
448 FETCH c_get_lkp_meaning INTO l_catchup_stlmnt;
449 CLOSE c_get_lkp_meaning;
450
451 OPEN c_get_lkp_meaning('OKL_CATCHUP_BASIS_CODE',l_cit_details.catchup_basis_code);
452 FETCH c_get_lkp_meaning INTO l_catchup_basis;
453 CLOSE c_get_lkp_meaning;
454
455 OPEN c_get_lkp_meaning('OKL_CONVERT_TYPE',l_cit_details.conversion_type_code);
456 FETCH c_get_lkp_meaning INTO l_conversion_type;
457 CLOSE c_get_lkp_meaning;
458
459 OPEN c_get_lkp_meaning('OKL_CONVERSION_OPTION_CODE',l_cit_details.conversion_option_code);
460 FETCH c_get_lkp_meaning INTO l_conversion_option;
461 CLOSE c_get_lkp_meaning;
462
463 OPEN c_get_index(l_cit_details.interest_index_id);
464 FETCH c_get_index INTO l_index_name;
465 CLOSE c_get_index;
466
467
468
469 wf_engine.SetItemAttrText (itemtype => itemtype,
470 itemkey => itemkey,
471 aname => 'PROP_EFFECTIVE_DATE',
472 avalue => l_cit_details.proposed_effective_date);
473
474 wf_engine.SetItemAttrText (itemtype => itemtype,
475 itemkey => itemkey,
476 aname => 'PROP_INDEX_NAME',
477 avalue => l_index_name);
478
479 wf_engine.SetItemAttrText (itemtype => itemtype,
480 itemkey => itemkey,
481 aname => 'PROP_BASE_RATE',
482 avalue => l_cit_details.base_rate);
483
484 wf_engine.SetItemAttrText (itemtype => itemtype,
485 itemkey => itemkey,
486 aname => 'PROP_ADDER',
487 avalue => l_cit_details.adder_rate);
488
489 wf_engine.SetItemAttrText (itemtype => itemtype,
490 itemkey => itemkey,
491 aname => 'PROP_MIN_RATE',
492 avalue => l_cit_details.minimum_rate);
493
494 wf_engine.SetItemAttrText (itemtype => itemtype,
495 itemkey => itemkey,
496 aname => 'PROP_MAX_RATE',
497 avalue => l_cit_details.maximum_rate);
498
499 wf_engine.SetItemAttrText (itemtype => itemtype,
500 itemkey => itemkey,
501 aname => 'PROP_DAYS_YR',
502 avalue => l_days_in_year);
503
504 wf_engine.SetItemAttrText (itemtype => itemtype,
505 itemkey => itemkey,
506 aname => 'PROP_DAYS_MONTH',
507 avalue => l_days_in_month);
508
509 wf_engine.SetItemAttrText (itemtype => itemtype,
510 itemkey => itemkey,
511 aname => 'PROP_INT_START_DT',
512 avalue => l_cit_details.conversion_date);
513
514 wf_engine.SetItemAttrText (itemtype => itemtype,
515 itemkey => itemkey,
516 aname => 'PROP_PRINC_BASIS',
517 avalue => l_principal_basis);
518
519 wf_engine.SetItemAttrText (itemtype => itemtype,
520 itemkey => itemkey,
521 aname => 'PROP_INT_BASIS',
522 avalue => l_interest_basis);
523
524 wf_engine.SetItemAttrText (itemtype => itemtype,
525 itemkey => itemkey,
526 aname => 'PROP_RATE_DELAY',
527 avalue => l_rate_delay);
528
529 wf_engine.SetItemAttrText (itemtype => itemtype,
530 itemkey => itemkey,
531 aname => 'PROP_COMP_FREQ',
532 avalue => l_comp_frequency);
533
534 wf_engine.SetItemAttrText (itemtype => itemtype,
535 itemkey => itemkey,
536 aname => 'PROP_FORMULA_NAME',
537 avalue => l_cit_details.calculation_formula_name);
538
539 wf_engine.SetItemAttrText (itemtype => itemtype,
540 itemkey => itemkey,
541 aname => 'PROP_RATE_DELAY_FREQ',
542 avalue => l_cit_details.rate_delay_frequency);
543
544 wf_engine.SetItemAttrText (itemtype => itemtype,
545 itemkey => itemkey,
546 aname => 'PROP_CATCHUP_START_DT',
547 avalue => l_cit_details.catchup_start_date);
548
549 wf_engine.SetItemAttrText (itemtype => itemtype,
550 itemkey => itemkey,
551 aname => 'PROP_CATCHUP_STLMNT',
552 avalue => l_catchup_stlmnt);
553
554 wf_engine.SetItemAttrText (itemtype => itemtype,
555 itemkey => itemkey,
556 aname => 'PROP_RATE_CHNG_ST_DT',
557 avalue => l_cit_details.rate_change_start_date);
558
559 wf_engine.SetItemAttrText (itemtype => itemtype,
560 itemkey => itemkey,
561 aname => 'PROP_RATE_CHNG_VAL',
562 avalue => l_cit_details.rate_change_value);
563
564 wf_engine.SetItemAttrText (itemtype => itemtype,
565 itemkey => itemkey,
566 aname => 'PROP_CATCHUP_BASIS',
567 avalue => l_catchup_basis);
568
569 wf_engine.SetItemAttrText (itemtype => itemtype,
570 itemkey => itemkey,
571 aname => 'PROP_CONVERT_TYPE',
572 avalue => l_conversion_type);
573
574 wf_engine.SetItemAttrText (itemtype => itemtype,
575 itemkey => itemkey,
576 aname => 'PROP_NEXT_CONV_DT',
577 avalue => l_cit_details.next_conversion_date);
578
579 wf_engine.SetItemAttrText (itemtype => itemtype,
580 itemkey => itemkey,
581 aname => 'PROP_CONVERT_OPTION',
582 avalue => l_conversion_option);
583
584
585
586 --ommented out by rkuttiya for 11i OKL.H Variable Rate
587 /* OPEN c_get_lkp_meaning('OKL_VARIABLE_METHOD',l_request_details.variable_method_code);
588 FETCH c_get_lkp_meaning INTO l_var_method;
589 CLOSE c_get_lkp_meaning;
590
591 wf_engine.SetItemAttrText (itemtype => itemtype,
592 itemkey => itemkey,
593 aname => 'VARIABLE_METHOD',
594 avalue => l_var_method);
595
596 OPEN c_get_lkp_meaning('OKL_ADJ_FREQUENCY',l_request_details.adjustment_frequency_code);
597 FETCH c_get_lkp_meaning INTO l_adj_freq;
598 CLOSE c_get_lkp_meaning;
599
600 wf_engine.SetItemAttrText (itemtype => itemtype,
601 itemkey => itemkey,
602 aname => 'ADJ_FREQ',
603 avalue => l_adj_freq);
604
605 OPEN c_get_lkp_meaning('OKL_VAR_INTCALC',l_request_details.interest_method_code);
606 FETCH c_get_lkp_meaning INTO l_int_method;
607 CLOSE c_get_lkp_meaning;
608
609 wf_engine.SetItemAttrText (itemtype => itemtype,
610 itemkey => itemkey,
611 aname => 'INTEREST_METHOD',
612 avalue => l_int_method);
613
614 OPEN c_get_lkp_meaning('OKL_CALC_METHOD',l_request_details.method_of_calculation_code);
615 FETCH c_get_lkp_meaning INTO l_calc_method;
616 CLOSE c_get_lkp_meaning;
617
618 wf_engine.SetItemAttrText (itemtype => itemtype,
619 itemkey => itemkey,
620 aname => 'CALC_METHOD',
621 avalue => l_calc_method); */
622 resultout := 'COMPLETE:';
623 return;
624 END IF;
625 -- CANCEL mode
626 IF (funcmode = 'CANCEL') then
627 resultout := 'COMPLETE:';
628 return;
629 END IF;
630 -- TIMEOUT mode
631 IF (funcmode = 'TIMEOUT') then
632 resultout := 'COMPLETE:';
633 return;
634 END IF;
635 EXCEPTION
636 WHEN OKL_API.G_EXCEPTION_ERROR THEN
637 wf_core.context('OKL_CONVERT_INT_TYPE_WF',
638 'Convert_Interest_Type',
639 itemtype,
640 itemkey,
641 to_char(actid),
642 funcmode);
643 RAISE;
644 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
645 wf_core.context('OKL_CONVERT_INT_TYPE_WF',
646 'Convert_Interest_Type',
647 itemtype,
648 itemkey,
649 to_char(actid),
650 funcmode);
651 RAISE;
652 WHEN OTHERS THEN
653 wf_core.context('OKL_CONVERT_INT_TYPE_WF',
654 'Convert_Interest_Type',
655 itemtype,
656 itemkey,
657 to_char(actid),
658 funcmode);
659 RAISE;
660 END populate_attributes;
661
662
663
664 --------------------------------------------------------------------------------------------------
665 ----------------------------------Main Approval Process ------------------------------------------
666 --------------------------------------------------------------------------------------------------
667 PROCEDURE contract_approval(itemtype in varchar2,
668 itemkey in varchar2,
669 actid in number,
670 funcmode in varchar2,
671 resultout out nocopy varchar2) AS
672
673 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
674 l_api_version NUMBER := 1.0;
675 l_msg_count NUMBER;
676 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
677 l_msg_data VARCHAR2(2000);
678
679 l_request_num OKL_TRX_REQUESTS.REQUEST_NUMBER%TYPE;
680 l_trx_id NUMBER;
681 l_nid NUMBER;
682 l_trq_rec okl_trq_pvt.trqv_rec_type;
683 lx_trq_rec okl_trq_pvt.trqv_rec_type;
684
685 --rkuttiya changed for fixing problem identified during bug:2923037
686 -- l_ntf_result VARCHAR2(30);
687 l_ntf_comments VARCHAR2(4000);
688 l_sts_code VARCHAR2(30);
689
690 l_error VARCHAR2(2000);
691
692 BEGIN
693 -- We getting the request_Id from WF
694 l_trx_id := wf_engine.GetItemAttrText(itemtype => itemtype,
695 itemkey => itemkey,
696 aname => 'TAS_ID');
697 -- We need to status to Approved Pending since We are sending for approval
698 IF (funcmode = 'RESPOND') THEN
699 --get notification id from wf_engine context
700 l_nid := WF_ENGINE.CONTEXT_NID;
701 l_ntf_result := wf_notification.GetAttrText(l_nid,'RESULT');
702
703 IF l_ntf_result = 'APPROVED' THEN
704 l_sts_code := 'APPROVED';
705 ELSIF l_ntf_result = 'REJECTED' THEN
706 l_sts_code := 'REJECTED';
707 END IF;
708
709 l_trq_rec.id := l_trx_id;
710 l_trq_rec.request_status_code := l_sts_code;
711 okl_trx_requests_pub.update_trx_requests(
712 p_api_version => l_api_version,
713 p_init_msg_list => l_init_msg_list,
714 x_return_status => l_return_status,
715 x_msg_count => l_msg_count,
716 x_msg_data => l_msg_data,
717 p_trqv_rec => l_trq_rec,
718 x_trqv_rec => lx_trq_rec);
719 IF l_return_status <> 'S' THEN
720 FND_MSG_PUB.Count_And_Get
721 ( p_count => l_msg_count,
722 p_data => l_msg_data);
723 Get_Messages(l_msg_count,l_error);
724
725 wf_engine.SetItemAttrText(itemtype => itemtype,
726 itemkey => itemkey,
727 aname => 'TOLERANCE',
728 avalue => l_error);
729
730 resultout := 'COMPLETE:N';
731 ELSE
732 IF l_ntf_result = 'REJECTED' THEN
733 resultout := 'COMPLETE:REJECTED';
734 return;
735 ELSIF l_ntf_result = 'APPROVED' THEN
736 resultout := 'COMPLETE:APPROVED';
737 return;
738 END IF;
739 END IF;
740
741 --rkuttiya commented for fixing problem identified during bug:2923037
742 -- resultout := 'COMPLETE:YES';
743 -- return;
744 END IF;
745 --Run Mode
746 --rkuttiya added for fixing problem identified during bug:2923037
747 IF funcmode = 'RUN' THEN
748 resultout := 'COMPLETE:'||l_ntf_result;
749 return;
750 END IF;
751 --Transfer Mode
752 IF funcmode = 'TRANSFER' THEN
753 resultout := wf_engine.eng_null;
754 return;
755 END IF;
756 -- CANCEL mode
757 IF (funcmode = 'CANCEL') THEN
758 resultout := 'COMPLETE:NO';
759 return;
760 END IF;
761 -- TIMEOUT mode
762 IF (funcmode = 'TIMEOUT') THEN
763 resultout := 'COMPLETE:NO';
764 return;
765 END IF;
766 EXCEPTION
767 WHEN OKL_API.G_EXCEPTION_ERROR THEN
768 wf_core.context('OKL_CONVERT_INT_TYPE_WF',
769 'Convert_Interest_Type',
770 itemtype,
771 itemkey,
772 to_char(actid),
773 funcmode);
774 RAISE;
775 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
776 wf_core.context('OKL_CONVERT_INT_TYPE_WF',
777 'Convert_Interest_Type',
778 itemtype,
779 itemkey,
780 to_char(actid),
781 funcmode);
782 RAISE;
783 WHEN OTHERS THEN
784 wf_core.context('OKL_CONVERT_INT_TYPE_WF',
785 'Convert_Interest_Type',
786 itemtype,
787 itemkey,
788 to_char(actid),
789 funcmode);
790 RAISE;
791 END contract_approval;
792
793
794 END okl_convert_int_TYPE_wf;
795