1 PACKAGE BODY iby_factor_pkg AS
2 /*$Header: ibyfactb.pls 115.15 2002/11/18 23:02:25 jleybovi ship $*/
3
4 /*
5 ** Procedure: save_PaymentAmount
6 ** Purpose: Saves the PaymentAmount factor configuration into
7 ** database. Checks if payeeid is null or not, if it is
8 ** null then updates the site level configuration values
9 ** otherwise checks if payee already has some entries.
10 ** if payee has entries then updates them otherwise
11 ** creates new entries.
12 */
13 procedure save_PaymentAmount( i_payeeid in VARCHAR2,
14 i_name in VARCHAR2,
15 i_description in VARCHAR2,
16 i_count in integer,
17 i_amountRanges in AmountRange_table )
18 is
19
20 i int;
21 l_lowerLimit number;
22 l_upperLimit number;
23 l_score varchar2(100);
24 l_seq int;
25
26 begin
27
28 -- Assumption, Ranges are verified in Java Code it self.
29
30
31 -- initialize the values.
32
33 -- if payeeid is not null check if payee has any entries in
34 -- table or not. If not present then create insert statment
35 -- else update statement.
36
37 -- loop through the list of ranges passed and update
38 -- the database.
39 -- delete all the entries first.
40
41 delete from iby_irf_pmt_amount
42 where ((payeeid = i_payeeid)
43 or (i_payeeid is null and payeeid is null));
44
45
46 i := 1;
47 while ( i <= i_count ) loop
48
49 -- extract the values fromt the database.
50 l_lowerLimit := i_amountRanges(i).LowAmtLmt;
51 l_upperLimit := i_amountRanges(i).UprAmtLmt;
52 l_seq := i_amountRanges(i).Seq;
53 l_score := i_amountRanges(i).score;
54 i := i+1;
55
56 insert into iby_irf_pmt_amount
57 (lower_limit, upper_limit, score, seq,
58 payeeid, object_version_number,
59 last_update_date, last_updated_by, creation_date, created_by)
60 values ( l_lowerLimit, l_upperLimit, l_score, l_seq,
61 i_payeeid,1,
62 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
63
64 /*
65 -- if no rows were created then raise the exception.
66 if ( SQL%ROWCOUNT = 0 ) then
67 -- raise application error for the range it has failed.
68 raise_application_error(-20000, 'IBY_204200#' ||
69 'LOWERLIMIT='|| l_lowerLimit ||
70 '#UPPERLIMIT='||l_upperLimit|| '#');
71 end if;
72 */
73 end loop;
74
75 -- commit the changes;
76 commit;
77
78 end;
79
80 /*
81 ** Procedure: load_PaymentAmount
82 ** Purpose: loads the PaymentAmount factor configuration into
83 ** output parameters. Checks if payeeid is null or not, if it is
84 ** null then loads the site level configuration values
85 ** otherwise checks if payee already has some entries.
86 ** if payee has entries then loads them otherwise
87 ** creates new entries.
88 */
89 procedure load_PaymentAmount( i_payeeid in VARCHAR2,
90 o_name out nocopy VARCHAR2,
91 o_description out nocopy VARCHAR2,
92 o_amountRanges out nocopy AmountRange_table )
93 is
94
95 l_cnt int;
96 l_payeeid varchar2(80);
97
98 cursor c_load_factor is
99 select meaning, description
100 from FND_LOOKUP_VALUES
101 where
102 lookup_code = 'PMTAMOUNT' and
103 lookup_type = 'IBY_RISK_FACTOR_NAME' and
104 language = userenv('LANG');
105 --and
106 --security_group_id = fnd_global.lookup_security_group
107 --(lookup_type,view_application_id);
108
109 cursor c_load_ranges(ci_payeeid VARCHAR2) is
110 select lower_limit, upper_limit, seq, score
111 from iby_irf_pmt_amount
112 where (( payeeid is null and ci_payeeid is null ) or
113 ( payeeid = ci_payeeid))
114 order by seq;
115
116 cursor c_payee_range_count(ci_payeeid VARCHAR2) is
117 select count(*)
118 from iby_irf_pmt_amount
119 where payeeid = ci_payeeid;
120
121 begin
122
123 -- if payeeid is not null and there are no rows for the
124 -- payeeid passed in the database for this factor then
125 -- need to load the default values. Default value rows
126 -- will be identified by null payeeid values.
127
128 l_payeeid := i_payeeid;
129 if ( i_payeeid is not null ) then
130 if ( c_payee_range_count%isopen ) then
131 close c_payee_range_count;
132 end if;
133 open c_payee_range_count(i_payeeid);
134 fetch c_payee_range_count into l_cnt;
135 close c_payee_range_count;
136 -- payee does not have any configured information.
137 -- default values must be retrieved.
138 if ( l_cnt = 0) then
139 l_payeeid := null;
140 end if;
141 end if;
142
143 -- close the cursors, if they are already open.
144 if ( c_load_factor%isopen ) then
145 close c_load_factor;
146 end if;
147
148 if ( c_load_ranges%isopen ) then
149 close c_load_ranges;
150 end if;
151
152 open c_load_factor;
153
154 -- load the factor information.
155 fetch c_load_factor into o_name, o_description;
156
157 -- if no factor found then raise the application error.
158 if ( c_load_factor%notfound ) then
159 raise_application_error(-20000,'IBY_204201#');
160 end if;
161
162 l_cnt := 1;
163
164 -- load all the ranges for this factor.
165
166 for i in c_load_ranges(l_payeeid) loop
167 o_amountRanges(l_cnt).lowAmtLmt := i.lower_limit;
168 o_amountRanges(l_cnt).uprAmtLmt := i.upper_limit;
169 o_amountRanges(l_cnt).score := i.score;
170 o_amountRanges(l_cnt).seq := i.seq;
171 l_cnt := l_cnt + 1;
172 end loop;
173
174 close c_load_factor;
175
176 end;
177
178 /*
179 ** Procedure: save_TimeOfPurchase
180 ** Purpose: Saves the TimeOfPurchase factor configuration into
181 ** database. Checks if payeeid is null or not, if it is
182 ** null then updates the site level configuration values
183 ** otherwise checks if payee already has some entries.
184 ** if payee has entries then updates them otherwise
185 ** creates new entries.
186 */
187 procedure save_TimeOfPurchase( i_payeeid in VARCHAR2,
188 i_name in VARCHAR2,
189 i_description in VARCHAR2,
190 i_count in integer,
191 i_timeRanges in TimeRange_table )
192 is
193
194 i int;
195 l_lowerlimit integer;
196 l_upperlimit integer;
197 l_score varchar2(100);
198 l_seq int;
199
200 begin
201
202 -- Assumption, Ranges are verified in Java Code it self.
203
204
205 -- initialize the values.
206 i := 1;
207
208 -- loop through the list of ranges passed and update
209 -- the database.
210
211 -- delete all the ranges
212 delete from iby_irf_timeof_purchase
213 where ( ( payeeid = i_payeeid ) or
214 ( i_payeeid is null and payeeid is null ) );
215
216 while ( i <= i_count ) loop
217
218 -- extract the values fromt the database.
219 l_lowerLimit := i_timeRanges(i).LowTimeLmt;
220 l_upperLimit := i_timeRanges(i).UprTimeLmt;
221 l_seq := i_timeRanges(i).Seq;
222 l_score := i_timeRanges(i).score;
223 i := i+1;
224
225 -- insert the ranges into database based on the sequence.
226 insert into iby_irf_timeof_purchase
227 ( duration_from ,duration_to, score, seq,
228 payeeid , object_version_number,
229 last_update_date, last_updated_by, creation_date, created_by)
230 values ( l_lowerLimit, l_upperLimit, l_score, l_seq,
231 i_payeeid,1,
232 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
233
234 -- if inserted number of rows is zero then raise an exception.
235 /*
236 if ( SQL%ROWCOUNT = 0 ) then
237 -- raise application error
238 raise_application_error(-20000, 'IBY_204202#' ||
239 'LOWERLIMIT=' || l_lowerLimit ||
240 '#UPPERLIMIT='|| l_upperLimit || '#');
241 end if;
242 */
243 end loop;
244 -- commit the changes;
245 commit;
246
247 end;
248
249 /*
250 ** Procedure: load_TimeOfPurchase
251 ** Purpose: loads the TimeOfPurchase factor configuration into
252 ** output parameters. Checks if payeeid is null or not, if it is
253 ** null then loads the site level configuration values
254 ** otherwise checks if payee already has some entries.
255 ** if payee has entries then loads them otherwise
256 ** loads the site level entries.
257 */
258 procedure load_TimeOfPurchase( i_payeeid in VARCHAR2,
259 o_name out nocopy VARCHAR2,
260 o_description out nocopy VARCHAR2,
261 o_timeRanges out nocopy TimeRange_table )
262 is
263
264 l_cnt integer;
265 l_duration_to integer;
266 l_duration_from integer;
267 l_score varchar2(100);
268 l_payeeid varchar2(80);
269
270 cursor c_load_factor is
271 select meaning, description
272 from FND_LOOKUP_VALUES
273 where
274 lookup_code = 'TIMEOFPURCHASE' and
275 lookup_type = 'IBY_RISK_FACTOR_NAME' and
276 language = userenv('LANG');
277 --and
278 --security_group_id = fnd_global.lookup_security_group
279 --(lookup_type,view_application_id);
280
281
282
283 cursor c_load_ranges(ci_payeeid VARCHAR2) is
284 select duration_from, duration_to, seq, score
285 from iby_irf_timeof_purchase
286 where (( payeeid is null and ci_payeeid is null ) or
287 ( payeeid = ci_payeeid))
288 order by seq;
289
290 cursor c_ranges_count(ci_payeeid VARCHAR2) is
291 select count(*)
292 from iby_irf_timeof_purchase
293 where payeeid = ci_payeeid;
294
295 begin
296
297 -- check whether payee id is not null or not. If payeeid is
298 -- not null then check whether the payeeid has any information
299 -- configured. if not then default values should be loaded.
300 -- For default values payeeid will be null.
301 l_payeeid := i_payeeid;
302 if ( i_payeeid is not null ) then
303 if ( c_ranges_count%isopen ) then
304 close c_ranges_count;
305 end if;
306 open c_ranges_count(i_payeeid);
307 fetch c_ranges_count into l_cnt;
308 close c_ranges_count;
309 -- if no rows are present, to load default values set payeeid
310 -- to null.
311 if ( l_cnt = 0) then
312 l_payeeid := null;
313 end if;
314 end if;
315
316 -- close all cursors if they are open.
317 if ( c_load_factor%isopen ) then
318 close c_load_factor;
319 end if;
320
321 if ( c_load_ranges%isopen ) then
322 close c_load_ranges;
323 end if;
324
325 -- load factor information.
326 open c_load_factor;
327
328 fetch c_load_factor into o_name, o_description;
329
330 -- if factor information is not present then raise the
331 -- exception.
332 if ( c_load_factor%notfound ) then
333 raise_application_error(-20000,'IBY_204201#');
334 end if;
335
336 l_cnt := 1;
337
338 -- load time ranges.
339 for i in c_load_ranges(l_payeeid) loop
340 o_timeranges(l_cnt).lowTimeLmt := i.duration_from;
341 o_timeranges(l_cnt).uprTimeLmt := i.duration_to;
342 o_timeranges(l_cnt).score := i.score;
343 o_timeranges(l_cnt).seq := i.seq;
344 l_cnt := l_cnt + 1;
345 end loop;
346
347 -- close the cursors.
348 close c_load_factor;
349
350 end;
351
352 /*
353 ** Procedure: save_TrxnAmountLimit
354 ** Purpose: Saves the TrxnAmountLimit factor configuration into
355 ** database. Checks if payeeid is null or not, if it is
356 ** null then updates the site level configuration values
357 ** otherwise checks if payee already has some entries.
358 ** if payee has entries then updates them otherwise
359 ** creates new entries.
360 */
361 procedure save_TrxnAmountLimit( i_payeeid in VARCHAR2,
362 i_name in VARCHAR2,
363 i_description in VARCHAR2,
364 i_duration in integer,
365 i_durationType in VARCHAR2,
366 i_amount in number )
367 is
368 begin
369
370 -- update the transaction amount table.
371 -- this will be successful either payeeid is
372 -- null or the payeeid was configured some information.
373 update iby_irf_trxn_amt_limit
374 set duration = i_duration,
375 duration_type = i_durationType,
376 amount = i_amount,
377 last_update_date = sysdate,
378 last_updated_by = fnd_global.user_id
379 where (( payeeid is null and i_payeeid is null ) or
380 ( payeeid = i_payeeid));
381
382 -- if there are no rows present insert rows into database.
383 -- this happens when save is called first time for certain
384 -- payeeid.
385 if ( SQL%ROWCOUNT = 0 ) then
386 -- insert the information.
387 insert into iby_irf_trxn_amt_limit
388 ( duration, duration_type, amount,
389 payeeid, object_version_number,
390 last_update_date, last_updated_by, creation_date, created_by)
391 values (i_duration, i_durationType, i_amount,
392 i_payeeid,1,
393 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
394
395 /*
396 -- if information could not be saved then raise
397 -- application error.
398 if ( SQL%ROWCOUNT = 0 ) then
399 -- raise application error
400 raise_application_error(-20000, 'IBY_204204#');
401 end if;
402 */
403 end if;
404
405 -- commit the changes;
406 commit;
407 end;
408
409 /*
410 ** Procedure: load_TrxnAmountLimit
411 ** Purpose: loads the TrxnAmountLimit factor configuration into
412 ** output parameters. Checks if payeeid is null or not, if it is
413 ** null then loads the site level configuration values
414 ** otherwise checks if payee already has some entries.
415 ** if payee has entries then loads them otherwise
416 ** loads the site level entries.
417 */
418 procedure load_TrxnAmountLimit( i_payeeid in varchar2,
419 o_name out nocopy VARCHAR2,
420 o_description out nocopy VARCHAR2,
421 o_duration out nocopy integer,
422 o_durationType out nocopy VARCHAR2,
423 o_amount out nocopy number )
424 is
425
426 l_cnt integer;
427 l_payeeid varchar2(80);
428
429 cursor c_load_factor is
430 select meaning, description
431 from FND_LOOKUP_VALUES
432 where
433 lookup_code = 'TRXNAMOUNT' and
434 lookup_type = 'IBY_RISK_FACTOR_NAME' and
435 language = userenv('LANG');
436 --and
437 --security_group_id = fnd_global.lookup_security_group
438 --(lookup_type,view_application_id);
439
440
441 cursor c_trxn_amount_limit(ci_payeeid varchar2) is
442 select duration, duration_type, amount
443 from iby_irf_trxn_amt_limit
444 where (( payeeid is null and ci_payeeid is null ) or
445 ( payeeid = ci_payeeid));
446
447 cursor c_trxn_amount_count(ci_payeeid varchar2) is
448 select count(*)
449 from iby_irf_trxn_amt_limit
450 where payeeid = ci_payeeid;
451
452 begin
453
454 -- check whether payee id is not null or not. If payeeid is
455 -- not null then check whether the payeeid has any information
456 -- configured. if not then default values should be loaded.
457 -- For default values payeeid will be null.
458 l_payeeid := i_payeeid;
459 if ( i_payeeid is not null ) then
460 if ( c_trxn_amount_count%isopen ) then
461 close c_trxn_amount_count;
462 end if;
463 open c_trxn_amount_count(i_payeeid);
464 fetch c_trxn_amount_count into l_cnt;
465 close c_trxn_amount_count;
466 -- payee does not have any configured information.
467 -- to retrieve defaule information set payeeid to null.
468 if ( l_cnt = 0) then
469 l_payeeid := null;
470 end if;
471 end if;
472
473 -- close all the cursors.
474 if ( c_load_factor%isopen )then
475 close c_load_factor;
476 end if;
477
478 if ( c_trxn_amount_limit%isopen )then
479 close c_trxn_amount_limit;
480 end if;
481
482 open c_load_factor;
483 open c_trxn_amount_limit(l_payeeid);
484
485 fetch c_load_factor into o_name, o_description;
486
487 -- if factor information is not found then
488 -- raise the exception.
489 if ( c_load_factor%notfound ) then
490 raise_application_error(-20000,'IBY_204201#');
491 end if;
492
493 fetch c_trxn_amount_limit into o_duration, o_durationType, o_amount;
494
495 close c_trxn_amount_limit;
496 close c_load_factor;
497
498 end;
499
500 /*
501 ** Procedure: save_PaymentHistory
502 ** Purpose: Saves the PaymentHistory factor configuration into
503 ** database. Checks if payeeid is null or not, if it is
504 ** null then updates the site level configuration values
505 ** otherwise checks if payee already has some entries.
506 ** if payee has entries then updates them otherwise
507 ** creates new entries.
508 */
509 procedure save_PaymentHistory(i_payeeid in VARCHAR2,
510 i_name in VARCHAR2,
511 i_description in VARCHAR2,
512 i_duration in integer,
513 i_durationType in VARCHAR2,
514 i_count in integer,
515 i_freqRanges in FreqRange_table )
516 is
517
518 i int;
519 l_lowerLimit int;
520 l_upperLimit int;
521 l_score varchar2(100);
522 l_seq int;
523 l_pmt_hist_id int;
524
525 cursor c_pmtHistId(ci_payeeid varchar2) is
526 select id
527 from iby_irf_pmt_history
528 where (( payeeid is null and ci_payeeid is null ) or
529 ( payeeid = ci_payeeid));
530
531
532 begin
533
534 -- Assumption, Ranges are verified in Java Code it self.
535
536 -- Update the master table. If there are no rows to update
537 -- then insert the information. This happens only when
538 -- payeeid id is not null.
539
540 update iby_irf_pmt_history
541 set duration = i_duration,
542 duration_type = i_durationType,
543 last_update_date = sysdate,
544 last_updated_by = fnd_global.user_id
545 where (( payeeid is null and i_payeeid is null) or
546 ( payeeid = i_payeeid));
547
548 if ( SQL%ROWCOUNT = 0 ) then
549
550 -- insert a row in master table.
551 SELECT iby_irf_pmt_history_s.nextval into l_pmt_hist_id
552 FROM dual;
553
554 insert into iby_irf_pmt_history (id, duration, duration_type,
555 payeeid, object_version_number,
556 last_update_date, last_updated_by, creation_date, created_by)
557 values ( l_pmt_hist_id, i_duration, i_durationType, i_payeeid,
558 1, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
559
560 else
561 open c_pmtHistId(i_payeeId);
562 fetch c_pmtHistId into l_pmt_hist_id;
563 close c_pmtHistId;
564
565 end if;
566
567 -- initialize the values.
568 i := 1;
569
570 -- loop through the list of ranges passed and update
571 -- the database.
572 -- delete all the entries corresoonding to
573 -- l_pmt_hist_id and then insert the new ranges.
574
575 delete from iby_irf_pmt_hist_range
576 where payment_hist_id = l_pmt_hist_id;
577
578 while ( i <= i_count ) loop
579
580 -- extract the values fromt the database.
581 l_lowerLimit := i_freqRanges(i).LowFreqLmt;
582 l_upperLimit := i_freqRanges(i).UprFreqLmt;
583 l_seq := i_freqRanges(i).Seq;
584 l_score := i_freqRanges(i).score;
585 i := i+1;
586
587 -- insert the ranges into database based on the sequence.
588 insert into iby_irf_pmt_hist_range
589 ( payment_hist_id, frequency_low_range,
590 frequency_high_range, score, seq,
591 object_version_number,
592 last_update_date, last_updated_by,
593 creation_date, created_by)
594
595 values ( l_pmt_hist_id, l_lowerlimit, l_upperlimit,
596 l_score, l_seq,
597 1,
598 sysdate, fnd_global.user_id,
599 sysdate, fnd_global.user_id);
600
601 /*
602 -- if no data was inserted then raise the exceotion.
603 if ( SQL%ROWCOUNT = 0 ) then
604 -- raise application error
605 raise_application_error(-20000, 'IBY_204206#'
606 || 'LOWERLIMIT=' || l_lowerlimit
607 || '#UPPERLIMIT=' || l_upperLimit || '#');
608 end if;
609 */
610 end loop;
611
612 -- commit the changes;
613 commit;
614 end;
615
616 /*
617 ** Procedure: load_PaymentHistory
618 ** Purpose: loads the PaymentHistory factor configuration into
619 ** output parameters. Checks if payeeid is null or not, if it is
620 ** null then loads the site level configuration values
621 ** otherwise checks if payee already has some entries.
622 ** if payee has entries then loads them otherwise
623 ** loads the site level Payemnet History values.
624 */
625 procedure load_PaymentHistory(i_payeeid in VARCHAR2,
626 o_name out nocopy VARCHAR2,
627 o_description out nocopy VARCHAR2,
628 o_duration out nocopy integer,
629 o_durationType out nocopy VARCHAR2,
630 o_freqRanges out nocopy FreqRange_table )
631 is
632
633 l_cnt int;
634 l_pmt_hist_id int;
635 l_lowerLimit int;
636 l_upperLimit int;
637 l_score varchar2(100);
638 l_seq int;
639 l_payeeid varchar2(80);
640
641 cursor c_load_factor is
642 select meaning, description
643 from FND_LOOKUP_VALUES
644 where
645 lookup_code = 'PMTHISTORY' and
646 lookup_type = 'IBY_RISK_FACTOR_NAME' and
647 language = userenv('LANG');
648 --and
649 --security_group_id = fnd_global.lookup_security_group
650 --(lookup_type,view_application_id);
651
652
653 cursor c_load_pmt_history(ci_payeeid varchar2) is
654 select id, duration, duration_type
655 from iby_irf_pmt_history
656 where (( payeeid is null and ci_payeeid is null ) or
657 ( payeeid = ci_payeeid));
658
659 cursor c_load_ranges(ci_id integer) is
660 select frequency_low_range, frequency_high_range, score, seq
661 from iby_irf_pmt_hist_range
662 where payment_hist_id = ci_id
663 order by seq;
664
665 cursor c_pmt_history_count(ci_payeeid varchar2) is
666 select count(*)
667 from iby_irf_pmt_history
668 where payeeid = ci_payeeid;
669
670 begin
671
672 -- check whether payee id is not null or not. If payeeid is
673 -- not null then check whether the payeeid has any information
674 -- configured. if not then default values should be loaded.
675 -- For default values payeeid will be null.
676 l_payeeid := i_payeeid;
677 if ( i_payeeid is not null ) then
678 if ( c_pmt_history_count%isopen ) then
679 close c_pmt_history_count;
680 end if;
681 open c_pmt_history_count(i_payeeid);
682 fetch c_pmt_history_count into l_cnt;
683 close c_pmt_history_count;
684 if ( l_cnt = 0) then
685 l_payeeid := null;
686 end if;
687 end if;
688
689 -- close all the cursors if they are already open
690 if ( c_load_factor%isopen ) then
691 close c_load_factor;
692 end if;
693
694 if ( c_load_pmt_history%isopen ) then
695 close c_load_pmt_history;
696 end if;
697
698 if ( c_load_ranges%isopen ) then
699 close c_load_ranges;
700 end if;
701
702 open c_load_factor;
703 open c_load_pmt_history(l_payeeid);
704
705 fetch c_load_factor into o_name, o_description;
706
707 -- if factor information is not found then raise an exception.
708 --
709 if ( c_load_factor%notfound ) then
710 raise_application_error(-20000,'IBY_204201#');
711 end if;
712
713 -- fetch the master level payment history information.
714 fetch c_load_pmt_History into l_pmt_hist_id, o_duration, o_durationType;
715 l_cnt := 1;
716 -- load the purchase frequency ranges form payment history.
717 for i in c_load_ranges(l_pmt_hist_id) loop
718 o_freqRanges(l_cnt).lowFreqLmt := i.frequency_low_range;
719 o_freqRanges(l_cnt).uprFreqLmt := i.frequency_high_range;
720 o_freqRanges(l_cnt).score := i.score;
721 o_freqRanges(l_cnt).seq := i.seq;
722 l_cnt := l_cnt + 1;
723 end loop;
724
725 -- close all the cursors.
726 close c_load_factor;
727 close c_load_pmt_history;
728
729 end;
730
731 /*
732 ** Procedure: save_AVSCodes
733 ** Purpose: Saves the AVSCodes factor configuration into
734 ** database. Checks if payeeid is null or not, if it is
735 ** null then updates the site level configuration values
736 ** otherwise checks if payee already has some entries.
737 ** if payee has entries then updates them otherwise
738 ** creates new entries.
739 */
740 procedure save_AVSCodes( i_payeeid in VARCHAR2,
741 i_name in VARCHAR2,
742 i_description in VARCHAR2,
743 i_count in integer,
744 i_codes in codes_table )
745 is
746 i int;
747 l_score varchar2(100);
748 l_code IBY_MAPPINGS.MAPPING_CODE%TYPE;
749
750 begin
751
752 -- Assumption, Ranges are verified in Java Code it self.
753
754 -- initialize the values.
755 i := 1;
756 -- loop through the list of ranges passed and update
757 -- the database.
758 delete from iby_mappings
759 where (( payeeid = i_payeeid) or
760 ( payeeid is null and i_payeeid is null ))
761 and mapping_type = 'AVS_CODE_TYPE';
762
763 while ( i <= i_count ) loop
764
765 -- extract the values from the input and insert in database.
766 l_code := i_codes(i).code;
767 l_score := i_codes(i).score;
768 i := i+1;
769
770 insert into iby_mappings ( payeeid, mapping_type, mapping_code,
771 value, object_version_number,
772 last_update_date, last_updated_by, creation_date, created_by)
773 values ( i_payeeid, 'AVS_CODE_TYPE', l_code,
774 l_score,1,
775 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
776
777 /*
778 -- if no of rows inserted is zero then raise an
779 -- exception.
780 if ( SQL%ROWCOUNT = 0 ) then
781 -- raise application error
782 raise_application_error(-20000, 'IBY_204208#'||
783 'AVSCODE=' || l_code || '#');
784 end if;
785 */
786 end loop;
787
788 -- commit the changes;
789 commit;
790 end;
791
792 /*
793 ** Procedure: load_AVSCodes
794 ** Purpose: loads the AVSCodes factor configuration into
795 ** output parameters. Checks if payeeid is null or not, if it is
796 ** null then loads the site level configuration values
797 ** otherwise checks if payee already has some entries.
798 ** if payee has entries then loads them otherwise
799 ** loads AVSCodes of the site level.
800 */
801 procedure load_AVSCodes( i_payeeid in varchar2,
802 o_name out nocopy VARCHAR2,
803 o_description out nocopy VARCHAR2,
804 o_codes out nocopy codes_table )
805 is
806
807 l_cnt int;
808 l_payeeid varchar2(80);
809
810 cursor c_load_factor is
811 select meaning, description
812 from FND_LOOKUP_VALUES
813 where
814 lookup_code = 'AVSCODES' and
815 lookup_type = 'IBY_RISK_FACTOR_NAME' and
816 language = userenv('LANG');
817 --and
818 --security_group_id = fnd_global.lookup_security_group
819 --(lookup_type,view_application_id);
820
821
822 cursor c_avs_codes(ci_payeeid varchar2) is
823 select mapping_code, value
824 from iby_mappings
825 where mapping_type = 'AVS_CODE_TYPE'
826 and (( payeeid is null and ci_payeeid is null ) or
827 ( payeeid = ci_payeeid));
828
829 cursor c_avs_codes_count(ci_payeeid varchar2) is
830 select count(*)
831 from iby_mappings
832 where mapping_type = 'AVS_CODE_TYPE'
833 and payeeid = ci_payeeid;
834
835 begin
836
837 -- check whether payee id is not null or not. If payeeid is
838 -- not null then check whether the payeeid has any information
839 -- configured. if not then default values should be loaded.
840 -- For default values payeeid will be null.
841 l_payeeid := i_payeeid;
842 if ( l_payeeid is not null ) then
843 if ( c_avs_codes_count%isopen ) then
844 close c_avs_codes_count;
845 end if;
846 open c_avs_codes_count(i_payeeid);
847 fetch c_avs_codes_count into l_cnt;
848 close c_avs_codes_count;
849 -- if there are no AVS codes set then
850 -- to load default codes set payeeid to null.
851 if ( l_cnt = 0) then
852 l_payeeid := null;
853 end if;
854 end if;
855
856 if ( c_load_factor%isopen ) then
857 close c_load_factor;
858 end if;
859
860 if ( c_avs_codes%isopen ) then
861 close c_avs_codes;
862 end if;
863
864 open c_load_factor;
865
866 fetch c_load_factor into o_name, o_description;
867
868 -- if factor information is not found then raise
869 -- the exception.
870 if ( c_load_factor%notfound ) then
871 raise_application_error(-20000,'IBY_204201#');
872 end if;
873
874 l_cnt := 1;
875
876 -- load avs codes.
877 for i in c_avs_codes(l_payeeid) loop
878 o_codes(l_cnt).code := i.mapping_code;
879 o_codes(l_cnt).score := i.value;
880 l_cnt := l_cnt + 1;
881 end loop;
882
883 close c_load_factor;
884 -- close c_avs_codes;
885
886 end;
887
888 /*
889 ** Procedure: save_RiskCodes
890 ** Purpose: Saves the RiskCodes factor configuration into
891 ** database. Checks if payeeid is null or not, if it is
892 ** null then updates the site level configuration values
893 ** otherwise checks if payee already has some entries.
894 ** if payee has entries then updates them otherwise
895 ** creates new entries.
896 */
897 procedure save_RiskCodes( i_payeeid in VARCHAR2,
898 i_name in VARCHAR2,
899 i_description in VARCHAR2,
900 i_count in integer,
901 i_codes in codes_table )
902 is
903 i int;
904 l_score varchar2(100);
905 l_code IBY_MAPPINGS.MAPPING_CODE%TYPE;
906
907 begin
908
909 -- initialize the values.
910 i := 1;
911
912 -- loop through the list of ranges passed and update
913 -- the database.
914 -- delete the risk scodes and then insert;
915 delete from iby_mappings
916 where (( payeeid = i_payeeid) or
917 ( payeeid is null and i_payeeid is null ))
918 and mapping_type = 'RISK_CODE_TYPE';
919
920 while ( i <= i_count ) loop
921 -- extract the values from the input and insert in database.
922 l_code := i_codes(i).code;
923 l_score := i_codes(i).score;
924 i := i+1;
925
926 insert into iby_mappings ( payeeid, mapping_type, mapping_code, value,
927 last_update_date, last_updated_by, creation_date, created_by,object_version_number)
928 values ( i_payeeid, 'RISK_CODE_TYPE', l_code, l_score,
929 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,1);
930
931 /*
932 if ( SQL%ROWCOUNT = 0 ) then
933 -- raise application error
934 raise_application_error(-20000, 'Unable insert RISK CODES ');
935 end if;
936 */
937 end loop;
938 commit;
939
940 end save_riskcodes;
941
942 /*
943 ** Procedure: load_RiskCodes
944 ** Purpose: loads the RiskCodes factor configuration into
945 ** output parameters. Checks if payeeid is null or not, if it is
946 ** null then loads the site level configuration values
947 ** otherwise checks if payee already has some entries.
948 ** if payee has entries then loads them otherwise
949 ** loads the sitelevel entries.
950 */
951 procedure load_RiskCodes( i_payeeid in VARCHAR2,
952 o_name out nocopy VARCHAR2,
953 o_description out nocopy VARCHAR2,
954 o_codes out nocopy codes_table )
955 is
956 l_cnt int;
957
958 l_payeeid varchar2(80);
959
960 cursor c_load_factor is
961 select meaning, description
962 from FND_LOOKUP_VALUES
963 where
964 lookup_code = 'RISKCODES' and
965 lookup_type = 'IBY_RISK_FACTOR_NAME' and
966 language = userenv('LANG');
967 --and
968 --security_group_id = fnd_global.lookup_security_group
969 --(lookup_type,view_application_id);
970
971
972 cursor c_risk_codes(ci_payeeid varchar2) is
973 select mapping_code lookup_code,value
974 from iby_mappings
975 where mapping_type = 'RISK_CODE_TYPE' and
976 payeeid = ci_payeeid
977 UNION
978 select lookup_code,null
979 from fnd_lookup_values
980 where lookup_type = 'RISK_CODE' and
981 enabled_flag = 'Y' and
982 language = userenv('LANG') and
983
984 --security_group_id = fnd_global.lookup_security_group
985 --(lookup_type,view_application_id) and
986
987 lookup_code not in ( select mapping_code
988 from iby_mappings
989 where mapping_type = 'RISK_CODE_TYPE' and
990 payeeid = ci_payeeid);
991
992
993 cursor c_risk_codes2 is
994 select lookup_code
995 from
996 fnd_lookup_values
997 where
998 lookup_type = 'RISK_CODE' and
999 enabled_flag = 'Y' and
1000 language = userenv('LANG');
1001 --and
1002 --security_group_id = fnd_global.lookup_security_group
1003 --(lookup_type,view_application_id);
1004
1005
1006
1007 cursor c_del_risk_codes is
1008 select lookup_code
1009 from iby_mappings a,
1010 fnd_lookup_values b
1011 where b.lookup_type = 'RISK_CODE' and
1012 b.enabled_flag = 'N' and
1013 b.lookup_code = a.mapping_code and
1014 b.language = userenv('LANG');
1015 --and
1016 --b.security_group_id = fnd_global.lookup_security_group
1017 --(b.lookup_type,b.view_application_id);
1018
1019 --del_code c_del_risk_codes%ROWTYPE;
1020
1021 cursor c_risk_codes_count(ci_payeeid varchar2) is
1022 select count(*)
1023 from iby_mappings
1024 where mapping_type = 'RISK_CODE_TYPE' and
1025 payeeid = ci_payeeid;
1026 begin
1027 --dbms_output.put_line(i_payeeid);
1028
1029 l_payeeid := i_payeeid;
1030 if (c_del_risk_codes%isopen) then
1031 close c_del_risk_codes;
1032 end if;
1033
1034
1035 for i in c_del_risk_codes
1036 loop
1037 --dbms_output.put_line('inside delete loop');
1038 delete from iby_mappings
1039 where mapping_type = 'RISK_CODE_TYPE' and
1040 mapping_code = i.lookup_code;
1041 end loop;
1042 if (l_payeeid is not null) then
1043
1044 if (c_risk_codes_count%isopen) then
1045 close c_risk_codes_count;
1046 end if;
1047 open c_risk_codes_count(i_payeeid);
1048 fetch c_risk_codes_count into l_cnt;
1049 close c_risk_codes_count;
1050 if (l_cnt = 0) then
1051 l_payeeid := null;
1052 end if;
1053
1054 end if;
1055
1056 if (c_load_factor%isopen) then
1057 close c_load_factor;
1058 end if;
1059
1060 if (c_risk_codes%isopen) then
1061 close c_risk_codes;
1062 end if;
1063
1064 open c_load_factor;
1065
1066 fetch c_load_factor into o_name, o_description;
1067
1068 if (c_load_factor%notfound) then
1069 raise_application_error(-20000,'IBY_204201#');
1070 end if;
1071 if (l_payeeid is not null) then
1072 l_cnt := 1;
1073
1074 for i in c_risk_codes(l_payeeid) loop
1075 o_codes(l_cnt).code := i.lookup_code;
1076 o_codes(l_cnt).score := i.value;
1077 l_cnt := l_cnt + 1;
1078 end loop;
1079 else
1080 l_cnt := 1;
1081
1082 for i in c_risk_codes2 loop
1083 o_codes(l_cnt).code := i.lookup_code;
1084 o_codes(l_cnt).score := 'NR';
1085 l_cnt := l_cnt + 1;
1086 end loop;
1087 end if;
1088 --dbms_output.put_line('l_cnt = '||to_char(l_cnt));
1089 close c_load_factor;
1090 --close c_risk_codes;
1091
1092 end;
1093
1094 /*
1095 ** Procedure: save_CreditRatingCodes
1096 ** Purpose: Saves the CreditRatingCodes factor configuration into
1097 ** database. Checks if payeeid is null or not, if it is
1098 ** null then updates the site level configuration values
1099 ** otherwise checks if payee already has some entries.
1100 ** if payee has entries then updates them otherwise
1101 ** creates new entries.
1102 */
1103 procedure save_CreditRatingCodes( i_payeeid in VARCHAR2,
1104 i_name in VARCHAR2,
1105 i_description in VARCHAR2,
1106 i_count in integer,
1107 i_codes in codes_table )
1108 is
1109 i int;
1110 l_score varchar2(100);
1111 l_code IBY_MAPPINGS.MAPPING_CODE%TYPE;
1112
1113 begin
1114
1115 -- Assumption, Ranges are verified in Java Code it self.
1116
1117 -- initialize the values.
1118 i := 1;
1119
1120 -- loop through the list of ranges passed and update
1121 -- the database.
1122 -- delete the existing config and insert new data.
1123 delete from iby_mappings
1124 where (( payeeid = i_payeeid) or
1125 ( payeeid is null and i_payeeid is null ))
1126 and mapping_type = 'CREDIT_CODE_TYPE';
1127
1128 while ( i <= i_count ) loop
1129
1130 -- extract the values from the input and insert in database.
1131 l_code := i_codes(i).code;
1132 l_score := i_codes(i).score;
1133 i := i+1;
1134 insert into iby_mappings ( payeeid, mapping_type, mapping_code, value,
1135 last_update_date, last_updated_by, creation_date, created_by,object_version_number)
1136 values ( i_payeeid, 'CREDIT_CODE_TYPE', l_code, l_score,
1137 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,1);
1138
1139 /*
1140 if ( SQL%ROWCOUNT = 0 ) then
1141 -- raise application error
1142 raise_application_error(-20000, 'Unable insert Credit Codes');
1143 end if;
1144 */
1145 end loop;
1146 commit;
1147 end;
1148
1149 /*
1150 ** Procedure: load_CreditRatingCodes
1151 ** Purpose: loads the CreditRatingCodes factor configuration into
1152 ** output parameters. Checks if payeeid is null or not, if it is
1153 ** null then loads the site level configuration values
1154 ** otherwise checks if payee already has some entries.
1155 ** if payee has entries then loads them otherwise
1156 ** loads new entries.
1157 */
1158 procedure load_CreditRatingCodes( i_payeeid in VARCHAR2,
1159 o_name out nocopy VARCHAR2,
1160 o_description out nocopy VARCHAR2,
1161 o_codes out nocopy codes_table )
1162 is
1163
1164 l_cnt int;
1165 l_payeeid varchar2(80);
1166
1167
1168 cursor c_load_factor is
1169 select meaning, description
1170 from FND_LOOKUP_VALUES
1171 where
1172 lookup_code = 'CREDITRATINGCODES' and
1173 lookup_type = 'IBY_RISK_FACTOR_NAME' and
1174 language = userenv('LANG');
1175 --and
1176 --security_group_id = fnd_global.lookup_security_group
1177 --(lookup_type,view_application_id);
1178
1179
1180 cursor c_creditrating_codes(ci_payeeid varchar2) is
1181 select mapping_code lookup_code,value
1182 from iby_mappings
1183 where mapping_type = 'CREDIT_CODE_TYPE' and
1184 payeeid = ci_payeeid
1185 UNION
1186 select lookup_code,null
1187 from fnd_lookup_values
1188 where lookup_type = 'CREDIT_RATING' and
1189 enabled_flag = 'Y' and
1190 language = userenv('LANG') and
1191
1192 --security_group_id = fnd_global.lookup_security_group
1193 --(lookup_type,view_application_id) and
1194
1195 lookup_code not in ( select mapping_code
1196 from iby_mappings
1197 where mapping_type = 'CREDIT_CODE_TYPE' and
1198 payeeid = ci_payeeid);
1199
1200
1201 cursor c_creditrating_codes2 is
1202 select lookup_code
1203 from
1204 fnd_lookup_values
1205 where
1206 lookup_type = 'CREDIT_RATING' and
1207 enabled_flag = 'Y' and
1208 language = userenv('LANG');
1209 --and
1210 --security_group_id = fnd_global.lookup_security_group
1211 --(lookup_type,view_application_id);
1212
1213 cursor c_del_creditrating_codes is
1214 select lookup_code
1215 from iby_mappings a,
1216 fnd_lookup_values b
1217 where b.lookup_type = 'CREDIT_RATING' and
1218 b.enabled_flag = 'N' and
1219 b.lookup_code = a.mapping_code and
1220 b.language = userenv('LANG');
1221 --and
1222 --b.security_group_id = fnd_global.lookup_security_group
1223 --(b.lookup_type,b.view_application_id);
1224
1225
1226 cursor c_creditrating_codes_count(ci_payeeid varchar2) is
1227 select count(*)
1228 from iby_mappings
1229 where mapping_type = 'CREDIT_CODE_TYPE' and
1230 payeeid = ci_payeeid;
1231
1232 begin
1233 --dbms_output.put_line(i_payeeid);
1234 l_payeeid := i_payeeid;
1235 if (c_del_creditrating_codes%isopen) then
1236 close c_del_creditrating_codes;
1237 end if;
1238
1239 for i in c_del_creditrating_codes
1240 loop
1241 delete from iby_mappings
1242 where mapping_type = 'CREDIT_CODE_TYPE' and
1243 mapping_code = i.lookup_code;
1244 end loop;
1245 if (l_payeeid is not null) then
1246
1247 if (c_creditrating_codes_count%isopen) then
1248 close c_creditrating_codes_count;
1249 end if;
1250 open c_creditrating_codes_count(i_payeeid);
1251 fetch c_creditrating_codes_count into l_cnt;
1252 close c_creditrating_codes_count;
1253 if (l_cnt = 0) then
1254 l_payeeid := null;
1255 end if;
1256 end if;
1257
1258 if (c_load_factor%isopen) then
1259 close c_load_factor;
1260 end if;
1261
1262 if (c_creditrating_codes%isopen) then
1263 close c_creditrating_codes;
1264 end if;
1265
1266 open c_load_factor;
1267
1268 fetch c_load_factor into o_name, o_description;
1269
1270 if (c_load_factor%notfound) then
1271 raise_application_error(-20000,'IBY_204201#');
1272 end if;
1273 if (l_payeeid is not null) then
1274 l_cnt := 1;
1275
1276 for i in c_creditrating_codes(l_payeeid) loop
1277 o_codes(l_cnt).code := i.lookup_code;
1278 o_codes(l_cnt).score := i.value;
1279 l_cnt := l_cnt + 1;
1280 end loop;
1281 else
1282 l_cnt := 1;
1283
1284 for i in c_creditrating_codes2 loop
1285 o_codes(l_cnt).code := i.lookup_code;
1286 o_codes(l_cnt).score := 'S';
1287 l_cnt := l_cnt + 1;
1288 end loop;
1289 end if;
1290 close c_load_factor;
1291 -- close c_creditrating_codes;
1292
1293 end;
1294
1295 /*
1296 ** Procedure: save_FreqOfPurchase
1297 ** Purpose: Saves the FreqOfPurchase factor configuration into
1298 ** database. Checks if payeeid is null or not, if it is
1299 ** null then updates the site level configuration values
1300 ** otherwise checks if payee already has some entries.
1301 ** if payee has entries then updates them otherwise
1302 ** creates new entries.
1303 */
1304 procedure save_FreqOfPurchase(i_payeeid in VARCHAR2,
1305 i_name in VARCHAR2,
1306 i_description in VARCHAR2,
1307 i_duration in integer,
1308 i_durationType in VARCHAR2,
1309 i_frequency in integer )
1310 is
1311 begin
1312
1313 -- update the FreqOfPurchase information. If payeeid is not null
1314 -- and payeeid does not have configured information then
1315 -- insert the configuration information.
1316 update iby_irf_pmt_frequency
1317 set duration = i_duration,
1318 duration_type = i_durationType,
1319 frequency = i_frequency,
1320 last_update_date = sysdate,
1321 last_updated_by = fnd_global.user_id
1322 where ( ( payeeid is null and i_payeeid is null ) or
1323 ( payeeid = i_payeeid ));
1324
1325 -- if no data configured.
1326 if ( SQL%ROWCOUNT = 0 ) then
1327 -- insert the data.
1328 insert into iby_irf_pmt_frequency ( duration, duration_type,
1329 frequency, payeeid, object_version_number,
1330 last_update_date, last_updated_by, creation_date, created_by)
1331 values ( i_duration, i_durationType, i_frequency, i_payeeid,1,
1332 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
1333 /*
1334 -- if it could not insert the rows.
1335 if ( SQL%ROWCOUNT = 0 ) then
1336 -- raise application error
1337 raise_application_error(-20000, 'IBY_204215#');
1338 end if;
1339 */
1340 end if;
1341
1342 -- commit the changes;
1343 commit;
1344
1345 end;
1346
1347 /*
1348 ** Procedure: load_FreqOfPurchase
1349 ** Purpose: loads the FreqOfPurchase factor configuration into
1350 ** output parameters. Checks if payeeid is null or not, if it is
1351 ** null then loads the site level configuration values
1352 ** otherwise checks if payee already has some entries.
1353 ** if payee has entries then loads them otherwise
1354 ** creates new entries.
1355 */
1356 procedure load_FreqOfPurchase(i_payeeid in VARCHAR2,
1357 o_name out nocopy VARCHAR2,
1358 o_description out nocopy VARCHAR2,
1359 o_duration out nocopy integer,
1360 o_durationType out nocopy VARCHAR2,
1361 o_frequency out nocopy integer )
1362 is
1363
1364 l_payeeid varchar2(80);
1365 l_cnt integer;
1366
1367 cursor c_load_factor is
1368 select meaning, description
1369 from FND_LOOKUP_VALUES
1370 where
1371 lookup_code = 'FREQOFPURCHASE' and
1372 lookup_type = 'IBY_RISK_FACTOR_NAME' and
1373 language = userenv('LANG');
1374 --and
1375 --security_group_id = fnd_global.lookup_security_group
1376 --(lookup_type,view_application_id);
1377
1378
1379 cursor c_pmt_freq(ci_payeeid varchar2) is
1380 select duration, duration_type, frequency
1381 from iby_irf_pmt_frequency
1382 where (( payeeid is null and ci_payeeid is null ) or
1383 ( payeeid = ci_payeeid));
1384
1385 cursor c_pmt_freq_count(ci_payeeid varchar2) is
1386 select count(*)
1387 from iby_irf_pmt_frequency
1388 where payeeid = ci_payeeid;
1389
1390 begin
1391
1392 -- check whether payee id is not null or not. If payeeid is
1393 -- not null then check whether the payeeid has any information
1394 -- configured. if not then default values should be loaded.
1395 -- For default values payeeid will be null.
1396 l_payeeid := i_payeeid;
1397 if ( l_payeeid is not null ) then
1398 if ( c_pmt_freq_count%isopen ) then
1399 close c_pmt_freq;
1400 end if;
1401 open c_pmt_freq_count(i_payeeid);
1402 fetch c_pmt_freq_count into l_cnt;
1403 close c_pmt_freq_count;
1404 -- set it to null if payeeid does not have any configured
1405 -- information.
1406 if ( l_cnt = 0) then
1407 l_payeeid := null;
1408 end if;
1409 end if;
1410
1411
1412 -- close all the open cursors, if any.
1413 if ( c_load_factor%isopen )then
1414 close c_load_factor;
1415 end if;
1416
1417 if ( c_pmt_freq%isopen )then
1418 close c_pmt_freq;
1419 end if;
1420
1421 -- open cursors.
1422 open c_load_factor;
1423 open c_pmt_freq(l_payeeid);
1424
1425 -- load the factor information.
1426 fetch c_load_factor into o_name, o_description;
1427 -- if factor information is not present then raise exception.
1428 if ( c_load_factor%notfound ) then
1429 raise_application_error(-20000,'IBY_204201#');
1430 end if;
1431 -- load the factor configured data.
1432 fetch c_pmt_freq into o_duration, o_durationType, o_frequency;
1433
1434 -- close the cursors.
1435 close c_pmt_freq;
1436 close c_load_factor;
1437
1438 end;
1439
1440 /*
1441 ** Procedure: save_RiskScores
1442 ** Purpose: Saves the RiskScores information into
1443 ** database. Checks if payeeid is null or not, if it is
1444 ** null then updates the site level RiskScores values
1445 ** otherwise checks if payee already has some entries.
1446 ** if payee has entries then updates them otherwise
1447 ** creates new entries.
1448 */
1449 procedure save_RiskScores( i_payeeid in VARCHAR2,
1450 i_lowval in integer,
1451 i_lowMedVal in integer,
1452 i_medVal in integer,
1453 i_medHighVal in integer,
1454 i_highVal in integer )
1455 is
1456 begin
1457
1458 -- update the risk scores based on the payeeid.
1459 update iby_mappings
1460 set value = i_lowVal,
1461 last_update_date = sysdate,
1462 last_updated_by = fnd_global.user_id
1463 where mapping_code = 'L'
1464 and mapping_type = 'IBY_RISK_SCORE_TYPE'
1465 and (( payeeid is null and i_payeeid is null ) or
1466 (payeeid = i_payeeid) );
1467
1468 -- if count is zero then insert new rows for all the scores.
1469 -- otherwise update the other risk Score rows.
1470 if ( SQL%ROWCOUNT = 0 ) then
1471 insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
1472 last_update_date, last_updated_by, creation_date, created_by, object_version_number)
1473 values( 0, 'S', 'IBY_RISK_SCORE_TYPE', i_payeeid,
1474 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
1475
1476 insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
1477 last_update_date, last_updated_by, creation_date, created_by, object_version_number)
1478 values( 0, 'NR', 'IBY_RISK_SCORE_TYPE', i_payeeid,
1479 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
1480
1481 insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
1482 last_update_date, last_updated_by, creation_date, created_by, object_version_number)
1483 values( i_lowVal, 'L', 'IBY_RISK_SCORE_TYPE', i_payeeid,
1484 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
1485
1486 insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
1487 last_update_date, last_updated_by, creation_date, created_by, object_version_number)
1488 values( i_lowMedVal, 'LM', 'IBY_RISK_SCORE_TYPE', i_payeeid,
1489 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
1490
1491 insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
1492 last_update_date, last_updated_by, creation_date, created_by, object_version_number)
1493 values( i_medVal, 'M', 'IBY_RISK_SCORE_TYPE', i_payeeid,
1494 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
1495
1496 insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
1497 last_update_date, last_updated_by, creation_date, created_by, object_version_number)
1498 values( i_medHighVal, 'MH', 'IBY_RISK_SCORE_TYPE', i_payeeid,
1499 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
1500
1501 insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
1502 last_update_date, last_updated_by, creation_date, created_by, object_version_number)
1503 values( i_highVal, 'H', 'IBY_RISK_SCORE_TYPE', i_payeeid,
1504 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
1505 else
1506
1507 update iby_mappings
1508 set value = i_lowMedVal,
1509 last_update_date = sysdate,
1510 last_updated_by = fnd_global.user_id
1511 where mapping_code = 'LM'
1512 and mapping_type = 'IBY_RISK_SCORE_TYPE'
1513 and (( payeeid is null and i_payeeid is null ) or
1514 (payeeid = i_payeeid) );
1515
1516 update iby_mappings
1517 set value = i_medVal,
1518 last_update_date = sysdate,
1519 last_updated_by = fnd_global.user_id
1520 where mapping_code = 'M'
1521 and mapping_type = 'IBY_RISK_SCORE_TYPE'
1522 and (( payeeid is null and i_payeeid is null ) or
1523 (payeeid = i_payeeid) );
1524
1525 update iby_mappings
1526 set value = i_medHighVal,
1527 last_update_date = sysdate,
1528 last_updated_by = fnd_global.user_id
1529 where mapping_code = 'MH'
1530 and mapping_type = 'IBY_RISK_SCORE_TYPE'
1531 and (( payeeid is null and i_payeeid is null ) or
1532 (payeeid = i_payeeid) );
1533
1534 update iby_mappings
1535 set value = i_highVal,
1536 last_update_date = sysdate,
1537 last_updated_by = fnd_global.user_id
1538 where mapping_code = 'H'
1539 and mapping_type = 'IBY_RISK_SCORE_TYPE'
1540 and (( payeeid is null and i_payeeid is null ) or
1541 (payeeid = i_payeeid) );
1542 end if;
1543
1544 -- commit the changes;
1545 commit;
1546 end;
1547
1548 /*
1549 ** Procedure: load_RiskScores
1550 ** Purpose: loads the RiskScores information into
1551 ** output parameters. Checks if payeeid is null or not, if it is
1552 ** null then loads the site level RiskScore values
1553 ** otherwise checks if payee already has some entries.
1554 ** if payee has entries then loads them otherwise
1555 ** retrieves new entries.
1556 */
1557 procedure load_RiskScores( i_payeeid in VARCHAR2,
1558 o_lowval out nocopy integer,
1559 o_lowMedVal out nocopy integer,
1560 o_medVal out nocopy integer,
1561 o_medHighVal out nocopy integer,
1562 o_highVal out nocopy integer )
1563 is
1564
1565 l_payeeid varchar2(80);
1566 l_cnt integer;
1567
1568 cursor c_insert_scores( ci_code in iby_mappings.mapping_code%type,
1569 ci_payeeid varchar2)
1570 is
1571 select value
1572 from iby_mappings
1573 where mapping_code = ci_code
1574 and mapping_type = 'IBY_RISK_SCORE_TYPE'
1575 and (( payeeid is null and ci_payeeid is null ) or
1576 ( payeeid = ci_payeeid));
1577
1578 cursor c_payee_scores_count(ci_payeeid varchar2)
1579 is
1580 select count(*)
1581 from iby_mappings
1582 where mapping_type = 'IBY_RISK_SCORE_TYPE'
1583 and payeeid = ci_payeeid;
1584
1585 begin
1586
1587 -- check whether payee id is not null or not. If payeeid is
1588 -- not null then check whether the payeeid has any information
1589 -- configured. if not then default values should be loaded.
1590 -- For default values payeeid will be null.
1591 l_payeeid := i_payeeid;
1592 if ( i_payeeid is not null ) then
1593 if ( c_payee_scores_count%isopen ) then
1594 close c_payee_scores_count;
1595 end if;
1596 open c_payee_scores_count(i_payeeid);
1597 fetch c_payee_scores_count into l_cnt;
1598 close c_payee_scores_count;
1599 if ( l_cnt = 0) then
1600 l_payeeid := null;
1601 end if;
1602 end if;
1603
1604 if ( c_insert_scores%isopen ) then
1605 close c_insert_scores;
1606 end if;
1607
1608 -- retrieve risk scores for each different type.
1609 open c_insert_scores('L', l_payeeid);
1610 fetch c_insert_scores into o_lowVal;
1611 close c_insert_scores;
1612
1613 open c_insert_scores('LM', l_payeeid);
1614 fetch c_insert_scores into o_lowMedVal;
1615 close c_insert_scores;
1616
1617 open c_insert_scores('M', l_payeeid);
1618 fetch c_insert_scores into o_medVal;
1619 close c_insert_scores;
1620
1621 open c_insert_scores('MH', l_payeeid);
1622 fetch c_insert_scores into o_medHighVal;
1623 close c_insert_scores;
1624
1625 open c_insert_scores('H', l_payeeid);
1626 fetch c_insert_scores into o_highVal;
1627 close c_insert_scores;
1628
1629 end;
1630
1631
1632 end iby_factor_pkg;
1633
1634
[Home] [Help]