VIEW: APPS.OKL_AEL_GL_AST_V

Source

View Text - Preformatted

SELECT tl.ASSET_NUMBER, b.date_placed_in_service, a.Attribute_category_code, k.CONTRACT_NUMBER, DECODE(SIGN(trunc(sysdate)-NVL(trunc(L.start_date),trunc(sysdate))),-1,'I',DECODE(SIGN(trunc(sysdate) - NVL(trunc(L.end_date),trunc(sysdate))), 1,'I','A')) ASSET_STATUS, v.vendor_name VENDOR, tl.CORPORATE_BOOK, tl.ID, TRANS_NUMBER TRX_NUMBER, ael.currency_code trx_currency_code, ast.DATE_TRANS_OCCURRED, NULL TRANSACTION_STATUS, avl.NAME, fma.NAME, sty.CODE, Okl_Accounting_Util.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE', STY.STREAM_TYPE_PURPOSE), try.trx_type_class, OKL_ACCOUNTING_UTIL.get_lookup_meaning('OKL_TRANSACTION_TYPE_CLASS', try.trx_type_class), ael.ACCOUNTED_DR, ael.ACCOUNTED_CR, ref.JE_HEADER_ID, ref.JE_LINE_NUM, 540 APPLICATION_ID, aeh.SET_OF_BOOKS_ID, aeh.ORG_ID, aeh.ae_header_id AEH_ID, ael.ae_line_id AEL_ID, ael.ae_line_type_code ACCT_LINE_TYPE, ael.third_party_id THIRD_PARTY_ID, ael.subledger_doc_sequence_value DOC_SEQUENCE_VALUE, 'OKLECL' AEL_TABLE, ael.entered_dr ENTERED_DR, ael.entered_Cr ENTERED_CR, ael.code_combination_id CODE_COMBINATION_ID, ael.CURRENCY_CODE, ael.currency_conversion_date CURRENCY_CONVERSION_DATE, ael.currency_conversion_rate CURRENCY_CONVERSION_RATE, ael.currency_conversion_type CURRENCY_CONVERSION_TYPE, GLCT.user_conversion_type CURRENCY_USER_CONVERSION_TYPE, ael.subledger_doc_sequence_id DOC_SEQUENCE_ID , aeh.gl_transfer_flag GL_TRANSFER_STATUS, aeh.ae_category JE_CATEGORY, aeh.accounting_date accounting_date, jc.user_je_category_name USER_JE_CATEGORY_NAME, ael.LAST_UPDATE_DATE LAST_UPDATE_DATE, ael.LAST_UPDATED_BY LAST_UPDATED_BY, ael.CREATION_DATE CREATION_DATE, ael.CREATED_BY CREATED_BY, ael.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, ael.REQUEST_ID REQUEST_ID, ael.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID, ael.PROGRAM_ID PROGRAM_ID, ael.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE, aet.event_number ACCOUNTING_EVENT_NUMBER, aet.event_type_code ACCOUNTING_EVENT_TYPE, ael.ae_line_number ACCOUNTING_LINE_NUMBER, aeh.accounting_error_code AEH_ACCOUNTING_ERROR_CODE, ael.accounting_error_code AEL_ACCOUNTING_ERROR_CODE, ael.SOURCE_ID, ael.SOURCE_TABLE FROM OKL_TXL_ASSETS_B TL,OKL_TRX_ASSETS AST,OKC_K_HEADERS_B K,FA_BOOKS B,FA_ADDITIONS_B A,OKC_K_LINES_B L,PO_VENDORS V,OKC_K_PARTY_ROLES_B R,OKL_TRX_TYPES_B TRY,OKL_AE_TEMPLATES_ALL AVL,OKL_FORMULAE_B FMA,OKL_STRM_TYPE_B STY,OKL_ACC_EVENTS_ALL AET,OKL_AE_HEADERS_ALL AEH,OKL_AE_LINES_ALL AEL,GL_IMPORT_REFERENCES REF,GL_DAILY_CONVERSION_TYPES GLCT,GL_JE_CATEGORIES_VL JC WHERE ast.id = tl.TAS_ID AND tl.tal_type = 'RFL' AND tl.dnz_khr_id = k.id AND tl.dnz_asset_id = b.asset_id AND tl.corporate_book = b.book_type_code AND b.date_ineffective is null AND b.transaction_header_id_out is null AND tl.dnz_asset_id = a.asset_id AND tl.kle_id = l.cle_id AND tl.kle_id = r.cle_id(+) AND r.rle_code(+)='VENDOR' AND r.object1_id1 = v.VENDOR_ID(+) AND aet.source_id = tl.ID AND ael.reference1 = avl.ID AND avl.sty_id = sty.id AND avl.fma_id = fma.id(+) AND ast.try_id = try.id AND aeh.accounting_event_id = aet.accounting_event_id AND aeh.ae_category IN ('Asset Disposition') AND ael.ae_header_id = aeh.ae_header_id AND ael.currency_conversion_type = GLCT.conversion_type(+) AND ref.gl_sl_link_id = ael.gl_sl_link_id AND jc.je_category_name = aeh.ae_category AND ref.GL_SL_LINK_TABLE = 'OKLECL'

View Text - HTML Formatted

