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