The following lines contain the word 'select', 'insert', 'update' or 'delete':
| PUBLIC PROCEDURE UPDATE_LINE_ADJUSTMENT_NUMBER
|
| DESCRIPTION
| This procedure updates the rec number column in loan lines table based on AR Adjustment api out parameter during loan approval
|
| NOTES
| There are no table-handler apis for loan lines table since it uses java-based EO
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 10-Jan-2006 karamach Added payment_schedule_id and installment_number for lns_loan_lines per bug#4887994
| 20-Dec-2004 karamach Created
|
*=======================================================================*/
PROCEDURE UPDATE_LINE_ADJUSTMENT_NUMBER(
p_init_msg_list IN VARCHAR2
,p_loan_id IN NUMBER
,p_loan_line_id IN NUMBER
,p_rec_adjustment_number IN VARCHAR2
,p_rec_adjustment_id IN NUMBER
,p_payment_schedule_id IN NUMBER
,p_installment_number IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(1);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin UPDATE_LINE_ADJUSTMENT_NUMBER for loan_line_id: '|| p_loan_line_id);
-- update loan line
UPDATE LNS_LOAN_LINES
SET REC_ADJUSTMENT_NUMBER = p_rec_adjustment_number,
REC_ADJUSTMENT_ID = p_rec_adjustment_id,
PAYMENT_SCHEDULE_ID = p_payment_schedule_id,
INSTALLMENT_NUMBER = p_installment_number,
LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE LOAN_LINE_ID = p_loan_line_id
AND LOAN_ID = p_loan_id;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End UPDATE_LINE_ADJUSTMENT_NUMBER for loan_line_id: '|| p_loan_line_id);
END UPDATE_LINE_ADJUSTMENT_NUMBER;
| for ERS loan receivables derivation and inserts into loan lines table.
| If NO rules have been defined for the loan product, calling this api retrieves
| ALL OPEN Receivables for the customer and inserts them into loan lines.
| The function returns the total requested amount for updating loan header
| after inserting the receivables into lns_loan_lines table.
|
| NOTES
| This api does a bulk select if max_requested_amount is NOT specified on the product.
| This api does bulk insert into lns_loan_lines after retrieving the matching receivables into table types.
| Incase an error is encountered during processing the api returns zero with error message in the stack.
| The api also returns zero if no receivables found for inserting into loan lines.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 13-Jan-2006 karamach Created
*=======================================================================*/
FUNCTION GET_RULES_DERIVED_ERS_AMOUNT(
p_loan_id IN NUMBER,
p_primary_borrower_id IN NUMBER,
p_currency_code IN VARCHAR2,
p_org_id IN NUMBER,
p_loan_product_id IN NUMBER
) RETURN NUMBER
IS
l_api_name CONSTANT VARCHAR2(30) := 'GET_RULES_DERIVED_ERS_AMOUNT';
select 'Y' line_exists
from dual
where exists
(select null from lns_loan_lines where loan_id = pLoanId and end_date is null);
select loan_product_name,MAX_REQUESTED_AMOUNT
from lns_loan_products_all_vl
where loan_product_id = pLoanProductId
and org_id = pOrgId;
select 'Y' rule_exists, attr.DEFAULT_VALUE sort_attribute
FROM
FUN_RULE_OBJECTS_B obj, FUN_RULE_OBJ_ATTRIBUTES attr
WHERE obj.RULE_OBJECT_NAME = pRuleObjectName
AND obj.application_id = pApplicationId
AND obj.instance_label = to_char(pLoanProductId)
AND obj.ORG_ID = pOrgId
AND obj.PARENT_RULE_OBJECT_ID is not null
AND obj.RULE_OBJECT_ID = attr.RULE_OBJECT_ID;
select inv.payment_schedule_id,
inv.customer_trx_id,
inv.trx_number,
inv.AMOUNT_DUE_REMAINING,
inv.TERMS_SEQUENCE_NUMBER
FROM
LNS_OPEN_RECEIVABLES_V inv,
FUN_RULE_BULK_RESULT_GT results,
FUN_RULE_DETAILS rules
WHERE inv.payment_schedule_id = results.id
AND rules.RULE_DETAIL_ID = results.RULE_DETAIL_ID
AND results.RULE_DETAIL_ID <> -99
ORDER BY rules.SEQ, pSortAttribute;
(select DEFAULT_VALUE from
FUN_RULE_OBJ_ATTRIBUTES attr, FUN_RULE_DETAILS rule, FUN_RULE_BULK_RESULT_GT gt
where attr.RULE_OBJECT_ID = rule.rule_object_id
and rule.rule_detail_id = gt.rule_detail_id
and rownum < 2);
select inv.payment_schedule_id,
inv.customer_trx_id,
inv.trx_number,
inv.AMOUNT_DUE_REMAINING reference_amount,
inv.AMOUNT_DUE_REMAINING,
inv.TERMS_SEQUENCE_NUMBER
FROM
LNS_OPEN_RECEIVABLES_V inv,
FUN_RULE_BULK_RESULT_GT results
WHERE inv.payment_schedule_id = results.id
AND results.RULE_DETAIL_ID <> -99;
select inv.payment_schedule_id,
inv.customer_trx_id,
inv.trx_number,
inv.AMOUNT_DUE_REMAINING reference_amount,
inv.AMOUNT_DUE_REMAINING,
inv.TERMS_SEQUENCE_NUMBER
FROM
LNS_OPEN_RECEIVABLES_V inv
WHERE inv.party_id = pPartyId
AND inv.org_id = pOrgId
AND inv.invoice_currency_code = pCurrencyCode;
select sum(requested_amount) total_amount, count(loan_line_id) record_count
from lns_loan_lines
where loan_id = pLoanId
and end_date is null;
l_login_id := LNS_UTILITY_PUB.last_update_login;
l_last_api_called := 'Bulk insert into lns_loan_lines';
insert into lns_loan_lines(
LOAN_LINE_ID
,LOAN_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
,OBJECT_VERSION_NUMBER
,REFERENCE_TYPE
,REFERENCE_ID
,REFERENCE_NUMBER
,REFERENCE_DESCRIPTION
,REFERENCE_AMOUNT
,REQUESTED_AMOUNT
,REC_ADJUSTMENT_NUMBER
,END_DATE
,REC_ADJUSTMENT_ID
,PAYMENT_SCHEDULE_ID
,INSTALLMENT_NUMBER
)
values(
LNS_LOAN_LINE_S.nextval
,l_loan_id
,l_date
,l_user_id
,l_login_id
,l_date
,l_user_id
,1
,'RECEIVABLE'
,l_reference_id_tbl(i)
,l_reference_number_tbl(i)
,null
,l_reference_amount_tbl(i)
,l_requested_amount_tbl(i)
,null
,null
,null
,l_pmt_sch_id_tbl(i)
,l_installment_number_tbl(i)
);
--handle case when no loan lines were inserted by this procedure
if (l_loan_amount is null) then
l_loan_amount := 0;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Inserted '|| l_record_count || ' rows into lns_loan_lines successfully!');