[Home] [Help]
1: Package BODY AR_CMGT_WF_ENGINE AS
2: /* $Header: ARCMGWFB.pls 120.67 2011/08/02 14:39:47 mraymond ship $ */
3:
4: pg_wf_debug VARCHAR2(1) := ar_cmgt_util.get_wf_debug_flag;
5:
5:
6: PROCEDURE debug (
7: p_message_name IN VARCHAR2 ) IS
8: BEGIN
9: ar_cmgt_util.wf_debug ('AR_CMGT_WF_ENGINE',p_message_name );
10: END;
11:
12:
13: PROCEDURE raise_recco_event (p_case_folder_id IN NUMBER) AS
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:
308: END IF;
309: END LOOP;
310: EXCEPTION
311: WHEN OTHERS THEN
312: p_errmsg := 'Error in ar_cmgt_wf_engine.validate_reference_data_points '|| sqlerrm;
313: p_resultout := 1;
314: return;
315: END;
316: PROCEDURE VALIDATE_REQUIRED_DATA_POINTS (
428: role_display_name => l_role_display_name,
429: expiration_date => to_date('31/12/4712','DD/MM/RRRR'));
430: END IF;
431:
432: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
433: itemKey => itemkey,
434: aname => 'CREDIT_ANALYST_ROLE',
435: avalue => 'AR_CMGT_CREDIT_ANALYST_ROLE');
436:
479: BEGIN
480: SELECT employee_id
481: INTO l_employee_id
482: FROM FND_USER
483: WHERE user_name = wf_engine.context_text;
484: EXCEPTION
485: WHEN NO_DATA_FOUND THEN
486: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
487: 'Employee Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
482: FROM FND_USER
483: WHERE user_name = wf_engine.context_text;
484: EXCEPTION
485: WHEN NO_DATA_FOUND THEN
486: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
487: 'Employee Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
488: raise;
489: WHEN OTHERS THEN
490: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
483: WHERE user_name = wf_engine.context_text;
484: EXCEPTION
485: WHEN NO_DATA_FOUND THEN
486: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
487: 'Employee Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
488: raise;
489: WHEN OTHERS THEN
490: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
491: 'Sqlerror while getiing Employee Id '||sqlerrm);
486: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
487: 'Employee Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
488: raise;
489: WHEN OTHERS THEN
490: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
491: 'Sqlerror while getiing Employee Id '||sqlerrm);
492: raise;
493: END;
494: BEGIN
507:
508:
509: EXCEPTION
510: WHEN NO_DATA_FOUND THEN
511: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
512: 'Resource Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
513: raise;
514: WHEN OTHERS THEN
515: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
508:
509: EXCEPTION
510: WHEN NO_DATA_FOUND THEN
511: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
512: 'Resource Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
513: raise;
514: WHEN OTHERS THEN
515: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
516: 'Sqlerror while getting Resource Id'||sqlerrm);
511: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
512: 'Resource Id Not found for User:'||wf_engine.context_text ||' '||'Sqlerror '||sqlerrm);
513: raise;
514: WHEN OTHERS THEN
515: wf_core.context('AR_CMGT_WF_ENGINE','GET_CREDIT_ANALYST',itemtype, itemkey,
516: 'Sqlerror while getting Resource Id'||sqlerrm);
517: raise;
518: END;
519: get_employee_details(l_employee_id,l_user_name, l_display_name);
517: raise;
518: END;
519: get_employee_details(l_employee_id,l_user_name, l_display_name);
520:
521: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
522: itemKey => itemkey,
523: aname => 'CREDIT_ANALYST_USER_NAME',
524: avalue => l_user_name);
525: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
521: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
522: itemKey => itemkey,
523: aname => 'CREDIT_ANALYST_USER_NAME',
524: avalue => l_user_name);
525: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
526: itemKey => itemkey,
527: aname => 'CREDIT_ANALYST_ID',
528: avalue => l_resource_id);
529: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
525: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
526: itemKey => itemkey,
527: aname => 'CREDIT_ANALYST_ID',
528: avalue => l_resource_id);
529: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
530: itemKey => itemkey,
531: aname => 'CREDIT_ANALYST_DISPLAY_NAME',
532: avalue => l_display_name);
533:
565: END IF;
566:
567: /*RVIRIYAL: BUG#9300043: END*/
568:
569: l_failure_function := WF_ENGINE.getItemAttrText(
570: itemtype => itemtype,
571: itemkey => itemkey,
572: aname => 'FAILURE_FUNCTION');
573:
575: THEN
576:
577: l_notification_id :=
578: WF_NOTIFICATION.send
579: ( role => wf_engine.context_text,
580: msg_type => 'ARCMGTAP',
581: msg_name => 'MSG_TO_CA_INV_POLICY_SUBMIT',
582: callback => 'WF_ENGINE.CB',
583: context => itemtype||':'||itemkey||':'||to_char(actid));
578: WF_NOTIFICATION.send
579: ( role => wf_engine.context_text,
580: msg_type => 'ARCMGTAP',
581: msg_name => 'MSG_TO_CA_INV_POLICY_SUBMIT',
582: callback => 'WF_ENGINE.CB',
583: context => itemtype||':'||itemkey||':'||to_char(actid));
584:
585: ELSIF l_failure_function = 'SCORING_MODEL'
586: THEN
585: ELSIF l_failure_function = 'SCORING_MODEL'
586: THEN
587: l_notification_id :=
588: WF_NOTIFICATION.send
589: ( role => wf_engine.context_text,
590: msg_type => 'ARCMGTAP',
591: msg_name => 'MSG_CA_NO_SM',
592: callback => 'WF_ENGINE.CB',
593: context => itemtype||':'||itemkey||':'||to_char(actid));
588: WF_NOTIFICATION.send
589: ( role => wf_engine.context_text,
590: msg_type => 'ARCMGTAP',
591: msg_name => 'MSG_CA_NO_SM',
592: callback => 'WF_ENGINE.CB',
593: context => itemtype||':'||itemkey||':'||to_char(actid));
594: ELSIF l_failure_function = 'SCORING_CURRENCY'
595: THEN
596: l_notification_id :=
594: ELSIF l_failure_function = 'SCORING_CURRENCY'
595: THEN
596: l_notification_id :=
597: WF_NOTIFICATION.send
598: ( role => wf_engine.context_text,
599: msg_type => 'ARCMGTAP',
600: msg_name => 'MSG_TO_CA_CURRENCY_NOT_MATCH',
601: callback => 'WF_ENGINE.CB',
602: context => itemtype||':'||itemkey||':'||to_char(actid));
597: WF_NOTIFICATION.send
598: ( role => wf_engine.context_text,
599: msg_type => 'ARCMGTAP',
600: msg_name => 'MSG_TO_CA_CURRENCY_NOT_MATCH',
601: callback => 'WF_ENGINE.CB',
602: context => itemtype||':'||itemkey||':'||to_char(actid));
603: ELSIF l_failure_function = 'MANUAL_ANALYSIS'
604: THEN
605: l_notification_id :=
603: ELSIF l_failure_function = 'MANUAL_ANALYSIS'
604: THEN
605: l_notification_id :=
606: WF_NOTIFICATION.send
607: ( role => wf_engine.context_text,
608: msg_type => 'ARCMGTAP',
609: msg_name => 'MSG_TO_CA_MANUAL_ANALYSIS',
610: callback => 'WF_ENGINE.CB',
611: context => itemtype||':'||itemkey||':'||to_char(actid));
606: WF_NOTIFICATION.send
607: ( role => wf_engine.context_text,
608: msg_type => 'ARCMGTAP',
609: msg_name => 'MSG_TO_CA_MANUAL_ANALYSIS',
610: callback => 'WF_ENGINE.CB',
611: context => itemtype||':'||itemkey||':'||to_char(actid));
612: ELSIF l_failure_function = 'SKIP_APPROVAL'
613: THEN
614: l_notification_id :=
612: ELSIF l_failure_function = 'SKIP_APPROVAL'
613: THEN
614: l_notification_id :=
615: WF_NOTIFICATION.send
616: ( role => wf_engine.context_text,
617: msg_type => 'ARCMGTAP',
618: msg_name => 'REASG_MSG_TO_CA_SKIP_APPROVAL',
619: callback => 'WF_ENGINE.CB',
620: context => itemtype||':'||itemkey||':'||to_char(actid));
615: WF_NOTIFICATION.send
616: ( role => wf_engine.context_text,
617: msg_type => 'ARCMGTAP',
618: msg_name => 'REASG_MSG_TO_CA_SKIP_APPROVAL',
619: callback => 'WF_ENGINE.CB',
620: context => itemtype||':'||itemkey||':'||to_char(actid));
621: ELSIF l_failure_function = 'GATHER_DATA_POINTS'
622: THEN
623: l_notification_id :=
621: ELSIF l_failure_function = 'GATHER_DATA_POINTS'
622: THEN
623: l_notification_id :=
624: WF_NOTIFICATION.send
625: ( role => wf_engine.context_text,
626: msg_type => 'ARCMGTAP',
627: msg_name => 'MSG_TO_CA_NO_CF_CREATED',
628: callback => 'WF_ENGINE.CB',
629: context => itemtype||':'||itemkey||':'||to_char(actid));
624: WF_NOTIFICATION.send
625: ( role => wf_engine.context_text,
626: msg_type => 'ARCMGTAP',
627: msg_name => 'MSG_TO_CA_NO_CF_CREATED',
628: callback => 'WF_ENGINE.CB',
629: context => itemtype||':'||itemkey||':'||to_char(actid));
630: ELSIF l_failure_function = 'GENERATE_RECOMMENDATION'
631: THEN
632: l_notification_id :=
630: ELSIF l_failure_function = 'GENERATE_RECOMMENDATION'
631: THEN
632: l_notification_id :=
633: WF_NOTIFICATION.send
634: ( role => wf_engine.context_text,
635: msg_type => 'ARCMGTAP',
636: msg_name => 'MSG_TO_CA_NO_RECO',
637: callback => 'WF_ENGINE.CB',
638: context => itemtype||':'||itemkey||':'||to_char(actid));
633: WF_NOTIFICATION.send
634: ( role => wf_engine.context_text,
635: msg_type => 'ARCMGTAP',
636: msg_name => 'MSG_TO_CA_NO_RECO',
637: callback => 'WF_ENGINE.CB',
638: context => itemtype||':'||itemkey||':'||to_char(actid));
639: ELSIF l_failure_function = 'VALIDATE_RECO'
640: THEN
641: l_notification_id :=
639: ELSIF l_failure_function = 'VALIDATE_RECO'
640: THEN
641: l_notification_id :=
642: WF_NOTIFICATION.send
643: ( role => wf_engine.context_text,
644: msg_type => 'ARCMGTAP',
645: msg_name => 'MSG_TO_CA_INVALID_RECO',
646: callback => 'WF_ENGINE.CB',
647: context => itemtype||':'||itemkey||':'||to_char(actid));
642: WF_NOTIFICATION.send
643: ( role => wf_engine.context_text,
644: msg_type => 'ARCMGTAP',
645: msg_name => 'MSG_TO_CA_INVALID_RECO',
646: callback => 'WF_ENGINE.CB',
647: context => itemtype||':'||itemkey||':'||to_char(actid));
648:
649: ELSIF l_failure_function = 'DUPLICATE_CASE_FOLDER'
650: THEN
649: ELSIF l_failure_function = 'DUPLICATE_CASE_FOLDER'
650: THEN
651: l_notification_id :=
652: WF_NOTIFICATION.send
653: ( role => wf_engine.context_text,
654: msg_type => 'ARCMGTAP',
655: msg_name => 'MESSAGE_APPEAL_INITIATED',
656: callback => 'WF_ENGINE.CB',
657: context => itemtype||':'||itemkey||':'||to_char(actid));
652: WF_NOTIFICATION.send
653: ( role => wf_engine.context_text,
654: msg_type => 'ARCMGTAP',
655: msg_name => 'MESSAGE_APPEAL_INITIATED',
656: callback => 'WF_ENGINE.CB',
657: context => itemtype||':'||itemkey||':'||to_char(actid));
658:
659: END IF;
660: END IF;
700:
701: BEGIN
702: IF pg_wf_debug = 'Y'
703: THEN
704: debug('ar_cmgt_wf_engine.getCAfromrulesengine()+');
705: debug(' p_credit_request_id = ' || p_credit_request_id);
706: END IF;
707: /* 7113063 Make sure guarantor CR/CF are assigned to the same
708: analyst as the parent CR/CF. We'll do that by using the
731: IF pg_wf_debug = 'Y'
732: THEN
733: debug(' Using parent analyst directly');
734: debug(' returning p_analyst_id = ' || l_parent_analyst_id);
735: debug('ar_cmgt_wf_engine.getCAfromrulesengine()-');
736: END IF;
737:
738: p_credit_analyst_id := l_parent_analyst_id;
739: RETURN;
787: THEN
788: p_credit_analyst_id := NULL;
789: return;
790: WHEN OTHERS THEN
791: wf_core.context('AR_CMGT_WF_ENGINE','getCAFromRulesEngine',p_itemtype, p_credit_request_id,
792: 'Error While getting Rules Engine Parameter Details',sqlerrm);
793: raise;
794:
795:
856:
857: IF pg_wf_debug = 'Y'
858: THEN
859: debug(' returning credit_analyst_id = ' || p_credit_analyst_id);
860: debug('ar_cmgt_wf_engine.getCAfromrulesengine()-');
861: END IF;
862:
863: EXCEPTION
864: WHEN OTHERS THEN
861: END IF;
862:
863: EXCEPTION
864: WHEN OTHERS THEN
865: wf_core.context('AR_CMGT_WF_ENGINE','getCAFromRulesEngine',p_itemtype, p_credit_request_id,
866: 'Error While calling Rules Engine',sqlerrm);
867: raise;
868: END;
869:
882: l_dummy VARCHAR2(1);
883: BEGIN
884: IF funcmode = 'RUN'
885: THEN
886: l_credit_analyst_id := WF_ENGINE.getItemAttrNumber
887: (itemType => 'ARCMGTAP',
888: itemKey => itemkey,
889: aname => 'CREDIT_ANALYST_ID');
890:
911: EXCEPTION
912: WHEN NO_DATA_FOUND THEN
913: l_credit_analyst_id := NULL;
914: WHEN OTHERS THEN
915: wf_core.context('AR_CMGT_WF_ENGINE','ASSIGN_CREDIT_ANALYST',itemtype, itemkey,
916: 'Error While getting Credit Analyst Id',sqlerrm);
917: raise;
918: END;
919: END IF;
936: EXCEPTION
937: WHEN NO_DATA_FOUND THEN
938: l_credit_analyst_id := NULL;
939: WHEN OTHERS THEN
940: wf_core.context('AR_CMGT_WF_ENGINE','ASSIGN_CREDIT_ANALYST',itemtype, itemkey,
941: 'Error While validating Credit Analyst Id:'||l_credit_analyst_id,sqlerrm);
942: raise;
943: END;
944:
957: AND category = 'EMPLOYEE';
958:
959: EXCEPTION
960: WHEN OTHERS THEN
961: wf_core.context('AR_CMGT_WF_ENGINE','ASSIGN_CREDIT_ANALYST',itemtype, itemkey,
962: 'Error While getting Employee Id for Credit Analyst',sqlerrm);
963: raise;
964: END;
965:
972: last_update_login = fnd_global.login_id
973: WHERE credit_request_id = itemkey;
974:
975:
976: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
977: itemKey => itemkey,
978: aname => 'CREDIT_ANALYST_USER_NAME',
979: avalue => l_user_name);
980: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
976: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
977: itemKey => itemkey,
978: aname => 'CREDIT_ANALYST_USER_NAME',
979: avalue => l_user_name);
980: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
981: itemKey => itemkey,
982: aname => 'CREDIT_ANALYST_ID',
983: avalue => l_credit_analyst_id);
984: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
980: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
981: itemKey => itemkey,
982: aname => 'CREDIT_ANALYST_ID',
983: avalue => l_credit_analyst_id);
984: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
985: itemKey => itemkey,
986: aname => 'CREDIT_ANALYST_DISPLAY_NAME',
987: avalue => l_display_name);
988:
987: avalue => l_display_name);
988:
989: END IF; -- end of credit analyst is null
990: -- Stamp CA to case folder
991: l_case_folder_id := WF_ENGINE.getItemAttrNumber
992: (itemType => 'ARCMGTAP',
993: itemKey => itemkey,
994: aname => 'CASE_FOLDER_ID');
995: IF l_case_folder_id IS NOT NULL
1033:
1034: BEGIN
1035:
1036: IF pg_wf_debug = 'Y' THEN
1037: debug('ar_cmgt_wf_engine.start_workflow()+');
1038: debug(' p_credit_request_id = ' || p_credit_request_id);
1039: debug(' p_application_status = ' || p_application_status);
1040: END IF;
1041:
1039: debug(' p_application_status = ' || p_application_status);
1040: END IF;
1041:
1042: -- The following parameter will kickoff WF in async. mode
1043: l_save_threshold := WF_ENGINE.threshold;
1044:
1045: WF_ENGINE.threshold := -1;
1046:
1047: IF p_application_status = 'FINISH'
1041:
1042: -- The following parameter will kickoff WF in async. mode
1043: l_save_threshold := WF_ENGINE.threshold;
1044:
1045: WF_ENGINE.threshold := -1;
1046:
1047: IF p_application_status = 'FINISH'
1048: THEN
1049: WF_ENGINE.CreateProcess (itemType => 'ARCMGTAP',
1045: WF_ENGINE.threshold := -1;
1046:
1047: IF p_application_status = 'FINISH'
1048: THEN
1049: WF_ENGINE.CreateProcess (itemType => 'ARCMGTAP',
1050: itemKey => p_credit_request_id,
1051: process => 'AR_CMGT_APPLICATION_PROCESS');
1052:
1053: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
1049: WF_ENGINE.CreateProcess (itemType => 'ARCMGTAP',
1050: itemKey => p_credit_request_id,
1051: process => 'AR_CMGT_APPLICATION_PROCESS');
1052:
1053: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
1054: itemKey => p_credit_request_id,
1055: aname => 'CREDIT_REQUEST_ID',
1056: avalue => p_credit_request_id);
1057:
1055: aname => 'CREDIT_REQUEST_ID',
1056: avalue => p_credit_request_id);
1057:
1058:
1059: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
1060: itemKey => p_credit_request_id,
1061: aname => 'APPLICATION_STATUS',
1062: avalue => p_application_status);
1063:
1060: itemKey => p_credit_request_id,
1061: aname => 'APPLICATION_STATUS',
1062: avalue => p_application_status);
1063:
1064: WF_ENGINE.StartProcess ( itemType => 'ARCMGTAP',
1065: itemKey => p_credit_request_id);
1066:
1067: ELSIF p_application_status = 'SUBMIT'
1068: THEN
1067: ELSIF p_application_status = 'SUBMIT'
1068: THEN
1069:
1070: BEGIN
1071: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
1072: itemKey => p_credit_request_id,
1073: aname => 'APPLICATION_STATUS',
1074: avalue => p_application_status);
1075:
1075:
1076: /* Try to complete the usual BLOCK activity */
1077: BEGIN
1078:
1079: WF_ENGINE.CompleteActivity( itemType => 'ARCMGTAP',
1080: itemkey => p_credit_request_id,
1081: activity => 'BLOCK',
1082: result => NULL);
1083:
1084: EXCEPTION
1085: WHEN OTHERS THEN
1086: /* Unable to complete the activity, so try
1087: to handle the error with a RETRY */
1088: WF_ENGINE.HandleErrorAll(itemType => 'ARCMGTAP',
1089: itemKey => p_credit_request_id,
1090: activity => NULL,
1091: command => 'RETRY',
1092: result => '',
1094: END;
1095:
1096: EXCEPTION
1097: WHEN OTHERS THEN
1098: WF_ENGINE.CreateProcess (itemType => 'ARCMGTAP',
1099: itemKey => p_credit_request_id,
1100: process => 'AR_CMGT_APPLICATION_PROCESS');
1101: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
1102: itemKey => p_credit_request_id,
1097: WHEN OTHERS THEN
1098: WF_ENGINE.CreateProcess (itemType => 'ARCMGTAP',
1099: itemKey => p_credit_request_id,
1100: process => 'AR_CMGT_APPLICATION_PROCESS');
1101: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
1102: itemKey => p_credit_request_id,
1103: aname => 'CREDIT_REQUEST_ID',
1104: avalue => p_credit_request_id);
1105:
1102: itemKey => p_credit_request_id,
1103: aname => 'CREDIT_REQUEST_ID',
1104: avalue => p_credit_request_id);
1105:
1106: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
1107: itemKey => p_credit_request_id,
1108: aname => 'APPLICATION_STATUS',
1109: avalue => p_application_status);
1110:
1107: itemKey => p_credit_request_id,
1108: aname => 'APPLICATION_STATUS',
1109: avalue => p_application_status);
1110:
1111: WF_ENGINE.StartProcess ( itemType => 'ARCMGTAP',
1112: itemKey => p_credit_request_id);
1113:
1114:
1115: END;
1118: END IF;
1119:
1120:
1121:
1122: WF_ENGINE.threshold := l_save_threshold;
1123:
1124: IF pg_wf_debug = 'Y' THEN
1125: debug('ar_cmgt_wf_engine.start_workflow()-');
1126: END IF;
1121:
1122: WF_ENGINE.threshold := l_save_threshold;
1123:
1124: IF pg_wf_debug = 'Y' THEN
1125: debug('ar_cmgt_wf_engine.start_workflow()-');
1126: END IF;
1127: END;
1128:
1129: PROCEDURE GENERATE_CREDIT_CLASSIFICATION (
1158: SET credit_classification = l_credit_classification
1159: WHERE credit_request_id = itemkey;
1160: END IF; -- end of credit classification
1161: -- Now update the credit classification in wf
1162: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1163: itemkey => itemkey,
1164: aname => 'CREDIT_CLASSIFICATION',
1165: avalue => l_credit_classification );
1166: END IF;
1166: END IF;
1167:
1168: EXCEPTION
1169: WHEN others THEN
1170: wf_core.context('AR_CMGT_WF_ENGINE','GENERATE_CREDIT_CLASSIFICATION',itemtype, itemkey,
1171: sqlerrm);
1172: raise;
1173: END;
1174:
1228:
1229: IF funcmode = 'RUN'
1230: THEN
1231:
1232: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
1233: (itemtype => itemtype,
1234: itemkey => itemkey,
1235: aname => 'CASE_FOLDER_ID');
1236:
1260:
1261: IF funcmode = 'RUN'
1262: THEN
1263: l_application_status :=
1264: WF_ENGINE.GetItemAttrText
1265: (itemtype => itemtype,
1266: itemkey => itemkey,
1267: aname => 'APPLICATION_STATUS');
1268:
1273:
1274: EXCEPTION
1275: WHEN OTHERS
1276: THEN
1277: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_APPLICATION_STATUS',itemtype, itemkey,
1278: sqlerrm);
1279: raise;
1280: END;
1281:
1293: l_return_status VARCHAR2(1);
1294: BEGIN
1295: IF pg_wf_debug = 'Y'
1296: THEN
1297: debug('ar_cmgt_wf_engine.create_party_profile()+');
1298: END IF;
1299:
1300: -- need to check and if not exists then craeet party profile(ARCMHZCB.pls AR_CMGT_HZ_COVER_API)
1301: IF funcmode = 'RUN'
1307:
1308: /* 9283064 - Determine if request is party, acct, or site
1309: level. Only proceed if it is party-level */
1310:
1311: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1312: itemkey => itemkey,
1313: aname => 'PARTY_ID',
1314: avalue => l_party_id);
1315:
1356: IF pg_wf_debug = 'Y'
1357: THEN
1358: debug(' TCA API failed to create party profile');
1359: END IF;
1360: wf_core.context('AR_CMGT_WF_ENGINE','CREATE_PARTY_PROFILE',
1361: itemtype, itemkey, 'TCA API failure');
1362: raise;
1363: END IF;
1364:
1363: END IF;
1364:
1365: WHEN OTHERS
1366: THEN
1367: wf_core.context('AR_CMGT_WF_ENGINE','CREATE_PARTY_PROFILE',
1368: itemtype, itemkey,sqlerrm);
1369: raise;
1370: END;
1371:
1381: END IF;
1382:
1383: IF pg_wf_debug = 'Y'
1384: THEN
1385: debug('ar_cmgt_wf_engine.create_party_profile()-');
1386: END IF;
1387: END;
1388:
1389: PROCEDURE CHECK_CREDIT_POLICY (
1426: THEN
1427:
1428: -- based on credit request id get credit classification and review type
1429: -- to find valid check list and build case folder table.
1430: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1431: itemkey => itemkey,
1432: aname => 'FAILURE_FUNCTION',
1433: avalue => 'CREDIT_POLICY');
1434: BEGIN
1524:
1525: resultout := 'COMPLETE:NOTFOUND';
1526: --return;
1527: WHEN OTHERS THEN
1528: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1529: sqlerrm);
1530: raise;
1531: END;
1532:
1529: sqlerrm);
1530: raise;
1531: END;
1532:
1533: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1534: itemkey => itemkey,
1535: aname => 'CREDIT_CLASSIFICATION',
1536: avalue => l_credit_classification );
1537: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1533: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1534: itemkey => itemkey,
1535: aname => 'CREDIT_CLASSIFICATION',
1536: avalue => l_credit_classification );
1537: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1538: itemkey => itemkey,
1539: aname => 'REVIEW_TYPE',
1540: avalue => l_review_type );
1541: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1537: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1538: itemkey => itemkey,
1539: aname => 'REVIEW_TYPE',
1540: avalue => l_review_type );
1541: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1542: itemkey => itemkey,
1543: aname => 'CURRENCY',
1544: avalue => l_currency );
1545: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1541: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1542: itemkey => itemkey,
1543: aname => 'CURRENCY',
1544: avalue => l_currency );
1545: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1546: itemkey => itemkey,
1547: aname => 'SOURCE_NAME',
1548: avalue => l_source_name );
1549: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1545: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1546: itemkey => itemkey,
1547: aname => 'SOURCE_NAME',
1548: avalue => l_source_name );
1549: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1550: itemkey => itemkey,
1551: aname => 'APPLICATION_NUMBER',
1552: avalue => l_application_number );
1553: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1549: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1550: itemkey => itemkey,
1551: aname => 'APPLICATION_NUMBER',
1552: avalue => l_application_number );
1553: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1554: itemkey => itemkey,
1555: aname => 'REQUESTED_CREDIT_LIMIT',
1556: avalue => l_amount_requested );
1557: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1553: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1554: itemkey => itemkey,
1555: aname => 'REQUESTED_CREDIT_LIMIT',
1556: avalue => l_amount_requested );
1557: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1558: itemkey => itemkey,
1559: aname => 'REQUESTOR_PERSON_ID',
1560: avalue => l_requestor_id );
1561: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1557: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1558: itemkey => itemkey,
1559: aname => 'REQUESTOR_PERSON_ID',
1560: avalue => l_requestor_id );
1561: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1562: itemkey => itemkey,
1563: aname => 'SOURCE_COL1',
1564: avalue => l_source_column1 );
1565: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1561: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1562: itemkey => itemkey,
1563: aname => 'SOURCE_COL1',
1564: avalue => l_source_column1 );
1565: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1566: itemkey => itemkey,
1567: aname => 'SOURCE_COL2',
1568: avalue => l_source_column2 );
1569: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1565: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1566: itemkey => itemkey,
1567: aname => 'SOURCE_COL2',
1568: avalue => l_source_column2 );
1569: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1570: itemkey => itemkey,
1571: aname => 'SOURCE_COL3',
1572: avalue => l_source_column3 );
1573: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1569: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1570: itemkey => itemkey,
1571: aname => 'SOURCE_COL3',
1572: avalue => l_source_column3 );
1573: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1574: itemkey => itemkey,
1575: aname => 'PARTY_NAME',
1576: avalue => l_party_name );
1577: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1573: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1574: itemkey => itemkey,
1575: aname => 'PARTY_NAME',
1576: avalue => l_party_name );
1577: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1578: itemkey => itemkey,
1579: aname => 'PARTY_NUMBER',
1580: avalue => l_party_number );
1581: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1577: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1578: itemkey => itemkey,
1579: aname => 'PARTY_NUMBER',
1580: avalue => l_party_number );
1581: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1582: itemkey => itemkey,
1583: aname => 'APPL_NOTES',
1584: avalue => l_notes );
1585: WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
1581: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1582: itemkey => itemkey,
1583: aname => 'APPL_NOTES',
1584: avalue => l_notes );
1585: WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
1586: itemkey => itemkey,
1587: aname => 'APPLICATION_DATE',
1588: avalue => l_application_date );
1589: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1585: WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
1586: itemkey => itemkey,
1587: aname => 'APPLICATION_DATE',
1588: avalue => l_application_date );
1589: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1590: itemkey => itemkey,
1591: aname => 'CREDIT_REQUEST_TYPE',
1592: avalue => l_credit_request_type );
1593:
1597: p_employee_id => l_requestor_id,
1598: p_user_name => l_requestor_user_name,
1599: p_display_name => l_requestor_display_name);
1600:
1601: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1602: itemkey => itemkey,
1603: aname => 'REQUESTOR_USER_NAME',
1604: avalue => l_requestor_user_name );
1605: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1601: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1602: itemkey => itemkey,
1603: aname => 'REQUESTOR_USER_NAME',
1604: avalue => l_requestor_user_name );
1605: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1606: itemkey => itemkey,
1607: aname => 'REQUESTOR_DISPLAY_NAME',
1608: avalue => l_requestor_display_name );
1609: ELSE
1613: INTO l_requestor_user_name
1614: FROM fnd_user
1615: WHERE user_id = l_requestor_id;
1616:
1617: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1618: itemkey => itemkey,
1619: aname => 'REQUESTOR_USER_NAME',
1620: avalue => l_requestor_user_name );
1621:
1618: itemkey => itemkey,
1619: aname => 'REQUESTOR_USER_NAME',
1620: avalue => l_requestor_user_name );
1621:
1622: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1623: itemkey => itemkey,
1624: aname => 'REQUESTOR_DISPLAY_NAME',
1625: avalue => l_requestor_user_name );
1626: EXCEPTION
1624: aname => 'REQUESTOR_DISPLAY_NAME',
1625: avalue => l_requestor_user_name );
1626: EXCEPTION
1627: WHEN NO_DATA_FOUND THEN
1628: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1629: 'FND User Not Found'|| sqlerrm);
1630: raise;
1631: WHEN OTHERS THEN
1632: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1628: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1629: 'FND User Not Found'|| sqlerrm);
1630: raise;
1631: WHEN OTHERS THEN
1632: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1633: 'Other Error '|| sqlerrm);
1634: raise;
1635: END;
1636: END IF;
1636: END IF;
1637: IF l_case_folder_number IS NOT NULL
1638: THEN
1639:
1640: WF_ENGINE.SetItemAttrNumber
1641: (itemtype => itemtype,
1642: itemkey => itemkey,
1643: aname => 'CASE_FOLDER_NUMBER',
1644: avalue => l_case_folder_number );
1645: END IF;
1646: IF l_score_model_id IS NOT NULL
1647: THEN
1648: l_score_model_already_set := 'T';
1649: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1650: itemkey => itemkey,
1651: aname => 'SCORE_MODEL_ID',
1652: avalue => l_score_model_id);
1653: END IF;
1662: EXCEPTION
1663: WHEN NO_DATA_FOUND THEN
1664: l_account_number := null;
1665: WHEN OTHERS THEN
1666: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1667: 'Getting Account Details SqlError: '|| sqlerrm);
1668: raise;
1669:
1670: END;
1667: 'Getting Account Details SqlError: '|| sqlerrm);
1668: raise;
1669:
1670: END;
1671: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1672: itemkey => itemkey,
1673: aname => 'ACCOUNT_NUMBER',
1674: avalue => l_account_number );
1675: END IF;
1688: EXCEPTION
1689: WHEN NO_DATA_FOUND THEN
1690: -- in case of no_data_found Assign Credit Analyst and send notification
1691: -- to CA.
1692: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1693: 'No Check List found for the combination');
1694: resultout := 'COMPLETE:NOTFOUND';
1695: return;
1696: WHEN OTHERS THEN
1693: 'No Check List found for the combination');
1694: resultout := 'COMPLETE:NOTFOUND';
1695: return;
1696: WHEN OTHERS THEN
1697: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_CREDIT_POLICY',itemtype,itemkey,
1698: sqlerrm);
1699: raise;
1700:
1701: END;
1701: END;
1702: IF l_score_model_id IS NOT NULL
1703: AND l_score_model_already_set = 'F'
1704: THEN
1705: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1706: itemkey => itemkey,
1707: aname => 'SCORE_MODEL_ID',
1708: avalue => l_score_model_id);
1709:
1709:
1710: ELSIF l_score_model_id IS NULL
1711: THEN
1712: -- in case of null set to -99
1713: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1714: itemkey => itemkey,
1715: aname => 'SCORE_MODEL_ID',
1716: avalue => -99);
1717: END IF;
1717: END IF;
1718:
1719: IF l_check_list_id IS NULL
1720: THEN
1721: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1722: itemkey => itemkey,
1723: aname => 'CHECK_LIST_ID',
1724: avalue => -99);
1725: resultout := 'COMPLETE:NOTFOUND';
1723: aname => 'CHECK_LIST_ID',
1724: avalue => -99);
1725: resultout := 'COMPLETE:NOTFOUND';
1726: ELSE
1727: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
1728: itemkey => itemkey,
1729: aname => 'CHECK_LIST_ID',
1730: avalue => l_check_list_id);
1731: --Update credit request table with checklistid
1770: BEGIN
1771: IF funcmode = 'RUN'
1772: THEN
1773: l_manual_analysis_flag :=
1774: WF_ENGINE.GetItemAttrText
1775: (itemtype => itemtype,
1776: itemkey => itemkey,
1777: aname => 'MANUAL_ANALYSIS_FLAG');
1778: IF ( l_manual_analysis_flag = 'Y' )
1797: END IF;
1798:
1799: IF (l_amount_requested IS NOT NULL) THEN
1800: l_amount_requested := unformat_amount(l_amount_requested); -- Added by rravikir - Bug 10083461
1801: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
1802: itemkey => itemkey,
1803: aname => 'REQUESTED_CREDIT_LIMIT',
1804: avalue => l_amount_requested );
1805: END IF;
1814:
1815: END IF;
1816: EXCEPTION
1817: WHEN OTHERS THEN
1818: wf_core.context('AR_CMGT_WF_ENGINE','SET_ROUTING_STATUS',itemtype, itemkey,
1819: sqlerrm);
1820: raise;
1821: END;
1822:
1837: BEGIN
1838: IF funcmode = 'RUN'
1839: THEN
1840:
1841: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1842: itemkey => itemkey,
1843: aname => 'FAILURE_FUNCTION',
1844: avalue => 'SCORING_MODEL');
1845:
1843: aname => 'FAILURE_FUNCTION',
1844: avalue => 'SCORING_MODEL');
1845:
1846: l_score_model_id :=
1847: WF_ENGINE.getItemAttrText(itemtype => itemtype,
1848: itemkey => itemkey,
1849: aname => 'SCORE_MODEL_ID');
1850: /*
1851: Bug# 9338716: RVIRIYAL: START
1860: INTO l_trx_amount
1861: FROM ar_cmgt_credit_requests
1862: WHERE credit_request_id = itemkey;
1863:
1864: WF_ENGINE.setItemAttrNumber
1865: (itemtype => itemtype,
1866: itemkey => itemkey,
1867: aname => 'TRX_AMOUNT',
1868: avalue => l_trx_amount);
1907:
1908: -- First get the requested currency and score_model_id
1909: IF funcmode = 'RUN'
1910: THEN
1911: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1912: itemkey => itemkey,
1913: aname => 'FAILURE_FUNCTION',
1914: avalue => 'SCORING_CURRENCY');
1915: l_score_model_id := WF_ENGINE.getItemAttrNumber
1911: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1912: itemkey => itemkey,
1913: aname => 'FAILURE_FUNCTION',
1914: avalue => 'SCORING_CURRENCY');
1915: l_score_model_id := WF_ENGINE.getItemAttrNumber
1916: (itemtype => itemtype,
1917: itemkey => itemkey,
1918: aname => 'SCORE_MODEL_ID');
1919:
1916: (itemtype => itemtype,
1917: itemkey => itemkey,
1918: aname => 'SCORE_MODEL_ID');
1919:
1920: l_limit_currency := WF_ENGINE.getItemAttrText
1921: (itemtype => itemtype,
1922: itemkey => itemkey,
1923: aname => 'LIMIT_CURRENCY');
1924:
1931: and submit_flag = 'Y'
1932: and TRUNC(nvl(end_date, SYSDATE)) >= TRUNC(sysdate)
1933: and ((currency = l_limit_currency) OR (nvl(skip_currency_test_flag, 'N') = 'Y')); -- Added for bug 8600040
1934:
1935: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1936: itemkey => itemkey,
1937: aname => 'SCORE_CURRENCY',
1938: avalue => l_score_currency);
1939: resultout := 'COMPLETE:SUCESS';
1943: resultout := 'COMPLETE:FAILURE';
1944: WHEN OTHERS THEN
1945: raise;
1946: resultout := 'COMPLETE:FAILURE';
1947: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_SCORING_CURRENCY',itemtype, itemkey,
1948: sqlerrm);
1949: END;
1950:
1951: END IF;
1965: BEGIN
1966:
1967: IF funcmode = 'RUN'
1968: THEN
1969: l_case_folder_id := WF_ENGINE.getItemAttrText
1970: (itemtype => itemtype,
1971: itemkey => itemkey,
1972: aname => 'CASE_FOLDER_ID');
1973: delete ar_cmgt_case_folders
1977: END IF;
1978: EXCEPTION
1979: WHEN OTHERS
1980: THEN
1981: wf_core.context('AR_CMGT_WF_ENGINE','UNDO_CASE_FOLDER',itemtype, itemkey,
1982: sqlerrm);
1983: raise;
1984: END;
1985:
2025: FROM ar_cmgt_credit_requests
2026: WHERE credit_request_id = itemkey;
2027: EXCEPTION
2028: WHEN OTHERS THEN
2029: wf_core.context ('AR_CMGT_WF_ENGINE','GATHER_DATA_POINTS',itemtype,itemkey,
2030: 'Error while getting records from AR_CMGT_CREDIT_REQUESTS',
2031: 'Sql Error: '||sqlerrm);
2032: raise;
2033: END;
2030: 'Error while getting records from AR_CMGT_CREDIT_REQUESTS',
2031: 'Sql Error: '||sqlerrm);
2032: raise;
2033: END;
2034: l_check_list_id := WF_ENGINE.getItemAttrNumber
2035: (itemtype => itemtype,
2036: itemkey => itemkey,
2037: aname => 'CHECK_LIST_ID');
2038: l_score_model_id := WF_ENGINE.getItemAttrNumber
2034: l_check_list_id := WF_ENGINE.getItemAttrNumber
2035: (itemtype => itemtype,
2036: itemkey => itemkey,
2037: aname => 'CHECK_LIST_ID');
2038: l_score_model_id := WF_ENGINE.getItemAttrNumber
2039: (itemtype => itemtype,
2040: itemkey => itemkey,
2041: aname => 'SCORE_MODEL_ID');
2042:
2039: (itemtype => itemtype,
2040: itemkey => itemkey,
2041: aname => 'SCORE_MODEL_ID');
2042:
2043: l_case_folder_number := WF_ENGINE.getItemAttrText
2044: (itemtype => itemtype,
2045: itemkey => itemkey,
2046: aname => 'CASE_FOLDER_NUMBER');
2047:
2066:
2067: -- this is the error due to some setup data missing
2068: IF l_resultout = 2
2069: THEN
2070: WF_ENGINE.setItemAttrText
2071: (itemtype => itemtype,
2072: itemkey => itemkey,
2073: aname => 'FAILURE_MESSAGE',
2074: avalue => l_error_message);
2071: (itemtype => itemtype,
2072: itemkey => itemkey,
2073: aname => 'FAILURE_MESSAGE',
2074: avalue => l_error_message);
2075: WF_ENGINE.setItemAttrText
2076: (itemtype => itemtype,
2077: itemkey => itemkey,
2078: aname => 'FAILURE_FUNCTION',
2079: avalue => 'GATHER_DATA_POINTS');
2081: return;
2082: END IF;
2083: IF l_resultout <> 0
2084: THEN
2085: wf_core.context ('AR_CMGT_WF_ENGINE','GATHER_DATA_POINTS',itemtype,itemkey,
2086: 'Unable to Generate Case Folder',l_error_message);
2087: raise BUILD_FAILURE;
2088: END IF;
2089: WF_ENGINE.setItemAttrNumber
2085: wf_core.context ('AR_CMGT_WF_ENGINE','GATHER_DATA_POINTS',itemtype,itemkey,
2086: 'Unable to Generate Case Folder',l_error_message);
2087: raise BUILD_FAILURE;
2088: END IF;
2089: WF_ENGINE.setItemAttrNumber
2090: (itemtype => itemtype,
2091: itemkey => itemkey,
2092: aname => 'CASE_FOLDER_ID',
2093: avalue => l_case_folder_id);
2090: (itemtype => itemtype,
2091: itemkey => itemkey,
2092: aname => 'CASE_FOLDER_ID',
2093: avalue => l_case_folder_id);
2094: WF_ENGINE.setItemAttrText
2095: (itemtype => itemtype,
2096: itemkey => itemkey,
2097: aname => 'LIMIT_CURRENCY',
2098: avalue => l_limit_currency);
2102: INTO l_case_folder_number, l_case_folder_date
2103: FROM ar_cmgt_case_folders
2104: WHERE case_folder_id = l_case_folder_id;
2105:
2106: WF_ENGINE.setItemAttrText
2107: (itemtype => itemtype,
2108: itemkey => itemkey,
2109: aname => 'CASE_FOLDER_NUMBER',
2110: avalue => l_case_folder_number);
2107: (itemtype => itemtype,
2108: itemkey => itemkey,
2109: aname => 'CASE_FOLDER_NUMBER',
2110: avalue => l_case_folder_number);
2111: WF_ENGINE.setItemAttrDate
2112: (itemtype => itemtype,
2113: itemkey => itemkey,
2114: aname => 'CASE_FOLDER_DATE',
2115: avalue => l_case_folder_date);
2117: WHEN NO_DATA_FOUND THEN
2118: l_case_folder_number := null;
2119: l_case_folder_date := null;
2120: WHEN OTHERS THEN
2121: wf_core.context ('AR_CMGT_WF_ENGINE','GATHER_DATA_POINTS',itemtype,itemkey,
2122: 'Unable to Get Case Folder Details, SqlError : '||sqlerrm);
2123: END;
2124: resultout := 'COMPLETE:SUCESS';
2125:
2148: BEGIN
2149: IF funcmode = 'RUN'
2150: THEN
2151: l_debug_msg := 'Calculate Score ';
2152: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2153: (itemtype => itemtype,
2154: itemkey => itemkey,
2155: aname => 'CASE_FOLDER_ID');
2156: l_check_list_id := WF_ENGINE.GetItemAttrNumber
2152: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2153: (itemtype => itemtype,
2154: itemkey => itemkey,
2155: aname => 'CASE_FOLDER_ID');
2156: l_check_list_id := WF_ENGINE.GetItemAttrNumber
2157: (itemtype => itemtype,
2158: itemkey => itemkey,
2159: aname => 'CHECK_LIST_ID');
2160: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2156: l_check_list_id := WF_ENGINE.GetItemAttrNumber
2157: (itemtype => itemtype,
2158: itemkey => itemkey,
2159: aname => 'CHECK_LIST_ID');
2160: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2161: itemkey => itemkey,
2162: aname => 'FAILURE_FUNCTION',
2163: avalue => 'MANUAL_ANALYSIS');
2164:
2171:
2172:
2173: IF l_resultout = 0
2174: THEN
2175: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
2176: itemkey => itemkey,
2177: aname => 'SCORE',
2178: avalue => l_score);
2179: resultout := 'COMPLETE:SUCESS';
2182: END IF;
2183: END IF;
2184: EXCEPTION
2185: WHEN OTHERS THEN
2186: wf_core.context('AR_CMGT_WF_ENGINE','CALCULATE_SCORE',itemtype,
2187: itemkey, l_debug_msg, l_error_msg);
2188: raise;
2189: END;
2190:
2205:
2206: -- first check whether there are any manual data items exist
2207: -- if exists then automation is not possible and route to manual analysis
2208:
2209: l_score_model_id := WF_ENGINE.GetItemAttrNumber
2210: (itemtype => itemtype,
2211: itemkey => itemkey,
2212: aname => 'SCORE_MODEL_ID');
2213:
2210: (itemtype => itemtype,
2211: itemkey => itemkey,
2212: aname => 'SCORE_MODEL_ID');
2213:
2214: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2215: itemkey => itemkey,
2216: aname => 'FAILURE_FUNCTION',
2217: avalue => 'MANUAL_ANALYSIS');
2218:
2238: WHERE score_model_id = l_score_model_id
2239: AND submit_flag = 'Y'
2240: AND TRUNC(sysdate) between TRUNC(start_date) and TRUNC(nvl(end_date,SYSDATE));
2241:
2242: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
2243: itemkey => itemkey,
2244: aname => 'AUTO_RULES_ID',
2245: avalue => l_auto_rules_id);
2246: resultout := 'COMPLETE:EXIST';
2248: EXCEPTION
2249: WHEN NO_DATA_FOUND THEN
2250: resultout := 'COMPLETE:NOTEXIST';
2251: WHEN OTHERS THEN
2252: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_AUTO_RULES',itemtype,
2253: itemkey, sqlerrm);
2254: raise;
2255: END;
2256: END IF;
2274: BEGIN
2275: IF funcmode = 'RUN'
2276: THEN
2277:
2278: l_auto_rules_id := WF_ENGINE.GetItemAttrNumber
2279: (itemtype => itemtype,
2280: itemkey => itemkey,
2281: aname => 'AUTO_RULES_ID');
2282:
2279: (itemtype => itemtype,
2280: itemkey => itemkey,
2281: aname => 'AUTO_RULES_ID');
2282:
2283: l_score := WF_ENGINE.GetItemAttrNumber
2284: (itemtype => itemtype,
2285: itemkey => itemkey,
2286: aname => 'SCORE');
2287: l_currency := WF_ENGINE.GetItemAttrText
2283: l_score := WF_ENGINE.GetItemAttrNumber
2284: (itemtype => itemtype,
2285: itemkey => itemkey,
2286: aname => 'SCORE');
2287: l_currency := WF_ENGINE.GetItemAttrText
2288: (itemtype => itemtype,
2289: itemkey => itemkey,
2290: aname => 'CURRENCY');
2291: -- Fixed for bug 9871620 - Start
2288: (itemtype => itemtype,
2289: itemkey => itemkey,
2290: aname => 'CURRENCY');
2291: -- Fixed for bug 9871620 - Start
2292: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2293: (itemtype => itemtype,
2294: itemkey => itemkey,
2295: aname => 'CASE_FOLDER_ID');
2296: -- Fixed for bug 9871620 - End
2313: WHERE auto_rules_id = l_auto_rules_id
2314: AND l_score between credit_score_low and credit_score_high
2315: AND currency = l_trans_currency; -- Fixed for bug 9871620
2316:
2317: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2318: itemkey => itemkey,
2319: aname => 'SKIP_APPROVAL',
2320: avalue => l_skip_approval);
2321: resultout := 'COMPLETE:'||l_override_checklist;
2321: resultout := 'COMPLETE:'||l_override_checklist;
2322: EXCEPTION
2323: WHEN NO_DATA_FOUND THEN
2324: resultout := 'COMPLETE:N';
2325: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2326: itemkey => itemkey,
2327: aname => 'SKIP_APPROVAL',
2328: avalue => 'N');
2329: WHEN OTHERS THEN
2326: itemkey => itemkey,
2327: aname => 'SKIP_APPROVAL',
2328: avalue => 'N');
2329: WHEN OTHERS THEN
2330: wf_core.context('AR_CMGT_WF_ENGINE','OVERRIDE_CHECKLIST',itemtype,
2331: itemkey, sqlerrm);
2332: raise;
2333: END;
2334: END IF;
2356: THEN
2357:
2358: l_credit_request_id := itemkey;
2359:
2360: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2361: itemkey => itemkey,
2362: aname => 'FAILURE_FUNCTION',
2363: avalue => 'MANUAL_ANALYSIS');
2364:
2361: itemkey => itemkey,
2362: aname => 'FAILURE_FUNCTION',
2363: avalue => 'MANUAL_ANALYSIS');
2364:
2365: l_check_list_id := WF_ENGINE.GetItemAttrNumber
2366: (itemtype => itemtype,
2367: itemkey => itemkey,
2368: aname => 'CHECK_LIST_ID');
2369:
2366: (itemtype => itemtype,
2367: itemkey => itemkey,
2368: aname => 'CHECK_LIST_ID');
2369:
2370: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2371: (itemtype => itemtype,
2372: itemkey => itemkey,
2373: aname => 'CASE_FOLDER_ID');
2374:
2390: THEN
2391: resultout := 'COMPLETE:FAILURE';
2392: ELSIF l_resultout = 1
2393: THEN
2394: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_REQUIRED_DATA_POINTS',itemtype,
2395: itemkey, l_errmsg);
2396: raise BUILD_FAILURE;
2397: END IF;
2398:
2425: where score_model_id= l_score_model_id;
2426: BEGIN
2427: IF funcmode = 'RUN'
2428: THEN
2429: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2430: itemkey => itemkey,
2431: aname => 'FAILURE_FUNCTION',
2432: avalue => 'CHECK_SCORING_DATA_POINTS');
2433: l_score_model_id := WF_ENGINE.GetItemAttrNumber
2429: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2430: itemkey => itemkey,
2431: aname => 'FAILURE_FUNCTION',
2432: avalue => 'CHECK_SCORING_DATA_POINTS');
2433: l_score_model_id := WF_ENGINE.GetItemAttrNumber
2434: (itemtype => itemtype,
2435: itemkey => itemkey,
2436: aname => 'SCORE_MODEL_ID');
2437: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2433: l_score_model_id := WF_ENGINE.GetItemAttrNumber
2434: (itemtype => itemtype,
2435: itemkey => itemkey,
2436: aname => 'SCORE_MODEL_ID');
2437: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2438: (itemtype => itemtype,
2439: itemkey => itemkey,
2440: aname => 'CASE_FOLDER_ID');
2441:
2454: WHERE score_model_id = l_score_model_id;
2455:
2456: EXCEPTION
2457: WHEN OTHERS THEN
2458: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_SCORING_DATA_POINTS, Scoring Model Not Found '||
2459: 'Score Model Id : '||l_score_model_id ,itemtype,
2460: itemkey, sqlerrm);
2461: raise;
2462: END;
2479: return;
2480: END IF;
2481: EXCEPTION
2482: WHEN OTHERS THEN
2483: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_SCORING_DATA_POINTS, Data Point Details Not Available '||
2484: 'for Data Point Id : '||dp_id_collec_rec.data_point_id ,itemtype,
2485: itemkey, sqlerrm);
2486: raise;
2487: END;
2514: WHEN TOO_MANY_ROWS THEN
2515: resultout := 'COMPLETE:FAILURE';
2516: return;
2517: WHEN OTHERS THEN
2518: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_SCORING_DATA_POINTS',itemtype,
2519: itemkey, sqlerrm);
2520: raise;
2521: END;
2522: ELSIF l_null_zero_flag = 'Y' AND
2557: AND credit_request_id = itemkey;
2558: -- Fix for Bug 8792071 - End
2559:
2560: BEGIN
2561: l_skip_approval := WF_ENGINE.getItemAttrText
2562: (itemtype => itemtype,
2563: itemkey => itemkey,
2564: aname => 'SKIP_APPROVAL');
2565: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2561: l_skip_approval := WF_ENGINE.getItemAttrText
2562: (itemtype => itemtype,
2563: itemkey => itemkey,
2564: aname => 'SKIP_APPROVAL');
2565: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2566: itemkey => itemkey,
2567: aname => 'FAILURE_FUNCTION',
2568: avalue => 'SKIP_APPROVAL');
2569: IF l_skip_approval = 'Y'
2588: END IF;
2589:
2590: IF (l_amount_requested IS NOT NULL) THEN
2591: l_amount_requested := unformat_amount(l_amount_requested); -- Added by rravikir - Bug 10083461
2592: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
2593: itemkey => itemkey,
2594: aname => 'REQUESTED_CREDIT_LIMIT',
2595: avalue => l_amount_requested );
2596: END IF;
2614: -- recommendation route can be reached either by automation or manual.
2615: -- In case we reach recommendation via manual analysis then we will implement
2616: -- whatever user defined in case folder.
2617:
2618: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2619: itemkey => itemkey,
2620: aname => 'MANUAL_ANALYSIS_FLAG',
2621: avalue => 'Y');
2622: END IF;
2893: resultout := 'COMPLETE:SUCESS';
2894: return;
2895: END IF;
2896:
2897: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
2898: itemkey => itemkey,
2899: aname => 'FAILURE_FUNCTION',
2900: avalue => 'GENERATE_RECOMMENDATION');
2901:
2898: itemkey => itemkey,
2899: aname => 'FAILURE_FUNCTION',
2900: avalue => 'GENERATE_RECOMMENDATION');
2901:
2902: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
2903: (itemtype => itemtype,
2904: itemkey => itemkey,
2905: aname => 'CASE_FOLDER_ID');
2906:
2903: (itemtype => itemtype,
2904: itemkey => itemkey,
2905: aname => 'CASE_FOLDER_ID');
2906:
2907: l_score := WF_ENGINE.GetItemAttrNumber
2908: (itemtype => itemtype,
2909: itemkey => itemkey,
2910: aname => 'SCORE');
2911:
2908: (itemtype => itemtype,
2909: itemkey => itemkey,
2910: aname => 'SCORE');
2911:
2912: l_auto_rules_id := WF_ENGINE.GetItemAttrNumber
2913: (itemtype => itemtype,
2914: itemkey => itemkey,
2915: aname => 'AUTO_RULES_ID');
2916:
2948:
2949: END IF;
2950: EXCEPTION
2951: WHEN insert_failure THEN
2952: wf_core.context('AR_CMGT_WF_ENGINE','GENERATE_RECOMMENDATION',itemtype,
2953: itemkey,
2954: 'Error while inserting into ar_cmgt_cf_recommends',
2955: sqlerrm);
2956: raise;
2980: THEN
2981: -- dbms_session.set_sql_trace(true);
2982:
2983:
2984: l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
2985: itemkey => itemkey,
2986: aname => 'CASE_FOLDER_ID');
2987:
2988:
2996: ame_api.getadminapprover(adminapproverout => l_admin_approver_rec);
2997: IF l_approver_rec_out.person_id = l_admin_approver_rec.person_id
2998: THEN
2999:
3000: wf_core.context('AR_CMGT_WF_ENGINE','APPROVAL_PROCESS',itemtype,
3001: itemkey, 'Approver is Admin User', null);
3002: --raise;
3003: END IF;
3004:
3012: l_approver_display_name);
3013:
3014: IF wf_directory.UserActive(l_approver_user_name)
3015: THEN
3016: WF_ENGINE.setItemAttrNumber(itemType => itemtype,
3017: itemKey => itemkey,
3018: aname => 'APPROVER_ID',
3019: avalue => l_approver_id);
3020:
3017: itemKey => itemkey,
3018: aname => 'APPROVER_ID',
3019: avalue => l_approver_id);
3020:
3021: WF_ENGINE.setItemAttrText(itemType => itemtype,
3022: itemKey => itemkey,
3023: aname => 'APPROVER_USER_NAME',
3024: avalue => l_approver_user_name);
3025: WF_ENGINE.setItemAttrText(itemType => itemtype,
3021: WF_ENGINE.setItemAttrText(itemType => itemtype,
3022: itemKey => itemkey,
3023: aname => 'APPROVER_USER_NAME',
3024: avalue => l_approver_user_name);
3025: WF_ENGINE.setItemAttrText(itemType => itemtype,
3026: itemKey => itemkey,
3027: aname => 'APPROVER_DISPLAY_NAME',
3028: avalue => l_approver_display_name);
3029: resultout := 'COMPLETE:EXIST';
3069: BEGIN
3070: IF funcmode = 'RUN'
3071: THEN
3072:
3073: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3074: itemKey => itemkey,
3075: aname => 'CREDIT_REQUEST_ID',
3076: avalue => itemkey);
3077: l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
3073: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3074: itemKey => itemkey,
3075: aname => 'CREDIT_REQUEST_ID',
3076: avalue => itemkey);
3077: l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
3078: itemkey => itemkey,
3079: aname => 'CASE_FOLDER_ID');
3080: l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
3081: itemkey => itemkey,
3076: avalue => itemkey);
3077: l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
3078: itemkey => itemkey,
3079: aname => 'CASE_FOLDER_ID');
3080: l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
3081: itemkey => itemkey,
3082: aname => 'APPROVER_ID');
3083:
3084: ame_api.updateApprovalStatus2(applicationIdIn => 222,
3108: END IF;
3109:
3110: IF (l_amount_requested IS NOT NULL) THEN
3111: l_amount_requested := unformat_amount(l_amount_requested); -- Added by rravikir - Bug 10083461
3112: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
3113: itemkey => itemkey,
3114: aname => 'REQUESTED_CREDIT_LIMIT',
3115: avalue => l_amount_requested );
3116: END IF;
3129: l_approver_id number;
3130: BEGIN
3131: IF funcmode = 'RUN'
3132: THEN
3133: l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
3134: itemkey => itemkey,
3135: aname => 'CASE_FOLDER_ID');
3136: l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
3137: itemkey => itemkey,
3132: THEN
3133: l_case_folder_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
3134: itemkey => itemkey,
3135: aname => 'CASE_FOLDER_ID');
3136: l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
3137: itemkey => itemkey,
3138: aname => 'APPROVER_ID');
3139: ame_api.updateApprovalStatus2(applicationIdIn => 222,
3140: transactionIdIn => l_case_folder_id,
3199: l_amount ar_cmgt_cf_recommends.RECOMMENDATION_VALUE2%TYPE;
3200: BEGIN
3201: IF pg_wf_debug = 'Y'
3202: THEN
3203: debug('ar_cmgt_wf_engine.implement_recommendation()+');
3204: END IF;
3205:
3206: IF funcmode = 'RUN'
3207: THEN
3214: WHERE credit_request_id = itemkey;
3215:
3216: END;
3217:
3218: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
3219: (itemtype => itemtype,
3220: itemkey => itemkey,
3221: aname => 'CASE_FOLDER_ID');
3222:
3294: x_msg_data => l_error_msg);
3295:
3296: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3297: THEN
3298: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_RECOMMENDATION',itemtype,
3299: itemkey,
3300: 'Error while applying Customer Hold for Party : '||l_party_id||' Cust Account Id '||
3301: l_cust_account_id||' Site Use Id '|| l_site_use_id ||' '|| l_error_msg,
3302: sqlerrm);
3329: x_msg_data => l_error_msg);
3330:
3331: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3332: THEN
3333: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_RECOMMENDATION',itemtype,
3334: itemkey,
3335: 'Error while applying Removing Hold for Party : '||l_party_id||' Cust Account Id '||
3336: l_cust_account_id||' Site Use Id '|| l_site_use_id ||' '|| l_error_msg,
3337: sqlerrm);
3355:
3356: EXCEPTION
3357: WHEN NO_DATA_FOUND
3358: THEN
3359: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
3360: itemkey,'No data found in recommendations for Appeal',
3361: sqlerrm);
3362: raise;
3363:
3362: raise;
3363:
3364: WHEN OTHERS
3365: THEN
3366: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
3367: itemkey,'Error accessing system options',
3368: sqlerrm);
3369: raise;
3370:
3452: END IF;
3453:
3454: IF pg_wf_debug = 'Y'
3455: THEN
3456: debug('ar_cmgt_wf_engine.implement_recommendation()-');
3457: END IF;
3458:
3459: EXCEPTION
3460: WHEN HOLD_ERROR THEN
3520: END IF;
3521:
3522: IF (l_amount_requested IS NOT NULL) THEN
3523: l_amount_requested := unformat_amount(l_amount_requested); -- Added by rravikir - Bug 10083461
3524: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
3525: itemkey => itemkey,
3526: aname => 'REQUESTED_CREDIT_LIMIT',
3527: avalue => l_amount_requested );
3528: END IF;
3535: FROM ar_cmgt_credit_requests
3536: WHERE credit_request_id = itemkey;
3537:
3538: END;
3539: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
3540: (itemtype => itemtype,
3541: itemkey => itemkey,
3542: aname => 'CASE_FOLDER_ID');
3543:
3540: (itemtype => itemtype,
3541: itemkey => itemkey,
3542: aname => 'CASE_FOLDER_ID');
3543:
3544: l_limit_currency := WF_ENGINE.getItemAttrText
3545: (itemtype => itemtype,
3546: itemkey => itemkey,
3547: aname => 'LIMIT_CURRENCY');
3548: /* Update the risk factor data point value */
3595: AND a.parent_credit_request_id = b.credit_request_id
3596: AND b.status <> 'PROCESSED';
3597:
3598: -- rows exist, so start workflow
3599: ar_cmgt_wf_engine.start_workflow (
3600: p_credit_request_id => l_parent_credit_request_id,
3601: p_application_status => 'SUBMIT' );
3602:
3603: EXCEPTION
3603: EXCEPTION
3604: WHEN NO_DATA_FOUND THEN
3605: NULL;
3606: WHEN OTHERS THEN
3607: wf_core.context('AR_CMGT_WF_ENGINE','POST_IMPLEMENT_PROCESS',itemtype,
3608: itemkey,
3609: 'Error while Getting Parent Credit Request Id',
3610: sqlerrm);
3611: raise;
3640:
3641: raise_recco_event(l_case_folder_id);
3642: EXCEPTION
3643: WHEN OTHERS THEN
3644: wf_core.context('AR_CMGT_WF_ENGINE','IMPLEMENT_CUSTOM_RECO',itemtype,
3645: itemkey,'Error while raise Business Event',
3646: sqlerrm);
3647: raise;
3648:
3659: resultout out NOCOPY varchar2) IS
3660:
3661: l_failure_function VARCHAR2(60);
3662: BEGIN
3663: l_failure_function := WF_ENGINE.getItemAttrText(
3664: itemtype => itemtype,
3665: itemkey => itemkey,
3666: aname => 'FAILURE_FUNCTION');
3667:
3666: aname => 'FAILURE_FUNCTION');
3667:
3668: IF l_failure_function IS NULL OR l_failure_function <> 'SKIP_APPROVAL'
3669: THEN
3670: WF_ENGINE.setItemAttrText(
3671: itemtype => itemtype,
3672: itemkey => itemkey,
3673: aname => 'SKIP_APPROVAL',
3674: avalue => 'N');
3683: resultout out NOCOPY varchar2) IS
3684:
3685: l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
3686: BEGIN
3687: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
3688: (itemtype => itemtype,
3689: itemkey => itemkey,
3690: aname => 'CASE_FOLDER_ID');
3691:
3771:
3772: BEGIN
3773: IF p_attribute_type = 'NUMBER'
3774: THEN
3775: WF_ENGINE.setItemAttrNumber(itemType => 'ARCMGTAP',
3776: itemKey => p_itemkey,
3777: aname => p_attribute_name,
3778: avalue => p_attribute_value);
3779: ELSIF p_attribute_type = 'TEXT'
3777: aname => p_attribute_name,
3778: avalue => p_attribute_value);
3779: ELSIF p_attribute_type = 'TEXT'
3780: THEN
3781: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3782: itemKey => p_itemkey,
3783: aname => p_attribute_name,
3784: avalue => p_attribute_value);
3785: END IF;
3789: l_resource_id => to_number(p_attribute_value));
3790:
3791: -- now get credit_ananlyst details
3792: get_employee_details(l_person_id,l_user_name, l_display_name);
3793: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3794: itemKey => p_itemkey,
3795: aname => 'CREDIT_ANALYST_USER_NAME',
3796: avalue => l_user_name);
3797: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3793: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3794: itemKey => p_itemkey,
3795: aname => 'CREDIT_ANALYST_USER_NAME',
3796: avalue => l_user_name);
3797: WF_ENGINE.setItemAttrText(itemType => 'ARCMGTAP',
3798: itemKey => p_itemkey,
3799: aname => 'CREDIT_ANALYST_DISPLAY_NAME',
3800: avalue => l_display_name);
3801:
3852: WHEN TOO_MANY_ROWS THEN
3853: -- mean no credit request exists
3854: resultout := 'COMPLETE:N';
3855: WHEN OTHERS THEN
3856: wf_core.context('AR_CMGT_WF_ENGINE','CHECK_CHILD_REQ_COMPLETED',itemtype,
3857: itemkey,'Error while Checking Child Credit Requests',
3858: sqlerrm);
3859: raise;
3860: END;
3884: THEN
3885:
3886: -- first validate for guarantors credit request
3887: -- there won't be any recommendations
3888: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3889: itemkey => itemkey,
3890: aname => 'FAILURE_FUNCTION',
3891: avalue => 'VALIDATE_RECO');
3892:
3889: itemkey => itemkey,
3890: aname => 'FAILURE_FUNCTION',
3891: avalue => 'VALIDATE_RECO');
3892:
3893: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
3894: (itemtype => itemtype,
3895: itemkey => itemkey,
3896: aname => 'CASE_FOLDER_ID');
3897: l_credit_request_type := WF_ENGINE.GetItemAttrText
3893: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
3894: (itemtype => itemtype,
3895: itemkey => itemkey,
3896: aname => 'CASE_FOLDER_ID');
3897: l_credit_request_type := WF_ENGINE.GetItemAttrText
3898: (itemtype => itemtype,
3899: itemkey => itemkey,
3900: aname => 'CREDIT_REQUEST_TYPE');
3901: --fetch the cursors
3921: IF l_reco_exist = 'N' and nvl(l_credit_request_type,'CREDIT_APP') <> 'GUARANTOR'
3922: THEN
3923: --call the program for populating the recommendations
3924: --before validating.
3925: AR_CMGT_WF_ENGINE.GET_EXT_SCORE_RECOMMENDATIONS(
3926: itemtype => itemtype,
3927: itemkey => itemkey,
3928: p_cf_id => l_case_folder_id,
3929: resultout=> resultout);
3955: WHEN TOO_MANY_ROWS THEN
3956: resultout := 'COMPLETE:FAILURE';
3957: return;
3958: WHEN OTHERS THEN
3959: wf_core.context('AR_CMGT_WF_ENGINE','VALIDATE_RECOMMENDATIONS',itemtype,
3960: itemkey,'Error while validating Recommendations for Party',
3961: sqlerrm);
3962: raise;
3963:
3981: END IF;
3982: END IF;
3983: EXCEPTION
3984: WHEN UNEXP_ERROR THEN
3985: wf_core.context('AR_CMGT_WF_ENGINE','VALIDATE_RECOMMENDATIONS',itemtype,
3986: itemkey,'Error while populating recommendation ',
3987: sqlerrm);
3988: raise;
3989: END;
3998: BEGIN
3999: IF funcmode = 'RUN'
4000: THEN
4001:
4002: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4003: itemkey => itemkey,
4004: aname => 'MANUAL_ANALYSIS_FLAG',
4005: avalue => 'H');
4006: END IF;
4042: BEGIN
4043: IF funcmode = 'RUN'
4044: THEN
4045:
4046: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4047: itemkey => itemkey,
4048: aname => 'FAILURE_FUNCTION',
4049: avalue => 'DUPLICATE_CASE_FOLDER');
4050:
4060: FROM ar_cmgt_credit_requests
4061: WHERE credit_request_id = itemkey;
4062: EXCEPTION
4063: WHEN OTHERS THEN
4064: wf_core.context ('AR_CMGT_WF_ENGINE','APPEAL_RESUB_DECISION',itemtype,itemkey,
4065: 'Error while getting records from AR_CMGT_CREDIT_REQUESTS',
4066: 'Sql Error: '||sqlerrm);
4067: raise;
4068: END;
4094: WHERE CREDIT_REQUEST_ID = itemkey;
4095:
4096: EXCEPTION
4097: WHEN OTHERS THEN
4098: wf_core.context ('AR_CMGT_WF_ENGINE','APPEAL_RESUB_DECISION',itemtype,itemkey,
4099: 'Error while getting parent records from AR_CMGT_CREDIT_REQUESTS',
4100: 'Sql Error: '||sqlerrm);
4101: raise;
4102: END;
4112: and type = 'CASE';
4113:
4114: EXCEPTION
4115: WHEN OTHERS THEN
4116: wf_core.context ('AR_CMGT_WF_ENGINE','APPEAL_RESUB_DECISION',itemtype,itemkey,
4117: 'Error while getting parent records from AR_CMGT_CASE_FOLDERS',
4118: 'Sql Error: '||sqlerrm);
4119: raise;
4120: END;
4180: raise l_dnb_data;
4181: END IF;
4182:
4183: --call the routine to populate all the wf_attributes
4184: AR_CMGT_WF_ENGINE.POPULATE_WF_ATTRIBUTES(
4185: itemtype => itemtype,
4186: itemkey => itemkey,
4187: actid => actid,
4188: funcmode => funcmode,
4431: hz_parties party
4432: WHERE credit_request_id = itemkey
4433: AND req.party_id = party.party_id;
4434: WHEN OTHERS THEN
4435: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTES',itemtype,itemkey,
4436: sqlerrm);
4437: raise;
4438: END;
4439:
4436: sqlerrm);
4437: raise;
4438: END;
4439:
4440: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
4441: itemkey => itemkey,
4442: aname => 'PARTY_ID',
4443: avalue => l_party_id );
4444:
4441: itemkey => itemkey,
4442: aname => 'PARTY_ID',
4443: avalue => l_party_id );
4444:
4445: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4446: itemkey => itemkey,
4447: aname => 'CREDIT_CLASSIFICATION',
4448: avalue => l_credit_classification );
4449: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4445: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4446: itemkey => itemkey,
4447: aname => 'CREDIT_CLASSIFICATION',
4448: avalue => l_credit_classification );
4449: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4450: itemkey => itemkey,
4451: aname => 'REVIEW_TYPE',
4452: avalue => l_review_type );
4453: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4449: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4450: itemkey => itemkey,
4451: aname => 'REVIEW_TYPE',
4452: avalue => l_review_type );
4453: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4454: itemkey => itemkey,
4455: aname => 'CURRENCY',
4456: avalue => l_currency );
4457: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4453: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4454: itemkey => itemkey,
4455: aname => 'CURRENCY',
4456: avalue => l_currency );
4457: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4458: itemkey => itemkey,
4459: aname => 'SOURCE_NAME',
4460: avalue => l_source_name );
4461: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4457: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4458: itemkey => itemkey,
4459: aname => 'SOURCE_NAME',
4460: avalue => l_source_name );
4461: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4462: itemkey => itemkey,
4463: aname => 'APPLICATION_NUMBER',
4464: avalue => l_application_number );
4465: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
4461: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4462: itemkey => itemkey,
4463: aname => 'APPLICATION_NUMBER',
4464: avalue => l_application_number );
4465: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
4466: itemkey => itemkey,
4467: aname => 'REQUESTED_CREDIT_LIMIT',
4468: avalue => l_amount_requested );
4469: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
4465: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
4466: itemkey => itemkey,
4467: aname => 'REQUESTED_CREDIT_LIMIT',
4468: avalue => l_amount_requested );
4469: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
4470: itemkey => itemkey,
4471: aname => 'REQUESTOR_PERSON_ID',
4472: avalue => l_requestor_id );
4473: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4469: WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
4470: itemkey => itemkey,
4471: aname => 'REQUESTOR_PERSON_ID',
4472: avalue => l_requestor_id );
4473: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4474: itemkey => itemkey,
4475: aname => 'SOURCE_COL1',
4476: avalue => l_source_column1 );
4477: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4473: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4474: itemkey => itemkey,
4475: aname => 'SOURCE_COL1',
4476: avalue => l_source_column1 );
4477: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4478: itemkey => itemkey,
4479: aname => 'SOURCE_COL2',
4480: avalue => l_source_column2 );
4481: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4477: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4478: itemkey => itemkey,
4479: aname => 'SOURCE_COL2',
4480: avalue => l_source_column2 );
4481: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4482: itemkey => itemkey,
4483: aname => 'SOURCE_COL3',
4484: avalue => l_source_column3 );
4485: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4481: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4482: itemkey => itemkey,
4483: aname => 'SOURCE_COL3',
4484: avalue => l_source_column3 );
4485: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4486: itemkey => itemkey,
4487: aname => 'PARTY_NAME',
4488: avalue => l_party_name );
4489: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4485: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4486: itemkey => itemkey,
4487: aname => 'PARTY_NAME',
4488: avalue => l_party_name );
4489: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4490: itemkey => itemkey,
4491: aname => 'PARTY_NUMBER',
4492: avalue => l_party_number );
4493: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4489: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4490: itemkey => itemkey,
4491: aname => 'PARTY_NUMBER',
4492: avalue => l_party_number );
4493: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4494: itemkey => itemkey,
4495: aname => 'APPL_NOTES',
4496: avalue => l_notes );
4497: WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
4493: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4494: itemkey => itemkey,
4495: aname => 'APPL_NOTES',
4496: avalue => l_notes );
4497: WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
4498: itemkey => itemkey,
4499: aname => 'APPLICATION_DATE',
4500: avalue => l_application_date );
4501:
4505: p_employee_id => l_requestor_id,
4506: p_user_name => l_requestor_user_name,
4507: p_display_name => l_requestor_display_name);
4508:
4509: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4510: itemkey => itemkey,
4511: aname => 'REQUESTOR_USER_NAME',
4512: avalue => l_requestor_user_name );
4513: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4509: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4510: itemkey => itemkey,
4511: aname => 'REQUESTOR_USER_NAME',
4512: avalue => l_requestor_user_name );
4513: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4514: itemkey => itemkey,
4515: aname => 'REQUESTOR_DISPLAY_NAME',
4516: avalue => l_requestor_display_name );
4517: ELSE
4521: INTO l_requestor_user_name
4522: FROM fnd_user
4523: WHERE user_id = l_requestor_id;
4524:
4525: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4526: itemkey => itemkey,
4527: aname => 'REQUESTOR_USER_NAME',
4528: avalue => l_requestor_user_name );
4529:
4526: itemkey => itemkey,
4527: aname => 'REQUESTOR_USER_NAME',
4528: avalue => l_requestor_user_name );
4529:
4530: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4531: itemkey => itemkey,
4532: aname => 'REQUESTOR_DISPLAY_NAME',
4533: avalue => l_requestor_user_name );
4534: EXCEPTION
4532: aname => 'REQUESTOR_DISPLAY_NAME',
4533: avalue => l_requestor_user_name );
4534: EXCEPTION
4535: WHEN NO_DATA_FOUND THEN
4536: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTE',itemtype,itemkey,
4537: 'FND User Not Found'|| sqlerrm);
4538: raise;
4539: WHEN OTHERS THEN
4540: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTE',itemtype,itemkey,
4536: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTE',itemtype,itemkey,
4537: 'FND User Not Found'|| sqlerrm);
4538: raise;
4539: WHEN OTHERS THEN
4540: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTE',itemtype,itemkey,
4541: 'Other Error '|| sqlerrm);
4542: raise;
4543: END;
4544: END IF;
4553: EXCEPTION
4554: WHEN NO_DATA_FOUND THEN
4555: l_account_number := null;
4556: WHEN OTHERS THEN
4557: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTES',itemtype,itemkey,
4558: 'Getting Account Details SqlError: '|| sqlerrm);
4559: raise;
4560:
4561: END;
4558: 'Getting Account Details SqlError: '|| sqlerrm);
4559: raise;
4560:
4561: END;
4562: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4563: itemkey => itemkey,
4564: aname => 'ACCOUNT_NUMBER',
4565: avalue => l_account_number );
4566: END IF;
4577: FROM ar_cmgt_case_folders
4578: WHERE credit_request_id = itemkey
4579: AND type = 'CASE';
4580:
4581: WF_ENGINE.setItemAttrNumber
4582: (itemtype => itemtype,
4583: itemkey => itemkey,
4584: aname => 'CASE_FOLDER_ID',
4585: avalue => l_case_folder_id);
4583: itemkey => itemkey,
4584: aname => 'CASE_FOLDER_ID',
4585: avalue => l_case_folder_id);
4586:
4587: WF_ENGINE.setItemAttrText
4588: (itemtype => itemtype,
4589: itemkey => itemkey,
4590: aname => 'LIMIT_CURRENCY',
4591: avalue => l_limit_currency);
4590: aname => 'LIMIT_CURRENCY',
4591: avalue => l_limit_currency);
4592:
4593:
4594: WF_ENGINE.setItemAttrText
4595: (itemtype => itemtype,
4596: itemkey => itemkey,
4597: aname => 'CASE_FOLDER_NUMBER',
4598: avalue => l_case_folder_number);
4597: aname => 'CASE_FOLDER_NUMBER',
4598: avalue => l_case_folder_number);
4599:
4600:
4601: WF_ENGINE.setItemAttrDate
4602: (itemtype => itemtype,
4603: itemkey => itemkey,
4604: aname => 'CASE_FOLDER_DATE',
4605: avalue => l_creation_date_time);
4603: itemkey => itemkey,
4604: aname => 'CASE_FOLDER_DATE',
4605: avalue => l_creation_date_time);
4606:
4607: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
4608: itemkey => itemkey,
4609: aname => 'CHECK_LIST_ID',
4610: avalue => l_check_list_id);
4611:
4608: itemkey => itemkey,
4609: aname => 'CHECK_LIST_ID',
4610: avalue => l_check_list_id);
4611:
4612: WF_ENGINE.setItemAttrNumber(itemtype => itemtype,
4613: itemkey => itemkey,
4614: aname => 'SCORE_MODEL_ID',
4615: avalue => l_score_model_id);
4616: EXCEPTION
4614: aname => 'SCORE_MODEL_ID',
4615: avalue => l_score_model_id);
4616: EXCEPTION
4617: WHEN OTHERS THEN
4618: wf_core.context ('AR_CMGT_WF_ENGINE','POPULATE_WF_ATTRIBUTES',itemtype,itemkey,
4619: 'Error while getting Case Folder Details, SqlError: '|| sqlerrm);
4620: raise;
4621: END;
4622: END IF;
4667:
4668: --initialize raise BE flag to 'N'
4669: l_raise_event:='N';
4670:
4671: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4672: itemkey => itemkey,
4673: aname => 'FAILURE_FUNCTION',
4674: avalue => 'CHECK_EXTRNAL_DATA_POINTS');
4675: l_score_model_id := WF_ENGINE.GetItemAttrNumber
4671: WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4672: itemkey => itemkey,
4673: aname => 'FAILURE_FUNCTION',
4674: avalue => 'CHECK_EXTRNAL_DATA_POINTS');
4675: l_score_model_id := WF_ENGINE.GetItemAttrNumber
4676: (itemtype => itemtype,
4677: itemkey => itemkey,
4678: aname => 'SCORE_MODEL_ID');
4679: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
4675: l_score_model_id := WF_ENGINE.GetItemAttrNumber
4676: (itemtype => itemtype,
4677: itemkey => itemkey,
4678: aname => 'SCORE_MODEL_ID');
4679: l_case_folder_id := WF_ENGINE.GetItemAttrNumber
4680: (itemtype => itemtype,
4681: itemkey => itemkey,
4682: aname => 'CASE_FOLDER_ID');
4683: IF pg_wf_debug = 'Y'
4727: END IF;
4728: END IF;
4729: EXCEPTION
4730: WHEN UNEXP_ERROR THEN
4731: wf_core.context ('AR_CMGT_WF_ENGINE','CHECK_EXTRNAL_DATA_POINTS',itemtype,itemkey,
4732: 'Error while submitting Con. Request Id, SqlError: '|| sqlerrm);
4733: raise;
4734: END;
4735: PROCEDURE GET_EXT_SCORE_RECOMMENDATIONS(
4868: raise;
4869: END;
4870:
4871:
4872: END AR_CMGT_WF_ENGINE;