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.

VIEW: APPS.LNS_LOAN_DETAILS_EXT_V

Source

View Text - Preformatted


SELECT XLE.Event_id Event_id ,XLEE.LEDGER_ID ,lnd.DISTRIBUTION_ID ,lnd.DISTRIBUTION_ID ,lnd.LOAN_ID ,lnd.LINE_TYPE ,lnd.ACCOUNT_NAME ,lnd.CODE_COMBINATION_ID ,lnd.ACCOUNT_TYPE ,lnd.DISTRIBUTION_PERCENT ,lnd.DISTRIBUTION_AMOUNT ENTERED_AMOUNT ,lnd.START_DATE_ACTIVE ,lnd.END_DATE_ACTIVE ,lnd.CREATED_BY ,lnd.CREATION_DATE ,lnd.LAST_UPDATED_BY ,lnd.LAST_UPDATE_DATE ,lnd.OBJECT_VERSION_NUMBER ,lnd.ATTRIBUTE_CATEGORY ,lnd.ATTRIBUTE1 ,lnd.ATTRIBUTE2 ,lnd.ATTRIBUTE3 ,lnd.ATTRIBUTE4 ,lnd.ATTRIBUTE5 ,lnd.ATTRIBUTE6 ,lnd.ATTRIBUTE7 ,lnd.ATTRIBUTE8 ,lnd.ATTRIBUTE9 ,lnd.ATTRIBUTE10 ,lnd.ATTRIBUTE11 ,lnd.ATTRIBUTE12 ,lnd.ATTRIBUTE13 ,lnd.ATTRIBUTE14 ,lnd.ATTRIBUTE15 ,lnd.ATTRIBUTE16 ,lnd.ATTRIBUTE17 ,lnd.ATTRIBUTE18 ,lnd.ATTRIBUTE19 ,lnd.ATTRIBUTE20 ,lnd.DISTRIBUTION_TYPE ,lnd.LAST_UPDATE_LOGIN ,lnd.USSGL_TRANSACTION_CODE ,lnd.FEE_ID ,nvl(lndh.disb_header_id, -1) DISB_HEADER_ID ,lndh.status ,lndh.header_amount ,lndh.disbursement_number ,xle.event_type_code ,DECODE(lns_utility_pub.IS_FED_FIN_ENABLED(lnh.org_id), 'N', 'X' , 'Y', DECODE(lns_distributions_pub.getFlexSegmentNumber('GL#', 101, 'GL_BALANCING'), 1, gl.segment1, 2, gl.segment2, 3, gl.segment3, 4, gl.segment4, 5, gl.segment5, 6, gl.segment6, 7, gl.segment7, 8, gl.segment8, 9, gl.segment9, 10, gl.segment10, 11, gl.segment11, 12, gl.segment12, 13, gl.segment13, 14, gl.segment14, 15, gl.segment15, 16, gl.segment16, 17, gl.segment17, 18, gl.segment18, 19, gl.segment19, 20, gl.segment20, 21, gl.segment21, 22, gl.segment22, 23, gl.segment23, 24, gl.segment24, 25, gl.segment25, 26, gl.segment26, 27, gl.segment27, 28, gl.segment28, 29, gl.segment29, 30, gl.segment30)) ,CASE WHEN (xle.event_type_code IN ('FUTURE_DISBURSEMENT_CANCELLED', 'DIRECT_LOAN_APPROVED')) THEN (SELECT fvp2.fund_value FROM fv_fund_parameters fvp2 , fv_fund_parameters fvp1 WHERE fvp1.fund_value = DECODE(lns_utility_pub.IS_FED_FIN_ENABLED(lnh.org_id), 'N', 'X' , 'Y', DECODE(lns_distributions_pub.getFlexSegmentNumber('GL#', 101, 'GL_BALANCING'), 1, gl.segment1, 2, gl.segment2, 3, gl.segment3, 4, gl.segment4, 5, gl.segment5, 6, gl.segment6, 7, gl.segment7, 8, gl.segment8, 9, gl.segment9, 10, gl.segment10, 11, gl.segment11, 12, gl.segment12, 13, gl.segment13, 14, gl.segment14, 15, gl.segment15, 16, gl.segment16, 17, gl.segment17, 18, gl.segment18, 19, gl.segment19, 20, gl.segment20, 21, gl.segment21, 22, gl.segment22, 23, gl.segment23, 24, gl.segment24, 25, gl.segment25, 26, gl.segment26, 27, gl.segment27, 28, gl.segment28, 29, gl.segment29, 30, gl.segment30)) AND fvp1.mapped_to_fund_parameter_id = fvp2.fund_parameter_id ) ELSE NULL END ,nvl(ladj.loan_amount_adj_id, -1) loan_amount_adj_id ,ladj.description adjustment_description ,nvl(lnd.loan_line_id, -1) ,CASE WHEN(lnh.loan_class_code = 'DIRECT' AND xle.event_type_code in ('DISBURSEMENT_FUNDED', 'FUTURE_DISBURSEMENT_CANCELLED', 'DIRECT_LOAN_ADJ_REVERSED') AND lns_utility_pub.IS_ENCUM_FIN_ENABLED(lnh.org_id) = 'Y' AND lnd.line_type <> 'SUBSIDY' AND lnd.distribution_type = 'ORIGINATION') THEN (select ldist.distribution_id from lns_distributions ldist where ldist.loan_id = lnh.loan_id and ldist.account_type = 'DR' and ldist.line_type <> 'SUBSIDY' and ldist.activity = 'LNS_APPROVAL' and ldist.distribution_percent = lnd.distribution_percent and rownum <= 1 ) ELSE lnd.DISTRIBUTION_ID END ENCUM_PARENT_DIST_ID ,CASE WHEN(lnh.loan_class_code = 'DIRECT' AND xle.event_type_code in ('DISBURSEMENT_FUNDED', 'FUTURE_DISBURSEMENT_CANCELLED', 'DIRECT_LOAN_ADJ_REVERSED') AND lns_utility_pub.IS_ENCUM_FIN_ENABLED(lnh.org_id) = 'Y' AND lnd.line_type <> 'SUBSIDY' AND lnd.distribution_type = 'ORIGINATION') THEN (select ldist.distribution_amount from lns_distributions ldist where ldist.loan_id = lnh.loan_id and ldist.account_type = 'DR' and ldist.line_type <> 'SUBSIDY' and ldist.activity = 'LNS_APPROVAL' and ldist.distribution_percent = lnd.distribution_percent and rownum <= 1 ) ELSE lnd.DISTRIBUTION_AMOUNT END ENCUM_PARENT_DIST_AMOUNT , -1 ORIG_ENCUM_SYS_TRX_ID FROM LNS_LOAN_HEADERS_ALL LNH , LNS_DISTRIBUTIONS LND , LNS_DISB_HEADERS LNDH , XLA_EVENTS XLE , XLA_TRANSACTION_ENTITIES XLEE , gl_code_combinations gl , LNS_LOAN_AMOUNT_ADJS ladj WHERE XLE.application_id = 206 AND XLE.entity_id = xlee.entity_id AND xle.process_Status_code <> 'P' AND XLEE.source_id_int_1 = lnh.loan_id AND XLEE.source_id_int_2 = DECODE(XLEE.source_id_int_2, -1, -1, lndh.disb_header_id) AND XLEE.source_id_int_3 = DECODE(XLEE.source_id_int_3, -1, -1, ladj.loan_amount_adj_id) AND xle.event_id = lnd.event_id AND lnd.code_combination_id = gl.code_combination_id(+) AND lnh.loan_id = lnd.loan_id AND lnd.disb_header_id = lndh.disb_header_id (+) AND lnd.loan_amount_adj_id = ladj.loan_amount_adj_id (+)

