1 PACKAGE BODY AR_CMGT_UTIL AS
2 /* $Header: ARCMUTLB.pls 120.32 2010/03/24 13:21:36 mraymond ship $ */
3
4 /*=======================================================================+
5 | Declare PUBLIC Data Types and Variables
6 +=======================================================================*/
7 pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
8
9 pg_wf_debug VARCHAR2(1) := nvl(fnd_profile.value('AR_CMGT_WF_DEBUG'),'N');
10 pg_wf_debug_file VARCHAR2(50) := fnd_profile.value('AR_CMGT_WF_DEBUG_FILE');
11 pg_wf_debug_path VARCHAR2(255) := fnd_profile.value('AR_CMGT_WF_DEBUG_PATH');
12
13 g_master_debug VARCHAR2(1);
14
15 g_file_handle utl_file.file_type;
16 /*=======================================================================+
17 | Declare PUBLIC Exceptions
18 +=======================================================================*/
19
20 /* This function tests both flags and returns Y if either
21 is turned on. Internally, we will utilize wf_debug only
22 if it is turned on and FND debug only if it is turned on.
23 It is possible for both to be enabled at the same time
24 and that would produce both debug logs */
25 FUNCTION get_wf_debug_flag
26 RETURN VARCHAR2 IS
27 BEGIN
28 IF g_master_debug = 'Y'
29 THEN
30 RETURN 'Y';
31 ELSE
32 RETURN 'N';
33 END IF;
34 END get_wf_debug_flag;
35
36 PROCEDURE debug (
37 p_message IN VARCHAR2,
38 p_module_name IN VARCHAR2,
39 p_log_level IN NUMBER) IS
40
41 BEGIN
42 IF ( p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
43 THEN
44 FND_LOG.string(p_log_level,p_module_name, p_message);
45 END IF;
46
47 END debug;
48
49 /* This debug routine was constructed to bypass the fnd_debug
50 because that one does not work easily or consistently
51 through workflow.
52
53 This debug routine is controlled by the AR_CMGT_WF_DEBUG profile
54 and simply writes the messages to the file AR_CMGT_WF_DEBUG_FILE
55 in AR_CMGT_WF_DEBUG_PATH. To effectively use this, you need to
56 set those profiles at the site level or for sysadmin user (where
57 WF is run from) */
58
59 PROCEDURE wf_debug( p_process_name IN VARCHAR2,
60 p_message IN VARCHAR2) IS
61 l_message VARCHAR2(512);
62 BEGIN
63 IF pg_wf_debug = 'Y'
64 THEN
65 l_message := TO_CHAR(sysdate, 'RRRR-MM-DD HH:MI:SS ~ ') ||
66 p_process_name || ' ~ ' || p_message;
67 utl_file.put_line(g_file_handle, l_message );
68 END IF;
69
70 /* Also write to FND debug log if it is enabled */
71 IF pg_debug in ('Y','C')
72 THEN
73 /* Calls internal debug routine which directly calls
74 FND_LOG routine. This avoids ARP_STANDARD and the
75 odd 'AR_NO_ROW_SYSTEM_PARAMETERS' error. */
76 debug(p_message, p_process_name, FND_LOG.G_CURRENT_RUNTIME_LEVEL);
77 END IF;
78 END wf_debug;
79
80 /*========================================================================
81 | PUBLIC FUNCTION
82 | check_user_resource()
83 | DESCRIPTION
84 | This function checks whether resource id passed is for the user who
85 | is logged.
86 |
87 | PSEUDO CODE/LOGIC
88 |
89 | PARAMETERS
90 | p_credit_analyst_id IN Credit Analyst Id of the case folder
91 |
92 | KNOWN ISSUES
93 |
94 | NOTES
95 |
96 | MODIFICATION HISTORY
97 | Date Author Description of Changes
98 | 20-May-2002 S.Nambiar Created
99 |
100 *=======================================================================*/
101 FUNCTION check_update_permissions(
102 p_credit_analyst_id IN NUMBER,
103 p_requestor_id IN NUMBER,
104 p_credit_request_status IN VARCHAR2 )
105 RETURN VARCHAR2 IS
106
107 l_update_permissions VARCHAR2(1) :='N';
108 l_user_employee_id NUMBER := fnd_global.employee_id;
109 l_resource_id NUMBER;
110
111 CURSOR get_resource_analyst IS
112 SELECT c.resource_id
113 FROM jtf_rs_role_relations a,
114 jtf_rs_roles_vl b,
115 jtf_rs_resource_extns c
116 WHERE a.role_resource_type = 'RS_INDIVIDUAL'
117 AND a.role_resource_id = c.resource_id
118 AND b.role_type_code = 'CREDIT_MGMT'
119 AND a.role_id = b.role_id
120 AND b.role_code = 'CREDIT_ANALYST'
121 AND c.category = 'EMPLOYEE'
122 AND c.source_id = l_user_employee_id
123 AND NVL(a.delete_flag,'N') <> 'Y';
124
125 l_check_for_requestor BOOLEAN := FALSE;
126 BEGIN
127
128 IF p_credit_request_status = 'SUBMIT' THEN
129 IF p_credit_analyst_id IS NOT NULL THEN
130
131 OPEN get_resource_analyst;
132
133 FETCH get_resource_analyst INTO l_resource_id;
134
135
136 IF p_credit_analyst_id = nvl(l_resource_id,-99) THEN
137 l_update_permissions := 'Y';
138 ELSE
139 l_check_for_requestor := TRUE;
140 l_update_permissions := 'N';
141 END IF;
142
143 CLOSE get_resource_analyst;
144
145 END IF;
146
147 ELSIF p_credit_request_status = 'SAVE' THEN
148 --Now check for the requestor .
149
150 /* IF p_requestor_id IS NOT NULL AND
151 (p_credit_analyst_id IS NULL
152 OR
153 (l_check_for_requestor)) THEN
154 IF l_user_employee_id = p_requestor_id THEN
155 l_update_permissions := 'Y';
156 ELSE
157 l_update_permissions := 'N';
158 END IF;
159
160 END IF; */
161 IF p_requestor_id IS NOT NULL
162 THEN
163 IF p_requestor_id = l_user_employee_id
164 THEN
165 l_update_permissions := 'Y';
166 ELSE
167 l_update_permissions := 'N';
168 END IF;
169 END IF;
170
171 END IF;
172
173 RETURN l_update_permissions;
174 EXCEPTION
175 WHEN others THEN
176 l_update_permissions := 'N';
177 RETURN l_update_permissions;
178 END;
179
180 /* Overloaded Function */
181 FUNCTION check_update_permissions(
182 p_credit_analyst_id IN NUMBER,
183 p_requestor_id IN NUMBER)
184 RETURN VARCHAR2 IS
185
186 l_update_permissions VARCHAR2(1) :='N';
187 l_user_employee_id NUMBER := fnd_global.employee_id;
188 l_resource_id NUMBER;
189
190 CURSOR get_resource_analyst IS
191 SELECT c.resource_id
192 FROM jtf_rs_role_relations a,
193 jtf_rs_roles_vl b,
194 jtf_rs_resource_extns c
195 WHERE a.role_resource_type = 'RS_INDIVIDUAL'
196 AND a.role_resource_id = c.resource_id
197 AND b.role_type_code = 'CREDIT_MGMT'
198 AND a.role_id = b.role_id
199 AND b.role_code = 'CREDIT_ANALYST'
200 AND c.category = 'EMPLOYEE'
201 AND c.source_id = l_user_employee_id
202 AND NVL(a.delete_flag,'N') <> 'Y';
203
204 l_check_for_requestor BOOLEAN := FALSE;
205 BEGIN
206
207 IF p_credit_analyst_id IS NOT NULL THEN
208
209 OPEN get_resource_analyst;
210
211 FETCH get_resource_analyst INTO l_resource_id;
212
213
214 IF p_credit_analyst_id = nvl(l_resource_id,-99) THEN
215 l_update_permissions := 'Y';
216 ELSE
217 l_check_for_requestor := TRUE;
218 l_update_permissions := 'N';
219 END IF;
220
221 CLOSE get_resource_analyst;
222
223 END IF;
224
225 --Now check for the requestor .
226
227 IF p_requestor_id IS NOT NULL AND
228 (p_credit_analyst_id IS NULL
229 OR
230 (l_check_for_requestor)) THEN
231 IF l_user_employee_id = p_requestor_id THEN
232 l_update_permissions := 'Y';
233 ELSE
234 l_update_permissions := 'N';
235 END IF;
236
237 END IF;
238 RETURN l_update_permissions;
239 EXCEPTION
240 WHEN others THEN
241 l_update_permissions := 'N';
242 RETURN l_update_permissions;
243 END;
244
245 /*========================================================================
246 | PUBLIC FUNCTION
247 | check_emp_credit_analyst()
248 | DESCRIPTION
249 | This function checks whether employee id passed is a credit analyst
250 | or not
251 | PSEUDO CODE/LOGIC
252 |
253 | PARAMETERS
254 | p_employee_id IN Employee Id of the user logged in
255 |
256 | KNOWN ISSUES
257 |
258 | NOTES
259 |
260 | MODIFICATION HISTORY
261 | Date Author Description of Changes
262 | 20-May-2002 S.Nambiar Created
263 |
264 *=======================================================================*/
265 FUNCTION check_emp_credit_analyst(p_employee_id IN NUMBER )
266 RETURN VARCHAR2 IS
267
268 l_credit_analyst VARCHAR2(1) :='N';
269
270 BEGIN
271 SELECT 'Y'
272 INTO l_credit_analyst
273 FROM jtf_rs_role_relations a,
274 jtf_rs_roles_vl b,
275 jtf_rs_resource_extns c
276 WHERE a.role_resource_type = 'RS_INDIVIDUAL'
277 AND a.role_resource_id = c.resource_id
278 AND a.role_id = b.role_id
279 AND b.role_type_code = 'CREDIT_MGMT'
280 AND b.role_code = 'CREDIT_ANALYST'
281 AND c.category = 'EMPLOYEE'
282 AND c.source_id = p_employee_id
283 AND NVl(a.delete_flag,'N') <> 'Y';
284
285 RETURN l_credit_analyst;
286
287 EXCEPTION
288 WHEN others THEN
289 RETURN l_credit_analyst;
290 END;
291
292 FUNCTION check_emp_credit_analyst
293 RETURN VARCHAR2 IS
294
295 l_credit_analyst VARCHAR2(1) :='N';
296 l_employee_id NUMBER := fnd_global.employee_id;
297
298 BEGIN
299 SELECT 'Y'
300 INTO l_credit_analyst
301 FROM jtf_rs_role_relations a,
302 jtf_rs_roles_vl b,
303 jtf_rs_resource_extns c
304 WHERE a.role_resource_type = 'RS_INDIVIDUAL'
305 AND a.role_resource_id = c.resource_id
306 AND a.role_id = b.role_id
307 AND b.role_type_code = 'CREDIT_MGMT'
308 AND b.role_code = 'CREDIT_ANALYST'
309 AND c.category = 'EMPLOYEE'
310 AND c.source_id = l_employee_id
311 AND NVl(a.delete_flag,'N') <> 'Y';
312
313 RETURN l_credit_analyst;
314
315 EXCEPTION
316 WHEN others THEN
317 RETURN l_credit_analyst;
318 END;
319
320 /*========================================================================
321 | PUBLIC FUNCTION
322 | check_credit_analyst()
323 | DESCRIPTION
324 | This function checks whether resource_id passed is a credit analyst.
325 | For a credit analysts there will be a credit analyst role assigned
326 | in resource manager.
327 |
328 | PSEUDO CODE/LOGIC
329 |
330 | PARAMETERS
331 | p_resource_id IN resource_id
332 |
333 | KNOWN ISSUES
334 |
335 | NOTES
336 |
337 | MODIFICATION HISTORY
338 | Date Author Description of Changes
339 | 20-May-2002 S.Nambiar Created
340 |
341 *=======================================================================*/
342 FUNCTION check_credit_analyst(p_resource_id IN NUMBER )
343 RETURN VARCHAR2 IS
344
345 l_credit_analyst VARCHAR2(1) :='N';
346
347 BEGIN
348 SELECT 'Y'
349 INTO l_credit_analyst
350 FROM jtf_rs_role_relations a,
351 jtf_rs_roles_vl b,
352 jtf_rs_resource_extns c
353 WHERE a.role_resource_type = 'RS_INDIVIDUAL'
354 AND a.role_resource_id = c.resource_id
355 AND a.role_id = b.role_id
356 AND b.role_type_code = 'CREDIT_MGMT'
357 AND b.role_code = 'CREDIT_ANALYST'
358 AND c.category = 'EMPLOYEE'
359 AND c.resource_id = p_resource_id;
360
361 RETURN l_credit_analyst;
362 EXCEPTION
363 WHEN others THEN
364 RETURN l_credit_analyst;
365 END;
366
367 FUNCTION get_credit_analyst_name(p_credit_analyst_id IN NUMBER)
368 RETURN VARCHAR2 IS
369 l_credit_analyst_name jtf_rs_resource_extns_vl.resource_name%TYPE;
370
371 CURSOR credit_analyst_name(p_resource_id IN NUMBER) IS
372 SELECT per.FULL_NAME
373 FROM JTF_RS_RESOURCE_EXTNS rextns,
374 JTF_RS_ROLE_RELATIONS rel,
375 JTF_RS_ROLES_VL role_vl,
376 PER_ALL_PEOPLE_F per
377 WHERE role_vl.role_type_code = 'CREDIT_MGMT'
378 and role_vl.role_code = 'CREDIT_ANALYST'
379 and role_vl.role_id = rel.role_id
380 and rel.role_resource_type = 'RS_INDIVIDUAL'
381 and rel.delete_flag <> 'Y'
382 and rel.role_resource_id = rextns.resource_id
383 and rextns.category = 'EMPLOYEE'
384 and rextns.source_id = per.person_id
385 and sysdate between per.effective_start_date and per.effective_end_date
386 and per.current_employee_flag = 'Y'
387 and rextns.resource_id = p_resource_id;
388
389 BEGIN
390
391 IF p_credit_analyst_id IS NOT NULL THEN
392
393 OPEN credit_analyst_name(p_credit_analyst_id);
394
395 FETCH credit_analyst_name INTO l_credit_analyst_name;
396
397 CLOSE credit_analyst_name;
398 END IF;
399
400 RETURN l_credit_analyst_name;
401 END;
402
403 FUNCTION get_latest_cf_number(p_credit_request_id IN NUMBER)
404 RETURN NUMBER IS
405
406 CURSOR latest_cf_number(p_credit_request_id IN NUMBER ) IS
407 SELECT case_folder_number
408 FROM AR_CMGT_CASE_FOLDERS
409 WHERE credit_request_id = p_credit_request_id;
410
411 l_case_folder_number NUMBER;
412
413 BEGIN
414 OPEN latest_cf_number(p_credit_request_id);
415
416 FETCH latest_cf_number INTO l_case_folder_number;
417
418 IF latest_cf_number%NOTFOUND THEN
419 l_case_folder_number := null;
420 END IF;
421
422 CLOSE latest_cf_number;
423
424 RETURN l_case_folder_number;
425
426 END;
427
428
429 /*========================================================================
430 | PUBLIC FUNCTION
431 | get_no_of_ref_data_points()
432 | DESCRIPTION
433 | This procedure returns the value and no of references based on
434 |
435 | PSEUDO CODE/LOGIC
436 |
437 | PARAMETERS
438 | p_credit_classification IN VARCHAR2,
439 | p_review_type IN VARCHAR2,
440 | p_data_point_id IN NUMBER,
441 | p_number_of_references OUT NOCOPY NUMBER,
442 | p_value OUT NOCOPY VARCHAR2 (for future use)
443 |
444 | KNOWN ISSUES
445 |
446 | NOTES
447 |
448 | MODIFICATION HISTORY
449 | Date Author Description of Changes
450 | 20-May-2002 B.Sarkar Created
451 |
452 *=======================================================================*/
453 PROCEDURE get_no_of_ref_data_points(
454 p_credit_classification IN VARCHAR2,
455 p_review_type IN VARCHAR2,
456 p_data_point_id IN NUMBER,
457 p_number_of_references OUT NOCOPY NUMBER,
458 p_value OUT NOCOPY VARCHAR2 ) IS
459
460 CURSOR c_number_of_reference IS
461 SELECT cld.number_of_references
462 FROM ar_cmgt_check_list_dtls cld,
463 ar_cmgt_check_lists cl
464 WHERE cl.check_list_id = cld.check_list_id
465 AND cld.data_point_id = p_data_point_id
466 AND cl.credit_classification = p_credit_classification
467 AND cl.review_type = p_review_type
468 AND cl.submit_flag = 'Y'
469 AND sysdate between cl.start_date and nvl(cl.end_date,SYSDATE);
470
471
472 BEGIN
473 OPEN c_number_of_reference;
474
475 FETCH c_number_of_reference INTO p_number_of_references;
476
477 IF c_number_of_reference%NOTFOUND THEN
478 p_number_of_references := null;
479 END IF;
480
481 CLOSE c_number_of_reference;
482 END get_no_of_ref_data_points;
483
484
485 /*========================================================================
486 | PUBLIC FUNCTION
487 | find_analysis_level()
488 | DESCRIPTION
489 | This function checks tells you whether the analysis is at the
490 | party, customer account, or account site level.
491 |
492 | PSEUDO CODE/LOGIC
493 |
494 | PARAMETERS
495 | p_party_id IN
496 | p_cust_account_id IN
497 | p_cust_acct_site_id IN
498 |
499 | KNOWN ISSUES
500 |
501 | NOTES
502 |
503 | MODIFICATION HISTORY
504 | Date Author Description of Changes
505 | 10-Jun-2003 M.Senthil Created
506 |
507 *=======================================================================*/
508 FUNCTION find_analysis_level
509 ( p_party_id IN NUMBER,
510 p_cust_account_id IN NUMBER,
511 p_cust_acct_site_id IN NUMBER)
512
513 return VARCHAR2 IS
514 l_analysis_level VARCHAR2(1);
515 BEGIN
516 IF p_cust_acct_site_id <> -99
517 THEN
518 l_analysis_level := 'S';
519 ELSIF p_cust_account_id <> -99
520 THEN
521 l_analysis_level := 'A';
522 ELSE
523 l_analysis_level := 'P';
524 END IF;
525 return l_analysis_level;
526 END;
527
528
529 /*========================================================================
530 | PUBLIC FUNCTION
531 | get_limit_currency()
532 | DESCRIPTION
533 | This function takes in some parameters and fills in the appropriate
534 | values regarding which currency is returned.
535 |
536 | PSEUDO CODE/LOGIC
537 |
538 | PARAMETERS
539 | p_party_id IN Party Id
540 | p_cust_account_id IN Customer Account Id
541 | p_cust_acct_site_id IN Customer Account Site Id
542 | p_trx_currency_code IN Transaction Currency Code
543 |
544 | KNOWN ISSUES
545 |
546 | NOTES
547 |
548 | MODIFICATION HISTORY
549 | Date Author Description of Changes
550 | 10-Jun-2003 M.Senthil Created
551 |
552 *=======================================================================*/
553 PROCEDURE get_limit_currency(
554 p_party_id IN NUMBER,
555 p_cust_account_id IN NUMBER,
556 p_cust_acct_site_id IN NUMBER,
557 p_trx_currency_code IN VARCHAR2,
558 p_limit_curr_code OUT nocopy VARCHAR2,
559 p_trx_limit OUT nocopy NUMBER,
560 p_overall_limit OUT nocopy NUMBER,
561 p_cust_acct_profile_amt_id OUT nocopy NUMBER,
562 p_global_exposure_flag OUT nocopy VARCHAR2,
563 p_include_all_flag OUT nocopy VARCHAR2,
564 p_usage_curr_tbl OUT nocopy HZ_CREDIT_USAGES_PKG.curr_tbl_type,
565 p_excl_curr_list OUT nocopy VARCHAR2
566 ) IS
567
568 l_entity_type VARCHAR2(30);
569 l_entity_id NUMBER(15);
570 l_limit_currency_code ar_cmgt_credit_requests.limit_currency%type;
571 l_analysis_level VARCHAR2(1);
572
573 BEGIN
574 l_analysis_level := AR_CMGT_UTIL.find_analysis_level(p_party_id,
575 p_cust_account_id, p_cust_acct_site_id);
576 IF ( l_analysis_level = 'P')
577 THEN
578 l_entity_type := 'PARTY';
579 l_entity_id := p_party_id;
580 ELSIF ( l_analysis_level = 'A')
581 THEN
582 l_entity_type := 'CUSTOMER';
583 l_entity_id := p_cust_account_id;
584 ELSIF ( l_analysis_level = 'S')
585 THEN
586 l_entity_type := 'SITE';
587 l_entity_id := p_cust_acct_site_id;
588 END IF;
589
590 hz_credit_usages_pkg.Get_Limit_Currency_usages (
591 p_entity_type => l_entity_type,
592 p_entity_id => l_entity_id,
593 p_trx_curr_code => p_trx_currency_code,
594 x_limit_curr_code => p_limit_curr_code,
595 x_trx_limit => p_trx_limit,
596 x_overall_limit => p_overall_limit,
597 x_cust_acct_profile_amt_id => p_cust_acct_profile_amt_id,
598 x_global_exposure_flag => p_global_exposure_flag,
599 x_include_all_flag => p_include_all_flag,
600 x_usage_curr_tbl => p_usage_curr_tbl,
601 x_excl_curr_list => p_excl_curr_list);
602
603 END;
604
605
606 /*========================================================================
607 | PUBLIC FUNCTION
608 | copy_checklist_datapoints()
609 | DESCRIPTION
610 | This procedure copies datapoints for a checklist.
611 |
612 | PSEUDO CODE/LOGIC
613 |
614 | PARAMETERS
615 | p_old_check_list_id IN NUMBER,
616 | p_new_check_list_id IN NUMBER,
617 |
618 | KNOWN ISSUES
619 |
620 | NOTES
621 |
622 | MODIFICATION HISTORY
623 | Date Author Description of Changes
624 | 20-May-2002 B.Sarkar Created
625 |
626 *=======================================================================*/
627 PROCEDURE copy_checklist_data_points(
628 p_new_check_list_id IN VARCHAR2,
629 p_old_check_list_id IN VARCHAR2) IS
630
631 CURSOR c_data_points IS
632 SELECT data_point_id,
633 required_flag,
634 number_of_references
635 FROM ar_cmgt_check_list_dtls
636 WHERE check_list_id = p_old_check_list_id;
637 BEGIN
638 FOR c_data_points_rec IN c_data_points
639 LOOP
640 INSERT INTO ar_cmgt_check_list_dtls
641 (check_list_id,
642 data_point_id,
643 number_of_references,
644 required_flag,
645 last_updated_by,
646 created_by,
647 creation_date,
648 last_update_date,
649 last_update_login )
650 VALUES (
651 p_new_check_list_id,
652 c_data_points_rec.data_point_id,
653 c_data_points_rec.number_of_references,
654 c_data_points_rec.required_flag,
655 -1,
656 -1,
657 SYSDATE,
658 SYSDATE,
659 -1);
660
661
662 END LOOP;
663 END copy_checklist_data_points;
664
665 FUNCTION IS_DUPLICATE_CHECKLIST (
666 p_credit_classification IN VARCHAR2,
667 p_review_type IN VARCHAR2,
668 p_start_date IN DATE)
669 return VARCHAR2 IS
670
671 CURSOR c_dup_checklist IS
672 SELECT Check_list_name
673 FROM ar_cmgt_check_lists
674 WHERE submit_flag = 'Y'
675 AND credit_classification = p_credit_classification
676 AND review_type = p_review_type
677 AND nvl(end_date,SYSDATE) >= p_start_date;
678
679 l_check_list_name ar_cmgt_check_lists.check_list_name%type;
680
681 BEGIN
682 OPEN c_dup_checklist;
683
684 FETCH c_dup_checklist INTO l_check_list_name;
685
686
687 IF c_dup_checklist%NOTFOUND THEN
688 l_check_list_name := null;
689 END IF;
690
691 CLOSE c_dup_checklist;
692
693 RETURN l_check_list_name;
694 END;
695
696
697 FUNCTION is_valid_date (
698 p_start_date IN DATE,
699 p_end_date IN DATE)
700 return NUMBER IS
701 l_result NUMBER;
702 BEGIN
703 IF p_start_date <= p_end_date
704 THEN
705 IF ( p_start_date < trunc(sysdate) or
706 p_end_date < trunc(sysdate) )
707 THEN
708 l_result := 2;
709 ELSE
710 l_result := 0; -- succss
711 END IF;
712 ELSE
713 l_result := 1; -- failure
714 END IF;
715
716 return l_result;
717 END;
718
719 FUNCTION get_fnd_user_name (
720 p_user_id IN NUMBER )
721 return VARCHAR2 IS
722
723 CURSOR c_user_name IS
724 SELECT user_name
725 FROM fnd_user
726 WHERE user_id = p_user_id
727 AND sysdate between start_date and nvl(end_date,SYSDATE);
728
729 l_user_name fnd_user.user_name%type;
730 BEGIN
731
732 OPEN c_user_name;
733
734 FETCH c_user_name INTO l_user_name;
735
736
737 IF c_user_name%NOTFOUND THEN
738 l_user_name := null;
739 END IF;
740
741 CLOSE c_user_name;
742
743 RETURN l_user_name;
744 END;
745
746 FUNCTION check_delete_permissions(p_credit_analyst_id IN NUMBER,
747 p_requestor_id IN NUMBER,
748 p_credit_request_status IN VARCHAR2 )
749 RETURN VARCHAR2
750 IS
751 l_del_permission varchar2(1) := 'N';
752 l_user_employee_id NUMBER := fnd_global.employee_id;
753 BEGIN
754 IF p_credit_request_status = 'SAVE' THEN
755 IF p_requestor_id IS NOT NULL THEN
756 if l_user_employee_id = p_requestor_id then
757 l_del_permission := 'Y';
758 end if;
759 END IF;
760 END IF;
761
762 return l_del_permission;
763
764 END check_delete_permissions;
765
766 FUNCTION get_person_based_on_resource ( l_resource_id IN NUMBER)
767 return NUMBER IS
768 l_person_id per_people_f.person_id%type;
769 CURSOR c_person_id IS
770 SELECT c.source_id
771 FROM jtf_rs_resource_extns c
772 WHERE c.category = 'EMPLOYEE'
773 AND c.resource_id = l_resource_id;
774
775 BEGIN
776
777 OPEN c_person_id;
778
779 FETCH c_person_id INTO l_person_id;
780
781 IF c_person_id%NOTFOUND THEN
782 l_person_id := null;
783 END IF;
784
785 CLOSE c_person_id;
786
787 return l_person_id;
788
789
790 END;
791
792 FUNCTION get_person_based_on_cf ( l_case_folder_id IN NUMBER)
793 return NUMBER IS
794 l_resource_id ar_cmgt_case_folders.credit_analyst_id%type;
795 l_person_id per_people_f.person_id%type;
796 CURSOR c_res_id IS
797 SELECT credit_analyst_id
798 FROM ar_cmgt_case_folders
799 WHERE case_folder_id = l_case_folder_id;
800
801 BEGIN
802
803 OPEN c_res_id;
804
805 FETCH c_res_id INTO l_resource_id;
806
807 IF c_res_id%NOTFOUND THEN
808 l_resource_id := null;
809 ELSE
810 l_person_id := get_person_based_on_resource (l_resource_id);
811 END IF;
812
813 CLOSE c_res_id;
814
815 return l_person_id;
816
817
818 END;
819
820 /* This function will check whether any case folders exist for the party */
821 FUNCTION check_casefolder_exists(p_party_id IN NUMBER,
822 p_cust_account_id IN NUMBER,
823 p_cust_account_site_id IN NUMBER)
824 RETURN VARCHAR2 IS
825 CURSOR case_folder_exists IS
826 select 'Y'
827 from dual
828 where exists (select case_folder_id
829 from ar_cmgt_case_folders
830 where party_id = p_party_id
831 and cust_account_id = nvl(p_cust_account_id,-99)
832 and site_use_id = nvl(p_cust_account_site_id,-99));
833
834 l_return_status VARCHAR2(1):= 'N';
835 BEGIN
836
837 /* If there is a CF exist for account/site and there is no CF exist for
838 party directly, are we supposed to display CF details for the party in
839 credit summary by rolling over the child CFs ????
840 we need to revisit this logic again, till that party search results view
841 credit summary will be disabled if there is no CF for party
842 */
843
844 OPEN case_folder_exists;
845 FETCH case_folder_exists INTO l_return_status;
846 CLOSE case_folder_exists;
847
848 RETURN l_return_status;
849
850 EXCEPTION
851 WHEN others THEN
852 l_return_status := 'N';
853 RETURN l_return_status;
854 END;
855
856 FUNCTION IsApplicationExists(
857 p_party_id IN NUMBER,
858 p_cust_account_id IN NUMBER,
859 p_site_use_id IN NUMBER)
860 return VARCHAR2 IS
861 l_credit_request_id NUMBER;
862 l_return_status VARCHAR2(1):= 'N';
863 BEGIN
864
865 BEGIN
866 SELECT credit_request_id
867 INTO l_credit_request_id
868 FROM ar_cmgt_credit_requests
869 WHERE party_id = p_party_id
870 AND cust_account_id = p_cust_account_id
871 AND site_use_id = p_site_use_id
872 AND status <> 'PROCESSED';
873
874 l_return_status := 'Y';
875
876 EXCEPTION
877 WHEN NO_DATA_FOUND
878 THEN
879 l_return_status := 'N';
880 WHEN TOO_MANY_ROWS
881 THEN
882 l_return_status := 'Y';
883 WHEN OTHERS -- any other error conditions
884 THEN
885 l_return_status := 'N';
886 END;
887 return l_return_status;
888 END;
889
890 FUNCTION get_score_summary(p_case_folder_id IN NUMBER)
891 RETURN NUMBER IS
892 l_score_total NUMBER := 0;
893 BEGIN
894
895 SELECT SUM(score)
896 INTO l_score_total
897 FROM ar_cmgt_cf_dtls
898 WHERE case_folder_id=p_case_folder_id;
899
900 RETURN l_score_total;
901 EXCEPTION
902 WHEN others THEN
903 RETURN l_score_total;
904
905 END;
906
907
908 FUNCTION get_credit_classification(p_party_id IN NUMBER,
909 p_cust_account_id IN NUMBER,
910 p_site_use_id IN NUMBER)
911 RETURN VARCHAR2 IS
912 l_credit_classification ar_cmgt_credit_requests.credit_classification%type;
913 l_sql_statement VARCHAR2(2000);
914
915 BEGIN
916
917 -- check what level the request came
918 IF p_site_use_id <> -99 -- means site level analysis
919 THEN
920 l_sql_statement :=
921 'SELECT credit_classification ' ||
922 'FROM hz_customer_profiles ' ||
923 'WHERE party_id = :1 ' ||
924 'AND cust_account_id = :2 ' ||
925 'AND site_use_id = :3 ';
926 EXECUTE IMMEDIATE l_sql_statement INTO l_credit_classification
927 USING p_party_id, p_cust_account_id, p_site_use_id;
928 ELSIF p_cust_account_id <> -99 -- means account level analysis
929 THEN
930 l_sql_statement :=
931 'SELECT credit_classification ' ||
932 'FROM hz_customer_profiles ' ||
933 'WHERE party_id = :1 ' ||
934 'AND cust_account_id = :2 ' ||
935 'AND site_use_id IS NULL ';
936 EXECUTE IMMEDIATE l_sql_statement INTO l_credit_classification
937 USING p_party_id, p_cust_account_id;
938 ELSE -- means party level analysis
939 l_sql_statement :=
940 'SELECT credit_classification ' ||
941 'FROM hz_customer_profiles ' ||
942 'WHERE party_id = :1 ' ||
943 'AND cust_account_id = -1 ' ||
944 'AND site_use_id IS NULL ';
945 EXECUTE IMMEDIATE l_sql_statement INTO l_credit_classification
946 USING p_party_id;
947 END IF;
948 IF l_credit_classification IS NULL -- means profile is not set
949 THEN
950 SELECT default_credit_classification
951 INTO l_credit_classification
952 FROM ar_cmgt_setup_options;
953 END IF;
954 return l_credit_classification;
955
956 EXCEPTION
957 WHEN others THEN
958 l_credit_classification := 'NULL';
959 return l_credit_classification;
960
961 END;
962
963
964 PROCEDURE CLOSE_WF_NOTIFICATION (
965 p_credit_request_id IN NUMBER,
966 p_message_name IN VARCHAR2,
967 p_recipient_role IN VARCHAR2,
968 p_resultout OUT NOCOPY VARCHAR2,
969 p_error_msg OUT NOCOPY VARCHAR2) IS
970
971 l_notification_id wf_notifications.notification_id%type;
972
973 BEGIN
974 IF pg_debug = 'Y' THEN
975 debug ('CLOSE_WF_NOTIFICATION(+)' );
976 debug ('p_credit_request_id '||p_credit_request_id );
977 debug ('Role '||p_recipient_role );
978 debug ('p_message_name '||p_message_name);
979 END IF;
980 p_resultout := 0;
981 p_error_msg := null;
982
983 select notification_id
984 INTO l_notification_id
985 from wf_notifications
986 where message_name = p_message_name --'MSG_TO_CM_NO_CA'
987 and Recipient_role = p_recipient_role --'FND_RESP222:23918'
988 and status = 'OPEN'
989 and context like 'ARCMGTAP:'||p_credit_request_id||'%';
990
991 IF pg_debug = 'Y' THEN
992 debug ('Notification Id '||l_notification_id);
993 END IF;
994 -- Now call wf_notification api to close the notification
995 WF_NOTIFICATION.close(
996 nid => l_notification_id );
997 IF pg_debug = 'Y' THEN
998 debug ('CLOSE_WF_NOTIFICATION(-)' );
999 END IF;
1000 EXCEPTION
1001 WHEN NO_DATA_FOUND
1002 THEN
1003 Null;
1004 WHEN OTHERS
1005 THEN
1006 p_resultout := 1;
1007 p_error_msg := sqlerrm;
1008 END CLOSE_WF_NOTIFICATION;
1009
1010 FUNCTION convert_amount (
1011 p_from_currency VARCHAR2,
1012 p_to_currency VARCHAR2,
1013 p_conversion_date DATE,
1014 p_conversion_type VARCHAR2 DEFAULT NULL,
1015 p_amount NUMBER )
1016 RETURN NUMBER
1017 IS
1018 converted_amount NUMBER;
1019 l_type VARCHAR2(8);
1020 BEGIN
1021 FND_MSG_PUB.initialize;
1022
1023 /* First verify if to currency is valid */
1024 SELECT decode( derive_type,
1025 'EURO', 'EURO',
1026 'EMU', decode( sign( trunc(sysdate) -
1027 trunc(derive_effective)),
1028 -1, 'OTHER',
1029 'EMU'),
1030 'OTHER' )
1031 INTO l_type
1032 FROM FND_CURRENCIES
1033 WHERE currency_code = p_to_currency;
1034
1035 converted_amount := gl_currency_api.convert_amount( p_from_currency,
1036 p_to_currency,
1037 p_conversion_date,
1038 p_conversion_type,
1039 p_amount );
1040 return( converted_amount );
1041
1042 EXCEPTION
1043 WHEN gl_currency_api.NO_RATE THEN
1044 FND_MESSAGE.SET_NAME('AR', 'OCM_NO_RATE');
1045 FND_MESSAGE.SET_TOKEN('CODE1', p_from_currency);
1046 FND_MESSAGE.SET_TOKEN('CODE2', p_to_currency);
1047 FND_MESSAGE.SET_TOKEN('DATE', p_conversion_date);
1048 FND_MESSAGE.SET_TOKEN('TYPE', p_conversion_type);
1049 fnd_msg_pub.add();
1050 raise;
1051
1052 WHEN gl_currency_api.INVALID_CURRENCY THEN
1053 FND_MESSAGE.SET_NAME('AR', 'OCM_INVALID_CURRENCY');
1054 FND_MESSAGE.SET_TOKEN('CURRCODE', p_from_currency);
1055 fnd_msg_pub.add();
1056 raise;
1057
1058 WHEN NO_DATA_FOUND THEN
1059 FND_MESSAGE.SET_NAME('AR', 'OCM_INVALID_CURRENCY');
1060 FND_MESSAGE.SET_TOKEN('CURRCODE', p_to_currency);
1061 fnd_msg_pub.add();
1062 raise;
1063 END convert_amount;
1064
1065
1066 /* Bug 2855292 */
1067 FUNCTION get_setup_option(p_detail_type IN VARCHAR2)
1068 RETURN VARCHAR2 IS
1069 l_return_val VARCHAR2(30);
1070 l_default_credit_classif VARCHAR2(30);
1071 l_default_exchange_type VARCHAR2(30);
1072 l_aging_bucket_id NUMBER(15);
1073 l_match_rule_id NUMBER(15);
1074 l_cer_dso_days NUMBER(15);
1075 l_period NUMBER(15);
1076 l_auto_application_num_flag varchar2(1);
1077 BEGIN
1078 IF p_detail_type IS NOT NULL THEN
1079 IF NOT pg_ocm_global_setup_options.EXISTS(1) THEN
1080
1081 SELECT default_credit_classification ,
1082 default_exchange_rate_type,
1083 aging_bucket_id,
1084 match_rule_id,
1085 cer_dso_days,
1086 period,
1087 auto_application_num_flag
1088 INTO l_default_credit_classif ,
1089 l_default_exchange_type,
1090 l_aging_bucket_id,
1091 l_match_rule_id,
1092 l_cer_dso_days,
1093 l_period,
1094 l_auto_application_num_flag
1095 FROM ar_cmgt_setup_options;
1096
1097 pg_ocm_global_setup_options(1).default_credit_classification :=
1098 l_default_credit_classif;
1099 pg_ocm_global_setup_options(1).default_exchange_rate_type:=
1100 l_default_exchange_type;
1101 pg_ocm_global_setup_options(1).aging_bucket_id:=l_aging_bucket_id;
1102 pg_ocm_global_setup_options(1).match_rule_id:=l_match_rule_id;
1103 pg_ocm_global_setup_options(1).cer_dso_days:=l_cer_dso_days;
1104 pg_ocm_global_setup_options(1).period := l_period;
1105 pg_ocm_global_setup_options(1).auto_application_num_flag :=
1106 l_auto_application_num_flag;
1107
1108 END IF;
1109
1110 IF p_detail_type = 'DEFAULT_CREDIT_CLASSIFICATION' THEN
1111 l_return_val :=
1112 pg_ocm_global_setup_options(1).default_credit_classification;
1113
1114 ELSIF p_detail_type = 'DEFAULT_EXCHANGE_RATE_TYPE' THEN
1115 l_return_val :=
1116 pg_ocm_global_setup_options(1).default_exchange_rate_type;
1117
1118 ELSIF p_detail_type = 'AGING_BUCKET_ID' THEN
1119 l_return_val := pg_ocm_global_setup_options(1).aging_bucket_id;
1120
1121 ELSIF p_detail_type = 'MATCH_RULE_ID' THEN
1122 l_return_val := pg_ocm_global_setup_options(1).match_rule_id;
1123
1124 ELSIF p_detail_type = 'CER_DSO_DAYS' THEN
1125 l_return_val := pg_ocm_global_setup_options(1).cer_dso_days;
1126
1127 ELSIF p_detail_type = 'PERIOD' THEN
1128 l_return_val := pg_ocm_global_setup_options(1).period;
1129
1130 ELSIF p_detail_type = 'AUTO_APPLICATION_NUM_FLAG' THEN
1131 l_return_val :=
1132 pg_ocm_global_setup_options(1).auto_application_num_flag;
1133 END IF;
1134
1135 RETURN l_return_val;
1136 END IF;
1137
1138 EXCEPTION
1139 WHEN others THEN
1140 RETURN NULL;
1141 END get_setup_option;
1142
1143 PROCEDURE OM_CUST_APPLY_HOLD (
1144 p_party_id IN NUMBER,
1145 p_cust_account_id IN NUMBER,
1146 p_site_use_id IN NUMBER,
1147 p_error_msg OUT NOCOPY VARCHAR2,
1148 p_return_status OUT NOCOPY VARCHAR2 ) IS
1149
1150 l_return_value NUMBER := 0;
1151 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type :=
1152 OE_HOLDS_PVT.G_MISS_Hold_Source_REC;
1153 l_msg_count NUMBER;
1154 l_entity_code VARCHAR2(1);
1155 CURSOR cAccounts IS
1156 SELECT CUST_ACCOUNT_ID
1157 FROM HZ_CUST_ACCOUNTS
1158 WHERE PARTY_ID = p_party_id
1159 AND STATUS = 'A';
1160
1161 BEGIN
1162
1163 l_hold_source_rec.hold_id := 1; -- credit hold
1164 -- For Ct based holds.
1165 -- first check the level of analysis
1166 IF p_cust_account_id = -1 -- party level
1167 THEN
1168 l_hold_source_rec.hold_entity_code := 'C';
1169 -- get all the acconts for the parties
1170 FOR cAccountsRec IN cAccounts
1171 LOOP
1172 l_hold_source_rec.hold_entity_id := cAccountsRec.cust_account_id;
1173 OE_Holds_PUB.Apply_Holds
1174 ( p_api_version => 1.0
1175 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
1176 , p_hold_source_rec => l_hold_source_rec
1177 , x_msg_count => l_msg_count
1178 , x_msg_data => p_error_msg
1179 , x_return_status => p_return_status
1180 );
1181 IF p_return_status <> FND_API.G_RET_STS_SUCCESS
1182 THEN
1183 exit;
1184 END IF;
1185 END LOOP;
1186 ELSIF p_cust_account_id <> -1 and p_site_use_id IS NULL
1187 THEN
1188 l_hold_source_rec.hold_entity_code := 'C';
1189 l_hold_source_rec.hold_entity_id := p_cust_account_id;
1190 OE_Holds_PUB.Apply_Holds
1191 ( p_api_version => 1.0
1192 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
1193 , p_hold_source_rec => l_hold_source_rec
1194 , x_msg_count => l_msg_count
1195 , x_msg_data => p_error_msg
1196 , x_return_status => p_return_status
1197 );
1198 IF p_return_status <> FND_API.G_RET_STS_SUCCESS
1199 THEN
1200 return;
1201 END IF;
1202 ELSIF p_cust_account_id <> -1 and p_site_use_id IS NOT NULL
1203 THEN
1204 l_hold_source_rec.hold_entity_code := 'S';
1205 l_hold_source_rec.hold_entity_id := p_site_use_id;
1206 OE_Holds_PUB.Apply_Holds
1207 ( p_api_version => 1.0
1208 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
1209 , p_hold_source_rec => l_hold_source_rec
1210 , x_msg_count => l_msg_count
1211 , x_msg_data => p_error_msg
1212 , x_return_status => p_return_status
1213 );
1214 IF p_return_status <> FND_API.G_RET_STS_SUCCESS
1215 THEN
1216 return;
1217 END IF;
1218 END IF;
1219 END;
1220
1221 PROCEDURE OM_CUST_RELEASE_HOLD (
1222 p_party_id IN NUMBER,
1223 p_cust_account_id IN NUMBER,
1224 p_site_use_id IN NUMBER,
1225 p_error_msg OUT NOCOPY VARCHAR2,
1226 p_return_status OUT NOCOPY VARCHAR2 ) IS
1227
1228 l_return_value NUMBER := 0;
1229 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type :=
1230 OE_HOLDS_PVT.G_MISS_Hold_Source_REC;
1231 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type :=
1232 OE_HOLDS_PVT.G_MISS_Hold_Release_REC;
1233
1234 l_msg_count NUMBER;
1235 l_entity_code VARCHAR2(1);
1236
1237 CURSOR cAccounts IS
1238 SELECT CUST_ACCOUNT_ID
1239 FROM HZ_CUST_ACCOUNTS
1240 WHERE PARTY_ID = p_party_id
1241 AND STATUS = 'A';
1242
1243 BEGIN
1244 l_hold_source_rec.hold_id := 1; -- credit hold
1245 l_hold_release_rec.release_reason_code := 'OCM_AUTOMATIC';
1246 l_hold_release_rec.release_comment := 'Credit Management Hold Release' ;
1247 -- For Ct based holds.
1248 -- first check the level of analysis
1249 IF p_cust_account_id = -1 -- party level
1250 THEN
1251 l_hold_source_rec.hold_entity_code := 'C';
1252 -- get all the acconts for the parties
1253 FOR cAccountsRec IN cAccounts
1254 LOOP
1255 l_hold_source_rec.hold_entity_id := cAccountsRec.cust_account_id;
1256 OE_Holds_PUB.Release_Holds
1257 ( p_api_version => 1.0
1258 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
1259 , p_hold_source_rec => l_hold_source_rec
1260 , p_hold_release_rec => l_hold_release_rec
1261 , x_msg_count => l_msg_count
1262 , x_msg_data => p_error_msg
1263 , x_return_status => p_return_status
1264 );
1265 IF p_return_status <> FND_API.G_RET_STS_SUCCESS
1266 THEN
1267 exit;
1268 END IF;
1269 END LOOP;
1270 ELSIF p_cust_account_id <> -1 and p_site_use_id IS NULL
1271 THEN
1272 l_hold_source_rec.hold_entity_code := 'C';
1273 l_hold_source_rec.hold_entity_id := p_cust_account_id;
1274 OE_Holds_PUB.Release_Holds
1275 ( p_api_version => 1.0
1276 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
1277 , p_hold_source_rec => l_hold_source_rec
1278 , p_hold_release_rec => l_hold_release_rec
1279 , x_msg_count => l_msg_count
1280 , x_msg_data => p_error_msg
1281 , x_return_status => p_return_status
1282 );
1283 IF p_return_status <> FND_API.G_RET_STS_SUCCESS
1284 THEN
1285 return;
1286 END IF;
1287 ELSIF p_cust_account_id <> -1 and p_site_use_id IS NOT NULL
1288 THEN
1289 l_hold_source_rec.hold_entity_code := 'S';
1290 l_hold_source_rec.hold_entity_id := p_site_use_id;
1291 OE_Holds_PUB.Release_Holds
1292 ( p_api_version => 1.0
1293 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
1294 , p_hold_source_rec => l_hold_source_rec
1295 , p_hold_release_rec => l_hold_release_rec
1296 , x_msg_count => l_msg_count
1297 , x_msg_data => p_error_msg
1298 , x_return_status => p_return_status
1299 );
1300 IF p_return_status <> FND_API.G_RET_STS_SUCCESS
1301 THEN
1302 return;
1303 END IF;
1304 END IF;
1305 END;
1306
1307 FUNCTION get_requestor_name(p_requestor_id IN NUMBER)
1308 RETURN VARCHAR2 IS
1309
1310 l_requestor_name per_all_people_f.full_name%TYPE;
1311
1312 CURSOR crequestorName IS
1313 SELECT FULL_NAME
1314 FROM PER_ALL_PEOPLE_F
1315 WHERE sysdate between effective_start_date and effective_end_date
1316 and person_id = p_requestor_id;
1317
1318 BEGIN
1319
1320 IF p_requestor_id IS NOT NULL THEN
1321
1322 OPEN cRequestorName;
1323
1324 FETCH cRequestorName INTO l_requestor_name;
1325
1326 CLOSE cRequestorName;
1327
1328 END IF;
1329
1330 RETURN l_requestor_name;
1331
1332 END;
1333
1334 BEGIN
1335 IF pg_wf_debug = 'Y'
1336 THEN
1337 /* make sure file handle is set */
1338 g_file_handle := utl_file.fopen(pg_wf_debug_path, pg_wf_debug_file, 'A');
1339 g_master_debug := 'Y';
1340 ELSE
1341 IF pg_debug in ('Y','C')
1342 THEN
1343 g_master_debug := 'Y';
1344 END IF;
1345 END IF;
1346
1347 END AR_CMGT_UTIL;
[Home] [Help]