[Home] [Help]
1: PACKAGE BODY LNS_PARTICIPANTS_PUB AS
2: /* $Header: LNS_PART_PUBP_B.pls 120.20 2010/08/10 11:48:59 gparuchu 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.20 2010/08/10 11:48:59 gparuchu 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
339: --dbms_output.put_line('after tblH');
340: x_participant_id := p_loan_participant_rec.PARTICIPANT_ID;
341:
342: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
343: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_participant procedure: After call to lns_participants_pkg.Insert_Row');
344: END IF;
345: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
346:
347: END do_create_participant;
375: l_loan_status varchar2(30);
376: l_loan_sec_status varchar2(30);
377:
378: CURSOR CheckDupParty(p_loan_id number, p_party_id number) IS
379: select count(1) from lns_participants
380: where loan_id = p_loan_id and
381: hz_party_id = p_party_id;
382:
383: CURSOR c_get_credit_info(p_loan_id number) IS
404: if (l_init_msg_list is null) then
405: l_init_msg_list := FND_API.G_FALSE;
406: end if;
407:
408: l_last_api_called := 'LNS_PARTICIPANTS_PUB.createParticipant';
409: l_participant_id := p_loan_participant_rec.participant_id;
410: l_loan_id := p_loan_participant_rec.loan_id;
411: l_hz_party_id := p_loan_participant_rec.hz_party_id;
412: l_loan_participant_rec := p_loan_participant_rec;
449:
450: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
451: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In createParticipant: Before call to validateParticipant');
452: END IF;
453: l_last_api_called := 'LNS_PARTICIPANTS_PUB.validateParticipant';
454: validateParticipant(p_loan_participant_rec => p_loan_participant_rec,
455: p_mode => 'CREATE',
456: x_return_status => l_return_status,
457: x_msg_count => l_msg_count,
477: END IF;
478:
479: 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
480: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling createCreditRequest');
481: l_last_api_called := 'LNS_PARTICIPANTS_PUB.createCreditRequest';
482: createCreditRequest(p_loan_participant_rec => l_loan_participant_rec,
483: x_return_status => l_return_status,
484: x_msg_count => l_msg_count,
485: x_msg_data => l_msg_data);
497: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_create_participant procedure');
498: END IF;
499:
500: -- call to business logic.
501: l_last_api_called := 'LNS_PARTICIPANTS_PUB.do_create_participant';
502: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling do_create_participant');
503: do_create_participant(
504: l_loan_participant_rec,
505: x_participant_id,
568: if (l_init_msg_list is null) then
569: l_init_msg_list := FND_API.G_FALSE;
570: end if;
571:
572: l_last_api_called := 'LNS_PARTICIPANTS_PUB.updateParticipant';
573: l_loan_participant_rec := p_loan_participant_rec;
574: savepoint updateParticipant;
575:
576: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
597: SELECT OBJECT_VERSION_NUMBER,
598: ROWID
599: INTO l_object_version,
600: l_rowid
601: FROM LNS_PARTICIPANTS
602: WHERE PARTICIPANT_ID = p_loan_participant_rec.participant_id
603: FOR UPDATE OF PARTICIPANT_ID NOWAIT;
604:
605: IF NOT
612: )
613: )
614: THEN
615: FND_MESSAGE.SET_NAME('LNS', 'LNS_API_RECORD_CHANGED');
616: FND_MESSAGE.SET_TOKEN('TABLE', 'lns_participants');
617: FND_MSG_PUB.ADD;
618: RAISE FND_API.G_EXC_ERROR;
619: END IF;
620:
645:
646: end if; -- end if (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
647:
648:
649: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'Before calling LNS_PARTICIPANTS_PKG.Update_Row');
650: l_last_api_called := 'LNS_PARTICIPANTS_PKG.Update_Row';
651: LNS_PARTICIPANTS_PKG.Update_Row(x_rowid => l_rowid
652: ,p_PARTICIPANT_ID => l_loan_participant_rec.PARTICIPANT_ID
653: ,p_LOAN_ID => l_loan_participant_rec.LOAN_ID
646: end if; -- end if (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
647:
648:
649: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'Before calling LNS_PARTICIPANTS_PKG.Update_Row');
650: l_last_api_called := 'LNS_PARTICIPANTS_PKG.Update_Row';
651: LNS_PARTICIPANTS_PKG.Update_Row(x_rowid => l_rowid
652: ,p_PARTICIPANT_ID => l_loan_participant_rec.PARTICIPANT_ID
653: ,p_LOAN_ID => l_loan_participant_rec.LOAN_ID
654: ,p_HZ_PARTY_ID => l_loan_participant_rec.HZ_PARTY_ID
647:
648:
649: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'Before calling LNS_PARTICIPANTS_PKG.Update_Row');
650: l_last_api_called := 'LNS_PARTICIPANTS_PKG.Update_Row';
651: LNS_PARTICIPANTS_PKG.Update_Row(x_rowid => l_rowid
652: ,p_PARTICIPANT_ID => l_loan_participant_rec.PARTICIPANT_ID
653: ,p_LOAN_ID => l_loan_participant_rec.LOAN_ID
654: ,p_HZ_PARTY_ID => l_loan_participant_rec.HZ_PARTY_ID
655: ,p_LOAN_PARTICIPANT_TYPE => l_loan_participant_rec.LOAN_PARTICIPANT_TYPE
686: ,p_CASE_FOLDER_ID => l_loan_participant_rec.CASE_FOLDER_ID
687: ,p_REVIEW_TYPE => l_loan_participant_rec.REVIEW_TYPE
688: ,p_CREDIT_CLASSIFICATION => l_loan_participant_rec.CREDIT_CLASSIFICATION
689: );
690: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'After calling LNS_PARTICIPANTS_PKG.Update_Row');
691:
692: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling denormalizePrimaryInfo');
693: denormalizePrimaryInfo(p_loan_participant_rec => l_loan_participant_rec,
694: x_return_status => l_return_status,
752: CURSOR c_get_primary_borrower(pLoanId Number) IS
753: select
754: participant_id, loan_id, LOAN_PARTICIPANT_TYPE, hz_party_id, credit_request_id,
755: case_folder_id, object_version_number,review_type,credit_classification
756: from lns_participants
757: where loan_id = pLoanId
758: and LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER'
759: and end_date_active is null;
760:
766:
767: BEGIN
768: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
769:
770: l_last_api_called := 'LNS_PARTICIPANTS_PUB.createAppealCreditRequest';
771: -- Standard Start of API savepoint
772: SAVEPOINT createAppealCreditRequest;
773: x_return_status := FND_API.G_RET_STS_SUCCESS;
774: l_loan_id := p_loan_id;
786: return;
787: end if;
788:
789: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling ');
790: l_last_api_called := 'LNS_PARTICIPANTS_PUB.createCreditRequest';
791: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling '|| l_last_api_called);
792: createCreditRequest(p_loan_participant_rec => l_loan_participant_rec,
793: x_return_status => l_return_status,
794: x_msg_count => l_msg_count,
804: --new credit request has not been submitted!
805: return;
806: end if;
807:
808: l_last_api_called := 'LNS_PARTICIPANTS_PUB.updateParticipant';
809: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before calling '|| l_last_api_called);
810: l_object_version := l_loan_participant_rec.object_version_number;
811: updateParticipant(p_init_msg_list => 'T',
812: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
915: BEGIN
916:
917: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
918:
919: l_last_api_called := 'LNS_PARTICIPANTS_PUB.createCreditRequest';
920: l_participant_type := p_loan_participant_rec.LOAN_PARTICIPANT_TYPE;
921: l_loan_id := p_loan_participant_rec.loan_id;
922: l_credit_request_id := null;
923: l_review_type := p_loan_participant_rec.REVIEW_TYPE;
1021: if (p_loan_participant_rec.credit_request_id is null) then
1022:
1023: --update with parent credit request id
1024: select credit_request_id into l_parent_credit_request_id
1025: from lns_participants
1026: where loan_id = l_loan_id
1027: and loan_participant_type = 'PRIMARY_BORROWER'
1028: and end_date_active is null;
1029:
1037: if (p_loan_participant_rec.credit_request_id is null) then
1038:
1039: --need to pass parent credit request id
1040: select credit_request_id into l_parent_credit_request_id
1041: from lns_participants
1042: where loan_id = l_loan_id
1043: and loan_participant_type = 'PRIMARY_BORROWER'
1044: and end_date_active is null;
1045:
1152: END createCreditRequest;
1153:
1154: ----------------------------------------------------------------
1155: --This procedure updates the loan header with denormalized information
1156: --from lns_participants for the primary borrower
1157: ----------------------------------------------------------------
1158: Procedure denormalizePrimaryInfo(p_loan_participant_rec IN loan_participant_rec_type,
1159: x_return_status OUT NOCOPY VARCHAR2,
1160: x_msg_count OUT NOCOPY NUMBER,
1177: WHERE LOAN_ID = pLoanId;
1178:
1179: BEGIN
1180: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1181: l_last_api_called := 'LNS_PARTICIPANTS_PUB.denormalizePrimaryInfo';
1182: l_participant_type := p_loan_participant_rec.LOAN_PARTICIPANT_TYPE;
1183: l_loan_id := p_loan_participant_rec.loan_id;
1184:
1185: -- Standard Start of API savepoint
1254:
1255: CURSOR C_GET_PRIMARY_CREQ_ID(pLoanId Number) IS
1256: SELECT CREDIT_REQUEST_ID
1257: FROM
1258: LNS_PARTICIPANTS
1259: WHERE LOAN_ID = pLoanId
1260: AND LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER'
1261: AND END_DATE_ACTIVE IS NULL;
1262:
1344: END submitCreditRequest;
1345:
1346:
1347: ----------------------------------------------------------------
1348: -- This is function updates lns_participants with the case_folder_id
1349: -- if credit management case folder has been submitted with recommendations
1350: -- for the loan application that submitted credit request
1351: -- This is called from workflow business event from credit management
1352: -- and also from the approval page UI if the loan is currently IN_CREDIT_REVIEW secondary status
1370: where loan_id = pLoanId;
1371:
1372: CURSOR check_case_folder_complete(pLoanId Number) is
1373: select count(case.case_folder_id)
1374: from ar_cmgt_case_folders case, lns_participants part
1375: where case.credit_request_id = part.credit_request_id and
1376: part.loan_id = p_loan_id and
1377: part.loan_participant_type = 'PRIMARY_BORROWER' and
1378: part.end_date_active is null and
1380: case.status = 'CLOSED';
1381:
1382: CURSOR get_case_folder_info(pLoanId Number) is
1383: SELECT case_folder.case_folder_id,case_folder.credit_classification,part.participant_id
1384: FROM ar_cmgt_case_folders case_folder,lns_participants part
1385: WHERE case_folder.credit_request_id = part.credit_request_id
1386: AND part.loan_id = pLoanId
1387: AND type = 'CASE'
1388: AND status = 'CLOSED';
1408: return l_return_flag;
1409: end if;
1410:
1411: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1412: 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');
1413: END IF;
1414:
1415: l_user_id := LNS_UTILITY_PUB.created_by;
1416: l_login_id := LNS_UTILITY_PUB.last_update_login;
1416: l_login_id := LNS_UTILITY_PUB.last_update_login;
1417: l_date := sysdate;
1418:
1419: FOR case_folder_rec IN get_case_folder_info(p_loan_id) LOOP
1420: UPDATE LNS_PARTICIPANTS
1421: SET CASE_FOLDER_ID = case_folder_rec.case_folder_id,
1422: CREDIT_CLASSIFICATION = case_folder_rec.credit_classification,
1423: LAST_UPDATED_BY = l_user_id,
1424: LAST_UPDATE_LOGIN = l_login_id,
1427: WHERE PARTICIPANT_ID = case_folder_rec.participant_id;
1428: END LOOP;
1429:
1430: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1431: 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');
1432: END IF;
1433:
1434: --Call to record history
1435: LNS_LOAN_HISTORY_PUB.log_record_pre(
1438: p_table_name => 'LNS_LOAN_HEADERS_ALL'
1439: );
1440:
1441: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1442: 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');
1443: END IF;
1444:
1445: UPDATE LNS_LOAN_HEADERS_ALL
1446: SET SECONDARY_STATUS = 'CREDIT_REVIEW_COMPLETE',
1450: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
1451: WHERE LOAN_ID = p_loan_id
1452: AND SECONDARY_STATUS = 'IN_CREDIT_REVIEW'
1453: AND EXISTS (SELECT case_folder_id
1454: FROM LNS_PARTICIPANTS
1455: WHERE loan_id = LNS_LOAN_HEADERS_ALL.loan_id and hz_party_id = LNS_LOAN_HEADERS_ALL.primary_borrower_id);
1456:
1457: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1458: 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');
1454: FROM LNS_PARTICIPANTS
1455: WHERE loan_id = LNS_LOAN_HEADERS_ALL.loan_id and hz_party_id = LNS_LOAN_HEADERS_ALL.primary_borrower_id);
1456:
1457: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1458: 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');
1459: END IF;
1460:
1461: --Call to record history
1462: LNS_LOAN_HISTORY_PUB.log_record_post(
1483: EXCEPTION
1484: WHEN OTHERS THEN
1485:
1486: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1487: 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);
1488: END IF;
1489:
1490: return l_return_flag;
1491:
1552: MO_GLOBAL.set_policy_context('S',l_org_id);
1553:
1554: IF (l_source_name = 'LNS') THEN
1555: l_loan_id := l_source_column1;
1556: l_update_status_flag := LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE(l_loan_id);
1557:
1558: if (l_update_status_flag NOT IN ('I', 'Y')) then
1559: l_wf_return_status := 'ERROR';
1560: end if;
1566: AR_CMGT_CASE_FOLDERS
1567: WHERE CREDIT_REQUEST_ID = l_credit_request_id;
1568:
1569: IF (l_case_folder_id IS NOT NULL) THEN
1570: UPDATE LNS_PARTICIPANTS
1571: SET CASE_FOLDER_ID = l_case_folder_id
1572: WHERE CREDIT_REQUEST_ID = l_credit_request_id;
1573:
1574: l_loan_header_rec.loan_id := l_loan_id;
1671: END getDefaultPrimaryContact;
1672:
1673:
1674:
1675: END LNS_PARTICIPANTS_PUB;