1 PACKAGE BODY iby_eval_ar_factors_pkg AS
2 /*$Header: ibyevarb.pls 115.5 2002/11/18 22:26:55 jleybovi ship $*/
3
4 /*
5 ** Procedure: eval_TrxnCreditLimit
6 ** Purpose: Evaluates the risk associated with Transaction Credit
7 ** Limit risk factor.
8 ** The transaction amount will be passed into this routine
9 ** along with the account number . Compare the
10 ** transaction credit limit set for this account with the
11 ** transaction amount and return the risk score.
12 */
13
14 procedure eval_TrxnCreditLimit(i_acctnumber in varchar2,
15 i_amount in number,
16 i_currency_code in varchar2,
17 i_payeeid in varchar2,
18 o_risk_score out nocopy number)
19 is
20
21 /*
22 ** Get the transaction credit limit set for the given account
23 */
24 cursor c_get_trxn_limit(ci_accountnumber varchar2,
25 ci_currency_code in varchar2) is
26 select p.trx_credit_limit
27 from hz_cust_accounts a,
28 hz_cust_profile_amts p
29 where a.account_number = ci_accountnumber and
30 a.status = 'A' and
31 p.currency_code = ci_currency_code and
32 p.site_use_id is null and
33 a.cust_account_id = p.cust_account_id;
34
35 l_trxnlimit number;
36 l_score varchar2(10);
37 begin
38 /*
39 ** close the cursor if it is already open.
40 */
41 if ( c_get_trxn_limit%isopen ) then
42 close c_get_trxn_limit;
43 end if;
44 --dbms_output.put_line('acct = '||i_acctnumber || 'currency = ' || i_currency_code);
45 /*
46 ** Raise an exception if accountnumber is null
47 */
48 if (i_acctnumber is null) then
49 raise_application_error(-20000,'IBY_204250#',FALSE);
50 end if;
51 /*
52 ** open the cursor and check if the corresponding trxn_limit exists in the
53 ** database for that account.
54 */
55
56 open c_get_trxn_limit(i_acctnumber,i_currency_code);
57 fetch c_get_trxn_limit into l_trxnlimit;
58 --dbms_output.put_line('Transaction limit : '|| l_trxnlimit);
59 if ( l_trxnlimit is null or l_trxnlimit = 0) then
60 o_risk_score := 0;
61 else
62 /*compare trxncreditlimit with trxnamount*/
63 if i_amount <= l_trxnlimit then
64 o_risk_score := 0;
65 else
66 o_risk_score := iby_risk_scores_pkg.getscore(i_payeeid,'H');
67 end if;
68
69 end if;
70 end eval_TrxnCreditLimit;
71
72 /*
73 ** Procedure: eval_OverallCreditLimit
74 ** Purpose: Evaluates the risk associated with Overall Credit Limit risk factor.
75 ** The accountnumber will be passed into this routine
76 ** Based on the account number get the associated Overall Credit Limit
77 ** and compare it with the overall balance .
78 ** Overall Balance is the amount due remaining for all the open
79 ** transactions of that account
80 */
81
82 Procedure eval_OverallCreditLimit(i_accountnumber in varchar2,
83 i_amount in number,
84 i_currency_code in varchar2,
85 i_payeeid in varchar2,
86 o_risk_score out nocopy number)
87 is
88
89 l_overall_credit_limit number;
90 l_customer_id number;
91 l_debit_balance number;
92 l_credit_balance number;
93 l_on_account_cash number;
94 l_unapplied_cash number;
95 l_ar_balance number;
96
97 l_score varchar2(10);
98 /*
99 ** Get the transaction credit limit set for the given account
100 */
101 cursor c_get_overall_credit_limit(ci_accountnumber in varchar2,
102 ci_currency_code in varchar2) is
103 select p.overall_credit_limit,a.party_id
104 from hz_cust_accounts a,
105 hz_cust_profile_amts p
106 where a.account_number = ci_accountnumber and
107 a.status = 'A' and
108 p.currency_code = ci_currency_code and
109 p.site_use_id is null and
110 a.cust_account_id = p.cust_account_id;
111 /*
112 ** Get the customer_id
113
114 cursor c_get_customer_id(ci_accountnumber in varchar2) is
115 select party_id
116 from hz_cust_accounts
117 where account_number = ci_accountnumber;
118 */
119
120 /*
121 ** Get the debit Balance
122 */
123 cursor c_get_debit_balance(ci_customer_id in number) is
124 select nvl(sum(amount_due_remaining),0)
125 from ar_payment_schedules_all
126 where customer_id = ci_customer_id and
127 class in ('DM','INV') and
128 status = 'OP';
129
130 /*
131 ** Get Credit Balance
132 */
133 cursor c_get_credit_balance(ci_customer_id in number) is
134 select nvl(sum(amount_due_remaining),0)
135 from ar_payment_schedules_all
136 where customer_id = ci_customer_id and
137 class in ('CM') and
138 status = 'OP';
139 /*
140 ** Get on account cash
141 */
142 cursor c_get_on_account_cash(ci_customer_id in number) is
143 select nvl(sum(decode(app.status,'ACC',(-1*app.amount_applied_from),0)),0)
144 from ar_receivable_applications_all app,
145 ar_payment_schedules_all ps,
146 gl_code_combinations cc
147 where ps.customer_id = ci_customer_id and
148 ps.cash_receipt_id = app.cash_receipt_id and
149 nvl(app.confirmed_flag ,'Y') = 'Y' and
150 app.status in ('ACC') and
151 app.code_combination_id = cc.code_combination_id;
152
153 /*
154 ** Get unapplied cash
155 */
156 cursor c_get_unapplied_cash(ci_customer_id in number) is
157 select nvl(sum(decode(app.status,'UNAPP',(-1*app.amount_applied_from),0)),0)
158 from ar_receivable_applications_all app,
159 ar_payment_schedules_all ps,
160 gl_code_combinations cc
161 where ps.customer_id = ci_customer_id and
162 ps.cash_receipt_id = app.cash_receipt_id and
163 nvl(app.confirmed_flag,'Y') = 'Y' and
164 app.status IN ('UNAPP') and
165 app.code_combination_id = cc.code_combination_id;
166
167 begin
168 /*
169 ** close the cursor if already open
170 */
171 if (c_get_overall_credit_limit%isopen) then
172 close c_get_overall_credit_limit;
173 end if;
174 --dbms_output.put_line('acct = '||i_accountnumber || 'currency = ' || i_currency_code);
175 /*
176 ** Raise an exception if accountnumber is null
177 */
178 if (i_accountnumber is null) then
179 raise_application_error(-20000,'IBY_20450#');
180 end if;
181
182 /*
183 ** open the cursor and get the overall credit limit for the account
184 */
185 open c_get_overall_credit_limit(i_accountnumber,i_currency_code);
186 fetch c_get_overall_credit_limit into l_overall_credit_limit,l_customer_id;
187 --dbms_output.put_line('ov_limit = '|| l_overall_credit_limit || 'cust = '||l_customer_id);
188 if (l_overall_credit_limit is null or l_overall_credit_limit = 0) then
189 o_risk_score := 0;
190 --raise_application_error(-20000,'IBY_204560#',FALSE);
191 else
192 /*
193 ** close the cursor if already open
194
195 if (c_get_customer_id%isopen) then
196 close c_get_customer_id;
197 end if;
198
199 ** open the cursor and get the customer_id corresponding to that account
200
201 open c_get_customer_id(i_accountnumber,i_org_id);
202 fetch c_get_customer_id into l_customer_id; */
203 /*
204 ** get debit balance for that customer
205 */
206 open c_get_debit_balance(l_customer_id);
207 fetch c_get_debit_balance into l_debit_balance;
208 --dbms_output.put_line('debit = '|| l_debit_balance);
209 if (c_get_debit_balance%notfound) then
210 l_debit_balance := 0;
211 end if;
212 /*
213 ** get credit balance for that customer
214 */
215 open c_get_credit_balance(l_customer_id);
216 fetch c_get_credit_balance into l_credit_balance;
217 --dbms_output.put_line('cred balance = ' || l_credit_balance);
218 if (c_get_credit_balance%notfound) then
219 l_credit_balance := 0;
220 end if;
221 /*
222 ** get on account cash for that customer
223 */
224 open c_get_on_account_cash(l_customer_id);
225 fetch c_get_on_account_cash into l_on_account_cash;
226 --dbms_output.put_line('account cash = '||l_on_account_cash);
227 if (c_get_on_account_cash%notfound) then
228 l_on_account_cash := 0;
229 end if;
230 /*
231 ** get unapplied cash for that customer
232 */
233 open c_get_unapplied_cash(l_customer_id);
234 fetch c_get_unapplied_cash into l_unapplied_cash;
235 --dbms_output.put_line('unapp cash = '||l_unapplied_cash);
236 if (c_get_unapplied_cash%notfound) then
237 l_unapplied_cash := 0;
238 end if;
239
240 /*
241 ** Compute the AR balance
242 */
243 l_ar_balance := nvl(l_debit_balance,0) + nvl(l_credit_balance,0) +
244 nvl(l_on_account_cash,0) + nvl(l_unapplied_cash,0);
245
246 --dbms_output.put_line('AR balance = '||l_ar_balance);
247 /*
248 ** Compare the AR balance with the Overall Credit Limit
249 ** and return the risk score.
250 */
251 if i_amount <= (l_overall_credit_limit - l_ar_balance) then
252 o_risk_score := 0;
253 else
254 o_risk_score := iby_risk_scores_pkg.getscore(i_payeeid,'H');
255 end if;
256 --dbms_output.put_line('risk score = '||o_risk_score);
257 close c_get_debit_balance;
258 close c_get_credit_balance;
259 close c_get_on_account_cash;
260 close c_get_unapplied_cash;
261 --close c_get_customer_id;
262
263 end if; /*overall_credit_limit found*/
264 close c_get_overall_credit_limit;
265
266
267 end eval_OverallCreditLimit; /* eval_OverallCreditLimit */
268
269
270
271 /*
272 ** Procedure: eval_CreditRatingCode
273 ** Purpose: Evaluates the risk associated with CreditRating Code risk factor.
274 ** The accountnumber will be passed into this routine
275 ** Based on the account number get the associated creditrating code
276 ** and compare the creditrating code with the creditratingcode mapping
277 ** stored in iby_mappings and return the appropriate risk score.
278 */
279
280 Procedure eval_CreditRatingCode(i_accountnumber in varchar2,
281 i_payeeid in varchar2,
282 o_risk_score out nocopy number)
283 is
284
285 l_creditratingcode varchar2(30);
286 l_risk_score_code varchar2(30);
287 l_cnt number;
288 l_payeeid varchar2(80);
289 /*
290 ** Get the creditrating code set up for the account
291 */
292 cursor c_get_creditratingcode(ci_accountnumber in varchar2) is
293 select p.credit_rating
294 from hz_cust_accounts a,
295 hz_customer_profiles p
296 where a.account_number = ci_accountnumber and
297 p.status = 'A' and
298 a.status = 'A' and
299 p.site_use_id is null and
300 a.cust_account_id = p.cust_account_id;
301 /*
302 ** Get the mapping creditrating_code from iby_mappings table
303 */
304 cursor c_get_mapping_code_value(ci_creditratingcode in varchar2, ci_payeeid in varchar2) is
305 select m.value
306 from iby_mappings m
307 where m.mapping_code = ci_creditratingcode and
308 m.mapping_type = 'CREDIT_CODE_TYPE' and
309 ((payeeid is null and ci_payeeid is null) or (m.payeeid = ci_payeeid));
310 begin
311 /*
312 ** close the cursor if it is already open.
313 */
314 if ( c_get_creditratingcode%isopen ) then
315 close c_get_creditratingcode;
316 end if;
317 /*
318 ** Raise an exception if accountnumber is null
319 */
320 if (i_accountnumber is null) then
321 raise_application_error(-20000,'IBY_20450#');
322 end if;
323
324 /*
325 ** open the cursor and check if the corresponding risk_code exists in the
326 ** database for that account.
327 */
328 open c_get_creditratingcode(i_accountnumber);
329 fetch c_get_creditratingcode into l_creditratingcode;
330 if (l_creditratingcode is null ) then
331 o_risk_score := 0;
332 else
333 /*
334 ** close the cursor if it is already open
335 */
336 if (c_get_mapping_code_value%isopen) then
337 close c_get_mapping_code_value;
338 end if;
339 /*
340 ** check whether this payeeid has any entry in for
341 ** creditrating codes.
342 ** if not then set payeeid to null
343 */
344 select count(1) into l_cnt
345 from iby_mappings
346 where mapping_type = 'CREDIT_CODE_TYPE'
347 and payeeid = i_payeeid;
348
349 if (l_cnt = 0) then
350 l_payeeid := null;
351 else
352 l_payeeid := i_payeeid;
353 end if;
354
355 /*
356 ** open the cursor and check if the corresponding risk_code exists in the
357 ** database
358 */
359 open c_get_mapping_code_value(l_creditratingcode, l_payeeid);
360 fetch c_get_mapping_code_value into l_risk_score_code;
361 -- if creditrating code is not present then assign norisk value
362 -- else get the corresponding value by calling
363 -- iby_risk_scores_pkg.getscore method.
364
365 if (c_get_mapping_code_value%notfound) then
366 o_risk_score := 0;
367 else
368 /*
369 ** get the riskscore value associated with the riskscore from iby_mappings
370 */
371 o_risk_score := iby_risk_scores_pkg.getscore(i_payeeid,l_risk_score_code);
372 end if;
373 close c_get_mapping_code_value;
374 close c_get_creditratingcode;
375 end if;
376 end eval_CreditRatingCode;
377
378 /*
379 ** Procedure: eval_RiskCode
380 ** Purpose: Evaluates the risk associated with Risk Code risk factor.
381 ** The accountnumber will be passed into this routine
382 ** Based on the account number get the associated risk code
383 ** and compare the riskcode with the riskcode mapping
384 ** stored in iby_mappings and return the appropriate risk score.
385 */
386
387 procedure eval_RiskCode(i_accountnumber in varchar2,
388 i_payeeid in varchar2,
389 o_risk_score out nocopy number)
390 is
391
392 l_riskcode varchar2(30);
393 l_riskscore_code varchar2(30);
394 l_cnt number;
395 l_payeeid varchar2(80);
396 /*
397 ** Get the risk code set up for the account
398 */
399 cursor c_get_riskcode(ci_accountnumber in varchar2) is
400 select p.risk_code
401 from hz_cust_accounts a,
402 hz_customer_profiles p
403 where a.account_number = ci_accountnumber and
404 p.status = 'A' and
405 a.status = 'A' and
406 p.site_use_id is null and
407 a.cust_account_id = p.cust_account_id;
408 /*
409 ** Get the mapping risk_code from iby_mappings table
410 */
411 cursor c_get_mapping_code_value(ci_riskcode in varchar2, ci_payeeid in varchar2) is
412 select m.value
413 from iby_mappings m
414 where m.mapping_code = ci_riskcode and
415 m.mapping_type = 'RISK_CODE_TYPE' and
416 ((m.payeeid is null and ci_payeeid is null) or (m.payeeid = ci_payeeid));
417
418 begin
419 /*
420 ** close the cursor if it is already open.
421 */
422 if ( c_get_riskcode%isopen ) then
423 close c_get_riskcode;
424 end if;
425 /*
426 ** Raise an exception if accountnumber is null
427 */
428 if (i_accountnumber is null) then
429 raise_application_error(-20000,'IBY_20450#');
430 end if;
431
432 /*
433 ** open the cursor and check if the corresponding risk_code exists in the
434 ** database for that account.
435 */
436 open c_get_riskcode(i_accountnumber);
437 fetch c_get_riskcode into l_riskcode;
438 if ( l_riskcode is null ) then
439 o_risk_score := 0;
440 else
441 /*
442 ** close the cursor if it is already open
443 */
444 if (c_get_mapping_code_value%isopen) then
445 close c_get_mapping_code_value;
446 end if;
447 /*
448 ** check whether this payeeid has any entry in
449 ** for RISKcodes.
450 ** if not the set payeeid to null.
451 */
452 select count(1) into l_cnt
453 from iby_mappings
454 where mapping_type = 'RISK_CODE_TYPE'
455 and payeeid = i_payeeid;
456
457 if (l_cnt = 0) then
458 l_payeeid := null;
459 else
460 l_payeeid := i_payeeid;
461 end if;
462 /*
463 ** open the cursor and check if the corresponding risk_code exists in the
464 ** database
465 */
466 open c_get_mapping_code_value(l_riskcode,i_payeeid);
467 fetch c_get_mapping_code_value into l_riskscore_code;
468 if (c_get_mapping_code_value%notfound) then
469 o_risk_score := 0;
470 else
471 /*
472 ** get the riskscore value associated with the riskscore from iby_mappings
473 */
474 o_risk_score := iby_risk_scores_pkg.getscore(i_payeeid, l_riskscore_code);
475 end if;
476
477 close c_get_mapping_code_value;
478 close c_get_riskcode;
479 end if;
480 end eval_RiskCode;
481 end iby_eval_ar_factors_pkg;
[Home] [Help]