VIEW: APPS.OKL_FA_TRX_LINE_UV

Source

View Text - Preformatted

SELECT FTH.BOOK_TYPE_CODE || '-' || OALV.ASSET_NUMBER || '-' || FTH.TRANSACTION_HEADER_ID ID, 1 LINE_NUMBER, FTH.BOOK_TYPE_CODE || ' - ' || FC.CONCATENATED_SEGMENTS DESCRIPTION, OKL_ACCOUNTING_UTIL.get_fa_trx_amount (FTH.BOOK_TYPE_CODE, OALV.ASSET_ID, FTH.TRANSACTION_TYPE_CODE, FTH.TRANSACTION_HEADER_ID) AMOUNT, OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE CURRENCY_CODE, TO_CHAR(NULL) STREAM_TYPE, TO_CHAR(NULL) STREAM_TYPE_PURPOSE, FTH.BOOK_TYPE_CODE || '-' || OALV.ASSET_NUMBER || '-' || FTH.TRANSACTION_HEADER_ID TRANSACTION_ID, 'FA_TRANSACTION_DETAIL' SOURCE_TABLE, FTH.ASSET_ID ASSET_ID, FTH.TRANSACTION_HEADER_ID DISTRIBUTION_ID, FTH.TRANSACTION_HEADER_ID PERIOD_COUNTER, FTH.BOOK_TYPE_CODE BOOK_TYPE_CODE, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER, OKHB.ID KHR_ID FROM OKL_VIEW_TRX_ASSETS_V OALV, OKC_K_HEADERS_ALL_B OKHB, FA_TRANSACTION_HEADERS FTH, FA_ADDITIONS FAD, FA_CATEGORIES_B_KFV FC WHERE OALV.DNZ_CHR_ID = OKHB.ID AND OALV.ASSET_ID = FTH.ASSET_ID AND OALV.CORPORATE_BOOK = FTH.BOOK_TYPE_CODE AND FTH.ASSET_ID = FAD.ASSET_ID AND FAD.ASSET_CATEGORY_ID = FC.CATEGORY_ID AND (TRUNC(FTH.TRANSACTION_DATE_ENTERED) >= OKL_ACCOUNTING_UTIL.get_fa_trx_start_date(OALV.ASSET_NUMBER, OALV.CORPORATE_BOOK, OALV.DNZ_CHR_ID, OKHB.STS_CODE) OR OKL_ACCOUNTING_UTIL.get_fa_trx_start_date(OALV.ASSET_NUMBER, OALV.CORPORATE_BOOK, OALV.DNZ_CHR_ID, OKHB.STS_CODE) IS NULL) AND ( TRUNC(FTH.TRANSACTION_DATE_ENTERED) <= OKL_ACCOUNTING_UTIL.get_fa_trx_end_date(OALV.ASSET_NUMBER, OALV.CORPORATE_BOOK, OALV.DNZ_CHR_ID) OR OKL_ACCOUNTING_UTIL.get_fa_trx_end_date(OALV.ASSET_NUMBER, OALV.CORPORATE_BOOK, OALV.DNZ_CHR_ID) IS NULL ) UNION ALL SELECT FDD.BOOK_TYPE_CODE || '-' || OALV.ASSET_NUMBER || '-' || FDD.PERIOD_COUNTER || '-' || FDD.DISTRIBUTION_ID ID, 1 LINE_NUMBER, FDD.BOOK_TYPE_CODE || ' - ' || FC.CONCATENATED_SEGMENTS DESCRIPTION, DEPRN_AMOUNT AMOUNT, OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE CURRENCY_CODE, TO_CHAR(NULL) STREAM_TYPE, TO_CHAR(NULL) STREAM_TYPE_PURPOSE, FDD.BOOK_TYPE_CODE || '-' || OALV.ASSET_NUMBER || '-' || FDD.PERIOD_COUNTER TRANSACTION_ID, 'FA_DEPRN_DETAIL' SOURCE_TABLE, FDD.ASSET_ID ASSET_ID, FDD.DISTRIBUTION_ID DISTRIBUTION_ID, FDD.PERIOD_COUNTER PERIOD_COUNTER, FDD.BOOK_TYPE_CODE BOOK_TYPE_CODE, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER, OKHB.ID KHR_ID FROM OKL_VIEW_TRX_ASSETS_V OALV, OKC_K_HEADERS_ALL_B OKHB, FA_DEPRN_DETAIL FDD, FA_DEPRN_PERIODS FDP, FA_ADDITIONS FAD, FA_CATEGORIES_B_KFV FC WHERE OALV.DNZ_CHR_ID = OKHB.ID AND OALV.ASSET_ID = FDD.ASSET_ID AND OALV.CORPORATE_BOOK = FDD.BOOK_TYPE_CODE AND FDD.PERIOD_COUNTER = FDP.PERIOD_COUNTER AND FDD.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE AND FDD.ASSET_ID = FAD.ASSET_ID AND FAD.ASSET_CATEGORY_ID = FC.CATEGORY_ID AND (OKHB.STS_CODE = 'EVERGREEN' or ( (TRUNC(FDP.CALENDAR_PERIOD_CLOSE_DATE) >= OKL_ACCOUNTING_UTIL.get_fa_trx_start_date(OALV.ASSET_NUMBER, OALV.CORPORATE_BOOK, OALV.DNZ_CHR_ID, OKHB.STS_CODE) OR OKL_ACCOUNTING_UTIL.get_fa_trx_start_date(OALV.ASSET_NUMBER, OALV.CORPORATE_BOOK, OALV.DNZ_CHR_ID, OKHB.STS_CODE) IS NULL ) AND ( TRUNC(FDP.CALENDAR_PERIOD_CLOSE_DATE) <= OKL_ACCOUNTING_UTIL.get_fa_trx_end_date(OALV.ASSET_NUMBER, OALV.CORPORATE_BOOK, OALV.DNZ_CHR_ID) OR OKL_ACCOUNTING_UTIL.get_fa_trx_end_date(OALV.ASSET_NUMBER, OALV.CORPORATE_BOOK, OALV.DNZ_CHR_ID) IS NULL ) ))

