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_OCM_ADP_PUB SQL Statements

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

Line: 43


	   SELECT requested_amount , loan_currency
	   INTO  l_loan_req_amt , l_loan_currency
	   FROM  lns_loan_headers_all
	   WHERE loan_id = l_cr_loan_id ;

Line: 115


	   SELECT loan_subtype
	   INTO  l_loan_subtype
	   FROM  lns_loan_headers_all
	   WHERE loan_id = l_cr_loan_id ;

Line: 182


	   SELECT collateral_percent
	   INTO  l_loan_collateral_perecent
	   FROM  lns_loan_headers_all
	   WHERE loan_id = l_cr_loan_id ;

Line: 245


	   SELECT lrs.current_interest_rate
       INTO l_initial_interest_rate
       FROM lns_rate_schedules lrs,
            lns_loan_headers_all llh ,
            lns_terms lt
       WHERE llh.loan_id = l_cr_loan_id
       AND lt.loan_id = llh.loan_id
       AND lrs.term_id = lt.term_id
       AND lrs.begin_installment_number = 1
       AND llh.CURRENT_PHASE = lrs.PHASE;

Line: 315


       SELECT  count(*)
       INTO l_number_of_coborrowers
       FROM LNS_PARTICIPANTS  lp
       WHERE lp.loan_id = l_cr_loan_id
       AND lp.loan_participant_type = 'COBORROWER' ;

Line: 381


	   SELECT NVL ( (SELECT  'Y'
                FROM DUAL
                WHERE EXISTS  ( SELECT null
                FROM LNS_PARTICIPANTS  lp
                WHERE lp.loan_id = l_cr_loan_id
                AND lp.loan_participant_type = 'COBORROWER'))
                ,'N' ) INTO l_is_having_coborrowers
       FROM DUAL ;

Line: 449


	   SELECT  count(*)
       INTO l_number_of_guarantors
       FROM LNS_PARTICIPANTS  lp
       WHERE lp.loan_id = l_cr_loan_id
       AND lp.loan_participant_type = 'GUARANTOR' ;

Line: 514


	   SELECT NVL ( (SELECT  'Y'
                FROM DUAL
                WHERE EXISTS  ( SELECT null
                FROM LNS_PARTICIPANTS  lp
                WHERE lp.loan_id = l_cr_loan_id
                AND lp.loan_participant_type = 'GUARANTOR'))
                ,'N' ) INTO l_is_having_guarantors
       FROM DUAL ;

Line: 584


     SELECT ( loan.requested_amount * nvl(loan.collateral_percent,0) / 100 ) ,
		loan_currency
     INTO l_req_coll_amount , l_loan_currency
     FROM lns_loan_headers_all loan
     WHERE loan.loan_id = l_cr_loan_id ;

Line: 653


    SELECT (select nvl(sum(laa.pledged_amount),0)
    from lns_asset_assignments laa
    where laa.loan_id = loan.loan_id
    and (laa.end_date_active is null or trunc(laa.end_date_active) > trunc(sysdate))
    and exists (select 1 from lns_assets la where la.asset_id = laa.asset_id)) , loan.loan_currency
    INTO l_total_coll_amount , l_loan_currency
    FROM lns_loan_headers_all loan
    WHERE loan.loan_id = l_cr_loan_id ;

Line: 725


	   SELECT lt.delinquency_threshold_amount , llh.loan_currency
       INTO l_deliquency_amount , l_loan_currency
       FROM lns_terms lt , lns_loan_headers_all llh
       WHERE llh.loan_id = l_cr_loan_id
       AND lt.loan_id = llh.loan_id ;

Line: 790


