VIEW: APPS.CE_CP_DISC_V

Source

View Text - Preformatted

SELECT HEAD.worksheet_header_id, HEAD.bank_account_id, HEAD.worksheet_line_id, DATA.balance, HEAD.source_description, HEAD.source_type, HEAD.trx_description, DATA.detail FROM (SELECT WBA.worksheet_header_id, WBA.bank_account_id, SRC.source_type, SRC.worksheet_line_id, SRC.source_description, SRC.trx_description FROM CE_CP_WS_BA_DISC_V WBA, (SELECT WSH.worksheet_header_id, LK.lookup_code SOURCE_TYPE, LK.meaning SOURCE_DESCRIPTION, null TRX_DESCRIPTION, -1 WORKSHEET_LINE_ID FROM CE_CP_WORKSHEET_HEADERS WSH, CE_LOOKUPS LK WHERE LK.lookup_type = 'POSITION_SRC_TYPE' AND LK.lookup_code in ('OPEN', 'PRIOR', 'NET', 'CLOSE', 'TARGET', 'SURPLUS') UNION ALL SELECT WSL.worksheet_header_id, decode(WSL.source_type, 'IDA', 'IDA', 'TRX') SOURCE_TYPE, decode(WSL.source_type, 'IDA', LK2.meaning, LK.meaning) SOURCE_DESCRIPTION, WSL.description SOURCE_DETAIL, WSL.worksheet_line_id FROM CE_CP_WORKSHEET_LINES WSL, CE_LOOKUPS LK, CE_LOOKUPS LK2 WHERE WSL.source_type in ('APP', 'ARR', 'PAY', 'XTI', 'XTO', 'UDI', 'UDO', 'IDA', 'CEI', 'CEO') AND WSL.include_flag = 'Y' AND LK.lookup_type = 'POSITION_SRC_TYPE' AND LK.lookup_code = 'TRX' AND LK2.lookup_type = 'POSITION_SRC_TYPE' AND LK2.lookup_code = 'IDA') SRC WHERE WBA.worksheet_header_id = SRC.worksheet_header_id) HEAD, (SELECT OPEN.worksheet_header_id, OPEN.bank_account_id, -1 WORKSHEET_LINE_ID, OPEN.balance, 'OPEN' SOURCE_TYPE, null SOURCE_DETAIL, null DETAIL FROM CE_CP_DISC_OPEN_V OPEN UNION ALL SELECT PD.worksheet_header_id, PD.bank_account_id, -1 WORKSHEET_LINE_ID, PD.balance, 'PRIOR' SOURCE_TYPE, null SOURCE_DETAIL, null DETAIL FROM CE_CP_DISC_PRIOR_V PD UNION ALL SELECT TRX.worksheet_header_id, TRX.bank_account_id, TRX.worksheet_line_id, TRX.balance, decode(TRX.trx_type, 'IDA', 'IDA', 'TRX') SOURCE_TYPE, TRX.trx_description SOURCE_DETAIL, TRX.detail FROM CE_CP_DISC_TRX_V TRX UNION ALL SELECT NET.worksheet_header_id, NET.bank_account_id, -1 WORKSHEET_LINE_ID, NET.balance, 'NET' SOURCE_TYPE, null SOURCE_DETAIL, null DETAIL FROM CE_CP_DISC_NET_V NET UNION ALL SELECT CLOSE.worksheet_header_id, CLOSE.bank_account_id, -1 WORKSHEET_LINE_ID, CLOSE.balance, 'CLOSE' SOURCE_TYPE, null SOURCE_DETAIL, null DETAIL FROM CE_CP_DISC_CLOSE_V CLOSE UNION ALL SELECT WSH.worksheet_header_id, TARGET.bank_account_id, -1 WORKSHEET_LINE_ID, TARGET.target_balance, 'TARGET' SOURCE_TYPE, null SOURCE_DETAIL, null DETAIL FROM CE_CP_WORKSHEET_HEADERS WSH, CE_CP_DISC_TARGET_V TARGET UNION ALL SELECT SURPLUS.worksheet_header_id, SURPLUS.bank_account_id, -1 WORKSHEET_LINE_ID, SURPLUS.balance, 'SURPLUS' SOURCE_TYPE, null SOURCE_DETAIL, null DETAIL FROM CE_CP_DISC_SURPLUS_V SURPLUS) DATA WHERE HEAD.worksheet_header_id = DATA.worksheet_header_id(+) AND HEAD.bank_account_id = DATA.bank_account_id(+) AND HEAD.source_type = DATA.source_type(+) AND HEAD.worksheet_line_id = DATA.worksheet_line_id(+)

View Text - HTML Formatted

