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.
[Home] [Help]
MATERIALIZED VIEW: APPS.LNS_LOAN_DTLS_ALL_MV

Source


SELECT LnsLoanHeaderEO.LOAN_ID,
       LnsLoanHeaderEO.LOAN_NUMBER,
       LnsLoanHeaderEO.LOAN_DESCRIPTION,
       (CASE WHEN LnsLoanHeaderEO.loan_status in ('INCOMPLETE','REJECTED','DELETED','PENDING','APPROVED','IN_FUNDING','FUNDING_ERROR','CANCELLED') OR LnsLoanHeaderEO.FUNDED_AMOUNT = 0 THEN LnsLoanHeaderEO.requested_amount
        ELSE
			LnsLoanHeaderEO.FUNDED_AMOUNT -
			(select nvl(SUM(psa_prin.AMOUNT_APPLIED), 0)
			from
			LNS_AMORTIZATION_SCHEDS am
			,ar_payment_schedules_all psa_prin
			where
			am.loan_id = LnsLoanHeaderEO.loan_id
			and am.PHASE = LnsLoanHeaderEO.CURRENT_PHASE
			and am.PAYMENT_NUMBER <= LnsLoanHeaderEO.LAST_PAYMENT_NUMBER
			and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
			and am.REAMORTIZATION_AMOUNT is null
			and psa_prin.customer_trx_id = am.principal_trx_id)
	END) TOTAL_PRINCIPAL_BALANCE,
       LnsLoanHeaderEO.PRIMARY_BORROWER_ID,
       party.party_name,
DECODE(LnsLoanHeaderEO.secondary_status,null,lnsk.meaning,lnsk.meaning || ': ' || sec_status_lkup.meaning) LOAN_STATUS_MEANING,
       'N' Detail_View_Flag,
       LnsLoanHeaderEO.initial_loan_balance,
       LnsLoanHeaderEO.loan_term || ' ' || lnsk2.meaning term_period,
       --null TOTAL_PRIN_PAID_TODATE,
       --null interest_paid_todate,
       loc.address1 || ' ' || loc.address2 || ' ' || loc.address3 || ' ' || loc.address4 address,
       loc.city || ',' || loc.county city_county,
       loc.state,
       loc.postal_code,
       party.jgzz_fiscal_code tax_reference,
       LnsLoanHeaderEO.product_id,
       ratesch.current_interest_rate  || '%' rate,
       lnsk3.meaning rate_type,
       con_phone.raw_phone_number contact_phone_number,
       'N' SELECT_LOAN_ROW,
       con_party.party_name contact_person_name,
       LnsLoanHeaderEO.loan_type,
       LnsLoanHeaderEO.loan_class_code,
       LnsLoanHeaderEO.creation_date,
       LnsLoanHeaderEO.loan_maturity_date,
       LnsLoanHeaderEO.loan_start_date,
       LnsLoanHeaderEO.loan_currency,
       LnsLoanHeaderEO.requested_amount,
       LnsLoanHeaderEO.last_update_date,
       LnsLoanHeaderEO.end_date,
       decode(LnsLoanHeaderEO.loan_status, 'INCOMPLETE',
'DeleteEnabled', 'DeleteDisabled') AS DELETE_SWITCHER,
	LnsLoanHeaderEO.loan_status,
	lnt.term_id,
	LnsLoanHeaderEO.loan_approval_date,
	lnsk4.meaning loan_class_meaning,
	loan_type.loan_type_name loan_type_meaning,
