VIEW: APPS.OKL_AEL_GL_TRX_V

Source

View Text - Preformatted

SELECT chr.CONTRACT_NUMBER, product.description, CHR.START_DATE, CHR.END_DATE, chr.sts_code, prt.party_name, program.contract_number pa_agreement_num, tcl.ID, tcn.TRX_NUMBER, tcn.currency_code, tcn.DATE_TRANSACTION_OCCURRED, tcn.TSU_CODE, 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 GL_IMPORT_REFERENCES REF,OKL_AE_LINES_ALL AEL,OKL_AE_HEADERS_ALL AEH,OKL_ACC_EVENTS_ALL AET,OKL_AE_TEMPLATES_ALL AVL,OKL_TXL_CNTRCT_LNS_ALL TCL,OKL_TRX_CONTRACTS_ALL TCN,OKC_K_HEADERS_B CHR,OKL_K_HEADERS KHR,OKC_K_HEADERS_ALL_B PROGRAM,OKC_K_PARTY_ROLES_B RLE,OKL_PRODUCTS PRODUCT,HZ_PARTIES PRT,OKL_TRX_TYPES_B TRY,OKL_FORMULAE_B FMA,OKL_STRM_TYPE_V STY,GL_JE_CATEGORIES_VL JC,GL_DAILY_CONVERSION_TYPES GLCT WHERE tcn.khr_id = khr.id AND khr.id = rle.chr_id AND rle.rle_code = 'LESSEE' AND prt.party_id = rle.object1_ID1 AND rle.object1_ID2 = '#' AND tcn.id = tcl.tcn_id AND tcn.try_id = try.id AND aet.source_id = tcl.id AND ael.reference1 = avl.ID(+) AND tcl.sty_id = sty.id(+) AND avl.fma_id = fma.id(+) AND aeh.ae_category IN ('Loss Provision', 'Adjustment', 'Accrual', 'Miscellaneous', 'Lease Accrual') AND aeh.accounting_event_id = aet.accounting_event_id 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 CHR.CONTRACT_NUMBER
, PRODUCT.DESCRIPTION
, CHR.START_DATE
, CHR.END_DATE
, CHR.STS_CODE
, PRT.PARTY_NAME
, PROGRAM.CONTRACT_NUMBER PA_AGREEMENT_NUM
, TCL.ID
, TCN.TRX_NUMBER
, TCN.CURRENCY_CODE
, TCN.DATE_TRANSACTION_OCCURRED
, TCN.TSU_CODE
, 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 GL_IMPORT_REFERENCES REF
, OKL_AE_LINES_ALL AEL
, OKL_AE_HEADERS_ALL AEH
, OKL_ACC_EVENTS_ALL AET
, OKL_AE_TEMPLATES_ALL AVL
, OKL_TXL_CNTRCT_LNS_ALL TCL
, OKL_TRX_CONTRACTS_ALL TCN
, OKC_K_HEADERS_B CHR
, OKL_K_HEADERS KHR
, OKC_K_HEADERS_ALL_B PROGRAM
, OKC_K_PARTY_ROLES_B RLE
, OKL_PRODUCTS PRODUCT
, HZ_PARTIES PRT
, OKL_TRX_TYPES_B TRY
, OKL_FORMULAE_B FMA
, OKL_STRM_TYPE_V STY
, GL_JE_CATEGORIES_VL JC
, GL_DAILY_CONVERSION_TYPES GLCT
WHERE TCN.KHR_ID = KHR.ID
AND KHR.ID = RLE.CHR_ID
AND RLE.RLE_CODE = 'LESSEE'
AND PRT.PARTY_ID = RLE.OBJECT1_ID1
AND RLE.OBJECT1_ID2 = '#'
AND TCN.ID = TCL.TCN_ID
AND TCN.TRY_ID = TRY.ID
AND AET.SOURCE_ID = TCL.ID
AND AEL.REFERENCE1 = AVL.ID(+)
AND TCL.STY_ID = STY.ID(+)
AND AVL.FMA_ID = FMA.ID(+)
AND AEH.AE_CATEGORY IN ('LOSS PROVISION'
, 'ADJUSTMENT'
, 'ACCRUAL'
, 'MISCELLANEOUS'
, 'LEASE ACCRUAL')
AND AEH.ACCOUNTING_EVENT_ID = AET.ACCOUNTING_EVENT_ID
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'