SELECT HEAD.WORKSHEET_HEADER_ID
, HEAD.BANK_ACCOUNT_ID
, HEAD.WORKSHEET_LINE_ID
, DATA.BALANCE
, HEAD.SOURCE_DESCRIPTION
, HEAD.SOURCE_TYPE
, HEAD.TRX_DESCRIPTION
, DATA.DETAIL
FROM (SELECT WBA.WORKSHEET_HEADER_ID
, WBA.BANK_ACCOUNT_ID
, SRC.SOURCE_TYPE
, SRC.WORKSHEET_LINE_ID
, SRC.SOURCE_DESCRIPTION
, SRC.TRX_DESCRIPTION
FROM CE_CP_WS_BA_DISC_V WBA
, (SELECT WSH.WORKSHEET_HEADER_ID
, LK.LOOKUP_CODE SOURCE_TYPE
, LK.MEANING SOURCE_DESCRIPTION
, NULL TRX_DESCRIPTION
, -1 WORKSHEET_LINE_ID
FROM CE_CP_WORKSHEET_HEADERS WSH
, CE_LOOKUPS LK
WHERE LK.LOOKUP_TYPE = 'POSITION_SRC_TYPE'
AND LK.LOOKUP_CODE IN ('OPEN'
, 'PRIOR'
, 'NET'
, 'CLOSE'
, 'TARGET'
, 'SURPLUS') UNION ALL SELECT WSL.WORKSHEET_HEADER_ID
, DECODE(WSL.SOURCE_TYPE
, 'IDA'
, 'IDA'
, 'TRX') SOURCE_TYPE
, DECODE(WSL.SOURCE_TYPE
, 'IDA'
, LK2.MEANING
, LK.MEANING) SOURCE_DESCRIPTION
, WSL.DESCRIPTION SOURCE_DETAIL
, WSL.WORKSHEET_LINE_ID
FROM CE_CP_WORKSHEET_LINES WSL
, CE_LOOKUPS LK
, CE_LOOKUPS LK2
WHERE WSL.SOURCE_TYPE IN ('APP'
, 'ARR'
, 'PAY'
, 'XTI'
, 'XTO'
, 'UDI'
, 'UDO'
, 'IDA'
, 'CEI'
, 'CEO')
AND WSL.INCLUDE_FLAG = 'Y'
AND LK.LOOKUP_TYPE = 'POSITION_SRC_TYPE'
AND LK.LOOKUP_CODE = 'TRX'
AND LK2.LOOKUP_TYPE = 'POSITION_SRC_TYPE'
AND LK2.LOOKUP_CODE = 'IDA') SRC
WHERE WBA.WORKSHEET_HEADER_ID = SRC.WORKSHEET_HEADER_ID) HEAD
, (SELECT OPEN.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID
, -1 WORKSHEET_LINE_ID
, OPEN.BALANCE
, 'OPEN' SOURCE_TYPE
, NULL SOURCE_DETAIL
, NULL DETAIL
FROM CE_CP_DISC_OPEN_V OPEN UNION ALL SELECT PD.WORKSHEET_HEADER_ID
, PD.BANK_ACCOUNT_ID
, -1 WORKSHEET_LINE_ID
, PD.BALANCE
, 'PRIOR' SOURCE_TYPE
, NULL SOURCE_DETAIL
, NULL DETAIL
FROM CE_CP_DISC_PRIOR_V PD UNION ALL SELECT TRX.WORKSHEET_HEADER_ID
, TRX.BANK_ACCOUNT_ID
, TRX.WORKSHEET_LINE_ID
, TRX.BALANCE
, DECODE(TRX.TRX_TYPE
, 'IDA'
, 'IDA'
, 'TRX') SOURCE_TYPE
, TRX.TRX_DESCRIPTION SOURCE_DETAIL
, TRX.DETAIL
FROM CE_CP_DISC_TRX_V TRX UNION ALL SELECT NET.WORKSHEET_HEADER_ID
, NET.BANK_ACCOUNT_ID
, -1 WORKSHEET_LINE_ID
, NET.BALANCE
, 'NET' SOURCE_TYPE
, NULL SOURCE_DETAIL
, NULL DETAIL
FROM CE_CP_DISC_NET_V NET UNION ALL SELECT CLOSE.WORKSHEET_HEADER_ID
, CLOSE.BANK_ACCOUNT_ID
, -1 WORKSHEET_LINE_ID
, CLOSE.BALANCE
, 'CLOSE' SOURCE_TYPE
, NULL SOURCE_DETAIL
, NULL DETAIL
FROM CE_CP_DISC_CLOSE_V CLOSE UNION ALL SELECT WSH.WORKSHEET_HEADER_ID
, TARGET.BANK_ACCOUNT_ID
, -1 WORKSHEET_LINE_ID
, TARGET.TARGET_BALANCE
, 'TARGET' SOURCE_TYPE
, NULL SOURCE_DETAIL
, NULL DETAIL
FROM CE_CP_WORKSHEET_HEADERS WSH
, CE_CP_DISC_TARGET_V TARGET UNION ALL SELECT SURPLUS.WORKSHEET_HEADER_ID
, SURPLUS.BANK_ACCOUNT_ID
, -1 WORKSHEET_LINE_ID
, SURPLUS.BALANCE
, 'SURPLUS' SOURCE_TYPE
, NULL SOURCE_DETAIL
, NULL DETAIL
FROM CE_CP_DISC_SURPLUS_V SURPLUS) DATA
WHERE HEAD.WORKSHEET_HEADER_ID = DATA.WORKSHEET_HEADER_ID(+)
AND HEAD.BANK_ACCOUNT_ID = DATA.BANK_ACCOUNT_ID(+)
AND HEAD.SOURCE_TYPE = DATA.SOURCE_TYPE(+)
AND HEAD.WORKSHEET_LINE_ID = DATA.WORKSHEET_LINE_ID(+)