decode(LnsLoanHeaderEO.loan_status, 'DELETED',
'UpdateDisabled', 'REJECTED', 'UpdateDisabled','UpdateEnabled') AS UPDATE_SWITCHER,
res.resource_id loan_agent_id,
res.source_name loan_agent_name,
--DECODE(res.user_id,FND_GLOBAL.user_id,'Y','N') logged_in_loan_agent_flag,
res.user_id loan_agent_user_id,
LnsLoanHeaderEO.last_update_date loan_max_last_update_date,
fund_advice.payment_request_date funding_advice_submitted_date,
LnsLoanHeaderEO.legal_entity_id,
le.name legal_entity_name,
ou.name operating_unit_name,
LnsLoanHeaderEO.loan_type_id,
LnsLoanHeaderEO.secondary_status,
loan_product.loan_product_name,
lnsk.meaning primary_status_meaning,
sec_status_lkup.meaning secondary_status_meaning,
LnsLoanHeaderEO.org_id,
loan_product.loan_product_id
FROM LNS_LOAN_HEADERS_ALL LnsLoanHeaderEO,
	hz_parties party,
	lns_terms lnt,
	hz_party_sites site,
	hz_locations loc,
	hz_cust_acct_sites_all acct_site,
	lns_lookups lnsk,
	lns_lookups lnsk2,
	lns_lookups lnsk3,
	lns_lookups lnsk4,
	lns_loan_types loan_type,
	hz_parties con_party,
	hz_contact_points con_phone,
	jtf_rs_resource_extns res,
	lns_disb_headers fund_advice,
	lns_rate_schedules ratesch,
	xle_entity_profiles le,
	hr_all_organization_units_tl ou,
	lns_lookups sec_status_lkup,
	lns_loan_products_all loan_product
where party.party_id=LnsLoanHeaderEO.primary_borrower_id
and LnsLoanHeaderEO.loan_id = lnt.loan_id
and LnsLoanHeaderEO.product_id = loan_product.loan_product_id(+)
and LnsLoanHeaderEO.bill_to_acct_site_id = acct_site.cust_acct_site_id
and acct_site.party_site_id = site.party_site_id
and  site.location_id = loc.location_id
and  lnsk.lookup_code=LnsLoanHeaderEO.loan_status
and  lnsk2.lookup_code=LnsLoanHeaderEO.loan_term_period
and  lnsk3.lookup_code=lnt.rate_type
and lnsk4.lookup_code=LnsLoanHeaderEO.loan_class_code
and loan_type.loan_type_id =LnsLoanHeaderEO.loan_type_id
and  lnsk.lookup_type='LOAN_STATUS'
and  lnsk2.lookup_type='PERIOD'
and  lnsk3.lookup_type='RATE_TYPE'
and  lnsk4.lookup_type='LOAN_CLASS'
and  sec_status_lkup.lookup_type(+) = 'SECONDARY_STATUS'
and sec_status_lkup.lookup_code(+) = LnsLoanHeaderEO.secondary_status
and con_party.party_id(+) = LnsLoanHeaderEO.contact_pers_party_id
and con_phone.owner_table_name(+) = 'HZ_PARTIES'
and con_phone.owner_table_id(+) = LnsLoanHeaderEO.contact_rel_party_id
and con_phone.primary_flag(+) = 'Y'
and con_phone.status(+) = 'A'
and con_phone.contact_point_type(+) = 'PHONE'
and LnsLoanHeaderEO.LOAN_ASSIGNED_TO = res.resource_id
and fund_advice.loan_id(+) = LnsLoanHeaderEO.loan_id
and fund_advice.disbursement_number(+) = 1
and le.legal_entity_id = LnsLoanHeaderEO.legal_entity_id
and ou.organization_id = LnsLoanHeaderEO.org_id
and ou.language = userenv('LANG')
and lnt.term_id = ratesch.term_id
and ratesch.end_date_active is null
and ( (ratesch.phase = LnsLoanHeaderEO.current_phase and
(LnsLoanHeaderEO.last_amortization_id is null or
LnsLoanHeaderEO.last_payment_number = 0) and
ratesch.begin_installment_number = 1)
        OR
        (ratesch.phase = LnsLoanHeaderEO.current_phase and
ratesch.rate_id = (select RATE_ID from LNS_AMORTIZATION_SCHEDS where
loan_id = LnsLoanHeaderEO.loan_id and payment_number = LnsLoanHeaderEO.last_payment_number and parent_amortization_id is null and reversed_flag = 'N'))
      )