[Home] [Help]
1: PACKAGE BODY LNS_PARTICIPANTS_PUB AS
2: /* $Header: LNS_PART_PUBP_B.pls 120.19.12010000.1 2008/07/29 09:12:19 appldev ship $ */
3: G_DEBUG_COUNT CONSTANT NUMBER := 0;
4: G_DEBUG CONSTANT BOOLEAN := FALSE;
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_PARTICIPANTS_PUB';
1: PACKAGE BODY LNS_PARTICIPANTS_PUB AS
2: /* $Header: LNS_PART_PUBP_B.pls 120.19.12010000.1 2008/07/29 09:12:19 appldev ship $ */
3: G_DEBUG_COUNT CONSTANT NUMBER := 0;
4: G_DEBUG CONSTANT BOOLEAN := FALSE;
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_PARTICIPANTS_PUB';
6:
7: --------------------------------------------------
8: -- Procedure for logging debug messages
9: --------------------------------------------------
296: -- initialize API return status to success.
297: x_return_status := FND_API.G_RET_STS_SUCCESS;
298:
299:
300: lns_participants_pkg.INSERT_ROW(
301: x_PARTICIPANT_ID => p_loan_participant_rec.PARTICIPANT_ID
302: ,P_LOAN_ID => p_loan_participant_rec.LOAN_ID
303: ,P_HZ_PARTY_ID => p_loan_participant_rec.HZ_PARTY_ID
304: ,P_LOAN_PARTICIPANT_TYPE=> p_loan_participant_rec.LOAN_PARTICIPANT_TYPE
338: --dbms_output.put_line('after tblH');
339: x_participant_id := p_loan_participant_rec.PARTICIPANT_ID;
340:
341: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
342: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_participant procedure: After call to lns_participants_pkg.Insert_Row');
343: END IF;
344: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
345:
346: END do_create_participant;
374: l_loan_status varchar2(30);
375: l_loan_sec_status varchar2(30);
376:
377: CURSOR CheckDupParty(p_loan_id number, p_party_id number) IS
378: select count(1) from lns_participants
379: where loan_id = p_loan_id and
380: hz_party_id = p_party_id;
381:
382: CURSOR c_get_credit_info(p_loan_id number) IS
403: if (l_init_msg_list is null) then
404: l_init_msg_list := FND_API.G_FALSE;
405: end if;
406:
407: l_last_api_called := 'LNS_PARTICIPANTS_PUB.createParticipant';
408: l_participant_id := p_loan_participant_rec.participant_id;
409: l_loan_id := p_loan_participant_rec.loan_id;
410: l_hz_party_id := p_loan_participant_rec.hz_party_id;
411: l_loan_participant_rec := p_loan_participant_rec;
448:
449: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
450: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In createParticipant: Before call to validateParticipant');
451: END IF;
452: l_last_api_called := 'LNS_PARTICIPANTS_PUB.validateParticipant';
453: validateParticipant(p_loan_participant_rec => p_loan_participant_rec,
454: p_mode => 'CREATE',
455: x_return_status => l_return_status,
456: x_msg_count => l_msg_count,
476: END IF;
477:
478: if (l_loan_credit_review_flag = 'Y' and l_loan_status = 'INCOMPLETE' and (l_loan_sec_status is null OR l_loan_sec_status NOT IN ('IN_CREDIT_REVIEW','CREDIT_REVIEW_COMPLETE'))) then
479: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling createCreditRequest');
480: l_last_api_called := 'LNS_PARTICIPANTS_PUB.createCreditRequest';
481: createCreditRequest(p_loan_participant_rec => l_loan_participant_rec,
482: x_return_status => l_return_status,
483: x_msg_count => l_msg_count,
484: x_msg_data => l_msg_data);
496: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_create_participant procedure');
497: END IF;
498:
499: -- call to business logic.
500: l_last_api_called := 'LNS_PARTICIPANTS_PUB.do_create_participant';
501: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling do_create_participant');
502: do_create_participant(
503: l_loan_participant_rec,
504: x_participant_id,
567: if (l_init_msg_list is null) then
568: l_init_msg_list := FND_API.G_FALSE;
569: end if;
570:
571: l_last_api_called := 'LNS_PARTICIPANTS_PUB.updateParticipant';
572: l_loan_participant_rec := p_loan_participant_rec;
573: savepoint updateParticipant;
574:
575: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
596: SELECT OBJECT_VERSION_NUMBER,
597: ROWID
598: INTO l_object_version,
599: l_rowid
600: FROM LNS_PARTICIPANTS
601: WHERE PARTICIPANT_ID = p_loan_participant_rec.participant_id
602: FOR UPDATE OF PARTICIPANT_ID NOWAIT;
603:
604: IF NOT
611: )
612: )
613: THEN
614: FND_MESSAGE.SET_NAME('LNS', 'LNS_API_RECORD_CHANGED');
615: FND_MESSAGE.SET_TOKEN('TABLE', 'lns_participants');
616: FND_MSG_PUB.ADD;
617: RAISE FND_API.G_EXC_ERROR;
618: END IF;
619:
644:
645: end if; -- end if (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
646:
647:
648: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'Before calling LNS_PARTICIPANTS_PKG.Update_Row');
649: l_last_api_called := 'LNS_PARTICIPANTS_PKG.Update_Row';
650: LNS_PARTICIPANTS_PKG.Update_Row(x_rowid => l_rowid
651: ,p_PARTICIPANT_ID => l_loan_participant_rec.PARTICIPANT_ID
652: ,p_LOAN_ID => l_loan_participant_rec.LOAN_ID
645: end if; -- end if (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
646:
647:
648: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'Before calling LNS_PARTICIPANTS_PKG.Update_Row');
649: l_last_api_called := 'LNS_PARTICIPANTS_PKG.Update_Row';
650: LNS_PARTICIPANTS_PKG.Update_Row(x_rowid => l_rowid
651: ,p_PARTICIPANT_ID => l_loan_participant_rec.PARTICIPANT_ID
652: ,p_LOAN_ID => l_loan_participant_rec.LOAN_ID
653: ,p_HZ_PARTY_ID => l_loan_participant_rec.HZ_PARTY_ID
646:
647:
648: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'Before calling LNS_PARTICIPANTS_PKG.Update_Row');
649: l_last_api_called := 'LNS_PARTICIPANTS_PKG.Update_Row';
650: LNS_PARTICIPANTS_PKG.Update_Row(x_rowid => l_rowid
651: ,p_PARTICIPANT_ID => l_loan_participant_rec.PARTICIPANT_ID
652: ,p_LOAN_ID => l_loan_participant_rec.LOAN_ID
653: ,p_HZ_PARTY_ID => l_loan_participant_rec.HZ_PARTY_ID
654: ,p_LOAN_PARTICIPANT_TYPE => l_loan_participant_rec.LOAN_PARTICIPANT_TYPE
684: ,p_CASE_FOLDER_ID => l_loan_participant_rec.CASE_FOLDER_ID
685: ,p_REVIEW_TYPE => l_loan_participant_rec.REVIEW_TYPE
686: ,p_CREDIT_CLASSIFICATION => l_loan_participant_rec.CREDIT_CLASSIFICATION
687: );
688: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'After calling LNS_PARTICIPANTS_PKG.Update_Row');
689:
690: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling denormalizePrimaryInfo');
691: denormalizePrimaryInfo(p_loan_participant_rec => l_loan_participant_rec,
692: x_return_status => l_return_status,
750: CURSOR c_get_primary_borrower(pLoanId Number) IS
751: select
752: participant_id, loan_id, LOAN_PARTICIPANT_TYPE, hz_party_id, credit_request_id,
753: case_folder_id, object_version_number,review_type,credit_classification
754: from lns_participants
755: where loan_id = pLoanId
756: and LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER'
757: and end_date_active is null;
758:
764:
765: BEGIN
766: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
767:
768: l_last_api_called := 'LNS_PARTICIPANTS_PUB.createAppealCreditRequest';
769: -- Standard Start of API savepoint
770: SAVEPOINT createAppealCreditRequest;
771: x_return_status := FND_API.G_RET_STS_SUCCESS;
772: l_loan_id := p_loan_id;
784: return;
785: end if;
786:
787: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling ');
788: l_last_api_called := 'LNS_PARTICIPANTS_PUB.createCreditRequest';
789: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling '|| l_last_api_called);
790: createCreditRequest(p_loan_participant_rec => l_loan_participant_rec,
791: x_return_status => l_return_status,
792: x_msg_count => l_msg_count,
802: --new credit request has not been submitted!
803: return;
804: end if;
805:
806: l_last_api_called := 'LNS_PARTICIPANTS_PUB.updateParticipant';
807: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling '|| l_last_api_called);
808: l_object_version := l_loan_participant_rec.object_version_number;
809: updateParticipant(p_init_msg_list => 'T',
810: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
913: BEGIN
914:
915: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
916:
917: l_last_api_called := 'LNS_PARTICIPANTS_PUB.createCreditRequest';
918: l_participant_type := p_loan_participant_rec.LOAN_PARTICIPANT_TYPE;
919: l_loan_id := p_loan_participant_rec.loan_id;
920: l_credit_request_id := null;
921: l_review_type := p_loan_participant_rec.REVIEW_TYPE;
1019: if (p_loan_participant_rec.credit_request_id is null) then
1020:
1021: --update with parent credit request id
1022: select credit_request_id into l_parent_credit_request_id
1023: from lns_participants
1024: where loan_id = l_loan_id
1025: and loan_participant_type = 'PRIMARY_BORROWER'
1026: and end_date_active is null;
1027:
1035: if (p_loan_participant_rec.credit_request_id is null) then
1036:
1037: --need to pass parent credit request id
1038: select credit_request_id into l_parent_credit_request_id
1039: from lns_participants
1040: where loan_id = l_loan_id
1041: and loan_participant_type = 'PRIMARY_BORROWER'
1042: and end_date_active is null;
1043:
1150: END createCreditRequest;
1151:
1152: ----------------------------------------------------------------
1153: --This procedure updates the loan header with denormalized information
1154: --from lns_participants for the primary borrower
1155: ----------------------------------------------------------------
1156: Procedure denormalizePrimaryInfo(p_loan_participant_rec IN loan_participant_rec_type,
1157: x_return_status OUT NOCOPY VARCHAR2,
1158: x_msg_count OUT NOCOPY NUMBER,
1175: WHERE LOAN_ID = pLoanId;
1176:
1177: BEGIN
1178: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1179: l_last_api_called := 'LNS_PARTICIPANTS_PUB.denormalizePrimaryInfo';
1180: l_participant_type := p_loan_participant_rec.LOAN_PARTICIPANT_TYPE;
1181: l_loan_id := p_loan_participant_rec.loan_id;
1182:
1183: -- Standard Start of API savepoint
1252:
1253: CURSOR C_GET_PRIMARY_CREQ_ID(pLoanId Number) IS
1254: SELECT CREDIT_REQUEST_ID
1255: FROM
1256: LNS_PARTICIPANTS
1257: WHERE LOAN_ID = pLoanId
1258: AND LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER'
1259: AND END_DATE_ACTIVE IS NULL;
1260:
1342: END submitCreditRequest;
1343:
1344:
1345: ----------------------------------------------------------------
1346: -- This is function updates lns_participants with the case_folder_id
1347: -- if credit management case folder has been submitted with recommendations
1348: -- for the loan application that submitted credit request
1349: -- This is called from workflow business event from credit management
1350: -- and also from the approval page UI if the loan is currently IN_CREDIT_REVIEW secondary status
1368: where loan_id = pLoanId;
1369:
1370: CURSOR check_case_folder_complete(pLoanId Number) is
1371: select count(case.case_folder_id)
1372: from ar_cmgt_case_folders case, lns_participants part
1373: where case.credit_request_id = part.credit_request_id and
1374: part.loan_id = p_loan_id and
1375: part.loan_participant_type = 'PRIMARY_BORROWER' and
1376: part.end_date_active is null and
1378: case.status = 'CLOSED';
1379:
1380: CURSOR get_case_folder_info(pLoanId Number) is
1381: SELECT case_folder.case_folder_id,case_folder.credit_classification,part.participant_id
1382: FROM ar_cmgt_case_folders case_folder,lns_participants part
1383: WHERE case_folder.credit_request_id = part.credit_request_id
1384: AND part.loan_id = pLoanId
1385: AND type = 'CASE'
1386: AND status = 'CLOSED';
1406: return l_return_flag;
1407: end if;
1408:
1409: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1410: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE: Before calling update for lns_participants to update case_folder_id');
1411: END IF;
1412:
1413: l_user_id := LNS_UTILITY_PUB.created_by;
1414: l_login_id := LNS_UTILITY_PUB.last_update_login;
1414: l_login_id := LNS_UTILITY_PUB.last_update_login;
1415: l_date := sysdate;
1416:
1417: FOR case_folder_rec IN get_case_folder_info(p_loan_id) LOOP
1418: UPDATE LNS_PARTICIPANTS
1419: SET CASE_FOLDER_ID = case_folder_rec.case_folder_id,
1420: CREDIT_CLASSIFICATION = case_folder_rec.credit_classification,
1421: LAST_UPDATED_BY = l_user_id,
1422: LAST_UPDATE_LOGIN = l_login_id,
1425: WHERE PARTICIPANT_ID = case_folder_rec.participant_id;
1426: END LOOP;
1427:
1428: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1429: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE: After calling update for lns_participants to update case_folder_id');
1430: END IF;
1431:
1432: --Call to record history
1433: LNS_LOAN_HISTORY_PUB.log_record_pre(
1436: p_table_name => 'LNS_LOAN_HEADERS_ALL'
1437: );
1438:
1439: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1440: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE: Before calling update for lns_loan_headers_all to update new secondary status');
1441: END IF;
1442:
1443: UPDATE LNS_LOAN_HEADERS_ALL
1444: SET SECONDARY_STATUS = 'CREDIT_REVIEW_COMPLETE',
1448: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
1449: WHERE LOAN_ID = p_loan_id
1450: AND SECONDARY_STATUS = 'IN_CREDIT_REVIEW'
1451: AND EXISTS (SELECT case_folder_id
1452: FROM LNS_PARTICIPANTS
1453: WHERE loan_id = LNS_LOAN_HEADERS_ALL.loan_id and hz_party_id = LNS_LOAN_HEADERS_ALL.primary_borrower_id);
1454:
1455: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1456: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE: After calling update for lns_loan_headers_all to update new secondary status');
1452: FROM LNS_PARTICIPANTS
1453: WHERE loan_id = LNS_LOAN_HEADERS_ALL.loan_id and hz_party_id = LNS_LOAN_HEADERS_ALL.primary_borrower_id);
1454:
1455: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1456: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE: After calling update for lns_loan_headers_all to update new secondary status');
1457: END IF;
1458:
1459: --Call to record history
1460: LNS_LOAN_HISTORY_PUB.log_record_post(
1481: EXCEPTION
1482: WHEN OTHERS THEN
1483:
1484: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1485: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE: Unexpected ERROR in the function call. SQLERRM is: ' || SQLERRM);
1486: END IF;
1487:
1488: return l_return_flag;
1489:
1550: MO_GLOBAL.set_policy_context('S',l_org_id);
1551:
1552: IF (l_source_name = 'LNS') THEN
1553: l_loan_id := l_source_column1;
1554: l_update_status_flag := LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE(l_loan_id);
1555:
1556: if (l_update_status_flag NOT IN ('I', 'Y')) then
1557: l_wf_return_status := 'ERROR';
1558: end if;
1564: AR_CMGT_CASE_FOLDERS
1565: WHERE CREDIT_REQUEST_ID = l_credit_request_id;
1566:
1567: IF (l_case_folder_id IS NOT NULL) THEN
1568: UPDATE LNS_PARTICIPANTS
1569: SET CASE_FOLDER_ID = l_case_folder_id
1570: WHERE CREDIT_REQUEST_ID = l_credit_request_id;
1571:
1572: l_loan_header_rec.loan_id := l_loan_id;
1669: END getDefaultPrimaryContact;
1670:
1671:
1672:
1673: END LNS_PARTICIPANTS_PUB;