[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE do_update_approval_action (
p_approval_action_rec IN OUT NOCOPY APPROVAL_ACTION_REC_TYPE
,p_object_version_number IN OUT NOCOPY NUMBER
,x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE do_delete_approval_action (
p_action_id IN NUMBER
,x_return_status IN OUT NOCOPY VARCHAR2
);
SELECT 'Y'
INTO l_dummy
FROM LNS_APPROVAL_ACTIONS
WHERE action_id = l_action_id;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_approval_action procedure: Before call to LNS_APPROVAL_ACTIONS_PKG.Insert_Row');
LNS_APPROVAL_ACTIONS_PKG.Insert_Row(X_ACTION_ID => p_approval_action_rec.action_id
,P_OBJECT_VERSION_NUMBER => 1
,P_LOAN_ID => p_approval_action_rec.loan_id
,P_ACTION_TYPE => p_approval_action_rec.action_type
,P_AMOUNT => p_approval_action_rec.amount
,P_REASON_CODE => p_approval_action_rec.reason_code
,P_ATTRIBUTE_CATEGORY => p_approval_action_rec.attribute_category
,P_ATTRIBUTE1 => p_approval_action_rec.attribute1
,P_ATTRIBUTE2 => p_approval_action_rec.attribute2
,P_ATTRIBUTE3 => p_approval_action_rec.attribute3
,P_ATTRIBUTE4 => p_approval_action_rec.attribute4
,P_ATTRIBUTE5 => p_approval_action_rec.attribute5
,P_ATTRIBUTE6 => p_approval_action_rec.attribute6
,P_ATTRIBUTE7 => p_approval_action_rec.attribute7
,P_ATTRIBUTE8 => p_approval_action_rec.attribute8
,P_ATTRIBUTE9 => p_approval_action_rec.attribute9
,P_ATTRIBUTE10 => p_approval_action_rec.attribute10
,P_ATTRIBUTE11 => p_approval_action_rec.attribute11
,P_ATTRIBUTE12 => p_approval_action_rec.attribute12
,P_ATTRIBUTE13 => p_approval_action_rec.attribute13
,P_ATTRIBUTE14 => p_approval_action_rec.attribute14
,P_ATTRIBUTE15 => p_approval_action_rec.attribute15
,P_ATTRIBUTE16 => p_approval_action_rec.attribute16
,P_ATTRIBUTE17 => p_approval_action_rec.attribute17
,P_ATTRIBUTE18 => p_approval_action_rec.attribute18
,P_ATTRIBUTE19 => p_approval_action_rec.attribute19
,P_ATTRIBUTE20 => p_approval_action_rec.attribute20);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_approval_action procedure: After call to LNS_APPROVAL_ACTION.Insert_Row');
| do_update_approval_action
|
| DESCRIPTION
| Updates approval action.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/OUT:
| p_approval_action_rec
| p_object_version_number
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
| 22-Jan-2004 Bernice Lam Created.
+===========================================================================*/
PROCEDURE do_update_approval_action(
p_approval_action_rec IN OUT NOCOPY APPROVAL_ACTION_REC_TYPE
,p_object_version_number IN OUT NOCOPY NUMBER
,x_return_status IN OUT NOCOPY VARCHAR2) IS
l_object_version_number NUMBER;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_update_approval_action procedure');
SELECT OBJECT_VERSION_NUMBER,
ROWID
INTO l_object_version_number,
l_rowid
FROM LNS_APPROVAL_ACTIONS
WHERE ACTION_ID = p_approval_action_rec.action_id
FOR UPDATE OF ACTION_ID NOWAIT;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_update_approval_action procedure: Before call to LNS_APPROVAL_ACTIONS_PKG.Update_Row');
LNS_APPROVAL_ACTIONS_PKG.Update_Row (X_ACTION_ID => p_approval_action_rec.action_id
,P_OBJECT_VERSION_NUMBER => p_OBJECT_VERSION_NUMBER
,P_LOAN_ID => p_approval_action_rec.LOAN_ID
,P_ACTION_TYPE => p_approval_action_rec.ACTION_TYPE
,P_AMOUNT => p_approval_action_rec.AMOUNT
,P_REASON_CODE => p_approval_action_rec.REASON_CODE
,P_ATTRIBUTE_CATEGORY => p_approval_action_rec.attribute_category
,P_ATTRIBUTE1 => p_approval_action_rec.attribute1
,P_ATTRIBUTE2 => p_approval_action_rec.attribute2
,P_ATTRIBUTE3 => p_approval_action_rec.attribute3
,P_ATTRIBUTE4 => p_approval_action_rec.attribute4
,P_ATTRIBUTE5 => p_approval_action_rec.attribute5
,P_ATTRIBUTE6 => p_approval_action_rec.attribute6
,P_ATTRIBUTE7 => p_approval_action_rec.attribute7
,P_ATTRIBUTE8 => p_approval_action_rec.attribute8
,P_ATTRIBUTE9 => p_approval_action_rec.attribute9
,P_ATTRIBUTE10 => p_approval_action_rec.attribute10
,P_ATTRIBUTE11 => p_approval_action_rec.attribute11
,P_ATTRIBUTE12 => p_approval_action_rec.attribute12
,P_ATTRIBUTE13 => p_approval_action_rec.attribute13
,P_ATTRIBUTE14 => p_approval_action_rec.attribute14
,P_ATTRIBUTE15 => p_approval_action_rec.attribute15
,P_ATTRIBUTE16 => p_approval_action_rec.attribute16
,P_ATTRIBUTE17 => p_approval_action_rec.attribute17
,P_ATTRIBUTE18 => p_approval_action_rec.attribute18
,P_ATTRIBUTE19 => p_approval_action_rec.attribute19
,P_ATTRIBUTE20 => p_approval_action_rec.attribute20);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_update_approval_action procedure: After call to LNS_APPROVAL_ACTIONS_PKG.Update_Row');
END do_update_approval_action;
| do_delete_approval_action
|
| DESCRIPTION
| Deletes approval action.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/OUT:
| p_action_id
| p_object_version_number
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
| 22-Jan-2004 Bernice Lam Created.
+===========================================================================*/
PROCEDURE do_delete_approval_action(
p_action_id NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_dummy VARCHAR2(1);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_delete_approval_action procedure');
SELECT 'Y'
INTO l_dummy
FROM LNS_APPROVAL_ACTIONS
WHERE ACTION_ID = p_action_id;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_delete_approval_action procedure: Before call to LNS_APPROVAL_ACTIONS_PKG.Delete_Row');
LNS_APPROVAL_ACTIONS_PKG.Delete_Row (P_ACTION_ID => p_action_id);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_delete_approval_action procedure: After call to LNS_APPROVAL_ACTIONS_PKG.Delete_Row');
END do_delete_approval_action;
SELECT 'X'
FROM LNS_FEE_ASSIGNMENTS
WHERE loan_id = X_LOAN_ID
AND end_installment_number > x_install_num;
SELECT H.OBJECT_VERSION_NUMBER
,H.LOAN_STATUS
,H.LOAN_CURRENCY
,H.LOAN_NUMBER
,H.LOAN_CLASS_CODE
,H.LOAN_TYPE
,H.GL_DATE
,H.REQUESTED_AMOUNT
,H.REFERENCE_TYPE
,H.REFERENCE_ID
,H.CURRENT_PHASE
,H.MULTIPLE_FUNDING_FLAG
,H.OPEN_TO_TERM_FLAG
,NVL(P.BDGT_REQ_FOR_APPR_FLAG, 'N') BDGT_REQ_FOR_APPR_FLAG
,NVL(P.LOAN_APPR_REQ_FLAG, 'Y') LOAN_APPR_REQ_FLAG
,H.SECONDARY_STATUS
,nvl(custom_payments_flag, 'N')
FROM LNS_LOAN_HEADERS_ALL H,
LNS_LOAN_PRODUCTS_ALL P
WHERE H.LOAN_ID = X_Loan_Id
and H.product_id = P.loan_product_id(+);
SELECT RESOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE USER_ID = X_USER_ID;
select DISTRIBUTION_ID
,CODE_COMBINATION_ID
,DISTRIBUTION_PERCENT
from lns_distributions
where LOAN_ID = x_loan_id
and account_type = x_acct_type
and account_name = x_acct_name
and line_type = x_line_type
and distribution_type = 'ORIGINATION';
select so.set_of_books_id
from lns_system_options sb,
gl_sets_of_books so
where sb.set_of_books_id = so.set_of_books_id;
select TERM_ID
,OBJECT_VERSION_NUMBER
from LNS_TERMS
where LOAN_ID = P_LOAN_ID;
select OBJECT_VERSION_NUMBER
from LNS_LOAN_HEADERS
where LOAN_ID = P_LOAN_ID;
select old_value
from lns_loan_histories_h
where table_name = 'LNS_LOAN_HEADERS_ALL' and
column_name = 'SECONDARY_STATUS' and
new_value = 'PENDING_CANCELLATION' and
loan_id = P_LOAN_ID and
loan_history_id =
(select max(loan_history_id)
from lns_loan_histories_h
where table_name = 'LNS_LOAN_HEADERS_ALL' and
column_name = 'SECONDARY_STATUS' and
loan_id = P_LOAN_ID);
select STATEMENT_XML
from LNS_LOAN_HEADERS loan,
lns_amortization_scheds am
where loan.loan_id = am.loan_id and
am.AMORTIZATION_SCHEDULE_ID = loan.LAST_AMORTIZATION_ID and
am.PAYMENT_NUMBER = loan.LAST_PAYMENT_NUMBER and
loan.loan_id = P_LOAN_ID;
SELECT count(1)
FROM lns_distributions dist
WHERE dist.loan_id = C_LOAN_ID
AND dist.activity = 'LNS_APPROVAL';
select nvl(BILL_ON_APPR_AMOUNT_FLAG, 'N')
from lns_loan_headers_all
where loan_id = p_loan_id;
select adj.gl_date
from ar_adjustments_all adj, lns_loan_lines lines
where lines.loan_id = p_loan_id
and lines.STATUS = 'APPROVED'
and lines.end_date is null
and lines.rec_adjustment_id = adj.adjustment_id
order by loan_line_id;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Before call to LNS_LOAN_HEADER_PUB.update_loan proc');
/* setting term data for do term update */
l_term_rec.TERM_ID := l_TERM_ID;
LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_TERM_VERSION_NUMBER
,p_init_msg_list => FND_API.G_FALSE
,p_loan_term_rec => l_term_rec
,X_RETURN_STATUS => x_return_status
,X_MSG_COUNT => x_msg_count
,X_MSG_DATA => x_msg_data);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Successfully update LNS_TERMS');
logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, l_api_name || ' - LNS_TERMS_PUB.update_term failed with message: ' || FND_MSG_PUB.Get(p_encoded => 'F'));
l_last_api_called := 'LNS_TERMS_PUB.update_term';
/* setting data for future loan update */
l_loan_header_rec.LOAN_START_DATE := l_loan_details.loan_start_date;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Data to Update loan header with during conversion...');
/* Bug#9255294 - No need to call this now, as the below method inserts records of conversionFees into feeSchds table
However, now the conversionFees insert into feeScheds when this fee is assigned to the loan
--Process Conversion Fees
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Before calling lns_fee_engine.processDisbursementFees to process Conversion fees for permanent conversion to TERM phase');
LNS_FEE_SCHEDULES_PKG.UPDATE_ROW(P_FEE_SCHEDULE_ID => l_fee_tbl(l_Count).fee_schedule_id
,P_FEE_ID => null
,P_LOAN_ID => l_approval_action_rec.loan_id
,P_FEE_AMOUNT => null
,P_FEE_INSTALLMENT => null
,P_FEE_DESCRIPTION => null
,P_ACTIVE_FLAG => null
,P_BILLED_FLAG => 'N' -- Make BilledFlag to 'N'
,P_FEE_WAIVABLE_FLAG => null
,P_WAIVED_AMOUNT => null
,P_LAST_UPDATED_BY => LNS_UTILITY_PUB.LAST_UPDATED_BY
,P_LAST_UPDATE_DATE => LNS_UTILITY_PUB.LAST_UPDATE_DATE
,P_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
,P_PROGRAM_ID => null
,P_REQUEST_ID => null
,P_OBJECT_VERSION_NUMBER => null);
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' fee_schedule_id : '|| l_fee_tbl(l_Count).fee_schedule_id||' updated succesfully ');
update LNS_COND_ASSIGNMENTS
set CONDITION_MET_FLAG = 'N',
FULFILLMENT_DATE = null,
FULFILLMENT_UPDATED_BY = null
where LOAN_ID = l_approval_action_rec.loan_id
and OWNER_OBJECT_ID is null
and OWNER_TABLE is null
and end_date_active is null
and CONDITION_ID in
(select CONDITION_ID
from LNS_CONDITIONS
where CONDITION_TYPE = 'APPROVAL'
and CUSTOM_PROCEDURE is not null);
select term_id, object_version_number into l_term_id, l_object_version_number
from lns_terms
where loan_id = l_loan_header_rec.loan_id;
LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_object_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_loan_term_rec => l_term_rec,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data);
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_TERMS');
SELECT
lower(iso_language),iso_territory
INTO
l_iso_language,l_iso_territory
FROM
FND_LANGUAGES
WHERE
language_code = USERENV('LANG');
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Before call to 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_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After call to LNS_LOAN_HEADER_PUB.update_loan proc');
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - LNS_LOAN_HEADER_PUB.update_loan failed with message: ' ||FND_MSG_PUB.Get(p_encoded => 'F'));
l_last_api_called := 'LNS_LOAN_HEADER_PUB.update_loan';
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Before call to LNS_LOAN_HEADER_PUB.update_loan 2');
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_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - After call to LNS_LOAN_HEADER_PUB.update_loan 2 proc');
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - LNS_LOAN_HEADER_PUB.update_loan failed with message: ' ||FND_MSG_PUB.Get(p_encoded => 'F'));
l_last_api_called := 'LNS_LOAN_HEADER_PUB.update_loan';
| update_approval_action
|
| DESCRIPTION
| Updates approval action.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_init_msg_list
| p_approval_action_rec
| OUT:
| x_return_status
| x_msg_count
| x_msg_data
| IN/OUT:
| p_object_version_number
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
| 22-Jan-2004 Bernice Lam Created
+===========================================================================*/
PROCEDURE update_approval_action (
p_init_msg_list IN VARCHAR2,
p_approval_action_rec IN APPROVAL_ACTION_REC_TYPE,
p_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_api_name CONSTANT VARCHAR2(30) := 'update_approval_action';
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Update_approval_action procedure');
SAVEPOINT update_approval_action;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Update_approval_action procedure: Before call to do_update_approval_action proc');
do_update_approval_action(
l_approval_action_rec,
p_object_version_number,
x_return_status);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Update_approval_action procedure: After call to do_update_approval_action proc');
ROLLBACK TO update_approval_action;
ROLLBACK TO update_approval_action;
ROLLBACK TO update_approval_action;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Update_approval_action procedure');
END update_approval_action;
| delete_approval_action
|
| DESCRIPTION
| Deletes approval action
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_init_msg_list
| p_action_id
| OUT:
| x_return_status
| x_msg_count
| x_msg_data
| IN/OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
| 06-Jan-2004 Bernice Lam Created.
+===========================================================================*/
PROCEDURE delete_approval_action (
p_init_msg_list IN VARCHAR2,
p_action_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_approval_action';
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Delete_approval_action procedure');
SAVEPOINT delete_approval_action;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Delete_approval_action procedure: Before call to do_delete_approval_action proc');
do_delete_approval_action(
l_action_id,
x_return_status);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Delete_approval_action procedure: After call to do_delete_approval_action proc');
ROLLBACK TO delete_approval_action;
ROLLBACK TO delete_approval_action;
ROLLBACK TO delete_approval_action;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Delete_approval_action procedure');
END delete_approval_action;
select loan.loan_id,
loan.LOAN_CURRENCY,
line.REFERENCE_NUMBER,
line.REQUESTED_AMOUNT,
line.ADJUSTMENT_DATE,
nvl(loan.FUNDED_AMOUNT, 0),
nvl(loan.ADD_REQUESTED_AMOUNT, 0),
loan.loan_status,
loan.OBJECT_VERSION_NUMBER
from lns_loan_lines line,
lns_loan_headers_all loan
where line.loan_line_id = P_LOAN_LINE_ID and
line.loan_id = loan.loan_id;
select count(1)
from LNS_COND_ASSIGNMENTS
where
OWNER_OBJECT_ID = P_LOAN_LINE_ID and
OWNER_TABLE = 'LNS_LOAN_LINES' and
MANDATORY_FLAG = 'Y' and
(CONDITION_MET_FLAG is null or CONDITION_MET_FLAG = 'N') and
(end_date_active is null or trunc(end_date_active) > trunc(sysdate));
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version_number,
P_LOAN_HEADER_REC => l_loan_header_rec,
P_INIT_MSG_LIST => FND_API.G_FALSE,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
select loan.loan_id,
loan.LOAN_CURRENCY,
adj.DESCRIPTION,
to_char(adj.ADJUSTMENT_AMOUNT, FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50)),
adj.EFFECTIVE_DATE
from LNS_LOAN_AMOUNT_ADJS adj,
lns_loan_headers_all loan
where adj.LOAN_AMOUNT_ADJ_ID = P_LOAN_AMOUNT_ADJ_ID and
adj.loan_id = loan.loan_id;
select count(1)
from LNS_COND_ASSIGNMENTS
where
OWNER_OBJECT_ID = P_LOAN_AMOUNT_ADJ_ID and
OWNER_TABLE = 'LNS_LOAN_AMOUNT_ADJS' and
MANDATORY_FLAG = 'Y' and
(CONDITION_MET_FLAG is null or CONDITION_MET_FLAG = 'N') and
(end_date_active is null or trunc(end_date_active) > trunc(sysdate));
FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
select wfn.notification_id
from WF_NOTIFICATIONS wfn
where item_key like l_loan_number
and message_name = P_MESSAGE_NAME
and recipient_role = P_CURRENT_APPROVER
and status = 'OPEN';