VIEW: APPS.LNS_LOAN_INFO

Source

View Text - Preformatted

SELECT trx.loan_id ,pty.party_name ,ca.account_number ,trx.loan_number ,trx.loan_start_date ,trx.funded_amount ,lns_utility_pub.get_lookup_meaning('LOAN_STATUS' ,trx.loan_status) ,lns_utility_pub.get_lookup_meaning('LOAN_CLASS', trx.loan_class_code) ,typ.loan_type_name ,-1 ,null STATUS ,null disbursement_number ,null payment_request_date ,-1 loan_amount_adj_id ,null loan_adjustment_description ,-1 loan_line_id ,null reference_description FROM lns_loan_headers_all trx ,lns_loan_types_vl typ ,hz_cust_accounts ca ,hz_parties pty WHERE trx.cust_account_id = ca.cust_account_id AND ca.party_id = pty.party_id AND trx.loan_type_id = typ.loan_type_id UNION ALL SELECT trx.loan_id ,pty.party_name ,ca.account_number ,trx.loan_number ,trx.loan_start_date ,trx.funded_amount ,lns_utility_pub.get_lookup_meaning('LOAN_STATUS', trx.loan_status) , lns_utility_pub.get_lookup_meaning('LOAN_CLASS', trx.loan_class_code) , typ.loan_type_name, nvl(disb.DISB_HEADER_ID, -1) , lns_utility_pub.get_lookup_meaning('FUNDING_STATUS',disb.STATUS) , disb.disbursement_number , disb.payment_request_date , -1 loan_amount_adj_id , null loan_adjustment_description , -1 loan_line_id , null reference_description FROM lns_loan_headers_all trx ,lns_loan_types_vl typ ,lns_disb_headers disb ,hz_cust_accounts ca ,hz_parties pty WHERE trx.cust_account_id = ca.cust_account_id AND ca.party_id = pty.party_id AND trx.loan_type_id = typ.loan_type_id AND trx.loan_id = disb.loan_id UNION ALL SELECT trx.loan_id ,pty.party_name ,ca.account_number ,trx.loan_number ,trx.loan_start_date ,trx.funded_amount ,lns_utility_pub.get_lookup_meaning('LOAN_STATUS', trx.loan_status) ,lns_utility_pub.get_lookup_meaning('LOAN_CLASS', trx.loan_class_code) ,typ.loan_type_name ,-1 DISB_HEADER_ID ,null STATUS ,null disbursement_number ,null payment_request_date ,ladj.loan_amount_adj_id ,ladj.description loan_adjustment_description ,-1 loan_line_id ,null reference_description FROM lns_loan_headers_all trx ,lns_loan_types_vl typ ,lns_loan_amount_adjs ladj ,hz_cust_accounts ca ,hz_parties pty WHERE trx.cust_account_id = ca.cust_account_id AND ca.party_id = pty.party_id AND trx.loan_type_id = typ.loan_type_id AND trx.loan_id = ladj.loan_id UNION ALL SELECT trx.loan_id ,pty.party_name ,ca.account_number ,trx.loan_number ,trx.loan_start_date ,trx.funded_amount ,lns_utility_pub.get_lookup_meaning('LOAN_STATUS', trx.loan_status) ,lns_utility_pub.get_lookup_meaning('LOAN_CLASS', trx.loan_class_code) ,typ.loan_type_name ,-1 DISB_HEADER_ID ,null STATUS ,null disbursement_number ,null payment_request_date ,-1 loan_amount_adj_id ,null loan_adjustment_description ,lline.loan_line_id ,lline.reference_description FROM lns_loan_headers_all trx ,lns_loan_types_vl typ ,lns_loan_lines lline ,hz_cust_accounts ca ,hz_parties pty WHERE trx.cust_account_id = ca.cust_account_id AND ca.party_id = pty.party_id AND trx.loan_type_id = typ.loan_type_id AND trx.loan_id = lline.loan_id AND NVL(lline.original_flag, 'Y') = 'N'

View Text - HTML Formatted

