View: OKL_TRX_HEADER_UV

Product:	OKL - Lease and Finance Management
Description:
Implementation/DBA Data:	APPS.OKL_TRX_HEADER_UV

View Text

SELECT TO_CHAR(OTCB.ID) ID
, 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
, 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
, NULL 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
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) UNION ALL SELECT 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
, NULL 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
WHERE AI.TRY_ID = TRY.ID UNION ALL SELECT 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
, NULL 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
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 ) UNION ALL SELECT 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
, NULL 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
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 (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 ) UNION ALL SELECT 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
, NULL 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 UNION ALL SELECT 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
, NULL 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 UNION ALL SELECT ID
, ID TRANS_ID
, KHR_ID
, CONTRACT_NUMBER
, SOURCE_CODE
, SOURCE
, TRANSACTION_NUMBER
, TRANSACTION_TYPE
, TRANSACTION_STATUS
, TRANSACTION_DATE
, DESCRIPTION
, PRODUCT_NAME
, AMOUNT
, CURRENCY_CODE
, ACTUAL_DRAFT_STATUS_CODE
, ACTUAL_DRAFT_STATUS
, TOTAL_TRANSACTION_AMOUNT
, CHECK_ID
, ORG_ID
, NULL REPRESENTATION_CODE
, NULL VENDOR_ID
, ACCOUNT_NUMBER
, NULL CONLS_INV_ID
, NULL BOOK_TYPE_CODE
, NULL PERIOD_COUNTER
, NULL DEPRN_RUN_ID
, TRY_ID
, NULL INVOICE_YN
FROM OKL_BPD_AR_ADJUSTMENTS_V

Columns