eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.LNS_INDEX_RATES_PUB SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 66


 |      UPDATE_FLOATING_RATE_LOANS
 |
 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
 |      None
 |
 | PARAMETERS
 |      p_msg_level     IN      Debug msg level
 |      p_msg           IN      Debug msg itself
 |
 | KNOWN ISSUES
 |      None
 |
 | NOTES
 |      Any interesting aspect of the code in the package body which needs
 |      to be stated.
 |
 | MODIFICATION HISTORY
 | Date                  Author            Description of Changes
 | 01-01-2004            scherkas          Created
 |
 *=======================================================================*/
Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
IS
BEGIN
    if (p_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then

        FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);

Line: 330


    l_update1                       boolean;

Line: 333


    l_do_insert                     boolean;

Line: 350


        SELECT rate_id,
            begin_installment_number,
            end_installment_number,
            index_rate,
            spread,
            CURRENT_INTEREST_RATE,
            INTEREST_ONLY_FLAG
        FROM lns_rate_schedules
        WHERE end_date_active IS NULL
            AND term_id = termId
            AND PHASE = p_phase
        order by begin_installment_number;

Line: 408


    l_RATE_SCHEDS_TBL.delete;

Line: 452


    l_TEMP_ADJ_RATES_TBL.delete;

Line: 473


    l_ADJ_RATES_TBL.delete;

Line: 503


    l_update1 := true;

Line: 550


                    l_merged_rates_tbl(merged_count).ACTION := 'UPDATE';

Line: 551


                    l_update1 := false;

Line: 568


                        l_merged_rates_tbl(merged_count).ACTION := 'UPDATE';

Line: 587


                        l_merged_rates_tbl(merged_count).ACTION := 'UPDATE';

Line: 648


                        l_do_insert := true;

Line: 650


                        if l_start_from_installment > 1 and l_update1 then

                            l_merged_rates_tbl(merged_count) := l_RATE_SCHEDS_TBL(rate_sched_count);

Line: 653


                            l_merged_rates_tbl(merged_count).ACTION := 'UPDATE';

Line: 656


                            l_update1 := false;

Line: 661


                                l_do_insert := false;

Line: 666


                                l_do_insert := true;

Line: 672


                        if l_do_insert then

                            l_merged_rates_tbl(merged_count).RATE_ID := null;

Line: 681


                            l_merged_rates_tbl(merged_count).ACTION := 'INSERT';

Line: 685


                                logMessage(FND_LOG.LEVEL_STATEMENT, 'if 45 - inserting');

Line: 691


                                logMessage(FND_LOG.LEVEL_STATEMENT, 'if 46 - inserting');

Line: 697


                                logMessage(FND_LOG.LEVEL_STATEMENT, 'if 47 - inserting');

Line: 757


        if l_merged_rates_tbl(k).ACTION = 'UPDATE' then

            update lns_rate_schedules
                set index_rate = l_merged_rates_tbl(k).INDEX_RATE
                    ,current_interest_rate = l_merged_rates_tbl(k).CURRENT_INTEREST_RATE
                    ,end_installment_number = l_merged_rates_tbl(k).END_INSTALLMENT_NUMBER
                    ,last_update_date = sysdate
                    ,last_updated_by = LNS_UTILITY_PUB.last_updated_by
                    ,last_update_login = LNS_UTILITY_PUB.last_update_login
                    ,object_version_number = object_version_number + 1
            where rate_id = l_merged_rates_tbl(k).RATE_ID;

Line: 769


        elsif l_merged_rates_tbl(k).ACTION = 'INSERT' then

            select LNS_RATE_SCHEDULES_S.NEXTVAL into l_merged_rates_tbl(k).RATE_ID from dual;

