[Home] [Help]
1: Package BODY AR_CMGT_WF_ENGINE AS
2: /* $Header: ARCMGWFB.pls 120.31 2006/04/13 19:20:10 bsarkar noship $ */
3:
4: pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
5:
5:
6: PROCEDURE debug (
7: p_message_name IN VARCHAR2 ) IS
8: BEGIN
9: ar_cmgt_util.debug (p_message_name, 'ar.cmgt.plsql.AR_CMGT_WF_ENGINE' );
10: END;
11:
12:
13: PROCEDURE raise_recco_event (p_case_folder_id IN NUMBER) AS
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:
287: END IF;
288: END LOOP;
289: EXCEPTION
290: WHEN OTHERS THEN
291: p_errmsg := 'Error in ar_cmgt_wf_engine.validate_reference_data_points '|| sqlerrm;
292: p_resultout := 1;
293: return;
294: END;
295: PROCEDURE VALIDATE_REQUIRED_DATA_POINTS (
407: role_display_name => l_role_display_name,
408: expiration_date => to_date('31/12/4712','DD/MM/RRRR'));
409: END IF;
410:
411: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
412: itemKey => itemkey,
413: aname => 'CREDIT_ANALYST_ROLE',
414: avalue => 'AR_CMGT_CREDIT_ANALYST_ROLE');
415:
458: BEGIN
459: SELECT employee_id
460: INTO l_employee_id
461: FROM FND_USER
462: WHERE user_name = wf_engine.context_text;
463: EXCEPTION
464: WHEN NO_DATA_FOUND THEN
465: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
466: 'Employee Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
461: FROM FND_USER
462: WHERE user_name = wf_engine.context_text;
463: EXCEPTION
464: WHEN NO_DATA_FOUND THEN
465: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
466: 'Employee Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
467: raise;
468: WHEN OTHERS THEN
469: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
462: WHERE user_name = wf_engine.context_text;
463: EXCEPTION
464: WHEN NO_DATA_FOUND THEN
465: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
466: 'Employee Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
467: raise;
468: WHEN OTHERS THEN
469: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
470: 'Sqlerror while getiing Employee Id '||sqlerrm);
465: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
466: 'Employee Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
467: raise;
468: WHEN OTHERS THEN
469: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
470: 'Sqlerror while getiing Employee Id '||sqlerrm);
471: raise;
472: END;
473: BEGIN
486:
487:
488: EXCEPTION
489: WHEN NO_DATA_FOUND THEN
490: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
491: 'Resource Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
492: raise;
493: WHEN OTHERS THEN
494: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
487:
488: EXCEPTION
489: WHEN NO_DATA_FOUND THEN
490: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
491: 'Resource Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
492: raise;
493: WHEN OTHERS THEN
494: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
495: 'Sqlerror while getting Resource Id'||sqlerrm);
490: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
491: 'Resource Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
492: raise;
493: WHEN OTHERS THEN
494: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
495: 'Sqlerror while getting Resource Id'||sqlerrm);
496: raise;
497: END;
498: get_employee_details(l_employee_id,l_user_name, l_display_name);
496: raise;
497: END;
498: get_employee_details(l_employee_id,l_user_name, l_display_name);
499:
500: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
501: itemKey => itemkey,
502: aname => 'CREDIT_ANALYST_USER_NAME',
503: avalue => l_user_name);
504: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
500: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
501: itemKey => itemkey,
502: aname => 'CREDIT_ANALYST_USER_NAME',
503: avalue => l_user_name);
504: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
505: itemKey => itemkey,
506: aname => 'CREDIT_ANALYST_ID',
507: avalue => l_resource_id);
508: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
504: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
505: itemKey => itemkey,
506: aname => 'CREDIT_ANALYST_ID',
507: avalue => l_resource_id);
508: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
509: itemKey => itemkey,
510: aname => 'CREDIT_ANALYST_DISPLAY_NAME',
511: avalue => l_display_name);
512:
526: last_update_login = fnd_global.login_id
527: WHERE credit_request_id = itemkey
528: AND credit_analyst_id IS NULL;
529:
530: l_failure_function := WF_ENGINE.getItemAttrText(
531: itemtype => itemtype,
532: itemkey => itemkey,
533: aname => 'FAILURE_FUNCTION');
534:
536: THEN
537:
538: l_notification_id :=
539: WF_NOTIFICATION.send
540: ( role => wf_engine.context_text,
541: msg_type => 'ARCMGTAP',
542: msg_name => 'MSG_TO_CA_INV_POLICY_SUBMIT',
543: callback => 'WF_ENGINE.CB',
544: context => itemtype||':'||itemkey||':'||to_char(actid));
539: WF_NOTIFICATION.send
540: ( role => wf_engine.context_text,
541: msg_type => 'ARCMGTAP',
542: msg_name => 'MSG_TO_CA_INV_POLICY_SUBMIT',
543: callback => 'WF_ENGINE.CB',
544: context => itemtype||':'||itemkey||':'||to_char(actid));
545:
546: ELSIF l_failure_function = 'SCORING_MODEL'
547: THEN
546: ELSIF l_failure_function = 'SCORING_MODEL'
547: THEN
548: l_notification_id :=
549: WF_NOTIFICATION.send
550: ( role => wf_engine.context_text,
551: msg_type => 'ARCMGTAP',
552: msg_name => 'MSG_CA_NO_SM',
553: callback => 'WF_ENGINE.CB',
554: context => itemtype||':'||itemkey||':'||to_char(actid));
549: WF_NOTIFICATION.send
550: ( role => wf_engine.context_text,
551: msg_type => 'ARCMGTAP',
552: msg_name => 'MSG_CA_NO_SM',
553: callback => 'WF_ENGINE.CB',
554: context => itemtype||':'||itemkey||':'||to_char(actid));
555: ELSIF l_failure_function = 'SCORING_CURRENCY'
556: THEN
557: l_notification_id :=
555: ELSIF l_failure_function = 'SCORING_CURRENCY'
556: THEN
557: l_notification_id :=
558: WF_NOTIFICATION.send
559: ( role => wf_engine.context_text,
560: msg_type => 'ARCMGTAP',
561: msg_name => 'MSG_TO_CA_CURRENCY_NOT_MATCH',
562: callback => 'WF_ENGINE.CB',
563: context => itemtype||':'||itemkey||':'||to_char(actid));
558: WF_NOTIFICATION.send
559: ( role => wf_engine.context_text,
560: msg_type => 'ARCMGTAP',
561: msg_name => 'MSG_TO_CA_CURRENCY_NOT_MATCH',
562: callback => 'WF_ENGINE.CB',
563: context => itemtype||':'||itemkey||':'||to_char(actid));
564: ELSIF l_failure_function = 'MANUAL_ANALYSIS'
565: THEN
566: l_notification_id :=
564: ELSIF l_failure_function = 'MANUAL_ANALYSIS'
565: THEN
566: l_notification_id :=
567: WF_NOTIFICATION.send
568: ( role => wf_engine.context_text,
569: msg_type => 'ARCMGTAP',
570: msg_name => 'MSG_TO_CA_MANUAL_ANALYSIS',
571: callback => 'WF_ENGINE.CB',
572: context => itemtype||':'||itemkey||':'||to_char(actid));
567: WF_NOTIFICATION.send
568: ( role => wf_engine.context_text,
569: msg_type => 'ARCMGTAP',
570: msg_name => 'MSG_TO_CA_MANUAL_ANALYSIS',
571: callback => 'WF_ENGINE.CB',
572: context => itemtype||':'||itemkey||':'||to_char(actid));
573: ELSIF l_failure_function = 'SKIP_APPROVAL'
574: THEN
575: l_notification_id :=
573: ELSIF l_failure_function = 'SKIP_APPROVAL'
574: THEN
575: l_notification_id :=
576: WF_NOTIFICATION.send
577: ( role => wf_engine.context_text,
578: msg_type => 'ARCMGTAP',
579: msg_name => 'REASG_MSG_TO_CA_SKIP_APPROVAL',
580: callback => 'WF_ENGINE.CB',
581: context => itemtype||':'||itemkey||':'||to_char(actid));
576: WF_NOTIFICATION.send
577: ( role => wf_engine.context_text,
578: msg_type => 'ARCMGTAP',
579: msg_name => 'REASG_MSG_TO_CA_SKIP_APPROVAL',
580: callback => 'WF_ENGINE.CB',
581: context => itemtype||':'||itemkey||':'||to_char(actid));
582: ELSIF l_failure_function = 'GATHER_DATA_POINTS'
583: THEN
584: l_notification_id :=
582: ELSIF l_failure_function = 'GATHER_DATA_POINTS'
583: THEN
584: l_notification_id :=
585: WF_NOTIFICATION.send
586: ( role => wf_engine.context_text,
587: msg_type => 'ARCMGTAP',
588: msg_name => 'MSG_TO_CA_NO_CF_CREATED',
589: callback => 'WF_ENGINE.CB',
590: context => itemtype||':'||itemkey||':'||to_char(actid));
585: WF_NOTIFICATION.send
586: ( role => wf_engine.context_text,
587: msg_type => 'ARCMGTAP',
588: msg_name => 'MSG_TO_CA_NO_CF_CREATED',
589: callback => 'WF_ENGINE.CB',
590: context => itemtype||':'||itemkey||':'||to_char(actid));
591: ELSIF l_failure_function = 'GENERATE_RECOMMENDATION'
592: THEN
593: l_notification_id :=
591: ELSIF l_failure_function = 'GENERATE_RECOMMENDATION'
592: THEN
593: l_notification_id :=
594: WF_NOTIFICATION.send
595: ( role => wf_engine.context_text,
596: msg_type => 'ARCMGTAP',
597: msg_name => 'MSG_TO_CA_NO_RECO',
598: callback => 'WF_ENGINE.CB',
599: context => itemtype||':'||itemkey||':'||to_char(actid));
594: WF_NOTIFICATION.send
595: ( role => wf_engine.context_text,
596: msg_type => 'ARCMGTAP',
597: msg_name => 'MSG_TO_CA_NO_RECO',
598: callback => 'WF_ENGINE.CB',
599: context => itemtype||':'||itemkey||':'||to_char(actid));
600: ELSIF l_failure_function = 'VALIDATE_RECO'
601: THEN
602: l_notification_id :=
600: ELSIF l_failure_function = 'VALIDATE_RECO'
601: THEN
602: l_notification_id :=
603: WF_NOTIFICATION.send
604: ( role => wf_engine.context_text,
605: msg_type => 'ARCMGTAP',
606: msg_name => 'MSG_TO_CA_INVALID_RECO',
607: callback => 'WF_ENGINE.CB',
608: context => itemtype||':'||itemkey||':'||to_char(actid));
603: WF_NOTIFICATION.send
604: ( role => wf_engine.context_text,
605: msg_type => 'ARCMGTAP',
606: msg_name => 'MSG_TO_CA_INVALID_RECO',
607: callback => 'WF_ENGINE.CB',
608: context => itemtype||':'||itemkey||':'||to_char(actid));
609:
610: ELSIF l_failure_function = 'DUPLICATE_CASE_FOLDER'
611: THEN
610: ELSIF l_failure_function = 'DUPLICATE_CASE_FOLDER'
611: THEN
612: l_notification_id :=
613: WF_NOTIFICATION.send
614: ( role => wf_engine.context_text,
615: msg_type => 'ARCMGTAP',
616: msg_name => 'MESSAGE_APPEAL_INITIATED',
617: callback => 'WF_ENGINE.CB',
618: context => itemtype||':'||itemkey||':'||to_char(actid));
613: WF_NOTIFICATION.send
614: ( role => wf_engine.context_text,
615: msg_type => 'ARCMGTAP',
616: msg_name => 'MESSAGE_APPEAL_INITIATED',
617: callback => 'WF_ENGINE.CB',
618: context => itemtype||':'||itemkey||':'||to_char(actid));
619:
620: END IF;
621: END IF;
670: THEN
671: p_credit_analyst_id := NULL;
672: return;
673: WHEN OTHERS THEN
674: wf_core.context('AR_CMGT_WF_ENGINE','getCAFromRulesEngine',p_itemtype, p_credit_request_id,
675: 'Error While getting Rules Engine Parameter Details',sqlerrm);
676: raise;
677:
678:
696: p_credit_analyst_id := FUN_RULE_PUB.get_number;
697:
698: EXCEPTION
699: WHEN OTHERS THEN
700: wf_core.context('AR_CMGT_WF_ENGINE','getCAFromRulesEngine',p_itemtype, p_credit_request_id,
701: 'Error While calling Rules Engine',sqlerrm);
702: raise;
703: END;
704:
717: l_dummy VARCHAR2(1);
718: BEGIN
719: IF funcmode = 'RUN'
720: THEN
721: l_credit_analyst_id := WF_ENGINE.getItemAttrNumber
722: (itemType => 'ARCMGTAP',
723: itemKey => itemkey,
724: aname => 'CREDIT_ANALYST_ID');
725:
746: EXCEPTION
747: WHEN NO_DATA_FOUND THEN
748: l_credit_analyst_id := NULL;
749: WHEN OTHERS THEN
750: wf_core.context('AR_CMGT_WF_ENGINE','ASSIGN_CREDIT_ANALYST',itemtype, itemkey,
751: 'Error While getting Credit Analyst Id',sqlerrm);
752: raise;
753: END;
754: END IF;
771: EXCEPTION
772: WHEN NO_DATA_FOUND THEN
773: l_credit_analyst_id := NULL;
774: WHEN OTHERS THEN
775: wf_core.context('AR_CMGT_WF_ENGINE','ASSIGN_CREDIT_ANALYST',itemtype, itemkey,
776: 'Error While validating Credit Analyst Id:'||l_credit_analyst_id,sqlerrm);
777: raise;
778: END;
779:
792: AND category = 'EMPLOYEE';
793:
794: EXCEPTION
795: WHEN OTHERS THEN
796: wf_core.context('AR_CMGT_WF_ENGINE','ASSIGN_CREDIT_ANALYST',itemtype, itemkey,
797: 'Error While getting Employee Id for Credit Analyst',sqlerrm);
798: raise;
799: END;
800:
807: last_update_login = fnd_global.login_id
808: WHERE credit_request_id = itemkey;
809:
810:
811: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
812: itemKey => itemkey,
813: aname => 'CREDIT_ANALYST_USER_NAME',
814: avalue => l_user_name);
815: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
811: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
812: itemKey => itemkey,
813: aname => 'CREDIT_ANALYST_USER_NAME',
814: avalue => l_user_name);
815: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
816: itemKey => itemkey,
817: aname => 'CREDIT_ANALYST_ID',
818: avalue => l_credit_analyst_id);
819: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
815: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
816: itemKey => itemkey,
817: aname => 'CREDIT_ANALYST_ID',
818: avalue => l_credit_analyst_id);
819: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
820: itemKey => itemkey,
821: aname => 'CREDIT_ANALYST_DISPLAY_NAME',
822: avalue => l_display_name);
823:
822: avalue => l_display_name);
823:
824: END IF; -- end of credit analyst is null
825: -- Stamp CA to case folder
826: l_case_folder_id := WF_ENGINE.getItemAttrNumber
827: (itemType => 'ARCMGTAP',
828: itemKey => itemkey,
829: aname => 'CASE_FOLDER_ID');
830: IF l_case_folder_id IS NOT NULL
867: l_application_status varchar2(2000);
868:
869: BEGIN
870: -- The following parameter will kickoff WF in async. mode
871: l_save_threshold := WF_ENGINE.threshold;
872:
873: WF_ENGINE.threshold := -1;
874:
875: IF p_application_status = 'FINISH'
869: BEGIN
870: -- The following parameter will kickoff WF in async. mode
871: l_save_threshold := WF_ENGINE.threshold;
872:
873: WF_ENGINE.threshold := -1;
874:
875: IF p_application_status = 'FINISH'
876: THEN
877: WF_ENGINE.CreateProcess (itemType => 'ARCMGTAP',
873: WF_ENGINE.threshold := -1;
874:
875: IF p_application_status = 'FINISH'
876: THEN
877: WF_ENGINE.CreateProcess (itemType => 'ARCMGTAP',
878: itemKey => p_credit_request_id,
879: process => 'AR_CMGT_APPLICATION_PROCESS');
880:
881: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
877: WF_ENGINE.CreateProcess (itemType => 'ARCMGTAP',
878: itemKey => p_credit_request_id,
879: process => 'AR_CMGT_APPLICATION_PROCESS');
880:
881: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
882: itemKey => p_credit_request_id,
883: aname => 'CREDIT_REQUEST_ID',
884: avalue => p_credit_request_id);
885:
883: aname => 'CREDIT_REQUEST_ID',
884: avalue => p_credit_request_id);
885:
886:
887: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
888: itemKey => p_credit_request_id,
889: aname => 'APPLICATION_STATUS',
890: avalue => p_application_status);
891:
888: itemKey => p_credit_request_id,
889: aname => 'APPLICATION_STATUS',
890: avalue => p_application_status);
891:
892: WF_ENGINE.StartProcess ( itemType => 'ARCMGTAP',
893: itemKey => p_credit_request_id);
894:
895: ELSIF p_application_status = 'SUBMIT'
896: THEN
896: THEN
897: -- first check whether this workflow is already submitted. If
898: -- it is already submitted then resume the process only
899: BEGIN
900: WF_ENGINE.ItemStatus(itemType => 'ARCMGTAP',
901: itemkey => p_credit_request_id,
902: status => l_status,
903: result => l_resultout);
904:
901: itemkey => p_credit_request_id,
902: status => l_status,
903: result => l_resultout);
904:
905: WF_ENGINE.CompleteActivity( itemType => 'ARCMGTAP',
906: itemkey => p_credit_request_id,
907: activity => 'BLOCK',
908: result => NULL);
909:
906: itemkey => p_credit_request_id,
907: activity => 'BLOCK',
908: result => NULL);
909:
910: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
911: itemKey => p_credit_request_id,
912: aname => 'APPLICATION_STATUS',
913: avalue => p_application_status);
914:
915: -- if the process does not exist it will rasie an exception. Otherwise
916: -- the process has already started. We need to resume the process.
917: EXCEPTION
918: WHEN OTHERS THEN
919: WF_ENGINE.CreateProcess (itemType => 'ARCMGTAP',
920: itemKey => p_credit_request_id,
921: process => 'AR_CMGT_APPLICATION_PROCESS');
922: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
923: itemKey => p_credit_request_id,
918: WHEN OTHERS THEN
919: WF_ENGINE.CreateProcess (itemType => 'ARCMGTAP',
920: itemKey => p_credit_request_id,
921: process => 'AR_CMGT_APPLICATION_PROCESS');
922: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
923: itemKey => p_credit_request_id,
924: aname => 'CREDIT_REQUEST_ID',
925: avalue => p_credit_request_id);
926:
923: itemKey => p_credit_request_id,
924: aname => 'CREDIT_REQUEST_ID',
925: avalue => p_credit_request_id);
926:
927: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
928: itemKey => p_credit_request_id,
929: aname => 'APPLICATION_STATUS',
930: avalue => p_application_status);
931:
928: itemKey => p_credit_request_id,
929: aname => 'APPLICATION_STATUS',
930: avalue => p_application_status);
931:
932: WF_ENGINE.StartProcess ( itemType => 'ARCMGTAP',
933: itemKey => p_credit_request_id);
934:
935:
936: END;
939: END IF;
940:
941:
942:
943: WF_ENGINE.threshold := l_save_threshold;
944:
945: END;
946:
947: PROCEDURE GENERATE_CREDIT_CLASSIFICATION (
976: SET credit_classification = l_credit_classification
977: WHERE credit_request_id = itemkey;
978: END IF; -- end of credit classification
979: -- Now update the credit classification in wf
980: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
981: itemkey => itemkey,
982: aname => 'CREDIT_CLASSIFICATION',
983: avalue => l_credit_classification );
984: END IF;
984: END IF;
985:
986: EXCEPTION
987: WHEN others THEN
988: wf_core.context('AR_CMGT_WF_ENGINE','GENERATE_CREDIT_CLASSIFICATION',itemtype, itemkey,
989: sqlerrm);
990: raise;
991: END;
992:
1046:
1047: IF funcmode = 'RUN'
1048: THEN
1049:
1050: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
1051: (itemtype => itemtype,
1052: itemkey => itemkey,
1053: aname => 'CASE_FOLDER_ID');
1054:
1078:
1079: IF funcmode = 'RUN'
1080: THEN
1081: l_application_status :=
1082: WF_ENGINE.GetItemAttrText
1083: (itemtype => itemtype,
1084: itemkey => itemkey,
1085: aname => 'APPLICATION_STATUS');
1086:
1091:
1092: EXCEPTION
1093: WHEN OTHERS
1094: THEN
1095: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_APPLICATION_STATUS',itemtype, itemkey,
1096: sqlerrm);
1097: raise;
1098: END;
1099:
1115: FROM ar_cmgt_credit_requests
1116: WHERE credit_request_id = itemkey;
1117: -- furst check whether the profile record exists or not. if exists
1118: -- then do n't create else create a record in hz_customer_profiles
1119: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1120: itemkey => itemkey,
1121: aname => 'PARTY_ID',
1122: avalue => l_party_id);
1123: BEGIN
1133: THEN
1134: ar_cmgt_hz_cover_api.create_party_profile(p_party_id => l_party_id);
1135: WHEN OTHERS
1136: THEN
1137: wf_core.context('AR_CMGT_WF_ENGINE','CREATE_PARTY_PROFILE',itemtype, itemkey,
1138: sqlerrm);
1139: raise;
1140: END;
1141:
1182: THEN
1183:
1184: -- based on credit request id get credit classification and review type
1185: -- to find valid check list and build case folder table.
1186: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1187: itemkey => itemkey,
1188: aname => 'FAILURE_FUNCTION',
1189: avalue => 'CREDIT_POLICY');
1190: BEGIN
1280:
1281: resultout := 'COMPLETE:NOTFOUND';
1282: --return;
1283: WHEN OTHERS THEN
1284: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1285: sqlerrm);
1286: raise;
1287: END;
1288:
1285: sqlerrm);
1286: raise;
1287: END;
1288:
1289: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1290: itemkey => itemkey,
1291: aname => 'CREDIT_CLASSIFICATION',
1292: avalue => l_credit_classification );
1293: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1289: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1290: itemkey => itemkey,
1291: aname => 'CREDIT_CLASSIFICATION',
1292: avalue => l_credit_classification );
1293: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1294: itemkey => itemkey,
1295: aname => 'REVIEW_TYPE',
1296: avalue => l_review_type );
1297: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1293: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1294: itemkey => itemkey,
1295: aname => 'REVIEW_TYPE',
1296: avalue => l_review_type );
1297: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1298: itemkey => itemkey,
1299: aname => 'CURRENCY',
1300: avalue => l_currency );
1301: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1297: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1298: itemkey => itemkey,
1299: aname => 'CURRENCY',
1300: avalue => l_currency );
1301: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1302: itemkey => itemkey,
1303: aname => 'SOURCE_NAME',
1304: avalue => l_source_name );
1305: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1301: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1302: itemkey => itemkey,
1303: aname => 'SOURCE_NAME',
1304: avalue => l_source_name );
1305: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1306: itemkey => itemkey,
1307: aname => 'APPLICATION_NUMBER',
1308: avalue => l_application_number );
1309: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1305: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1306: itemkey => itemkey,
1307: aname => 'APPLICATION_NUMBER',
1308: avalue => l_application_number );
1309: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1310: itemkey => itemkey,
1311: aname => 'REQUESTED_CREDIT_LIMIT',
1312: avalue => l_amount_requested );
1313: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1309: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1310: itemkey => itemkey,
1311: aname => 'REQUESTED_CREDIT_LIMIT',
1312: avalue => l_amount_requested );
1313: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1314: itemkey => itemkey,
1315: aname => 'REQUESTOR_PERSON_ID',
1316: avalue => l_requestor_id );
1317: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1313: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1314: itemkey => itemkey,
1315: aname => 'REQUESTOR_PERSON_ID',
1316: avalue => l_requestor_id );
1317: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1318: itemkey => itemkey,
1319: aname => 'SOURCE_COL1',
1320: avalue => l_source_column1 );
1321: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1317: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1318: itemkey => itemkey,
1319: aname => 'SOURCE_COL1',
1320: avalue => l_source_column1 );
1321: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1322: itemkey => itemkey,
1323: aname => 'SOURCE_COL2',
1324: avalue => l_source_column2 );
1325: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1321: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1322: itemkey => itemkey,
1323: aname => 'SOURCE_COL2',
1324: avalue => l_source_column2 );
1325: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1326: itemkey => itemkey,
1327: aname => 'SOURCE_COL3',
1328: avalue => l_source_column3 );
1329: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1325: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1326: itemkey => itemkey,
1327: aname => 'SOURCE_COL3',
1328: avalue => l_source_column3 );
1329: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1330: itemkey => itemkey,
1331: aname => 'PARTY_NAME',
1332: avalue => l_party_name );
1333: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1329: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1330: itemkey => itemkey,
1331: aname => 'PARTY_NAME',
1332: avalue => l_party_name );
1333: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1334: itemkey => itemkey,
1335: aname => 'PARTY_NUMBER',
1336: avalue => l_party_number );
1337: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1333: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1334: itemkey => itemkey,
1335: aname => 'PARTY_NUMBER',
1336: avalue => l_party_number );
1337: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1338: itemkey => itemkey,
1339: aname => 'APPL_NOTES',
1340: avalue => l_notes );
1341: WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
1337: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1338: itemkey => itemkey,
1339: aname => 'APPL_NOTES',
1340: avalue => l_notes );
1341: WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
1342: itemkey => itemkey,
1343: aname => 'APPLICATION_DATE',
1344: avalue => l_application_date );
1345: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1341: WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
1342: itemkey => itemkey,
1343: aname => 'APPLICATION_DATE',
1344: avalue => l_application_date );
1345: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1346: itemkey => itemkey,
1347: aname => 'CREDIT_REQUEST_TYPE',
1348: avalue => l_credit_request_type );
1349:
1353: p_employee_id => l_requestor_id,
1354: p_user_name => l_requestor_user_name,
1355: p_display_name => l_requestor_display_name);
1356:
1357: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1358: itemkey => itemkey,
1359: aname => 'REQUESTOR_USER_NAME',
1360: avalue => l_requestor_user_name );
1361: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1357: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1358: itemkey => itemkey,
1359: aname => 'REQUESTOR_USER_NAME',
1360: avalue => l_requestor_user_name );
1361: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1362: itemkey => itemkey,
1363: aname => 'REQUESTOR_DISPLAY_NAME',
1364: avalue => l_requestor_display_name );
1365: ELSE
1369: INTO l_requestor_user_name
1370: FROM fnd_user
1371: WHERE user_id = l_requestor_id;
1372:
1373: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1374: itemkey => itemkey,
1375: aname => 'REQUESTOR_USER_NAME',
1376: avalue => l_requestor_user_name );
1377:
1374: itemkey => itemkey,
1375: aname => 'REQUESTOR_USER_NAME',
1376: avalue => l_requestor_user_name );
1377:
1378: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1379: itemkey => itemkey,
1380: aname => 'REQUESTOR_DISPLAY_NAME',
1381: avalue => l_requestor_user_name );
1382: EXCEPTION
1380: aname => 'REQUESTOR_DISPLAY_NAME',
1381: avalue => l_requestor_user_name );
1382: EXCEPTION
1383: WHEN NO_DATA_FOUND THEN
1384: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1385: 'FND User Not Found'|| sqlerrm);
1386: raise;
1387: WHEN OTHERS THEN
1388: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1384: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1385: 'FND User Not Found'|| sqlerrm);
1386: raise;
1387: WHEN OTHERS THEN
1388: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1389: 'Other Error '|| sqlerrm);
1390: raise;
1391: END;
1392: END IF;
1392: END IF;
1393: IF l_case_folder_number IS NOT NULL
1394: THEN
1395:
1396: WF_ENGINE.SetItemAttrNumber
1397: (itemtype => itemtype,
1398: itemkey => itemkey,
1399: aname => 'CASE_FOLDER_NUMBER',
1400: avalue => l_case_folder_number );
1401: END IF;
1402: IF l_score_model_id IS NOT NULL
1403: THEN
1404: l_score_model_already_set := 'T';
1405: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1406: itemkey => itemkey,
1407: aname => 'SCORE_MODEL_ID',
1408: avalue => l_score_model_id);
1409: END IF;
1418: EXCEPTION
1419: WHEN NO_DATA_FOUND THEN
1420: l_account_number := null;
1421: WHEN OTHERS THEN
1422: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1423: 'Getting Account Details SqlError: '|| sqlerrm);
1424: raise;
1425:
1426: END;
1423: 'Getting Account Details SqlError: '|| sqlerrm);
1424: raise;
1425:
1426: END;
1427: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1428: itemkey => itemkey,
1429: aname => 'ACCOUNT_NUMBER',
1430: avalue => l_account_number );
1431: END IF;
1444: EXCEPTION
1445: WHEN NO_DATA_FOUND THEN
1446: -- in case of no_data_found Assign Credit Analyst and send notification
1447: -- to CA.
1448: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1449: 'No Check List found for the combination');
1450: resultout := 'COMPLETE:NOTFOUND';
1451: return;
1452: WHEN OTHERS THEN
1449: 'No Check List found for the combination');
1450: resultout := 'COMPLETE:NOTFOUND';
1451: return;
1452: WHEN OTHERS THEN
1453: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1454: sqlerrm);
1455: raise;
1456:
1457: END;
1457: END;
1458: IF l_score_model_id IS NOT NULL
1459: AND l_score_model_already_set = 'F'
1460: THEN
1461: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1462: itemkey => itemkey,
1463: aname => 'SCORE_MODEL_ID',
1464: avalue => l_score_model_id);
1465:
1465:
1466: ELSIF l_score_model_id IS NULL
1467: THEN
1468: -- in case of null set to -99
1469: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1470: itemkey => itemkey,
1471: aname => 'SCORE_MODEL_ID',
1472: avalue => -99);
1473: END IF;
1473: END IF;
1474:
1475: IF l_check_list_id IS NULL
1476: THEN
1477: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1478: itemkey => itemkey,
1479: aname => 'CHECK_LIST_ID',
1480: avalue => -99);
1481: resultout := 'COMPLETE:NOTFOUND';
1479: aname => 'CHECK_LIST_ID',
1480: avalue => -99);
1481: resultout := 'COMPLETE:NOTFOUND';
1482: ELSE
1483: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1484: itemkey => itemkey,
1485: aname => 'CHECK_LIST_ID',
1486: avalue => l_check_list_id);
1487: --Update credit request table with checklistid
1508: BEGIN
1509: IF funcmode = 'RUN'
1510: THEN
1511: l_manual_analysis_flag :=
1512: WF_ENGINE.GetItemAttrText
1513: (itemtype => itemtype,
1514: itemkey => itemkey,
1515: aname => 'MANUAL_ANALYSIS_FLAG');
1516: IF ( l_manual_analysis_flag = 'Y' )
1525:
1526: END IF;
1527: EXCEPTION
1528: WHEN OTHERS THEN
1529: wf_core.context('AR_CMGT_WF_ENGINE','SET_ROUTING_STATUS',itemtype, itemkey,
1530: sqlerrm);
1531: raise;
1532: END;
1533:
1547: BEGIN
1548: IF funcmode = 'RUN'
1549: THEN
1550:
1551: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1552: itemkey => itemkey,
1553: aname => 'FAILURE_FUNCTION',
1554: avalue => 'SCORING_MODEL');
1555:
1553: aname => 'FAILURE_FUNCTION',
1554: avalue => 'SCORING_MODEL');
1555:
1556: l_score_model_id :=
1557: WF_ENGINE.getItemAttrText(itemtype => itemtype,
1558: itemkey => itemkey,
1559: aname => 'SCORE_MODEL_ID');
1560: IF l_score_model_id = -99
1561: THEN
1595:
1596: -- First get the requested currency and score_model_id
1597: IF funcmode = 'RUN'
1598: THEN
1599: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1600: itemkey => itemkey,
1601: aname => 'FAILURE_FUNCTION',
1602: avalue => 'SCORING_CURRENCY');
1603: l_score_model_id := WF_ENGINE.getItemAttrNumber
1599: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1600: itemkey => itemkey,
1601: aname => 'FAILURE_FUNCTION',
1602: avalue => 'SCORING_CURRENCY');
1603: l_score_model_id := WF_ENGINE.getItemAttrNumber
1604: (itemtype => itemtype,
1605: itemkey => itemkey,
1606: aname => 'SCORE_MODEL_ID');
1607:
1604: (itemtype => itemtype,
1605: itemkey => itemkey,
1606: aname => 'SCORE_MODEL_ID');
1607:
1608: l_limit_currency := WF_ENGINE.getItemAttrText
1609: (itemtype => itemtype,
1610: itemkey => itemkey,
1611: aname => 'LIMIT_CURRENCY');
1612:
1619: and submit_flag = 'Y'
1620: and TRUNC(nvl(end_date, SYSDATE)) >= TRUNC(sysdate)
1621: and currency = l_limit_currency;
1622:
1623: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1624: itemkey => itemkey,
1625: aname => 'SCORE_CURRENCY',
1626: avalue => l_score_currency);
1627: resultout := 'COMPLETE:SUCESS';
1631: resultout := 'COMPLETE:FAILURE';
1632: WHEN OTHERS THEN
1633: raise;
1634: resultout := 'COMPLETE:FAILURE';
1635: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_SCORING_CURRENCY',itemtype, itemkey,
1636: sqlerrm);
1637: END;
1638:
1639: END IF;
1653: BEGIN
1654:
1655: IF funcmode = 'RUN'
1656: THEN
1657: l_case_folder_id := WF_ENGINE.getItemAttrText
1658: (itemtype => itemtype,
1659: itemkey => itemkey,
1660: aname => 'CASE_FOLDER_ID');
1661: delete ar_cmgt_case_folders
1665: END IF;
1666: EXCEPTION
1667: WHEN OTHERS
1668: THEN
1669: wf_core.context('AR_CMGT_WF_ENGINE','UNDO_CASE_FOLDER',itemtype, itemkey,
1670: sqlerrm);
1671: raise;
1672: END;
1673:
1713: FROM ar_cmgt_credit_requests
1714: WHERE credit_request_id = itemkey;
1715: EXCEPTION
1716: WHEN OTHERS THEN
1717: wf_core.context ('AR_CMGT_WF_ENGINE','GATHER_DATA_POINTS',itemtype,itemkey,
1718: 'Error while getting records from AR_CMGT_CREDIT_REQUESTS',
1719: 'Sql Error: '||sqlerrm);
1720: raise;
1721: END;
1718: 'Error while getting records from AR_CMGT_CREDIT_REQUESTS',
1719: 'Sql Error: '||sqlerrm);
1720: raise;
1721: END;
1722: l_check_list_id := WF_ENGINE.getItemAttrNumber
1723: (itemtype => itemtype,
1724: itemkey => itemkey,
1725: aname => 'CHECK_LIST_ID');
1726: l_score_model_id := WF_ENGINE.getItemAttrNumber
1722: l_check_list_id := WF_ENGINE.getItemAttrNumber
1723: (itemtype => itemtype,
1724: itemkey => itemkey,
1725: aname => 'CHECK_LIST_ID');
1726: l_score_model_id := WF_ENGINE.getItemAttrNumber
1727: (itemtype => itemtype,
1728: itemkey => itemkey,
1729: aname => 'SCORE_MODEL_ID');
1730:
1727: (itemtype => itemtype,
1728: itemkey => itemkey,
1729: aname => 'SCORE_MODEL_ID');
1730:
1731: l_case_folder_number := WF_ENGINE.getItemAttrText
1732: (itemtype => itemtype,
1733: itemkey => itemkey,
1734: aname => 'CASE_FOLDER_NUMBER');
1735:
1754:
1755: -- this is the error due to some setup data missing
1756: IF l_resultout = 2
1757: THEN
1758: WF_ENGINE.setItemAttrText
1759: (itemtype => itemtype,
1760: itemkey => itemkey,
1761: aname => 'FAILURE_MESSAGE',
1762: avalue => l_error_message);
1759: (itemtype => itemtype,
1760: itemkey => itemkey,
1761: aname => 'FAILURE_MESSAGE',
1762: avalue => l_error_message);
1763: WF_ENGINE.setItemAttrText
1764: (itemtype => itemtype,
1765: itemkey => itemkey,
1766: aname => 'FAILURE_FUNCTION',
1767: avalue => 'GATHER_DATA_POINTS');
1769: return;
1770: END IF;
1771: IF l_resultout <> 0
1772: THEN
1773: wf_core.context ('AR_CMGT_WF_ENGINE','GATHER_DATA_POINTS',itemtype,itemkey,
1774: 'Unable to Generate Case Folder',l_error_message);
1775: raise BUILD_FAILURE;
1776: END IF;
1777: WF_ENGINE.setItemAttrNumber
1773: wf_core.context ('AR_CMGT_WF_ENGINE','GATHER_DATA_POINTS',itemtype,itemkey,
1774: 'Unable to Generate Case Folder',l_error_message);
1775: raise BUILD_FAILURE;
1776: END IF;
1777: WF_ENGINE.setItemAttrNumber
1778: (itemtype => itemtype,
1779: itemkey => itemkey,
1780: aname => 'CASE_FOLDER_ID',
1781: avalue => l_case_folder_id);
1778: (itemtype => itemtype,
1779: itemkey => itemkey,
1780: aname => 'CASE_FOLDER_ID',
1781: avalue => l_case_folder_id);
1782: WF_ENGINE.setItemAttrText
1783: (itemtype => itemtype,
1784: itemkey => itemkey,
1785: aname => 'LIMIT_CURRENCY',
1786: avalue => l_limit_currency);
1790: INTO l_case_folder_number, l_case_folder_date
1791: FROM ar_cmgt_case_folders
1792: WHERE case_folder_id = l_case_folder_id;
1793:
1794: WF_ENGINE.setItemAttrText
1795: (itemtype => itemtype,
1796: itemkey => itemkey,
1797: aname => 'CASE_FOLDER_NUMBER',
1798: avalue => l_case_folder_number);
1795: (itemtype => itemtype,
1796: itemkey => itemkey,
1797: aname => 'CASE_FOLDER_NUMBER',
1798: avalue => l_case_folder_number);
1799: WF_ENGINE.setItemAttrDate
1800: (itemtype => itemtype,
1801: itemkey => itemkey,
1802: aname => 'CASE_FOLDER_DATE',
1803: avalue => l_case_folder_date);
1805: WHEN NO_DATA_FOUND THEN
1806: l_case_folder_number := null;
1807: l_case_folder_date := null;
1808: WHEN OTHERS THEN
1809: wf_core.context ('AR_CMGT_WF_ENGINE','GATHER_DATA_POINTS',itemtype,itemkey,
1810: 'Unable to Get Case Folder Details, SqlError : '||sqlerrm);
1811: END;
1812: resultout := 'COMPLETE:SUCESS';
1813:
1836: BEGIN
1837: IF funcmode = 'RUN'
1838: THEN
1839: l_debug_msg := 'Calculate Score ';
1840: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
1841: (itemtype => itemtype,
1842: itemkey => itemkey,
1843: aname => 'CASE_FOLDER_ID');
1844: l_check_list_id := WF_ENGINE.GetItemAttrNumber
1840: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
1841: (itemtype => itemtype,
1842: itemkey => itemkey,
1843: aname => 'CASE_FOLDER_ID');
1844: l_check_list_id := WF_ENGINE.GetItemAttrNumber
1845: (itemtype => itemtype,
1846: itemkey => itemkey,
1847: aname => 'CHECK_LIST_ID');
1848: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1844: l_check_list_id := WF_ENGINE.GetItemAttrNumber
1845: (itemtype => itemtype,
1846: itemkey => itemkey,
1847: aname => 'CHECK_LIST_ID');
1848: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1849: itemkey => itemkey,
1850: aname => 'FAILURE_FUNCTION',
1851: avalue => 'MANUAL_ANALYSIS');
1852:
1859:
1860:
1861: IF l_resultout = 0
1862: THEN
1863: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1864: itemkey => itemkey,
1865: aname => 'SCORE',
1866: avalue => l_score);
1867: resultout := 'COMPLETE:SUCESS';
1870: END IF;
1871: END IF;
1872: EXCEPTION
1873: WHEN OTHERS THEN
1874: wf_core.context('AR_CMGT_WF_ENGINE','CALCULATE_SCORE',itemtype,
1875: itemkey, l_debug_msg, l_error_msg);
1876: raise;
1877: END;
1878:
1893:
1894: -- first check whether there are any manual data items exist
1895: -- if exists then automation is not possible and route to manual analysis
1896:
1897: l_score_model_id := WF_ENGINE.GetItemAttrNumber
1898: (itemtype => itemtype,
1899: itemkey => itemkey,
1900: aname => 'SCORE_MODEL_ID');
1901:
1898: (itemtype => itemtype,
1899: itemkey => itemkey,
1900: aname => 'SCORE_MODEL_ID');
1901:
1902: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1903: itemkey => itemkey,
1904: aname => 'FAILURE_FUNCTION',
1905: avalue => 'MANUAL_ANALYSIS');
1906:
1926: WHERE score_model_id = l_score_model_id
1927: AND submit_flag = 'Y'
1928: AND TRUNC(sysdate) between TRUNC(start_date) and TRUNC(nvl(end_date,SYSDATE));
1929:
1930: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1931: itemkey => itemkey,
1932: aname => 'AUTO_RULES_ID',
1933: avalue => l_auto_rules_id);
1934: resultout := 'COMPLETE:EXIST';
1936: EXCEPTION
1937: WHEN NO_DATA_FOUND THEN
1938: resultout := 'COMPLETE:NOTEXIST';
1939: WHEN OTHERS THEN
1940: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_AUTO_RULES',itemtype,
1941: itemkey, sqlerrm);
1942: raise;
1943: END;
1944: END IF;
1960: BEGIN
1961: IF funcmode = 'RUN'
1962: THEN
1963:
1964: l_auto_rules_id := WF_ENGINE.GetItemAttrNumber
1965: (itemtype => itemtype,
1966: itemkey => itemkey,
1967: aname => 'AUTO_RULES_ID');
1968:
1965: (itemtype => itemtype,
1966: itemkey => itemkey,
1967: aname => 'AUTO_RULES_ID');
1968:
1969: l_score := WF_ENGINE.GetItemAttrNumber
1970: (itemtype => itemtype,
1971: itemkey => itemkey,
1972: aname => 'SCORE');
1973: l_currency := WF_ENGINE.GetItemAttrText
1969: l_score := WF_ENGINE.GetItemAttrNumber
1970: (itemtype => itemtype,
1971: itemkey => itemkey,
1972: aname => 'SCORE');
1973: l_currency := WF_ENGINE.GetItemAttrText
1974: (itemtype => itemtype,
1975: itemkey => itemkey,
1976: aname => 'CURRENCY');
1977:
1984: AND l_score between credit_score_low and credit_score_high
1985: AND currency = l_currency;
1986:
1987:
1988: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1989: itemkey => itemkey,
1990: aname => 'SKIP_APPROVAL',
1991: avalue => l_skip_approval);
1992: resultout := 'COMPLETE:'||l_override_checklist;
1992: resultout := 'COMPLETE:'||l_override_checklist;
1993: EXCEPTION
1994: WHEN NO_DATA_FOUND THEN
1995: resultout := 'COMPLETE:N';
1996: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1997: itemkey => itemkey,
1998: aname => 'SKIP_APPROVAL',
1999: avalue => 'N');
2000: WHEN OTHERS THEN
1997: itemkey => itemkey,
1998: aname => 'SKIP_APPROVAL',
1999: avalue => 'N');
2000: WHEN OTHERS THEN
2001: wf_core.context('AR_CMGT_WF_ENGINE','OVERRIDE_CHECKLIST',itemtype,
2002: itemkey, sqlerrm);
2003: raise;
2004: END;
2005: END IF;
2027: THEN
2028:
2029: l_credit_request_id := itemkey;
2030:
2031: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2032: itemkey => itemkey,
2033: aname => 'FAILURE_FUNCTION',
2034: avalue => 'MANUAL_ANALYSIS');
2035:
2032: itemkey => itemkey,
2033: aname => 'FAILURE_FUNCTION',
2034: avalue => 'MANUAL_ANALYSIS');
2035:
2036: l_check_list_id := WF_ENGINE.GetItemAttrNumber
2037: (itemtype => itemtype,
2038: itemkey => itemkey,
2039: aname => 'CHECK_LIST_ID');
2040:
2037: (itemtype => itemtype,
2038: itemkey => itemkey,
2039: aname => 'CHECK_LIST_ID');
2040:
2041: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2042: (itemtype => itemtype,
2043: itemkey => itemkey,
2044: aname => 'CASE_FOLDER_ID');
2045:
2061: THEN
2062: resultout := 'COMPLETE:FAILURE';
2063: ELSIF l_resultout = 1
2064: THEN
2065: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_REQUIRED_DATA_POINTS',itemtype,
2066: itemkey, l_errmsg);
2067: raise BUILD_FAILURE;
2068: END IF;
2069:
2096: where score_model_id= l_score_model_id;
2097: BEGIN
2098: IF funcmode = 'RUN'
2099: THEN
2100: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2101: itemkey => itemkey,
2102: aname => 'FAILURE_FUNCTION',
2103: avalue => 'CHECK_SCORING_DATA_POINTS');
2104: l_score_model_id := WF_ENGINE.GetItemAttrNumber
2100: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2101: itemkey => itemkey,
2102: aname => 'FAILURE_FUNCTION',
2103: avalue => 'CHECK_SCORING_DATA_POINTS');
2104: l_score_model_id := WF_ENGINE.GetItemAttrNumber
2105: (itemtype => itemtype,
2106: itemkey => itemkey,
2107: aname => 'SCORE_MODEL_ID');
2108: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2104: l_score_model_id := WF_ENGINE.GetItemAttrNumber
2105: (itemtype => itemtype,
2106: itemkey => itemkey,
2107: aname => 'SCORE_MODEL_ID');
2108: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2109: (itemtype => itemtype,
2110: itemkey => itemkey,
2111: aname => 'CASE_FOLDER_ID');
2112:
2125: WHERE score_model_id = l_score_model_id;
2126:
2127: EXCEPTION
2128: WHEN OTHERS THEN
2129: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_SCORING_DATA_POINTS, Scoring Model Not Found '||
2130: 'Score Model Id : '||l_score_model_id ,itemtype,
2131: itemkey, sqlerrm);
2132: raise;
2133: END;
2150: return;
2151: END IF;
2152: EXCEPTION
2153: WHEN OTHERS THEN
2154: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_SCORING_DATA_POINTS, Data Point Details Not Available '||
2155: 'for Data Point Id : '||dp_id_collec_rec.data_point_id ,itemtype,
2156: itemkey, sqlerrm);
2157: raise;
2158: END;
2185: WHEN TOO_MANY_ROWS THEN
2186: resultout := 'COMPLETE:FAILURE';
2187: return;
2188: WHEN OTHERS THEN
2189: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_SCORING_DATA_POINTS',itemtype,
2190: itemkey, sqlerrm);
2191: raise;
2192: END;
2193: ELSIF l_null_zero_flag = 'Y' AND
2210:
2211: l_skip_approval VARCHAR2(1);
2212:
2213: BEGIN
2214: l_skip_approval := WF_ENGINE.getItemAttrText
2215: (itemtype => itemtype,
2216: itemkey => itemkey,
2217: aname => 'SKIP_APPROVAL');
2218: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2214: l_skip_approval := WF_ENGINE.getItemAttrText
2215: (itemtype => itemtype,
2216: itemkey => itemkey,
2217: aname => 'SKIP_APPROVAL');
2218: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2219: itemkey => itemkey,
2220: aname => 'FAILURE_FUNCTION',
2221: avalue => 'SKIP_APPROVAL');
2222: IF l_skip_approval = 'Y'
2240: -- recommendation route can be reached either by automation or manual.
2241: -- In case we reach recommendation via manual analysis then we will implement
2242: -- whatever user defined in case folder.
2243:
2244: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2245: itemkey => itemkey,
2246: aname => 'MANUAL_ANALYSIS_FLAG',
2247: avalue => 'Y');
2248: END IF;
2519: resultout := 'COMPLETE:SUCESS';
2520: return;
2521: END IF;
2522:
2523: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2524: itemkey => itemkey,
2525: aname => 'FAILURE_FUNCTION',
2526: avalue => 'GENERATE_RECOMMENDATION');
2527:
2524: itemkey => itemkey,
2525: aname => 'FAILURE_FUNCTION',
2526: avalue => 'GENERATE_RECOMMENDATION');
2527:
2528: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2529: (itemtype => itemtype,
2530: itemkey => itemkey,
2531: aname => 'CASE_FOLDER_ID');
2532:
2529: (itemtype => itemtype,
2530: itemkey => itemkey,
2531: aname => 'CASE_FOLDER_ID');
2532:
2533: l_score := WF_ENGINE.GetItemAttrNumber
2534: (itemtype => itemtype,
2535: itemkey => itemkey,
2536: aname => 'SCORE');
2537:
2534: (itemtype => itemtype,
2535: itemkey => itemkey,
2536: aname => 'SCORE');
2537:
2538: l_auto_rules_id := WF_ENGINE.GetItemAttrNumber
2539: (itemtype => itemtype,
2540: itemkey => itemkey,
2541: aname => 'AUTO_RULES_ID');
2542:
2574:
2575: END IF;
2576: EXCEPTION
2577: WHEN insert_failure THEN
2578: wf_core.context('AR_CMGT_WF_ENGINE','GENERATE_RECOMMENDATION',itemtype,
2579: itemkey,
2580: 'Error while inserting into ar_cmgt_cf_recommends',
2581: sqlerrm);
2582: raise;
2606: THEN
2607: -- dbms_session.set_sql_trace(true);
2608:
2609:
2610: l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2611: itemkey => itemkey,
2612: aname => 'CASE_FOLDER_ID');
2613:
2614:
2622: ame_api.getadminapprover(adminapproverout => l_admin_approver_rec);
2623: IF l_approver_rec_out.person_id = l_admin_approver_rec.person_id
2624: THEN
2625:
2626: wf_core.context('AR_CMGT_WF_ENGINE','APPROVAL_PROCESS',itemtype,
2627: itemkey, 'Approver is Admin User', null);
2628: --raise;
2629: END IF;
2630:
2638: l_approver_display_name);
2639:
2640: IF wf_directory.UserActive(l_approver_user_name)
2641: THEN
2642: WF_ENGINE.setItemAttrNumber(itemType => itemtype,
2643: itemKey => itemkey,
2644: aname => 'APPROVER_ID',
2645: avalue => l_approver_id);
2646:
2643: itemKey => itemkey,
2644: aname => 'APPROVER_ID',
2645: avalue => l_approver_id);
2646:
2647: WF_ENGINE.setItemAttrText(itemType => itemtype,
2648: itemKey => itemkey,
2649: aname => 'APPROVER_USER_NAME',
2650: avalue => l_approver_user_name);
2651: WF_ENGINE.setItemAttrText(itemType => itemtype,
2647: WF_ENGINE.setItemAttrText(itemType => itemtype,
2648: itemKey => itemkey,
2649: aname => 'APPROVER_USER_NAME',
2650: avalue => l_approver_user_name);
2651: WF_ENGINE.setItemAttrText(itemType => itemtype,
2652: itemKey => itemkey,
2653: aname => 'APPROVER_DISPLAY_NAME',
2654: avalue => l_approver_display_name);
2655: resultout := 'COMPLETE:EXIST';
2676: BEGIN
2677: IF funcmode = 'RUN'
2678: THEN
2679:
2680: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
2681: itemKey => itemkey,
2682: aname => 'CREDIT_REQUEST_ID',
2683: avalue => itemkey);
2684: l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2680: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
2681: itemKey => itemkey,
2682: aname => 'CREDIT_REQUEST_ID',
2683: avalue => itemkey);
2684: l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2685: itemkey => itemkey,
2686: aname => 'CASE_FOLDER_ID');
2687: l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2688: itemkey => itemkey,
2683: avalue => itemkey);
2684: l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2685: itemkey => itemkey,
2686: aname => 'CASE_FOLDER_ID');
2687: l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2688: itemkey => itemkey,
2689: aname => 'APPROVER_ID');
2690:
2691: ame_api.updateApprovalStatus2(applicationIdIn => 222,
2713: l_approver_id number;
2714: BEGIN
2715: IF funcmode = 'RUN'
2716: THEN
2717: l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2718: itemkey => itemkey,
2719: aname => 'CASE_FOLDER_ID');
2720: l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2721: itemkey => itemkey,
2716: THEN
2717: l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2718: itemkey => itemkey,
2719: aname => 'CASE_FOLDER_ID');
2720: l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2721: itemkey => itemkey,
2722: aname => 'APPROVER_ID');
2723: ame_api.updateApprovalStatus2(applicationIdIn => 222,
2724: transactionIdIn => l_case_folder_id,
2788: WHERE credit_request_id = itemkey;
2789:
2790: END;
2791:
2792: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2793: (itemtype => itemtype,
2794: itemkey => itemkey,
2795: aname => 'CASE_FOLDER_ID');
2796:
2825: p_error_msg => l_error_msg,
2826: p_return_status => l_return_status );
2827: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2828: THEN
2829: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_RECOMMENDATION',itemtype,
2830: itemkey,
2831: 'Error while applying Customer Hold for Party : '||l_party_id||' Cust Account Id '||
2832: l_cust_account_id||' Site Use Id '|| l_site_use_id ||' '|| l_error_msg,
2833: sqlerrm);
2844: p_error_msg => l_error_msg,
2845: p_return_status => l_return_status );
2846: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2847: THEN
2848: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_RECOMMENDATION',itemtype,
2849: itemkey,
2850: 'Error while applying Removing Hold for Party : '||l_party_id||' Cust Account Id '||
2851: l_cust_account_id||' Site Use Id '|| l_site_use_id ||' '|| l_error_msg,
2852: sqlerrm);
2870:
2871: EXCEPTION
2872: WHEN NO_DATA_FOUND
2873: THEN
2874: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2875: itemkey,'No data found in recommendations for Appeal',
2876: sqlerrm);
2877: raise;
2878:
2877: raise;
2878:
2879: WHEN OTHERS
2880: THEN
2881: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2882: itemkey,'Error accessing system options',
2883: sqlerrm);
2884: raise;
2885:
2921:
2922: EXCEPTION
2923: WHEN NO_DATA_FOUND
2924: THEN
2925: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2926: itemkey,'No data found in customer profiles',
2927: sqlerrm);
2928: raise;
2929:
2928: raise;
2929:
2930: WHEN OTHERS
2931: THEN
2932: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2933: itemkey,'Error accessing customer profiles',
2934: sqlerrm);
2935: raise;
2936:
2956:
2957: EXCEPTION
2958: WHEN NO_DATA_FOUND
2959: THEN
2960: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2961: itemkey,'No data found in profile for updation',
2962: sqlerrm);
2963: raise;
2964:
2963: raise;
2964:
2965: WHEN OTHERS
2966: THEN
2967: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2968: itemkey,'Error accessing customer profiles',
2969: sqlerrm);
2970: raise;
2971:
2991:
2992: EXCEPTION
2993: WHEN NO_DATA_FOUND
2994: THEN
2995: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
2996: itemkey,'No data found in cust profile for update',
2997: sqlerrm);
2998: raise;
2999:
2998: raise;
2999:
3000: WHEN OTHERS
3001: THEN
3002: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
3003: itemkey,'Error accessing customer profile',
3004: sqlerrm);
3005: raise;
3006:
3025:
3026: EXCEPTION
3027: WHEN NO_DATA_FOUND
3028: THEN
3029: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
3030: itemkey,'No data found in profile for updation',
3031: sqlerrm);
3032: raise;
3033:
3032: raise;
3033:
3034: WHEN OTHERS
3035: THEN
3036: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
3037: itemkey,'Error accessing customer profiles',
3038: sqlerrm);
3039: raise;
3040:
3105: FROM ar_cmgt_credit_requests
3106: WHERE credit_request_id = itemkey;
3107:
3108: END;
3109: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
3110: (itemtype => itemtype,
3111: itemkey => itemkey,
3112: aname => 'CASE_FOLDER_ID');
3113:
3110: (itemtype => itemtype,
3111: itemkey => itemkey,
3112: aname => 'CASE_FOLDER_ID');
3113:
3114: l_limit_currency := WF_ENGINE.getItemAttrText
3115: (itemtype => itemtype,
3116: itemkey => itemkey,
3117: aname => 'LIMIT_CURRENCY');
3118: /* Update the risk factor data point value */
3165: AND a.parent_credit_request_id = b.credit_request_id
3166: AND b.status <> 'PROCESSED';
3167:
3168: -- rows exist, so start workflow
3169: ar_cmgt_wf_engine.start_workflow (
3170: p_credit_request_id => l_parent_credit_request_id,
3171: p_application_status => 'SUBMIT' );
3172:
3173: EXCEPTION
3173: EXCEPTION
3174: WHEN NO_DATA_FOUND THEN
3175: NULL;
3176: WHEN OTHERS THEN
3177: wf_core.context('AR_CMGT_WF_ENGINE','POST_IMPLEMENT_PROCESS',itemtype,
3178: itemkey,
3179: 'Error while Getting Parent Credit Request Id',
3180: sqlerrm);
3181: raise;
3210:
3211: raise_recco_event(l_case_folder_id);
3212: EXCEPTION
3213: WHEN OTHERS THEN
3214: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
3215: itemkey,'Error while raise Business Event',
3216: sqlerrm);
3217: raise;
3218:
3229: resultout out NOCOPY varchar2) IS
3230:
3231: l_failure_function VARCHAR2(60);
3232: BEGIN
3233: l_failure_function := WF_ENGINE.getItemAttrText(
3234: itemtype => itemtype,
3235: itemkey => itemkey,
3236: aname => 'FAILURE_FUNCTION');
3237:
3236: aname => 'FAILURE_FUNCTION');
3237:
3238: IF l_failure_function IS NULL OR l_failure_function <> 'SKIP_APPROVAL'
3239: THEN
3240: WF_ENGINE.setItemAttrText(
3241: itemtype => itemtype,
3242: itemkey => itemkey,
3243: aname => 'SKIP_APPROVAL',
3244: avalue => 'N');
3253: resultout out NOCOPY varchar2) IS
3254:
3255: l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
3256: BEGIN
3257: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
3258: (itemtype => itemtype,
3259: itemkey => itemkey,
3260: aname => 'CASE_FOLDER_ID');
3261:
3283:
3284: BEGIN
3285: IF p_attribute_type = 'NUMBER'
3286: THEN
3287: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
3288: itemKey => p_itemkey,
3289: aname => p_attribute_name,
3290: avalue => p_attribute_value);
3291: ELSIF p_attribute_type = 'TEXT'
3289: aname => p_attribute_name,
3290: avalue => p_attribute_value);
3291: ELSIF p_attribute_type = 'TEXT'
3292: THEN
3293: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3294: itemKey => p_itemkey,
3295: aname => p_attribute_name,
3296: avalue => p_attribute_value);
3297: END IF;
3301: l_resource_id => to_number(p_attribute_value));
3302:
3303: -- now get credit_ananlyst details
3304: get_employee_details(l_person_id,l_user_name, l_display_name);
3305: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3306: itemKey => p_itemkey,
3307: aname => 'CREDIT_ANALYST_USER_NAME',
3308: avalue => l_user_name);
3309: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3305: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3306: itemKey => p_itemkey,
3307: aname => 'CREDIT_ANALYST_USER_NAME',
3308: avalue => l_user_name);
3309: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3310: itemKey => p_itemkey,
3311: aname => 'CREDIT_ANALYST_DISPLAY_NAME',
3312: avalue => l_display_name);
3313:
3344: WHEN TOO_MANY_ROWS THEN
3345: -- mean no credit request exists
3346: resultout := 'COMPLETE:N';
3347: WHEN OTHERS THEN
3348: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_CHILD_REQ_COMPLETED',itemtype,
3349: itemkey,'Error while Checking Child Credit Requests',
3350: sqlerrm);
3351: raise;
3352: END;
3376: THEN
3377:
3378: -- first validate for guarantors credit request
3379: -- there won't be any recommendations
3380: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3381: itemkey => itemkey,
3382: aname => 'FAILURE_FUNCTION',
3383: avalue => 'VALIDATE_RECO');
3384:
3381: itemkey => itemkey,
3382: aname => 'FAILURE_FUNCTION',
3383: avalue => 'VALIDATE_RECO');
3384:
3385: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
3386: (itemtype => itemtype,
3387: itemkey => itemkey,
3388: aname => 'CASE_FOLDER_ID');
3389: l_credit_request_type := WF_ENGINE.GetItemAttrText
3385: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
3386: (itemtype => itemtype,
3387: itemkey => itemkey,
3388: aname => 'CASE_FOLDER_ID');
3389: l_credit_request_type := WF_ENGINE.GetItemAttrText
3390: (itemtype => itemtype,
3391: itemkey => itemkey,
3392: aname => 'CREDIT_REQUEST_TYPE');
3393: --fetch the cursors
3413: IF l_reco_exist = 'N' and nvl(l_credit_request_type,'CREDIT_APP') <> 'GUARANTOR'
3414: THEN
3415: --call the program for populating the recommendations
3416: --before validating.
3417: AR_CMGT_WF_ENGINE.GET_EXT_SCORE_RECOMMENDATIONS(
3418: itemtype => itemtype,
3419: itemkey => itemkey,
3420: p_cf_id => l_case_folder_id,
3421: resultout=> resultout);
3447: WHEN TOO_MANY_ROWS THEN
3448: resultout := 'COMPLETE:FAILURE';
3449: return;
3450: WHEN OTHERS THEN
3451: wf_core.context('AR_CMGT_WF_ENGINE','VALIDATE_RECOMMENDATIONS',itemtype,
3452: itemkey,'Error while validating Recommendations for Party',
3453: sqlerrm);
3454: raise;
3455:
3473: END IF;
3474: END IF;
3475: EXCEPTION
3476: WHEN UNEXP_ERROR THEN
3477: wf_core.context('AR_CMGT_WF_ENGINE','VALIDATE_RECOMMENDATIONS',itemtype,
3478: itemkey,'Error while populating recommendation ',
3479: sqlerrm);
3480: raise;
3481: END;
3490: BEGIN
3491: IF funcmode = 'RUN'
3492: THEN
3493:
3494: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3495: itemkey => itemkey,
3496: aname => 'MANUAL_ANALYSIS_FLAG',
3497: avalue => 'H');
3498: END IF;
3534: BEGIN
3535: IF funcmode = 'RUN'
3536: THEN
3537:
3538: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3539: itemkey => itemkey,
3540: aname => 'FAILURE_FUNCTION',
3541: avalue => 'DUPLICATE_CASE_FOLDER');
3542:
3552: FROM ar_cmgt_credit_requests
3553: WHERE credit_request_id = itemkey;
3554: EXCEPTION
3555: WHEN OTHERS THEN
3556: wf_core.context ('AR_CMGT_WF_ENGINE','APPEAL_RESUB_DECISION',itemtype,itemkey,
3557: 'Error while getting records from AR_CMGT_CREDIT_REQUESTS',
3558: 'Sql Error: '||sqlerrm);
3559: raise;
3560: END;
3586: WHERE CREDIT_REQUEST_ID = itemkey;
3587:
3588: EXCEPTION
3589: WHEN OTHERS THEN
3590: wf_core.context ('AR_CMGT_WF_ENGINE','APPEAL_RESUB_DECISION',itemtype,itemkey,
3591: 'Error while getting parent records from AR_CMGT_CREDIT_REQUESTS',
3592: 'Sql Error: '||sqlerrm);
3593: raise;
3594: END;
3604: and type = 'CASE';
3605:
3606: EXCEPTION
3607: WHEN OTHERS THEN
3608: wf_core.context ('AR_CMGT_WF_ENGINE','APPEAL_RESUB_DECISION',itemtype,itemkey,
3609: 'Error while getting parent records from AR_CMGT_CASE_FOLDERS',
3610: 'Sql Error: '||sqlerrm);
3611: raise;
3612: END;
3672: raise l_dnb_data;
3673: END IF;
3674:
3675: --call the routine to populate all the wf_attributes
3676: AR_CMGT_WF_ENGINE.POPULATE_WF_ATTRIBUTES(
3677: itemtype => itemtype,
3678: itemkey => itemkey,
3679: actid => actid,
3680: funcmode => funcmode,
3922: hz_parties party
3923: WHERE credit_request_id = itemkey
3924: AND req.party_id = party.party_id;
3925: WHEN OTHERS THEN
3926: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTES',itemtype,itemkey,
3927: sqlerrm);
3928: raise;
3929: END;
3930:
3927: sqlerrm);
3928: raise;
3929: END;
3930:
3931: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
3932: itemkey => itemkey,
3933: aname => 'PARTY_ID',
3934: avalue => l_party_id );
3935:
3932: itemkey => itemkey,
3933: aname => 'PARTY_ID',
3934: avalue => l_party_id );
3935:
3936: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3937: itemkey => itemkey,
3938: aname => 'CREDIT_CLASSIFICATION',
3939: avalue => l_credit_classification );
3940: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3936: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3937: itemkey => itemkey,
3938: aname => 'CREDIT_CLASSIFICATION',
3939: avalue => l_credit_classification );
3940: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3941: itemkey => itemkey,
3942: aname => 'REVIEW_TYPE',
3943: avalue => l_review_type );
3944: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3940: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3941: itemkey => itemkey,
3942: aname => 'REVIEW_TYPE',
3943: avalue => l_review_type );
3944: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3945: itemkey => itemkey,
3946: aname => 'CURRENCY',
3947: avalue => l_currency );
3948: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3944: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3945: itemkey => itemkey,
3946: aname => 'CURRENCY',
3947: avalue => l_currency );
3948: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3949: itemkey => itemkey,
3950: aname => 'SOURCE_NAME',
3951: avalue => l_source_name );
3952: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3948: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3949: itemkey => itemkey,
3950: aname => 'SOURCE_NAME',
3951: avalue => l_source_name );
3952: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3953: itemkey => itemkey,
3954: aname => 'APPLICATION_NUMBER',
3955: avalue => l_application_number );
3956: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
3952: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3953: itemkey => itemkey,
3954: aname => 'APPLICATION_NUMBER',
3955: avalue => l_application_number );
3956: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
3957: itemkey => itemkey,
3958: aname => 'REQUESTED_CREDIT_LIMIT',
3959: avalue => l_amount_requested );
3960: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
3956: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
3957: itemkey => itemkey,
3958: aname => 'REQUESTED_CREDIT_LIMIT',
3959: avalue => l_amount_requested );
3960: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
3961: itemkey => itemkey,
3962: aname => 'REQUESTOR_PERSON_ID',
3963: avalue => l_requestor_id );
3964: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3960: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
3961: itemkey => itemkey,
3962: aname => 'REQUESTOR_PERSON_ID',
3963: avalue => l_requestor_id );
3964: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3965: itemkey => itemkey,
3966: aname => 'SOURCE_COL1',
3967: avalue => l_source_column1 );
3968: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3964: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3965: itemkey => itemkey,
3966: aname => 'SOURCE_COL1',
3967: avalue => l_source_column1 );
3968: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3969: itemkey => itemkey,
3970: aname => 'SOURCE_COL2',
3971: avalue => l_source_column2 );
3972: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3968: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3969: itemkey => itemkey,
3970: aname => 'SOURCE_COL2',
3971: avalue => l_source_column2 );
3972: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3973: itemkey => itemkey,
3974: aname => 'SOURCE_COL3',
3975: avalue => l_source_column3 );
3976: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3972: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3973: itemkey => itemkey,
3974: aname => 'SOURCE_COL3',
3975: avalue => l_source_column3 );
3976: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3977: itemkey => itemkey,
3978: aname => 'PARTY_NAME',
3979: avalue => l_party_name );
3980: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3976: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3977: itemkey => itemkey,
3978: aname => 'PARTY_NAME',
3979: avalue => l_party_name );
3980: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3981: itemkey => itemkey,
3982: aname => 'PARTY_NUMBER',
3983: avalue => l_party_number );
3984: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3980: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3981: itemkey => itemkey,
3982: aname => 'PARTY_NUMBER',
3983: avalue => l_party_number );
3984: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3985: itemkey => itemkey,
3986: aname => 'APPL_NOTES',
3987: avalue => l_notes );
3988: WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
3984: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3985: itemkey => itemkey,
3986: aname => 'APPL_NOTES',
3987: avalue => l_notes );
3988: WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
3989: itemkey => itemkey,
3990: aname => 'APPLICATION_DATE',
3991: avalue => l_application_date );
3992:
3996: p_employee_id => l_requestor_id,
3997: p_user_name => l_requestor_user_name,
3998: p_display_name => l_requestor_display_name);
3999:
4000: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4001: itemkey => itemkey,
4002: aname => 'REQUESTOR_USER_NAME',
4003: avalue => l_requestor_user_name );
4004: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4000: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4001: itemkey => itemkey,
4002: aname => 'REQUESTOR_USER_NAME',
4003: avalue => l_requestor_user_name );
4004: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4005: itemkey => itemkey,
4006: aname => 'REQUESTOR_DISPLAY_NAME',
4007: avalue => l_requestor_display_name );
4008: ELSE
4012: INTO l_requestor_user_name
4013: FROM fnd_user
4014: WHERE user_id = l_requestor_id;
4015:
4016: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4017: itemkey => itemkey,
4018: aname => 'REQUESTOR_USER_NAME',
4019: avalue => l_requestor_user_name );
4020:
4017: itemkey => itemkey,
4018: aname => 'REQUESTOR_USER_NAME',
4019: avalue => l_requestor_user_name );
4020:
4021: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4022: itemkey => itemkey,
4023: aname => 'REQUESTOR_DISPLAY_NAME',
4024: avalue => l_requestor_user_name );
4025: EXCEPTION
4023: aname => 'REQUESTOR_DISPLAY_NAME',
4024: avalue => l_requestor_user_name );
4025: EXCEPTION
4026: WHEN NO_DATA_FOUND THEN
4027: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTE',itemtype,itemkey,
4028: 'FND User Not Found'|| sqlerrm);
4029: raise;
4030: WHEN OTHERS THEN
4031: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTE',itemtype,itemkey,
4027: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTE',itemtype,itemkey,
4028: 'FND User Not Found'|| sqlerrm);
4029: raise;
4030: WHEN OTHERS THEN
4031: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTE',itemtype,itemkey,
4032: 'Other Error '|| sqlerrm);
4033: raise;
4034: END;
4035: END IF;
4044: EXCEPTION
4045: WHEN NO_DATA_FOUND THEN
4046: l_account_number := null;
4047: WHEN OTHERS THEN
4048: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTES',itemtype,itemkey,
4049: 'Getting Account Details SqlError: '|| sqlerrm);
4050: raise;
4051:
4052: END;
4049: 'Getting Account Details SqlError: '|| sqlerrm);
4050: raise;
4051:
4052: END;
4053: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4054: itemkey => itemkey,
4055: aname => 'ACCOUNT_NUMBER',
4056: avalue => l_account_number );
4057: END IF;
4068: FROM ar_cmgt_case_folders
4069: WHERE credit_request_id = itemkey
4070: AND type = 'CASE';
4071:
4072: WF_ENGINE.setItemAttrNumber
4073: (itemtype => itemtype,
4074: itemkey => itemkey,
4075: aname => 'CASE_FOLDER_ID',
4076: avalue => l_case_folder_id);
4074: itemkey => itemkey,
4075: aname => 'CASE_FOLDER_ID',
4076: avalue => l_case_folder_id);
4077:
4078: WF_ENGINE.setItemAttrText
4079: (itemtype => itemtype,
4080: itemkey => itemkey,
4081: aname => 'LIMIT_CURRENCY',
4082: avalue => l_limit_currency);
4081: aname => 'LIMIT_CURRENCY',
4082: avalue => l_limit_currency);
4083:
4084:
4085: WF_ENGINE.setItemAttrText
4086: (itemtype => itemtype,
4087: itemkey => itemkey,
4088: aname => 'CASE_FOLDER_NUMBER',
4089: avalue => l_case_folder_number);
4088: aname => 'CASE_FOLDER_NUMBER',
4089: avalue => l_case_folder_number);
4090:
4091:
4092: WF_ENGINE.setItemAttrDate
4093: (itemtype => itemtype,
4094: itemkey => itemkey,
4095: aname => 'CASE_FOLDER_DATE',
4096: avalue => l_creation_date_time);
4094: itemkey => itemkey,
4095: aname => 'CASE_FOLDER_DATE',
4096: avalue => l_creation_date_time);
4097:
4098: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
4099: itemkey => itemkey,
4100: aname => 'CHECK_LIST_ID',
4101: avalue => l_check_list_id);
4102:
4099: itemkey => itemkey,
4100: aname => 'CHECK_LIST_ID',
4101: avalue => l_check_list_id);
4102:
4103: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
4104: itemkey => itemkey,
4105: aname => 'SCORE_MODEL_ID',
4106: avalue => l_score_model_id);
4107: EXCEPTION
4105: aname => 'SCORE_MODEL_ID',
4106: avalue => l_score_model_id);
4107: EXCEPTION
4108: WHEN OTHERS THEN
4109: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTES',itemtype,itemkey,
4110: 'Error while getting Case Folder Details, SqlError: '|| sqlerrm);
4111: raise;
4112: END;
4113: END IF;
4158:
4159: --initialize raise BE flag to 'N'
4160: l_raise_event:='N';
4161:
4162: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4163: itemkey => itemkey,
4164: aname => 'FAILURE_FUNCTION',
4165: avalue => 'CHECK_EXTRNAL_DATA_POINTS');
4166: l_score_model_id := WF_ENGINE.GetItemAttrNumber
4162: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4163: itemkey => itemkey,
4164: aname => 'FAILURE_FUNCTION',
4165: avalue => 'CHECK_EXTRNAL_DATA_POINTS');
4166: l_score_model_id := WF_ENGINE.GetItemAttrNumber
4167: (itemtype => itemtype,
4168: itemkey => itemkey,
4169: aname => 'SCORE_MODEL_ID');
4170: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
4166: l_score_model_id := WF_ENGINE.GetItemAttrNumber
4167: (itemtype => itemtype,
4168: itemkey => itemkey,
4169: aname => 'SCORE_MODEL_ID');
4170: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
4171: (itemtype => itemtype,
4172: itemkey => itemkey,
4173: aname => 'CASE_FOLDER_ID');
4174: IF pg_debug = 'Y'
4217: END IF;
4218: END IF;
4219: EXCEPTION
4220: WHEN UNEXP_ERROR THEN
4221: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_EXTRNAL_DATA_POINTS',itemtype,itemkey,
4222: 'Error while submitting Con. Request Id, SqlError: '|| sqlerrm);
4223: raise;
4224: END;
4225: PROCEDURE GET_EXT_SCORE_RECOMMENDATIONS(
4358: raise;
4359: END;
4360:
4361:
4362: END AR_CMGT_WF_ENGINE;