VIEW: APPS.LNS_LOAN_PARTICIPANTS_V

Source

View Text - Preformatted

SELECT participant.loan_id, party.party_name, participant_type.meaning, party_type.meaning, party.address1 || ' ' || party.city || ' ' || party.state || ' ' || party.postal_code || ' ' || terr.territory_short_name, con_party.party_name, con_phone.raw_phone_number, participant.hz_party_id, participant.participant_id, participant.cust_account_id, participant.bill_to_acct_site_id, participant.start_date_active, participant.end_date_active, participant.attribute_category, participant.attribute1, participant.attribute2, participant.attribute3, participant.attribute4, participant.attribute5, participant.attribute6, participant.attribute7, participant.attribute8, participant.attribute9, participant.attribute10, participant.attribute11, participant.attribute12, participant.attribute13, participant.attribute14, participant.attribute15, participant.attribute16, participant.attribute17, participant.attribute18, participant.attribute19, participant.attribute20, participant.loan_participant_type, party.party_type, participant.contact_pers_party_id, con_phone.contact_point_id, participant.contact_rel_party_id, participant.credit_request_id FROM lns_participants participant, hz_parties party, lns_lookups participant_type, ar_lookups party_type, fnd_territories_vl terr, hz_parties con_party, hz_contact_points con_phone WHERE participant.hz_party_id = party.party_id AND (participant.end_date_active IS NULL OR (sysdate between participant.start_date_active and participant.end_date_active) ) AND party.country = terr.TERRITORY_CODE(+) AND participant.LOAN_PARTICIPANT_TYPE = participant_type.lookup_code AND participant_type.lookup_type = 'LNS_PARTICIPANT_TYPE' AND party.party_type = party_type.lookup_code AND party_type.lookup_type = 'PARTY_TYPE' AND con_party.party_id(+) = participant.contact_pers_party_id AND con_phone.owner_table_name(+) = 'HZ_PARTIES' AND con_phone.owner_table_id(+) = participant.contact_rel_party_id AND con_phone.primary_flag(+) = 'Y' AND con_phone.status(+) = 'A' AND con_phone.contact_point_type(+) = 'PHONE'

View Text - HTML Formatted

SELECT PARTICIPANT.LOAN_ID
, PARTY.PARTY_NAME
, PARTICIPANT_TYPE.MEANING
, PARTY_TYPE.MEANING
, PARTY.ADDRESS1 || ' ' || PARTY.CITY || ' ' || PARTY.STATE || ' ' || PARTY.POSTAL_CODE || ' ' || TERR.TERRITORY_SHORT_NAME
, CON_PARTY.PARTY_NAME
, CON_PHONE.RAW_PHONE_NUMBER
, PARTICIPANT.HZ_PARTY_ID
, PARTICIPANT.PARTICIPANT_ID
, PARTICIPANT.CUST_ACCOUNT_ID
, PARTICIPANT.BILL_TO_ACCT_SITE_ID
, PARTICIPANT.START_DATE_ACTIVE
, PARTICIPANT.END_DATE_ACTIVE
, PARTICIPANT.ATTRIBUTE_CATEGORY
, PARTICIPANT.ATTRIBUTE1
, PARTICIPANT.ATTRIBUTE2
, PARTICIPANT.ATTRIBUTE3
, PARTICIPANT.ATTRIBUTE4
, PARTICIPANT.ATTRIBUTE5
, PARTICIPANT.ATTRIBUTE6
, PARTICIPANT.ATTRIBUTE7
, PARTICIPANT.ATTRIBUTE8
, PARTICIPANT.ATTRIBUTE9
, PARTICIPANT.ATTRIBUTE10
, PARTICIPANT.ATTRIBUTE11
, PARTICIPANT.ATTRIBUTE12
, PARTICIPANT.ATTRIBUTE13
, PARTICIPANT.ATTRIBUTE14
, PARTICIPANT.ATTRIBUTE15
, PARTICIPANT.ATTRIBUTE16
, PARTICIPANT.ATTRIBUTE17
, PARTICIPANT.ATTRIBUTE18
, PARTICIPANT.ATTRIBUTE19
, PARTICIPANT.ATTRIBUTE20
, PARTICIPANT.LOAN_PARTICIPANT_TYPE
, PARTY.PARTY_TYPE
, PARTICIPANT.CONTACT_PERS_PARTY_ID
, CON_PHONE.CONTACT_POINT_ID
, PARTICIPANT.CONTACT_REL_PARTY_ID
, PARTICIPANT.CREDIT_REQUEST_ID
FROM LNS_PARTICIPANTS PARTICIPANT
, HZ_PARTIES PARTY
, LNS_LOOKUPS PARTICIPANT_TYPE
, AR_LOOKUPS PARTY_TYPE
, FND_TERRITORIES_VL TERR
, HZ_PARTIES CON_PARTY
, HZ_CONTACT_POINTS CON_PHONE
WHERE PARTICIPANT.HZ_PARTY_ID = PARTY.PARTY_ID
AND (PARTICIPANT.END_DATE_ACTIVE IS NULL OR (SYSDATE BETWEEN PARTICIPANT.START_DATE_ACTIVE
AND PARTICIPANT.END_DATE_ACTIVE) )
AND PARTY.COUNTRY = TERR.TERRITORY_CODE(+)
AND PARTICIPANT.LOAN_PARTICIPANT_TYPE = PARTICIPANT_TYPE.LOOKUP_CODE
AND PARTICIPANT_TYPE.LOOKUP_TYPE = 'LNS_PARTICIPANT_TYPE'
AND PARTY.PARTY_TYPE = PARTY_TYPE.LOOKUP_CODE
AND PARTY_TYPE.LOOKUP_TYPE = 'PARTY_TYPE'
AND CON_PARTY.PARTY_ID(+) = PARTICIPANT.CONTACT_PERS_PARTY_ID
AND CON_PHONE.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CON_PHONE.OWNER_TABLE_ID(+) = PARTICIPANT.CONTACT_REL_PARTY_ID
AND CON_PHONE.PRIMARY_FLAG(+) = 'Y'
AND CON_PHONE.STATUS(+) = 'A'
AND CON_PHONE.CONTACT_POINT_TYPE(+) = 'PHONE'