Line: 773


            insert into lns_rate_schedules(
                RATE_ID
                ,TERM_ID
                ,INDEX_RATE
                ,SPREAD
                ,CURRENT_INTEREST_RATE
                ,START_DATE_ACTIVE
                ,END_DATE_ACTIVE
                ,CREATED_BY
                ,CREATION_DATE
                ,LAST_UPDATED_BY
                ,LAST_UPDATE_DATE
                ,LAST_UPDATE_LOGIN
                ,OBJECT_VERSION_NUMBER
                ,BEGIN_INSTALLMENT_NUMBER
                ,END_INSTALLMENT_NUMBER
                ,INTEREST_ONLY_FLAG
                ,PHASE
                )
            VALUES
                (l_merged_rates_tbl(k).RATE_ID
                ,l_merged_rates_tbl(k).TERM_ID
                ,l_merged_rates_tbl(k).INDEX_RATE
                ,l_merged_rates_tbl(k).SPREAD
                ,l_merged_rates_tbl(k).CURRENT_INTEREST_RATE
                ,sysdate
                ,null
                ,LNS_UTILITY_PUB.created_by
                ,sysdate
                ,LNS_UTILITY_PUB.last_updated_by
                ,sysdate
                ,LNS_UTILITY_PUB.last_update_login
                ,1
                ,l_merged_rates_tbl(k).BEGIN_INSTALLMENT_NUMBER
                ,l_merged_rates_tbl(k).END_INSTALLMENT_NUMBER
                ,l_merged_rates_tbl(k).INTEREST_ONLY_FLAG
                ,P_LOAN_REC.CURRENT_PHASE
                );

Line: 874


 | PUBLIC PROCEDURE UPDATE_FLOATING_RATE_LOANS
 |
 | DESCRIPTION
 |      This procedure gets called from CM to mass update index rate for floating loans.
 |		Concurrent Program Name: "LNS: Mass Update Floating Rate Loans"
 |
 | PSEUDO CODE/LOGIC
 |
 | PARAMETERS
 |      ERRBUF              OUT     Returns errors to CM
 |      RETCODE             OUT     Returns error code to CM
 |      INDEX_RATE_ID     IN      Inputs index rate type
 |      INTEREST_RATE_LINE_ID IN    Inputs index rate
 |
 | KNOWN ISSUES
 |      None
 |
 | NOTES
 |
 | MODIFICATION HISTORY
 | Date                  Author            Description of Changes
 | 07-SEP-2006           karamach          Created
 | 12-Mar-2008           scherkas          Fix for bug 6849817: changed program logic to support multiple rate schedule rows
 |
 *=======================================================================*/
PROCEDURE UPDATE_FLOATING_RATE_LOANS(
    ERRBUF              OUT NOCOPY     VARCHAR2,
    RETCODE             OUT NOCOPY     VARCHAR2,
    P_INDEX_RATE_ID     IN             NUMBER)
IS

/*-----------------------------------------------------------------------+
 | Local Variable Declarations and initializations                       |
 +-----------------------------------------------------------------------*/
    l_api_name                      CONSTANT VARCHAR2(30) := 'UPDATE_FLOATING_RATE_LOANS';

Line: 929


        SELECT loan.loan_id,
            term.term_id,
            loan.loan_number,
            lns_billing_util_pub.last_payment_number(term.loan_id) last_billed_installment,
            loan.loan_status,
            loan.CURRENT_PHASE,
            decode(loan.CURRENT_PHASE, 'TERM', term.percent_increase, 'OPEN', term.open_percent_increase),
            decode(loan.CURRENT_PHASE, 'TERM', term.percent_increase_life, 'OPEN', term.open_percent_increase_life),
            decode(loan.CURRENT_PHASE, 'TERM', term.floor_rate, 'OPEN', term.open_floor_rate),
            decode(loan.CURRENT_PHASE, 'TERM', term.ceiling_rate, 'OPEN', term.open_ceiling_rate)
        FROM lns_loan_headers loan,
            lns_terms term
        WHERE loan.loan_id = term.loan_id
            AND term.rate_type = 'FLOATING'
            AND loan.loan_status NOT IN ('PAIDOFF','REJECTED','DELETED')
            AND nvl(indexRateId, term.index_rate_id) = term.index_rate_id;

