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 ,CASE WHEN(lnh.loan_class_code = 'DIRECT' AND xle.event_type_code = 'FUTURE_DISBURSEMENT_CANCELLED' AND lnd.ACCOUNT_TYPE = 'DR') THEN 'CR' WHEN(lnh.loan_class_code = 'DIRECT' AND xle.event_type_code = 'FUTURE_DISBURSEMENT_CANCELLED' AND lnd.ACCOUNT_TYPE = 'CR') THEN 'DR' ELSE lnd.ACCOUNT_TYPE END ,lnd.DISTRIBUTION_PERCENT ,CASE WHEN(lnh.loan_class_code = 'DIRECT' AND xle.event_type_code = 'FUTURE_DISBURSEMENT_CANCELLED' AND lnd.line_type = 'SUBSIDY') THEN (lnh.requested_amount - lnh.funded_amount) * nvl(lnh.subsidy_rate/100, 0) WHEN(lnh.loan_class_code = 'DIRECT' AND xle.event_type_code = 'FUTURE_DISBURSEMENT_CANCELLED' AND lnd.line_type <> 'SUBSIDY') THEN lnh.requested_amount - lnh.funded_amount WHEN (lnh.loan_class_code = 'DIRECT' AND xle.event_type_code = 'DIRECT_LOAN_APPROVED' AND lnd.line_type <> 'SUBSIDY') THEN lnh.requested_amount WHEN (lnh.loan_class_code = 'DIRECT' AND xle.event_type_code = 'DIRECT_LOAN_APPROVED' AND lnd.line_type = 'SUBSIDY') THEN lnh.requested_amount * nvl(lnh.subsidy_rate/100, 0) ELSE lnd.DISTRIBUTION_AMOUNT END 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 ,lndh.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 from LNS_LOAN_HEADERS_ALL LNH ,LNS_DISTRIBUTIONS LND ,LNS_DISB_HEADERS LNDH ,XLA_EVENTS XLE ,XLA_TRANSACTION_ENTITIES XLEE ,gl_code_combinations gl 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 xle.event_id = lnd.event_id and lnd.code_combination_id = gl.code_combination_id(+) and lnh.loan_id = lnd.loan_id and lnd.distribution_type = 'ORIGINATION' and lnh.loan_id = lndh.loan_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
, CASE WHEN(LNH.LOAN_CLASS_CODE = 'DIRECT'
AND XLE.EVENT_TYPE_CODE = 'FUTURE_DISBURSEMENT_CANCELLED'
AND LND.ACCOUNT_TYPE = 'DR') THEN 'CR' WHEN(LNH.LOAN_CLASS_CODE = 'DIRECT'
AND XLE.EVENT_TYPE_CODE = 'FUTURE_DISBURSEMENT_CANCELLED'
AND LND.ACCOUNT_TYPE = 'CR') THEN 'DR' ELSE LND.ACCOUNT_TYPE END
, LND.DISTRIBUTION_PERCENT
, CASE WHEN(LNH.LOAN_CLASS_CODE = 'DIRECT'
AND XLE.EVENT_TYPE_CODE = 'FUTURE_DISBURSEMENT_CANCELLED'
AND LND.LINE_TYPE = 'SUBSIDY') THEN (LNH.REQUESTED_AMOUNT - LNH.FUNDED_AMOUNT) * NVL(LNH.SUBSIDY_RATE/100
, 0) WHEN(LNH.LOAN_CLASS_CODE = 'DIRECT'
AND XLE.EVENT_TYPE_CODE = 'FUTURE_DISBURSEMENT_CANCELLED'
AND LND.LINE_TYPE <> 'SUBSIDY') THEN LNH.REQUESTED_AMOUNT - LNH.FUNDED_AMOUNT WHEN (LNH.LOAN_CLASS_CODE = 'DIRECT'
AND XLE.EVENT_TYPE_CODE = 'DIRECT_LOAN_APPROVED'
AND LND.LINE_TYPE <> 'SUBSIDY') THEN LNH.REQUESTED_AMOUNT WHEN (LNH.LOAN_CLASS_CODE = 'DIRECT'
AND XLE.EVENT_TYPE_CODE = 'DIRECT_LOAN_APPROVED'
AND LND.LINE_TYPE = 'SUBSIDY') THEN LNH.REQUESTED_AMOUNT * NVL(LNH.SUBSIDY_RATE/100
, 0) ELSE LND.DISTRIBUTION_AMOUNT END 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
, LNDH.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
FROM LNS_LOAN_HEADERS_ALL LNH
, LNS_DISTRIBUTIONS LND
, LNS_DISB_HEADERS LNDH
, XLA_EVENTS XLE
, XLA_TRANSACTION_ENTITIES XLEE
, GL_CODE_COMBINATIONS GL
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 XLE.EVENT_ID = LND.EVENT_ID
AND LND.CODE_COMBINATION_ID = GL.CODE_COMBINATION_ID(+)
AND LNH.LOAN_ID = LND.LOAN_ID
AND LND.DISTRIBUTION_TYPE = 'ORIGINATION'
AND LNH.LOAN_ID = LNDH.LOAN_ID(+)