SELECT sum(VALUATION), CURRENCY_CODE
FROM LNS_ASSETS
WHERE asset_owner_id IN (
            SELECT HZ_PARTY_ID FROM LNS_PARTICIPANTS
            WHERE LOAN_ID = ( select loan_id from lns_participants
		              where loan_id = C_LOAN_ID and hz_party_id = C_PARTY_ID
				and loan_participant_type = 'PRIMARY_BORROWER' )
            AND ( LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER' OR LOAN_PARTICIPANT_TYPE ='COBORROWER' )

            UNION ALL

            SELECT HZ_PARTY_ID FROM LNS_PARTICIPANTS
            WHERE LOAN_ID = C_LOAN_ID
            AND HZ_PARTY_ID = C_PARTY_ID
            AND LOAN_PARTICIPANT_TYPE = 'GUARANTOR'
            )
and (end_date_active is null
or trunc(end_date_active) > trunc(sysdate))
group by CURRENCY_CODE ;

Line: 897


SELECT sum(nvl( (select sum (assign.pledged_amount) from lns_asset_assignments assign where LnsAssets.asset_id = assign.asset_id(+) and (assign.end_date_active is null or trunc(assign.end_date_active) > trunc(sysdate)) )
         ,0) ), CURRENCY_CODE
FROM LNS_ASSETS LnsAssets
WHERE asset_owner_id IN (
            SELECT HZ_PARTY_ID FROM LNS_PARTICIPANTS
            WHERE LOAN_ID = ( select loan_id from lns_participants
			      where loan_id = C_LOAN_ID and hz_party_id = C_PARTY_ID
				and loan_participant_type = 'PRIMARY_BORROWER' )
            AND ( LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER' OR LOAN_PARTICIPANT_TYPE ='COBORROWER' )

            UNION ALL

            SELECT HZ_PARTY_ID FROM LNS_PARTICIPANTS
            WHERE LOAN_ID = C_LOAN_ID
            AND HZ_PARTY_ID = C_PARTY_ID
            AND LOAN_PARTICIPANT_TYPE = 'GUARANTOR'
            )
and (end_date_active is null
or trunc(end_date_active) > trunc(sysdate))
group by CURRENCY_CODE ;

Line: 1005


SELECT sum( nvl(LnsAssets.valuation,0) - nvl( (select sum (assign.pledged_amount) from lns_asset_assignments
assign where LnsAssets.asset_id = assign.asset_id(+) and (assign.end_date_active is null or trunc(assign.end_date_active) > trunc(sysdate)) )
         ,0) ), CURRENCY_CODE
FROM LNS_ASSETS LnsAssets
WHERE asset_owner_id IN (
            SELECT HZ_PARTY_ID FROM LNS_PARTICIPANTS
            WHERE LOAN_ID = ( select loan_id from lns_participants where loan_id = C_LOAN_ID and hz_party_id = C_PARTY_ID and loan_participant_type = 'PRIMARY_BORROWER' )
            AND ( LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER' OR LOAN_PARTICIPANT_TYPE ='COBORROWER' )

            UNION ALL

            SELECT HZ_PARTY_ID FROM LNS_PARTICIPANTS
            WHERE LOAN_ID = C_LOAN_ID
            AND HZ_PARTY_ID = C_PARTY_ID
            AND LOAN_PARTICIPANT_TYPE = 'GUARANTOR'
            )
or trunc(end_date_active) > trunc(sysdate)
group by CURRENCY_CODE ;

Line: 1119


        SELECT count(*)
        INTO l_count_active_loans
        FROM lns_loan_headers_all
        WHERE loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id = l_cr_party_id
                            AND ( loan_participant_type = 'PRIMARY_BORROWER'
                                  OR loan_participant_type = 'COBORROWER')
                          )
        AND loan_status = 'ACTIVE' ;

Line: 1180


SELECT sum(lps.total_principal_balance) , llh.loan_currency
FROM lns_loan_headers_all llh ,
LNS_PAY_SUM_V lps
WHERE llh.loan_id = lps.loan_id
AND llh.loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id = c_party_id
                            AND ( loan_participant_type = 'PRIMARY_BORROWER'
                                  OR loan_participant_type = 'COBORROWER')
                          )
AND llh.loan_status = 'ACTIVE'
GROUP BY loan_currency ;

Line: 1284


        SELECT count(*)
        INTO l_count_pending_loans
        FROM lns_loan_headers_all
        WHERE loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id = l_cr_party_id
                            AND ( loan_participant_type = 'PRIMARY_BORROWER'
                                  OR loan_participant_type = 'COBORROWER')
                          )
        AND loan_status = 'PENDING' ;

Line: 1345


SELECT sum(lps.total_principal_balance) , llh.loan_currency
FROM lns_loan_headers_all llh ,
LNS_PAY_SUM_V  lps
WHERE llh.loan_id = lps.loan_id
AND llh.loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id = c_party_id
                            AND ( loan_participant_type = 'PRIMARY_BORROWER'
                                  OR loan_participant_type = 'COBORROWER')
                          )
