1 PACKAGE BODY IEX_SCORE_CASE_PVT AS
2 /* $Header: iexcscrb.pls 120.2 2006/02/02 16:15:23 jypark ship $ */
3
4 -- Global package variables - These will be our Static Variables
5 -- These will be loaded from Name/Value pairs in IEX_SCORE_COMP_PARAMS
6 s_nAmountOutsdandingLimit NUMBER := -1;
7 s_nDaysPastDueLimit NUMBER := -1;
8 s_nLastScoreLimit NUMBER := -1;
9 s_nTimesDelinquentLimit NUMBER := -1;
10 s_nConsiderPastXMonths NUMBER := -1;
11 s_nAnnualPaymntLimit NUMBER := -1;
12 s_nInvXPctGreaterLimit NUMBER := -1;
13 s_nLastComponentID NUMBER := -1;
14 s_nAnnualPayInXDays NUMBER := -1;
15 -- PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
16 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
17 --
18 -- Function Calculate_Score
19 --
20 -- Parameters: Object_id -> What case are we scoring
21 -- Component_id -> What component are we on the score so we can get config. data
22 --
23 -- This function calculates the score of case objects sent by the scoring engine
24 -- each case contains many contracts, the case score is the worse score of all
25 -- contracts in the case.
26 --
27 Function Calculate_Score(p_case_id IN NUMBER, p_score_component_id IN NUMBER) RETURN NUMBER IS
28 cursor c_caseobj(nCaseID NUMBER) IS
29 SELECT object_id
30 FROM IEX_CASE_OBJECTS
31 where CAS_ID = nCaseID;
32
33 -- begin raverma 04172003
34 -- change join from IEX_CASES_ALL_B to IEX_CASES_VL
35 -- remove join on PARTY_ID since CUSTOMER_ID is FK TO HZ_CUST_ACCOUNTS
36 -- and join is not needed since we have CAS_ID
37 -- and remove Consolidate Date since we dont use it anywhere
38 -- begin raverma 05302003
39 -- change the cursor to look at OKL_BPD_CONTRACT_REMAING_V
40 -- this synchs with payment processing screen for CONTRACTS
41
42 cursor c_invamt(nCaseID NUMBER) IS
43 -- Begin fix bug #4932921-JYPARK-performance bug
44 -- select con.amount
45 -- from IEX_BPD_CONTRACT_REMAINING_V CON, IEX_CASES_VL CAS,
46 -- IEX_CASE_OBJECTS CAO
47 -- where CON.CONTRACT_ID = CAO.OBJECT_ID(+) AND
48 -- CAO.OBJECT_CODE(+) = 'CONTRACTS' AND
49 -- CAO.CAS_ID = CAS.CAS_ID(+) AND
50 -- CAS.CAS_ID = nCaseID AND
51 -- con.amount > 0;
52 SELECT SUM (PS.ACCTD_AMOUNT_DUE_REMAINING) AMOUNT
53 FROM
54 AR_PAYMENT_SCHEDULES PS,
55 OKL_CNSLD_AR_STRMS_B ST,
56 OKC_K_HEADERS_B KH,
57 IEX_CASE_OBJECTS CAO
58 WHERE
59 PS.CUSTOMER_TRX_ID = ST.RECEIVABLES_INVOICE_ID
60 AND KH.ID = ST.KHR_ID
61 AND CAO.object_id =KH.ID
62 AND CAO.OBJECT_CODE = 'CONTRACTS'
63 AND CAO.object_id =KH.ID
64 AND CAO.CAS_ID = nCaseId;
65 -- End fix bug #4932921-JYPARK-performance bug
66
67 v_bReturn BOOLEAN;
68 l_ContractID NUMBER;
69 l_caseScore NUMBER := 100;
70 l_nAmount NUMBER := NULL;
71 l_nCurrAmount NUMBER := -1;
72 l_sReturn VARCHAR2(30);
73 l_tempScore NUMBER;
74
75 -- begin raverma 04172003 add discrete variables
76 --l_NumContracts NUMBER;
77 l_CustAccountID NUMBER;
78 l_AmountOutstanding NUMBER;
79 l_LastScore NUMBER := NULL;
80 l_DaysPastDue NUMBER;
81 l_NumPromiseBroken NUMBER;
82 l_NumDelinquencies NUMBER;
83 l_DueInDays NUMBER;
84 l_return_status VARCHAR2(10);
85 l_msg_count NUMBER;
86 l_msg_data VARCHAR2(32767);
87
88 BEGIN
89
90 -- IF PG_DEBUG < 10 THEN
91 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
92 IEX_DEBUG_PUB.logMessage('IEX_SCORE_CASE_PVT: Calculate_Score: Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
93 END IF;
94 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE_CASE_PVT: Calculate_Score: Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
95
96 --First we load our required variables only once per component
97 IF p_score_component_id = -1 or p_score_component_id is null THEN
98 -- IF PG_DEBUG < 10 THEN
99 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
100 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Load config because of component ID ' || p_score_component_id);
101 END IF;
102 --s_nLastComponentID := p_score_component_id;
103 v_bReturn := Load_Configuration(p_score_component_id);
104 ELSIF s_nAmountOutsdandingLimit = -1 or
105 s_nDaysPastDueLimit = -1 or
106 s_nLastScoreLimit = -1 or
107 s_nTimesDelinquentLimit = -1 or
108 s_nConsiderPastXMonths = -1 or
109 s_nAnnualPaymntLimit = -1 or
110 s_nInvXPctGreaterLimit = -1 or
111 s_nLastComponentID = -1 THEN
112 -- IF PG_DEBUG < 10 THEN
113 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
114 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Load config missing value');
115 END IF;
116 v_bReturn := Load_Configuration(p_score_component_id);
117 END IF;
118
119 -- IF PG_DEBUG < 10 THEN
120 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
121 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Values Loaded:');
122 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Score_ComponentID ' || p_score_component_id);
123 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: CASE ID ' || p_case_id);
124 iex_debug_pub.logmessage('-----------------------------------------------------------------');
125 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: AmountOutsdandingLimit ' || s_nAmountOutsdandingLimit);
126 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: DaysPastDueLimit ' || s_nDaysPastDueLimit);
127 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: LastScoreLimit ' || s_nLastScoreLimit);
128 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: TimesDelinquentLimit ' || s_nTimesDelinquentLimit);
129 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: ConsiderPastXMonths ' || s_nConsiderPastXMonths);
130 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: InvXPctGreaterLimit ' || s_nInvXPctGreaterLimit);
131 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: AnnualPayInXDays ' || s_nAnnualPayInXDays);
132 END IF;
133
134 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calculating Score for case: ' || p_case_id);
135 -- begin raverma 04172003
136 -- get the Account for the case
137 -- IF PG_DEBUG < 10 THEN
138 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
139 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Finding AccountID');
140 END IF;
141 BEGIN
142 SELECT column_value
143 INTO l_CustAccountID
144 FROM iex_case_definitions
145 WHERE cas_id = p_case_id AND
146 column_name = 'CUSTOMER_ACCOUNT';
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 -- IF PG_DEBUG < 10 THEN
150 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
151 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: NO AccountID found for case');
152 END IF;
153 RAISE FND_API.G_EXC_ERROR;
154 END;
155 FND_FILE.PUT_LINE(FND_FILE.LOG, 'AccountID: ' || l_CustAccountID);
156
157 -- IF PG_DEBUG < 10 THEN
158 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
159 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: AccountID found ' || l_CustAccountID);
160 END IF;
161
162 -- Now that we have the configuration lets load all contracts on the case
163 BEGIN
164
165 OPEN c_caseobj(p_case_id);
166 LOOP
167 FETCH c_caseobj into l_ContractID;
168 EXIT WHEN c_caseobj%NOTFOUND;
169
170 -- IF PG_DEBUG < 10 THEN
171 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
172 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Contract ID ' || l_ContractID);
173 END IF;
174 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ContractID: ' || l_ContractID);
175
176 -- --IF PG_DEBUG < 10 THEN
177 --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
178 -- iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Getting Number of Contracts');
179 --END IF;
180
181 -- initialize all variables
182 l_AmountOutstanding := NULL;
183 l_DaysPastDue := NULL;
184 l_NumPromiseBroken := NULL;
185 l_NumDelinquencies := NULL;
186 l_DueInDays := NULL;
187 l_tempScore := 100;
188
189 /* test to see if it's a valid contract */
190 IEX_UTILITIES.Validate_any_id(p_api_version => 1.0,
191 p_init_msg_list => FND_API.G_FALSE,
192 x_msg_count => l_msg_Count,
193 x_msg_data => l_msg_data,
194 x_return_status => l_return_status,
195 p_col_id => l_ContractID,
196 p_col_name => 'ID',
197 p_table_name => 'OKL_K_HEADERS');
198
199 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
200 -- IF PG_DEBUG < 10 THEN
201 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
202 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Invalid contract found ' || l_ContractID);
203 END IF;
204 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE_CASE_PVT: Calculate_Score: Invalid contract found ' || l_ContractID);
205 RAISE FND_API.G_EXC_ERROR;
206 else
207 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE_CASE_PVT: Calculate_Score: contract found ' || l_ContractID);
208 end if;
209
210 -- 2) Is the amount outstanding > X?
211 -- IF PG_DEBUG < 10 THEN
212 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
213 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Get amount outstanding.');
214 END IF;
215
216 -- begin raverma 04162003 -- trap no data found and assign 0 to Remaining amount
217 BEGIN
218 select amount into l_AmountOutstanding
219 from IEX_BPD_CONTRACT_REMAINING_V
220 where contract_id = l_ContractID;
221
222 EXCEPTION
223 WHEN NO_DATA_FOUND THEN
224 -- IF PG_DEBUG < 10 THEN
225 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
226 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Could not find an outstanding amount => default to 0');
227 END IF;
228 l_AmountOutstanding := 0;
229 END;
230 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Amount outstanding is: ' || l_AmountOutstanding);
231 -- IF PG_DEBUG < 10 THEN
232 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
233 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Amount Outstanding: ' || l_AmountOutstanding);
234 END IF;
235
236 if l_AmountOutstanding > s_nAmountOutsdandingLimit then
237 l_tempScore := ReduceScore(l_tempScore,0);
238 end if;
239
240 -- 3) Does contract have sevice/supply hold? (TBD)
241 -- 4) Does case have unrefunded cures?
242 /* begin raverma 05012002 comment out as per Andre request
243 l_sReturn := OKL_CONTRACT_INFO_PVT.GET_UNREFUNDED_CURES(l_ContractID, l_nReturn);
244 if l_nReturn > 0 then
245 l_nFinalScore := ReduceScore(l_nFinalScore,0);
246 end if;
247 */
248
249 -- 5) Is contract past due > X?
250 l_sReturn := OKL_CONTRACT_INFO.GET_DAYS_PAST_DUE(l_ContractID, l_DaysPastDue);
251 -- IF PG_DEBUG < 10 THEN
252 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
253 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Calculate_Score: Days Past Due ' || l_DaysPastDue);
254 END IF;
255 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Days past due is: ' || l_DaysPastDue);
256
257 if l_DaysPastDue > 0 and l_DaysPastDue > s_nDaysPastDueLimit then
258 l_tempScore := ReduceScore(l_tempScore,0);
259 end if;
260
261 -- 6) Is last collection score < X?
262 -- raverma 04162003 base the score on the last score of the CASE not the CONTRACT
263 -- also, in the event this is the first time the CASE is being scored
264 -- add WHEN_NO_DATA found
265 -- IF PG_DEBUG < 10 THEN
266 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
267 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Verify last Case score.');
268 END IF;
269
270 -- we only need to get the last score once per CASE
271 -- since the score is the score of the last run
272 if l_lastScore is NULL then
273 BEGIN
274 select a.score_value
275 into l_LastScore
276 from iex_score_histories a
277 where a.SCORE_OBJECT_ID = p_case_id
278 and a.creation_date = (select max(b.creation_date)
279 from iex_score_histories b
280 where b.score_object_id = p_case_id
281 AND b.score_object_code = 'IEX_CASES');
282
283 EXCEPTION
284 WHEN NO_DATA_FOUND THEN
285 -- IF PG_DEBUG < 10 THEN
286 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
287 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: No previous score found.');
288 END IF;
289 l_tempScore := s_nLastScoreLimit;
290 END;
291
292 -- IF PG_DEBUG < 10 THEN
293 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
294 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Last Case score: ' || l_LastScore);
295 END IF;
296 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Last case score is: ' || l_LastScore);
297
298 if l_lastScore < s_nLastScoreLimit then
299 l_tempScore := ReduceScore(l_tempScore,0);
300 end if;
301 end if; -- last_score
302
303 -- 7) Does the contract has a broken promise to pay?
304 -- begin raverma 04172003 handle no data found
305 -- also, synch up with new PROB definition of broken promises
306 -- IF PG_DEBUG < 10 THEN
307 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
308 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Verify broken promises.');
309 END IF;
310 BEGIN
311 select count(1)
312 into l_NumPromiseBroken
313 from iex_promise_details A, iex_delinquencies B
314 where A.DELINQUENCY_ID = B.DELINQUENCY_ID
315 and B.CASE_ID = p_case_id
316 and A.STATE = 'BROKEN_PROMISE'
317 and A.STATUS <> 'PENDING';
318 EXCEPTION
319 WHEN NO_DATA_FOUND THEN
320 l_NumPromiseBroken := 0;
321 -- IF PG_DEBUG < 10 THEN
322 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
323 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: no broken promises found');
324 END IF;
325 END;
326 -- IF PG_DEBUG < 10 THEN
327 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
328 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Number of broken promises is ' || l_NumPromiseBroken);
329 END IF;
330 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Number of broken promises is: ' || l_NumPromiseBroken);
331
332 if l_NumPromiseBroken > 0 then
333 l_tempScore := ReduceScore(l_tempScore,0);
334 end if;
335
336 -- 8) Is contract past elegibility date? (TBD)
337 -- If it is not delinquent, maybe pre-delinquent
338 -- IF PG_DEBUG < 10 THEN
339 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
340 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: SCORE NOW ' || l_tempScore);
341 END IF;
342
343 -- uncomment to force pre-delinquency check
344 -- 05302003 raverma
345 -- the pre-delinquency check logic needs to be revisited no of it makes sense
346 IF PG_DEBUG < 2 THEN
347 l_tempScore := 100;
348 END IF;
349
350 if l_tempScore = 100 then
351 -- IF PG_DEBUG < 10 THEN
352 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
353 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Check pre-delinquency.');
354 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Number of delinquencies in last ' || s_nConsiderPastXMonths || ' months');
355 END IF;
356
357 -- begin raverma 04172003 append cust_account_id to WHERE clause
358 BEGIN
359 select count(1)
360 into l_NumDelinquencies
361 from iex_delinquencies
362 where creation_date = sysdate - (s_nConsiderPastXMonths * 30)
363 and cust_account_id = l_CustAccountID
364 and status <> 'PREDELINQUENT';
365 EXCEPTION
366 WHEN NO_DATA_FOUND THEN
367 l_NumDelinquencies := 0;
368 END;
369 -- IF PG_DEBUG < 10 THEN
370 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
371 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Number of delinquencies in last ' || s_nConsiderPastXMonths ||
372 ' months: ' || l_NumDelinquencies);
373 END IF;
374 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Number of delinquencies in past ' || s_nConsiderPastXMonths ||
375 ' months: ' || l_NumDelinquencies);
376
377 -- begin raverma 05302003 add l_AmountOutstanding > s_nAmountOutsdandingLimit clause according
378 -- to OKL documentation requirements
379 if l_NumDelinquencies > s_nTimesDelinquentLimit and l_AmountOutstanding > s_nAmountOutsdandingLimit then
380 l_tempScore := ReduceScore(l_tempScore,1);
381 end if;
382
383 -- really after the first check of amount due > X in the last Y months we should NOT
384 -- need to check pre-delinquency status after this
385 -- pursuing the logic below as OKL scoring requirements are ambiguous
386
387 -- begin raverma 04182003 only need to get this ONCE per CASE
388 if l_nAmount is NULL then
389 -- IF PG_DEBUG < 10 THEN
390 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
391 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Is Current contract ' || s_nInvXPctGreaterLimit || '% greater than previous');
392 END IF;
393
394 OPEN c_invamt(p_case_id);
395 LOOP
396 FETCH c_invamt into l_nAmount;
397 EXIT WHEN c_invamt%NOTFOUND;
398
399 if l_nCurrAmount = -1 then
400 l_nCurrAmount := l_nAmount;
401 end if;
402 END LOOP;
403 CLOSE c_invamt;
404
405 -- IF PG_DEBUG < 10 THEN
406 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
407 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Current Amount: ' || l_nAmount);
408 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Previous Amount: ' || l_nCurrAmount);
409 END IF;
410
411 if l_nAmount is not null then
412 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ' || l_nCurrAmount || ' greater than or less than ' || (l_nAmount * (1 + (s_nInvXPctGreaterLimit/100))));
413 if l_nCurrAmount > (l_nAmount * (1 + (s_nInvXPctGreaterLimit/100))) then
414 l_tempScore := ReduceScore(l_tempScore,1);
415 end if;
416 else
417 l_nAmount := -1;
418 end if;
419 Else
420 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
421 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Skipping Amount: ' || l_nAmount);
422 END IF;
423 End if;
424
425 --Annual payment coming due in X days
426 -- IF PG_DEBUG < 10 THEN
427 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
428 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Annual payment comming due in ' || s_nAnnualPayInXDays || ' days.');
429 END IF;
430
431 -- begin raverma 04182003 change AR_PAY_SCHED_ALL to org enabled view
432 BEGIN
433 select round(ps.due_date - sysdate) into l_DueInDays
434 from okc_k_headers_b chr
435 ,okc_rules_b sll
436 ,okc_rules_b slh
437 ,okc_rule_groups_b rg
438 ,OKL_CNSLD_AR_STRMS_B ST
439 ,okl_strm_type_v strm
440 ,AR_PAYMENT_SCHEDULES PS
441 where chr.id = rg.dnz_chr_id
442 and sll.rule_information_category = 'SLL'
443 and sll.object2_id1 = slh.id
444 and sll.jtot_object1_code = 'OKL_TUOM'
445 and slh.rule_information_category = 'SLH'
446 and sll.object1_id1 = 'A'
447 and nvl(sll.object1_id2,'#') = '#'
448 and slh.rgp_id = rg.id
449 and rg.rgd_code = 'LALEVL'
450 and rg.dnz_chr_id = l_contractID
451 and rg.dnz_chr_id = st.khr_id
452 and rg.cle_id = st.kle_id
453 and strm.id=st.sty_id
454 and strm.name not like 'CURE'
455 and st.receivables_invoice_id = ps.customer_trx_id
456 and ps.due_date between sysdate and sysdate + s_nAnnualPayInXDays
457 and ps.amount_due_remaining > 0;
458
459 EXCEPTION
460 WHEN NO_DATA_FOUND THEN
461 -- no payment coming due in next X number of days
462 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
463 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: No payments coming due soon...');
464 END IF;
465 l_DueInDays := s_nAnnualPayInXDays;
466 END;
467
468 -- IF PG_DEBUG < 10 THEN
469 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
470 --iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: due in days limit is ' || s_nAnnualPayInXDays || ' days');
471 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Next payment not coming due in next ' || s_nAnnualPayInXDays || ' days');
472 END IF;
473
474 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ' || 'Coming due in ' || l_DueInDays || ' days');
475 if l_DueInDays < s_nAnnualPayInXDays then
476 l_tempScore := ReduceScore(l_tempScore,1);
477 end if;
478 -- Payment Rejected (TBD)
479 -- Auto payment cancelled (TBD)
480 end if; --pre-delinquent
481
482 if l_tempScore < l_caseScore then
483 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
484 iex_debug_pub.logMessage('IEX_SCORE_CASE_PVT: Calculate_Score: ----------------------> new lowest score found: ' || l_tempScore);
485 END IF;
486 l_caseScore := l_tempScore;
487 end if;
488
489 END LOOP;
490
491 CLOSE c_caseobj;
492
493 EXCEPTION
494 WHEN NO_DATA_FOUND THEN
495 -- IF PG_DEBUG < 10 THEN
496 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
497 IEX_DEBUG_PUB.logMessage('IEX_SCORE_CASE_PVT: Calculate_Score: NO CONTRACTS FOUND');
498 END IF;
499 RAISE FND_API.G_EXC_ERROR;
500 END;
501 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE_CASE_PVT: Calculate_Score: End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
502
503 RETURN l_caseScore;
504
505 -- IF PG_DEBUG < 10 THEN
506 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
507 IEX_DEBUG_PUB.logMessage('IEX_SCORE_CASE_PVT: Calculate_Score: End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
508 END IF;
509
510 EXCEPTION
511 WHEN OTHERS THEN
512 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE_CASE_PVT: Calculate_Score: Exception ' || sqlerrm);
513 -- IF PG_DEBUG < 10 THEN
514 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
515 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Calculate_Score: Exception ' || sqlerrm);
516 END IF;
517 RAISE FND_API.G_EXC_ERROR;
518 END Calculate_Score;
519
520 --
521 -- Function Load_Configuration
522 --
523 -- Parameters: Component_id -> What component are we on the score so we can get config. data
524 --
525 -- This function is pretty dumb. It will find the component and load the values from DB
526 --
527 Function Load_Configuration(p_score_component_id IN NUMBER) RETURN BOOLEAN IS
528 cursor c_values(p_Component_id NUMBER) IS
529 SELECT code, value
530 FROM IEX_SCORE_COMP_PARAMS
531 WHERE SCORE_COMPONENT_ID = p_Component_id;
532
533 sCode VARCHAR2(40);
534 sValue VARCHAR2(200);
535
536 BEGIN
537 -- IF PG_DEBUG < 10 THEN
538 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
539 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: Load_Configuration: Loading Configuration Values ....');
540 END IF;
541 OPEN c_values(p_score_component_id);
542
543 LOOP
544 FETCH c_values into sCode, sValue;
545 EXIT WHEN c_values%NOTFOUND;
546
547 IF sCode = 'AMOUNT_OUTSTANDING_LIMIT' THEN
548 s_nAmountOutsdandingLimit := sValue;
549 ELSIF sCode = 'DAYS_PAST_DUE_LIMIT' THEN
550 s_nDaysPastDueLimit := sValue;
551 ELSIF sCode = 'LAST_SCORE_LIMIT' THEN
552 s_nLastScoreLimit := sValue;
553 ELSIF sCode = 'TIMES_DELINQUENT_LIMIT' THEN
554 s_nTimesDelinquentLimit := sValue;
555 ELSIF sCode = 'CONSIDER_PAST_X_MONTHS' THEN
556 s_nConsiderPastXMonths := sValue;
557 ELSIF sCode = 'ANNUAL_PAYMENT_LIMIT' THEN
558 s_nAnnualPaymntLimit := sValue;
559 ELSIF sCode = 'INVOICE_XPCT_GREATER_LIMIT' THEN
560 s_nInvXPctGreaterLimit := sValue;
561 ELSIF sCode = 'ANNUAL_PAYMENT_IN_XDAYS' THEN
562 s_nAnnualPayInXDays := sValue;
563 END IF;
564 END LOOP;
565
566 CLOSE c_values;
567
568 RETURN TRUE;
569 END Load_Configuration;
570
571 --
572 -- Function ReduceScore
573 --
574 -- Parameters: Score -> Current Score. Will use this to know what value to reduce from score.
575 -- Delinquency type -> 0:Delinquent ; 1- Pre-delinquent
576 --
577 --
578 Function ReduceScore(p_Score IN NUMBER, p_type IN NUMBER) return NUMBER IS
579 l_Return NUMBER;
580 BEGIN
581 -- IF PG_DEBUG < 10 THEN
582 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
583 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: pre-ReduceScore: Score= ' || p_Score);
584 END IF;
585 l_Return := p_Score;
586 if p_Score <= 100 and p_Score >= 80 and p_type = 0 then
587 l_Return := l_Return - 20;
588 elsif p_type = 0 then
589 l_Return := l_Return - 10;
590 elsif p_type = 1 then
591 l_Return := l_Return - 5;
592 end if;
593
594 -- IF PG_DEBUG < 10 THEN
595 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
596 iex_debug_pub.logmessage('IEX_SCORE_CASE_PVT: post-ReduceScore: Score= ' || l_Return);
597 END IF;
598
599 RETURN l_Return;
600
601 END ReduceScore;
602
603 END IEX_SCORE_CASE_PVT;
[Home] [Help]