[Home] [Help]
PACKAGE BODY: APPS.AR_CMGT_CONTROLS
Source
1 Package BODY AR_CMGT_CONTROLS AS
2 /* $Header: ARCMGCCB.pls 120.14 2006/01/31 07:04:06 kjoshi noship $ */
3 PROCEDURE POPULATE_DNB_DATA (
4 p_case_folder_id IN NUMBER,
5 p_source_table_name IN VARCHAR2,
6 p_source_key IN VARCHAR2,
7 p_source_key_type IN VARCHAR2 default NULL,
8 p_source_key_column_name IN VARCHAR2,
9 p_source_key_column_type IN VARCHAR2 default NULL,
10 p_errmsg OUT NOCOPY VARCHAR2,
11 p_resultout OUT NOCOPY VARCHAR2) IS
12 BEGIN
13 p_resultout := 0;
14
15 INSERT INTO ar_cmgt_cf_dnb_dtls (
16 case_folder_id,
17 source_table_name,
18 source_key,
19 source_key_type,
20 source_key_column_name,
21 source_key_column_type_name,
22 last_updated_by,
23 last_update_date,
24 created_by,
25 creation_date,
26 last_update_login)
27 VALUES
28 ( p_case_folder_id,
29 p_source_table_name,
30 p_source_key,
31 p_source_key_type,
32 p_source_key_column_name,
33 p_source_key_column_type,
34 fnd_global.user_id,
35 SYSDATE,
36 fnd_global.user_id,
37 sysdate,
38 fnd_global.login_id);
39 EXCEPTION
40 WHEN OTHERS THEN
41 p_errmsg := 'Error while inserting into ar_cmgt_cf_dnb_dtls '||sqlerrm;
42 p_resultout := 1;
43 END;
44
45 PROCEDURE POPULATE_CF_DETAILS_PVT (
46 p_case_folder_id IN NUMBER,
47 p_data_point_id IN NUMBER,
48 p_sequence_number IN NUMBER,
49 p_parent_data_point_id IN NUMBER,
50 p_parent_cf_detail_id IN NUMBER,
51 p_data_point_value IN VARCHAR2,
52 p_score IN NUMBER default NULL,
53 p_included_in_checklist IN VARCHAR2 default NULL,
54 p_data_point_value_id IN NUMBER default NULL,
55 p_case_folder_detail_id OUT NOCOPY NUMBER,
56 x_errmsg OUT NOCOPY VARCHAR2,
57 x_resultout OUT NOCOPY VARCHAR2) IS
58
59 l_date_check Date;
60 BEGIN
61 x_resultout := 0;
62 -- first validate the format of date in case the data type is DAte
63 --
64 BEGIN
65 select to_date(p_data_point_value, return_date_format)
66 INTO l_date_check
67 from ar_cmgt_data_points_vl
68 where data_point_id = p_data_point_id
69 and return_data_type = 'D';
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 NULL;
73 WHEN OTHERS THEN
74 x_resultout := 1;
75 x_errmsg := 'Incorrect Date Format for Data Point Id :'||p_data_point_id;
76 return;
77 END;
78
79 SELECT ar_cmgt_cf_dtls_s.nextval
80 INTO p_case_folder_detail_id
81 FROM dual;
82
83 INSERT INTO ar_cmgt_cf_dtls
84 ( case_folder_detail_id,
85 case_folder_id,
86 data_point_id,
87 sequence_number,
88 parent_data_point_id,
89 parent_cf_detail_id,
90 data_point_value,
91 score,
92 included_in_checklist,
93 data_point_value_id,
94 last_updated_by,
95 created_by,
96 creation_date,
97 last_update_login,
98 last_update_date)
99 VALUES ( p_case_folder_detail_id,
100 p_case_folder_id,
101 p_data_point_id,
102 p_sequence_number,
103 p_parent_data_point_id,
104 p_parent_cf_detail_id,
105 p_data_point_value,
106 p_score,
107 p_included_in_checklist,
108 p_data_point_value_id,
109 fnd_global.user_id,
110 fnd_global.user_id,
111 sysdate,
112 fnd_global.login_id,
113 sysdate);
114 return ;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 x_resultout := 1;
119 x_errmsg := 'Error While trying to populate Case folder Details '||sqlerrm;
120
121 END;
122
123 PROCEDURE POPULATE_CASE_FOLDER (
124 p_case_folder_id IN NUMBER,
125 p_case_folder_number IN VARCHAR2 default NULL,
126 p_credit_request_id IN NUMBER default NULL,
127 p_check_list_id IN NUMBER default NULL,
128 p_status IN VARCHAR2 default NULL,
129 p_party_id IN NUMBER,
130 p_cust_account_id IN NUMBER,
131 p_cust_acct_site_id IN NUMBER,
132 p_score_model_id IN NUMBER default NULL,
133 p_credit_classification IN VARCHAR2 default NULL,
134 p_review_type IN VARCHAR2 default NULL,
135 p_limit_currency IN VARCHAR2,
136 p_exchange_rate_type IN VARCHAR2,
137 p_type IN VARCHAR2 ,
138 p_errmsg OUT NOCOPY VARCHAR2,
139 p_resultout OUT NOCOPY VARCHAR2) IS
140
141 l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
142 l_case_folder_number ar_cmgt_case_folders.case_folder_number%type;
143 BEGIN
144 p_resultout := 0;
145 IF (p_case_folder_id IS NULL) OR (p_case_folder_id = -99)
146 THEN
147 SELECT ar_cmgt_case_folders_s.nextval
148 INTO l_case_folder_id
149 FROM DUAL;
150 ELSE
151 l_case_folder_id := p_case_folder_id;
152 END IF;
153 IF p_case_folder_number IS NULL
154 THEN
155 SELECT ar_cmgt_case_folder_number_s.nextval
156 INTO l_case_folder_number
157 FROM DUAL;
158 ELSE
159 l_case_folder_number := p_case_folder_number;
160 END IF;
161 INSERT INTO ar_cmgt_case_folders (
162 case_folder_id,
163 case_folder_number,
164 credit_request_id,
165 check_list_id,
166 status,
167 cust_account_id,
168 party_id,
169 site_use_id,
170 score_model_id,
171 credit_classification,
172 review_type,
173 type,
174 limit_currency,
175 exchange_rate_type,
176 last_updated,
177 last_updated_by,
178 last_update_date,
179 last_update_login,
180 creation_date,
181 created_by,
182 creation_date_time)
183 VALUES ( l_case_folder_id,
184 l_case_folder_number,
185 p_credit_request_id,
186 p_check_list_id,
187 'CREATED',
188 nvl(p_cust_account_id,-99),
189 p_party_id,
190 nvl(p_cust_acct_site_id,-99),
191 p_score_model_id,
192 p_credit_classification,
193 p_review_type,
194 p_type,
195 p_limit_currency,
196 p_exchange_rate_type,
197 SYSDATE,
198 fnd_global.user_id,
199 SYSDATE,
200 fnd_global.user_id,
201 SYSDATE,
202 fnd_global.login_id,
203 SYSDATE);
204 EXCEPTION
205 WHEN OTHERS
206 THEN
207 p_errmsg := 'Error while populating Case folder Data '||sqlerrm;
208 p_resultout := 1;
209
210
211 END;
212
213 PROCEDURE POPULATE_CASE_FOLDER_DETAILS (
214 p_case_folder_id IN NUMBER,
215 p_data_point_id IN NUMBER,
216 p_data_point_value IN VARCHAR2,
217 p_included_in_check_list IN VARCHAR2,
218 p_score IN NUMBER default NULL,
219 p_errmsg OUT NOCOPY VARCHAR2,
220 p_resultout OUT NOCOPY VARCHAR2) IS
221
222 l_case_folder_detail_id NUMBER;
223 BEGIN
224 p_resultout := 0;
225
226 POPULATE_CF_DETAILS_PVT
227 ( p_case_folder_id,
228 p_data_point_id,
229 1,
230 null,
231 null,
232 p_data_point_value,
233 p_score,
234 p_included_in_check_list,
235 NULL,
236 l_case_folder_detail_id,
237 p_errmsg,
238 p_resultout);
239 EXCEPTION
240 WHEN OTHERS THEN
241 p_resultout := 1;
242 p_errmsg := 'Error While trying to populate Case folder Details '||sqlerrm;
243 END;
244
245 PROCEDURE UPDATE_CASE_FOLDER_DETAILS (
246 p_case_folder_id IN NUMBER,
247 p_data_point_id IN NUMBER,
248 p_data_point_value IN VARCHAR2,
249 p_score IN NUMBER default NULL,
250 p_errmsg OUT NOCOPY VARCHAR2,
251 p_resultout OUT NOCOPY VARCHAR2) IS
252 BEGIN
253 p_resultout := 0;
254
255 UPDATE ar_cmgt_cf_dtls
256 SET score = nvl(p_score,score),
257 data_point_value = nvl(p_data_point_value, data_point_value),
258 last_updated_by = fnd_global.user_id,
259 last_update_date = sysdate,
260 last_update_login = fnd_global.login_id
261 WHERE case_folder_id = p_case_folder_id
262 AND data_point_id = p_data_point_id;
263
264 IF sql%NOTFOUND
265 THEN
266 POPULATE_CASE_FOLDER_DETAILS (
267 p_case_folder_id => p_case_folder_id,
268 p_data_point_id => p_data_point_id,
269 p_data_point_value => p_data_point_value,
270 p_included_in_check_list => 'N',
271 p_score => p_score,
272 p_errmsg => p_errmsg,
273 p_resultout => p_resultout);
274 END IF;
275 EXCEPTION
276 WHEN OTHERS THEN
277 p_resultout := 1;
278 p_errmsg := 'Error While trying to update Case folder Details '||sqlerrm;
279
280 END;
281
282 PROCEDURE POPULATE_CF_ADP_DETAILS (
283 p_case_folder_id IN NUMBER,
284 p_data_point_id IN NUMBER,
285 p_sequence_number IN NUMBER,
286 p_parent_data_point_id IN NUMBER,
287 p_parent_cf_detail_id IN NUMBER,
288 p_data_point_value IN VARCHAR2,
289 p_score IN NUMBER default NULL,
290 p_included_in_checklist IN VARCHAR2 default NULL,
291 p_data_point_value_id IN NUMBER default NULL,
292 p_case_folder_detail_id OUT NOCOPY NUMBER,
293 p_errmsg OUT NOCOPY VARCHAR2,
294 p_resultout OUT NOCOPY VARCHAR2) IS
295
296 BEGIN
297 p_resultout := 0;
298 POPULATE_CF_DETAILS_PVT
299 ( p_case_folder_id,
300 p_data_point_id,
301 p_sequence_number,
302 p_parent_data_point_id,
303 p_parent_cf_detail_id,
304 p_data_point_value,
305 p_score,
306 p_included_in_checklist,
307 p_data_point_value_id,
308 p_case_folder_detail_id,
309 p_errmsg,
310 p_resultout);
311 END;
312
313 PROCEDURE UPDATE_CF_ADP_DETAILS (
314 p_case_folder_id IN NUMBER,
315 p_data_point_id IN NUMBER,
316 p_sequence_number IN NUMBER,
317 p_parent_data_point_id IN NUMBER,
318 p_parent_cf_detail_id IN NUMBER,
319 p_data_point_value IN VARCHAR2,
320 p_score IN NUMBER default NULL,
321 p_included_in_checklist IN VARCHAR2 default NULL,
322 p_data_point_value_id IN NUMBER,
323 p_case_folder_detail_id IN OUT NOCOPY NUMBER,
324 x_errmsg OUT NOCOPY VARCHAR2,
325 x_resultout OUT NOCOPY VARCHAR2)
326 IS
327 BEGIN
328 x_resultout := 0;
329
330 UPDATE ar_cmgt_cf_dtls
331 SET score = nvl(p_score,score),
332 data_point_value = nvl(p_data_point_value, data_point_value),
333 last_updated_by = fnd_global.user_id,
334 last_update_date = sysdate,
335 last_update_login = fnd_global.login_id
336 WHERE case_folder_detail_id = p_case_folder_detail_id
337 AND data_point_id = p_data_point_id;
338
339 IF sql%NOTFOUND
340 THEN
341 POPULATE_CF_DETAILS_PVT (
342 p_case_folder_id => p_case_folder_id,
343 p_data_point_id => p_data_point_id,
344 p_sequence_number => p_sequence_number,
345 p_parent_data_point_id => p_parent_data_point_id,
346 p_parent_cf_detail_id => p_parent_cf_detail_id,
347 p_data_point_value => p_data_point_value,
348 p_score => p_score,
349 p_included_in_checklist => 'N',
350 p_data_point_value_id => p_data_point_value_id,
351 p_case_folder_detail_id => p_case_folder_detail_id,
352 x_errmsg => x_errmsg,
353 x_resultout => x_resultout);
354 END IF;
355 EXCEPTION
356 WHEN OTHERS THEN
357 x_resultout := 1;
358 x_errmsg := 'Error While trying to update Case folder Details '||sqlerrm;
359
360 END;
361 procedure populate_recommendation(
362 p_case_folder_id IN NUMBER,
363 p_credit_request_id IN NUMBER,
364 p_score IN NUMBER,
365 p_recommended_credit_limit IN NUMBER,
366 p_credit_review_date IN DATE,
367 p_credit_recommendation IN VARCHAR2,
368 p_recommendation_value1 IN VARCHAR2,
369 p_recommendation_value2 IN VARCHAR2,
370 p_status IN VARCHAR2,
371 p_credit_type IN VARCHAR2,
372 p_errmsg OUT NOCOPY VARCHAR2,
373 p_resultout OUT NOCOPY VARCHAR2 ) IS
374
375 l_recommendation_name ar_cmgt_credit_requests.recommendation_name%type;
376 l_appl_id ar_cmgt_credit_requests.source_resp_appln_id%type;
377 BEGIN
378 p_resultout := 0;
379 BEGIN
380 SELECT recommendation_name,source_resp_appln_id
381 INTO l_recommendation_name, l_appl_id
382 FROM ar_cmgt_credit_requests
383 WHERE credit_request_id = p_credit_request_id;
384 EXCEPTION
385 WHEN OTHERS THEN
386 p_resultout := 1;
387 p_errmsg := 'Error While Selecting Recommendation Name'||sqlerrm;
388 return;
389 END;
390 IF l_recommendation_name IS NULL
391 THEN
392 IF p_credit_type = 'TERM'
393 THEN
394 l_recommendation_name := 'AR_CMGT_TERM_RECOMMENDATIONS';
395 ELSIF p_credit_type = 'TRADE'
396 THEN
397 l_recommendation_name := 'AR_CMGT_RECOMMENDATIONS';
398 END IF;
399 END IF;
400 insert into ar_cmgt_cf_recommends
401 ( Recommendation_id,
402 case_folder_id,
403 credit_request_id,
404 credit_review_date,
405 credit_recommendation,
406 recommendation_value1,
407 recommendation_value2,
408 status,
409 last_updated_by,
410 last_update_date,
411 last_update_login,
412 creation_date,
413 created_by,
417 ( ar_cmgt_cf_recommends_s.nextval,
414 credit_type,
415 recommendation_name,
416 application_id) values
418 p_case_folder_id,
419 p_credit_request_id,
420 p_credit_review_date,
421 p_credit_recommendation,
422 p_recommendation_value1,
423 p_recommendation_value2,
424 p_status,
425 fnd_global.user_id,
426 sysdate,
427 fnd_global.login_id,
428 sysdate,
429 fnd_global.user_id,
430 p_credit_type,
431 l_recommendation_name,
432 l_appl_id);
433 EXCEPTION
434 WHEN OTHERS THEN
435 p_resultout := 1;
436 p_errmsg := 'Error While creating Recommendation '||sqlerrm;
437 END;
438
439 procedure populate_data_points
440 ( p_data_point_name IN VARCHAR2,
441 p_data_point_category IN VARCHAR2,
442 p_user_defined_flag IN VARCHAR2,
443 p_scorable_flag IN VARCHAR2,
444 p_display_on_checklist IN VARCHAR2,
445 p_created_by IN NUMBER,
446 p_data_point_code IN VARCHAR2,
447 p_data_point_id OUT NOCOPY NUMBER) IS
448
449 BEGIN
450 SELECT ar_cmgt_data_points_s.nextval
451 INTO p_data_point_id
452 FROM dual;
453
454 AR_CMGT_DP_TABLE_HANDLER.insert_row(
455 p_data_point_name => p_data_point_name,
456 p_description => null,
457 p_data_point_category => p_data_point_category,
458 p_user_defined_flag => p_user_defined_flag,
459 p_scorable_flag => p_scorable_flag,
460 p_display_on_checklist => p_display_on_checklist,
461 p_created_by => fnd_global.user_id,
462 p_last_updated_by => fnd_global.user_id,
463 p_last_update_login => fnd_global.login_id,
464 p_data_point_id => p_data_point_id,
465 p_return_data_type => 'C',
466 p_return_date_format => null,
467 p_application_id => 222,
468 p_parent_data_point_id => null,
469 p_enabled_flag => 'N',
470 p_package_name => null,
471 p_function_name => null,
472 p_data_point_sub_category => null,
473 p_data_point_code => p_data_point_code);
474
475 END;
476
477 PROCEDURE populate_add_data_points
478 ( p_data_point_code IN VARCHAR2,
479 p_data_point_name IN VARCHAR2,
480 p_description IN VARCHAR2,
481 p_data_point_sub_category IN VARCHAR2,
482 p_data_point_category IN VARCHAR2,
483 p_user_defined_flag IN VARCHAR2,
484 p_scorable_flag IN VARCHAR2,
485 p_display_on_checklist IN VARCHAR2,
486 p_created_by IN NUMBER,
487 p_application_id IN NUMBER,
488 p_parent_data_point_id IN NUMBER,
489 p_enabled_flag IN VARCHAR2,
490 p_package_name IN VARCHAR2,
491 p_function_name IN VARCHAR2,
492 p_function_type IN VARCHAR2,
493 p_return_data_type IN VARCHAR2,
494 p_return_date_format IN VARCHAR2,
495 x_data_point_id OUT NOCOPY NUMBER
496 )
497 IS
498 l_dp_id NUMBER;
499 BEGIN
500
501 SELECT ar_cmgt_data_points_s.nextval
502 INTO l_dp_id
503 FROM dual;
504
505 x_data_point_id := l_dp_id;
506
507 AR_CMGT_DP_TABLE_HANDLER.insert_adp_row(
508 p_data_point_code => p_data_point_code,
509 p_data_point_name => p_data_point_name,
510 p_description => p_description,
511 p_data_point_sub_category => p_data_point_sub_category,
512 p_data_point_category => p_data_point_category,
513 p_user_defined_flag => p_user_defined_flag,
514 p_scorable_flag => p_scorable_flag,
515 p_display_on_checklist => p_display_on_checklist,
516 p_created_by => fnd_global.user_id,
517 p_last_updated_by => fnd_global.user_id,
518 p_last_update_login => fnd_global.login_id,
519 p_data_point_id => l_dp_id,
520 p_application_id => p_application_id,
521 p_parent_data_point_id => p_parent_data_point_id,
522 p_enabled_flag => p_enabled_flag,
523 p_package_name => p_package_name,
524 p_function_name => p_function_name,
525 p_function_type => p_function_type,
526 p_return_data_type => p_return_data_type,
527 p_return_date_format => p_return_date_format );
528 END;
529
530 PROCEDURE populate_aging_dtls(
531 p_case_folder_id IN NUMBER,
532 p_aging_bucket_id IN NUMBER,
533 p_aging_bucket_line_id IN NUMBER,
534 p_amount IN NUMBER,
535 p_error_msg OUT NOCOPY VARCHAR2,
536 p_resultout OUT NOCOPY VARCHAR2) IS
537 BEGIN
538 INSERT INTO AR_CMGT_CF_AGING_DTLS
539 (
540 case_folder_id,
541 last_updated_by,
542 last_update_date,
543 last_update_login,
547 aging_bucket_line_id,
544 creation_date,
545 created_by,
546 aging_bucket_id,
548 amount
549 )
550 values
551 (p_case_folder_id,
552 fnd_global.user_id,
553 sysdate,
554 fnd_global.user_id,
555 sysdate,
556 fnd_global.user_id,
557 p_aging_bucket_id,
558 p_aging_bucket_line_id,
559 p_amount
560 );
561 EXCEPTION
562 WHEN OTHERS THEN
563 p_error_msg := 'Error While creating Aging records for casefolder id '
564 ||p_case_folder_id ||' SqlError '||sqlerrm;
565 p_resultout := 1;
566 END;
567
568 PROCEDURE update_aging_dtls(
569 p_case_folder_id IN NUMBER,
570 p_aging_bucket_id IN NUMBER,
571 p_aging_bucket_line_id IN NUMBER,
572 p_amount IN NUMBER,
573 p_error_msg OUT NOCOPY VARCHAR2,
574 p_resultout OUT NOCOPY VARCHAR2) IS
575 BEGIN
576 UPDATE AR_CMGT_CF_AGING_DTLS
577 SET last_updated_by = fnd_global.user_id,
578 last_update_date = sysdate,
579 last_update_login = fnd_global.login_id,
580 amount = p_amount
581 WHERE case_folder_id = p_case_folder_id
582 AND aging_bucket_id = p_aging_bucket_id
583 AND aging_bucket_line_id = p_aging_bucket_line_id;
584
585 EXCEPTION
586 WHEN OTHERS THEN
587 p_error_msg := 'Error While updating Aging records for casefolder id '
588 ||p_case_folder_id ||' SqlError '||sqlerrm;
589 p_resultout := 1;
590 END;
591
592
593 /*--This procedure creates duplicate case folder in case of appeal and re-submit
594 --------------------------------------------------------------------------------*/
595 PROCEDURE DUPLICATE_CASE_FOLDER_TBL
596 ( p_parnt_case_folder_id IN NUMBER ,
597 p_credit_request_id IN NUMBER ,
598 p_errmsg OUT NOCOPY VARCHAR2,
599 p_resultout OUT NOCOPY VARCHAR2
600 ) IS
601 l_credit_request_id AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE;
602 l_case_folders_id AR_CMGT_CASE_FOLDERS.CASE_FOLDER_ID%TYPE;
603 BEGIN
604
605 l_credit_request_id := p_credit_request_id;
606 l_case_folders_id := p_parnt_case_folder_id;
607 p_resultout :=0;
608 -- Create the duplicate record.
609 INSERT INTO AR_CMGT_CASE_FOLDERS
610 (
611 CASE_FOLDER_ID,
612 CASE_FOLDER_NUMBER,
613 LAST_UPDATED_BY,
614 LAST_UPDATE_DATE,
615 LAST_UPDATE_LOGIN,
616 CREATION_DATE,
617 CREATED_BY,
618 CREDIT_REQUEST_ID,
619 CHECK_LIST_ID,
620 STATUS,
621 CUST_ACCOUNT_ID,
622 PARTY_ID,
623 SCORE_MODEL_ID,
624 SITE_USE_ID,
625 CREDIT_CLASSIFICATION,
626 REVIEW_TYPE,
627 CREDIT_ANALYST_ID,
628 TYPE,
629 DISPLAY_FLAG,
630 CREATION_DATE_TIME,
631 LAST_UPDATED,
632 LIMIT_CURRENCY,
633 EXCHANGE_RATE_TYPE,
634 REVIEW_CYCLE)
635 ( SELECT AR_CMGT_CASE_FOLDERS_s.NEXTVAL,
636 ar_cmgt_case_folder_number_s.nextval,
637 fnd_global.user_id,
638 SYSDATE,
639 fnd_global.user_id,
640 SYSDATE,
641 fnd_global.login_id,
642 l_credit_request_id,
643 CHECK_LIST_ID,
644 'CREATED',
645 CUST_ACCOUNT_ID,
646 PARTY_ID,
647 SCORE_MODEL_ID,
648 SITE_USE_ID,
649 CREDIT_CLASSIFICATION,
650 REVIEW_TYPE,
651 CREDIT_ANALYST_ID,
652 TYPE,
653 DISPLAY_FLAG,
657 EXCHANGE_RATE_TYPE,
654 SYSDATE,
655 SYSDATE,
656 LIMIT_CURRENCY,
658 REVIEW_CYCLE
659 FROM AR_CMGT_CASE_FOLDERS
660 WHERE CASE_FOLDER_ID = l_case_folders_id
661 AND TYPE = 'CASE') ;
662 EXCEPTION
663 WHEN OTHERS
664 THEN
665 p_errmsg := 'Error while populating Case folder Data '||sqlerrm;
666 p_resultout := 1;
667 END DUPLICATE_CASE_FOLDER_TBL;
668
669 /*--This procedure creates duplicate record for case folder details for appeal and re-submit
670 ------------------------------------------------------------------------------------------*/
671 PROCEDURE DUPLICATE_CASE_FOLDER_DTLS(
672 p_parnt_case_folder_id IN NUMBER ,
673 p_credit_request_id IN NUMBER ,
674 p_errmsg OUT NOCOPY VARCHAR2,
675 p_resultout OUT NOCOPY VARCHAR2
676 ) IS
677 l_credit_request_id AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE;
678 l_case_folders_id AR_CMGT_CASE_FOLDERS.CASE_FOLDER_ID%TYPE;
679 l_processing_flag VARCHAR2(1);
680 TYPE list_change_id IS RECORD (
681 parent NUMBER,
682 changed NUMBER);
683 l_rec_changed list_change_id;
684 TYPE list_of_ids IS VARRAY(50000) OF list_change_id;
685 rec_changed_ids list_of_ids := list_of_ids();
686 counter BINARY_INTEGER;
687 counter1 BINARY_INTEGER;
688 l_seq_num AR_CMGT_CF_DTLS.CASE_FOLDER_DETAIL_ID%TYPE;
689
690 CURSOR select_dtls IS
691 SELECT case_folder_id,
692 DATA_POINT_ID,
693 DATA_POINT_VALUE,
694 INCLUDED_IN_CHECKLIST,
695 SCORE,
696 CASE_FOLDER_DETAIL_ID,
697 SEQUENCE_NUMBER,
698 PARENT_DATA_POINT_ID,
699 PARENT_CF_DETAIL_ID
700 FROM AR_CMGT_CF_DTLS
701 WHERE CASE_FOLDER_ID=p_parnt_case_folder_id;
702
703 BEGIN
704
705 p_resultout :=0;
706 l_processing_flag :='Y';
707 counter :=1;
708 --fetch the newly created case_folder_id.
709
710 BEGIN
711
712 SELECT CASE_FOLDER_ID
713 INTO l_case_folders_id
714 FROM AR_CMGT_CASE_FOLDERS
715 WHERE CREDIT_REQUEST_ID = p_credit_request_id;
716
717 EXCEPTION
718 WHEN OTHERS
719 THEN
720 p_errmsg := 'Error while fetching Case folder ID '||sqlerrm;
721 p_resultout := 1;
722 l_processing_flag :='N';
723 END;
724
725 --create the details record
726
727
728 IF l_processing_flag = 'Y'
729
730 THEN
731
732
733 FOR select_dtls_rec IN select_dtls
734 LOOP
735 BEGIN
736
737 SELECT ar_cmgt_cf_dtls_s.NEXTVAL
738 INTO l_seq_num
739 FROM dual;
740
741 --store the parent_rec_id and corrosponding seq_num_id
742
743 l_rec_changed.parent := select_dtls_rec.CASE_FOLDER_DETAIL_ID;
744 l_rec_changed.changed := l_seq_num;
745
746 rec_changed_ids.EXTEND;
747
748 rec_changed_ids(counter) := l_rec_changed;
749
750 counter := counter +1;
751
752
753
754 --insert the records into case folder details.
755
756 INSERT INTO AR_CMGT_CF_DTLS
757 (CASE_FOLDER_ID,
758 DATA_POINT_ID,
759 LAST_UPDATED_BY,
760 LAST_UPDATE_DATE,
761 LAST_UPDATE_LOGIN,
762 CREATION_DATE,
763 CREATED_BY,
764 DATA_POINT_VALUE,
765 INCLUDED_IN_CHECKLIST,
766 SCORE,
767 CASE_FOLDER_DETAIL_ID,
768 SEQUENCE_NUMBER,
769 PARENT_DATA_POINT_ID,
770 PARENT_CF_DETAIL_ID)
771 (select l_case_folders_id,
772 DATA_POINT_ID,
773 fnd_global.user_id,
774 SYSDATE,
775 fnd_global.login_id,
776 SYSDATE,
777 fnd_global.user_id,
778 DATA_POINT_VALUE,
779 INCLUDED_IN_CHECKLIST,
780 SCORE,
781 l_seq_num,
782 SEQUENCE_NUMBER,
783 PARENT_DATA_POINT_ID,
784 PARENT_CF_DETAIL_ID
785 FROM AR_CMGT_CF_DTLS
786 WHERE CASE_FOLDER_ID=p_parnt_case_folder_id
787 AND CASE_FOLDER_DETAIL_ID = select_dtls_rec.CASE_FOLDER_DETAIL_ID);
788
789
790
791 EXCEPTION
792 WHEN OTHERS
793 THEN
794 p_errmsg := 'Error while populating Case folder Data '||sqlerrm;
795 p_resultout := 1;
796
800 END LOOP;
797 l_processing_flag :='N';
798 return;
799 END;
801
802
803
804
805 --update the records with parent cf detail id
806 --with the new changed values
807 if l_processing_flag = 'Y'
808 THEN
809
810 counter1 := 1;
811
812 WHILE counter1 < counter
813 LOOP
814 BEGIN
815
816
817 l_rec_changed := rec_changed_ids(counter1);
818 counter1 := counter1 + 1 ;
819 --update the values of parent cf detail id
820
821 UPDATE ar_cmgt_cf_dtls
822 SET parent_cf_detail_id = l_rec_changed.changed
823 WHERE case_folder_id = l_case_folders_id
824 AND PARENT_CF_DETAIL_ID = l_rec_changed.parent;
825
826 EXCEPTION
827 WHEN NO_DATA_FOUND
828 THEN
829 --do nothing continue in loop.
830 l_processing_flag :='Y';
831 WHEN OTHERS
832 THEN
833 p_errmsg := 'Error while updating Case folder Data '||sqlerrm;
834 p_resultout := 1;
835
836 END;
837 END LOOP;
838
839 END IF;
840
841
842
843 END IF;
844 END DUPLICATE_CASE_FOLDER_DTLS;
845
846 /*--This procedure creates duplicate record for aging details for appeal and re-submit
847 ------------------------------------------------------------------------------------------*/
848 PROCEDURE DUPLICATE_AGING_DATA(
849 p_parnt_case_folder_id IN NUMBER ,
850 p_credit_request_id IN NUMBER ,
851 p_errmsg OUT NOCOPY VARCHAR2,
852 p_resultout OUT NOCOPY VARCHAR2
853 ) IS
854 l_credit_request_id AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE;
855 l_case_folders_id AR_CMGT_CASE_FOLDERS.CASE_FOLDER_ID%TYPE;
856 l_processing_flag VARCHAR2(1);
857 BEGIN
858
859 p_resultout :=0;
860 l_processing_flag := 'Y';
861 --fetch the newly created case_folder_id.
862 BEGIN
863
864 SELECT CASE_FOLDER_ID
865 INTO l_case_folders_id
866 FROM AR_CMGT_CASE_FOLDERS
867 WHERE CREDIT_REQUEST_ID = p_credit_request_id;
868
869 EXCEPTION
870 WHEN OTHERS
871 THEN
872 p_errmsg := 'Error while fetching Case folder ID '||sqlerrm;
873 p_resultout := 1;
874 l_processing_flag :='N';
875 END;
876 --duplicate aging details
877
878 IF l_processing_flag = 'Y'
879 THEN
880
881 BEGIN
882
883 INSERT INTO AR_CMGT_CF_AGING_DTLS
884 (CASE_FOLDER_ID,
885 LAST_UPDATED_BY,
886 LAST_UPDATE_DATE,
887 LAST_UPDATE_LOGIN,
888 CREATION_DATE,
889 CREATED_BY,
890 AGING_BUCKET_ID,
891 AGING_BUCKET_LINE_ID,
892 AMOUNT,
893 COUNT)
894 (SELECT l_case_folders_id,
895 fnd_global.user_id,
896 SYSDATE,
897 fnd_global.login_id,
898 SYSDATE,
899 fnd_global.user_id,
900 AGING_BUCKET_ID,
901 AGING_BUCKET_LINE_ID,
902 AMOUNT,
903 COUNT
904 FROM AR_CMGT_CF_AGING_DTLS
905 WHERE CASE_FOLDER_ID=p_parnt_case_folder_id);
906
907 EXCEPTION
908 WHEN OTHERS
909 THEN
910 p_errmsg := 'Error while populating Aging Data '||sqlerrm;
911 p_resultout := 1;
912 END;
913 END IF;
914 END DUPLICATE_AGING_DATA;
915
916 /*--This procedure creates duplicate record for DNB DATA for appeal and re-submit
917 ------------------------------------------------------------------------------------------*/
918 PROCEDURE DUPLICATE_DNB_DATA(
919 p_parnt_case_folder_id IN NUMBER ,
920 p_credit_request_id IN NUMBER ,
921 p_errmsg OUT NOCOPY VARCHAR2,
922 p_resultout OUT NOCOPY VARCHAR2
923 ) IS
924 l_credit_request_id AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE;
925 l_case_folders_id AR_CMGT_CASE_FOLDERS.CASE_FOLDER_ID%TYPE;
926 l_processing_flag VARCHAR2(1);
927 BEGIN
928
929 p_resultout :=0;
930 l_processing_flag := 'Y';
931 --fetch the newly created case_folder_id.
932 BEGIN
933
934 SELECT CASE_FOLDER_ID
935 INTO l_case_folders_id
936 FROM AR_CMGT_CASE_FOLDERS
937 WHERE CREDIT_REQUEST_ID = p_credit_request_id;
938
939 EXCEPTION
940 WHEN OTHERS
941 THEN
942 p_errmsg := 'Error while fetching Case folder ID '||sqlerrm;
943 p_resultout := 1;
944 l_processing_flag :='N';
948 THEN
945 END;
946 --duplicate DNB data.
947 IF l_processing_flag = 'Y'
949
950 BEGIN
951 INSERT INTO AR_CMGT_CF_DNB_DTLS
952 (CASE_FOLDER_ID,
953 SOURCE_TABLE_NAME,
954 SOURCE_KEY,
955 SOURCE_KEY_TYPE,
956 SOURCE_KEY_COLUMN_NAME,
957 SOURCE_KEY_COLUMN_TYPE_NAME,
958 LAST_UPDATED_BY,
959 LAST_UPDATE_DATE,
960 LAST_UPDATE_LOGIN,
961 CREATION_DATE,
962 CREATED_BY)
963 (SELECT l_case_folders_id,
964 SOURCE_TABLE_NAME,
965 SOURCE_KEY,
966 SOURCE_KEY_TYPE,
967 SOURCE_KEY_COLUMN_NAME,
968 SOURCE_KEY_COLUMN_TYPE_NAME,
969 fnd_global.user_id,
970 sysdate,
971 fnd_global.login_id,
972 sysdate,
973 fnd_global.user_id
974 FROM AR_CMGT_CF_DNB_DTLS
975 WHERE CASE_FOLDER_ID=p_parnt_case_folder_id);
976 EXCEPTION
977 WHEN OTHERS
978 THEN
979 p_errmsg := 'Error while populating DNB Data '||sqlerrm;
980 p_resultout := 1;
981 END;
982 END IF;
983 END DUPLICATE_DNB_DATA;
984
985 /*--This procedure creates duplicate record for financial data for appeal and re-submit
986 ------------------------------------------------------------------------------------------*/
987 PROCEDURE DUPLICATE_FINANCIAL_DATA(
988 p_parnt_credit_req_id IN NUMBER ,
989 p_credit_request_id IN NUMBER ,
990 p_errmsg OUT NOCOPY VARCHAR2,
991 p_resultout OUT NOCOPY VARCHAR2
992 ) IS
993 l_credit_request_id AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE;
994 l_case_folders_id AR_CMGT_CASE_FOLDERS.CASE_FOLDER_ID%TYPE;
995 l_processing_flag VARCHAR2(1);
996 BEGIN
997 l_credit_request_id := p_credit_request_id;
998 p_resultout :=0;
999 l_processing_flag := 'Y';
1000 --fetch the newly created case_folder_id.
1001 BEGIN
1002
1003 SELECT CASE_FOLDER_ID
1004 INTO l_case_folders_id
1005 FROM AR_CMGT_CASE_FOLDERS
1006 WHERE CREDIT_REQUEST_ID = p_credit_request_id;
1007
1008 EXCEPTION
1009 WHEN OTHERS
1010 THEN
1011 p_errmsg := 'Error while fetching Case folder ID '||sqlerrm;
1012 p_resultout := 1;
1013 l_processing_flag :='N';
1014 END;
1015 --duplicate financial data.
1016 IF l_processing_flag = 'Y'
1017 THEN
1018 BEGIN
1019
1020 INSERT INTO AR_CMGT_FINANCIAL_DATA
1021 (FINANCIAL_DATA_ID,
1022 CREDIT_REQUEST_ID,
1023 LAST_UPDATE_DATE,
1024 LAST_UPDATED_BY,
1025 CREATION_DATE,
1026 CREATED_BY,
1027 LAST_UPDATE_LOGIN,
1028 REPORTING_CURRENCY,
1029 MONETARY_UNIT,
1030 CURR_FIN_ST_DATE,
1031 REPORTING_PERIOD,
1032 CASH,
1033 ACCOUNTS_PAYABLE,
1034 NET_RECEIVABLES,
1035 SHORT_TERM_DEBT,
1036 INVENTORIES,
1037 OTHER_CUR_LIABILITIES,
1038 OTHER_CUR_ASSETS,
1039 TOTAL_CUR_LIABILITIES,
1040 TOTAL_CUR_ASSETS,
1041 LONG_TERM_DEBT,
1042 NET_FIXED_ASSETS,
1043 OTHER_NON_CUR_LIABILITIES,
1044 OTHER_NON_CUR_ASSETS,
1045 TOTAL_LIABILITIES,
1046 TOTAL_ASSETS,
1047 STOCKHOLDER_EQUITY,
1048 TOTAL_LIABILITIES_EQUITY,
1049 REVENUE,
1050 NON_OPERATING_INCOME,
1051 COST_OF_GOODS_SOLD,
1052 NON_OPERATING_EXPENSES,
1053 SGA_EXPENSES,
1054 PRE_TAX_NET_INCOME,
1055 OPERATING_INCOME,
1056 INCOME_TAXES,
1057 OPERATING_MARGIN,
1058 NET_INCOME,
1059 EARNINGS_PER_SHARE,
1060 CASE_FOLDER_ID,
1061 PARTY_ID,
1062 CUST_ACCOUNT_ID,
1063 SITE_USE_ID)
1064 (SELECT AR_CMGT_FINANCIAL_DATA_S.nextval,
1065 l_credit_request_id,
1066 SYSDATE,
1067 fnd_global.user_id,
1068 SYSDATE,
1072 MONETARY_UNIT,
1069 fnd_global.user_id,
1070 fnd_global.login_id,
1071 REPORTING_CURRENCY,
1073 CURR_FIN_ST_DATE,
1074 REPORTING_PERIOD,
1075 CASH,
1076 ACCOUNTS_PAYABLE,
1077 NET_RECEIVABLES,
1078 SHORT_TERM_DEBT,
1079 INVENTORIES,
1080 OTHER_CUR_LIABILITIES,
1081 OTHER_CUR_ASSETS,
1082 TOTAL_CUR_LIABILITIES,
1083 TOTAL_CUR_ASSETS,
1084 LONG_TERM_DEBT,
1085 NET_FIXED_ASSETS,
1086 OTHER_NON_CUR_LIABILITIES,
1087 OTHER_NON_CUR_ASSETS,
1088 TOTAL_LIABILITIES,
1089 TOTAL_ASSETS,
1090 STOCKHOLDER_EQUITY,
1091 TOTAL_LIABILITIES_EQUITY,
1092 REVENUE,
1093 NON_OPERATING_INCOME,
1094 COST_OF_GOODS_SOLD,
1095 NON_OPERATING_EXPENSES,
1096 SGA_EXPENSES,
1097 PRE_TAX_NET_INCOME,
1098 OPERATING_INCOME,
1099 INCOME_TAXES,
1100 OPERATING_MARGIN,
1101 NET_INCOME,
1102 EARNINGS_PER_SHARE,
1103 nvl(l_case_folders_id,-99),
1104 PARTY_ID,
1105 CUST_ACCOUNT_ID,
1106 SITE_USE_ID
1107 FROM AR_CMGT_FINANCIAL_DATA
1108 WHERE CREDIT_REQUEST_ID = p_parnt_credit_req_id);
1109
1110 EXCEPTION
1111 WHEN OTHERS
1112 THEN
1113 p_errmsg := 'Error while populating Financial Data '||sqlerrm;
1114 p_resultout := 1;
1115 END;
1116 END IF;
1117 END DUPLICATE_FINANCIAL_DATA;
1118
1119 /*--This procedure creates duplicate record for Trade references for appeal and re-submit
1120 ------------------------------------------------------------------------------------------*/
1121 PROCEDURE DUPLICATE_TRADE_DATA(
1122 p_parnt_credit_req_id IN NUMBER ,
1123 p_credit_request_id IN NUMBER ,
1124 p_errmsg OUT NOCOPY VARCHAR2,
1125 p_resultout OUT NOCOPY VARCHAR2
1126 ) IS
1127 l_credit_request_id AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE;
1128 l_case_folders_id AR_CMGT_CASE_FOLDERS.CASE_FOLDER_ID%TYPE;
1129 l_processing_flag VARCHAR2(1);
1130 BEGIN
1131 l_credit_request_id := p_credit_request_id;
1132 p_resultout :=0;
1133 l_processing_flag := 'Y';
1134 --fetch the newly created case_folder_id.
1135 BEGIN
1136
1137 SELECT CASE_FOLDER_ID
1138 INTO l_case_folders_id
1139 FROM AR_CMGT_CASE_FOLDERS
1140 WHERE CREDIT_REQUEST_ID = p_credit_request_id;
1141
1142 EXCEPTION
1143 WHEN OTHERS
1144 THEN
1145 p_errmsg := 'Error while fetching Case folder ID '||sqlerrm;
1146 p_resultout := 1;
1147 l_processing_flag :='N';
1148 END;
1149 --duplicate trade ref data.
1150
1151 IF l_processing_flag = 'Y'
1152 THEN
1153
1154 BEGIN
1155
1156 INSERT INTO ar_cmgt_trade_ref_data
1157 (DATAPOINT_ID,
1158 CREDIT_REQUEST_ID,
1159 LAST_UPDATE_DATE,
1160 LAST_UPDATED_BY,
1161 CREATION_DATE,
1162 CREATED_BY,
1163 LAST_UPDATE_LOGIN,
1164 COUNTRY,
1165 TAX_NUMBER,
1166 REFERENCE_NAME,
1167 CONTACT_NAME,
1168 ADDRESS,
1169 PHONE_NUMBER,
1170 CITY,
1171 FAX_NUMBER,
1172 STATE,
1173 EMAIL,
1174 POSTAL_CODE,
1175 PROVINCE,
1176 URL,
1177 REPORT_DATE,
1178 LAST_TRANSACTION_DATE,
1179 NUMBERS_OF_YEARS_IN_TRADE,
1180 PAYMENT_TERMS,
1181 CREDIT_LIMIT,
1182 CURRENCY,
1183 AMOUNT_OWED,
1184 CREDIT_BALANCE,
1185 PAST_DUE_AMOUNT,
1186 INTERNAL_TRADE_RATING,
1187 CASE_FOLDER_ID,
1188 NOTES)
1189 (SELECT AR_CMGT_TRADE_REF_DATA_S.NEXTVAL,
1190 l_credit_request_id,
1191 SYSDATE,
1192 fnd_global.user_id,
1193 SYSDATE,
1194 fnd_global.user_id,
1195 fnd_global.login_id,
1196 COUNTRY,
1197 TAX_NUMBER,
1198 REFERENCE_NAME,
1199 CONTACT_NAME,
1200 ADDRESS,
1201 PHONE_NUMBER,
1205 EMAIL,
1202 CITY,
1203 FAX_NUMBER,
1204 STATE,
1206 POSTAL_CODE,
1207 PROVINCE,
1208 URL,
1209 REPORT_DATE,
1210 LAST_TRANSACTION_DATE,
1211 NUMBERS_OF_YEARS_IN_TRADE,
1212 PAYMENT_TERMS,
1213 CREDIT_LIMIT,
1214 CURRENCY,
1215 AMOUNT_OWED,
1216 CREDIT_BALANCE,
1217 PAST_DUE_AMOUNT,
1218 INTERNAL_TRADE_RATING,
1219 nvl(l_case_folders_id,-99),
1220 NOTES
1221 FROM AR_CMGT_TRADE_REF_DATA
1222 WHERE CREDIT_REQUEST_ID= p_parnt_credit_req_id);
1223
1224 EXCEPTION
1225 WHEN OTHERS
1226 THEN
1227 p_errmsg := 'Error while populating Trade Data '||sqlerrm;
1228 p_resultout := 1;
1229 END;
1230 END IF;
1231 END DUPLICATE_TRADE_DATA;
1232
1233 /*--This procedure creates duplicate record for bank accounts for appeal and re-submit
1234 ------------------------------------------------------------------------------------------*/
1235 PROCEDURE DUPLICATE_BANK_DATA(
1236 p_parnt_credit_req_id IN NUMBER ,
1237 p_credit_request_id IN NUMBER ,
1238 p_errmsg OUT NOCOPY VARCHAR2,
1239 p_resultout OUT NOCOPY VARCHAR2
1240 ) IS
1241 l_credit_request_id AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE;
1242 l_case_folders_id AR_CMGT_CASE_FOLDERS.CASE_FOLDER_ID%TYPE;
1243 l_data_point_id ar_cmgt_bank_ref_data.DATAPOINT_ID%TYPE;
1244 l_next_val ar_cmgt_bank_ref_data.DATAPOINT_ID%TYPE;
1245 l_processing_flag VARCHAR2(1);
1246
1247 CURSOR bank_data IS
1248 SELECT DATAPOINT_ID
1249 FROM ar_cmgt_bank_ref_data
1250 WHERE CREDIT_REQUEST_ID = p_parnt_credit_req_id;
1251
1252 BEGIN
1253 l_credit_request_id := p_credit_request_id;
1254 p_resultout :=0;
1255 l_processing_flag := 'Y';
1256 --fetch the newly created case_folder_id.
1257 BEGIN
1258
1259 SELECT CASE_FOLDER_ID
1260 INTO l_case_folders_id
1261 FROM AR_CMGT_CASE_FOLDERS
1262 WHERE CREDIT_REQUEST_ID = p_credit_request_id;
1263
1264 EXCEPTION
1265 WHEN OTHERS
1266 THEN
1267 p_errmsg := 'Error while fetching Case folder ID '||sqlerrm;
1268 p_resultout := 1;
1269 l_processing_flag :='N';
1270 END;
1271 --duplicate bank ref data.
1272
1273 IF l_processing_flag = 'Y'
1274 THEN
1275
1276 BEGIN
1277
1278 FOR bank_data_rec IN bank_data
1279 LOOP
1280
1281 BEGIN
1282
1283 l_data_point_id := bank_data_rec.DATAPOINT_ID;
1284
1285 SELECT AR_CMGT_BANK_REF_DATA_S.NEXTVAL
1286 INTO l_next_val FROM dual;
1287
1288 INSERT INTO ar_cmgt_bank_ref_data
1289 (DATAPOINT_ID,
1290 CREDIT_REQUEST_ID,
1291 LAST_UPDATE_DATE,
1292 LAST_UPDATED_BY,
1293 CREATION_DATE,
1294 CREATED_BY,
1295 LAST_UPDATE_LOGIN,
1296 BANK_NAME,
1297 ADDRESS,
1298 CITY,
1299 STATE,
1300 POSTAL_CODE,
1301 PROVINCE,
1302 COUNTRY,
1303 CONTACT_NAME,
1304 PHONE,
1305 FAX,
1306 EMAIL,
1307 URL,
1308 BANK_ROUTING_NUMBER,
1309 CASE_FOLDER_ID,
1310 NOTES)
1311 (SELECT l_next_val,
1312 l_credit_request_id,
1313 SYSDATE,
1314 fnd_global.user_id,
1315 SYSDATE,
1316 fnd_global.user_id,
1317 fnd_global.login_id,
1318 BANK_NAME,
1319 ADDRESS,
1320 CITY,
1321 STATE,
1322 POSTAL_CODE,
1323 PROVINCE,
1324 COUNTRY,
1325 CONTACT_NAME,
1326 PHONE,
1327 FAX,
1328 EMAIL,
1329 URL,
1330 BANK_ROUTING_NUMBER,
1331 nvl(l_case_folders_id,-99),
1332 NOTES
1333 FROM ar_cmgt_bank_ref_data
1334 WHERE CREDIT_REQUEST_ID = p_parnt_credit_req_id
1335 AND DATAPOINT_ID = l_data_point_id);
1336
1337 -- duplicate the account details.
1338
1339
1340 INSERT INTO AR_CMGT_BANK_REF_ACCTS
1341 (BANK_REFERENCE_ACCOUNT_ID,
1342 DATAPOINT_ID,
1343 CREDIT_REQUEST_ID,
1347 CREATED_BY,
1344 LAST_UPDATE_DATE,
1345 LAST_UPDATED_BY,
1346 CREATION_DATE,
1348 LAST_UPDATE_LOGIN,
1349 ACCOUNT_NUMBER,
1350 ACCOUNT_TYPE,
1351 DATE_OPENED,
1352 CURRENCY,
1353 CURRENT_BALANCE,
1354 BALANCE_DATE,
1355 AVERAGE_BALANCE)
1356 ( SELECT AR_CMGT_BANK_REF_ACCTS_S.NEXTVAL,
1357 l_next_val,
1358 l_credit_request_id,
1359 SYSDATE,
1360 fnd_global.user_id,
1361 SYSDATE,
1362 fnd_global.user_id,
1363 fnd_global.login_id,
1364 ACCOUNT_NUMBER,
1365 ACCOUNT_TYPE,
1366 DATE_OPENED,
1367 CURRENCY,
1368 CURRENT_BALANCE,
1369 BALANCE_DATE,
1370 AVERAGE_BALANCE
1371 FROM AR_CMGT_BANK_REF_ACCTS
1372 WHERE CREDIT_REQUEST_ID= p_parnt_credit_req_id
1373 AND DATAPOINT_ID = l_data_point_id);
1374
1375
1376 EXCEPTION
1377 WHEN OTHERS
1378 THEN
1379 p_errmsg := 'Error while populating bank Account Data '||sqlerrm;
1380 p_resultout := 1;
1381 END;
1382 END LOOP;
1383 END;
1384 END IF;
1385 END DUPLICATE_BANK_DATA;
1386
1387 /*--This procedure creates duplicate record for collateral data for appeal and re-submit
1388 ------------------------------------------------------------------------------------------*/
1389 PROCEDURE DUPLICATE_COLLATERAL_DATA(
1390 p_parnt_credit_req_id IN NUMBER ,
1391 p_credit_request_id IN NUMBER ,
1392 p_errmsg OUT NOCOPY VARCHAR2,
1393 p_resultout OUT NOCOPY VARCHAR2
1394 ) IS
1395 l_credit_request_id AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE;
1396 l_case_folders_id AR_CMGT_CASE_FOLDERS.CASE_FOLDER_ID%TYPE;
1397 l_processing_flag VARCHAR2(1);
1398 BEGIN
1399
1400 l_credit_request_id := p_credit_request_id;
1401 p_resultout :=0;
1402 l_processing_flag := 'Y';
1403
1404 --fetch the newly created case_folder_id.
1405 BEGIN
1406
1407 SELECT CASE_FOLDER_ID
1408 INTO l_case_folders_id
1409 FROM AR_CMGT_CASE_FOLDERS
1410 WHERE CREDIT_REQUEST_ID = p_credit_request_id;
1411
1412 EXCEPTION
1413 WHEN OTHERS
1414 THEN
1415 p_errmsg := 'Error while fetching Case folder ID '||sqlerrm;
1416 p_resultout := 1;
1417 l_processing_flag :='N';
1418 END;
1419 --duplicate trade ref data.
1420
1421 IF l_processing_flag = 'Y'
1422 THEN
1423 BEGIN
1424
1425 INSERT INTO ar_cmgt_collateral_data
1426 (DATAPOINT_ID,
1427 CREDIT_REQUEST_ID,
1428 LAST_UPDATE_DATE,
1429 LAST_UPDATED_BY,
1430 CREATION_DATE,
1431 CREATED_BY,
1432 LAST_UPDATE_LOGIN,
1433 COLLATERAL_DESCRIPTION,
1434 COLLATERAL_CATEGORY,
1435 COLLATERAL_VALUE,
1436 COLLATERAL_CURRENCY,
1437 VALUATION_TYPE,
1438 VALUATION_DATE,
1439 PREV_VALUATION_DATE,
1440 APPRAISER_NAME,
1441 APPRAISER_PHONE_NUMBER,
1442 COLLATERAL_LOCATION,
1443 CASE_FOLDER_ID,
1444 NOTES)
1445 ( SELECT AR_CMGT_COLLATERAL_DATA_S.NEXTVAL,
1446 l_credit_request_id,
1447 SYSDATE,
1448 fnd_global.user_id,
1449 SYSDATE,
1450 fnd_global.user_id,
1451 fnd_global.login_id,
1452 COLLATERAL_DESCRIPTION,
1453 COLLATERAL_CATEGORY,
1454 COLLATERAL_VALUE,
1455 COLLATERAL_CURRENCY,
1456 VALUATION_TYPE,
1457 VALUATION_DATE,
1458 PREV_VALUATION_DATE,
1459 APPRAISER_NAME,
1460 APPRAISER_PHONE_NUMBER,
1461 COLLATERAL_LOCATION,
1462 nvl(l_case_folders_id,-99),
1463 NOTES
1464 FROM ar_cmgt_collateral_data
1465 WHERE CREDIT_REQUEST_ID = p_parnt_credit_req_id);
1466
1467 EXCEPTION
1468 WHEN OTHERS
1469 THEN
1470 p_errmsg := 'Error while populating Collateral Data '||sqlerrm;
1471 p_resultout := 1;
1472
1473 END;
1474 END IF;
1475 END DUPLICATE_COLLATERAL_DATA;
1476
1477 /*--This procedure creates duplicate record for other data for appeal and re-submit
1481 p_credit_request_id IN NUMBER ,
1478 ------------------------------------------------------------------------------------------*/
1479 PROCEDURE DUPLICATE_OTHER_DATA(
1480 p_parnt_credit_req_id IN NUMBER ,
1482 p_errmsg OUT NOCOPY VARCHAR2,
1483 p_resultout OUT NOCOPY VARCHAR2
1484 ) IS
1485 l_credit_request_id AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE;
1486 l_case_folders_id AR_CMGT_CASE_FOLDERS.CASE_FOLDER_ID%TYPE;
1487 l_processing_flag VARCHAR2(1);
1488 BEGIN
1489
1490 l_credit_request_id := p_credit_request_id;
1491 p_resultout :=0;
1492 l_processing_flag := 'Y';
1493
1494 --fetch the newly created case_folder_id.
1495 BEGIN
1496
1497 SELECT CASE_FOLDER_ID
1498 INTO l_case_folders_id
1499 FROM AR_CMGT_CASE_FOLDERS
1500 WHERE CREDIT_REQUEST_ID = p_credit_request_id;
1501
1502 EXCEPTION
1503 WHEN OTHERS
1504 THEN
1505 p_errmsg := 'Error while fetching Case folder ID '||sqlerrm;
1506 p_resultout := 1;
1507 l_processing_flag :='N';
1508 END;
1509 --duplicate trade ref data.
1510
1511 IF l_processing_flag = 'Y'
1512 THEN
1513 BEGIN
1514
1515 INSERT INTO ar_cmgt_other_data
1516 (DATAPOINT_ID,
1517 CREDIT_REQUEST_ID,
1518 LAST_UPDATE_DATE,
1519 LAST_UPDATED_BY,
1520 CREATION_DATE,
1521 CREATED_BY,
1522 LAST_UPDATE_LOGIN,
1523 COUNTRY,
1524 KEY_EXECUTIVE,
1525 VENTURE_CAPITAL_NAME,
1526 VC_CONTACT_NAME,
1527 VC_ADDRESS,
1528 VC_CONTACT_PHONE,
1529 VC_STATE,
1530 VC_CONTACT_FAX,
1531 VC_POSTAL_CODE,
1532 VC_PROVINCE,
1533 VC_CONTACT_EMAIL,
1534 CAPITAL_STAGE_COMPLETED,
1535 CURRENCY,
1536 FUNDING_AMOUNT,
1537 PERCENT_INVESTED,
1538 BURN_RATE,
1539 FUTURE_FUNDING_PLANS,
1540 NOTES,
1541 CASE_FOLDER_ID)
1542 ( SELECT AR_CMGT_OTHER_DATA_S.NEXTVAL,
1543 l_credit_request_id,
1544 SYSDATE,
1545 fnd_global.user_id,
1546 SYSDATE,
1547 fnd_global.user_id,
1548 fnd_global.login_id,
1549 COUNTRY,
1550 KEY_EXECUTIVE,
1551 VENTURE_CAPITAL_NAME,
1552 VC_CONTACT_NAME,
1553 VC_ADDRESS,
1554 VC_CONTACT_PHONE,
1555 VC_STATE,
1556 VC_CONTACT_FAX,
1557 VC_POSTAL_CODE,
1558 VC_PROVINCE,
1559 VC_CONTACT_EMAIL,
1560 CAPITAL_STAGE_COMPLETED,
1561 CURRENCY,
1562 FUNDING_AMOUNT,
1563 PERCENT_INVESTED,
1564 BURN_RATE,
1565 FUTURE_FUNDING_PLANS,
1566 NOTES,
1567 nvl(l_case_folders_id,-99)
1568 FROM ar_cmgt_other_data
1569 WHERE CREDIT_REQUEST_ID = p_parnt_credit_req_id);
1570
1571 EXCEPTION
1572 WHEN OTHERS
1573 THEN
1574 p_errmsg := 'Error while populating other Data '||sqlerrm;
1575 p_resultout := 1;
1576
1577 END;
1578 END IF;
1579 END DUPLICATE_OTHER_DATA;
1580
1581 /*--This procedure creates duplicate record for recommendations for appeal and re-submit
1582 ------------------------------------------------------------------------------------------*/
1583 PROCEDURE DUPLICATE_RECO_DATA(
1584 p_parnt_case_folder_id IN NUMBER ,
1585 p_credit_request_id IN NUMBER ,
1586 p_errmsg OUT NOCOPY VARCHAR2,
1587 p_resultout OUT NOCOPY VARCHAR2
1588 ) IS
1589 l_credit_request_id AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE;
1590 l_case_folders_id AR_CMGT_CASE_FOLDERS.CASE_FOLDER_ID%TYPE;
1591 l_processing_flag VARCHAR2(1);
1592 BEGIN
1593
1594 l_credit_request_id := p_credit_request_id;
1595 p_resultout :=0;
1596 l_processing_flag := 'Y';
1597
1598 --fetch the newly created case_folder_id.
1599 BEGIN
1600
1601 SELECT CASE_FOLDER_ID
1602 INTO l_case_folders_id
1603 FROM AR_CMGT_CASE_FOLDERS
1604 WHERE CREDIT_REQUEST_ID = p_credit_request_id;
1605
1606 EXCEPTION
1607 WHEN OTHERS
1608 THEN
1609 p_errmsg := 'Error while fetching Case folder ID '||sqlerrm;
1610 p_resultout := 1;
1611 l_processing_flag :='N';
1612 END;
1616 THEN
1613 --duplicate trade ref data.
1614
1615 IF l_processing_flag = 'Y'
1617 BEGIN
1618 INSERT INTO AR_CMGT_CF_RECOMMENDS
1619 (RECOMMENDATION_ID,
1620 LAST_UPDATED_BY,
1621 LAST_UPDATE_DATE,
1622 LAST_UPDATE_LOGIN,
1623 CREATION_DATE,
1624 CREATED_BY,
1625 CASE_FOLDER_ID,
1626 CREDIT_REQUEST_ID,
1627 CREDIT_REVIEW_DATE,
1628 CREDIT_RECOMMENDATION,
1629 RECOMMENDATION_VALUE1,
1630 RECOMMENDATION_VALUE2,
1631 STATUS,
1632 CREDIT_TYPE,
1633 RECOMMENDATION_NAME,
1634 APPLICATION_ID)
1635 (SELECT AR_CMGT_CF_RECOMMENDS_S.NEXTVAL,
1636 fnd_global.user_id,
1637 SYSDATE,
1638 fnd_global.login_id,
1639 SYSDATE,
1640 fnd_global.user_id,
1641 l_case_folders_id,
1642 l_credit_request_id,
1643 CREDIT_REVIEW_DATE,
1644 CREDIT_RECOMMENDATION,
1645 RECOMMENDATION_VALUE1,
1646 RECOMMENDATION_VALUE2,
1647 STATUS,
1648 CREDIT_TYPE,
1649 RECOMMENDATION_NAME,
1650 APPLICATION_ID
1651 FROM AR_CMGT_CF_RECOMMENDS
1652 WHERE CASE_FOLDER_ID= p_parnt_case_folder_id);
1653 EXCEPTION
1654 WHEN OTHERS
1655 THEN
1656 p_errmsg := 'Error while populating Recommendation Data '||sqlerrm;
1657 p_resultout := 1;
1658
1659 END;
1660 END IF;
1661 END DUPLICATE_RECO_DATA;
1662
1663
1664 /*--This procedure creates duplicate record for analysis NOTES for appeal and re-submit
1665 ------------------------------------------------------------------------------------------*/
1666 PROCEDURE DUPLICATE_NOTES_DATA(
1667 p_parnt_case_folder_id IN NUMBER ,
1668 p_credit_request_id IN NUMBER ,
1669 p_errmsg OUT NOCOPY VARCHAR2,
1670 p_resultout OUT NOCOPY VARCHAR2
1671 ) IS
1672 l_credit_request_id AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE;
1673 l_case_folders_id AR_CMGT_CASE_FOLDERS.CASE_FOLDER_ID%TYPE;
1674 l_processing_flag VARCHAR2(1);
1675 BEGIN
1676
1677 p_resultout :=0;
1678 l_processing_flag := 'Y';
1679 --fetch the newly created case_folder_id.
1680 BEGIN
1681
1682 SELECT CASE_FOLDER_ID
1683 INTO l_case_folders_id
1684 FROM AR_CMGT_CASE_FOLDERS
1685 WHERE CREDIT_REQUEST_ID = p_credit_request_id;
1686
1687 EXCEPTION
1688 WHEN OTHERS
1689 THEN
1690 p_errmsg := 'Error while fetching Case folder ID '||sqlerrm;
1691 p_resultout := 1;
1692 l_processing_flag :='N';
1693 END;
1694 --duplicate NOTES data.
1695 IF l_processing_flag = 'Y'
1696 THEN
1697
1698 BEGIN
1699 INSERT INTO AR_CMGT_CF_ANL_NOTES
1700 (ANALYSIS_NOTES_ID,
1701 CASE_FOLDER_ID,
1702 LAST_UPDATED_BY,
1703 LAST_UPDATE_DATE,
1704 LAST_UPDATE_LOGIN,
1705 CREATION_DATE,
1706 CREATED_BY,
1707 TOPIC,
1708 DISPLAY,
1709 IMPORTANCE,
1710 NOTES,
1711 DATE_OPENED)
1712 (SELECT AR_CMGT_CF_ANL_NOTES_S.NEXTVAL,
1713 l_case_folders_id,
1714 fnd_global.user_id,
1715 sysdate,
1716 fnd_global.login_id,
1717 sysdate,
1718 fnd_global.user_id,
1719 TOPIC,
1720 DISPLAY,
1721 IMPORTANCE,
1722 NOTES,
1723 DATE_OPENED
1724 FROM AR_CMGT_CF_ANL_NOTES
1725 WHERE CASE_FOLDER_ID= p_parnt_case_folder_id);
1726 EXCEPTION
1727 WHEN OTHERS
1728 THEN
1729 p_errmsg := 'Error while populating Analysis Notes Data '||sqlerrm;
1730 p_resultout := 1;
1731 END;
1732 END IF;
1733 END DUPLICATE_NOTES_DATA;
1734
1735 PROCEDURE UPDATE_CASEFOLDER_DETAILS(
1736 P_DATA_POINT_ID IN NUMBER,
1737 P_CASE_FOLDER_ID IN NUMBER,
1738 P_RESULT OUT NOCOPY NUMBER) IS
1739 BEGIN
1740 --initialization
1741 P_RESULT:=0;
1742 BEGIN
1743 --update the included in checklist flag to "Y"
1744
1745 update ar_cmgt_cf_dtls
1746 set included_in_checklist = 'Y'
1747 where case_folder_id=P_CASE_FOLDER_ID
1748 and (data_point_id=P_DATA_POINT_ID
1749 OR PARENT_DATA_POINT_ID =P_DATA_POINT_ID);
1750
1751 EXCEPTION
1752
1753 WHEN NO_DATA_FOUND
1754 THEN
1755 P_RESULT := 1;
1756 return;
1760 WHEN OTHERS THEN
1757 WHEN TOO_MANY_ROWS
1758 THEN
1759 NULL;
1761 P_RESULT := 1;
1762 return;
1763 END;
1764 END;
1765
1766 PROCEDURE UPDATE_CF_DETAILS_NEGATION(
1767 P_DATA_POINT_ID IN NUMBER,
1768 P_CASE_FOLDER_ID IN NUMBER,
1769 P_RESULT OUT NOCOPY NUMBER) IS
1770 BEGIN
1771 --initialization
1772 P_RESULT:=0;
1773 BEGIN
1774 --update the included in checklist flag to "Y"
1775
1776 update ar_cmgt_cf_dtls
1777 set included_in_checklist = 'N'
1778 where case_folder_id=P_CASE_FOLDER_ID
1779 and data_point_id=P_DATA_POINT_ID;
1780
1781 EXCEPTION
1782
1783 WHEN NO_DATA_FOUND
1784 THEN
1785 P_RESULT := 1;
1786 return;
1787 WHEN TOO_MANY_ROWS
1788 THEN
1789 NULL;
1790 WHEN OTHERS THEN
1791 P_RESULT := 1;
1792 return;
1793 END;
1794 END;
1795
1796
1797 END AR_CMGT_CONTROLS;