VIEW: APPS.CE_CP_DISC_TRX_V

Source

View Text - Preformatted

SELECT TRX.worksheet_header_id, TRX.bank_account_id, TRX.worksheet_line_id, TRX.description, TRX.source_type, TRX.detail, TRX.balance FROM (SELECT worksheet_header_id, bank_account_id, worksheet_line_id, description, 'ARR' SOURCE_TYPE, detail, balance, reference_id FROM CE_CP_DISC_ARR_V UNION ALL SELECT worksheet_header_id, bank_account_id, worksheet_line_id, description, 'APP' SOURCE_TYPE, detail, balance, reference_id FROM CE_CP_DISC_APP_V UNION ALL SELECT worksheet_header_id, bank_account_id, worksheet_line_id, description, 'XTI' SOURCE_TYPE, detail, balance, reference_id FROM CE_CP_DISC_XTI_V UNION ALL SELECT worksheet_header_id, bank_account_id, worksheet_line_id, description, 'XTO' SOURCE_TYPE, detail, balance, reference_id FROM CE_CP_DISC_XTO_V UNION ALL SELECT worksheet_header_id, bank_account_id, worksheet_line_id, description, 'PAY' SOURCE_TYPE, detail, balance, reference_id FROM CE_CP_DISC_PAY_V UNION ALL SELECT worksheet_header_id, bank_account_id, worksheet_line_id, description, 'CEI' SOURCE_TYPE, detail, balance, reference_id FROM CE_CP_DISC_CEI_V UNION ALL SELECT worksheet_header_id, bank_account_id, worksheet_line_id, description, 'CEO' SOURCE_TYPE, detail, balance, reference_id FROM CE_CP_DISC_CEO_V UNION ALL SELECT worksheet_header_id, bank_account_id, worksheet_line_id, description, source_type, description, decode(source_type, 'UDO', -amount, amount) BALANCE, worksheet_line_id REFERENCE_ID FROM CE_CP_WORKSHEET_LINES WHERE source_type in ('UDI', 'UDO')) TRX WHERE not exists ( SELECT 1 FROM CE_CP_TRX_EXCLUDED EXC WHERE EXC.worksheet_header_id = TRX.worksheet_header_id AND EXC.trx_type = TRX.source_type AND EXC.as_of_date = to_date(sysdate,'YYYY/MM/DD') AND EXC.reference_id = TRX.reference_id)

View Text - HTML Formatted

SELECT TRX.WORKSHEET_HEADER_ID
, TRX.BANK_ACCOUNT_ID
, TRX.WORKSHEET_LINE_ID
, TRX.DESCRIPTION
, TRX.SOURCE_TYPE
, TRX.DETAIL
, TRX.BALANCE
FROM (SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'ARR' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID
FROM CE_CP_DISC_ARR_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'APP' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID
FROM CE_CP_DISC_APP_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'XTI' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID
FROM CE_CP_DISC_XTI_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'XTO' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID
FROM CE_CP_DISC_XTO_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'PAY' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID
FROM CE_CP_DISC_PAY_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'CEI' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID
FROM CE_CP_DISC_CEI_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'CEO' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID
FROM CE_CP_DISC_CEO_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, SOURCE_TYPE
, DESCRIPTION
, DECODE(SOURCE_TYPE
, 'UDO'
, -AMOUNT
, AMOUNT) BALANCE
, WORKSHEET_LINE_ID REFERENCE_ID
FROM CE_CP_WORKSHEET_LINES
WHERE SOURCE_TYPE IN ('UDI'
, 'UDO')) TRX
WHERE NOT EXISTS ( SELECT 1
FROM CE_CP_TRX_EXCLUDED EXC
WHERE EXC.WORKSHEET_HEADER_ID = TRX.WORKSHEET_HEADER_ID
AND EXC.TRX_TYPE = TRX.SOURCE_TYPE
AND EXC.AS_OF_DATE = TO_DATE(SYSDATE
, 'YYYY/MM/DD')
AND EXC.REFERENCE_ID = TRX.REFERENCE_ID)