Line: 947


        SELECT hdr.interest_rate_id,
            hdr.interest_rate_name,
            hdr.interest_rate_description
        FROM lns_int_rate_headers_vl hdr
        WHERE (EXISTS
            (SELECT null
            FROM lns_loan_headers loan,
                lns_terms term
            WHERE loan.loan_id = term.loan_id
                AND term.rate_type = 'FLOATING'
                AND loan.loan_status NOT IN ('PAIDOFF','REJECTED','DELETED')
                AND term.index_rate_id = hdr.interest_rate_id)
        AND nvl(P_INDEX_RATE_ID, hdr.interest_rate_id) = hdr.interest_rate_id)
        order by hdr.interest_rate_name;

Line: 963


        SELECT interest_rate_line_id,
            interest_rate_id,
            interest_rate,
            start_date_active,
            end_date_active
        FROM lns_int_rate_lines
        WHERE interest_rate_id = indexRateId
        order by start_date_active;

Line: 977


    SAVEPOINT UPDATE_FLOATING_RATE_LOANS_PVT;

Line: 1003


        l_RATE_LINES_TBL.delete;

Line: 1077


        ERRBUF := 'Not all floating rate loans were updated successfully. Please review log file.';

Line: 1088


        ERRBUF := 	'Update of floating rate loans has failed. Please review log file.';

Line: 1095


END UPDATE_FLOATING_RATE_LOANS;

Line: 1100


PROCEDURE UPDATE_LOAN_FLOATING_RATE(
    P_API_VERSION		    IN          NUMBER,
    P_INIT_MSG_LIST		    IN          VARCHAR2,
    P_COMMIT			    IN          VARCHAR2,
    P_VALIDATION_LEVEL	    IN          NUMBER,
    P_LOAN_ID               IN          NUMBER,
    X_RETURN_STATUS		    OUT NOCOPY  VARCHAR2,
    X_MSG_COUNT			    OUT NOCOPY  NUMBER,
    X_MSG_DATA	    	    OUT NOCOPY  VARCHAR2)
IS

/*-----------------------------------------------------------------------+
 | Local Variable Declarations and initializations                       |
 +-----------------------------------------------------------------------*/

    l_api_name                      CONSTANT VARCHAR2(30) := 'UPDATE_LOAN_FLOATING_RATE';

Line: 1132


        SELECT loan.loan_id,
            term.term_id,
            loan.loan_number,
            lns_billing_util_pub.last_payment_number(term.loan_id) last_billed_installment,
            loan.loan_status,
            loan.CURRENT_PHASE,
            term.index_rate_id,
            hdr.interest_rate_name,
            decode(loan.CURRENT_PHASE, 'TERM', term.percent_increase, 'OPEN', term.open_percent_increase),
            decode(loan.CURRENT_PHASE, 'TERM', term.percent_increase_life, 'OPEN', term.open_percent_increase_life),
            decode(loan.CURRENT_PHASE, 'TERM', term.floor_rate, 'OPEN', term.open_floor_rate),
            decode(loan.CURRENT_PHASE, 'TERM', term.ceiling_rate, 'OPEN', term.open_ceiling_rate)
        FROM lns_loan_headers loan,
            lns_terms term,
            lns_int_rate_headers_vl hdr
        WHERE loan.loan_id = p_loan_id
            AND loan.loan_id = term.loan_id
            AND term.rate_type = 'FLOATING'
            AND loan.loan_status NOT IN ('PAIDOFF','REJECTED','DELETED')
            AND term.index_rate_id = hdr.interest_rate_id;

Line: 1154


        SELECT interest_rate_line_id,
            interest_rate_id,
            interest_rate,
            start_date_active,
            end_date_active+1
        FROM lns_int_rate_lines
        WHERE interest_rate_id = indexRateId
        order by start_date_active;

Line: 1168


    SAVEPOINT UPDATE_LOAN_FLOATING_RATE;

Line: 1206


    l_RATE_LINES_TBL.delete;

Line: 1260


        ROLLBACK TO UPDATE_LOAN_FLOATING_RATE;

Line: 1267


        ROLLBACK TO UPDATE_LOAN_FLOATING_RATE;

Line: 1274


        ROLLBACK TO UPDATE_LOAN_FLOATING_RATE;