View: LNS_LOAN_DETAILS_EXT_V

Product:	LNS - Loans
Description:	Loans Accounting Lines Extract
Implementation/DBA Data:	APPS.LNS_LOAN_DETAILS_EXT_V

View Text

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(+)

Columns