eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OKL_TRX_HEADER_UV

Source

View Text - Preformatted


SELECT TO_CHAR(OTCB.ID) ID, /* View 1 */ TO_CHAR(OTCB.ID) TRANS_ID, OKHB.ID KHR_ID, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER, 'LEASE' SOURCE_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES', 'LEASE') SOURCE, OTCB.TRX_NUMBER TRANSACTION_NUMBER, OTTT.NAME TRANSACTION_TYPE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_TRANSACTION_STATUS', OTCB.TSU_CODE) TRANSACTION_STATUS, OTCB.DATE_TRANSACTION_OCCURRED TRANSACTION_DATE, OTCB.DESCRIPTION DESCRIPTION, NULL PRODUCT_NAME, OTCB.AMOUNT AMOUNT , OTCB.CURRENCY_CODE CURRENCY_CODE , OKL_ACCOUNTING_UTIL.Get_Draft_Actual_Trx(OTCB.ID, 'OKL_TXL_CNTRCT_LNS',OKHB.ID) ACTUAL_DRAFT_STATUS_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS', OKL_ACCOUNTING_UTIL.Get_Draft_Actual_Trx(OTCB.ID, 'OKL_TXL_CNTRCT_LNS',OKHB.ID)) ACTUAL_DRAFT_STATUS, NULL TOTAL_TRANSACTION_AMOUNT, NULL CHECK_ID, OKHB.ORG_ID, GL.SHORT_NAME REPRESENTATION_CODE, NULL VENDOR_ID, NULL ACCOUNT_NUMBER, null CONLS_INV_ID , NULL BOOK_TYPE_CODE, NULL PERIOD_COUNTER, NULL DEPRN_RUN_ID, OTTT.ID TRY_ID, 'N' INVOICE_YN FROM OKL_TRX_CONTRACTS_ALL OTCB,OKC_K_HEADERS_B OKHB,OKL_TRX_TYPES_V OTTT,GL_LEDGERS GL WHERE OTCB.KHR_ID = OKHB.ID AND OTCB.TRY_ID = OTTT.ID AND OTTT.AEP_CODE IS NOT NULL AND OTCB.SET_OF_BOOKS_ID = GL.LEDGER_ID AND EXISTS (select 1 from OKL_TXL_CNTRCT_LNS_UV TRANS where TRANS.KHR_ID = OKHB.ID and TRANS.TCN_ID = OTCB.ID) UNION ALL SELECT TO_CHAR(OTCB.ID) ID, /* View 2 */ TO_CHAR(OTCB.ID) TRANS_ID, OKHB.ID KHR_ID, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER, 'LEASE' SOURCE_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES', 'LEASE') SOURCE, TO_CHAR(OTCB.TRANS_NUMBER) TRANSACTION_NUMBER, OTTT.NAME TRANSACTION_TYPE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_TRANSACTION_STATUS', OTCB.TSU_CODE) TRANSACTION_STATUS, OTCB.DATE_TRANS_OCCURRED TRANSACTION_DATE, OTCB.COMMENTS DESCRIPTION, NULL PRODUCT_NAME, OTCB.TOTAL_MATCH_AMOUNT AMOUNT , OKHB.CURRENCY_CODE CURRENCY_CODE , OKL_ACCOUNTING_UTIL.Get_Draft_Actual_Trx(OTCB.ID, 'OKL_TXL_ASSETS_B',OKHB.ID) ACTUAL_DRAFT_STATUS_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS', OKL_ACCOUNTING_UTIL.Get_Draft_Actual_Trx(OTCB.ID, 'OKL_TXL_ASSETS_B',OKHB.ID)) ACTUAL_DRAFT_STATUS, NULL TOTAL_TRANSACTION_AMOUNT, NULL CHECK_ID, OKHB.ORG_ID, GL.SHORT_NAME REPRESENTATION_CODE, NULL VENDOR_ID, NULL ACCOUNT_NUMBER, null CONLS_INV_ID, NULL BOOK_TYPE_CODE, NULL PERIOD_COUNTER, NULL DEPRN_RUN_ID, OTTT.ID TRY_ID, 'N' INVOICE_YN FROM OKL_TRX_ASSETS OTCB, OKL_TXL_ASSETS_B TXL, OKC_K_HEADERS_B OKHB, OKL_TRX_TYPES_V OTTT, FA_BOOK_CONTROLS FAB, GL_LEDGERS GL WHERE OTCB.ID = TXL.TAS_ID AND TXL.DNZ_KHR_ID = OKHB.ID AND OTCB.TRY_ID = OTTT.ID AND OTTT.AEP_CODE IS NOT NULL AND EXISTS (select 1 from OKL_TXL_CNTRCT_LNS_UV TRANS where TRANS.KHR_ID = OKHB.ID and TRANS.TCN_ID = OTCB.ID) AND FAB.BOOK_TYPE_CODE = TXL.CORPORATE_BOOK AND FAB.SET_OF_BOOKS_ID = GL.LEDGER_ID UNION ALL SELECT /* View 3 */ TO_CHAR(AI.INVOICE_ID) ID, TO_CHAR(AI.INVOICE_ID) TRANS_ID, AI.KHR_ID KHR_ID, AI.CONTRACT_NUMBER CONTRACT_NUMBER, 'PAY' SOURCE_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES', 'PAY') SOURCE, AI.INVOICE_NUM TRANSACTION_NUMBER, TRY.NAME TRANSACTION_TYPE, AI.TRANSACTION_STATUS TRANSACTION_STATUS, AI.INVOICE_DATE TRANSACTION_DATE, AI.DESCRIPTION DESCRIPTION, NULL PRODUCT_NAME, AI.KHR_LINE_AMOUNT AMOUNT, AI.INVOICE_CURRENCY_CODE CURRENCY_CODE , 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS', 'ACTUAL') ACTUAL_DRAFT_STATUS, AI.INVOICE_AMOUNT TOTAL_TRANSACTION_AMOUNT, NULL CHECK_ID, AI.ORG_ID ,GL.SHORT_NAME REPRESENTATION_CODE, AI.VENDOR_ID VENDOR_ID, NULL ACCOUNT_NUMBER, AI.cnsld_ap_inv_id CONLS_INV_ID, NULL BOOK_TYPE_CODE, NULL PERIOD_COUNTER, NULL DEPRN_RUN_ID, AI.TRY_ID TRY_ID, 'Y' INVOICE_YN FROM OKL_BPD_AP_INVOICE_UV AI, OKL_TRX_TYPES_V TRY, GL_LEDGERS GL WHERE AI.TRY_ID = TRY.ID AND GL.LEDGER_ID = AI.SET_OF_BOOKS_ID UNION ALL SELECT oalv.tax_book || '-' || oalv.asset_number || '-' || fth.transaction_header_id id, /* View 4*/ to_char(fth.transaction_header_id) trans_id, okhb.id khr_id, okhb.contract_number contract_number, 'FA' source_code, okl_accounting_util.get_lookup_meaning('OKL_VIEW_ACC_SOURCES', 'FA') source, oalv.asset_number || '-' || fth.transaction_header_id transaction_number, fal.meaning transaction_type, NULL transaction_status, fth.transaction_date_entered transaction_date, oalv.tax_book || ' - ' || fc.concatenated_segments description, NULL product_name, okl_accounting_util.get_fa_trx_amount(oalv.tax_book, oalv.asset_id, fth.transaction_type_code, fth.transaction_header_id) amount, okl_accounting_util.get_func_curr_code currency_code, 'ACTUAL' actual_draft_status_code, okl_accounting_util.get_lookup_meaning('OKL_ACTUAL_DRAFT_STATUS', 'ACTUAL') actual_draft_status, NULL total_transaction_amount, NULL check_id, okhb.org_id, gl.short_name representation_code, NULL vendor_id, NULL account_number, NULL conls_inv_id, NULL book_type_code, NULL period_counter, NULL deprn_run_id, -888 try_id, 'N' invoice_yn FROM okl_tax_bk_trx_assts_v oalv, okc_k_headers_b okhb, fa_transaction_headers fth, fa_lookups fal, fa_additions fad, fa_categories_b_kfv fc, okl_sys_acct_opts sao, okl_system_params sys, fa_book_controls fab, gl_ledgers gl WHERE oalv.dnz_chr_id = okhb.id AND oalv.asset_id = fth.asset_id AND oalv.tax_book = fth.book_type_code AND fth.transaction_type_code NOT IN('TRANSFER', 'TRANSFER IN', 'TRANSFER  IN/VOID', 'ADDITION/VOID', 'TRANSFER OUT', 'CIP ADDITION/VOID') AND fth.transaction_type_code = fal.lookup_code AND fal.lookup_type = 'FAXOLTRX' AND oalv.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.tax_book, oalv.dnz_chr_id, okhb.sts_code) OR okl_accounting_util.get_fa_trx_start_date(oalv.asset_number, oalv.tax_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.tax_book, oalv.dnz_chr_id) OR okl_accounting_util.get_fa_trx_end_date(oalv.asset_number, oalv.tax_book, oalv.dnz_chr_id) IS NULL) AND fab.book_type_code = fth.book_type_code AND fab.set_of_books_id = gl.ledger_id AND sao.org_id = sys.org_id AND sys.rpt_prod_book_type_code = fab.book_type_code AND fab.set_of_books_id = gl.ledger_id AND sao.secondary_rep_method = 'AUTOMATED' UNION ALL SELECT /* View 5 */ OALV.CORPORATE_BOOK || '-' || OALV.ASSET_NUMBER || '-' || FTH.TRANSACTION_HEADER_ID ID, TO_CHAR(FTH.TRANSACTION_HEADER_ID) TRANS_ID, OKHB.ID KHR_ID, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER, 'FA' SOURCE_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES', 'FA') SOURCE, OALV.ASSET_NUMBER || '-' || FTH.TRANSACTION_HEADER_ID TRANSACTION_NUMBER, FAL.MEANING TRANSACTION_TYPE, NULL TRANSACTION_STATUS, FTH.TRANSACTION_DATE_ENTERED TRANSACTION_DATE, OALV.CORPORATE_BOOK || ' - ' || FC.CONCATENATED_SEGMENTS DESCRIPTION, NULL PRODUCT_NAME, OKL_ACCOUNTING_UTIL.GET_FA_TRX_AMOUNT (OALV.CORPORATE_BOOK, OALV.ASSET_ID, FTH.TRANSACTION_TYPE_CODE, FTH.TRANSACTION_HEADER_ID) AMOUNT, OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE CURRENCY_CODE , 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS', 'ACTUAL') ACTUAL_DRAFT_STATUS, NULL TOTAL_TRANSACTION_AMOUNT, NULL CHECK_ID, OKHB.ORG_ID, GL.SHORT_NAME REPRESENTATION_CODE, NULL VENDOR_ID, NULL ACCOUNT_NUMBER, null CONLS_INV_ID , NULL BOOK_TYPE_CODE, NULL PERIOD_COUNTER, NULL DEPRN_RUN_ID, -888 TRY_ID, 'N' INVOICE_YN FROM OKL_VIEW_TRX_ASSETS_V OALV, OKC_K_HEADERS_B OKHB, FA_TRANSACTION_HEADERS FTH, FA_LOOKUPS FAL , FA_ADDITIONS FAD, FA_CATEGORIES_B_KFV FC, FA_BOOK_CONTROLS FAB, GL_LEDGERS GL WHERE OALV.DNZ_CHR_ID = OKHB.ID AND OALV.ASSET_ID = FTH.ASSET_ID AND OALV.CORPORATE_BOOK = FTH.BOOK_TYPE_CODE AND FTH.TRANSACTION_TYPE_CODE NOT IN ('TRANSFER', 'TRANSFER IN', 'TRANSFER  IN/VOID', 'ADDITION/VOID', 'TRANSFER OUT', 'CIP ADDITION/VOID') AND FTH.TRANSACTION_TYPE_CODE = FAL.LOOKUP_CODE AND FAL.LOOKUP_TYPE = 'FAXOLTRX' AND OALV.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 ) AND FAB.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE AND FAB.SET_OF_BOOKS_ID = GL.LEDGER_ID UNION ALL SELECT oalv.tax_book || '-' || oalv.asset_number || '-' || fds.period_counter id, /* View 6 */ to_char(fds.asset_id) trans_id, okhb.id khr_id, okhb.contract_number contract_number, 'FA' source_code, okl_accounting_util.get_lookup_meaning('OKL_VIEW_ACC_SOURCES', 'FA') source, oalv.asset_number || ' - ' || fdp.period_name transaction_number, okl_accounting_util.get_lookup_meaning('PN_PAYMENT_PURPOSE_TYPE', 'DEP', 240, 0) transaction_type, NULL transaction_status, TRUNC(fdp.calendar_period_close_date) transaction_date, oalv.tax_book || '-' || fc.concatenated_segments description, NULL product_name, fds.deprn_amount amount, okl_accounting_util.get_func_curr_code currency_code, 'ACTUAL' actual_draft_status_code, okl_accounting_util.get_lookup_meaning('OKL_ACTUAL_DRAFT_STATUS', 'ACTUAL') actual_draft_status, NULL total_transaction_amount, NULL check_id, okhb.org_id, gl.short_name representation_code, NULL vendor_id, NULL account_number, NULL conls_inv_id, fds.book_type_code book_type_code, fds.period_counter period_counter, fds.deprn_run_id deprn_run_id, -999 try_id, 'N' invoice_yn FROM okl_tax_bk_trx_assts_v oalv, fa_deprn_summary fds, okc_k_headers_b okhb, fa_deprn_periods fdp, fa_additions fad, fa_categories_b_kfv fc, okl_sys_acct_opts sao, okl_system_params sys, fa_book_controls fab, gl_ledgers gl WHERE fds.asset_id = oalv.asset_id AND fds.book_type_code = oalv.tax_book AND oalv.dnz_chr_id = okhb.id AND fds.period_counter = fdp.period_counter AND fds.book_type_code = fdp.book_type_code AND oalv.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.tax_book, oalv.dnz_chr_id, okhb.sts_code) OR okl_accounting_util.get_fa_trx_start_date(oalv.asset_number, oalv.tax_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.tax_book, oalv.dnz_chr_id) OR okl_accounting_util.get_fa_trx_end_date(oalv.asset_number, oalv.tax_book, oalv.dnz_chr_id) IS NULL))) AND fab.book_type_code = fds.book_type_code AND sao.org_id = sys.org_id AND sys.rpt_prod_book_type_code = fab.book_type_code AND fab.set_of_books_id = gl.ledger_id AND sao.secondary_rep_method = 'AUTOMATED' UNION ALL SELECT /* View 7 */ OALV.CORPORATE_BOOK || '-' || OALV.ASSET_NUMBER || '-' || FDS.PERIOD_COUNTER ID, TO_CHAR(FDS.ASSET_ID) TRANS_ID, OKHB.ID KHR_ID, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER, 'FA' SOURCE_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES', 'FA') SOURCE, OALV.ASSET_NUMBER || ' - ' || FDP.PERIOD_NAME TRANSACTION_NUMBER, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('PN_PAYMENT_PURPOSE_TYPE', 'DEP', 240, 0) TRANSACTION_TYPE, NULL TRANSACTION_STATUS, TRUNC(FDP.CALENDAR_PERIOD_CLOSE_DATE) TRANSACTION_DATE, OALV.CORPORATE_BOOK || '-' || FC.CONCATENATED_SEGMENTS DESCRIPTION, NULL PRODUCT_NAME, FDS.DEPRN_AMOUNT AMOUNT , OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE CURRENCY_CODE , 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS', 'ACTUAL') ACTUAL_DRAFT_STATUS, NULL TOTAL_TRANSACTION_AMOUNT, NULL CHECK_ID, OKHB.ORG_ID, GL.SHORT_NAME REPRESENTATION_CODE, NULL VENDOR_ID, NULL ACCOUNT_NUMBER, null CONLS_INV_ID, FDS.BOOK_TYPE_CODE BOOK_TYPE_CODE, FDS.PERIOD_COUNTER PERIOD_COUNTER, FDS.DEPRN_RUN_ID DEPRN_RUN_ID, -999 TRY_ID, 'N' INVOICE_YN FROM OKL_VIEW_TRX_ASSETS_V OALV, FA_DEPRN_SUMMARY FDS, OKC_K_HEADERS_B OKHB, FA_DEPRN_PERIODS FDP, FA_ADDITIONS FAD, FA_CATEGORIES_B_KFV FC, FA_BOOK_CONTROLS FAB, GL_LEDGERS GL WHERE FDS.ASSET_ID = OALV.ASSET_ID AND FDS.BOOK_TYPE_CODE = OALV.CORPORATE_BOOK AND OALV.DNZ_CHR_ID = OKHB.ID AND FDS.PERIOD_COUNTER = FDP.PERIOD_COUNTER AND FDS.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE AND OALV.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 ))) AND FAB.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE AND FAB.SET_OF_BOOKS_ID = GL.LEDGER_ID UNION ALL SELECT /* View 8 */ TO_CHAR(RCT.ID) ID, TO_CHAR(RCT.ID) TRANS_ID, RCT.KHR_ID KHR_ID, RCT.CONTRACT_NUMBER CONTRACT_NUMBER, RCT.SOURCE_CODE SOURCE_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES', 'REC') SOURCE, RCT.TRANSACTION_NUMBER TRANSACTION_NUMBER, RCT.TRANSACTION_TYPE TRANSACTION_TYPE, RCT.TRANSACTION_STATUS TRANSACTION_STATUS, RCT.TRANSACTION_DATE TRANSACTION_DATE, RCT.DESCRIPTION DESCRIPTION, NULL PRODUCT_NAME, RCT.AMOUNT AMOUNT, RCT.CURRENCY_CODE CURRENCY_CODE, RCT.ACTUAL_DRAFT_STATUS_CODE, RCT.ACTUAL_DRAFT_STATUS, RCT.TOTAL_TRANSACTION_AMOUNT TOTAL_TRANSACTION_AMOUNT, RCT.CHECK_ID CHECK_ID, RCT.ORG_ID ORG_ID, GL.SHORT_NAME REPRESENTATION_CODE , NULL VENDOR_ID, RCT.ACCOUNT_NUMBER ACCOUNT_NUMBER, null CONLS_INV_ID, NULL BOOK_TYPE_CODE, NULL PERIOD_COUNTER, NULL DEPRN_RUN_ID, RCT.TRY_ID TRY_ID, 'Y' INVOICE_YN FROM OKL_BPD_AR_HEADER_V RCT, GL_LEDGERS GL WHERE RCT.LEDGER_ID = GL.LEDGER_ID UNION ALL SELECT /* View 9 */ TO_CHAR(RAP.RECEIPT_ID) ID, TO_CHAR(RAP.RECEIPT_ID) TRANS_ID, RAP.CONTRACT_ID KHR_ID, RAP.CONTRACT_NUMBER CONTRACT_NUMBER, RAP.SOURCE_CODE SOURCE_CODE, RAP.SOURCE SOURCE, RAP.RECEIPT_NUMBER TRANSACTION_NUMBER, RAP.TRANSACTION_TYPE TRANSACTION_TYPE, RAP.TRANSACTION_STATUS TRANSACTION_STATUS, RAP.RECEIPT_DATE TRANSACTION_DATE, RAP.DESCRIPTION DESCRIPTION, NULL PRODUCT_NAME,/*not used*/ RAP.AMOUNT_APPLIED AMOUNT, RAP.CURRENCY_CODE CURRENCY_CODE, 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS', 'ACTUAL') ACTUAL_DRAFT_STATUS, RAP.TOTAL_AMOUNT TOTAL_TRANSACTION_AMOUNT, NULL CHECK_ID, RAP.ORG_ID, GL.SHORT_NAME REPRESENTATION_CODE , NULL VENDOR_ID, NULL ACCOUNT_NUMBER, null CONLS_INV_ID, NULL BOOK_TYPE_CODE, NULL PERIOD_COUNTER, NULL DEPRN_RUN_ID, -777 TRY_ID, 'N' INVOICE_YN FROM OKL_BPD_TRX_RCPT_HEADER_V RAP, GL_LEDGERS GL WHERE GL.LEDGER_ID = RAP.SET_OF_BOOKS_ID UNION ALL SELECT ID, /* View 10 */ AR_ADJ.ID TRANS_ID, AR_ADJ.KHR_ID, AR_ADJ.CONTRACT_NUMBER, AR_ADJ.SOURCE_CODE, AR_ADJ.SOURCE, AR_ADJ.TRANSACTION_NUMBER, AR_ADJ.TRANSACTION_TYPE, AR_ADJ.TRANSACTION_STATUS, AR_ADJ.TRANSACTION_DATE, AR_ADJ.DESCRIPTION, AR_ADJ.PRODUCT_NAME, AR_ADJ.AMOUNT, AR_ADJ.CURRENCY_CODE, AR_ADJ.ACTUAL_DRAFT_STATUS_CODE, AR_ADJ.ACTUAL_DRAFT_STATUS, AR_ADJ.TOTAL_TRANSACTION_AMOUNT, AR_ADJ.CHECK_ID, AR_ADJ.ORG_ID, GL.SHORT_NAME REPRESENTATION_CODE, NULL VENDOR_ID, AR_ADJ.ACCOUNT_NUMBER, NULL CONLS_INV_ID, NULL BOOK_TYPE_CODE, NULL PERIOD_COUNTER, NULL DEPRN_RUN_ID, AR_ADJ.TRY_ID, NULL INVOICE_YN FROM OKL_BPD_AR_ADJUSTMENTS_V AR_ADJ, GL_LEDGERS GL WHERE GL.LEDGER_ID = AR_ADJ.LEDGER_ID