View Text - HTML Formatted

SELECT XLE.EVENT_ID EVENT_ID
, XLEE.LEDGER_ID
, LND.DISTRIBUTION_ID
, LND.DISTRIBUTION_ID
, LND.LOAN_ID
, LND.LINE_TYPE
, LND.ACCOUNT_NAME
, LND.CODE_COMBINATION_ID
, LND.ACCOUNT_TYPE
, LND.DISTRIBUTION_PERCENT
, LND.DISTRIBUTION_AMOUNT ENTERED_AMOUNT
, LND.START_DATE_ACTIVE
, LND.END_DATE_ACTIVE
, LND.CREATED_BY
, LND.CREATION_DATE
, LND.LAST_UPDATED_BY
, LND.LAST_UPDATE_DATE
, LND.OBJECT_VERSION_NUMBER
, LND.ATTRIBUTE_CATEGORY
, LND.ATTRIBUTE1
, LND.ATTRIBUTE2
, LND.ATTRIBUTE3
, LND.ATTRIBUTE4
, LND.ATTRIBUTE5
, LND.ATTRIBUTE6
, LND.ATTRIBUTE7
, LND.ATTRIBUTE8
, LND.ATTRIBUTE9
, LND.ATTRIBUTE10
, LND.ATTRIBUTE11
, LND.ATTRIBUTE12
, LND.ATTRIBUTE13
, LND.ATTRIBUTE14
, LND.ATTRIBUTE15
, LND.ATTRIBUTE16
, LND.ATTRIBUTE17
, LND.ATTRIBUTE18
, LND.ATTRIBUTE19
, LND.ATTRIBUTE20
, LND.DISTRIBUTION_TYPE
, LND.LAST_UPDATE_LOGIN
, LND.USSGL_TRANSACTION_CODE
, LND.FEE_ID
, NVL(LNDH.DISB_HEADER_ID
, -1) DISB_HEADER_ID
, LNDH.STATUS
, LNDH.HEADER_AMOUNT
, LNDH.DISBURSEMENT_NUMBER
, XLE.EVENT_TYPE_CODE
, DECODE(LNS_UTILITY_PUB.IS_FED_FIN_ENABLED(LNH.ORG_ID)
, 'N'
, 'X'
, 'Y'
, DECODE(LNS_DISTRIBUTIONS_PUB.GETFLEXSEGMENTNUMBER('GL#'
, 101
, 'GL_BALANCING')
, 1
, GL.SEGMENT1
, 2
, GL.SEGMENT2
, 3
, GL.SEGMENT3
, 4
, GL.SEGMENT4
, 5
, GL.SEGMENT5
, 6
, GL.SEGMENT6
, 7
, GL.SEGMENT7
, 8
, GL.SEGMENT8
, 9
, GL.SEGMENT9
, 10
, GL.SEGMENT10
, 11
, GL.SEGMENT11
, 12
, GL.SEGMENT12
, 13
, GL.SEGMENT13
, 14
, GL.SEGMENT14
, 15
, GL.SEGMENT15
, 16
, GL.SEGMENT16
, 17
, GL.SEGMENT17
, 18
, GL.SEGMENT18
, 19
, GL.SEGMENT19
, 20
, GL.SEGMENT20
, 21
, GL.SEGMENT21
, 22
, GL.SEGMENT22
, 23
, GL.SEGMENT23
, 24
, GL.SEGMENT24
, 25
, GL.SEGMENT25
, 26
, GL.SEGMENT26
, 27
, GL.SEGMENT27
, 28
, GL.SEGMENT28
, 29
, GL.SEGMENT29
, 30
, GL.SEGMENT30))
, CASE WHEN (XLE.EVENT_TYPE_CODE IN ('FUTURE_DISBURSEMENT_CANCELLED'
, 'DIRECT_LOAN_APPROVED')) THEN (SELECT FVP2.FUND_VALUE
FROM FV_FUND_PARAMETERS FVP2
, FV_FUND_PARAMETERS FVP1
WHERE FVP1.FUND_VALUE = DECODE(LNS_UTILITY_PUB.IS_FED_FIN_ENABLED(LNH.ORG_ID)
, 'N'
, 'X'
, 'Y'
, DECODE(LNS_DISTRIBUTIONS_PUB.GETFLEXSEGMENTNUMBER('GL#'
, 101
, 'GL_BALANCING')
, 1
, GL.SEGMENT1
, 2
, GL.SEGMENT2
, 3
, GL.SEGMENT3
, 4
, GL.SEGMENT4
, 5
, GL.SEGMENT5
, 6
, GL.SEGMENT6
, 7
, GL.SEGMENT7
, 8
, GL.SEGMENT8
, 9
, GL.SEGMENT9
, 10
, GL.SEGMENT10
, 11
, GL.SEGMENT11
, 12
, GL.SEGMENT12
, 13
, GL.SEGMENT13
, 14
, GL.SEGMENT14
, 15
, GL.SEGMENT15
, 16
, GL.SEGMENT16
, 17
, GL.SEGMENT17
, 18
, GL.SEGMENT18
, 19
, GL.SEGMENT19
, 20
, GL.SEGMENT20
, 21
, GL.SEGMENT21
, 22
, GL.SEGMENT22
, 23
, GL.SEGMENT23
, 24
, GL.SEGMENT24
, 25
, GL.SEGMENT25
, 26
, GL.SEGMENT26
, 27
, GL.SEGMENT27
, 28
, GL.SEGMENT28
, 29
, GL.SEGMENT29
, 30
, GL.SEGMENT30))
AND FVP1.MAPPED_TO_FUND_PARAMETER_ID = FVP2.FUND_PARAMETER_ID ) ELSE NULL END
, NVL(LADJ.LOAN_AMOUNT_ADJ_ID
, -1) LOAN_AMOUNT_ADJ_ID
, LADJ.DESCRIPTION ADJUSTMENT_DESCRIPTION
, NVL(LND.LOAN_LINE_ID
, -1)
, CASE WHEN(LNH.LOAN_CLASS_CODE = 'DIRECT'
AND XLE.EVENT_TYPE_CODE IN ('DISBURSEMENT_FUNDED'
, 'FUTURE_DISBURSEMENT_CANCELLED'
, 'DIRECT_LOAN_ADJ_REVERSED')
AND LNS_UTILITY_PUB.IS_ENCUM_FIN_ENABLED(LNH.ORG_ID) = 'Y'
AND LND.LINE_TYPE <> 'SUBSIDY'
AND LND.DISTRIBUTION_TYPE = 'ORIGINATION') THEN (SELECT LDIST.DISTRIBUTION_ID
FROM LNS_DISTRIBUTIONS LDIST
WHERE LDIST.LOAN_ID = LNH.LOAN_ID
AND LDIST.ACCOUNT_TYPE = 'DR'
AND LDIST.LINE_TYPE <> 'SUBSIDY'
AND LDIST.ACTIVITY = 'LNS_APPROVAL'
AND LDIST.DISTRIBUTION_PERCENT = LND.DISTRIBUTION_PERCENT
AND ROWNUM <= 1 ) ELSE LND.DISTRIBUTION_ID END ENCUM_PARENT_DIST_ID
, CASE WHEN(LNH.LOAN_CLASS_CODE = 'DIRECT'
AND XLE.EVENT_TYPE_CODE IN ('DISBURSEMENT_FUNDED'
, 'FUTURE_DISBURSEMENT_CANCELLED'
, 'DIRECT_LOAN_ADJ_REVERSED')
AND LNS_UTILITY_PUB.IS_ENCUM_FIN_ENABLED(LNH.ORG_ID) = 'Y'
AND LND.LINE_TYPE <> 'SUBSIDY'
AND LND.DISTRIBUTION_TYPE = 'ORIGINATION') THEN (SELECT LDIST.DISTRIBUTION_AMOUNT
FROM LNS_DISTRIBUTIONS LDIST
WHERE LDIST.LOAN_ID = LNH.LOAN_ID
AND LDIST.ACCOUNT_TYPE = 'DR'
AND LDIST.LINE_TYPE <> 'SUBSIDY'
AND LDIST.ACTIVITY = 'LNS_APPROVAL'
AND LDIST.DISTRIBUTION_PERCENT = LND.DISTRIBUTION_PERCENT
AND ROWNUM <= 1 ) ELSE LND.DISTRIBUTION_AMOUNT END ENCUM_PARENT_DIST_AMOUNT
, -1 ORIG_ENCUM_SYS_TRX_ID
FROM LNS_LOAN_HEADERS_ALL LNH
, LNS_DISTRIBUTIONS LND
, LNS_DISB_HEADERS LNDH
, XLA_EVENTS XLE
, XLA_TRANSACTION_ENTITIES XLEE
, GL_CODE_COMBINATIONS GL
, LNS_LOAN_AMOUNT_ADJS LADJ
WHERE XLE.APPLICATION_ID = 206
AND XLE.ENTITY_ID = XLEE.ENTITY_ID
AND XLE.PROCESS_STATUS_CODE <> 'P'
AND XLEE.SOURCE_ID_INT_1 = LNH.LOAN_ID
AND XLEE.SOURCE_ID_INT_2 = DECODE(XLEE.SOURCE_ID_INT_2
, -1
, -1
, LNDH.DISB_HEADER_ID)
AND XLEE.SOURCE_ID_INT_3 = DECODE(XLEE.SOURCE_ID_INT_3
, -1
, -1
, LADJ.LOAN_AMOUNT_ADJ_ID)
AND XLE.EVENT_ID = LND.EVENT_ID
AND LND.CODE_COMBINATION_ID = GL.CODE_COMBINATION_ID(+)
AND LNH.LOAN_ID = LND.LOAN_ID
AND LND.DISB_HEADER_ID = LNDH.DISB_HEADER_ID (+)
AND LND.LOAN_AMOUNT_ADJ_ID = LADJ.LOAN_AMOUNT_ADJ_ID (+)