AND llh.loan_status = 'PENDING'
GROUP BY loan_currency ;

Line: 1448


        SELECT count(*)
        INTO l_count_delinquent_loans
        FROM lns_loan_headers_all
        WHERE loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id = l_cr_party_id
                            AND ( loan_participant_type = 'PRIMARY_BORROWER'
                                  OR loan_participant_type = 'COBORROWER')
                          )
        AND loan_status = 'DELINQUENT' ;

Line: 1509


SELECT sum(lps.total_principal_balance) , llh.loan_currency
FROM lns_loan_headers_all llh ,
LNS_PAY_SUM_V  lps
WHERE llh.loan_id = lps.loan_id
AND llh.loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id = c_party_id
                            AND ( loan_participant_type = 'PRIMARY_BORROWER'
                                  OR loan_participant_type = 'COBORROWER')
                          )
AND llh.loan_status = 'DELINQUENT'
GROUP BY loan_currency ;

Line: 1613


        SELECT count(*)
        INTO l_count_default_loans
        FROM lns_loan_headers_all
        WHERE loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id = l_cr_party_id
                            AND ( loan_participant_type = 'PRIMARY_BORROWER'
                                  OR loan_participant_type = 'COBORROWER')
                          )
        AND loan_status = 'DEFAULT' ;

Line: 1673


SELECT sum(lps.total_principal_balance) , llh.loan_currency
FROM lns_loan_headers_all llh ,
LNS_PAY_SUM_V lps
WHERE llh.loan_id = lps.loan_id
AND llh.loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id = c_party_id
                            AND ( loan_participant_type = 'PRIMARY_BORROWER'
                                  OR loan_participant_type = 'COBORROWER')
                          )
AND llh.loan_status = 'DEFAULT'
GROUP BY loan_currency ;

Line: 1776


        SELECT count(*)
        INTO l_count_paidoff_loans
        FROM lns_loan_headers_all
        WHERE loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id = l_cr_party_id
                            AND ( loan_participant_type = 'PRIMARY_BORROWER'
                                  OR loan_participant_type = 'COBORROWER')
                          )
        AND loan_status = 'PAIDOFF' ;

Line: 1836


SELECT sum(lps.total_principal_balance) , llh.loan_currency
FROM lns_loan_headers_all llh ,
LNS_PAY_SUM_V lps
WHERE llh.loan_id = lps.loan_id
AND llh.loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id = c_party_id
                            AND ( loan_participant_type = 'PRIMARY_BORROWER'
                                  OR loan_participant_type = 'COBORROWER')
                          )
AND llh.loan_status = 'PAIDOFF'
GROUP BY loan_currency ;

Line: 1945


        SELECT count(*)
        INTO l_total_active_loans
        FROM lns_loan_headers_all
        WHERE loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id IN ( SELECT hz_party_id
                                                   FROM lns_participants
                                                   WHERE loan_id = l_cr_loan_id
                                                   AND (loan_participant_type = 'PRIMARY_BORROWER' or loan_participant_type = 'COBORROWER' )
                                                   AND EXISTS ( select null from lns_participants
                                                                where loan_id = l_cr_loan_id
                                                                and hz_party_id = l_cr_party_id
                                                                and loan_participant_type = 'PRIMARY_BORROWER' )

                                                   UNION ALL

                                                   SELECT hz_party_id
                                                   FROM lns_participants
                                                   WHERE loan_id = l_cr_loan_id
                                                   AND hz_party_id = l_cr_party_id
                                                   AND loan_participant_type = 'GUARANTOR'
                                                  )
                                AND ( loan_participant_type = 'PRIMARY_BORROWER'  OR loan_participant_type = 'COBORROWER' )
                            )
                AND loan_status = 'ACTIVE' ;

Line: 2027