View Text - HTML Formatted

SELECT TO_CHAR(OTCB.ID) ID
, /* VIEW 1 */ TO_CHAR(OTCB.ID) TRANS_ID
, OKHB.ID KHR_ID
, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER
, 'LEASE' SOURCE_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES'
, 'LEASE') SOURCE
, OTCB.TRX_NUMBER TRANSACTION_NUMBER
, OTTT.NAME TRANSACTION_TYPE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_TRANSACTION_STATUS'
, OTCB.TSU_CODE) TRANSACTION_STATUS
, OTCB.DATE_TRANSACTION_OCCURRED TRANSACTION_DATE
, OTCB.DESCRIPTION DESCRIPTION
, NULL PRODUCT_NAME
, OTCB.AMOUNT AMOUNT
, OTCB.CURRENCY_CODE CURRENCY_CODE
, OKL_ACCOUNTING_UTIL.GET_DRAFT_ACTUAL_TRX(OTCB.ID
, 'OKL_TXL_CNTRCT_LNS'
, OKHB.ID) ACTUAL_DRAFT_STATUS_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS'
, OKL_ACCOUNTING_UTIL.GET_DRAFT_ACTUAL_TRX(OTCB.ID
, 'OKL_TXL_CNTRCT_LNS'
, OKHB.ID)) ACTUAL_DRAFT_STATUS
, NULL TOTAL_TRANSACTION_AMOUNT
, NULL CHECK_ID
, OKHB.ORG_ID
, GL.SHORT_NAME REPRESENTATION_CODE
, NULL VENDOR_ID
, NULL ACCOUNT_NUMBER
, NULL CONLS_INV_ID
, NULL BOOK_TYPE_CODE
, NULL PERIOD_COUNTER
, NULL DEPRN_RUN_ID
, OTTT.ID TRY_ID
, 'N' INVOICE_YN
FROM OKL_TRX_CONTRACTS_ALL OTCB
, OKC_K_HEADERS_B OKHB
, OKL_TRX_TYPES_V OTTT
, GL_LEDGERS GL
WHERE OTCB.KHR_ID = OKHB.ID
AND OTCB.TRY_ID = OTTT.ID
AND OTTT.AEP_CODE IS NOT NULL
AND OTCB.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND EXISTS (SELECT 1
FROM OKL_TXL_CNTRCT_LNS_UV TRANS
WHERE TRANS.KHR_ID = OKHB.ID
AND TRANS.TCN_ID = OTCB.ID) UNION ALL SELECT TO_CHAR(OTCB.ID) ID
, /* VIEW 2 */ TO_CHAR(OTCB.ID) TRANS_ID
, OKHB.ID KHR_ID
, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER
, 'LEASE' SOURCE_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES'
, 'LEASE') SOURCE
, TO_CHAR(OTCB.TRANS_NUMBER) TRANSACTION_NUMBER
, OTTT.NAME TRANSACTION_TYPE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_TRANSACTION_STATUS'
, OTCB.TSU_CODE) TRANSACTION_STATUS
, OTCB.DATE_TRANS_OCCURRED TRANSACTION_DATE
, OTCB.COMMENTS DESCRIPTION
, NULL PRODUCT_NAME
, OTCB.TOTAL_MATCH_AMOUNT AMOUNT
, OKHB.CURRENCY_CODE CURRENCY_CODE
, OKL_ACCOUNTING_UTIL.GET_DRAFT_ACTUAL_TRX(OTCB.ID
, 'OKL_TXL_ASSETS_B'
, OKHB.ID) ACTUAL_DRAFT_STATUS_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS'
, OKL_ACCOUNTING_UTIL.GET_DRAFT_ACTUAL_TRX(OTCB.ID
, 'OKL_TXL_ASSETS_B'
, OKHB.ID)) ACTUAL_DRAFT_STATUS
, NULL TOTAL_TRANSACTION_AMOUNT
, NULL CHECK_ID
, OKHB.ORG_ID
, GL.SHORT_NAME REPRESENTATION_CODE
, NULL VENDOR_ID
, NULL ACCOUNT_NUMBER
, NULL CONLS_INV_ID
, NULL BOOK_TYPE_CODE
, NULL PERIOD_COUNTER
, NULL DEPRN_RUN_ID
, OTTT.ID TRY_ID
, 'N' INVOICE_YN
FROM OKL_TRX_ASSETS OTCB
, OKL_TXL_ASSETS_B TXL
, OKC_K_HEADERS_B OKHB
, OKL_TRX_TYPES_V OTTT
, FA_BOOK_CONTROLS FAB
, GL_LEDGERS GL
WHERE OTCB.ID = TXL.TAS_ID
AND TXL.DNZ_KHR_ID = OKHB.ID
AND OTCB.TRY_ID = OTTT.ID
AND OTTT.AEP_CODE IS NOT NULL
AND EXISTS (SELECT 1
FROM OKL_TXL_CNTRCT_LNS_UV TRANS
WHERE TRANS.KHR_ID = OKHB.ID
AND TRANS.TCN_ID = OTCB.ID)
AND FAB.BOOK_TYPE_CODE = TXL.CORPORATE_BOOK
AND FAB.SET_OF_BOOKS_ID = GL.LEDGER_ID UNION ALL SELECT /* VIEW 3 */ TO_CHAR(AI.INVOICE_ID) ID
, TO_CHAR(AI.INVOICE_ID) TRANS_ID
, AI.KHR_ID KHR_ID
, AI.CONTRACT_NUMBER CONTRACT_NUMBER
, 'PAY' SOURCE_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES'
, 'PAY') SOURCE
, AI.INVOICE_NUM TRANSACTION_NUMBER
, TRY.NAME TRANSACTION_TYPE
, AI.TRANSACTION_STATUS TRANSACTION_STATUS
, AI.INVOICE_DATE TRANSACTION_DATE
, AI.DESCRIPTION DESCRIPTION
, NULL PRODUCT_NAME
, AI.KHR_LINE_AMOUNT AMOUNT
, AI.INVOICE_CURRENCY_CODE CURRENCY_CODE
, 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS'
, 'ACTUAL') ACTUAL_DRAFT_STATUS
, AI.INVOICE_AMOUNT TOTAL_TRANSACTION_AMOUNT
, NULL CHECK_ID
, AI.ORG_ID
, GL.SHORT_NAME REPRESENTATION_CODE
, AI.VENDOR_ID VENDOR_ID
, NULL ACCOUNT_NUMBER
, AI.CNSLD_AP_INV_ID CONLS_INV_ID
, NULL BOOK_TYPE_CODE
, NULL PERIOD_COUNTER
, NULL DEPRN_RUN_ID
, AI.TRY_ID TRY_ID
, 'Y' INVOICE_YN
FROM OKL_BPD_AP_INVOICE_UV AI
, OKL_TRX_TYPES_V TRY
, GL_LEDGERS GL
WHERE AI.TRY_ID = TRY.ID
AND GL.LEDGER_ID = AI.SET_OF_BOOKS_ID UNION ALL SELECT OALV.TAX_BOOK || '-' || OALV.ASSET_NUMBER || '-' || FTH.TRANSACTION_HEADER_ID ID
, /* VIEW 4*/ TO_CHAR(FTH.TRANSACTION_HEADER_ID) TRANS_ID
, OKHB.ID KHR_ID
, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER
, 'FA' SOURCE_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES'
, 'FA') SOURCE
, OALV.ASSET_NUMBER || '-' || FTH.TRANSACTION_HEADER_ID TRANSACTION_NUMBER
, FAL.MEANING TRANSACTION_TYPE
, NULL TRANSACTION_STATUS
, FTH.TRANSACTION_DATE_ENTERED TRANSACTION_DATE
, OALV.TAX_BOOK || ' - ' || FC.CONCATENATED_SEGMENTS DESCRIPTION
, NULL PRODUCT_NAME
, OKL_ACCOUNTING_UTIL.GET_FA_TRX_AMOUNT(OALV.TAX_BOOK
, OALV.ASSET_ID
, FTH.TRANSACTION_TYPE_CODE
, FTH.TRANSACTION_HEADER_ID) AMOUNT
, OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE CURRENCY_CODE
, 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS'
, 'ACTUAL') ACTUAL_DRAFT_STATUS
, NULL TOTAL_TRANSACTION_AMOUNT
, NULL CHECK_ID
, OKHB.ORG_ID
, GL.SHORT_NAME REPRESENTATION_CODE
, NULL VENDOR_ID
, NULL ACCOUNT_NUMBER
, NULL CONLS_INV_ID
, NULL BOOK_TYPE_CODE
, NULL PERIOD_COUNTER
, NULL DEPRN_RUN_ID
, -888 TRY_ID
, 'N' INVOICE_YN
FROM OKL_TAX_BK_TRX_ASSTS_V OALV
, OKC_K_HEADERS_B OKHB
, FA_TRANSACTION_HEADERS FTH
, FA_LOOKUPS FAL
, FA_ADDITIONS FAD
, FA_CATEGORIES_B_KFV FC
, OKL_SYS_ACCT_OPTS SAO
, OKL_SYSTEM_PARAMS SYS
, FA_BOOK_CONTROLS FAB
, GL_LEDGERS GL
WHERE OALV.DNZ_CHR_ID = OKHB.ID
AND OALV.ASSET_ID = FTH.ASSET_ID
AND OALV.TAX_BOOK = FTH.BOOK_TYPE_CODE
AND FTH.TRANSACTION_TYPE_CODE NOT IN('TRANSFER'
, 'TRANSFER IN'
, 'TRANSFER IN/VOID'
, 'ADDITION/VOID'
, 'TRANSFER OUT'
, 'CIP ADDITION/VOID')
AND FTH.TRANSACTION_TYPE_CODE = FAL.LOOKUP_CODE
AND FAL.LOOKUP_TYPE = 'FAXOLTRX'
AND OALV.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.TAX_BOOK
, OALV.DNZ_CHR_ID
, OKHB.STS_CODE) OR OKL_ACCOUNTING_UTIL.GET_FA_TRX_START_DATE(OALV.ASSET_NUMBER
, OALV.TAX_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.TAX_BOOK
, OALV.DNZ_CHR_ID) OR OKL_ACCOUNTING_UTIL.GET_FA_TRX_END_DATE(OALV.ASSET_NUMBER
, OALV.TAX_BOOK
, OALV.DNZ_CHR_ID) IS NULL)
AND FAB.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE
AND FAB.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND SAO.ORG_ID = SYS.ORG_ID
AND SYS.RPT_PROD_BOOK_TYPE_CODE = FAB.BOOK_TYPE_CODE
AND FAB.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND SAO.SECONDARY_REP_METHOD = 'AUTOMATED' UNION ALL SELECT /* VIEW 5 */ OALV.CORPORATE_BOOK || '-' || OALV.ASSET_NUMBER || '-' || FTH.TRANSACTION_HEADER_ID ID
, TO_CHAR(FTH.TRANSACTION_HEADER_ID) TRANS_ID
, OKHB.ID KHR_ID
, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER
, 'FA' SOURCE_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES'
, 'FA') SOURCE
, OALV.ASSET_NUMBER || '-' || FTH.TRANSACTION_HEADER_ID TRANSACTION_NUMBER
, FAL.MEANING TRANSACTION_TYPE
, NULL TRANSACTION_STATUS
, FTH.TRANSACTION_DATE_ENTERED TRANSACTION_DATE
, OALV.CORPORATE_BOOK || ' - ' || FC.CONCATENATED_SEGMENTS DESCRIPTION
, NULL PRODUCT_NAME
, OKL_ACCOUNTING_UTIL.GET_FA_TRX_AMOUNT (OALV.CORPORATE_BOOK
, OALV.ASSET_ID
, FTH.TRANSACTION_TYPE_CODE
, FTH.TRANSACTION_HEADER_ID) AMOUNT
, OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE CURRENCY_CODE
, 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS'
, 'ACTUAL') ACTUAL_DRAFT_STATUS
, NULL TOTAL_TRANSACTION_AMOUNT
, NULL CHECK_ID
, OKHB.ORG_ID
, GL.SHORT_NAME REPRESENTATION_CODE
, NULL VENDOR_ID
, NULL ACCOUNT_NUMBER
, NULL CONLS_INV_ID
, NULL BOOK_TYPE_CODE
, NULL PERIOD_COUNTER
, NULL DEPRN_RUN_ID
, -888 TRY_ID
, 'N' INVOICE_YN
FROM OKL_VIEW_TRX_ASSETS_V OALV
, OKC_K_HEADERS_B OKHB
, FA_TRANSACTION_HEADERS FTH
, FA_LOOKUPS FAL
, FA_ADDITIONS FAD
, FA_CATEGORIES_B_KFV FC
, FA_BOOK_CONTROLS FAB
, GL_LEDGERS GL
WHERE OALV.DNZ_CHR_ID = OKHB.ID
AND OALV.ASSET_ID = FTH.ASSET_ID
AND OALV.CORPORATE_BOOK = FTH.BOOK_TYPE_CODE
AND FTH.TRANSACTION_TYPE_CODE NOT IN ('TRANSFER'
, 'TRANSFER IN'
, 'TRANSFER IN/VOID'
, 'ADDITION/VOID'
, 'TRANSFER OUT'
, 'CIP ADDITION/VOID')
AND FTH.TRANSACTION_TYPE_CODE = FAL.LOOKUP_CODE
AND FAL.LOOKUP_TYPE = 'FAXOLTRX'
AND OALV.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 )
AND FAB.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE
AND FAB.SET_OF_BOOKS_ID = GL.LEDGER_ID UNION ALL SELECT OALV.TAX_BOOK || '-' || OALV.ASSET_NUMBER || '-' || FDS.PERIOD_COUNTER ID
, /* VIEW 6 */ TO_CHAR(FDS.ASSET_ID) TRANS_ID
, OKHB.ID KHR_ID
, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER
, 'FA' SOURCE_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES'
, 'FA') SOURCE
, OALV.ASSET_NUMBER || ' - ' || FDP.PERIOD_NAME TRANSACTION_NUMBER
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('PN_PAYMENT_PURPOSE_TYPE'
, 'DEP'
, 240
, 0) TRANSACTION_TYPE
, NULL TRANSACTION_STATUS
, TRUNC(FDP.CALENDAR_PERIOD_CLOSE_DATE) TRANSACTION_DATE
, OALV.TAX_BOOK || '-' || FC.CONCATENATED_SEGMENTS DESCRIPTION
, NULL PRODUCT_NAME
, FDS.DEPRN_AMOUNT AMOUNT
, OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE CURRENCY_CODE
, 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS'
, 'ACTUAL') ACTUAL_DRAFT_STATUS
, NULL TOTAL_TRANSACTION_AMOUNT
, NULL CHECK_ID
, OKHB.ORG_ID
, GL.SHORT_NAME REPRESENTATION_CODE
, NULL VENDOR_ID
, NULL ACCOUNT_NUMBER
, NULL CONLS_INV_ID
, FDS.BOOK_TYPE_CODE BOOK_TYPE_CODE
, FDS.PERIOD_COUNTER PERIOD_COUNTER
, FDS.DEPRN_RUN_ID DEPRN_RUN_ID
, -999 TRY_ID
, 'N' INVOICE_YN
FROM OKL_TAX_BK_TRX_ASSTS_V OALV
, FA_DEPRN_SUMMARY FDS
, OKC_K_HEADERS_B OKHB
, FA_DEPRN_PERIODS FDP
, FA_ADDITIONS FAD
, FA_CATEGORIES_B_KFV FC
, OKL_SYS_ACCT_OPTS SAO
, OKL_SYSTEM_PARAMS SYS
, FA_BOOK_CONTROLS FAB
, GL_LEDGERS GL
WHERE FDS.ASSET_ID = OALV.ASSET_ID
AND FDS.BOOK_TYPE_CODE = OALV.TAX_BOOK
AND OALV.DNZ_CHR_ID = OKHB.ID
AND FDS.PERIOD_COUNTER = FDP.PERIOD_COUNTER
AND FDS.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND OALV.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.TAX_BOOK
, OALV.DNZ_CHR_ID
, OKHB.STS_CODE) OR OKL_ACCOUNTING_UTIL.GET_FA_TRX_START_DATE(OALV.ASSET_NUMBER
, OALV.TAX_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.TAX_BOOK
, OALV.DNZ_CHR_ID) OR OKL_ACCOUNTING_UTIL.GET_FA_TRX_END_DATE(OALV.ASSET_NUMBER
, OALV.TAX_BOOK
, OALV.DNZ_CHR_ID) IS NULL)))
AND FAB.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
AND SAO.ORG_ID = SYS.ORG_ID
AND SYS.RPT_PROD_BOOK_TYPE_CODE = FAB.BOOK_TYPE_CODE
AND FAB.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND SAO.SECONDARY_REP_METHOD = 'AUTOMATED' UNION ALL SELECT /* VIEW 7 */ OALV.CORPORATE_BOOK || '-' || OALV.ASSET_NUMBER || '-' || FDS.PERIOD_COUNTER ID
, TO_CHAR(FDS.ASSET_ID) TRANS_ID
, OKHB.ID KHR_ID
, OKHB.CONTRACT_NUMBER CONTRACT_NUMBER
, 'FA' SOURCE_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES'
, 'FA') SOURCE
, OALV.ASSET_NUMBER || ' - ' || FDP.PERIOD_NAME TRANSACTION_NUMBER
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('PN_PAYMENT_PURPOSE_TYPE'
, 'DEP'
, 240
, 0) TRANSACTION_TYPE
, NULL TRANSACTION_STATUS
, TRUNC(FDP.CALENDAR_PERIOD_CLOSE_DATE) TRANSACTION_DATE
, OALV.CORPORATE_BOOK || '-' || FC.CONCATENATED_SEGMENTS DESCRIPTION
, NULL PRODUCT_NAME
, FDS.DEPRN_AMOUNT AMOUNT
, OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE CURRENCY_CODE
, 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS'
, 'ACTUAL') ACTUAL_DRAFT_STATUS
, NULL TOTAL_TRANSACTION_AMOUNT
, NULL CHECK_ID
, OKHB.ORG_ID
, GL.SHORT_NAME REPRESENTATION_CODE
, NULL VENDOR_ID
, NULL ACCOUNT_NUMBER
, NULL CONLS_INV_ID
, FDS.BOOK_TYPE_CODE BOOK_TYPE_CODE
, FDS.PERIOD_COUNTER PERIOD_COUNTER
, FDS.DEPRN_RUN_ID DEPRN_RUN_ID
, -999 TRY_ID
, 'N' INVOICE_YN
FROM OKL_VIEW_TRX_ASSETS_V OALV
, FA_DEPRN_SUMMARY FDS
, OKC_K_HEADERS_B OKHB
, FA_DEPRN_PERIODS FDP
, FA_ADDITIONS FAD
, FA_CATEGORIES_B_KFV FC
, FA_BOOK_CONTROLS FAB
, GL_LEDGERS GL
WHERE FDS.ASSET_ID = OALV.ASSET_ID
AND FDS.BOOK_TYPE_CODE = OALV.CORPORATE_BOOK
AND OALV.DNZ_CHR_ID = OKHB.ID
AND FDS.PERIOD_COUNTER = FDP.PERIOD_COUNTER
AND FDS.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND OALV.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 )))
AND FAB.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
AND FAB.SET_OF_BOOKS_ID = GL.LEDGER_ID UNION ALL SELECT /* VIEW 8 */ TO_CHAR(RCT.ID) ID
, TO_CHAR(RCT.ID) TRANS_ID
, RCT.KHR_ID KHR_ID
, RCT.CONTRACT_NUMBER CONTRACT_NUMBER
, RCT.SOURCE_CODE SOURCE_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES'
, 'REC') SOURCE
, RCT.TRANSACTION_NUMBER TRANSACTION_NUMBER
, RCT.TRANSACTION_TYPE TRANSACTION_TYPE
, RCT.TRANSACTION_STATUS TRANSACTION_STATUS
, RCT.TRANSACTION_DATE TRANSACTION_DATE
, RCT.DESCRIPTION DESCRIPTION
, NULL PRODUCT_NAME
, RCT.AMOUNT AMOUNT
, RCT.CURRENCY_CODE CURRENCY_CODE
, RCT.ACTUAL_DRAFT_STATUS_CODE
, RCT.ACTUAL_DRAFT_STATUS
, RCT.TOTAL_TRANSACTION_AMOUNT TOTAL_TRANSACTION_AMOUNT
, RCT.CHECK_ID CHECK_ID
, RCT.ORG_ID ORG_ID
, GL.SHORT_NAME REPRESENTATION_CODE
, NULL VENDOR_ID
, RCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, NULL CONLS_INV_ID
, NULL BOOK_TYPE_CODE
, NULL PERIOD_COUNTER
, NULL DEPRN_RUN_ID
, RCT.TRY_ID TRY_ID
, 'Y' INVOICE_YN
FROM OKL_BPD_AR_HEADER_V RCT
, GL_LEDGERS GL
WHERE RCT.LEDGER_ID = GL.LEDGER_ID UNION ALL SELECT /* VIEW 9 */ TO_CHAR(RAP.RECEIPT_ID) ID
, TO_CHAR(RAP.RECEIPT_ID) TRANS_ID
, RAP.CONTRACT_ID KHR_ID
, RAP.CONTRACT_NUMBER CONTRACT_NUMBER
, RAP.SOURCE_CODE SOURCE_CODE
, RAP.SOURCE SOURCE
, RAP.RECEIPT_NUMBER TRANSACTION_NUMBER
, RAP.TRANSACTION_TYPE TRANSACTION_TYPE
, RAP.TRANSACTION_STATUS TRANSACTION_STATUS
, RAP.RECEIPT_DATE TRANSACTION_DATE
, RAP.DESCRIPTION DESCRIPTION
, NULL PRODUCT_NAME
, /*NOT USED*/ RAP.AMOUNT_APPLIED AMOUNT
, RAP.CURRENCY_CODE CURRENCY_CODE
, 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS'
, 'ACTUAL') ACTUAL_DRAFT_STATUS
, RAP.TOTAL_AMOUNT TOTAL_TRANSACTION_AMOUNT
, NULL CHECK_ID
, RAP.ORG_ID
, GL.SHORT_NAME REPRESENTATION_CODE
, NULL VENDOR_ID
, NULL ACCOUNT_NUMBER
, NULL CONLS_INV_ID
, NULL BOOK_TYPE_CODE
, NULL PERIOD_COUNTER
, NULL DEPRN_RUN_ID
, -777 TRY_ID
, 'N' INVOICE_YN
FROM OKL_BPD_TRX_RCPT_HEADER_V RAP
, GL_LEDGERS GL
WHERE GL.LEDGER_ID = RAP.SET_OF_BOOKS_ID UNION ALL SELECT ID
, /* VIEW 10 */ AR_ADJ.ID TRANS_ID
, AR_ADJ.KHR_ID
, AR_ADJ.CONTRACT_NUMBER
, AR_ADJ.SOURCE_CODE
, AR_ADJ.SOURCE
, AR_ADJ.TRANSACTION_NUMBER
, AR_ADJ.TRANSACTION_TYPE
, AR_ADJ.TRANSACTION_STATUS
, AR_ADJ.TRANSACTION_DATE
, AR_ADJ.DESCRIPTION
, AR_ADJ.PRODUCT_NAME
, AR_ADJ.AMOUNT
, AR_ADJ.CURRENCY_CODE
, AR_ADJ.ACTUAL_DRAFT_STATUS_CODE
, AR_ADJ.ACTUAL_DRAFT_STATUS
, AR_ADJ.TOTAL_TRANSACTION_AMOUNT
, AR_ADJ.CHECK_ID
, AR_ADJ.ORG_ID
, GL.SHORT_NAME REPRESENTATION_CODE
, NULL VENDOR_ID
, AR_ADJ.ACCOUNT_NUMBER
, NULL CONLS_INV_ID
, NULL BOOK_TYPE_CODE
, NULL PERIOD_COUNTER
, NULL DEPRN_RUN_ID
, AR_ADJ.TRY_ID
, NULL INVOICE_YN
FROM OKL_BPD_AR_ADJUSTMENTS_V AR_ADJ
, GL_LEDGERS GL
WHERE GL.LEDGER_ID = AR_ADJ.LEDGER_ID