[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y' valid_acct_site_party
from
hz_parties party,
hz_party_sites site,
hz_cust_accounts acct,
hz_cust_acct_sites acct_site,
hz_cust_site_uses acc_site_use
where party.party_id = p_primary_borrower_id
and acct.party_id = party.party_id
and acct.cust_account_id = p_cust_account_id
and acct_site.cust_account_id = acct.cust_account_id
and acct_site.cust_acct_site_id = p_bill_to_acct_site_id
and acc_site_use.cust_acct_site_id = acct_site.cust_acct_site_id
and acct_site.party_site_id = site.party_site_id
and acc_site_use.site_use_code = 'BILL_TO'
and acc_site_use.status = 'A'
and site.status = 'A'
and acct.status = 'A'
and party.status = 'A';
lns_participants_pkg.INSERT_ROW(
x_PARTICIPANT_ID => p_loan_participant_rec.PARTICIPANT_ID
,P_LOAN_ID => p_loan_participant_rec.LOAN_ID
,P_HZ_PARTY_ID => p_loan_participant_rec.HZ_PARTY_ID
,P_LOAN_PARTICIPANT_TYPE=> p_loan_participant_rec.LOAN_PARTICIPANT_TYPE
,P_START_DATE_ACTIVE => p_loan_participant_rec.START_DATE_ACTIVE
,P_END_DATE_ACTIVE => p_loan_participant_rec.END_DATE_ACTIVE
,P_CUST_ACCOUNT_ID => p_loan_participant_rec.CUST_ACCOUNT_ID
,P_BILL_TO_ACCT_SITE_ID => p_loan_participant_rec.BILL_TO_ACCT_SITE_ID
,P_OBJECT_VERSION_NUMBER => p_loan_participant_rec.OBJECT_VERSION_NUMBER
,P_ATTRIBUTE_CATEGORY => p_loan_participant_rec.ATTRIBUTE_CATEGORY
,P_ATTRIBUTE1 => p_loan_participant_rec.ATTRIBUTE1
,P_ATTRIBUTE2 => p_loan_participant_rec.ATTRIBUTE2
,P_ATTRIBUTE3 => p_loan_participant_rec.ATTRIBUTE3
,P_ATTRIBUTE4 => p_loan_participant_rec.ATTRIBUTE4
,P_ATTRIBUTE5 => p_loan_participant_rec.ATTRIBUTE5
,P_ATTRIBUTE6 => p_loan_participant_rec.ATTRIBUTE6
,P_ATTRIBUTE7 => p_loan_participant_rec.ATTRIBUTE7
,P_ATTRIBUTE8 => p_loan_participant_rec.ATTRIBUTE8
,P_ATTRIBUTE9 => p_loan_participant_rec.ATTRIBUTE9
,P_ATTRIBUTE10 => p_loan_participant_rec.ATTRIBUTE10
,P_ATTRIBUTE11 => p_loan_participant_rec.ATTRIBUTE11
,P_ATTRIBUTE12 => p_loan_participant_rec.ATTRIBUTE12
,P_ATTRIBUTE13 => p_loan_participant_rec.ATTRIBUTE13
,P_ATTRIBUTE14 => p_loan_participant_rec.ATTRIBUTE14
,P_ATTRIBUTE15 => p_loan_participant_rec.ATTRIBUTE15
,P_ATTRIBUTE16 => p_loan_participant_rec.ATTRIBUTE16
,P_ATTRIBUTE17 => p_loan_participant_rec.ATTRIBUTE17
,P_ATTRIBUTE18 => p_loan_participant_rec.ATTRIBUTE18
,P_ATTRIBUTE19 => p_loan_participant_rec.ATTRIBUTE19
,P_ATTRIBUTE20 => p_loan_participant_rec.ATTRIBUTE20
,P_ACCESS_LEVEL => p_loan_participant_rec.ACCESS_LEVEL
,P_CONTACT_REL_PARTY_ID => p_loan_participant_rec.CONTACT_REL_PARTY_ID
,P_CONTACT_PERS_PARTY_ID => p_loan_participant_rec.CONTACT_PERS_PARTY_ID
,P_CREDIT_REQUEST_ID => p_loan_participant_rec.CREDIT_REQUEST_ID
,P_CASE_FOLDER_ID => p_loan_participant_rec.CASE_FOLDER_ID
,p_REVIEW_TYPE => p_loan_participant_rec.REVIEW_TYPE
,p_CREDIT_CLASSIFICATION => p_loan_participant_rec.CREDIT_CLASSIFICATION
);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_participant procedure: After call to lns_participants_pkg.Insert_Row');
select count(1) from lns_participants
where loan_id = p_loan_id and
hz_party_id = p_party_id;
select loan.party_type, loan.credit_review_flag, prod.credit_review_type,
prod.guarantor_review_type, loan.loan_status, loan.secondary_status
from lns_loan_products_all prod, lns_loan_headers_all loan
where loan.loan_id = p_loan_id
and prod.loan_product_id = loan.product_id;
select party_type
from hz_parties
where party_id = p_party_id;
PROCEDURE updateParticipant(p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_loan_participant_rec IN loan_participant_rec_type,
x_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
is
l_init_msg_list VARCHAR2(1);
l_api_name CONSTANT VARCHAR2(30) := 'updateParticipant';
l_last_api_called := 'LNS_PARTICIPANTS_PUB.updateParticipant';
savepoint updateParticipant;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin update participant');
SELECT OBJECT_VERSION_NUMBER,
ROWID
INTO l_object_version,
l_rowid
FROM LNS_PARTICIPANTS
WHERE PARTICIPANT_ID = p_loan_participant_rec.participant_id
FOR UPDATE OF PARTICIPANT_ID NOWAIT;
p_mode => 'UPDATE',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'Before calling LNS_PARTICIPANTS_PKG.Update_Row');
l_last_api_called := 'LNS_PARTICIPANTS_PKG.Update_Row';
LNS_PARTICIPANTS_PKG.Update_Row(x_rowid => l_rowid
,p_PARTICIPANT_ID => l_loan_participant_rec.PARTICIPANT_ID
,p_LOAN_ID => l_loan_participant_rec.LOAN_ID
,p_HZ_PARTY_ID => l_loan_participant_rec.HZ_PARTY_ID
,p_LOAN_PARTICIPANT_TYPE => l_loan_participant_rec.LOAN_PARTICIPANT_TYPE
,p_START_DATE_ACTIVE => l_loan_participant_rec.START_DATE_ACTIVE
,p_END_DATE_ACTIVE => l_loan_participant_rec.END_DATE_ACTIVE
,p_CUST_ACCOUNT_ID => l_loan_participant_rec.CUST_ACCOUNT_ID
,p_BILL_TO_ACCT_SITE_ID => l_loan_participant_rec.BILL_TO_ACCT_SITE_ID
,p_OBJECT_VERSION_NUMBER => x_object_version_number
,p_ATTRIBUTE_CATEGORY => l_loan_participant_rec.ATTRIBUTE_CATEGORY
,p_ATTRIBUTE1 => l_loan_participant_rec.ATTRIBUTE1
,p_ATTRIBUTE2 => l_loan_participant_rec.ATTRIBUTE2
,p_ATTRIBUTE3 => l_loan_participant_rec.ATTRIBUTE3
,p_ATTRIBUTE4 => l_loan_participant_rec.ATTRIBUTE4
,p_ATTRIBUTE5 => l_loan_participant_rec.ATTRIBUTE5
,p_ATTRIBUTE6 => l_loan_participant_rec.ATTRIBUTE6
,p_ATTRIBUTE7 => l_loan_participant_rec.ATTRIBUTE7
,p_ATTRIBUTE8 => l_loan_participant_rec.ATTRIBUTE8
,p_ATTRIBUTE9 => l_loan_participant_rec.ATTRIBUTE9
,p_ATTRIBUTE10 => l_loan_participant_rec.ATTRIBUTE10
,p_ATTRIBUTE11 => l_loan_participant_rec.ATTRIBUTE11
,p_ATTRIBUTE12 => l_loan_participant_rec.ATTRIBUTE12
,p_ATTRIBUTE13 => l_loan_participant_rec.ATTRIBUTE13
,p_ATTRIBUTE14 => l_loan_participant_rec.ATTRIBUTE14
,p_ATTRIBUTE15 => l_loan_participant_rec.ATTRIBUTE15
,p_ATTRIBUTE16 => l_loan_participant_rec.ATTRIBUTE16
,p_ATTRIBUTE17 => l_loan_participant_rec.ATTRIBUTE17
,p_ATTRIBUTE18 => l_loan_participant_rec.ATTRIBUTE18
,p_ATTRIBUTE19 => l_loan_participant_rec.ATTRIBUTE19
,p_ATTRIBUTE20 => l_loan_participant_rec.ATTRIBUTE20
,P_ACCESS_LEVEL => p_loan_participant_rec.ACCESS_LEVEL
,p_CONTACT_REL_PARTY_ID => l_loan_participant_rec.CONTACT_REL_PARTY_ID
,p_CONTACT_PERS_PARTY_ID => l_loan_participant_rec.CONTACT_PERS_PARTY_ID
,p_CREDIT_REQUEST_ID => l_loan_participant_rec.CREDIT_REQUEST_ID
,p_CASE_FOLDER_ID => l_loan_participant_rec.CASE_FOLDER_ID
,p_REVIEW_TYPE => l_loan_participant_rec.REVIEW_TYPE
,p_CREDIT_CLASSIFICATION => l_loan_participant_rec.CREDIT_CLASSIFICATION
);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'After calling LNS_PARTICIPANTS_PKG.Update_Row');
ROLLBACK TO updateParticipant;
ROLLBACK TO updateParticipant;
ROLLBACK TO updateParticipant;
END updateParticipant;
select
participant_id, loan_id, LOAN_PARTICIPANT_TYPE, hz_party_id, credit_request_id,
case_folder_id, object_version_number,review_type,credit_classification
from lns_participants
where loan_id = pLoanId
and LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER'
and end_date_active is null;
SELECT OBJECT_VERSION_NUMBER
FROM
LNS_LOAN_HEADERS_ALL
WHERE LOAN_ID = pLoanId;
l_last_api_called := 'LNS_PARTICIPANTS_PUB.updateParticipant';
updateParticipant(p_init_msg_list => 'T',
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_loan_participant_rec => l_loan_participant_rec,
x_object_version_number => l_object_version,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_last_api_called := 'lns_loan_header_pub.update_loan';
lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_FALSE
,P_LOAN_HEADER_REC => l_loan_header_rec
,P_OBJECT_VERSION_NUMBER => l_object_version
,X_RETURN_STATUS => x_return_status
,X_MSG_COUNT => x_msg_count
,X_MSG_DATA => x_msg_data);
select org_id,credit_review_flag,loan_number,requested_amount,loan_currency,loan_description,loan_status,secondary_status
from lns_loan_headers_all
where loan_id = p_loan_id;
--update with parent credit request id
select credit_request_id into l_parent_credit_request_id
from lns_participants
where loan_id = l_loan_id
and loan_participant_type = 'PRIMARY_BORROWER'
and end_date_active is null;
select credit_request_id into l_parent_credit_request_id
from lns_participants
where loan_id = l_loan_id
and loan_participant_type = 'PRIMARY_BORROWER'
and end_date_active is null;
SELECT OBJECT_VERSION_NUMBER
FROM
LNS_LOAN_HEADERS_ALL
WHERE LOAN_ID = pLoanId;
l_last_api_called := 'lns_loan_header_pub.update_loan';
lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_FALSE
,P_LOAN_HEADER_REC => l_loan_header_rec
,P_OBJECT_VERSION_NUMBER => l_object_version
,X_RETURN_STATUS => x_return_status
,X_MSG_COUNT => x_msg_count
,X_MSG_DATA => x_msg_data);
SELECT CREDIT_REQUEST_ID
FROM
LNS_PARTICIPANTS
WHERE LOAN_ID = pLoanId
AND LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER'
AND END_DATE_ACTIVE IS NULL;
SELECT OBJECT_VERSION_NUMBER
FROM
LNS_LOAN_HEADERS_ALL
WHERE LOAN_ID = pLoanId;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In submitCreditRequest: Before calling OCM_CREDIT_REQUEST_UPDATE_PUB.update_credit_request_status api');
l_last_api_called := 'OCM_CREDIT_REQUEST_UPDATE_PUB.update_credit_request_status';
OCM_CREDIT_REQUEST_UPDATE_PUB.update_credit_request_status (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_credit_request_id => l_primary_credit_request_id,
p_credit_request_status => 'SUBMIT');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In submitCreditRequest: Before calling lns_loan_header_pub.update_loan api');
l_last_api_called := 'lns_loan_header_pub.update_loan';
lns_loan_header_pub.update_loan(
p_init_msg_list => FND_API.G_TRUE
,P_LOAN_HEADER_REC => l_loan_header_rec
,P_OBJECT_VERSION_NUMBER => l_object_version
,X_RETURN_STATUS => x_return_status
,X_MSG_COUNT => x_msg_count
,X_MSG_DATA => x_msg_data);
FUNCTION CASE_FOLDER_UPDATE(p_loan_id IN NUMBER) RETURN VARCHAR2
IS
l_return_flag VARCHAR2(1);
l_api_name CONSTANT VARCHAR2(30) := 'CASE_FOLDER_UPDATE';
select secondary_status
from lns_loan_headers_all
where loan_id = pLoanId;
select count(case.case_folder_id)
from ar_cmgt_case_folders case, lns_participants part
where case.credit_request_id = part.credit_request_id and
part.loan_id = p_loan_id and
part.loan_participant_type = 'PRIMARY_BORROWER' and
part.end_date_active is null and
case.type = 'CASE' and
case.status = 'CLOSED';
SELECT case_folder.case_folder_id,case_folder.credit_classification,part.participant_id
FROM ar_cmgt_case_folders case_folder,lns_participants part
WHERE case_folder.credit_request_id = part.credit_request_id
AND part.loan_id = pLoanId
AND type = 'CASE'
AND status = 'CLOSED';
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');
l_login_id := LNS_UTILITY_PUB.last_update_login;
UPDATE LNS_PARTICIPANTS
SET CASE_FOLDER_ID = case_folder_rec.case_folder_id,
CREDIT_CLASSIFICATION = case_folder_rec.credit_classification,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = l_date,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE PARTICIPANT_ID = case_folder_rec.participant_id;
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');
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');
UPDATE LNS_LOAN_HEADERS_ALL
SET SECONDARY_STATUS = 'CREDIT_REVIEW_COMPLETE',
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = l_date,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE LOAN_ID = p_loan_id
AND SECONDARY_STATUS = 'IN_CREDIT_REVIEW'
AND EXISTS (SELECT case_folder_id
FROM LNS_PARTICIPANTS
WHERE loan_id = LNS_LOAN_HEADERS_ALL.loan_id and hz_party_id = LNS_LOAN_HEADERS_ALL.primary_borrower_id);
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');
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);
END CASE_FOLDER_UPDATE;
l_update_status_flag VARCHAR2(1);
SELECT OBJECT_VERSION_NUMBER
FROM
LNS_LOAN_HEADERS_ALL
WHERE LOAN_ID = pLoanId;
l_update_status_flag := LNS_PARTICIPANTS_PUB.CASE_FOLDER_UPDATE(l_loan_id);
if (l_update_status_flag NOT IN ('I', 'Y')) then
l_wf_return_status := 'ERROR';
SELECT CASE_FOLDER_ID INTO l_case_folder_id
FROM
AR_CMGT_CASE_FOLDERS
WHERE CREDIT_REQUEST_ID = l_credit_request_id;
UPDATE LNS_PARTICIPANTS
SET CASE_FOLDER_ID = l_case_folder_id
WHERE CREDIT_REQUEST_ID = l_credit_request_id;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In OCM_WORKFLOW_CREDIT_RECO_EVENT: Before calling lns_loan_header_pub.update_loan api');
lns_loan_header_pub.update_loan(
p_init_msg_list => FND_API.G_TRUE
,P_LOAN_HEADER_REC => l_loan_header_rec
,P_OBJECT_VERSION_NUMBER => l_object_version
,X_RETURN_STATUS => l_return_status
,X_MSG_COUNT => l_msg_count
,X_MSG_DATA => l_msg_data);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In OCM_WORKFLOW_CREDIT_RECO_EVENT: After calling lns_loan_header_pub.update_loan api: return_status:'||l_return_status);
select party_type
from hz_parties
where party_id = p_party_id;