SELECT TRX.LOAN_ID
, PTY.PARTY_NAME
, CA.ACCOUNT_NUMBER
, TRX.LOAN_NUMBER
, TRX.LOAN_START_DATE
, TRX.FUNDED_AMOUNT
, LNS_UTILITY_PUB.GET_LOOKUP_MEANING('LOAN_STATUS'
, TRX.LOAN_STATUS)
, LNS_UTILITY_PUB.GET_LOOKUP_MEANING('LOAN_CLASS'
, TRX.LOAN_CLASS_CODE)
, TYP.LOAN_TYPE_NAME
, -1
, NULL STATUS
, NULL DISBURSEMENT_NUMBER
, NULL PAYMENT_REQUEST_DATE
, -1 LOAN_AMOUNT_ADJ_ID
, NULL LOAN_ADJUSTMENT_DESCRIPTION
, -1 LOAN_LINE_ID
, NULL REFERENCE_DESCRIPTION
FROM LNS_LOAN_HEADERS_ALL TRX
, LNS_LOAN_TYPES_VL TYP
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PTY
WHERE TRX.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID
AND CA.PARTY_ID = PTY.PARTY_ID
AND TRX.LOAN_TYPE_ID = TYP.LOAN_TYPE_ID UNION ALL SELECT TRX.LOAN_ID
, PTY.PARTY_NAME
, CA.ACCOUNT_NUMBER
, TRX.LOAN_NUMBER
, TRX.LOAN_START_DATE
, TRX.FUNDED_AMOUNT
, LNS_UTILITY_PUB.GET_LOOKUP_MEANING('LOAN_STATUS'
, TRX.LOAN_STATUS)
, LNS_UTILITY_PUB.GET_LOOKUP_MEANING('LOAN_CLASS'
, TRX.LOAN_CLASS_CODE)
, TYP.LOAN_TYPE_NAME
, NVL(DISB.DISB_HEADER_ID
, -1)
, LNS_UTILITY_PUB.GET_LOOKUP_MEANING('FUNDING_STATUS'
, DISB.STATUS)
, DISB.DISBURSEMENT_NUMBER
, DISB.PAYMENT_REQUEST_DATE
, -1 LOAN_AMOUNT_ADJ_ID
, NULL LOAN_ADJUSTMENT_DESCRIPTION
, -1 LOAN_LINE_ID
, NULL REFERENCE_DESCRIPTION
FROM LNS_LOAN_HEADERS_ALL TRX
, LNS_LOAN_TYPES_VL TYP
, LNS_DISB_HEADERS DISB
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PTY
WHERE TRX.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID
AND CA.PARTY_ID = PTY.PARTY_ID
AND TRX.LOAN_TYPE_ID = TYP.LOAN_TYPE_ID
AND TRX.LOAN_ID = DISB.LOAN_ID UNION ALL SELECT TRX.LOAN_ID
, PTY.PARTY_NAME
, CA.ACCOUNT_NUMBER
, TRX.LOAN_NUMBER
, TRX.LOAN_START_DATE
, TRX.FUNDED_AMOUNT
, LNS_UTILITY_PUB.GET_LOOKUP_MEANING('LOAN_STATUS'
, TRX.LOAN_STATUS)
, LNS_UTILITY_PUB.GET_LOOKUP_MEANING('LOAN_CLASS'
, TRX.LOAN_CLASS_CODE)
, TYP.LOAN_TYPE_NAME
, -1 DISB_HEADER_ID
, NULL STATUS
, NULL DISBURSEMENT_NUMBER
, NULL PAYMENT_REQUEST_DATE
, LADJ.LOAN_AMOUNT_ADJ_ID
, LADJ.DESCRIPTION LOAN_ADJUSTMENT_DESCRIPTION
, -1 LOAN_LINE_ID
, NULL REFERENCE_DESCRIPTION
FROM LNS_LOAN_HEADERS_ALL TRX
, LNS_LOAN_TYPES_VL TYP
, LNS_LOAN_AMOUNT_ADJS LADJ
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PTY
WHERE TRX.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID
AND CA.PARTY_ID = PTY.PARTY_ID
AND TRX.LOAN_TYPE_ID = TYP.LOAN_TYPE_ID
AND TRX.LOAN_ID = LADJ.LOAN_ID UNION ALL SELECT TRX.LOAN_ID
, PTY.PARTY_NAME
, CA.ACCOUNT_NUMBER
, TRX.LOAN_NUMBER
, TRX.LOAN_START_DATE
, TRX.FUNDED_AMOUNT
, LNS_UTILITY_PUB.GET_LOOKUP_MEANING('LOAN_STATUS'
, TRX.LOAN_STATUS)
, LNS_UTILITY_PUB.GET_LOOKUP_MEANING('LOAN_CLASS'
, TRX.LOAN_CLASS_CODE)
, TYP.LOAN_TYPE_NAME
, -1 DISB_HEADER_ID
, NULL STATUS
, NULL DISBURSEMENT_NUMBER
, NULL PAYMENT_REQUEST_DATE
, -1 LOAN_AMOUNT_ADJ_ID
, NULL LOAN_ADJUSTMENT_DESCRIPTION
, LLINE.LOAN_LINE_ID
, LLINE.REFERENCE_DESCRIPTION
FROM LNS_LOAN_HEADERS_ALL TRX
, LNS_LOAN_TYPES_VL TYP
, LNS_LOAN_LINES LLINE
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PTY
WHERE TRX.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID
AND CA.PARTY_ID = PTY.PARTY_ID
AND TRX.LOAN_TYPE_ID = TYP.LOAN_TYPE_ID
AND TRX.LOAN_ID = LLINE.LOAN_ID
AND NVL(LLINE.ORIGINAL_FLAG
, 'Y') = 'N'