[Home] [Help]
View: OKL_BPD_RECEIPT_ASSET_UV
View Text
SELECT XTL.XCR_ID_DETAILS EXT_ID
, FA.ASSET_ID ASSET_ID
, FA.SERIAL_NUMBER ASSET_NUMBER
, FA.ASSET_NUMBER ASSET_NAME
, FA.DESCRIPTION ASSET_DESCRIPTION
, TO_CHAR (SUM (XTL.AMOUNT_APPLIED)
, '99
, 999
, 999
, 999.99') AMOUNT_APPLIED
, KLE.STS_CODE ASSET_STATUS
, OST.NAME STREAM_NAME
, LKUP.MEANING
FROM FND_LOOKUPS LKUP
, FA_ADDITIONS_V FA
, OKL_STRM_TYPE_V OST
, OKL_XTL_CSH_APPS_B XTL
, OKL_CNSLD_AR_STRMS_B ST
, OKC_K_LINES_B KLE
, OKC_LINE_STYLES_B LSE
, OKC_K_ITEMS CIM
WHERE ST.ID = XTL.LSM_ID
AND ST.STY_ID = OST.ID
AND KLE.CLE_ID = ST.KLE_ID
AND KLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND CIM.CLE_ID = KLE.ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID2 = '#'
AND FA.ASSET_ID = CIM.OBJECT1_ID1
AND LKUP.LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LKUP.LOOKUP_CODE = OST.STREAM_TYPE_PURPOSE GROUP BY XTL.XCR_ID_DETAILS
, FA.ASSET_ID
, FA.ASSET_NUMBER
, FA.SERIAL_NUMBER
, FA.DESCRIPTION
, KLE.STS_CODE
, OST.NAME
, LKUP.MEANING UNION ALL SELECT XTL.XCR_ID_DETAILS
, TO_NUMBER (NULL)
, ''
, ''
, ''
, TO_CHAR (SUM (XTL.AMOUNT_APPLIED)
, '99
, 999
, 999
, 999.99')
, ''
, OST.NAME
, LKUP.MEANING
FROM OKL_XTL_CSH_APPS_B XTL
, OKL_CNSLD_AR_STRMS_B ST
, OKL_STRM_TYPE_V OST
, FND_LOOKUPS LKUP
WHERE ST.ID = XTL.LSM_ID
AND OST.ID = ST.STY_ID
AND ( ST.KLE_ID IS NULL OR ( ST.KLE_ID IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM OKC_K_LINES_B KLE
, OKC_LINE_STYLES_B LSE
WHERE KLE.CLE_ID = ST.KLE_ID
AND KLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET')))
AND LKUP.LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LKUP.LOOKUP_CODE = OST.STREAM_TYPE_PURPOSE GROUP BY XTL.XCR_ID_DETAILS
, OST.NAME
, LKUP.MEANING UNION ALL SELECT XTL.XCR_ID_DETAILS EXT_ID
, TO_NUMBER (NULL)
, ''
, ''
, ''
, TO_CHAR (SUM (XTL.AMOUNT_APPLIED)
, '99
, 999
, 999
, 999.99') AMOUNT_APPLIED
, KLE.STS_CODE ASSET_STATUS
, OST.NAME STREAM_NAME
, LKUP.MEANING
FROM FND_LOOKUPS LKUP
, OKL_STRM_TYPE_V OST
, OKL_XTL_CSH_APPS_B XTL
, OKL_CNSLD_AR_STRMS_B ST
, OKC_K_LINES_B KLE
, OKC_LINE_STYLES_B LSE
, OKC_K_ITEMS CIM
WHERE ST.ID = XTL.LSM_ID
AND ST.STY_ID = OST.ID
AND KLE.CLE_ID = ST.KLE_ID
AND KLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND CIM.CLE_ID = KLE.ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID1 IS NULL
AND LKUP.LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LKUP.LOOKUP_CODE = OST.STREAM_TYPE_PURPOSE GROUP BY XTL.XCR_ID_DETAILS
, KLE.STS_CODE
, OST.NAME
, LKUP.MEANING
Columns
Name |
EXT_ID |
ASSET_ID |
ASSET_NUMBER |
ASSET_NAME |
ASSET_DESCRIPTION |
AMOUNT_APPLIED |
ASSET_STATUS |
STREAM_NAME |
STREAM_PURPOSE_MEANING |