SELECT sum( nvl(lps.total_principal_balance,0) ) , llh.loan_currency
FROM lns_loan_headers_all llh ,
LNS_PAY_SUM_V lps
WHERE llh.loan_id = lps.loan_id
AND llh.loan_id IN (  SELECT loan_id
                    FROM lns_participants
                    WHERE hz_party_id IN ( SELECT hz_party_id
                                           FROM lns_participants
                                           WHERE loan_id = c_loan_id
                                           AND (loan_participant_type = 'PRIMARY_BORROWER' or loan_participant_type = 'COBORROWER' )
                                           AND EXISTS ( select null from lns_participants where loan_id = c_loan_id and 							hz_party_id = c_party_id and loan_participant_type = 'PRIMARY_BORROWER' )

                                           UNION ALL

                                           SELECT hz_party_id
                                           FROM lns_participants
                                           WHERE loan_id = c_loan_id
                                           AND hz_party_id = c_party_id
                                           AND loan_participant_type = 'GUARANTOR'
					)

                    AND ( loan_participant_type = 'PRIMARY_BORROWER'  OR loan_participant_type = 'COBORROWER' )
                    )
AND llh.loan_status = 'ACTIVE'
GROUP BY loan_currency ;

Line: 2148


        SELECT count(*)
        INTO l_total_deliquent_loans
        FROM lns_loan_headers_all
        WHERE loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id IN ( SELECT hz_party_id
                                                   FROM lns_participants
                                                   WHERE loan_id = l_cr_loan_id
                                                   AND (loan_participant_type = 'PRIMARY_BORROWER' or loan_participant_type = 'COBORROWER' )
                                                   AND EXISTS ( select null from lns_participants
                                                                where loan_id = l_cr_loan_id
                                                                and hz_party_id = l_cr_party_id
                                                                and loan_participant_type = 'PRIMARY_BORROWER' )

                                                   UNION ALL

                                                   SELECT hz_party_id
                                                   FROM lns_participants
                                                   WHERE loan_id = l_cr_loan_id
                                                   AND hz_party_id = l_cr_party_id
                                                   AND loan_participant_type = 'GUARANTOR'
                                                  )
                                AND ( loan_participant_type = 'PRIMARY_BORROWER'  OR loan_participant_type = 'COBORROWER' )
                            )
                AND loan_status = 'DELINQUENT' ;

Line: 2228


SELECT sum( nvl(lps.total_overdue ,0) ) , llh.loan_currency
FROM lns_loan_headers_all llh ,
LNS_PAY_SUM_OVERDUE_V  lps
WHERE llh.loan_id = lps.loan_id
AND llh.loan_id IN (  SELECT loan_id
                    FROM lns_participants
                    WHERE hz_party_id IN ( SELECT hz_party_id
                                           FROM lns_participants
                                           WHERE loan_id = c_loan_id
                                           AND (loan_participant_type = 'PRIMARY_BORROWER' or loan_participant_type = 'COBORROWER' )
                                           AND EXISTS ( select null from lns_participants where loan_id = c_loan_id and 							hz_party_id = c_party_id and loan_participant_type = 'PRIMARY_BORROWER' )

                                           UNION ALL

                                           SELECT hz_party_id
                                           FROM lns_participants
                                           WHERE loan_id = c_loan_id
                                           AND hz_party_id = c_party_id
                                           AND loan_participant_type = 'GUARANTOR'
					)

                    AND ( loan_participant_type = 'PRIMARY_BORROWER'  OR loan_participant_type = 'COBORROWER' )
                    )
AND llh.loan_status = 'ACTIVE'
GROUP BY loan_currency ;

Line: 2350


        SELECT count(*)
        INTO l_total_default_loans
        FROM lns_loan_headers_all
        WHERE loan_id IN (  SELECT loan_id
                            FROM lns_participants
                            WHERE hz_party_id IN ( SELECT hz_party_id
                                                   FROM lns_participants
                                                   WHERE loan_id = l_cr_loan_id
                                                   AND (loan_participant_type = 'PRIMARY_BORROWER' or loan_participant_type = 'COBORROWER' )
                                                   AND EXISTS ( select null from lns_participants
                                                                where loan_id = l_cr_loan_id
                                                                and hz_party_id = l_cr_party_id
                                                                and loan_participant_type = 'PRIMARY_BORROWER' )

                                                   UNION ALL

                                                   SELECT hz_party_id
                                                   FROM lns_participants
                                                   WHERE loan_id = l_cr_loan_id
                                                   AND hz_party_id = l_cr_party_id
                                                   AND loan_participant_type = 'GUARANTOR'
                                                  )
                                AND ( loan_participant_type = 'PRIMARY_BORROWER'  OR loan_participant_type = 'COBORROWER' )
                            )
                AND loan_status = 'DEFAULT' ;