VIEW: APPS.LNS_SL_GL_V2

Source

View Text - Preformatted

SELECT 206 APPLICATION_ID ,null JE_CATEGORY ,null USER_JE_CATEGORY ,aeh.ledger_id SET_OF_BOOKS_ID ,null ORG_ID ,null TRX_CLASS ,null TRX_CLASS_NAME ,null TRX_TYPE_NAME ,null TRX_NUMBER_DISPLAYED ,null TRX_NUMBER_C ,null TRX_DATE ,null COMMENTS ,null DOC_SEQUENCE_ID ,lnh.loan_number DOC_SEQUENCE_VALUE ,lnh.loan_id TRX_HDR_ID ,null ACCT_LINE_TYPE ,lns_utility_pub.get_lookup_meaning('LOAN_DISTRIBUTION_ACCOUNTS',LND.ACCOUNT_NAME) ACCT_LINE_TYPE_NAME ,gl.code_combination_id CODE_COMBINATION_ID ,lnh.loan_currency CURRENCY_CODE ,ael.entered_dr ENTERED_DR ,ael.entered_cr ENTERED_CR ,ael.accounted_dr ACCOUNTED_DR ,ael.accounted_cr ACCOUNTED_CR ,ael.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE ,ael.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE ,ael.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE ,null THIRD_PARTY_NUMBER ,null THIRD_PARTY_NAME ,null THIRD_PARTY_SUB_ID ,null ACCOUNTING_DATE ,null TRX_LINE_NUMBER ,null TRX_LINE_TYPE ,null TRX_LINE_TYPE_NAME ,null REQUEST_ID ,null PROGRAM_APPLICATION_ID ,null PROGRAM_ID ,null PROGRAM_UPDATE_DATE ,gl.segment1 || decode(gl.segment2, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment2 || decode(gl.segment3, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment3 || decode(gl.segment4, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment4 || decode(gl.segment5, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment5 || decode(gl.segment6, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment6 || decode(gl.segment7, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment7 || decode(gl.segment8, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment8 || decode(gl.segment9, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment9 || decode(gl.segment10, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment10 || decode(gl.segment11, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment11 || decode(gl.segment12, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment12 || decode(gl.segment13, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment13 || decode(gl.segment14, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment14 || decode(gl.segment15, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment15 || decode(gl.segment16, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment16 || decode(gl.segment17, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment17 || decode(gl.segment18, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment18 || decode(gl.segment19, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment19 || decode(gl.segment20, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment20 || decode(gl.segment21, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment21 || decode(gl.segment22, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment22 || decode(gl.segment23, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment23 || decode(gl.segment24, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment24 || decode(gl.segment25, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment25 || decode(gl.segment26, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment26 || decode(gl.segment27, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment27 || decode(gl.segment28, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment28 || decode(gl.segment29, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) || gl.segment29 || decode(gl.segment30, null, null, fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gl.chart_of_accounts_id)) ACCOUNTING_EVENT_TYPE FROM lns_loan_headers_all lnh ,xla_ae_headers aeh ,xla_ae_lines ael ,xla_transaction_entities xte ,xla_events xev ,lns_distributions lnd ,lns_disb_headers disb ,gl_code_combinations gl ,gl_je_headers glh ,gl_je_lines gll ,gl_import_references R WHERE xev.application_id = 206 AND lnh.loan_id = lnd.loan_id AND lnh.loan_id = disb.loan_id(+) and disb.status(+) = 'FULLY_FUNDED' AND lnh.loan_id = xte.source_id_int_1 and xte.source_id_int_2 = decode(xte.source_id_int_2, -1,-1,disb.disb_header_id) AND xte.entity_id = xev.entity_id AND xev.event_id = aeh.event_id and aeh.ae_header_id = ael.ae_header_id and ael.code_combination_id = lnd.code_combination_id and lnd.code_combination_id = gl.code_combination_id and ((lnd.account_name = 'LOAN_RECEIVABLE' AND lnd.account_type = 'DR') OR (lnd.account_name = 'LOAN_PAYABLE' AND lnd.account_type = 'CR') OR (lnd.account_name = 'LOAN_CLEARING' AND lnd.account_type = 'CR')) and r.gl_sl_link_id = ael.gl_sl_link_id and R.je_header_id = glh.je_header_id and R.je_line_num = gll.je_line_num and glh.je_header_id = gll.je_header_id and gl.code_combination_id = gll.code_combination_id

View Text - HTML Formatted

SELECT 206 APPLICATION_ID
, NULL JE_CATEGORY
, NULL USER_JE_CATEGORY
, AEH.LEDGER_ID SET_OF_BOOKS_ID
, NULL ORG_ID
, NULL TRX_CLASS
, NULL TRX_CLASS_NAME
, NULL TRX_TYPE_NAME
, NULL TRX_NUMBER_DISPLAYED
, NULL TRX_NUMBER_C
, NULL TRX_DATE
, NULL COMMENTS
, NULL DOC_SEQUENCE_ID
, LNH.LOAN_NUMBER DOC_SEQUENCE_VALUE
, LNH.LOAN_ID TRX_HDR_ID
, NULL ACCT_LINE_TYPE
, LNS_UTILITY_PUB.GET_LOOKUP_MEANING('LOAN_DISTRIBUTION_ACCOUNTS'
, LND.ACCOUNT_NAME) ACCT_LINE_TYPE_NAME
, GL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, LNH.LOAN_CURRENCY CURRENCY_CODE
, AEL.ENTERED_DR ENTERED_DR
, AEL.ENTERED_CR ENTERED_CR
, AEL.ACCOUNTED_DR ACCOUNTED_DR
, AEL.ACCOUNTED_CR ACCOUNTED_CR
, AEL.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, AEL.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, AEL.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, NULL THIRD_PARTY_NUMBER
, NULL THIRD_PARTY_NAME
, NULL THIRD_PARTY_SUB_ID
, NULL ACCOUNTING_DATE
, NULL TRX_LINE_NUMBER
, NULL TRX_LINE_TYPE
, NULL TRX_LINE_TYPE_NAME
, NULL REQUEST_ID
, NULL PROGRAM_APPLICATION_ID
, NULL PROGRAM_ID
, NULL PROGRAM_UPDATE_DATE
, GL.SEGMENT1 || DECODE(GL.SEGMENT2
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT2 || DECODE(GL.SEGMENT3
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT3 || DECODE(GL.SEGMENT4
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT4 || DECODE(GL.SEGMENT5
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT5 || DECODE(GL.SEGMENT6
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT6 || DECODE(GL.SEGMENT7
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT7 || DECODE(GL.SEGMENT8
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT8 || DECODE(GL.SEGMENT9
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT9 || DECODE(GL.SEGMENT10
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT10 || DECODE(GL.SEGMENT11
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT11 || DECODE(GL.SEGMENT12
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT12 || DECODE(GL.SEGMENT13
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT13 || DECODE(GL.SEGMENT14
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT14 || DECODE(GL.SEGMENT15
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT15 || DECODE(GL.SEGMENT16
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT16 || DECODE(GL.SEGMENT17
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT17 || DECODE(GL.SEGMENT18
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT18 || DECODE(GL.SEGMENT19
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT19 || DECODE(GL.SEGMENT20
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT20 || DECODE(GL.SEGMENT21
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT21 || DECODE(GL.SEGMENT22
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT22 || DECODE(GL.SEGMENT23
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT23 || DECODE(GL.SEGMENT24
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT24 || DECODE(GL.SEGMENT25
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT25 || DECODE(GL.SEGMENT26
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT26 || DECODE(GL.SEGMENT27
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT27 || DECODE(GL.SEGMENT28
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT28 || DECODE(GL.SEGMENT29
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) || GL.SEGMENT29 || DECODE(GL.SEGMENT30
, NULL
, NULL
, FND_FLEX_EXT.GET_DELIMITER('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID)) ACCOUNTING_EVENT_TYPE
FROM LNS_LOAN_HEADERS_ALL LNH
, XLA_AE_HEADERS AEH
, XLA_AE_LINES AEL
, XLA_TRANSACTION_ENTITIES XTE
, XLA_EVENTS XEV
, LNS_DISTRIBUTIONS LND
, LNS_DISB_HEADERS DISB
, GL_CODE_COMBINATIONS GL
, GL_JE_HEADERS GLH
, GL_JE_LINES GLL
, GL_IMPORT_REFERENCES R
WHERE XEV.APPLICATION_ID = 206
AND LNH.LOAN_ID = LND.LOAN_ID
AND LNH.LOAN_ID = DISB.LOAN_ID(+)
AND DISB.STATUS(+) = 'FULLY_FUNDED'
AND LNH.LOAN_ID = XTE.SOURCE_ID_INT_1
AND XTE.SOURCE_ID_INT_2 = DECODE(XTE.SOURCE_ID_INT_2
, -1
, -1
, DISB.DISB_HEADER_ID)
AND XTE.ENTITY_ID = XEV.ENTITY_ID
AND XEV.EVENT_ID = AEH.EVENT_ID
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEL.CODE_COMBINATION_ID = LND.CODE_COMBINATION_ID
AND LND.CODE_COMBINATION_ID = GL.CODE_COMBINATION_ID
AND ((LND.ACCOUNT_NAME = 'LOAN_RECEIVABLE'
AND LND.ACCOUNT_TYPE = 'DR') OR (LND.ACCOUNT_NAME = 'LOAN_PAYABLE'
AND LND.ACCOUNT_TYPE = 'CR') OR (LND.ACCOUNT_NAME = 'LOAN_CLEARING'
AND LND.ACCOUNT_TYPE = 'CR'))
AND R.GL_SL_LINK_ID = AEL.GL_SL_LINK_ID
AND R.JE_HEADER_ID = GLH.JE_HEADER_ID
AND R.JE_LINE_NUM = GLL.JE_LINE_NUM
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GL.CODE_COMBINATION_ID = GLL.CODE_COMBINATION_ID