[Home] [Help]
PACKAGE BODY: APPS.AR_CMGT_WF_ENGINE
Source
1 Package BODY AR_CMGT_WF_ENGINE AS
2 /* $Header: ARCMGWFB.pls 120.31 2006/04/13 19:20:10 bsarkar noship $ */
3
4 pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
5
6 PROCEDURE debug (
7 p_message_name IN VARCHAR2 ) IS
8 BEGIN
9 ar_cmgt_util.debug (p_message_name, 'ar.cmgt.plsql.AR_CMGT_WF_ENGINE' );
10 END;
11
12
13 PROCEDURE raise_recco_event (p_case_folder_id IN NUMBER) AS
14 l_list WF_PARAMETER_LIST_T;
15 l_param WF_PARAMETER_T;
16 l_key VARCHAR2(240);
17 l_exist VARCHAR2(1);
18 l_event_name VARCHAR2(240) := 'oracle.apps.ar.cmgt.CreditRequestRecommendation.implement';
19 l_credit_request_id NUMBER;
20 l_source_name AR_CMGT_CREDIT_REQUESTS.source_name%TYPE;
21 l_source_column1 AR_CMGT_CREDIT_REQUESTS.source_column1%TYPE;
22 l_source_column2 AR_CMGT_CREDIT_REQUESTS.source_column2%TYPE;
23 l_source_column3 AR_CMGT_CREDIT_REQUESTS.source_column3%TYPE;
24 l_source_user_id ar_cmgt_credit_requests.SOURCE_USER_ID%type;
25 l_source_resp_id ar_cmgt_credit_requests.SOURCE_RESP_ID%type;
26 l_source_resp_appln_id ar_cmgt_credit_requests.SOURCE_RESP_APPLN_ID%type;
27 l_source_security_group_id ar_cmgt_credit_requests.SOURCE_SECURITY_GROUP_ID%type;
28 l_source_org_id ar_cmgt_credit_requests.SOURCE_ORG_ID%type;
29 l_case_folder_exists VARCHAR2(1);
30
31 CURSOR get_case_folder_info (p_cf_id IN NUMBER) IS
32 SELECT cr.credit_request_id,
33 cr.source_name, cr.source_column1,
34 cr.source_column2, cr.source_column3,
35 cr.source_user_id,
36 cr.source_resp_id,
37 cr.source_resp_appln_id,
38 cr.source_security_group_id,
39 cr.source_org_id
40 FROM ar_cmgt_credit_requests cr,
41 ar_cmgt_case_folders cf
42 WHERE case_folder_id = p_cf_id
43 and cr.credit_request_id = cf.credit_request_id;
44
45 BEGIN
46 SAVEPOINT raise_cr_recco_event;
47 -- Test if there are any active subscritions
48 -- if it is the case then execute the subscriptions
49
50 l_exist := AR_CMGT_EVENT_PKG.exist_subscription( l_event_name );
51
52 OPEN get_case_folder_info(p_case_folder_id);
53 FETCH get_case_folder_info INTO l_credit_request_id,
54 l_source_name,
55 l_source_column1,
56 l_source_column2,
57 l_source_column3,
58 l_source_user_id,
59 l_source_resp_id,
60 l_source_resp_appln_id,
61 l_source_security_group_id,
62 l_source_org_id ;
63 l_case_folder_exists := 'Y';
64
65 IF get_case_folder_info%NOTFOUND THEN
66 l_case_folder_exists := 'N';
67 END IF;
68
69 CLOSE get_case_folder_info ;
70
71
72 IF l_exist = 'Y' AND
73 l_case_folder_exists = 'Y' THEN
74
75 --Get the item key
76 l_key := AR_CMGT_EVENT_PKG.item_key( p_event_name => l_event_name,
77 p_unique_identifier => p_case_folder_id );
78
79
80 -- initialization of object variables
81 l_list := WF_PARAMETER_LIST_T();
82
83 -- Add Context values to the list
84 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
85
86
87 -- add more parameters to the parameters list
88
89 wf_event.AddParameterToList(p_name => 'CREDIT_REQUEST_ID',
90 p_value => l_credit_request_id,
91 p_parameterlist => l_list);
92
93 wf_event.AddParameterToList(p_name => 'CASE_FOLDER_ID',
94 p_value => p_case_folder_id,
95 p_parameterlist => l_list);
96
97 wf_event.AddParameterToList(p_name => 'SOURCE_NAME',
98 p_value => l_source_name,
99 p_parameterlist => l_list);
100
101 wf_event.AddParameterToList(p_name => 'SOURCE_COLUMN1',
102 p_value => l_source_column1,
103 p_parameterlist => l_list);
104 wf_event.AddParameterToList(p_name => 'SOURCE_COLUMN2',
105 p_value => l_source_column2,
106 p_parameterlist => l_list);
107 wf_event.AddParameterToList(p_name => 'SOURCE_COLUMN3',
108 p_value => l_source_column3,
109 p_parameterlist => l_list);
110
111 wf_event.AddParameterToList(p_name => 'SOURCE_USER_ID',
112 p_value => l_source_user_id,
113 p_parameterlist => l_list);
114
115 wf_event.AddParameterToList(p_name => 'SOURCE_RESP_ID',
116 p_value => l_source_resp_id,
117 p_parameterlist => l_list);
118 wf_event.AddParameterToList(p_name => 'SOURCE_RESP_APPLN_ID',
119 p_value => l_source_resp_appln_id,
120 p_parameterlist => l_list);
121 wf_event.AddParameterToList(p_name => 'SOURCE_SECURITY_GROUP_ID',
122 p_value => l_source_security_group_id,
123 p_parameterlist => l_list);
124 wf_event.AddParameterToList(p_name => 'SOURCE_ORG_ID',
125 p_value => l_source_org_id,
126 p_parameterlist => l_list);
127 -- Raise Event
128 AR_CMGT_EVENT_PKG.raise_event(
129 p_event_name => l_event_name,
130 p_event_key => l_key,
131 p_parameters => l_list );
132
133 l_list.DELETE;
134
135 END IF;
136 EXCEPTION
137 WHEN OTHERS THEN
138 ROLLBACK TO raise_cr_recco_event;
139 raise;
140 END raise_recco_event;
141
142
143
144 procedure get_employee_details(
145 p_employee_id IN NUMBER,
146 p_user_name OUT NOCOPY VARCHAR2,
147 p_display_name OUT NOCOPY VARCHAR2) AS
148 BEGIN
149 wf_directory.getusername ('PER', p_employee_id,
150 p_user_name,
151 p_display_name);
152 EXCEPTION
153 WHEN OTHERS
154 THEN
155 wf_core.context ('AR_CMGT_WF_ENGINE','GET_EMPLOYEE_DETAILS',
156 sqlerrm);
157 raise;
158 END;
159
160 PROCEDURE check_required_dnb_data_points (
161 p_case_folder_id IN NUMBER,
162 p_check_list_id IN NUMBER,
163 p_errmsg OUT NOCOPY VARCHAR2,
164 p_resultout OUT NOCOPY VARCHAR2 ) IS
165
166 cnt NUMBER;
167 l_data_point_id ar_cmgt_data_points_vl.data_point_id%type;
168 CURSOR c_dnb_required_data IS
169 SELECT data_point_id
170 FROM ar_cmgt_check_list_dtls
171 WHERE data_point_id between 10000 and 20000
172 AND check_list_id = p_check_list_id
173 AND required_flag = 'Y';
174
175 -- need to find out NOCOPY the table involved for the data points
176 CURSOR c_dnb_source_table IS
177 SELECT distinct source_table_name
178 FROM ar_cmgt_dnb_elements_vl
179 WHERE source_table_name <> 'HZ_FINANCIAL_NUMBERS' -- this table is accessed via hz_financial_reports
180 and data_element_id in (
181 SELECT data_element_id
182 FROM ar_cmgt_dnb_mappings
183 WHERE data_point_id = l_data_point_id);
184
185
186 BEGIN
187 p_resultout := 0;
188 FOR c_dnb_required_data_rec IN c_dnb_required_data
189 LOOP
190 l_data_point_id := c_dnb_required_data_rec.data_point_id;
191 FOR c_dnb_source_table_rec IN c_dnb_source_table
192 LOOP
193 BEGIN
194 SELECT 1
195 INTO cnt
196 FROM ar_cmgt_cf_dnb_dtls
197 WHERE case_folder_id = p_case_folder_id
198 AND source_table_name = c_dnb_source_table_rec.source_table_name;
199
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 p_resultout := 2;
203 p_errmsg := 'Required DNB Data Points missing';
204 return;
205 WHEN TOO_MANY_ROWS THEN
206 NULL;
207 END;
208 END LOOP;
209 END LOOP;
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 p_resultout := 1;
214 p_errmsg := 'Sql Error in check_required_dnb_data_points '||sqlerrm;
215 return;
216
217
218 END;
219
220 PROCEDURE validate_reference_data_points (
221 p_credit_request_id IN NUMBER,
222 p_check_list_id IN NUMBER,
223 p_errmsg OUT NOCOPY VARCHAR2,
224 p_resultout OUT NOCOPY VARCHAR2) IS
225
226 /*******************************************************
227 Data Points Id Name
228 86 Bank Reference
229 87 Trade Reference
230 88 Gurantors
231 ********************************************************/
232 CURSOR c_ref_data_points IS
233 SELECT data_point_id, number_of_references
234 FROM ar_cmgt_check_list_dtls
235 WHERE check_list_id = p_check_list_id
236 AND data_point_id IN (86,87,88)
237 AND required_flag = 'Y';
238
239 l_cnt NUMBER := 0;
240 BEGIN
241 p_resultout := 0;
242 FOR c_ref_data_points_rec IN c_ref_data_points
243 LOOP
244 -- Get the No. of Bank reference Data
245 IF c_ref_data_points_rec.data_point_id = 86
246 THEN
247 BEGIN
248 SELECT COUNT(*)
249 INTO l_cnt
250 FROM ar_cmgt_bank_ref_data
251 WHERE credit_request_id = p_credit_request_id;
252
253 IF l_cnt < c_ref_data_points_rec.number_of_references
254 THEN
255 p_resultout := 2;
256 return;
257 END IF;
258 END;
259 ELSIF c_ref_data_points_rec.data_point_id = 87
260 THEN
261 BEGIN
262 SELECT COUNT(*)
263 INTO l_cnt
264 FROM ar_cmgt_trade_ref_data
265 WHERE credit_request_id = p_credit_request_id;
266
267 IF l_cnt < c_ref_data_points_rec.number_of_references
268 THEN
269 p_resultout := 2;
270 return;
271 END IF;
272 END;
273 ELSIF c_ref_data_points_rec.data_point_id = 88
274 THEN
275 BEGIN
276 SELECT COUNT(*)
277 INTO l_cnt
278 FROM ar_cmgt_guarantor_data
279 WHERE credit_request_id = p_credit_request_id;
280
281 IF l_cnt < c_ref_data_points_rec.number_of_references
282 THEN
283 p_resultout := 2;
284 return;
285 END IF;
286 END;
287 END IF;
288 END LOOP;
289 EXCEPTION
290 WHEN OTHERS THEN
291 p_errmsg := 'Error in ar_cmgt_wf_engine.validate_reference_data_points '|| sqlerrm;
292 p_resultout := 1;
293 return;
294 END;
295 PROCEDURE VALIDATE_REQUIRED_DATA_POINTS (
296 p_credit_request_id IN NUMBER,
297 p_case_folder_id IN NUMBER,
298 p_check_list_id IN NUMBER default NULL,
299 p_errmsg OUT NOCOPY VARCHAR2,
300 p_resultout OUT NOCOPY VARCHAR2) AS
301
302 l_check_list_id NUMBER;
303 l_cnt NUMBER;
304 /**********************************************
305 p_resultout = 0 means Sucess
306 p_resultout = 1 means fatal error
307 p_resultout = 2 Missing values for required data points
308 ***********************************************/
309
310 BEGIN
311 p_resultout := 0;
312 IF p_check_list_id IS NULL
313 THEN
314 BEGIN
315 SELECT check_list_id
316 INTO l_check_list_id
317 FROM ar_cmgt_case_folders
318 WHERE case_folder_id = p_case_folder_id
319 AND type = 'CASE';
320
321 EXCEPTION
322 WHEN NO_DATA_FOUND THEN
323 p_resultout := 1;
324 p_errmsg := 'Check List Id not found';
325 return;
326 WHEN OTHERS THEN
327 p_resultout := 1;
328 p_errmsg := sqlerrm;
329 return;
330 END;
331 END IF;
332 l_check_list_id := p_check_list_id;
333 BEGIN
334 SELECT 1 into l_cnt
335 FROM ar_cmgt_check_list_dtls a, ar_cmgt_cf_dtls b,
336 ar_cmgt_data_points_vl dp
337 WHERE a.check_list_id = l_check_list_id
338 AND b.case_folder_id = p_case_folder_id
339 AND a.data_point_id = b.data_point_id
340 AND dp.data_point_id = a.data_point_id
341 AND dp.data_point_category not in ('AGING','INVOICE')
342 AND a.required_flag = 'Y'
343 AND a.data_point_id < 10000 -- eliminate DNB data points
344 and b.data_point_value is null;
345
346 p_resultout := 2; -- one row exist so validation failed
347
348 EXCEPTION
349 WHEN NO_DATA_FOUND THEN
350 p_resultout := 0; -- success
351 -- need to move this procedure to util.
352 validate_reference_data_points(
353 p_credit_request_id,
354 p_check_list_id,
355 p_errmsg,
356 p_resultout);
357 IF p_resultout = 0
358 THEN
359 check_required_dnb_data_points(
360 p_case_folder_id,
361 p_check_list_id,
362 p_errmsg,
363 p_resultout);
364 END IF;
365 WHEN TOO_MANY_ROWS THEN
366 p_resultout := 2; -- all required values does not exist
367 WHEN OTHERS THEN
368 p_resultout := 1;
369 p_errmsg := sqlerrm;
370
371 END;
372 END;
373
374 PROCEDURE create_creditManagement_role (
375 itemtype in varchar2,
376 itemkey in varchar2 ) IS
377
378 l_role_name VARCHAR2(30):= 'AR_CMGT_CREDIT_ANALYST_ROLE';
379 l_role_display_name VARCHAR2(240) := 'Credit Analyst Role';
380 l_user_name fnd_user.user_name%type;
381 l_display_name per_people_f.full_name%type;
382
383 CURSOR c_get_resource_id IS
384 SELECT c.resource_id, c.source_id --employee id
385 FROM jtf_rs_role_relations a,
386 jtf_rs_roles_vl b,
387 jtf_rs_resource_extns_vl c
388 WHERE a.role_resource_type = 'RS_INDIVIDUAL'
389 AND a.role_resource_id = c.resource_id
390 AND a.role_id = b.role_id
391 AND b.role_code = 'CREDIT_ANALYST'
392 AND c.category = 'EMPLOYEE'
393 AND nvl(a.delete_flag,'N') <> 'Y';
394
395 BEGIN
396 -- this role will be used to re-assign credit analyst by Credit Scheduler
397 -- in case credit analyst is not available. Credit Analyst is defined in
398 -- resource manger as role_type CREDIT_ANALYST.
399
400 l_role_display_name := wf_directory.getRoleDisplayName (
401 p_role_name => 'AR_CMGT_CREDIT_ANALYST_ROLE');
402 IF l_role_display_name IS NULL
403 THEN
404 l_role_display_name := 'Credit Analyst Role';
405 wf_directory.CreateAdHocRole
406 (role_name => l_role_name,
407 role_display_name => l_role_display_name,
408 expiration_date => to_date('31/12/4712','DD/MM/RRRR'));
409 END IF;
410
411 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
412 itemKey => itemkey,
413 aname => 'CREDIT_ANALYST_ROLE',
414 avalue => 'AR_CMGT_CREDIT_ANALYST_ROLE');
415
416 -- get the resource id for all Credit Analysts
417 FOR c_get_resurce_rec IN c_get_resource_id
418 LOOP
419 get_employee_details(c_get_resurce_rec.source_id,
420 l_user_name,
421 l_display_name);
422 -- first check whether this is the active user or not
423 IF wf_directory.useractive(l_user_name)
424 THEN
425 --if user already added then it will raise an exception
426 --if not then it will add the user.
427 BEGIN
428 wf_directory.AddUsersToAdHocRole
429 ('AR_CMGT_CREDIT_ANALYST_ROLE',l_user_name);
430 EXCEPTION
431 WHEN OTHERS THEN
432 NULL;
433 END;
434 END IF;
435 END LOOP;
436
437 END;
438
439 PROCEDURE POST_CREDIT_ANALYST_ASSIGNMENT (
440 itemtype in varchar2,
441 itemkey in varchar2,
442 actid in number,
443 funcmode in varchar2,
444 resultout out NOCOPY varchar2) IS
445
446 l_employee_id fnd_user.employee_id%type;
447 l_resource_id jtf_rs_resource_extns_vl.resource_id%type;
448 l_notification_id NUMBER;
449 l_user_name VARCHAR2(60);
450 l_display_name VARCHAR2(240);
451 l_failure_function VARCHAR2(60);
452
453 BEGIN
454 IF funcmode = 'TRANSFER' OR funcmode = 'FORWARD'
455 THEN
456
457 -- get resource id for credit analyst user
458 BEGIN
459 SELECT employee_id
460 INTO l_employee_id
461 FROM FND_USER
462 WHERE user_name = wf_engine.context_text;
463 EXCEPTION
464 WHEN NO_DATA_FOUND THEN
465 wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
466 'Employee Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
467 raise;
468 WHEN OTHERS THEN
469 wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
470 'Sqlerror while getiing Employee Id '||sqlerrm);
471 raise;
472 END;
473 BEGIN
474 SELECT c.resource_id
475 INTO l_resource_id
476 FROM jtf_rs_role_relations a,
477 jtf_rs_roles_vl b,
478 jtf_rs_resource_extns_vl c
479 WHERE a.role_resource_type = 'RS_INDIVIDUAL'
480 AND a.role_resource_id = c.resource_id
481 AND a.role_id = b.role_id
482 AND b.role_code = 'CREDIT_ANALYST'
483 AND c.category = 'EMPLOYEE'
484 AND c.source_id = l_employee_id
485 AND nvl(a.delete_flag,'N') <> 'Y';
486
487
488 EXCEPTION
489 WHEN NO_DATA_FOUND THEN
490 wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
491 'Resource Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
492 raise;
493 WHEN OTHERS THEN
494 wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
495 'Sqlerror while getting Resource Id'||sqlerrm);
496 raise;
497 END;
498 get_employee_details(l_employee_id,l_user_name, l_display_name);
499
500 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
501 itemKey => itemkey,
502 aname => 'CREDIT_ANALYST_USER_NAME',
503 avalue => l_user_name);
504 WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
505 itemKey => itemkey,
506 aname => 'CREDIT_ANALYST_ID',
507 avalue => l_resource_id);
508 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
509 itemKey => itemkey,
510 aname => 'CREDIT_ANALYST_DISPLAY_NAME',
511 avalue => l_display_name);
512
513 UPDATE ar_cmgt_credit_requests
514 set credit_analyst_id = l_resource_id,
515 last_update_date = sysdate,
516 last_updated_by = fnd_global.user_id,
517 last_update_login = fnd_global.login_id
518 WHERE credit_request_id = itemkey
519 AND credit_analyst_id IS NULL;
520
521 UPDATE ar_cmgt_case_folders
522 set credit_analyst_id = l_resource_id,
523 last_updated = sysdate,
524 last_update_date = sysdate,
525 last_updated_by = fnd_global.user_id,
526 last_update_login = fnd_global.login_id
527 WHERE credit_request_id = itemkey
528 AND credit_analyst_id IS NULL;
529
530 l_failure_function := WF_ENGINE.getItemAttrText(
531 itemtype => itemtype,
532 itemkey => itemkey,
533 aname => 'FAILURE_FUNCTION');
534
535 IF l_failure_function = 'CREDIT_POLICY'
536 THEN
537
538 l_notification_id :=
539 WF_NOTIFICATION.send
540 ( role => wf_engine.context_text,
541 msg_type => 'ARCMGTAP',
542 msg_name => 'MSG_TO_CA_INV_POLICY_SUBMIT',
543 callback => 'WF_ENGINE.CB',
544 context => itemtype||':'||itemkey||':'||to_char(actid));
545
546 ELSIF l_failure_function = 'SCORING_MODEL'
547 THEN
548 l_notification_id :=
549 WF_NOTIFICATION.send
550 ( role => wf_engine.context_text,
551 msg_type => 'ARCMGTAP',
552 msg_name => 'MSG_CA_NO_SM',
553 callback => 'WF_ENGINE.CB',
554 context => itemtype||':'||itemkey||':'||to_char(actid));
555 ELSIF l_failure_function = 'SCORING_CURRENCY'
556 THEN
557 l_notification_id :=
558 WF_NOTIFICATION.send
559 ( role => wf_engine.context_text,
560 msg_type => 'ARCMGTAP',
561 msg_name => 'MSG_TO_CA_CURRENCY_NOT_MATCH',
562 callback => 'WF_ENGINE.CB',
563 context => itemtype||':'||itemkey||':'||to_char(actid));
564 ELSIF l_failure_function = 'MANUAL_ANALYSIS'
565 THEN
566 l_notification_id :=
567 WF_NOTIFICATION.send
568 ( role => wf_engine.context_text,
569 msg_type => 'ARCMGTAP',
570 msg_name => 'MSG_TO_CA_MANUAL_ANALYSIS',
571 callback => 'WF_ENGINE.CB',
572 context => itemtype||':'||itemkey||':'||to_char(actid));
573 ELSIF l_failure_function = 'SKIP_APPROVAL'
574 THEN
575 l_notification_id :=
576 WF_NOTIFICATION.send
577 ( role => wf_engine.context_text,
578 msg_type => 'ARCMGTAP',
579 msg_name => 'REASG_MSG_TO_CA_SKIP_APPROVAL',
580 callback => 'WF_ENGINE.CB',
581 context => itemtype||':'||itemkey||':'||to_char(actid));
582 ELSIF l_failure_function = 'GATHER_DATA_POINTS'
583 THEN
584 l_notification_id :=
585 WF_NOTIFICATION.send
586 ( role => wf_engine.context_text,
587 msg_type => 'ARCMGTAP',
588 msg_name => 'MSG_TO_CA_NO_CF_CREATED',
589 callback => 'WF_ENGINE.CB',
590 context => itemtype||':'||itemkey||':'||to_char(actid));
591 ELSIF l_failure_function = 'GENERATE_RECOMMENDATION'
592 THEN
593 l_notification_id :=
594 WF_NOTIFICATION.send
595 ( role => wf_engine.context_text,
596 msg_type => 'ARCMGTAP',
597 msg_name => 'MSG_TO_CA_NO_RECO',
598 callback => 'WF_ENGINE.CB',
599 context => itemtype||':'||itemkey||':'||to_char(actid));
600 ELSIF l_failure_function = 'VALIDATE_RECO'
601 THEN
602 l_notification_id :=
603 WF_NOTIFICATION.send
604 ( role => wf_engine.context_text,
605 msg_type => 'ARCMGTAP',
606 msg_name => 'MSG_TO_CA_INVALID_RECO',
607 callback => 'WF_ENGINE.CB',
608 context => itemtype||':'||itemkey||':'||to_char(actid));
609
610 ELSIF l_failure_function = 'DUPLICATE_CASE_FOLDER'
611 THEN
612 l_notification_id :=
613 WF_NOTIFICATION.send
614 ( role => wf_engine.context_text,
615 msg_type => 'ARCMGTAP',
616 msg_name => 'MESSAGE_APPEAL_INITIATED',
617 callback => 'WF_ENGINE.CB',
618 context => itemtype||':'||itemkey||':'||to_char(actid));
619
620 END IF;
621 END IF;
622 END;
623
624 PROCEDURE getCAFromRulesEngine(
625 p_itemtype IN VARCHAR2,
626 p_credit_request_id IN number,
627 p_credit_analyst_id OUT NOCOPY NUMBER ) IS
628
629
630 l_country hz_parties.country%type;
631 l_state hz_parties.state%type;
632 l_province hz_parties.province%type;
633 l_sic_code hz_parties.sic_code%type;
634 l_postal_code hz_parties.postal_code%type;
635 l_party_name hz_parties.party_name%type;
636 l_employees_total hz_parties.employees_total%type;
637 l_credit_classification ar_cmgt_credit_requests.credit_classification%type;
638 l_amount ar_cmgt_credit_requests.limit_amount%type;
639 l_review_type ar_cmgt_credit_requests.review_type%type;
640 l_profile_class_name hz_cust_profile_classes.name%type;
641 l_currency ar_cmgt_credit_requests.limit_currency%type;
642 l_party_id hz_parties.party_id%type;
643
644
645 BEGIN
646 -- Get All values for parametsrs
647 BEGIN
648 SELECT p.country,p.state, p.province,p.sic_code,p.party_name,
649 p.employees_total, c.credit_classification,
650 nvl(c.limit_amount,c.trx_amount), c.review_type,
651 nvl(c.limit_currency,c.trx_currency),
652 profclass.name, p.postal_code, p.state, p.party_id
653 INTO l_country, l_state, l_province, l_sic_code,
654 l_party_name, l_employees_total, l_credit_classification,
655 l_amount, l_review_type, l_currency,
656 l_profile_class_name, l_postal_code, l_state, l_party_id
657 FROM ar_cmgt_credit_requests c,
658 hz_parties p,
659 hz_cust_profile_classes profclass,
660 hz_customer_profiles prof
661 WHERE c.credit_request_id = p_credit_request_id
662 AND c.party_id = p.party_id
663 AND p.party_id = prof.party_id
664 AND c.cust_account_id = decode(prof.cust_account_id,-1,-99,prof.cust_account_id)
665 AND c.site_use_id = nvl(prof.site_use_id,-99)
666 AND prof.profile_class_id = profclass.profile_class_id;
667
668 EXCEPTION
669 WHEN NO_DATA_FOUND
670 THEN
671 p_credit_analyst_id := NULL;
672 return;
673 WHEN OTHERS THEN
674 wf_core.context('AR_CMGT_WF_ENGINE','getCAFromRulesEngine',p_itemtype, p_credit_request_id,
675 'Error While getting Rules Engine Parameter Details',sqlerrm);
676 raise;
677
678
679 END;
680 -- now call rules engine
681 FUN_RULE_PUB.init_parameter_list;
682 FUN_RULE_PUB.add_parameter('AMOUNT_REQUESTED',l_amount );
683 FUN_RULE_PUB.add_parameter('CREDIT_CLASSIFICATION', l_credit_classification);
684 FUN_RULE_PUB.add_parameter('CUSTOMER_PROFILE_CLASS',l_profile_class_name );
685 FUN_RULE_PUB.add_parameter('COUNTRY',l_country );
686 FUN_RULE_PUB.add_parameter('STATE',l_state );
687 FUN_RULE_PUB.add_parameter('PROVINCE',l_province );
688 FUN_RULE_PUB.add_parameter('SIC_CODE',l_sic_code );
689 FUN_RULE_PUB.add_parameter('CUSTOMER_NAME',l_party_id);
690 FUN_RULE_PUB.add_parameter('NUM_EMPLOYEE',l_employees_total );
691 FUN_RULE_PUB.add_parameter('REVIEW_TYPE',l_review_type );
692 FUN_RULE_PUB.add_parameter('CURRENCY',l_currency );
693 FUN_RULE_PUB.add_parameter('POSTAL_CODE',l_postal_code );
694 FUN_RULE_PUB.apply_rule('AR','OCM_CREDIT_ANALYST_ASSGN');
695
696 p_credit_analyst_id := FUN_RULE_PUB.get_number;
697
698 EXCEPTION
699 WHEN OTHERS THEN
700 wf_core.context('AR_CMGT_WF_ENGINE','getCAFromRulesEngine',p_itemtype, p_credit_request_id,
701 'Error While calling Rules Engine',sqlerrm);
702 raise;
703 END;
704
705 PROCEDURE ASSIGN_CREDIT_ANALYST (
706 itemtype in varchar2,
707 itemkey in varchar2,
708 actid in number,
709 funcmode in varchar2,
710 resultout out NOCOPY varchar2) IS
711
712 l_user_name VARCHAR2(60);
713 l_display_name VARCHAR2(240);
714 l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
715 l_credit_analyst_id ar_cmgt_credit_requests.credit_analyst_id%type;
716 l_employee_id per_people_f.person_id%type;
717 l_dummy VARCHAR2(1);
718 BEGIN
719 IF funcmode = 'RUN'
720 THEN
721 l_credit_analyst_id := WF_ENGINE.getItemAttrNumber
722 (itemType => 'ARCMGTAP',
723 itemKey => itemkey,
724 aname => 'CREDIT_ANALYST_ID');
725
726 IF l_credit_analyst_id IS NULL
727 THEN
728 -- Bug 4414431
729 -- Rules engine uptake
730 getCAFromRulesEngine(
731 p_itemtype => itemtype,
732 p_credit_request_id => itemkey,
733 p_credit_analyst_id => l_credit_analyst_id );
734
735 IF l_credit_analyst_id IS NULL
736 THEN
737 BEGIN
738 SELECT a.CREDIT_ANALYST_ID
739 INTO l_credit_analyst_id
740 FROM hz_customer_profiles a, ar_cmgt_credit_requests b
741 WHERE b.credit_request_id = itemkey
742 AND a.party_id = b.party_id
743 AND a.cust_account_id = decode(b.cust_account_id,-99,-1,b.cust_account_id)
744 AND nvl(a.site_use_id,-99) = nvl(b.site_use_id, -99);
745
746 EXCEPTION
747 WHEN NO_DATA_FOUND THEN
748 l_credit_analyst_id := NULL;
749 WHEN OTHERS THEN
750 wf_core.context('AR_CMGT_WF_ENGINE','ASSIGN_CREDIT_ANALYST',itemtype, itemkey,
751 'Error While getting Credit Analyst Id',sqlerrm);
752 raise;
753 END;
754 END IF;
755 -- Since we found the credit ananlyst, validate the credit ananlyst
756 -- against jtf
757
758 BEGIN
759 SELECT 'X'
760 INTO l_dummy
761 FROM jtf_rs_role_relations a,
762 jtf_rs_roles_vl b,
763 jtf_rs_resource_extns_vl c
764 WHERE a.role_resource_type = 'RS_INDIVIDUAL'
765 AND a.role_resource_id = c.resource_id
766 AND c.resource_id = l_credit_analyst_id
767 AND a.role_id = b.role_id
768 AND b.role_code = 'CREDIT_ANALYST'
769 AND c.category = 'EMPLOYEE'
770 AND nvl(a.delete_flag,'N') <> 'Y';
771 EXCEPTION
772 WHEN NO_DATA_FOUND THEN
773 l_credit_analyst_id := NULL;
774 WHEN OTHERS THEN
775 wf_core.context('AR_CMGT_WF_ENGINE','ASSIGN_CREDIT_ANALYST',itemtype, itemkey,
776 'Error While validating Credit Analyst Id:'||l_credit_analyst_id,sqlerrm);
777 raise;
778 END;
779
780 -- Also check if credit analyst is null
781 IF l_credit_analyst_id IS NULL
782 THEN
783 create_creditManagement_role(itemtype, itemkey);
784 resultout := 'COMPLETE:FAILURE';
785 return;
786 END IF;
787 BEGIN
788 SELECT source_id
789 INTO l_employee_id
790 FROM jtf_rs_resource_extns_vl
791 WHERE resource_id = l_credit_analyst_id
792 AND category = 'EMPLOYEE';
793
794 EXCEPTION
795 WHEN OTHERS THEN
796 wf_core.context('AR_CMGT_WF_ENGINE','ASSIGN_CREDIT_ANALYST',itemtype, itemkey,
797 'Error While getting Employee Id for Credit Analyst',sqlerrm);
798 raise;
799 END;
800
801 get_employee_details(l_employee_id,l_user_name, l_display_name);
802
803 UPDATE ar_cmgt_credit_requests
804 SET credit_analyst_id = l_credit_analyst_id,
805 last_update_date = sysdate,
806 last_updated_by = fnd_global.user_id,
807 last_update_login = fnd_global.login_id
808 WHERE credit_request_id = itemkey;
809
810
811 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
812 itemKey => itemkey,
813 aname => 'CREDIT_ANALYST_USER_NAME',
814 avalue => l_user_name);
815 WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
816 itemKey => itemkey,
817 aname => 'CREDIT_ANALYST_ID',
818 avalue => l_credit_analyst_id);
819 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
820 itemKey => itemkey,
821 aname => 'CREDIT_ANALYST_DISPLAY_NAME',
822 avalue => l_display_name);
823
824 END IF; -- end of credit analyst is null
825 -- Stamp CA to case folder
826 l_case_folder_id := WF_ENGINE.getItemAttrNumber
827 (itemType => 'ARCMGTAP',
828 itemKey => itemkey,
829 aname => 'CASE_FOLDER_ID');
830 IF l_case_folder_id IS NOT NULL
831 THEN
832 update ar_cmgt_case_folders
833 set credit_analyst_id = l_credit_analyst_id,
834 last_updated = sysdate,
835 last_update_date = sysdate,
836 last_updated_by = fnd_global.user_id,
837 last_update_login = fnd_global.login_id
838 WHERE case_folder_id = l_case_folder_id;
839
840 -- Also upadate data records with credit_analyst_id
841 update ar_cmgt_case_folders
842 set credit_analyst_id = l_credit_analyst_id,
843 last_updated = sysdate,
844 last_update_date = sysdate,
845 last_updated_by = fnd_global.user_id,
846 last_update_login = fnd_global.login_id
847 WHERE credit_request_id = itemkey
848 AND type = 'DATA';
849 END IF;
850 resultout := 'COMPLETE:SUCESS';
851 END IF;
852 END;
853
854
855 /**************************************************
856 ** This procedure will start workflow process
857 ***************************************************/
858 PROCEDURE start_workflow (
859 p_credit_request_id IN NUMBER,
860 p_application_status IN VARCHAR2 default 'SUBMIT') AS
861
862 l_save_threshold NUMBER;
863 l_status VARCHAR2(2000);
864 l_resultout VARCHAR2(2000);
865 l_user_name VARCHAR2(60);
866 l_display_name VARCHAR2(240);
867 l_application_status varchar2(2000);
868
869 BEGIN
870 -- The following parameter will kickoff WF in async. mode
871 l_save_threshold := WF_ENGINE.threshold;
872
873 WF_ENGINE.threshold := -1;
874
875 IF p_application_status = 'FINISH'
876 THEN
877 WF_ENGINE.CreateProcess (itemType => 'ARCMGTAP',
878 itemKey => p_credit_request_id,
879 process => 'AR_CMGT_APPLICATION_PROCESS');
880
881 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
882 itemKey => p_credit_request_id,
883 aname => 'CREDIT_REQUEST_ID',
884 avalue => p_credit_request_id);
885
886
887 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
888 itemKey => p_credit_request_id,
889 aname => 'APPLICATION_STATUS',
890 avalue => p_application_status);
891
892 WF_ENGINE.StartProcess ( itemType => 'ARCMGTAP',
893 itemKey => p_credit_request_id);
894
895 ELSIF p_application_status = 'SUBMIT'
896 THEN
897 -- first check whether this workflow is already submitted. If
898 -- it is already submitted then resume the process only
899 BEGIN
900 WF_ENGINE.ItemStatus(itemType => 'ARCMGTAP',
901 itemkey => p_credit_request_id,
902 status => l_status,
903 result => l_resultout);
904
905 WF_ENGINE.CompleteActivity( itemType => 'ARCMGTAP',
906 itemkey => p_credit_request_id,
907 activity => 'BLOCK',
908 result => NULL);
909
910 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
911 itemKey => p_credit_request_id,
912 aname => 'APPLICATION_STATUS',
913 avalue => p_application_status);
914
915 -- if the process does not exist it will rasie an exception. Otherwise
916 -- the process has already started. We need to resume the process.
917 EXCEPTION
918 WHEN OTHERS THEN
919 WF_ENGINE.CreateProcess (itemType => 'ARCMGTAP',
920 itemKey => p_credit_request_id,
921 process => 'AR_CMGT_APPLICATION_PROCESS');
922 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
923 itemKey => p_credit_request_id,
924 aname => 'CREDIT_REQUEST_ID',
925 avalue => p_credit_request_id);
926
927 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
928 itemKey => p_credit_request_id,
929 aname => 'APPLICATION_STATUS',
930 avalue => p_application_status);
931
932 WF_ENGINE.StartProcess ( itemType => 'ARCMGTAP',
933 itemKey => p_credit_request_id);
934
935
936 END;
937
938
939 END IF;
940
941
942
943 WF_ENGINE.threshold := l_save_threshold;
944
945 END;
946
947 PROCEDURE GENERATE_CREDIT_CLASSIFICATION (
948 itemtype in varchar2,
949 itemkey in varchar2,
950 actid in number,
951 funcmode in varchar2,
952 resultout out NOCOPY varchar2) IS
953
954 l_credit_classification ar_cmgt_credit_requests.credit_classification%type;
955 l_party_id ar_cmgt_credit_requests.party_id%type;
956 l_cust_account_id ar_cmgt_credit_requests.cust_account_id%type;
957 l_site_use_id ar_cmgt_credit_requests.site_use_id%type;
958
959 l_sql_statement VARCHAR2(2000);
960
961 BEGIN
962 IF funcmode = 'RUN'
963 THEN
964 SELECT credit_classification, party_id, cust_account_id, site_use_id
965 INTO l_credit_classification, l_party_id, l_cust_account_id, l_site_use_id
966 FROM ar_cmgt_credit_requests
967 WHERE credit_request_id = itemkey;
968
969 IF l_credit_classification IS NULL
970 THEN
971
972 l_credit_classification := AR_CMGT_UTIL.get_credit_classification(p_party_id => l_party_id,
973 p_cust_account_id => l_cust_account_id,
974 p_site_use_id => l_site_use_id);
975 UPDATE ar_cmgt_credit_requests
976 SET credit_classification = l_credit_classification
977 WHERE credit_request_id = itemkey;
978 END IF; -- end of credit classification
979 -- Now update the credit classification in wf
980 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
981 itemkey => itemkey,
982 aname => 'CREDIT_CLASSIFICATION',
983 avalue => l_credit_classification );
984 END IF;
985
986 EXCEPTION
987 WHEN others THEN
988 wf_core.context('AR_CMGT_WF_ENGINE','GENERATE_CREDIT_CLASSIFICATION',itemtype, itemkey,
989 sqlerrm);
990 raise;
991 END;
992
993 PROCEDURE UPDATE_CREDIT_REQ_TO_PROCESS (
994 itemtype in varchar2,
995 itemkey in varchar2,
996 actid in number,
997 funcmode in varchar2,
998 resultout out NOCOPY varchar2) IS
999
1000 BEGIN
1001
1002 IF funcmode = 'RUN'
1003 THEN
1004 UPDATE ar_cmgt_credit_requests
1005 SET status = 'IN_PROCESS',
1006 last_update_date = sysdate,
1007 last_updated_by = fnd_global.user_id,
1008 last_update_login = fnd_global.login_id
1009 WHERE credit_request_id = itemkey;
1010
1011 END IF;
1012
1013 END;
1014
1015 PROCEDURE UPDATE_CREDIT_REQ_TO_SUBMIT (
1016 itemtype in varchar2,
1017 itemkey in varchar2,
1018 actid in number,
1019 funcmode in varchar2,
1020 resultout out NOCOPY varchar2) IS
1021
1022 BEGIN
1023
1024 IF funcmode = 'RUN'
1025 THEN
1026 UPDATE ar_cmgt_credit_requests
1027 SET status = 'SUBMIT',
1028 last_update_date = sysdate,
1029 last_updated_by = fnd_global.user_id,
1030 last_update_login = fnd_global.login_id
1031 WHERE credit_request_id = itemkey;
1032
1033 END IF;
1034
1035 END;
1036
1037 PROCEDURE UPDATE_CASE_FOLDER_SUBMITTED (
1038 itemtype in varchar2,
1039 itemkey in varchar2,
1040 actid in number,
1041 funcmode in varchar2,
1042 resultout out NOCOPY varchar2) IS
1043
1044 l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
1045 BEGIN
1046
1047 IF funcmode = 'RUN'
1048 THEN
1049
1050 l_case_folder_id := WF_ENGINE.GetItemAttrNumber
1051 (itemtype => itemtype,
1052 itemkey => itemkey,
1053 aname => 'CASE_FOLDER_ID');
1054
1055 UPDATE ar_cmgt_case_folders
1056 SET status = 'SUBMITTED',
1057 last_updated = sysdate,
1058 last_update_date = sysdate,
1059 last_updated_by = fnd_global.user_id,
1060 last_update_login = fnd_global.login_id
1061 WHERE case_folder_id = l_case_folder_id;
1062
1063 END IF;
1064
1065 END;
1066
1067
1068 PROCEDURE CHECK_APPLICATION_STATUS (
1069 itemtype in varchar2,
1070 itemkey in varchar2,
1071 actid in number,
1072 funcmode in varchar2,
1073 resultout out NOCOPY varchar2) IS
1074
1075 l_application_status VARCHAR2(30);
1076
1077 BEGIN
1078
1079 IF funcmode = 'RUN'
1080 THEN
1081 l_application_status :=
1082 WF_ENGINE.GetItemAttrText
1083 (itemtype => itemtype,
1084 itemkey => itemkey,
1085 aname => 'APPLICATION_STATUS');
1086
1087
1088 resultout := 'COMPLETE:'||l_application_status;
1089
1090 END IF;
1091
1092 EXCEPTION
1093 WHEN OTHERS
1094 THEN
1095 wf_core.context('AR_CMGT_WF_ENGINE','CHECK_APPLICATION_STATUS',itemtype, itemkey,
1096 sqlerrm);
1097 raise;
1098 END;
1099
1100 PROCEDURE CREATE_PARTY_PROFILE (
1101 itemtype in varchar2,
1102 itemkey in varchar2,
1103 actid in number,
1104 funcmode in varchar2,
1105 resultout out NOCOPY varchar2) IS
1106
1107 l_party_id ar_cmgt_credit_requests.party_id%type;
1108 l_cust_account_profile_id hz_customer_profiles.cust_account_profile_id%type;
1109 BEGIN
1110 -- need to check and if not exists then craeet party profile(ARCMHZCB.pls AR_CMGT_HZ_COVER_API)
1111 IF funcmode = 'RUN'
1112 THEN
1113 SELECT party_id
1114 INTO l_party_id
1115 FROM ar_cmgt_credit_requests
1116 WHERE credit_request_id = itemkey;
1117 -- furst check whether the profile record exists or not. if exists
1118 -- then do n't create else create a record in hz_customer_profiles
1119 WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1120 itemkey => itemkey,
1121 aname => 'PARTY_ID',
1122 avalue => l_party_id);
1123 BEGIN
1124 SELECT cust_account_profile_id
1125 INTO l_cust_account_profile_id
1126 FROM hz_customer_profiles
1127 WHERE party_id = l_party_id
1128 AND cust_account_id = -1
1129 AND site_use_id IS NULL;
1130
1131 EXCEPTION
1132 WHEN NO_DATA_FOUND
1133 THEN
1134 ar_cmgt_hz_cover_api.create_party_profile(p_party_id => l_party_id);
1135 WHEN OTHERS
1136 THEN
1137 wf_core.context('AR_CMGT_WF_ENGINE','CREATE_PARTY_PROFILE',itemtype, itemkey,
1138 sqlerrm);
1139 raise;
1140 END;
1141
1142 END IF;
1143 END;
1144
1145 PROCEDURE CHECK_CREDIT_POLICY (
1146 itemtype in varchar2,
1147 itemkey in varchar2,
1148 actid in number,
1149 funcmode in varchar2,
1150 resultout out NOCOPY varchar2) IS
1151
1152 l_credit_classification ar_cmgt_check_lists.credit_classification%TYPE;
1153 l_review_type ar_cmgt_check_lists.review_type%TYPE;
1154 l_check_list_id ar_cmgt_check_lists.check_list_id%TYPE;
1155 l_score_model_id ar_cmgt_scores.score_model_id%TYPE;
1156 l_currency ar_cmgt_credit_requests.limit_currency%TYPE;
1157 l_amount_requested ar_cmgt_credit_requests.limit_amount%TYPE;
1158 l_case_folder_number ar_cmgt_case_folders.case_folder_number%type;
1159 l_source_name ar_cmgt_credit_requests.source_name%type;
1160 l_classification_meaning ar_lookups.meaning%type;
1161 l_review_type_meaning ar_lookups.meaning%type;
1162 l_application_number ar_cmgt_credit_requests.application_number%type;
1163 l_score_model_already_set VARCHAR2(1) := 'F';
1164 l_requestor_id ar_cmgt_credit_requests.requestor_id%type;
1165 l_requestor_user_name fnd_user.user_name%type;
1166 l_requestor_display_name per_people_f.full_name%type;
1167 l_party_id hz_parties.party_id%type;
1168 l_cust_account_id hz_cust_accounts.cust_account_id%type;
1169 l_party_name hz_parties.party_name%type;
1170 l_party_number hz_parties.party_number%type;
1171 l_account_number hz_cust_accounts.account_number%type;
1172 l_application_date ar_cmgt_credit_requests.application_date%type;
1173 l_source_column1 ar_cmgt_credit_requests.source_column1%type;
1174 l_source_column2 ar_cmgt_credit_requests.source_column2%type;
1175 l_source_column3 ar_cmgt_credit_requests.source_column3%type;
1176 l_notes ar_cmgt_credit_requests.notes%type;
1177 l_credit_request_type ar_cmgt_credit_requests.credit_request_type%type;
1178 l_requestor_type ar_cmgt_credit_requests.requestor_type%type;
1179
1180 BEGIN
1181 IF funcmode = 'RUN'
1182 THEN
1183
1184 -- based on credit request id get credit classification and review type
1185 -- to find valid check list and build case folder table.
1186 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1187 itemkey => itemkey,
1188 aname => 'FAILURE_FUNCTION',
1189 avalue => 'CREDIT_POLICY');
1190 BEGIN
1191 SELECT req.credit_classification, req.review_type,
1192 nvl(req.limit_currency, trx_currency),
1193 nvl(nvl(req.limit_amount,req.trx_amount),0),
1194 req.case_folder_number, req.score_model_id, req.source_name,
1195 req.application_number,
1196 lkp1.meaning classification_meaning,
1197 lkp2.meaning review_type_meaning,
1198 requestor_id,
1199 application_date,
1200 req.party_id,
1201 cust_account_id,
1202 source_column1,
1203 source_column2,
1204 source_column3,
1205 party.party_name,
1206 party.party_number,
1207 req.notes,
1208 req.credit_request_type,
1209 nvl(req.requestor_type, 'EMPLOYEE')
1210 INTO l_credit_classification, l_review_type, l_currency,
1211 l_amount_requested, l_case_folder_number, l_score_model_id,
1212 l_source_name, l_application_number,
1213 l_classification_meaning,
1214 l_review_type_meaning,
1215 l_requestor_id,
1216 l_application_date,
1217 l_party_id,
1218 l_cust_account_id,
1219 l_source_column1,
1220 l_source_column2,
1221 l_source_column3,
1222 l_party_name,
1223 l_party_number,
1224 l_notes,
1225 l_credit_request_type,
1226 l_requestor_type
1227 FROM ar_cmgt_credit_requests req,
1228 ar_lookups lkp1,
1229 ar_lookups lkp2,
1230 hz_parties party
1231 WHERE req.credit_request_id = itemkey
1232 AND req.party_id = party.party_id
1233 AND lkp1.lookup_type = 'AR_CMGT_CREDIT_CLASSIFICATION'
1234 AND lkp1.lookup_code = req.credit_classification
1235 AND lkp2.lookup_type = 'AR_CMGT_REVIEW_TYPE'
1236 AND lkp2.lookup_code = req.review_type;
1237 EXCEPTION
1238 WHEN NO_DATA_FOUND THEN
1239 SELECT req.credit_classification, req.review_type, req.application_number,
1240 req.score_model_id,
1241 application_date,
1242 req.party_id,
1243 cust_account_id,
1244 source_column1,
1245 source_column2,
1246 source_column3,
1247 party.party_name,
1248 party.party_number,
1249 req.notes,
1250 req.requestor_id,
1251 req.source_name,
1252 req.case_folder_number,
1253 nvl(req.limit_currency, trx_currency),
1254 nvl(nvl(req.limit_amount,req.trx_amount),0),
1255 req.credit_request_type,
1256 nvl(req.requestor_type, 'EMPLOYEE')
1257 INTO l_credit_classification, l_review_type, l_application_number,
1258 l_score_model_id,
1259 l_application_date,
1260 l_party_id,
1261 l_cust_account_id,
1262 l_source_column1,
1263 l_source_column2,
1264 l_source_column3,
1265 l_party_name,
1266 l_party_number,
1267 l_notes,
1268 l_requestor_id,
1269 l_source_name,
1270 l_case_folder_number,
1271 l_currency,
1272 l_amount_requested,
1273 l_credit_request_type,
1274 l_requestor_type
1275 FROM ar_cmgt_credit_requests req,
1276 hz_parties party
1277 WHERE credit_request_id = itemkey
1278 AND req.party_id = party.party_id;
1279
1280
1281 resultout := 'COMPLETE:NOTFOUND';
1282 --return;
1283 WHEN OTHERS THEN
1284 wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1285 sqlerrm);
1286 raise;
1287 END;
1288
1289 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1290 itemkey => itemkey,
1291 aname => 'CREDIT_CLASSIFICATION',
1292 avalue => l_credit_classification );
1293 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1294 itemkey => itemkey,
1295 aname => 'REVIEW_TYPE',
1296 avalue => l_review_type );
1297 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1298 itemkey => itemkey,
1299 aname => 'CURRENCY',
1300 avalue => l_currency );
1301 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1302 itemkey => itemkey,
1303 aname => 'SOURCE_NAME',
1304 avalue => l_source_name );
1305 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1306 itemkey => itemkey,
1307 aname => 'APPLICATION_NUMBER',
1308 avalue => l_application_number );
1309 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1310 itemkey => itemkey,
1311 aname => 'REQUESTED_CREDIT_LIMIT',
1312 avalue => l_amount_requested );
1313 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1314 itemkey => itemkey,
1315 aname => 'REQUESTOR_PERSON_ID',
1316 avalue => l_requestor_id );
1317 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1318 itemkey => itemkey,
1319 aname => 'SOURCE_COL1',
1320 avalue => l_source_column1 );
1321 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1322 itemkey => itemkey,
1323 aname => 'SOURCE_COL2',
1324 avalue => l_source_column2 );
1325 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1326 itemkey => itemkey,
1327 aname => 'SOURCE_COL3',
1328 avalue => l_source_column3 );
1329 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1330 itemkey => itemkey,
1331 aname => 'PARTY_NAME',
1332 avalue => l_party_name );
1333 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1334 itemkey => itemkey,
1335 aname => 'PARTY_NUMBER',
1336 avalue => l_party_number );
1337 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1338 itemkey => itemkey,
1339 aname => 'APPL_NOTES',
1340 avalue => l_notes );
1341 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
1342 itemkey => itemkey,
1343 aname => 'APPLICATION_DATE',
1344 avalue => l_application_date );
1345 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1346 itemkey => itemkey,
1347 aname => 'CREDIT_REQUEST_TYPE',
1348 avalue => l_credit_request_type );
1349
1350 IF l_requestor_type = 'EMPLOYEE'
1351 THEN
1352 get_employee_details(
1353 p_employee_id => l_requestor_id,
1354 p_user_name => l_requestor_user_name,
1355 p_display_name => l_requestor_display_name);
1356
1357 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1358 itemkey => itemkey,
1359 aname => 'REQUESTOR_USER_NAME',
1360 avalue => l_requestor_user_name );
1361 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1362 itemkey => itemkey,
1363 aname => 'REQUESTOR_DISPLAY_NAME',
1364 avalue => l_requestor_display_name );
1365 ELSE
1366 -- get user id
1367 BEGIN
1368 SELECT user_name
1369 INTO l_requestor_user_name
1370 FROM fnd_user
1371 WHERE user_id = l_requestor_id;
1372
1373 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1374 itemkey => itemkey,
1375 aname => 'REQUESTOR_USER_NAME',
1376 avalue => l_requestor_user_name );
1377
1378 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1379 itemkey => itemkey,
1380 aname => 'REQUESTOR_DISPLAY_NAME',
1381 avalue => l_requestor_user_name );
1382 EXCEPTION
1383 WHEN NO_DATA_FOUND THEN
1384 wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1385 'FND User Not Found'|| sqlerrm);
1386 raise;
1387 WHEN OTHERS THEN
1388 wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1389 'Other Error '|| sqlerrm);
1390 raise;
1391 END;
1392 END IF;
1393 IF l_case_folder_number IS NOT NULL
1394 THEN
1395
1396 WF_ENGINE.SetItemAttrNumber
1397 (itemtype => itemtype,
1398 itemkey => itemkey,
1399 aname => 'CASE_FOLDER_NUMBER',
1400 avalue => l_case_folder_number );
1401 END IF;
1402 IF l_score_model_id IS NOT NULL
1403 THEN
1404 l_score_model_already_set := 'T';
1405 WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1406 itemkey => itemkey,
1407 aname => 'SCORE_MODEL_ID',
1408 avalue => l_score_model_id);
1409 END IF;
1410 -- check if the application is on accounts level and set the account Number
1411 IF l_cust_account_id <> -99
1412 THEN
1413 BEGIN
1414 SELECT ACCOUNT_NUMBER
1415 INTO l_account_number
1416 FROM hz_cust_accounts
1417 WHERE cust_account_id = l_cust_account_id;
1418 EXCEPTION
1419 WHEN NO_DATA_FOUND THEN
1420 l_account_number := null;
1421 WHEN OTHERS THEN
1422 wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1423 'Getting Account Details SqlError: '|| sqlerrm);
1424 raise;
1425
1426 END;
1427 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1428 itemkey => itemkey,
1429 aname => 'ACCOUNT_NUMBER',
1430 avalue => l_account_number );
1431 END IF;
1432 IF resultout = 'COMPLETE:NOTFOUND'
1433 THEN
1434 return;
1435 END IF;
1436 BEGIN
1437 SELECT check_list_id, score_model_id
1438 INTO l_check_list_id, l_score_model_id
1439 FROM ar_cmgt_check_lists
1440 WHERE submit_flag = 'Y'
1441 AND credit_classification = l_credit_classification
1442 AND review_type = l_review_type
1443 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date) and TRUNC(nvl(end_date,SYSDATE));
1444 EXCEPTION
1445 WHEN NO_DATA_FOUND THEN
1446 -- in case of no_data_found Assign Credit Analyst and send notification
1447 -- to CA.
1448 wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1449 'No Check List found for the combination');
1450 resultout := 'COMPLETE:NOTFOUND';
1451 return;
1452 WHEN OTHERS THEN
1453 wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1454 sqlerrm);
1455 raise;
1456
1457 END;
1458 IF l_score_model_id IS NOT NULL
1459 AND l_score_model_already_set = 'F'
1460 THEN
1461 WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1462 itemkey => itemkey,
1463 aname => 'SCORE_MODEL_ID',
1464 avalue => l_score_model_id);
1465
1466 ELSIF l_score_model_id IS NULL
1467 THEN
1468 -- in case of null set to -99
1469 WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1470 itemkey => itemkey,
1471 aname => 'SCORE_MODEL_ID',
1472 avalue => -99);
1473 END IF;
1474
1475 IF l_check_list_id IS NULL
1476 THEN
1477 WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1478 itemkey => itemkey,
1479 aname => 'CHECK_LIST_ID',
1480 avalue => -99);
1481 resultout := 'COMPLETE:NOTFOUND';
1482 ELSE
1483 WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1484 itemkey => itemkey,
1485 aname => 'CHECK_LIST_ID',
1486 avalue => l_check_list_id);
1487 --Update credit request table with checklistid
1488 UPDATE ar_cmgt_credit_requests
1489 set check_list_id = l_check_list_id
1490 WHERE credit_request_id = itemkey;
1491
1492 resultout := 'COMPLETE:FOUND';
1493 END IF;
1494
1495
1496 END IF;
1497 END;
1498
1499 PROCEDURE SET_ROUTING_STATUS (
1500 itemtype in varchar2,
1501 itemkey in varchar2,
1502 actid in number,
1503 funcmode in varchar2,
1504 resultout out NOCOPY varchar2) IS
1505
1506 l_manual_analysis_flag VARCHAR2(1);
1507
1508 BEGIN
1509 IF funcmode = 'RUN'
1510 THEN
1511 l_manual_analysis_flag :=
1512 WF_ENGINE.GetItemAttrText
1513 (itemtype => itemtype,
1514 itemkey => itemkey,
1515 aname => 'MANUAL_ANALYSIS_FLAG');
1516 IF ( l_manual_analysis_flag = 'Y' )
1517 THEN
1518 resultout := 'COMPLETE:APPROVAL_ROUTE';
1519 ELSIF ( l_manual_analysis_flag = 'H' )
1520 THEN
1521 resultout := 'COMPLETE:HOLD_ROUTE';
1522 ELSE
1523 resultout := 'COMPLETE:INITIAL_ROUTE';
1524 END IF;
1525
1526 END IF;
1527 EXCEPTION
1528 WHEN OTHERS THEN
1529 wf_core.context('AR_CMGT_WF_ENGINE','SET_ROUTING_STATUS',itemtype, itemkey,
1530 sqlerrm);
1531 raise;
1532 END;
1533
1534
1535 PROCEDURE CHECK_SCORING_MODEL (
1536 itemtype in varchar2,
1537 itemkey in varchar2,
1538 actid in number,
1539 funcmode in varchar2,
1540 resultout out NOCOPY varchar2) IS
1541
1542
1543 l_score_model_id ar_cmgt_scores.score_model_id%TYPE;
1544 l_score_model_id_1 ar_cmgt_scores.score_model_id%TYPE;
1545
1546
1547 BEGIN
1548 IF funcmode = 'RUN'
1549 THEN
1550
1551 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1552 itemkey => itemkey,
1553 aname => 'FAILURE_FUNCTION',
1554 avalue => 'SCORING_MODEL');
1555
1556 l_score_model_id :=
1557 WF_ENGINE.getItemAttrText(itemtype => itemtype,
1558 itemkey => itemkey,
1559 aname => 'SCORE_MODEL_ID');
1560 IF l_score_model_id = -99
1561 THEN
1562 resultout := 'COMPLETE:NOTFOUND';
1563 ELSE
1564 -- check whether the score is valid or not
1565 BEGIN
1566 SELECT score_model_id
1567 INTO l_score_model_id_1
1568 FROM ar_cmgt_scores
1569 WHERE score_model_id = l_score_model_id
1570 AND submit_flag = 'Y'
1571 AND TRUNC(sysdate) between TRUNC(start_date) and TRUNC(nvl(end_date,SYSDATE));
1572
1573 resultout := 'COMPLETE:FOUND';
1574 EXCEPTION
1575 WHEN NO_DATA_FOUND
1576 THEN
1577 resultout := 'COMPLETE:NOTFOUND';
1578 return;
1579 END;
1580 END IF;
1581 END IF;
1582 END;
1583
1584 PROCEDURE CHECK_SCORING_CURRENCY (
1585 itemtype in varchar2,
1586 itemkey in varchar2,
1587 actid in number,
1588 funcmode in varchar2,
1589 resultout out NOCOPY varchar2) IS
1590
1591 l_limit_currency ar_cmgt_credit_requests.limit_currency%type;
1592 l_score_currency ar_cmgt_scores.currency%type;
1593 l_score_model_id ar_cmgt_scores.score_model_id%type;
1594 BEGIN
1595
1596 -- First get the requested currency and score_model_id
1597 IF funcmode = 'RUN'
1598 THEN
1599 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1600 itemkey => itemkey,
1601 aname => 'FAILURE_FUNCTION',
1602 avalue => 'SCORING_CURRENCY');
1603 l_score_model_id := WF_ENGINE.getItemAttrNumber
1604 (itemtype => itemtype,
1605 itemkey => itemkey,
1606 aname => 'SCORE_MODEL_ID');
1607
1608 l_limit_currency := WF_ENGINE.getItemAttrText
1609 (itemtype => itemtype,
1610 itemkey => itemkey,
1611 aname => 'LIMIT_CURRENCY');
1612
1613 BEGIN
1614 /* bug4527823: Added trunc function while comparing dates */
1615 SELECT currency
1616 INTO l_score_currency
1617 FROM ar_cmgt_scores
1618 WHERE score_model_id = l_score_model_id
1619 and submit_flag = 'Y'
1620 and TRUNC(nvl(end_date, SYSDATE)) >= TRUNC(sysdate)
1621 and currency = l_limit_currency;
1622
1623 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1624 itemkey => itemkey,
1625 aname => 'SCORE_CURRENCY',
1626 avalue => l_score_currency);
1627 resultout := 'COMPLETE:SUCESS';
1628
1629 EXCEPTION
1630 WHEN NO_DATA_FOUND THEN
1631 resultout := 'COMPLETE:FAILURE';
1632 WHEN OTHERS THEN
1633 raise;
1634 resultout := 'COMPLETE:FAILURE';
1635 wf_core.context('AR_CMGT_WF_ENGINE','CHECK_SCORING_CURRENCY',itemtype, itemkey,
1636 sqlerrm);
1637 END;
1638
1639 END IF;
1640
1641 END;
1642
1643
1644
1645 PROCEDURE UNDO_CASE_FOLDER (
1646 itemtype in varchar2,
1647 itemkey in varchar2,
1648 actid in number,
1649 funcmode in varchar2,
1650 resultout out NOCOPY varchar2) IS
1651
1652 l_case_folder_id NUMBER;
1653 BEGIN
1654
1655 IF funcmode = 'RUN'
1656 THEN
1657 l_case_folder_id := WF_ENGINE.getItemAttrText
1658 (itemtype => itemtype,
1659 itemkey => itemkey,
1660 aname => 'CASE_FOLDER_ID');
1661 delete ar_cmgt_case_folders
1662 WHERE case_folder_id = case_folder_id;
1663 delete ar_cmgt_cf_dtls
1664 WHERE case_folder_id = case_folder_id;
1665 END IF;
1666 EXCEPTION
1667 WHEN OTHERS
1668 THEN
1669 wf_core.context('AR_CMGT_WF_ENGINE','UNDO_CASE_FOLDER',itemtype, itemkey,
1670 sqlerrm);
1671 raise;
1672 END;
1673
1674
1675 PROCEDURE GATHER_DATA_POINTS (
1676 itemtype in varchar2,
1677 itemkey in varchar2,
1678 actid in number,
1679 funcmode in varchar2,
1680 resultout out NOCOPY varchar2) IS
1681
1682 l_credit_classification ar_cmgt_check_lists.credit_classification%TYPE;
1683 l_review_type ar_cmgt_check_lists.review_type%TYPE;
1684 l_check_list_id ar_cmgt_check_lists.check_list_id%TYPE;
1685 l_party_id ar_cmgt_credit_requests.party_id%type;
1686 l_cust_account_id ar_cmgt_credit_requests.cust_account_id%type;
1687 l_cust_acct_site_id ar_cmgt_credit_requests.cust_acct_site_id%type;
1688 l_request_id ar_cmgt_credit_requests.credit_request_id%type;
1689 l_trx_currency ar_cmgt_credit_requests.trx_currency%type;
1690 l_limit_currency ar_cmgt_credit_requests.limit_currency%type;
1691 l_case_folder_number ar_cmgt_case_folders.case_folder_number%type;
1692 l_org_id NUMBER ;
1693 l_score_model_id NUMBER;
1694 l_credit_request_id NUMBER;
1695 l_case_folder_id NUMBER;
1696 l_resultout VARCHAR2(200);
1697 BUILD_FAILURE EXCEPTION;
1698 l_error_message VARCHAR2(2000);
1699 l_case_folder_date ar_cmgt_case_folders.last_updated%type;
1700
1701 BEGIN
1702 IF funcmode = 'RUN'
1703 THEN
1704 -- get all the relevant information to generate case folder.
1705 l_credit_request_id := itemkey;
1706 BEGIN
1707 SELECT credit_classification, review_type,
1708 cust_account_id, party_id, site_use_id, nvl(limit_currency,trx_currency),
1709 source_org_id, case_folder_number
1710 INTO l_credit_classification, l_review_type, l_cust_account_id,
1711 l_party_id, l_cust_acct_site_id, l_trx_currency, l_org_id,
1712 l_case_folder_number
1713 FROM ar_cmgt_credit_requests
1714 WHERE credit_request_id = itemkey;
1715 EXCEPTION
1716 WHEN OTHERS THEN
1717 wf_core.context ('AR_CMGT_WF_ENGINE','GATHER_DATA_POINTS',itemtype,itemkey,
1718 'Error while getting records from AR_CMGT_CREDIT_REQUESTS',
1719 'Sql Error: '||sqlerrm);
1720 raise;
1721 END;
1722 l_check_list_id := WF_ENGINE.getItemAttrNumber
1723 (itemtype => itemtype,
1724 itemkey => itemkey,
1725 aname => 'CHECK_LIST_ID');
1726 l_score_model_id := WF_ENGINE.getItemAttrNumber
1727 (itemtype => itemtype,
1728 itemkey => itemkey,
1729 aname => 'SCORE_MODEL_ID');
1730
1731 l_case_folder_number := WF_ENGINE.getItemAttrText
1732 (itemtype => itemtype,
1733 itemkey => itemkey,
1734 aname => 'CASE_FOLDER_NUMBER');
1735
1736
1737 ar_cmgt_data_points_pkg.gather_data_points
1738 (p_party_id => l_party_id,
1739 p_cust_account_id => l_cust_account_id,
1740 p_cust_acct_site_id => l_cust_acct_site_id,
1741 p_trx_currency => l_trx_currency,
1742 p_org_id => l_org_id,
1743 p_check_list_id => l_check_list_id,
1744 p_credit_request_id => l_credit_request_id,
1745 p_score_model_id => l_score_model_id,
1746 p_credit_classification => l_credit_classification,
1747 p_review_type => l_review_type,
1748 p_case_folder_number => l_case_folder_number,
1749 p_mode => 'CREATE',
1750 p_limit_currency => l_limit_currency,
1751 p_case_folder_id => l_case_folder_id,
1752 p_error_msg => l_error_message,
1753 p_resultout => l_resultout);
1754
1755 -- this is the error due to some setup data missing
1756 IF l_resultout = 2
1757 THEN
1758 WF_ENGINE.setItemAttrText
1759 (itemtype => itemtype,
1760 itemkey => itemkey,
1761 aname => 'FAILURE_MESSAGE',
1762 avalue => l_error_message);
1763 WF_ENGINE.setItemAttrText
1764 (itemtype => itemtype,
1765 itemkey => itemkey,
1766 aname => 'FAILURE_FUNCTION',
1767 avalue => 'GATHER_DATA_POINTS');
1768 resultout := 'COMPLETE:FAILURE';
1769 return;
1770 END IF;
1771 IF l_resultout <> 0
1772 THEN
1773 wf_core.context ('AR_CMGT_WF_ENGINE','GATHER_DATA_POINTS',itemtype,itemkey,
1774 'Unable to Generate Case Folder',l_error_message);
1775 raise BUILD_FAILURE;
1776 END IF;
1777 WF_ENGINE.setItemAttrNumber
1778 (itemtype => itemtype,
1779 itemkey => itemkey,
1780 aname => 'CASE_FOLDER_ID',
1781 avalue => l_case_folder_id);
1782 WF_ENGINE.setItemAttrText
1783 (itemtype => itemtype,
1784 itemkey => itemkey,
1785 aname => 'LIMIT_CURRENCY',
1786 avalue => l_limit_currency);
1787 -- Get case folder details
1788 BEGIN
1789 SELECT case_folder_number, last_updated
1790 INTO l_case_folder_number, l_case_folder_date
1791 FROM ar_cmgt_case_folders
1792 WHERE case_folder_id = l_case_folder_id;
1793
1794 WF_ENGINE.setItemAttrText
1795 (itemtype => itemtype,
1796 itemkey => itemkey,
1797 aname => 'CASE_FOLDER_NUMBER',
1798 avalue => l_case_folder_number);
1799 WF_ENGINE.setItemAttrDate
1800 (itemtype => itemtype,
1801 itemkey => itemkey,
1802 aname => 'CASE_FOLDER_DATE',
1803 avalue => l_case_folder_date);
1804 EXCEPTION
1805 WHEN NO_DATA_FOUND THEN
1806 l_case_folder_number := null;
1807 l_case_folder_date := null;
1808 WHEN OTHERS THEN
1809 wf_core.context ('AR_CMGT_WF_ENGINE','GATHER_DATA_POINTS',itemtype,itemkey,
1810 'Unable to Get Case Folder Details, SqlError : '||sqlerrm);
1811 END;
1812 resultout := 'COMPLETE:SUCESS';
1813
1814 END IF;
1815
1816 EXCEPTION
1817 WHEN BUILD_FAILURE THEN
1818 raise;
1819 END;
1820
1821 procedure CALCULATE_SCORE(
1822 itemtype in varchar2,
1823 itemkey in varchar2,
1824 actid in number,
1825 funcmode in varchar2,
1826 resultout out NOCOPY varchar2) IS
1827
1828 l_case_folder_id ar_cmgt_case_folders.case_folder_id%TYPE;
1829 l_check_list_id ar_cmgt_check_lists.check_list_id%TYPE;
1830 l_score NUMBER;
1831 l_resultout VARCHAR2(1);
1832 l_debug_msg VARCHAR2(2000);
1833 l_error_msg VARCHAR2(2000);
1834
1835
1836 BEGIN
1837 IF funcmode = 'RUN'
1838 THEN
1839 l_debug_msg := 'Calculate Score ';
1840 l_case_folder_id := WF_ENGINE.GetItemAttrNumber
1841 (itemtype => itemtype,
1842 itemkey => itemkey,
1843 aname => 'CASE_FOLDER_ID');
1844 l_check_list_id := WF_ENGINE.GetItemAttrNumber
1845 (itemtype => itemtype,
1846 itemkey => itemkey,
1847 aname => 'CHECK_LIST_ID');
1848 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1849 itemkey => itemkey,
1850 aname => 'FAILURE_FUNCTION',
1851 avalue => 'MANUAL_ANALYSIS');
1852
1853
1854 AR_CMGT_SCORING_ENGINE.GENERATE_SCORE(
1855 p_case_folder_id => l_case_folder_id,
1856 p_score => l_score,
1857 p_error_msg => l_error_msg,
1858 p_resultout => l_resultout);
1859
1860
1861 IF l_resultout = 0
1862 THEN
1863 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1864 itemkey => itemkey,
1865 aname => 'SCORE',
1866 avalue => l_score);
1867 resultout := 'COMPLETE:SUCESS';
1868 ELSE
1869 resultout := 'COMPLETE:FAILURE';
1870 END IF;
1871 END IF;
1872 EXCEPTION
1873 WHEN OTHERS THEN
1874 wf_core.context('AR_CMGT_WF_ENGINE','CALCULATE_SCORE',itemtype,
1875 itemkey, l_debug_msg, l_error_msg);
1876 raise;
1877 END;
1878
1879 procedure CHECK_AUTO_RULES(
1880 itemtype in varchar2,
1881 itemkey in varchar2,
1882 actid in number,
1883 funcmode in varchar2,
1884 resultout out NOCOPY varchar2) IS
1885
1886 l_score_model_id ar_cmgt_scores.score_model_id%TYPE;
1887 l_auto_rules_id ar_cmgt_auto_rules.auto_rules_id%type;
1888 l_score_model_id_1 ar_cmgt_scores.score_model_id%TYPE;
1889
1890 BEGIN
1891 IF funcmode = 'RUN'
1892 THEN
1893
1894 -- first check whether there are any manual data items exist
1895 -- if exists then automation is not possible and route to manual analysis
1896
1897 l_score_model_id := WF_ENGINE.GetItemAttrNumber
1898 (itemtype => itemtype,
1899 itemkey => itemkey,
1900 aname => 'SCORE_MODEL_ID');
1901
1902 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1903 itemkey => itemkey,
1904 aname => 'FAILURE_FUNCTION',
1905 avalue => 'MANUAL_ANALYSIS');
1906
1907 -- first check whether score model is valid for this date or not.
1908 BEGIN
1909 SELECT score_model_id
1910 INTO l_score_model_id_1
1911 FROM ar_cmgt_scores
1912 WHERE score_model_id = l_score_model_id
1913 AND submit_flag = 'Y'
1914 AND TRUNC(sysdate) between TRUNC(start_date) and TRUNC(nvl(end_date,SYSDATE));
1915
1916 EXCEPTION
1917 WHEN NO_DATA_FOUND
1918 THEN
1919 resultout := 'COMPLETE:NOTEXIST';
1920 return;
1921 END;
1922 BEGIN
1923 SELECT auto_rules_id
1924 INTO l_auto_rules_id
1925 FROM ar_cmgt_auto_rules
1926 WHERE score_model_id = l_score_model_id
1927 AND submit_flag = 'Y'
1928 AND TRUNC(sysdate) between TRUNC(start_date) and TRUNC(nvl(end_date,SYSDATE));
1929
1930 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1931 itemkey => itemkey,
1932 aname => 'AUTO_RULES_ID',
1933 avalue => l_auto_rules_id);
1934 resultout := 'COMPLETE:EXIST';
1935
1936 EXCEPTION
1937 WHEN NO_DATA_FOUND THEN
1938 resultout := 'COMPLETE:NOTEXIST';
1939 WHEN OTHERS THEN
1940 wf_core.context('AR_CMGT_WF_ENGINE','CHECK_AUTO_RULES',itemtype,
1941 itemkey, sqlerrm);
1942 raise;
1943 END;
1944 END IF;
1945
1946 END;
1947
1948 procedure OVERRIDE_CHECKLIST(
1949 itemtype in varchar2,
1950 itemkey in varchar2,
1951 actid in number,
1952 funcmode in varchar2,
1953 resultout out NOCOPY varchar2) IS
1954
1955 l_score NUMBER;
1956 l_auto_rules_id NUMBER;
1957 l_override_checklist ar_cmgt_auto_rule_dtls.override_checklist_flag%type;
1958 l_skip_approval ar_cmgt_auto_rule_dtls.skip_approval_flag%type;
1959 l_currency ar_cmgt_credit_requests.limit_currency%TYPE;
1960 BEGIN
1961 IF funcmode = 'RUN'
1962 THEN
1963
1964 l_auto_rules_id := WF_ENGINE.GetItemAttrNumber
1965 (itemtype => itemtype,
1966 itemkey => itemkey,
1967 aname => 'AUTO_RULES_ID');
1968
1969 l_score := WF_ENGINE.GetItemAttrNumber
1970 (itemtype => itemtype,
1971 itemkey => itemkey,
1972 aname => 'SCORE');
1973 l_currency := WF_ENGINE.GetItemAttrText
1974 (itemtype => itemtype,
1975 itemkey => itemkey,
1976 aname => 'CURRENCY');
1977
1978
1979 BEGIN
1980 SELECT override_checklist_flag, skip_approval_flag
1981 INTO l_override_checklist, l_skip_approval
1982 FROM ar_cmgt_auto_rule_dtls
1983 WHERE auto_rules_id = l_auto_rules_id
1984 AND l_score between credit_score_low and credit_score_high
1985 AND currency = l_currency;
1986
1987
1988 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1989 itemkey => itemkey,
1990 aname => 'SKIP_APPROVAL',
1991 avalue => l_skip_approval);
1992 resultout := 'COMPLETE:'||l_override_checklist;
1993 EXCEPTION
1994 WHEN NO_DATA_FOUND THEN
1995 resultout := 'COMPLETE:N';
1996 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1997 itemkey => itemkey,
1998 aname => 'SKIP_APPROVAL',
1999 avalue => 'N');
2000 WHEN OTHERS THEN
2001 wf_core.context('AR_CMGT_WF_ENGINE','OVERRIDE_CHECKLIST',itemtype,
2002 itemkey, sqlerrm);
2003 raise;
2004 END;
2005 END IF;
2006
2007 END;
2008
2009
2010 procedure CHECK_REQUIRED_DATA_POINTS(
2011 itemtype in varchar2,
2012 itemkey in varchar2,
2013 actid in number,
2014 funcmode in varchar2,
2015 resultout out NOCOPY varchar2) IS
2016
2017 l_check_list_id NUMBER;
2018 l_case_folder_id NUMBER;
2019 l_credit_request_id NUMBER;
2020 l_errmsg VARCHAR2(2000);
2021 l_resultout VARCHAR2(2000);
2022
2023 BUILD_FAILURE EXCEPTION;
2024
2025 BEGIN
2026 IF funcmode = 'RUN'
2027 THEN
2028
2029 l_credit_request_id := itemkey;
2030
2031 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2032 itemkey => itemkey,
2033 aname => 'FAILURE_FUNCTION',
2034 avalue => 'MANUAL_ANALYSIS');
2035
2036 l_check_list_id := WF_ENGINE.GetItemAttrNumber
2037 (itemtype => itemtype,
2038 itemkey => itemkey,
2039 aname => 'CHECK_LIST_ID');
2040
2041 l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2042 (itemtype => itemtype,
2043 itemkey => itemkey,
2044 aname => 'CASE_FOLDER_ID');
2045
2046 validate_required_data_points(
2047 l_credit_request_id,
2048 l_case_folder_id,
2049 l_check_list_id,
2050 l_errmsg,
2051 l_resultout);
2052 -- l_resultout will have 3 values
2053 -- 0 = Sucess
2054 -- 1 = fatal error
2055 -- 2 = Required data points missing
2056
2057 IF l_resultout = 0
2058 THEN
2059 resultout := 'COMPLETE:SUCESS';
2060 ELSIF l_resultout = 2
2061 THEN
2062 resultout := 'COMPLETE:FAILURE';
2063 ELSIF l_resultout = 1
2064 THEN
2065 wf_core.context('AR_CMGT_WF_ENGINE','CHECK_REQUIRED_DATA_POINTS',itemtype,
2066 itemkey, l_errmsg);
2067 raise BUILD_FAILURE;
2068 END IF;
2069
2070 END IF;
2071
2072 EXCEPTION
2073 WHEN BUILD_FAILURE THEN
2074 raise;
2075 END;
2076
2077 procedure CHECK_SCORING_DATA_POINTS(
2078 itemtype in varchar2,
2079 itemkey in varchar2,
2080 actid in number,
2081 funcmode in varchar2,
2082 resultout out NOCOPY varchar2) IS
2083
2084 l_score_model_id NUMBER;
2085 l_case_folder_id NUMBER;
2086 l_data_point_id NUMBER;
2087 l_data_point_value ar_cmgt_cf_dtls.data_point_value%type;
2088 l_data_point_type VARCHAR2(255);
2089 l_null_zero_flag VARCHAR2(1);
2090 l_success_flg VARCHAR2(1);
2091 l_data_point_code ar_cmgt_data_points_b.data_point_code%type;
2092
2093 CURSOR dp_id_collec IS
2094 select distinct data_point_id
2095 from ar_cmgt_score_dtls
2096 where score_model_id= l_score_model_id;
2097 BEGIN
2098 IF funcmode = 'RUN'
2099 THEN
2100 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2101 itemkey => itemkey,
2102 aname => 'FAILURE_FUNCTION',
2103 avalue => 'CHECK_SCORING_DATA_POINTS');
2104 l_score_model_id := WF_ENGINE.GetItemAttrNumber
2105 (itemtype => itemtype,
2106 itemkey => itemkey,
2107 aname => 'SCORE_MODEL_ID');
2108 l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2109 (itemtype => itemtype,
2110 itemkey => itemkey,
2111 aname => 'CASE_FOLDER_ID');
2112
2113 -- initialize the sucess flag as this flag value will only
2114 -- change in case the value of null zero flag is 'null'
2115 -- and data point type is numeric.
2116
2117 l_success_flg := 'Y';
2118
2119 --get the null zero flag
2120
2121 BEGIN
2122 SELECT nvl(null_zero_flag,'N')
2123 INTO l_null_zero_flag
2124 FROM ar_cmgt_scores
2125 WHERE score_model_id = l_score_model_id;
2126
2127 EXCEPTION
2128 WHEN OTHERS THEN
2129 wf_core.context('AR_CMGT_WF_ENGINE','CHECK_SCORING_DATA_POINTS, Scoring Model Not Found '||
2130 'Score Model Id : '||l_score_model_id ,itemtype,
2131 itemkey, sqlerrm);
2132 raise;
2133 END;
2134
2135 --get the data point data type for each data point.
2136
2137 FOR dp_id_collec_rec IN dp_id_collec
2138 LOOP
2139
2140 BEGIN
2141 SELECT RETURN_DATA_TYPE, data_point_code
2142 INTO l_data_point_type, l_data_point_code
2143 FROM AR_CMGT_SCORABLE_DATA_POINTS_V
2144 WHERE DATA_POINT_ID = dp_id_collec_rec.data_point_id;
2145
2146 -- come out of the loop if scoring model contains External data points
2147 IF l_data_point_code = 'OCM_EXTERNAL_SCORE'
2148 THEN
2149 resultout := 'COMPLETE:FAILURE';
2150 return;
2151 END IF;
2152 EXCEPTION
2153 WHEN OTHERS THEN
2154 wf_core.context('AR_CMGT_WF_ENGINE','CHECK_SCORING_DATA_POINTS, Data Point Details Not Available '||
2155 'for Data Point Id : '||dp_id_collec_rec.data_point_id ,itemtype,
2156 itemkey, sqlerrm);
2157 raise;
2158 END;
2159
2160
2161 IF l_null_zero_flag = 'N'
2162 OR ( l_null_zero_flag = 'Y'
2163 AND l_data_point_type <> 'N')
2164 THEN
2165 -- if there are null values in data_point_value then
2166 -- scoring is not allowed.
2167 BEGIN
2168
2169 SELECT case1.data_point_value, case1.data_point_id
2170 INTO l_data_point_value, l_data_point_id
2171 FROM ar_cmgt_score_dtls score,
2172 ar_cmgt_cf_dtls case1
2173 WHERE score.score_model_id = l_score_model_id
2174 AND case1.case_folder_id = l_case_folder_id
2175 AND score.data_point_id = case1.data_point_id
2176 AND score.data_point_id = dp_id_collec_rec.data_point_id
2177 AND case1.data_point_value IS NULL;
2178
2179 --if a data point value is null and null zero flag is also 'N'
2180 resultout := 'COMPLETE:FAILURE';
2181 return;
2182 EXCEPTION
2183 WHEN NO_DATA_FOUND THEN
2184 resultout := 'COMPLETE:SUCESS';
2185 WHEN TOO_MANY_ROWS THEN
2186 resultout := 'COMPLETE:FAILURE';
2187 return;
2188 WHEN OTHERS THEN
2189 wf_core.context('AR_CMGT_WF_ENGINE','CHECK_SCORING_DATA_POINTS',itemtype,
2190 itemkey, sqlerrm);
2191 raise;
2192 END;
2193 ELSIF l_null_zero_flag = 'Y' AND
2194 l_data_point_type = 'N'
2195 THEN
2196 resultout := 'COMPLETE:SUCESS';
2197 END IF;
2198 END LOOP;
2199
2200 END IF;
2201 END;
2202
2203
2204 procedure SKIP_APPROVAL(
2205 itemtype in varchar2,
2206 itemkey in varchar2,
2207 actid in number,
2208 funcmode in varchar2,
2209 resultout out NOCOPY varchar2) IS
2210
2211 l_skip_approval VARCHAR2(1);
2212
2213 BEGIN
2214 l_skip_approval := WF_ENGINE.getItemAttrText
2215 (itemtype => itemtype,
2216 itemkey => itemkey,
2217 aname => 'SKIP_APPROVAL');
2218 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2219 itemkey => itemkey,
2220 aname => 'FAILURE_FUNCTION',
2221 avalue => 'SKIP_APPROVAL');
2222 IF l_skip_approval = 'Y'
2223 THEN
2224 resultout := 'COMPLETE:Y';
2225 ELSE
2226 resultout := 'COMPLETE:N';
2227 END IF;
2228 END;
2229
2230 procedure MARK_MANUAL_ANALYSIS(
2231 itemtype in varchar2,
2232 itemkey in varchar2,
2233 actid in number,
2234 funcmode in varchar2,
2235 resultout out NOCOPY varchar2) IS
2236 BEGIN
2237 IF funcmode = 'RUN'
2238 THEN
2239 -- Depending on the value of the flag recommendation will be implemented.
2240 -- recommendation route can be reached either by automation or manual.
2241 -- In case we reach recommendation via manual analysis then we will implement
2242 -- whatever user defined in case folder.
2243
2244 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2245 itemkey => itemkey,
2246 aname => 'MANUAL_ANALYSIS_FLAG',
2247 avalue => 'Y');
2248 END IF;
2249 END;
2250 procedure UPDATE_RECOMMENDATION
2251 ( p_party_id IN NUMBER,
2252 p_cust_account_id IN NUMBER,
2253 p_site_use_id IN NUMBER default null,
2254 p_credit_recommendation IN VARCHAR2,
2255 p_reco_value1 IN VARCHAR2,
2256 p_reco_value2 IN VARCHAR2) IS
2257
2258 l_sql_statement VARCHAR2(2000);
2259
2260 BEGIN
2261
2262 IF p_credit_recommendation = 'CLASSIFICATION'
2263 THEN
2264 IF p_site_use_id IS NULL
2265 THEN
2266 l_sql_statement :=
2267 ' UPDATE hz_customer_profiles ' ||
2268 ' set credit_classification = :1 ,'||
2269 ' last_update_date = sysdate ,'||
2270 ' last_updated_by = fnd_global.user_id, '||
2271 ' last_update_login = fnd_global.login_id '||
2272 ' where party_id = :2 '||
2273 ' and cust_account_id = :3 '||
2274 ' and site_use_id IS NULL' ;
2275 EXECUTE IMMEDIATE l_sql_statement using
2276 p_reco_value1,
2277 p_party_id,
2278 p_cust_account_id;
2279 ELSIF p_site_use_id IS NOT NULL
2280 THEN
2281 l_sql_statement :=
2282 ' UPDATE hz_customer_profiles ' ||
2283 ' set credit_classification = :1, '||
2284 ' last_update_date = sysdate ,'||
2285 ' last_updated_by = fnd_global.user_id, '||
2286 ' last_update_login = fnd_global.login_id '||
2287 ' where party_id = :2 '||
2288 ' and cust_account_id = :3 '||
2289 ' and site_use_id = :4 ';
2290 EXECUTE IMMEDIATE l_sql_statement using
2291 p_reco_value1,
2292 p_party_id,
2293 p_cust_account_id,
2294 p_site_use_id;
2295 END IF;
2296 ELSIF p_credit_recommendation = 'TXN_CREDIT_LIMIT'
2297 THEN
2298 IF p_site_use_id IS NULL
2299 THEN
2300 UPDATE hz_cust_profile_amts
2301 set trx_credit_limit = p_reco_value2,
2302 last_update_date = sysdate,
2303 last_updated_by = fnd_global.user_id,
2304 last_update_login = fnd_global.login_id
2305 WHERE cust_account_profile_id = (
2306 select cust_account_profile_id
2307 from hz_customer_profiles
2308 WHERE party_id = p_party_id
2309 AND cust_account_id = p_cust_account_id
2310 AND site_use_id IS NULL )
2311 AND currency_code = p_reco_value1;
2312 ELSIF p_site_use_id IS NOT NULL
2313 THEN
2314 UPDATE hz_cust_profile_amts
2315 set trx_credit_limit = p_reco_value2,
2316 last_update_date = sysdate,
2317 last_updated_by = fnd_global.user_id,
2318 last_update_login = fnd_global.login_id
2319 WHERE cust_account_profile_id = (
2320 select cust_account_profile_id
2321 from hz_customer_profiles
2322 WHERE party_id = p_party_id
2323 AND cust_account_id = p_cust_account_id
2324 AND site_use_id = p_site_use_id )
2325 AND currency_code = p_reco_value1;
2326 END IF;
2327 ELSIF p_credit_recommendation = 'CREDIT_LIMIT'
2328 THEN
2329 IF p_site_use_id IS NULL
2330 THEN
2331 UPDATE hz_cust_profile_amts
2332 set overall_credit_limit = p_reco_value2,
2333 last_update_date = sysdate,
2334 last_updated_by = fnd_global.user_id,
2335 last_update_login = fnd_global.login_id
2336 WHERE cust_account_profile_id = (
2337 select cust_account_profile_id
2338 from hz_customer_profiles
2339 WHERE party_id = p_party_id
2340 AND cust_account_id = p_cust_account_id
2341 AND site_use_id IS NULL )
2342 AND currency_code = p_reco_value1;
2343 ELSIF p_site_use_id IS NOT NULL
2344 THEN
2345 UPDATE hz_cust_profile_amts
2346 set overall_credit_limit = p_reco_value2,
2347 last_update_date = sysdate,
2348 last_updated_by = fnd_global.user_id,
2349 last_update_login = fnd_global.login_id
2350 WHERE cust_account_profile_id = (
2351 select cust_account_profile_id
2352 from hz_customer_profiles
2353 WHERE party_id = p_party_id
2354 AND cust_account_id = p_cust_account_id
2355 AND site_use_id = p_site_use_id )
2356 AND currency_code = p_reco_value1;
2357 END IF;
2358 ELSIF p_credit_recommendation = 'CUST_HOLD'
2359 THEN
2360 IF p_site_use_id IS NULL
2361 THEN
2362 UPDATE hz_customer_profiles
2363 set credit_hold = 'Y',
2364 last_update_date = sysdate,
2365 last_updated_by = fnd_global.user_id,
2366 last_update_login = fnd_global.login_id
2367 WHERE party_id = p_party_id
2368 AND cust_account_id = p_cust_account_id
2369 AND site_use_id IS NULL;
2370 ELSIF p_site_use_id IS NOT NULL
2371 THEN
2372 UPDATE hz_customer_profiles
2373 set credit_hold = 'Y',
2374 last_update_date = sysdate,
2375 last_updated_by = fnd_global.user_id,
2376 last_update_login = fnd_global.login_id
2377 WHERE party_id = p_party_id
2378 AND cust_account_id = p_cust_account_id
2379 AND site_use_id = p_site_use_id;
2380 END IF;
2381
2382 ELSIF p_credit_recommendation = 'REMOVE_CUST_HOLD'
2383 THEN
2384 IF p_site_use_id IS NULL
2385 THEN
2386 UPDATE hz_customer_profiles
2387 set credit_hold = 'N',
2388 last_update_date = sysdate,
2389 last_updated_by = fnd_global.user_id,
2390 last_update_login = fnd_global.login_id
2391 WHERE party_id = p_party_id
2392 AND cust_account_id = p_cust_account_id
2393 AND site_use_id IS NULL;
2394 ELSIF p_site_use_id IS NOT NULL
2395 THEN
2396 UPDATE hz_customer_profiles
2397 set credit_hold = 'N',
2398 last_update_date = sysdate,
2399 last_updated_by = fnd_global.user_id,
2400 last_update_login = fnd_global.login_id
2401 WHERE party_id = p_party_id
2402 AND cust_account_id = p_cust_account_id
2403 AND site_use_id = p_site_use_id;
2404 END IF;
2405 ELSIF p_credit_recommendation = 'PERCENT_CREDIT_LIMIT'
2406 THEN
2407 IF p_site_use_id IS NULL
2408 THEN
2409 UPDATE hz_cust_profile_amts
2410 set overall_credit_limit = (overall_credit_limit +
2411 ( overall_credit_limit * p_reco_value2/100)),
2412 last_update_date = sysdate,
2413 last_updated_by = fnd_global.user_id,
2414 last_update_login = fnd_global.login_id
2415 WHERE cust_account_profile_id = (
2416 select cust_account_profile_id
2417 from hz_customer_profiles
2418 WHERE party_id = p_party_id
2419 AND cust_account_id = p_cust_account_id
2420 AND site_use_id IS NULL )
2421 AND currency_code = p_reco_value1;
2422 ELSIF p_site_use_id IS NOT NULL
2423 THEN
2424 UPDATE hz_cust_profile_amts
2425 set overall_credit_limit = (overall_credit_limit +
2426 ( overall_credit_limit * p_reco_value2/100)),
2427 last_update_date = sysdate,
2428 last_updated_by = fnd_global.user_id,
2429 last_update_login = fnd_global.login_id
2430 WHERE cust_account_profile_id = (
2431 select cust_account_profile_id
2432 from hz_customer_profiles
2433 WHERE party_id = p_party_id
2434 AND cust_account_id = p_cust_account_id
2435 AND site_use_id = p_site_use_id )
2436 AND currency_code = p_reco_value1;
2437 END IF;
2438 ELSIF p_credit_recommendation = 'PERCENT_TXN_CREDIT_LIMIT'
2439 THEN
2440 IF p_site_use_id IS NULL
2441 THEN
2442 UPDATE hz_cust_profile_amts
2443 set trx_credit_limit = (trx_credit_limit +
2444 ( trx_credit_limit * p_reco_value2/100)),
2445 last_update_date = sysdate,
2446 last_updated_by = fnd_global.user_id,
2447 last_update_login = fnd_global.login_id
2448 WHERE cust_account_profile_id = (
2449 select cust_account_profile_id
2450 from hz_customer_profiles
2451 WHERE party_id = p_party_id
2452 AND cust_account_id = p_cust_account_id
2453 AND site_use_id IS NULL )
2454 AND currency_code = p_reco_value1;
2455 ELSIF p_site_use_id IS NOT NULL
2456 THEN
2457 UPDATE hz_cust_profile_amts
2458 set trx_credit_limit = (trx_credit_limit +
2459 ( trx_credit_limit * p_reco_value2/100)),
2460 last_update_date = sysdate,
2461 last_updated_by = fnd_global.user_id,
2462 last_update_login = fnd_global.login_id
2463 WHERE cust_account_profile_id = (
2464 select cust_account_profile_id
2465 from hz_customer_profiles
2466 WHERE party_id = p_party_id
2467 AND cust_account_id = p_cust_account_id
2468 AND site_use_id = p_site_use_id )
2469 AND currency_code = p_reco_value1;
2470 END IF;
2471 END IF;
2472 END;
2473
2474
2475 procedure GENERATE_RECOMMENDATION(
2476 itemtype in varchar2,
2477 itemkey in varchar2,
2478 actid in number,
2479 funcmode in varchar2,
2480 resultout out NOCOPY varchar2) IS
2481
2482 insert_failure EXCEPTION;
2483 l_auto_rules_id NUMBER;
2484 l_score NUMBER;
2485 l_credit_limit NUMBER := 0;
2486 l_case_folder_id NUMBER;
2487 l_auto_reco_exist VARCHAR2(1) := 'N';
2488 l_errmsg VARCHAR2(2000);
2489 l_resultout VARCHAR2(1);
2490
2491
2492 l_currency_code ar_cmgt_credit_requests.limit_currency%type;
2493 l_credit_type ar_cmgt_credit_requests.credit_type%type;
2494 l_credit_request_type ar_cmgt_credit_requests.credit_request_type%type;
2495 l_exposure NUMBER;
2496 l_risk_factor NUMBER;
2497
2498
2499 CURSOR c_auto_reco IS
2500 SELECT a.credit_recommendation, a.recommendation_value1,
2501 a.recommendation_value2
2502 FROM ar_cmgt_auto_recommends a, ar_cmgt_auto_rule_dtls b
2503 WHERE a.auto_rule_details_id = b.auto_rule_details_id
2504 AND l_score between b.credit_score_low and b.credit_score_high
2505 AND a.credit_type = l_credit_type
2506 AND b.auto_rules_id = l_auto_rules_id;
2507 BEGIN
2508 IF funcmode = 'RUN'
2509 THEN
2510 -- get credit type from credit requests
2511 SELECT CREDIT_TYPE, credit_request_type
2512 INTO l_credit_type, l_credit_request_type
2513 FROM ar_cmgt_credit_requests
2514 WHERE credit_request_id = itemkey;
2515
2516 -- in case of Guarantor no reco. will be generated
2517 IF l_credit_request_type = 'GUARANTOR'
2518 THEN
2519 resultout := 'COMPLETE:SUCESS';
2520 return;
2521 END IF;
2522
2523 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2524 itemkey => itemkey,
2525 aname => 'FAILURE_FUNCTION',
2526 avalue => 'GENERATE_RECOMMENDATION');
2527
2528 l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2529 (itemtype => itemtype,
2530 itemkey => itemkey,
2531 aname => 'CASE_FOLDER_ID');
2532
2533 l_score := WF_ENGINE.GetItemAttrNumber
2534 (itemtype => itemtype,
2535 itemkey => itemkey,
2536 aname => 'SCORE');
2537
2538 l_auto_rules_id := WF_ENGINE.GetItemAttrNumber
2539 (itemtype => itemtype,
2540 itemkey => itemkey,
2541 aname => 'AUTO_RULES_ID');
2542
2543 -- Now check if any auto recommendation exists
2544 FOR c_auto_rec IN c_auto_reco
2545 LOOP
2546 l_auto_reco_exist := 'Y';
2547 AR_CMGT_CONTROLS.populate_recommendation(
2548 p_case_folder_id => l_case_folder_id,
2549 p_credit_request_id => to_number(itemkey),
2550 p_score => l_score,
2551 p_recommended_credit_limit => l_credit_limit,
2552 p_credit_review_date => sysdate,
2553 p_credit_recommendation => c_auto_rec.credit_recommendation,
2554 p_recommendation_value1 => c_auto_rec.recommendation_value1,
2555 p_recommendation_value2 => trunc(c_auto_rec.recommendation_value2),
2556 p_status => 'O',
2557 p_credit_type => l_credit_type,
2558 p_errmsg => l_errmsg,
2559 p_resultout => l_resultout);
2560
2561 IF l_resultout <> 0
2562 THEN
2563 raise insert_failure;
2564 END IF;
2565 END LOOP;
2566
2567 IF l_auto_reco_exist = 'Y'
2568 THEN
2569 resultout := 'COMPLETE:SUCESS';
2570 ELSE
2571 resultout := 'COMPLETE:FAILURE';
2572 END IF;
2573
2574
2575 END IF;
2576 EXCEPTION
2577 WHEN insert_failure THEN
2578 wf_core.context('AR_CMGT_WF_ENGINE','GENERATE_RECOMMENDATION',itemtype,
2579 itemkey,
2580 'Error while inserting into ar_cmgt_cf_recommends',
2581 sqlerrm);
2582 raise;
2583 END;
2584
2585
2586
2587 procedure APPROVAL_PROCESS(
2588 itemtype in varchar2,
2589 itemkey in varchar2,
2590 actid in number,
2591 funcmode in varchar2,
2592 resultout out NOCOPY varchar2) IS
2593
2594 l_approver_rec_out ame_util.approverRecord;
2595 l_admin_approver_rec ame_util.approverRecord;
2596 l_case_folder_id number;
2597 l_approver_id number;
2598 l_approver_user_name varchar2(100);
2599 l_approver_display_name varchar2(100);
2600
2601 l_employee_id number;
2602
2603
2604 BEGIN
2605 IF funcmode = 'RUN'
2606 THEN
2607 -- dbms_session.set_sql_trace(true);
2608
2609
2610 l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2611 itemkey => itemkey,
2612 aname => 'CASE_FOLDER_ID');
2613
2614
2615 ame_api.getNextApprover( applicationIdIn => 222,
2616 transactionIdIn => l_case_folder_id,
2617 transactionTypeIn => 'ARCMGTAP',
2618 nextApproverOut => l_approver_rec_out);
2619
2620 l_approver_id := l_approver_rec_out.person_id;
2621
2622 ame_api.getadminapprover(adminapproverout => l_admin_approver_rec);
2623 IF l_approver_rec_out.person_id = l_admin_approver_rec.person_id
2624 THEN
2625
2626 wf_core.context('AR_CMGT_WF_ENGINE','APPROVAL_PROCESS',itemtype,
2627 itemkey, 'Approver is Admin User', null);
2628 --raise;
2629 END IF;
2630
2631
2632 if ( l_approver_id is not null ) -- next approver exist
2633 then
2634
2635
2636 get_employee_details(l_approver_id,
2637 l_approver_user_name,
2638 l_approver_display_name);
2639
2640 IF wf_directory.UserActive(l_approver_user_name)
2641 THEN
2642 WF_ENGINE.setItemAttrNumber(itemType => itemtype,
2643 itemKey => itemkey,
2644 aname => 'APPROVER_ID',
2645 avalue => l_approver_id);
2646
2647 WF_ENGINE.setItemAttrText(itemType => itemtype,
2648 itemKey => itemkey,
2649 aname => 'APPROVER_USER_NAME',
2650 avalue => l_approver_user_name);
2651 WF_ENGINE.setItemAttrText(itemType => itemtype,
2652 itemKey => itemkey,
2653 aname => 'APPROVER_DISPLAY_NAME',
2654 avalue => l_approver_display_name);
2655 resultout := 'COMPLETE:EXIST';
2656 ELSE
2657
2658 resultout := 'COMPLETE:NOTEXIST';
2659 END IF;
2660
2661 else -- next approver doesnot exist
2662 resultout := 'COMPLETE:NOTEXIST';
2663 end if;
2664 END IF;
2665 END;
2666
2667 procedure UPDATE_AME_APPROVE(
2668 itemtype in varchar2,
2669 itemkey in varchar2,
2670 actid in number,
2671 funcmode in varchar2,
2672 resultout out NOCOPY varchar2) IS
2673
2674 l_case_folder_id number;
2675 l_approver_id number;
2676 BEGIN
2677 IF funcmode = 'RUN'
2678 THEN
2679
2680 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
2681 itemKey => itemkey,
2682 aname => 'CREDIT_REQUEST_ID',
2683 avalue => itemkey);
2684 l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2685 itemkey => itemkey,
2686 aname => 'CASE_FOLDER_ID');
2687 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2688 itemkey => itemkey,
2689 aname => 'APPROVER_ID');
2690
2691 ame_api.updateApprovalStatus2(applicationIdIn => 222,
2692 transactionIdIn => l_case_folder_id,
2693 approvalStatusIn =>
2694 AME_UTIL.approvedStatus,
2695 approverPersonIdIn => l_approver_id,
2696 transactionTypeIn => 'ARCMGTAP');
2697
2698
2699
2700
2701 resultout := 'COMPLETE:';
2702 END IF;
2703 END;
2704
2705 procedure UPDATE_AME_REJECT(
2706 itemtype in varchar2,
2707 itemkey in varchar2,
2708 actid in number,
2709 funcmode in varchar2,
2710 resultout out NOCOPY varchar2) IS
2711
2712 l_case_folder_id number;
2713 l_approver_id number;
2714 BEGIN
2715 IF funcmode = 'RUN'
2716 THEN
2717 l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2718 itemkey => itemkey,
2719 aname => 'CASE_FOLDER_ID');
2720 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2721 itemkey => itemkey,
2722 aname => 'APPROVER_ID');
2723 ame_api.updateApprovalStatus2(applicationIdIn => 222,
2724 transactionIdIn => l_case_folder_id,
2725 approvalStatusIn => AME_UTIL.rejectStatus,
2726 approverPersonIdIn => l_approver_id,
2727 transactionTypeIn => 'ARCMGTAP');
2728 Update ar_cmgt_cf_recommends
2729 set status = 'R',
2730 last_update_date = sysdate,
2731 last_updated_by = fnd_global.user_id,
2732 last_update_login = fnd_global.login_id
2733 WHERE case_folder_id = l_case_folder_id;
2734
2735 Update ar_cmgt_case_folders
2736 set status = 'CLOSED',
2737 last_updated = sysdate,
2738 last_update_date = sysdate,
2739 last_updated_by = fnd_global.user_id,
2740 last_update_login = fnd_global.login_id
2741 WHERE case_folder_id = l_case_folder_id;
2742
2743 Update ar_cmgt_credit_requests
2744 set status = 'PROCESSED',
2745 last_update_date = sysdate,
2746 last_updated_by = fnd_global.user_id,
2747 last_update_login = fnd_global.login_id
2748 WHERE credit_request_id = itemkey;
2749
2750 END IF;
2751 END;
2752
2753 PROCEDURE IMPLEMENT_RECOMMENDATION(
2754 itemtype in varchar2,
2755 itemkey in varchar2,
2756 actid in number,
2757 funcmode in varchar2,
2758 resultout out NOCOPY varchar2) IS
2759
2760 l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
2761 l_credit_type ar_cmgt_credit_requests.credit_type%type;
2762 l_party_id ar_cmgt_credit_requests.party_id%type;
2763 l_cust_account_id ar_cmgt_credit_requests.cust_account_id%type;
2764 l_site_use_id ar_cmgt_credit_requests.site_use_id%type;
2765 l_error_msg VARCHAR2(2000);
2766 l_return_status VARCHAR2(1);
2767 HOLD_ERROR EXCEPTION;
2768 l_reco_id ar_cmgt_cf_recommends.RECOMMENDATION_ID%TYPE;
2769 l_dayz ar_cmgt_cf_recommends.RECOMMENDATION_VALUE1%TYPE;
2770 no_data_found EXCEPTION;
2771 l_last_revw_date hz_customer_profiles.LAST_CREDIT_REVIEW_DATE%TYPE;
2772
2773 CURSOR c_reco IS
2774 SELECT credit_recommendation, recommendation_value1,
2775 recommendation_value2
2776 FROM ar_cmgt_cf_recommends
2777 WHERE case_folder_id = l_case_folder_id
2778 AND credit_type = l_credit_type;
2779 BEGIN
2780 IF funcmode = 'RUN'
2781 THEN
2782
2783 BEGIN
2784 SELECT party_id, decode(cust_account_id,-99,-1,cust_account_id),
2785 decode(site_use_id,-99,null,site_use_id), credit_type
2786 INTO l_party_id, l_cust_account_id, l_site_use_id, l_credit_type
2787 FROM ar_cmgt_credit_requests
2788 WHERE credit_request_id = itemkey;
2789
2790 END;
2791
2792 l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2793 (itemtype => itemtype,
2794 itemkey => itemkey,
2795 aname => 'CASE_FOLDER_ID');
2796
2797 FOR c_reco_rec IN c_reco
2798 LOOP
2799
2800
2801 IF c_reco_rec.credit_recommendation = 'CLASSIFICATION' OR
2802 c_reco_rec.credit_recommendation = 'TXN_CREDIT_LIMIT' OR
2803 c_reco_rec.credit_recommendation = 'CREDIT_LIMIT' OR
2804 c_reco_rec.credit_recommendation = 'CUST_HOLD' OR
2805 c_reco_rec.credit_recommendation = 'REMOVE_CUST_HOLD' OR
2806 c_reco_rec.credit_recommendation = 'PERCENT_CREDIT_LIMIT' OR
2807 c_reco_rec.credit_recommendation = 'PERCENT_TXN_CREDIT_LIMIT'
2808 THEN
2809 UPDATE_RECOMMENDATION
2810 ( p_party_id => l_party_id,
2811 p_cust_account_id => l_cust_account_id,
2812 p_site_use_id => l_site_use_id,
2813 p_credit_recommendation => c_reco_rec.credit_recommendation,
2814 p_reco_value1 => c_reco_rec.recommendation_value1,
2815 p_reco_value2 => trunc(c_reco_rec.recommendation_value2));
2816
2817 -- When customer is placed on hold put all the
2818 -- orders on hold
2819 IF c_reco_rec.credit_recommendation = 'CUST_HOLD'
2820 THEN
2821 AR_CMGT_UTIL.OM_CUST_APPLY_HOLD (
2822 p_party_id => l_party_id,
2823 p_cust_account_id => l_cust_account_id,
2824 p_site_use_id => l_site_use_id,
2825 p_error_msg => l_error_msg,
2826 p_return_status => l_return_status );
2827 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2828 THEN
2829 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_RECOMMENDATION',itemtype,
2830 itemkey,
2831 'Error while applying Customer Hold for Party : '||l_party_id||' Cust Account Id '||
2832 l_cust_account_id||' Site Use Id '|| l_site_use_id ||' '|| l_error_msg,
2833 sqlerrm);
2834 raise hold_error;
2835 END IF;
2836 ELSIF c_reco_rec.credit_recommendation = 'REMOVE_CUST_HOLD'
2837 THEN
2838 -- When customer is placed on hold put all the
2839 -- orders on hold
2840 AR_CMGT_UTIL.OM_CUST_RELEASE_HOLD (
2841 p_party_id => l_party_id,
2842 p_cust_account_id => l_cust_account_id,
2843 p_site_use_id => l_site_use_id,
2844 p_error_msg => l_error_msg,
2845 p_return_status => l_return_status );
2846 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2847 THEN
2848 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_RECOMMENDATION',itemtype,
2849 itemkey,
2850 'Error while applying Removing Hold for Party : '||l_party_id||' Cust Account Id '||
2851 l_cust_account_id||' Site Use Id '|| l_site_use_id ||' '|| l_error_msg,
2852 sqlerrm);
2853 raise hold_error;
2854 END IF;
2855 END IF;
2856 ELSIF c_reco_rec.credit_recommendation = 'AUTHORIZE_APPEAL'
2857 THEN
2858
2859 --get the number of days as authorized by CA
2860
2861 BEGIN
2862
2863 SELECT recommendation_value1,RECOMMENDATION_ID
2864 INTO l_dayz,l_reco_id
2865 FROM ar_cmgt_cf_recommends
2866 WHERE CASE_FOLDER_ID = l_case_folder_id
2867 AND credit_recommendation = 'AUTHORIZE_APPEAL';
2868
2869
2870
2871 EXCEPTION
2872 WHEN NO_DATA_FOUND
2873 THEN
2874 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2875 itemkey,'No data found in recommendations for Appeal',
2876 sqlerrm);
2877 raise;
2878
2879 WHEN OTHERS
2880 THEN
2881 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2882 itemkey,'Error accessing system options',
2883 sqlerrm);
2884 raise;
2885
2886 END;
2887
2888
2889 --update number of days in ar_cmgt_cf_recommends
2890
2891 UPDATE AR_CMGT_CF_RECOMMENDS
2892 SET RECOMMENDATION_VALUE2 = fnd_date.DATE_TO_CANONICAL(trunc(sysdate) + to_number(trunc(l_dayz))),
2893 last_update_date = sysdate,
2894 last_updated_by = fnd_global.user_id,
2895 last_update_login = fnd_global.login_id
2896 WHERE RECOMMENDATION_ID = l_reco_id;
2897
2898 if (sql%notfound)
2899 THEN
2900 raise no_data_found;
2901 END IF;
2902
2903 --the recommendations is 'Change Review Cycle'
2904
2905 ELSIF c_reco_rec.credit_recommendation = 'CHANGE_REVIEW_CYCLE'
2906 THEN
2907
2908 --get the last credit review date for updation if site use id is null.
2909
2910 IF l_site_use_id IS NULL
2911 THEN
2912
2913 BEGIN
2914
2915 SELECT LAST_CREDIT_REVIEW_DATE
2916 INTO l_last_revw_date
2917 FROM hz_customer_profiles
2918 WHERE party_id = l_party_id
2919 AND cust_account_id = l_cust_account_id
2920 AND site_use_id IS NULL;
2921
2922 EXCEPTION
2923 WHEN NO_DATA_FOUND
2924 THEN
2925 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2926 itemkey,'No data found in customer profiles',
2927 sqlerrm);
2928 raise;
2929
2930 WHEN OTHERS
2931 THEN
2932 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2933 itemkey,'Error accessing customer profiles',
2934 sqlerrm);
2935 raise;
2936
2937 END;
2938
2939
2940 IF l_last_revw_date IS NOT NULL
2941 THEN
2942 BEGIN
2943
2944 UPDATE hz_customer_profiles
2945 SET REVIEW_CYCLE = c_reco_rec.recommendation_value1,
2946 NEXT_CREDIT_REVIEW_DATE = DECODE(c_reco_rec.recommendation_value1,
2947 'YEARLY', (l_last_revw_date + 365),
2948 'HALF_YEARLY', (l_last_revw_date + 180),
2949 'QUARTERLY', (l_last_revw_date + 90),
2950 'MONTHLY', (l_last_revw_date + 30),
2951 'WEEKLY', (l_last_revw_date + 7),
2952 l_last_revw_date + 1)
2953 WHERE party_id = l_party_id
2954 AND cust_account_id = l_cust_account_id
2955 AND site_use_id IS NULL;
2956
2957 EXCEPTION
2958 WHEN NO_DATA_FOUND
2959 THEN
2960 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2961 itemkey,'No data found in profile for updation',
2962 sqlerrm);
2963 raise;
2964
2965 WHEN OTHERS
2966 THEN
2967 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2968 itemkey,'Error accessing customer profiles',
2969 sqlerrm);
2970 raise;
2971
2972 END;
2973 END IF;
2974
2975
2976
2977 ELSIF l_site_use_id IS NOT NULL
2978 THEN
2979
2980 --get last review date for site use id not null.
2981 BEGIN
2982
2983 SELECT LAST_CREDIT_REVIEW_DATE
2984 INTO l_last_revw_date
2985 FROM hz_customer_profiles
2986 WHERE party_id = l_party_id
2987 AND cust_account_id = l_cust_account_id
2988 AND site_use_id = l_site_use_id;
2989
2990
2991
2992 EXCEPTION
2993 WHEN NO_DATA_FOUND
2994 THEN
2995 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2996 itemkey,'No data found in cust profile for update',
2997 sqlerrm);
2998 raise;
2999
3000 WHEN OTHERS
3001 THEN
3002 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
3003 itemkey,'Error accessing customer profile',
3004 sqlerrm);
3005 raise;
3006
3007 END;
3008
3009 IF l_last_revw_date IS NOT NULL
3010 THEN
3011 BEGIN
3012
3013 UPDATE hz_customer_profiles
3014 SET REVIEW_CYCLE = c_reco_rec.recommendation_value1,
3015 NEXT_CREDIT_REVIEW_DATE = DECODE(review_cycle,
3016 'YEARLY', (l_last_revw_date + 365),
3017 'HALF_YEARLY', (l_last_revw_date + 180),
3018 'QUARTERLY', (l_last_revw_date + 90),
3019 'MONTHLY', (l_last_revw_date + 30),
3020 'WEEKLY', (l_last_revw_date + 7),
3021 l_last_revw_date + 1)
3022 WHERE party_id = l_party_id
3023 AND cust_account_id = l_cust_account_id
3024 AND site_use_id IS NOT NULL;
3025
3026 EXCEPTION
3027 WHEN NO_DATA_FOUND
3028 THEN
3029 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
3030 itemkey,'No data found in profile for updation',
3031 sqlerrm);
3032 raise;
3033
3034 WHEN OTHERS
3035 THEN
3036 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
3037 itemkey,'Error accessing customer profiles',
3038 sqlerrm);
3039 raise;
3040
3041 END;
3042
3043 END IF;
3044
3045 END IF;
3046
3047 END IF;
3048
3049 END LOOP;
3050
3051 Update ar_cmgt_cf_recommends
3052 set status = 'I',
3053 last_update_date = sysdate,
3054 last_updated_by = fnd_global.user_id,
3055 last_update_login = fnd_global.login_id
3056 WHERE case_folder_id = l_case_folder_id;
3057
3058 Update ar_cmgt_case_folders
3059 set status = 'CLOSED',
3060 last_updated = sysdate,
3061 last_update_date = sysdate,
3062 last_updated_by = fnd_global.user_id,
3063 last_update_login = fnd_global.login_id
3064 WHERE case_folder_id = l_case_folder_id;
3065
3066 Update ar_cmgt_credit_requests
3067 set status = 'PROCESSED',
3068 last_update_date = sysdate,
3069 last_updated_by = fnd_global.user_id,
3070 last_update_login = fnd_global.login_id
3071 WHERE credit_request_id = itemkey;
3072 END IF;
3073 EXCEPTION
3074 WHEN HOLD_ERROR THEN
3075 raise;
3076 END;
3077
3078 PROCEDURE POST_IMPLEMENT_PROCESS(
3079 itemtype in varchar2,
3080 itemkey in varchar2,
3081 actid in number,
3082 funcmode in varchar2,
3083 resultout out NOCOPY varchar2) IS
3084
3085 l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
3086 l_party_id ar_cmgt_credit_requests.party_id%type;
3087 l_cust_account_id ar_cmgt_credit_requests.cust_account_id%type;
3088 l_site_use_id ar_cmgt_credit_requests.site_use_id%type;
3089 l_credit_limit NUMBER := 0;
3090 l_exposure NUMBER;
3091 l_risk_factor NUMBER;
3092 l_limit_currency ar_cmgt_case_folders.limit_currency%type;
3093 l_errmsg VARCHAR2(2000);
3094 l_resultout VARCHAR2(1);
3095 l_parent_credit_request_id NUMBER;
3096
3097 BEGIN
3098 IF funcmode = 'RUN'
3099 THEN
3100
3101 BEGIN
3102 SELECT party_id, decode(cust_account_id,-99,-1,cust_account_id),
3103 decode(site_use_id,-99,null,site_use_id)
3104 INTO l_party_id, l_cust_account_id, l_site_use_id
3105 FROM ar_cmgt_credit_requests
3106 WHERE credit_request_id = itemkey;
3107
3108 END;
3109 l_case_folder_id := WF_ENGINE.GetItemAttrNumber
3110 (itemtype => itemtype,
3111 itemkey => itemkey,
3112 aname => 'CASE_FOLDER_ID');
3113
3114 l_limit_currency := WF_ENGINE.getItemAttrText
3115 (itemtype => itemtype,
3116 itemkey => itemkey,
3117 aname => 'LIMIT_CURRENCY');
3118 /* Update the risk factor data point value */
3119
3120 BEGIN
3121 SELECT DECODE(to_number(NVL(cfd.data_point_value,'1')),0,1,NVL(cfd.data_point_value,'1'))
3122 INTO l_exposure
3123 FROM ar_cmgt_cf_dtls cfd
3124 WHERE cfd.case_folder_id = l_case_folder_id
3125 AND cfd.data_point_id = 34;
3126 EXCEPTION
3127 WHEN others THEN
3128 --If you dont put 1, this will cause a zero divide error
3129 l_exposure := 1;
3130 END;
3131
3132 BEGIN
3133 SELECT nvl(overall_credit_limit,0)
3134 INTO l_credit_limit
3135 FROM hz_cust_profile_amts hzp
3136 WHERE cust_account_profile_id = (
3137 SELECT cust_account_profile_id
3138 FROM hz_customer_profiles
3139 WHERE party_id = l_party_id
3140 AND cust_account_id = l_cust_account_id
3141 AND ( site_use_id IS NULL
3142 OR site_use_id = l_site_use_id))
3143 AND currency_code = l_limit_currency;
3144 EXCEPTION
3145 WHEN OTHERS THEN
3146 l_credit_limit := 0;
3147 END;
3148 l_risk_factor := (1 - round((l_credit_limit/l_exposure),2));
3149
3150
3151 AR_CMGT_CONTROLS.UPDATE_CASE_FOLDER_DETAILS (
3152 p_case_folder_id => l_case_folder_id,
3153 p_data_point_id => 182,
3154 p_data_point_value => l_risk_factor,
3155 p_score => NULL,
3156 p_errmsg => l_errmsg,
3157 p_resultout => l_resultout);
3158
3159 -- Now Release the parent credit request from HOLD(if any)
3160 BEGIN
3161 SELECT a.parent_credit_request_id
3162 INTO l_parent_credit_request_id
3163 FROM ar_cmgt_credit_requests a, ar_cmgt_credit_requests b
3164 WHERE a.credit_request_id = itemkey
3165 AND a.parent_credit_request_id = b.credit_request_id
3166 AND b.status <> 'PROCESSED';
3167
3168 -- rows exist, so start workflow
3169 ar_cmgt_wf_engine.start_workflow (
3170 p_credit_request_id => l_parent_credit_request_id,
3171 p_application_status => 'SUBMIT' );
3172
3173 EXCEPTION
3174 WHEN NO_DATA_FOUND THEN
3175 NULL;
3176 WHEN OTHERS THEN
3177 wf_core.context('AR_CMGT_WF_ENGINE','POST_IMPLEMENT_PROCESS',itemtype,
3178 itemkey,
3179 'Error while Getting Parent Credit Request Id',
3180 sqlerrm);
3181 raise;
3182 END;
3183
3184 END IF;
3185 END;
3186
3187
3188 PROCEDURE IMPLEMENT_CUSTOM_RECO(
3189 itemtype in varchar2,
3190 itemkey in varchar2,
3191 actid in number,
3192 funcmode in varchar2,
3193 resultout out NOCOPY varchar2) IS
3194
3195 l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
3196 l_reco_name ar_cmgt_cf_recommends.CREDIT_RECOMMENDATION%type;
3197 l_dayz AR_CMGT_SETUP_OPTIONS.CER_DSO_DAYS%TYPE;
3198 l_reco_id ar_cmgt_cf_recommends.RECOMMENDATION_ID%TYPE;
3199 CURSOR reco_check IS
3200 SELECT CREDIT_RECOMMENDATION,RECOMMENDATION_ID
3201 FROM AR_CMGT_CF_RECOMMENDS
3202 WHERE CREDIT_REQUEST_ID = itemkey;
3203 BEGIN
3204
3205 select case_folder_id
3206 INTO l_case_folder_id
3207 FROM ar_cmgt_case_folders
3208 WHERE credit_request_id = itemkey
3209 and type = 'CASE';
3210
3211 raise_recco_event(l_case_folder_id);
3212 EXCEPTION
3213 WHEN OTHERS THEN
3214 wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
3215 itemkey,'Error while raise Business Event',
3216 sqlerrm);
3217 raise;
3218
3219
3220
3221
3222 END;
3223
3224 PROCEDURE UPDATE_SKIP_APPROVAL_FLAG (
3225 itemtype in varchar2,
3226 itemkey in varchar2,
3227 actid in number,
3228 funcmode in varchar2,
3229 resultout out NOCOPY varchar2) IS
3230
3231 l_failure_function VARCHAR2(60);
3232 BEGIN
3233 l_failure_function := WF_ENGINE.getItemAttrText(
3234 itemtype => itemtype,
3235 itemkey => itemkey,
3236 aname => 'FAILURE_FUNCTION');
3237
3238 IF l_failure_function IS NULL OR l_failure_function <> 'SKIP_APPROVAL'
3239 THEN
3240 WF_ENGINE.setItemAttrText(
3241 itemtype => itemtype,
3242 itemkey => itemkey,
3243 aname => 'SKIP_APPROVAL',
3244 avalue => 'N');
3245 END IF;
3246
3247 END;
3248 PROCEDURE UPDATE_CF_TO_CREATE (
3249 itemtype in varchar2,
3250 itemkey in varchar2,
3251 actid in number,
3252 funcmode in varchar2,
3253 resultout out NOCOPY varchar2) IS
3254
3255 l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
3256 BEGIN
3257 l_case_folder_id := WF_ENGINE.GetItemAttrNumber
3258 (itemtype => itemtype,
3259 itemkey => itemkey,
3260 aname => 'CASE_FOLDER_ID');
3261
3262 UPDATE ar_cmgt_case_folders
3263 set status = 'CREATED',
3264 last_updated = sysdate,
3265 last_update_date = sysdate,
3266 last_updated_by = fnd_global.user_id,
3267 last_update_login = fnd_global.login_id
3268 WHERE case_folder_id = l_case_folder_id;
3269
3270 END;
3271
3272 /* This procedure is used tyo update a particular
3273 ** workflow item attribute, for eg. credit_analyst_id */
3274 PROCEDURE UPDATE_WF_ATTRIBUTE (
3275 p_itemkey IN VARCHAR2,
3276 p_attribute_type IN VARCHAR2,
3277 p_attribute_name IN VARCHAR2,
3278 p_attribute_value IN VARCHAR2 ) IS
3279
3280 l_person_id per_people_f.person_id%type;
3281 l_user_name VARCHAR2(60);
3282 l_display_name VARCHAR2(240);
3283
3284 BEGIN
3285 IF p_attribute_type = 'NUMBER'
3286 THEN
3287 WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
3288 itemKey => p_itemkey,
3289 aname => p_attribute_name,
3290 avalue => p_attribute_value);
3291 ELSIF p_attribute_type = 'TEXT'
3292 THEN
3293 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3294 itemKey => p_itemkey,
3295 aname => p_attribute_name,
3296 avalue => p_attribute_value);
3297 END IF;
3298 IF p_attribute_name = 'CREDIT_ANALYST_ID'
3299 THEN
3300 l_person_id := ar_cmgt_util.get_person_based_on_resource (
3301 l_resource_id => to_number(p_attribute_value));
3302
3303 -- now get credit_ananlyst details
3304 get_employee_details(l_person_id,l_user_name, l_display_name);
3305 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3306 itemKey => p_itemkey,
3307 aname => 'CREDIT_ANALYST_USER_NAME',
3308 avalue => l_user_name);
3309 WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3310 itemKey => p_itemkey,
3311 aname => 'CREDIT_ANALYST_DISPLAY_NAME',
3312 avalue => l_display_name);
3313
3314 END IF;
3315 END UPDATE_WF_ATTRIBUTE;
3316
3317 PROCEDURE CHECK_CHILD_REQ_COMPLETED(
3318 itemtype in varchar2,
3319 itemkey in varchar2,
3320 actid in number,
3321 funcmode in varchar2,
3322 resultout out NOCOPY varchar2) IS
3323
3324 l_credit_request_id ar_cmgt_credit_requests.credit_request_id%type;
3325 l_credit_request_type ar_cmgt_credit_requests.credit_request_type%type;
3326 l_status ar_cmgt_case_folders.status%type;
3327
3328 BEGIN
3329 IF funcmode = 'RUN'
3330 THEN
3331 BEGIN
3332 SELECT r.credit_request_id, r.credit_request_type, r.status
3333 INTO l_credit_request_id, l_credit_request_type, l_status
3334 FROM ar_cmgt_credit_requests r
3335 WHERE r.parent_credit_request_id = itemkey
3336 AND r.status <> 'PROCESSED';
3337
3338 resultout := 'COMPLETE:N';
3339
3340 EXCEPTION
3341 WHEN NO_DATA_FOUND THEN
3342 -- mean no credit request exists
3343 resultout := 'COMPLETE:Y';
3344 WHEN TOO_MANY_ROWS THEN
3345 -- mean no credit request exists
3346 resultout := 'COMPLETE:N';
3347 WHEN OTHERS THEN
3348 wf_core.context('AR_CMGT_WF_ENGINE','CHECK_CHILD_REQ_COMPLETED',itemtype,
3349 itemkey,'Error while Checking Child Credit Requests',
3350 sqlerrm);
3351 raise;
3352 END;
3353 END IF;
3354 END;
3355
3356 PROCEDURE VALIDATE_RECOMMENDATIONS(
3357 itemtype in varchar2,
3358 itemkey in varchar2,
3359 actid in number,
3360 funcmode in varchar2,
3361 resultout out NOCOPY varchar2) IS
3362
3363 l_case_folder_id ar_cmgt_case_folders.case_folder_id%TYPE;
3364 l_reco_exist VARCHAR2(1) := 'N';
3365 l_credit_request_type ar_cmgt_credit_requests.credit_request_type%type;
3366 l_flag varchar2(1);
3367 UNEXP_ERROR EXCEPTION;
3368 --this will check if recommendations exists or not.
3369 CURSOR CHECK_RECO_EXISTS IS
3370 SELECT 'Y'
3371 FROM ar_cmgt_cf_recommends
3372 WHERE CASE_FOLDER_ID=l_case_folder_id;
3373
3374 BEGIN
3375 IF funcmode = 'RUN'
3376 THEN
3377
3378 -- first validate for guarantors credit request
3379 -- there won't be any recommendations
3380 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3381 itemkey => itemkey,
3382 aname => 'FAILURE_FUNCTION',
3383 avalue => 'VALIDATE_RECO');
3384
3385 l_case_folder_id := WF_ENGINE.GetItemAttrNumber
3386 (itemtype => itemtype,
3387 itemkey => itemkey,
3388 aname => 'CASE_FOLDER_ID');
3389 l_credit_request_type := WF_ENGINE.GetItemAttrText
3390 (itemtype => itemtype,
3391 itemkey => itemkey,
3392 aname => 'CREDIT_REQUEST_TYPE');
3393 --fetch the cursors
3394 OPEN CHECK_RECO_EXISTS;
3395 FETCH CHECK_RECO_EXISTS INTO l_reco_exist;
3396 CLOSE CHECK_RECO_EXISTS;
3397
3398 -- if the request type is Guarantor then reco. is not allowed
3399 IF nvl(l_credit_request_type,'CREDIT_APP') = 'GUARANTOR' and l_reco_exist = 'Y'
3400 THEN
3401 resultout := 'COMPLETE:FAILURE';
3402 return;
3403 END IF;
3404
3405 --check both the flags before setting the
3406 -- generate flag to generate recommendation
3407 --cause if the recommendations aleready exist
3408 --we do not need to generate them. And in case
3409 --recommendations exists for guarantor then
3410 --we need to raise the error which will be raised
3411 --while validating the recommendations.
3412
3413 IF l_reco_exist = 'N' and nvl(l_credit_request_type,'CREDIT_APP') <> 'GUARANTOR'
3414 THEN
3415 --call the program for populating the recommendations
3416 --before validating.
3417 AR_CMGT_WF_ENGINE.GET_EXT_SCORE_RECOMMENDATIONS(
3418 itemtype => itemtype,
3419 itemkey => itemkey,
3420 p_cf_id => l_case_folder_id,
3421 resultout=> resultout);
3422 IF resultout <> 0
3423 THEN
3424
3425 raise UNEXP_ERROR;
3426 END IF;
3427 END IF;
3428
3429
3430 -- do the next validations
3431 BEGIN
3432 SELECT 'X'
3433 INTO l_flag
3434 FROM ar_cmgt_cf_recommends cf, ar_cmgt_credit_requests req
3435 WHERE req.credit_request_id = itemkey
3436 AND req.credit_request_id = cf.credit_request_id
3437 AND req.cust_account_id = -99
3438 AND req.site_use_id = -99
3439 AND cf.credit_recommendation = 'CUST_HOLD';
3440
3441 resultout := 'COMPLETE:FAILURE';
3442 return;
3443
3444 EXCEPTION
3445 WHEN NO_DATA_FOUND THEN
3446 resultout := 'COMPLETE:SUCESS';
3447 WHEN TOO_MANY_ROWS THEN
3448 resultout := 'COMPLETE:FAILURE';
3449 return;
3450 WHEN OTHERS THEN
3451 wf_core.context('AR_CMGT_WF_ENGINE','VALIDATE_RECOMMENDATIONS',itemtype,
3452 itemkey,'Error while validating Recommendations for Party',
3453 sqlerrm);
3454 raise;
3455
3456 END;
3457 -- validate again whether reco. has been created or not
3458 OPEN CHECK_RECO_EXISTS;
3459 FETCH CHECK_RECO_EXISTS INTO l_reco_exist;
3460 CLOSE CHECK_RECO_EXISTS;
3461 IF l_reco_exist = 'Y' AND
3462 nvl(l_credit_request_type,'CREDIT_APP') <> 'GUARANTOR'
3463 THEN
3464 resultout := 'COMPLETE:SUCESS';
3465 ELSIF nvl(l_credit_request_type,'CREDIT_APP') = 'GUARANTOR' and l_reco_exist = 'Y'
3466 THEN
3467 resultout := 'COMPLETE:FAILURE';
3468 ELSIF nvl(l_credit_request_type,'CREDIT_APP') = 'GUARANTOR' and l_reco_exist = 'N'
3469 THEN
3470 resultout := 'COMPLETE:SUCESS';
3471 ELSE
3472 resultout := 'COMPLETE:FAILURE';
3473 END IF;
3474 END IF;
3475 EXCEPTION
3476 WHEN UNEXP_ERROR THEN
3477 wf_core.context('AR_CMGT_WF_ENGINE','VALIDATE_RECOMMENDATIONS',itemtype,
3478 itemkey,'Error while populating recommendation ',
3479 sqlerrm);
3480 raise;
3481 END;
3482
3483 PROCEDURE MARK_REQUEST_ON_HOLD (
3484 itemtype in varchar2,
3485 itemkey in varchar2,
3486 actid in number,
3487 funcmode in varchar2,
3488 resultout out NOCOPY varchar2) IS
3489
3490 BEGIN
3491 IF funcmode = 'RUN'
3492 THEN
3493
3494 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3495 itemkey => itemkey,
3496 aname => 'MANUAL_ANALYSIS_FLAG',
3497 avalue => 'H');
3498 END IF;
3499 END;
3500
3501 /*This procedure Duplicates all the data in case of Appeal/Re-Submit
3502 ----------------------------------------------------------------------*/
3503 procedure APPEAL_RESUB_DECISION(
3504 itemtype in varchar2,
3505 itemkey in varchar2,
3506 actid in number,
3507 funcmode in varchar2,
3508 resultout out NOCOPY varchar2) IS
3509
3510 l_case_folder_tbl_exp EXCEPTION;
3511 l_case_folder_dtls_exp EXCEPTION;
3512 l_aging_data EXCEPTION;
3513 l_dnb_data EXCEPTION;
3514 l_financial_data EXCEPTION;
3515 l_trade_data EXCEPTION;
3516 l_bank_data EXCEPTION;
3517 l_collateral_data EXCEPTION;
3518 l_other_data EXCEPTION;
3519 l_reco_data EXCEPTION;
3520 l_errmsg VARCHAR2(2000);
3521 l_resultout VARCHAR2(1);
3522 l_processng_flag VARCHAR2(1);
3523 l_case_rec_num NUMBER;
3524 l_credit_type ar_cmgt_credit_requests.credit_type%type;
3525 l_credit_request_type ar_cmgt_credit_requests.credit_request_type%type;
3526 l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
3527 l_parent_cf_id ar_cmgt_case_folders.case_folder_id%type;
3528 l_parent_creq_id ar_cmgt_credit_requests.parent_credit_request_id%type;
3529 insert_failure EXCEPTION;
3530 l_cf_id ar_cmgt_case_folders.case_folder_id%type;
3531 populate_failure EXCEPTION;
3532
3533
3534 BEGIN
3535 IF funcmode = 'RUN'
3536 THEN
3537
3538 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3539 itemkey => itemkey,
3540 aname => 'FAILURE_FUNCTION',
3541 avalue => 'DUPLICATE_CASE_FOLDER');
3542
3543
3544 --initialise the variables
3545 l_processng_flag := 'N';
3546
3547 -- get credit type from credit requests
3548 BEGIN
3549
3550 SELECT credit_request_type
3551 INTO l_credit_request_type
3552 FROM ar_cmgt_credit_requests
3553 WHERE credit_request_id = itemkey;
3554 EXCEPTION
3555 WHEN OTHERS THEN
3556 wf_core.context ('AR_CMGT_WF_ENGINE','APPEAL_RESUB_DECISION',itemtype,itemkey,
3557 'Error while getting records from AR_CMGT_CREDIT_REQUESTS',
3558 'Sql Error: '||sqlerrm);
3559 raise;
3560 END;
3561
3562 -- If the request is not "Appeal" or "Re-Submit"
3563 --then it follows the normal path.
3564
3565 IF l_credit_request_type = 'APPEAL'
3566 OR l_credit_request_type = 'RESUBMIT'
3567 OR l_credit_request_type = 'APPEAL_REJECTION'
3568 THEN
3569 l_processng_flag := 'Y';
3570 ELSE
3571 resultout := 'COMPLETE:N';
3572 return;
3573 END IF;
3574
3575 -- The request is appeal and need to process
3576 IF l_processng_flag = 'Y'
3577 THEN
3578
3579 --get parent credit request id
3580
3581 BEGIN
3582
3583 SELECT PARENT_CREDIT_REQUEST_ID
3584 INTO l_parent_creq_id
3585 FROM AR_CMGT_CREDIT_REQUESTS
3586 WHERE CREDIT_REQUEST_ID = itemkey;
3587
3588 EXCEPTION
3589 WHEN OTHERS THEN
3590 wf_core.context ('AR_CMGT_WF_ENGINE','APPEAL_RESUB_DECISION',itemtype,itemkey,
3591 'Error while getting parent records from AR_CMGT_CREDIT_REQUESTS',
3592 'Sql Error: '||sqlerrm);
3593 raise;
3594 END;
3595
3596 --get case folder id for parent credit request
3597
3598 BEGIN
3599
3600 SELECT CASE_FOLDER_ID
3601 INTO l_parent_cf_id
3602 FROM AR_CMGT_CASE_FOLDERS
3603 WHERE CREDIT_REQUEST_ID = l_parent_creq_id
3604 and type = 'CASE';
3605
3606 EXCEPTION
3607 WHEN OTHERS THEN
3608 wf_core.context ('AR_CMGT_WF_ENGINE','APPEAL_RESUB_DECISION',itemtype,itemkey,
3609 'Error while getting parent records from AR_CMGT_CASE_FOLDERS',
3610 'Sql Error: '||sqlerrm);
3611 raise;
3612 END;
3613
3614 --generate case folder for new credit request.
3615
3616 AR_CMGT_CONTROLS.DUPLICATE_CASE_FOLDER_TBL(
3617 p_parnt_case_folder_id => l_parent_cf_id,
3618 p_credit_request_id => to_number(itemkey),
3619 p_errmsg => l_errmsg,
3620 p_resultout => l_resultout);
3621 IF l_resultout <> 0
3622 THEN
3623
3624 raise l_case_folder_tbl_exp;
3625
3626 END IF;
3627
3628
3629 --populate the details of newly created case folder.
3630 IF l_resultout <> 0
3631 THEN
3632 raise populate_failure;
3633 END IF;
3634
3635 AR_CMGT_CONTROLS.DUPLICATE_CASE_FOLDER_DTLS(
3636 p_parnt_case_folder_id => l_parent_cf_id,
3637 p_credit_request_id => to_number(itemkey),
3638 p_errmsg => l_errmsg,
3639 p_resultout => l_resultout);
3640
3641 IF l_resultout <> 0
3642 THEN
3643 raise l_case_folder_dtls_exp;
3644 END IF;
3645
3646
3647 --populate AR_CMGT_CF_AGING_DTLS
3648
3649 AR_CMGT_CONTROLS.DUPLICATE_AGING_DATA(
3650 p_parnt_case_folder_id => l_parent_cf_id,
3651 p_credit_request_id => to_number(itemkey),
3652 p_errmsg => l_errmsg,
3653 p_resultout => l_resultout);
3654
3655 IF l_resultout <> 0
3656 THEN
3657
3658 raise l_aging_data;
3659 END IF;
3660
3661 --populate ar_cmgt_cf_dnb_dtls
3662
3663 AR_CMGT_CONTROLS.DUPLICATE_DNB_DATA(
3664 p_parnt_case_folder_id => l_parent_cf_id,
3665 p_credit_request_id => to_number(itemkey),
3666 p_errmsg => l_errmsg,
3667 p_resultout => l_resultout);
3668
3669 IF l_resultout <> 0
3670 THEN
3671
3672 raise l_dnb_data;
3673 END IF;
3674
3675 --call the routine to populate all the wf_attributes
3676 AR_CMGT_WF_ENGINE.POPULATE_WF_ATTRIBUTES(
3677 itemtype => itemtype,
3678 itemkey => itemkey,
3679 actid => actid,
3680 funcmode => funcmode,
3681 p_called_from => 'APPEAL',
3682 resultout => l_resultout);
3683 --populate AR_CMGT_CF_ANL_NOTES
3684
3685 AR_CMGT_CONTROLS.DUPLICATE_NOTES_DATA(
3686 p_parnt_case_folder_id => l_parent_cf_id,
3687 p_credit_request_id => to_number(itemkey),
3688 p_errmsg => l_errmsg,
3689 p_resultout => l_resultout);
3690
3691 IF l_resultout <> 0
3692 THEN
3693
3694 raise l_dnb_data;
3695 END IF;
3696
3697 --populate ar_cmgt_financial_data
3698
3699 AR_CMGT_CONTROLS.DUPLICATE_FINANCIAL_DATA(
3700 p_parnt_credit_req_id => l_parent_creq_id,
3701 p_credit_request_id => to_number(itemkey),
3702 p_errmsg => l_errmsg,
3703 p_resultout => l_resultout);
3704
3705 IF l_resultout <> 0
3706 THEN
3707
3708 raise l_financial_data;
3709 END IF;
3710
3711
3712 --populate ar_cmgt_trade_ref_data
3713
3714 AR_CMGT_CONTROLS.DUPLICATE_TRADE_DATA(
3715 p_parnt_credit_req_id => l_parent_creq_id,
3716 p_credit_request_id => to_number(itemkey),
3717 p_errmsg => l_errmsg,
3718 p_resultout => l_resultout);
3719
3720 IF l_resultout <> 0
3721 THEN
3722
3723 raise l_trade_data;
3724 END IF;
3725
3726
3727 --populate ar_cmgt_bank_ref_data
3728
3729 AR_CMGT_CONTROLS.DUPLICATE_BANK_DATA(
3730 p_parnt_credit_req_id => l_parent_creq_id,
3731 p_credit_request_id => to_number(itemkey),
3732 p_errmsg => l_errmsg,
3733 p_resultout => l_resultout);
3734
3735 IF l_resultout <> 0
3736 THEN
3737
3738 raise l_bank_data;
3739 END IF;
3740
3741
3742 --populate ar_cmgt_collateral_data
3743
3744 AR_CMGT_CONTROLS.DUPLICATE_COLLATERAL_DATA(
3745 p_parnt_credit_req_id => l_parent_creq_id,
3746 p_credit_request_id => to_number(itemkey),
3747 p_errmsg => l_errmsg,
3748 p_resultout => l_resultout);
3749
3750 IF l_resultout <> 0
3751 THEN
3752
3753 raise l_collateral_data;
3754 END IF;
3755
3756
3757 --populate ar_cmgt_other_data
3758
3759 AR_CMGT_CONTROLS.DUPLICATE_OTHER_DATA(
3760 p_parnt_credit_req_id => l_parent_creq_id,
3761 p_credit_request_id => to_number(itemkey),
3762 p_errmsg => l_errmsg,
3763 p_resultout => l_resultout);
3764
3765 IF l_resultout <> 0
3766 THEN
3767
3768 raise l_other_data;
3769 END IF;
3770
3771 --populate ar_cmgt_cf_recommends
3772
3773 AR_CMGT_CONTROLS.DUPLICATE_RECO_DATA(
3774 p_parnt_case_folder_id => to_number(l_parent_cf_id),
3775 p_credit_request_id => to_number(itemkey),
3776 p_errmsg => l_errmsg,
3777 p_resultout => l_resultout);
3778
3779 IF l_resultout <> 0
3780 THEN
3781
3782 raise l_reco_data;
3783 END IF;
3784
3785
3786 IF l_resultout = 0
3787 THEN
3788 resultout := 'COMPLETE:Y';
3789 return;
3790 END IF;
3791
3792
3793 END IF;
3794 END IF;
3795 END APPEAL_RESUB_DECISION;
3796
3797 /*This procedure populates all the wf_attributes
3798 -------------------------------------------------------------------------------------*/
3799 PROCEDURE POPULATE_WF_ATTRIBUTES (
3800 itemtype in varchar2,
3801 itemkey in varchar2,
3802 actid in number,
3803 funcmode in varchar2,
3804 p_called_from IN VARCHAR2,
3805 resultout out NOCOPY varchar2) IS
3806
3807 l_credit_classification ar_cmgt_check_lists.credit_classification%TYPE;
3808 l_review_type ar_cmgt_check_lists.review_type%TYPE;
3809 l_check_list_id ar_cmgt_check_lists.check_list_id%TYPE;
3810 l_score_model_id ar_cmgt_scores.score_model_id%TYPE;
3811 l_currency ar_cmgt_credit_requests.limit_currency%TYPE;
3812 l_amount_requested ar_cmgt_credit_requests.limit_amount%TYPE;
3813 l_case_folder_number ar_cmgt_case_folders.case_folder_number%type;
3814 l_source_name ar_cmgt_credit_requests.source_name%type;
3815 l_classification_meaning ar_lookups.meaning%type;
3816 l_review_type_meaning ar_lookups.meaning%type;
3817 l_application_number ar_cmgt_credit_requests.application_number%type;
3818 l_score_model_already_set VARCHAR2(1) := 'F';
3819 l_requestor_id ar_cmgt_credit_requests.requestor_id%type;
3820 l_requestor_user_name fnd_user.user_name%type;
3821 l_requestor_display_name per_people_f.full_name%type;
3822 l_party_id hz_parties.party_id%type;
3823 l_cust_account_id hz_cust_accounts.cust_account_id%type;
3824 l_party_name hz_parties.party_name%type;
3825 l_party_number hz_parties.party_number%type;
3826 l_account_number hz_cust_accounts.account_number%type;
3827 l_application_date ar_cmgt_credit_requests.application_date%type;
3828 l_source_column1 ar_cmgt_credit_requests.source_column1%type;
3829 l_source_column2 ar_cmgt_credit_requests.source_column2%type;
3830 l_source_column3 ar_cmgt_credit_requests.source_column3%type;
3831 l_notes ar_cmgt_credit_requests.notes%type;
3832 l_case_folder_id ar_cmgt_case_folders.case_folder_id%TYPE;
3833 l_limit_currency ar_cmgt_case_folders.limit_currency%TYPE;
3834 l_creation_date_time ar_cmgt_case_folders.creation_date_time%TYPE;
3835 l_requestor_type ar_cmgt_credit_requests.requestor_type%TYPE;
3836 BEGIN
3837 IF funcmode = 'RUN'
3838 THEN
3839
3840 BEGIN
3841 SELECT req.credit_classification, req.review_type,
3842 nvl(req.limit_currency, trx_currency),
3843 nvl(nvl(req.limit_amount,req.trx_amount),0),
3844 req.case_folder_number, req.score_model_id, req.source_name,
3845 req.application_number,
3846 lkp1.meaning classification_meaning,
3847 lkp2.meaning review_type_meaning,
3848 requestor_id,
3849 application_date,
3850 req.party_id,
3851 cust_account_id,
3852 source_column1,
3853 source_column2,
3854 source_column3,
3855 party.party_name,
3856 party.party_number,
3857 req.notes,
3858 nvl(req.requestor_type, 'EMPLOYEE')
3859 INTO l_credit_classification, l_review_type, l_currency,
3860 l_amount_requested, l_case_folder_number, l_score_model_id,
3861 l_source_name, l_application_number,
3862 l_classification_meaning,
3863 l_review_type_meaning,
3864 l_requestor_id,
3865 l_application_date,
3866 l_party_id,
3867 l_cust_account_id,
3868 l_source_column1,
3869 l_source_column2,
3870 l_source_column3,
3871 l_party_name,
3872 l_party_number,
3873 l_notes,
3874 l_requestor_type
3875 FROM ar_cmgt_credit_requests req,
3876 ar_lookups lkp1,
3877 ar_lookups lkp2,
3878 hz_parties party
3879 WHERE req.credit_request_id = itemkey
3880 AND req.party_id = party.party_id
3881 AND lkp1.lookup_type = 'AR_CMGT_CREDIT_CLASSIFICATION'
3882 AND lkp1.lookup_code = req.credit_classification
3883 AND lkp2.lookup_type = 'AR_CMGT_REVIEW_TYPE'
3884 AND lkp2.lookup_code = req.review_type;
3885 EXCEPTION
3886 WHEN NO_DATA_FOUND THEN
3887 SELECT req.credit_classification, req.review_type, req.application_number,
3888 req.score_model_id,
3889 application_date,
3890 req.party_id,
3891 cust_account_id,
3892 source_column1,
3893 source_column2,
3894 source_column3,
3895 party.party_name,
3896 party.party_number,
3897 req.notes,
3898 req.requestor_id,
3899 req.source_name,
3900 req.case_folder_number,
3901 nvl(req.limit_currency, trx_currency),
3902 nvl(nvl(req.limit_amount,req.trx_amount),0),
3903 nvl(req.requestor_type, 'EMPLOYEE')
3904 INTO l_credit_classification, l_review_type, l_application_number,
3905 l_score_model_id,
3906 l_application_date,
3907 l_party_id,
3908 l_cust_account_id,
3909 l_source_column1,
3910 l_source_column2,
3911 l_source_column3,
3912 l_party_name,
3913 l_party_number,
3914 l_notes,
3915 l_requestor_id,
3916 l_source_name,
3917 l_case_folder_number,
3918 l_currency,
3919 l_amount_requested,
3920 l_requestor_type
3921 FROM ar_cmgt_credit_requests req,
3922 hz_parties party
3923 WHERE credit_request_id = itemkey
3924 AND req.party_id = party.party_id;
3925 WHEN OTHERS THEN
3926 wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTES',itemtype,itemkey,
3927 sqlerrm);
3928 raise;
3929 END;
3930
3931 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
3932 itemkey => itemkey,
3933 aname => 'PARTY_ID',
3934 avalue => l_party_id );
3935
3936 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3937 itemkey => itemkey,
3938 aname => 'CREDIT_CLASSIFICATION',
3939 avalue => l_credit_classification );
3940 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3941 itemkey => itemkey,
3942 aname => 'REVIEW_TYPE',
3943 avalue => l_review_type );
3944 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3945 itemkey => itemkey,
3946 aname => 'CURRENCY',
3947 avalue => l_currency );
3948 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3949 itemkey => itemkey,
3950 aname => 'SOURCE_NAME',
3951 avalue => l_source_name );
3952 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3953 itemkey => itemkey,
3954 aname => 'APPLICATION_NUMBER',
3955 avalue => l_application_number );
3956 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
3957 itemkey => itemkey,
3958 aname => 'REQUESTED_CREDIT_LIMIT',
3959 avalue => l_amount_requested );
3960 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
3961 itemkey => itemkey,
3962 aname => 'REQUESTOR_PERSON_ID',
3963 avalue => l_requestor_id );
3964 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3965 itemkey => itemkey,
3966 aname => 'SOURCE_COL1',
3967 avalue => l_source_column1 );
3968 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3969 itemkey => itemkey,
3970 aname => 'SOURCE_COL2',
3971 avalue => l_source_column2 );
3972 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3973 itemkey => itemkey,
3974 aname => 'SOURCE_COL3',
3975 avalue => l_source_column3 );
3976 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3977 itemkey => itemkey,
3978 aname => 'PARTY_NAME',
3979 avalue => l_party_name );
3980 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3981 itemkey => itemkey,
3982 aname => 'PARTY_NUMBER',
3983 avalue => l_party_number );
3984 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3985 itemkey => itemkey,
3986 aname => 'APPL_NOTES',
3987 avalue => l_notes );
3988 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
3989 itemkey => itemkey,
3990 aname => 'APPLICATION_DATE',
3991 avalue => l_application_date );
3992
3993 IF l_requestor_type = 'EMPLOYEE'
3994 THEN
3995 get_employee_details(
3996 p_employee_id => l_requestor_id,
3997 p_user_name => l_requestor_user_name,
3998 p_display_name => l_requestor_display_name);
3999
4000 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4001 itemkey => itemkey,
4002 aname => 'REQUESTOR_USER_NAME',
4003 avalue => l_requestor_user_name );
4004 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4005 itemkey => itemkey,
4006 aname => 'REQUESTOR_DISPLAY_NAME',
4007 avalue => l_requestor_display_name );
4008 ELSE
4009 -- get user id
4010 BEGIN
4011 SELECT user_name
4012 INTO l_requestor_user_name
4013 FROM fnd_user
4014 WHERE user_id = l_requestor_id;
4015
4016 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4017 itemkey => itemkey,
4018 aname => 'REQUESTOR_USER_NAME',
4019 avalue => l_requestor_user_name );
4020
4021 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4022 itemkey => itemkey,
4023 aname => 'REQUESTOR_DISPLAY_NAME',
4024 avalue => l_requestor_user_name );
4025 EXCEPTION
4026 WHEN NO_DATA_FOUND THEN
4027 wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTE',itemtype,itemkey,
4028 'FND User Not Found'|| sqlerrm);
4029 raise;
4030 WHEN OTHERS THEN
4031 wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTE',itemtype,itemkey,
4032 'Other Error '|| sqlerrm);
4033 raise;
4034 END;
4035 END IF;
4036 -- check if the application is on accounts level and set the account Number
4037 IF l_cust_account_id <> -99
4038 THEN
4039 BEGIN
4040 SELECT ACCOUNT_NUMBER
4041 INTO l_account_number
4042 FROM hz_cust_accounts
4043 WHERE cust_account_id = l_cust_account_id;
4044 EXCEPTION
4045 WHEN NO_DATA_FOUND THEN
4046 l_account_number := null;
4047 WHEN OTHERS THEN
4048 wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTES',itemtype,itemkey,
4049 'Getting Account Details SqlError: '|| sqlerrm);
4050 raise;
4051
4052 END;
4053 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4054 itemkey => itemkey,
4055 aname => 'ACCOUNT_NUMBER',
4056 avalue => l_account_number );
4057 END IF;
4058
4059
4060 IF p_called_from = 'APPEAL'
4061 THEN
4062 -- Get case folder details
4063 BEGIN
4064 SELECT case_folder_id, case_folder_number, check_list_id, score_model_id,
4065 limit_currency, creation_date_time
4066 INTO l_case_folder_id, l_case_folder_number, l_check_list_id, l_score_model_id,
4067 l_limit_currency, l_creation_date_time
4068 FROM ar_cmgt_case_folders
4069 WHERE credit_request_id = itemkey
4070 AND type = 'CASE';
4071
4072 WF_ENGINE.setItemAttrNumber
4073 (itemtype => itemtype,
4074 itemkey => itemkey,
4075 aname => 'CASE_FOLDER_ID',
4076 avalue => l_case_folder_id);
4077
4078 WF_ENGINE.setItemAttrText
4079 (itemtype => itemtype,
4080 itemkey => itemkey,
4081 aname => 'LIMIT_CURRENCY',
4082 avalue => l_limit_currency);
4083
4084
4085 WF_ENGINE.setItemAttrText
4086 (itemtype => itemtype,
4087 itemkey => itemkey,
4088 aname => 'CASE_FOLDER_NUMBER',
4089 avalue => l_case_folder_number);
4090
4091
4092 WF_ENGINE.setItemAttrDate
4093 (itemtype => itemtype,
4094 itemkey => itemkey,
4095 aname => 'CASE_FOLDER_DATE',
4096 avalue => l_creation_date_time);
4097
4098 WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
4099 itemkey => itemkey,
4100 aname => 'CHECK_LIST_ID',
4101 avalue => l_check_list_id);
4102
4103 WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
4104 itemkey => itemkey,
4105 aname => 'SCORE_MODEL_ID',
4106 avalue => l_score_model_id);
4107 EXCEPTION
4108 WHEN OTHERS THEN
4109 wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTES',itemtype,itemkey,
4110 'Error while getting Case Folder Details, SqlError: '|| sqlerrm);
4111 raise;
4112 END;
4113 END IF;
4114 END IF;
4115 END;
4116
4117 /* ***************************************************
4118 Here we check for the data point External Score *
4119 *If the Data point Name (Will substitute it later *
4120 *with data Point Id.) is External Score.The Workflow *
4121 *will call the Conc. program and raise the business *
4122 *event. *
4123 ******************************************************/
4124 procedure CHECK_EXTRNAL_DATA_POINTS(
4125 itemtype in varchar2,
4126 itemkey in varchar2,
4127 actid in number,
4128 funcmode in varchar2,
4129 resultout out NOCOPY varchar2) IS
4130
4131 l_score_model_id NUMBER;
4132 l_case_folder_id NUMBER;
4133 l_data_point_id NUMBER;
4134 l_data_point_value ar_cmgt_cf_dtls.data_point_value%type;
4135 l_data_point_type VARCHAR2(255);
4136 l_null_zero_flag VARCHAR2(1);
4137 l_success_flg VARCHAR2(1);
4138 l_data_point_name AR_CMGT_SCORABLE_DATA_POINTS_V.DATA_POINT_NAME%type;
4139 l_raise_event VARCHAR2(1);
4140 l_cf_number ar_cmgt_case_folders.case_folder_number%type;
4141 l_request_id NUMBER;
4142 l_list WF_PARAMETER_LIST_T;
4143 l_param WF_PARAMETER_T;
4144 l_key VARCHAR2(240);
4145 l_event_name VARCHAR2(240) := 'oracle.apps.ar.cmgt.CaseFolder.extract';
4146 UNEXP_ERROR EXCEPTION;
4147
4148 CURSOR dp_id_collec IS
4149 select sc.data_point_id, dp.data_point_code
4150 from ar_cmgt_score_dtls sc, ar_cmgt_data_points_vl dp
4151 where sc.score_model_id= l_score_model_id
4152 AND sc.data_point_id = dp.data_point_id
4153 AND dp.data_point_code = 'OCM_EXTERNAL_SCORE';
4154
4155 BEGIN
4156 IF funcmode = 'RUN'
4157 THEN
4158
4159 --initialize raise BE flag to 'N'
4160 l_raise_event:='N';
4161
4162 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4163 itemkey => itemkey,
4164 aname => 'FAILURE_FUNCTION',
4165 avalue => 'CHECK_EXTRNAL_DATA_POINTS');
4166 l_score_model_id := WF_ENGINE.GetItemAttrNumber
4167 (itemtype => itemtype,
4168 itemkey => itemkey,
4169 aname => 'SCORE_MODEL_ID');
4170 l_case_folder_id := WF_ENGINE.GetItemAttrNumber
4171 (itemtype => itemtype,
4172 itemkey => itemkey,
4173 aname => 'CASE_FOLDER_ID');
4174 IF pg_debug = 'Y'
4175 THEN
4176 debug('In Procedure CHECK_EXTRNAL_DATA_POINTS ++ for case folder id:'||l_case_folder_id);
4177 END IF;
4178 --get the data point data name(Id) for each data point
4179 --and compare to check if External Score data point Exists.
4180
4181 FOR dp_id_collec_rec IN dp_id_collec
4182 LOOP
4183
4184 IF dp_id_collec_rec.data_point_code = 'OCM_EXTERNAL_SCORE'
4185 THEN
4186 l_raise_event := 'Y';
4187 END IF;
4188
4189 END LOOP;
4190 -- Check for the result if result is Success
4191 -- and raise aise BE flag is 'Y' spawn the Conc. program and
4192 -- then raise the BE
4193 IF l_raise_event = 'Y'
4194 THEN
4195 l_request_id :=0;
4196 submit_xml_case_folder (
4197 p_case_folder_id => l_case_folder_id,
4198 p_request_id => l_request_id);
4199
4200 IF l_request_id <> 0
4201 THEN
4202 resultout := 'COMPLETE:Y';
4203 UPDATE ar_cmgt_case_folders
4204 SET request_id = l_request_id,
4205 status = 'IN_PROCESS',
4206 last_update_date = sysdate,
4207 last_updated_by = fnd_global.user_id
4208 WHERE case_folder_id = l_case_folder_id;
4209 return;
4210 ELSE
4211 resultout := 'COMPLETE:N';
4212 raise UNEXP_ERROR;
4213 END IF;
4214 ELSIF l_raise_event = 'N'
4215 THEN
4216 resultout := 'COMPLETE:N';
4217 END IF;
4218 END IF;
4219 EXCEPTION
4220 WHEN UNEXP_ERROR THEN
4221 wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_EXTRNAL_DATA_POINTS',itemtype,itemkey,
4222 'Error while submitting Con. Request Id, SqlError: '|| sqlerrm);
4223 raise;
4224 END;
4225 PROCEDURE GET_EXT_SCORE_RECOMMENDATIONS(
4226 itemtype in varchar2,
4227 itemkey in varchar2,
4228 p_cf_id in NUMBER,
4229 resultout out NOCOPY varchar2) IS
4230
4231 l_score NUMBER;
4232 l_score_model_id ar_cmgt_case_folders.score_model_id%type;
4233 l_credit_type ar_cmgt_credit_requests.credit_type%type;
4234 l_resultout VARCHAR2(1);
4235 l_errmsg VARCHAR2(32767);
4236 l_credit_limit NUMBER := 0;
4237 insert_failure EXCEPTION;
4238 l_auto_rules_id ar_cmgt_auto_rules.auto_rules_id%type;
4239
4240 --get score
4241
4242 CURSOR GET_SCORE IS
4243 SELECT sum(nvl(SCORE,0))
4244 FROM AR_CMGT_CF_DTLS
4245 WHERE CASE_FOLDER_ID=p_cf_id;
4246
4247
4248 --get detailz
4249 CURSOR get_details IS
4250 SELECT cf.score_model_id,cr.credit_type
4251 from ar_cmgt_case_folders cf,ar_cmgt_credit_requests cr
4252 where case_folder_id=p_cf_id
4253 and cr.credit_request_id=cf.credit_request_id;
4254
4255
4256 --get auto rule id
4257
4258 CURSOR get_auto_id IS
4259 SELECT auto_rules_id
4260 FROM ar_cmgt_auto_rules
4261 WHERE score_model_id=l_score_model_id
4262 AND Trunc(SYSDATE) BETWEEN Trunc(start_date) AND Trunc(Nvl(end_date,SYSDATE))
4263 AND submit_flag='Y';
4264
4265 -- get recommendations
4266
4267 CURSOR c_auto_reco IS
4268 SELECT a.credit_recommendation, a.recommendation_value1,
4269 a.recommendation_value2
4270 FROM ar_cmgt_auto_recommends a, ar_cmgt_auto_rule_dtls b
4271 WHERE a.auto_rule_details_id = b.auto_rule_details_id
4272 AND l_score between b.credit_score_low and b.credit_score_high
4273 AND a.credit_type = l_credit_type
4274 AND b.auto_rules_id = l_auto_rules_id;
4275
4276 BEGIN
4277
4278 --get the score
4279
4280 OPEN GET_SCORE;
4281 FETCH GET_SCORE into l_score;
4282 CLOSE GET_SCORE;
4283
4284 --get the detailz
4285
4286 OPEN get_details;
4287 FETCH get_details INTO l_score_model_id,
4288 l_credit_type;
4289 CLOSE get_details;
4290 --get auto rule id
4291
4292 OPEN get_auto_id;
4293 FETCH get_auto_id into l_auto_rules_id;
4294 CLOSE get_auto_id;
4295 --loop through the auto rule cursor
4296 FOR c_auto_rec IN c_auto_reco
4297 LOOP
4298
4299 AR_CMGT_CONTROLS.populate_recommendation(
4300 p_case_folder_id => p_cf_id,
4301 p_credit_request_id => to_number(itemkey),
4302 p_score => l_score,
4303 p_recommended_credit_limit => l_credit_limit,
4304 p_credit_review_date => sysdate,
4305 p_credit_recommendation => c_auto_rec.credit_recommendation,
4306 p_recommendation_value1 => c_auto_rec.recommendation_value1,
4307 p_recommendation_value2 => trunc(c_auto_rec.recommendation_value2),
4308 p_status => 'O',
4309 p_credit_type => l_credit_type,
4310 p_errmsg => l_errmsg,
4311 p_resultout => l_resultout);
4312
4313 IF l_resultout <> 0
4314 THEN
4315 -- raise insert_failure;
4316 resultout :=l_resultout;
4317 return;
4318 ELSE
4319 resultout :=l_resultout;
4320 END IF;
4321 END LOOP;
4322
4323 END GET_EXT_SCORE_RECOMMENDATIONS;
4324
4325 PROCEDURE submit_xml_case_folder (
4326 p_case_folder_id IN NUMBER,
4327 p_request_id OUT NOCOPY NUMBER ) IS
4328 BEGIN
4329 IF p_case_folder_id IS NOT NULL
4330 THEN
4331 p_request_id := FND_REQUEST.SUBMIT_REQUEST('AR','OCMXMLCASEFOLDER',
4332 null,
4333 null,
4334 FALSE,
4335 p_case_folder_id,chr(0)
4336 ,'','','','','','','',''
4337 ,'','','','','','','','','',''
4338 ,'','','','','','','','','',''
4339 ,'','','','','','','','','',''
4340 ,'','','','','','','','','',''
4341 ,'','','','','','','','','',''
4342 ,'','','','','','','','','',''
4343 ,'','','','','','','','','',''
4344 ,'','','','','','','','','',''
4345 ,'','','','','','','','','','');
4346 /* IF p_request_id <> 0
4347 THEN
4348 UPDATE ar_cmgt_case_folders
4349 SET request_id = p_request_id,
4350 status = 'IN_PROCESS',
4351 last_update_date = sysdate,
4352 last_updated_by = fnd_global.user_id
4353 WHERE case_folder_id = p_case_folder_id;
4354 END IF; */
4355 END IF;
4356 EXCEPTION
4357 WHEN OTHERS THEN
4358 raise;
4359 END;
4360
4361
4362 END AR_CMGT_WF_ENGINE;