VIEW: APPS.OKL_SPLIT_ASSETS_UV

Source

View Text - Preformatted

SELECT cle.cle_id cle_id, cle.dnz_chr_id chr_id, tl.name asset_number, tl.item_description description, cim.number_of_items units, kle.oec oec, tal.id tal_id, tal.split_into_singles_flag split_into_individuals, tal.split_into_units split_into_number_of_units, tas.date_trans_occurred trx_date FROM OKC_LINE_STYLES_B LSE,OKC_K_LINES_B CLE,OKC_STATUSES_B STS,OKL_K_LINES KLE,OKC_K_LINES_TL TL,OKC_K_ITEMS CIM,OKL_TRX_ASSETS TAS,OKL_TXL_ASSETS_B TAL WHERE TAL.KLE_ID = CLE.ID AND TAL.TAL_TYPE = 'ALI' AND TAS.TSU_CODE = 'ENTERED' AND TAS.TAS_TYPE = 'ALI' AND TAS.ID = TAL.TAS_ID AND CIM.CLE_ID = CLE.ID AND CIM.DNZ_CHR_ID = CLE.DNZ_CHR_ID AND CLE.LSE_ID = LSE.ID AND LSE.LTY_CODE = 'FIXED_ASSET' AND KLE.ID = CLE.CLE_ID AND CLE.CLE_ID = TL.ID AND STS.CODE = CLE.STS_CODE AND TRUNC(SYSDATE) between TRUNC(NVL(STS.START_DATE,SYSDATE)) and TRUNC(NVL(STS.END_DATE,SYSDATE+1)) and STS.STE_CODE not in ('EXPIRED','TERMINATED','CANCELLED') and tl.language=userenv('LANG') UNION SELECT cle.cle_id cle_id, cle.dnz_chr_id chr_id, tl.name asset_number, tl.item_description description, cim.number_of_items units, kle.oec oec, to_number(NULL) tal_id, NULL split_into_individuals, to_number(NULL) split_into_number_of_units, to_date(NULL) trx_date FROM OKC_LINE_STYLES_B LSE, OKC_K_LINES_B CLE, OKC_STATUSES_B STS, OKL_K_LINES KLE, OKC_K_LINES_TL TL, OKC_K_ITEMS CIM WHERE CIM.CLE_ID = CLE.ID AND CIM.DNZ_CHR_ID = CLE.DNZ_CHR_ID AND CLE.LSE_ID = LSE.ID AND LSE.LTY_CODE = 'FIXED_ASSET' AND KLE.ID = CLE.CLE_ID AND CLE.CLE_ID = TL.ID AND STS.CODE = CLE.STS_CODE AND TRUNC(SYSDATE) between TRUNC(NVL(STS.START_DATE,SYSDATE)) and TRUNC(NVL(STS.END_DATE,SYSDATE+1)) and STS.STE_CODE not in ('EXPIRED','TERMINATED','CANCELLED') and tl.language=userenv('LANG') and not exists (select 1 from OKL_TRX_ASSETS TAS, OKL_TXL_ASSETS_B TAL where TAS.ID = TAL.TAS_ID and TAS.TSU_CODE = 'ENTERED' and TAS.TAS_TYPE = 'ALI' and TAL.KLE_ID = CLE.ID and TAL.TAL_TYPE = 'ALI')

View Text - HTML Formatted

SELECT CLE.CLE_ID CLE_ID
, CLE.DNZ_CHR_ID CHR_ID
, TL.NAME ASSET_NUMBER
, TL.ITEM_DESCRIPTION DESCRIPTION
, CIM.NUMBER_OF_ITEMS UNITS
, KLE.OEC OEC
, TAL.ID TAL_ID
, TAL.SPLIT_INTO_SINGLES_FLAG SPLIT_INTO_INDIVIDUALS
, TAL.SPLIT_INTO_UNITS SPLIT_INTO_NUMBER_OF_UNITS
, TAS.DATE_TRANS_OCCURRED TRX_DATE
FROM OKC_LINE_STYLES_B LSE
, OKC_K_LINES_B CLE
, OKC_STATUSES_B STS
, OKL_K_LINES KLE
, OKC_K_LINES_TL TL
, OKC_K_ITEMS CIM
, OKL_TRX_ASSETS TAS
, OKL_TXL_ASSETS_B TAL
WHERE TAL.KLE_ID = CLE.ID
AND TAL.TAL_TYPE = 'ALI'
AND TAS.TSU_CODE = 'ENTERED'
AND TAS.TAS_TYPE = 'ALI'
AND TAS.ID = TAL.TAS_ID
AND CIM.CLE_ID = CLE.ID
AND CIM.DNZ_CHR_ID = CLE.DNZ_CHR_ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND KLE.ID = CLE.CLE_ID
AND CLE.CLE_ID = TL.ID
AND STS.CODE = CLE.STS_CODE
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(STS.START_DATE
, SYSDATE))
AND TRUNC(NVL(STS.END_DATE
, SYSDATE+1))
AND STS.STE_CODE NOT IN ('EXPIRED'
, 'TERMINATED'
, 'CANCELLED')
AND TL.LANGUAGE=USERENV('LANG') UNION SELECT CLE.CLE_ID CLE_ID
, CLE.DNZ_CHR_ID CHR_ID
, TL.NAME ASSET_NUMBER
, TL.ITEM_DESCRIPTION DESCRIPTION
, CIM.NUMBER_OF_ITEMS UNITS
, KLE.OEC OEC
, TO_NUMBER(NULL) TAL_ID
, NULL SPLIT_INTO_INDIVIDUALS
, TO_NUMBER(NULL) SPLIT_INTO_NUMBER_OF_UNITS
, TO_DATE(NULL) TRX_DATE
FROM OKC_LINE_STYLES_B LSE
, OKC_K_LINES_B CLE
, OKC_STATUSES_B STS
, OKL_K_LINES KLE
, OKC_K_LINES_TL TL
, OKC_K_ITEMS CIM
WHERE CIM.CLE_ID = CLE.ID
AND CIM.DNZ_CHR_ID = CLE.DNZ_CHR_ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND KLE.ID = CLE.CLE_ID
AND CLE.CLE_ID = TL.ID
AND STS.CODE = CLE.STS_CODE
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(STS.START_DATE
, SYSDATE))
AND TRUNC(NVL(STS.END_DATE
, SYSDATE+1))
AND STS.STE_CODE NOT IN ('EXPIRED'
, 'TERMINATED'
, 'CANCELLED')
AND TL.LANGUAGE=USERENV('LANG')
AND NOT EXISTS (SELECT 1
FROM OKL_TRX_ASSETS TAS
, OKL_TXL_ASSETS_B TAL
WHERE TAS.ID = TAL.TAS_ID
AND TAS.TSU_CODE = 'ENTERED'
AND TAS.TAS_TYPE = 'ALI'
AND TAL.KLE_ID = CLE.ID
AND TAL.TAL_TYPE = 'ALI')