View Text - HTML Formatted

SELECT FTH.BOOK_TYPE_CODE || '-' || OALV.ASSET_NUMBER || '-' || FTH.TRANSACTION_HEADER_ID ID
, 1 LINE_NUMBER
, FTH.BOOK_TYPE_CODE || ' - ' || FC.CONCATENATED_SEGMENTS DESCRIPTION
, OKL_ACCOUNTING_UTIL.GET_FA_TRX_AMOUNT (FTH.BOOK_TYPE_CODE
, OALV.ASSET_ID
, FTH.TRANSACTION_TYPE_CODE
, FTH.TRANSACTION_HEADER_ID) AMOUNT
, OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE CURRENCY_CODE
, TO_CHAR(NULL) STREAM_TYPE
, TO_CHAR(NULL) STREAM_TYPE_PURPOSE
, FTH.BOOK_TYPE_CODE || '-' || OALV.ASSET_NUMBER || '-' || FTH.TRANSACTION_HEADER_ID TRANSACTION_ID
, 'FA_TRANSACTION_DETAIL' SOURCE_TABLE
, FTH.ASSET_ID ASSET_ID
, FTH.TRANSACTION_HEADER_ID DISTRIBUTION_ID
, FTH.TRANSACTION_HEADER_ID PERIOD_COUNTER
, FTH.BOOK_TYPE_CODE BOOK_TYPE_CODE
, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER
, OKHB.ID KHR_ID
FROM OKL_VIEW_TRX_ASSETS_V OALV
, OKC_K_HEADERS_ALL_B OKHB
, FA_TRANSACTION_HEADERS FTH
, FA_ADDITIONS FAD
, FA_CATEGORIES_B_KFV FC
WHERE OALV.DNZ_CHR_ID = OKHB.ID
AND OALV.ASSET_ID = FTH.ASSET_ID
AND OALV.CORPORATE_BOOK = FTH.BOOK_TYPE_CODE
AND FTH.ASSET_ID = FAD.ASSET_ID
AND FAD.ASSET_CATEGORY_ID = FC.CATEGORY_ID
AND (TRUNC(FTH.TRANSACTION_DATE_ENTERED) >= OKL_ACCOUNTING_UTIL.GET_FA_TRX_START_DATE(OALV.ASSET_NUMBER
, OALV.CORPORATE_BOOK
, OALV.DNZ_CHR_ID
, OKHB.STS_CODE) OR OKL_ACCOUNTING_UTIL.GET_FA_TRX_START_DATE(OALV.ASSET_NUMBER
, OALV.CORPORATE_BOOK
, OALV.DNZ_CHR_ID
, OKHB.STS_CODE) IS NULL)
AND ( TRUNC(FTH.TRANSACTION_DATE_ENTERED) <= OKL_ACCOUNTING_UTIL.GET_FA_TRX_END_DATE(OALV.ASSET_NUMBER
, OALV.CORPORATE_BOOK
, OALV.DNZ_CHR_ID) OR OKL_ACCOUNTING_UTIL.GET_FA_TRX_END_DATE(OALV.ASSET_NUMBER
, OALV.CORPORATE_BOOK
, OALV.DNZ_CHR_ID) IS NULL ) UNION ALL SELECT FDD.BOOK_TYPE_CODE || '-' || OALV.ASSET_NUMBER || '-' || FDD.PERIOD_COUNTER || '-' || FDD.DISTRIBUTION_ID ID
, 1 LINE_NUMBER
, FDD.BOOK_TYPE_CODE || ' - ' || FC.CONCATENATED_SEGMENTS DESCRIPTION
, DEPRN_AMOUNT AMOUNT
, OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE CURRENCY_CODE
, TO_CHAR(NULL) STREAM_TYPE
, TO_CHAR(NULL) STREAM_TYPE_PURPOSE
, FDD.BOOK_TYPE_CODE || '-' || OALV.ASSET_NUMBER || '-' || FDD.PERIOD_COUNTER TRANSACTION_ID
, 'FA_DEPRN_DETAIL' SOURCE_TABLE
, FDD.ASSET_ID ASSET_ID
, FDD.DISTRIBUTION_ID DISTRIBUTION_ID
, FDD.PERIOD_COUNTER PERIOD_COUNTER
, FDD.BOOK_TYPE_CODE BOOK_TYPE_CODE
, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER
, OKHB.ID KHR_ID
FROM OKL_VIEW_TRX_ASSETS_V OALV
, OKC_K_HEADERS_ALL_B OKHB
, FA_DEPRN_DETAIL FDD
, FA_DEPRN_PERIODS FDP
, FA_ADDITIONS FAD
, FA_CATEGORIES_B_KFV FC
WHERE OALV.DNZ_CHR_ID = OKHB.ID
AND OALV.ASSET_ID = FDD.ASSET_ID
AND OALV.CORPORATE_BOOK = FDD.BOOK_TYPE_CODE
AND FDD.PERIOD_COUNTER = FDP.PERIOD_COUNTER
AND FDD.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND FDD.ASSET_ID = FAD.ASSET_ID
AND FAD.ASSET_CATEGORY_ID = FC.CATEGORY_ID
AND (OKHB.STS_CODE = 'EVERGREEN' OR ( (TRUNC(FDP.CALENDAR_PERIOD_CLOSE_DATE) >= OKL_ACCOUNTING_UTIL.GET_FA_TRX_START_DATE(OALV.ASSET_NUMBER
, OALV.CORPORATE_BOOK
, OALV.DNZ_CHR_ID
, OKHB.STS_CODE) OR OKL_ACCOUNTING_UTIL.GET_FA_TRX_START_DATE(OALV.ASSET_NUMBER
, OALV.CORPORATE_BOOK
, OALV.DNZ_CHR_ID
, OKHB.STS_CODE) IS NULL )
AND ( TRUNC(FDP.CALENDAR_PERIOD_CLOSE_DATE) <= OKL_ACCOUNTING_UTIL.GET_FA_TRX_END_DATE(OALV.ASSET_NUMBER
, OALV.CORPORATE_BOOK
, OALV.DNZ_CHR_ID) OR OKL_ACCOUNTING_UTIL.GET_FA_TRX_END_DATE(OALV.ASSET_NUMBER
, OALV.CORPORATE_BOOK
, OALV.DNZ_CHR_ID) IS NULL ) ))