SELECT TL.ASSET_NUMBER
, B.DATE_PLACED_IN_SERVICE
, A.ATTRIBUTE_CATEGORY_CODE
, K.CONTRACT_NUMBER
, DECODE(SIGN(TRUNC(SYSDATE)-NVL(TRUNC(L.START_DATE)
, TRUNC(SYSDATE)))
, -1
, 'I'
, DECODE(SIGN(TRUNC(SYSDATE) - NVL(TRUNC(L.END_DATE)
, TRUNC(SYSDATE)))
, 1
, 'I'
, 'A')) ASSET_STATUS
, V.VENDOR_NAME VENDOR
, TL.CORPORATE_BOOK
, TL.ID
, TRANS_NUMBER TRX_NUMBER
, AEL.CURRENCY_CODE TRX_CURRENCY_CODE
, AST.DATE_TRANS_OCCURRED
, NULL TRANSACTION_STATUS
, AVL.NAME
, FMA.NAME
, STY.CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE'
, STY.STREAM_TYPE_PURPOSE)
, TRY.TRX_TYPE_CLASS
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_TRANSACTION_TYPE_CLASS'
, TRY.TRX_TYPE_CLASS)
, AEL.ACCOUNTED_DR
, AEL.ACCOUNTED_CR
, REF.JE_HEADER_ID
, REF.JE_LINE_NUM
, 540 APPLICATION_ID
, AEH.SET_OF_BOOKS_ID
, AEH.ORG_ID
, AEH.AE_HEADER_ID AEH_ID
, AEL.AE_LINE_ID AEL_ID
, AEL.AE_LINE_TYPE_CODE ACCT_LINE_TYPE
, AEL.THIRD_PARTY_ID THIRD_PARTY_ID
, AEL.SUBLEDGER_DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'OKLECL' AEL_TABLE
, AEL.ENTERED_DR ENTERED_DR
, AEL.ENTERED_CR ENTERED_CR
, AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, AEL.CURRENCY_CODE
, AEL.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, AEL.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, AEL.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, AEL.SUBLEDGER_DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, AEH.GL_TRANSFER_FLAG GL_TRANSFER_STATUS
, AEH.AE_CATEGORY JE_CATEGORY
, AEH.ACCOUNTING_DATE ACCOUNTING_DATE
, JC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, AEL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AEL.LAST_UPDATED_BY LAST_UPDATED_BY
, AEL.CREATION_DATE CREATION_DATE
, AEL.CREATED_BY CREATED_BY
, AEL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AEL.REQUEST_ID REQUEST_ID
, AEL.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, AEL.PROGRAM_ID PROGRAM_ID
, AEL.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, AET.EVENT_NUMBER ACCOUNTING_EVENT_NUMBER
, AET.EVENT_TYPE_CODE ACCOUNTING_EVENT_TYPE
, AEL.AE_LINE_NUMBER ACCOUNTING_LINE_NUMBER
, AEH.ACCOUNTING_ERROR_CODE AEH_ACCOUNTING_ERROR_CODE
, AEL.ACCOUNTING_ERROR_CODE AEL_ACCOUNTING_ERROR_CODE
, AEL.SOURCE_ID
, AEL.SOURCE_TABLE
FROM OKL_TXL_ASSETS_B TL
, OKL_TRX_ASSETS AST
, OKC_K_HEADERS_B K
, FA_BOOKS B
, FA_ADDITIONS_B A
, OKC_K_LINES_B L
, PO_VENDORS V
, OKC_K_PARTY_ROLES_B R
, OKL_TRX_TYPES_B TRY
, OKL_AE_TEMPLATES_ALL AVL
, OKL_FORMULAE_B FMA
, OKL_STRM_TYPE_B STY
, OKL_ACC_EVENTS_ALL AET
, OKL_AE_HEADERS_ALL AEH
, OKL_AE_LINES_ALL AEL
, GL_IMPORT_REFERENCES REF
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES_VL JC
WHERE AST.ID = TL.TAS_ID
AND TL.TAL_TYPE = 'RFL'
AND TL.DNZ_KHR_ID = K.ID
AND TL.DNZ_ASSET_ID = B.ASSET_ID
AND TL.CORPORATE_BOOK = B.BOOK_TYPE_CODE
AND B.DATE_INEFFECTIVE IS NULL
AND B.TRANSACTION_HEADER_ID_OUT IS NULL
AND TL.DNZ_ASSET_ID = A.ASSET_ID
AND TL.KLE_ID = L.CLE_ID
AND TL.KLE_ID = R.CLE_ID(+)
AND R.RLE_CODE(+)='VENDOR'
AND R.OBJECT1_ID1 = V.VENDOR_ID(+)
AND AET.SOURCE_ID = TL.ID
AND AEL.REFERENCE1 = AVL.ID
AND AVL.STY_ID = STY.ID
AND AVL.FMA_ID = FMA.ID(+)
AND AST.TRY_ID = TRY.ID
AND AEH.ACCOUNTING_EVENT_ID = AET.ACCOUNTING_EVENT_ID
AND AEH.AE_CATEGORY IN ('ASSET DISPOSITION')
AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND AEL.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+)
AND REF.GL_SL_LINK_ID = AEL.GL_SL_LINK_ID
AND JC.JE_CATEGORY_NAME = AEH.AE_CATEGORY
AND REF.GL_SL_LINK_TABLE = 'OKLECL'